MySql Tinytext vs Varchar vs Char

Question

Den Aufbau eines Systems, das mit vielen Zugriffen und Traffic stark belastet werden könnte. Es handelt sich um eine typische Apache/PHP/MySql-Konfiguration.

Habe schon viele Systeme gebaut, aber noch nie musste ich Entscheidungen zur potenziellen Skalierbarkeit in dieser Größenordnung treffen. Ich habe Dutzende von Fragen zum Bau eines Systems dieser Größenordnung, aber für diese spezielle Frage versuche ich zu entscheiden, welchen Datentyp ich verwenden soll.

Hier ist der 100-Fuß-Blick:

Wir haben eine Tabelle, die (unter anderem) ein Beschreibung-Feld hat. Wir haben beschlossen, es auf 255 Zeichen zu beschränken. Es wird durchsuchbar sein (d.h.: Zeige mir alle Einträge mit einer Beschreibung, die ... enthält). Problem: Diese Tabelle wird wahrscheinlich irgendwann Millionen von Einträgen haben (oder so denken wir).

Ich habe die Strategie für die Suche noch nicht ausgearbeitet (der MySql LIKE-Operator wird vermutlich langsam und/oder ein Ressourcenfresser sein, vermute ich, für eine so große Anzahl von Datensätzen), aber das ist eine andere Frage für Stack Overflow. Für diese Frage frage ich mich, welche Vor- und Nachteile es hat, dieses Feld als tinytext, varchar und char zu erstellen.

Ich bin kein Datenbankexperte, also ist jeglicher Kommentar hilfreich. Danke -

Answer 1

Verwenden Sie ein CHAR.

BLOB's und TEXT's werden außerhalb der Zeile gespeichert, daher gibt es einen Zugriffsstrafe beim Lesen von ihnen. VARCHAR's sind variabler Länge, was Speicherplatz spart, aber eine geringe Zugriffsstrafe verursachen könnte (da die Zeilen nicht alle fest sind Länge).

Wenn Sie Ihren Index jedoch ordnungsgemäß erstellen, kann entweder VARCHAR oder CHAR vollständig im Index gespeichert werden, was den Zugriff erheblich beschleunigen wird.

Siehe: varchar(255) v tinyblob v tinytext
Und: http://213.136.52.31/mysql/540
Und: http://forums.mysql.com/read.php?10,254231,254231#msg-254231
Und: http://forums.mysql.com/read.php?20,223006,223683#msg-223683

Übrigens ist meiner Erfahrung nach der MySQL regex Operator für einfache Abfragen (d.h., SELECT ID WHERE SOME_COLUMN REGEX 'search.*') wesentlich schneller als LIKE und offensichtlich vielseitiger.

Answer 2

Ich glaube, dass bei varchar eine variable Länge in der eigentlichen Datenbank auf niedriger Ebene gespeichert ist, was bedeutet, dass es weniger Speicherplatz beanspruchen könnte. Bei dem Textfeld ist die Länge fixiert, auch wenn eine Zeile nicht alles davon verwendet. Der String mit fester Länge sollte schneller abfragbar sein.

Bearbeiten: Ich habe es gerade nachgeschlagen, Texttypen werden auch als variable Länge gespeichert. Das Beste wäre, es mit etwas wie mysqlslap zu benchmarken.

In Bezug auf Ihre nicht gestellte Frage, möchten Sie wahrscheinlich eine Art Suchindex erstellen, der jedes nützliche Wort im Beschreibungsfeld einzeln mit einer Beschreibung verknüpft, dann können Sie das indexieren und danach suchen. Das wird viel viel schneller sein als %like%.

Answer 3

In Ihrer Situation sind alle drei Typen schlecht, wenn Sie LIKE verwenden (ein LIKE '%string%' verwendet keinen Index, der auf dieser Spalte erstellt wurde, unabhängig von ihrem Typ). Alles andere ist nur Lärm.

Ich kenne keinen wesentlichen Unterschied zwischen TINYTEXT und VARCHAR bis zu 255 Zeichen, und CHAR ist einfach nicht für Zeichenketten variabler Länge gedacht.

Also mein Vorschlag: wählen Sie VARCHAR oder TINYTEXT (ich persönlich würde VARCHAR wählen) und indizieren Sie den Inhalt dieser Spalte mithilfe einer Volltext-Suchmaschine wie Lucene, Sphinx oder einer anderen, die die Arbeit für Sie erledigt. Vergessen Sie einfach LIKE (auch wenn Sie dafür den Volltextsuchindex-Motor selbst erstellen müssen aus welchen Gründen auch immer, z.B. wenn Sie Unterstützung für eine Reihe von Funktionen benötigen, die kein vorhandener Motor erfüllen kann).

Answer 4

Wenn Sie unter Millionen von Zeilen suchen möchten, speichern Sie all diese Texte in einer anderen Tabelle (was die Zeilengröße Ihrer großen Tabelle verringert) und verwenden Sie VARCHAR, wenn Ihre Textdaten kurz sind, oder TEXT, wenn Sie eine größere Länge benötigen.

Verwenden Sie anstelle von LIKE eine spezialisierte Lösung wie Lucene, Sphinx oder Solr. Ich erinnere mich nicht genau, aber mindestens eine davon kann leicht für Echtzeit- oder nahe Echtzeitindizierung konfiguriert werden.

BEARBEITEN

Mein Vorschlag, Texte in einer anderen Tabelle zu speichern, reduziert den für die Haupttabelle erforderlichen E/A-Vorgang, erfordert jedoch beim Einfügen von Daten das Beibehalten eines zusätzlichen Index und fügt einen Join-Overhead in den SELECTs hinzu. Dies ist daher nur gültig, wenn Sie Ihre Tabelle verwenden, um gleichzeitig wenige Beschreibungen zu lesen und andere Daten aus der Tabelle häufiger verwendet werden.