MySql Tinytext vs. Varchar vs. Char

Aufbau eines Systems, das das Potenzial hat, mit Treffern und Verkehr ziemlich hart gehämmert zu werden. Es ist ein typisches Apache/PHP/MySql-Setup.

Ich habe schon viele Systeme gebaut, hatte aber noch nie ein Szenario, in dem ich wirklich Entscheidungen über die potenzielle Skalierbarkeit dieser Größe treffen musste. Ich habe Dutzende von Fragen zum Aufbau eines Systems dieser Größenordnung, aber für diese spezielle Frage versuche ich zu entscheiden, was als Datentyp verwendet werden soll.

Hier ist die 100-Fuß-Ansicht:

Wir haben eine Tabelle, die (unter anderem) eine hat Bezeichnung aufstellen. Wir haben uns entschieden, es zu begrenzen 255 Zeichen. Es wird durchsuchbar sein (dh: zeige mir alle Einträge mit Beschreibung, die …). Problem: Diese Tabelle ist wahrscheinlich zu haben Millionen und Abermillionen von Einträgen irgendwann (oder so denken wir).

Ich habe die Strategie für die Suche noch nicht herausgefunden (der MySql LIKE-Operator ist wahrscheinlich langsam und / oder ein Schwein, das ich für so große # Datensätze vermute), aber das ist eine andere SO-Frage. Für diese Frage frage ich mich was die Vor- und Nachteile sind, dieses Feld als tinytext, varchar und char zu erstellen.

Ich bin nicht ein Datenbankexperte, daher ist jeder Kommentar hilfreich. Vielen Dank –

Verwenden ein CHAR.

BLOB‘s und TEXT‘s werden außerhalb der Zeile gespeichert, daher wird es eine Zugriffsstrafe geben, um sie zu lesen.
VARCHAR‘s haben eine variable Länge, was Speicherplatz spart, indem eine kleine Zugriffsstrafe eingeführt werden könnte (da die Zeilen nicht alle eine feste Länge haben).

Wenn Sie Ihren Index jedoch richtig erstellen, auch nicht VARCHAR oder CHAR können vollständig im Index gespeichert werden, was den Zugriff erheblich beschleunigt.

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 meiner Erfahrung nach die MySQL regex Betreiber ist viel schneller als LIKE für einfache Abfragen (z. SELECT ID WHERE SOME_COLUMN REGEX 'search.*') und offensichtlich vielseitiger.

Ich glaube, dass Sie mit varchar eine variable Länge in der tatsächlichen Datenbank auf den niedrigen Ebenen gespeichert haben, was bedeutet, dass weniger Speicherplatz benötigt wird, da das Textfeld seine feste Länge hat, selbst wenn eine Zeile nicht alles davon verwendet. Die Zeichenfolge mit fester Länge sollte schneller abzufragen sein.

Bearbeiten: Ich habe gerade nachgesehen, Texttypen werden auch mit variabler Länge gespeichert. Am besten ist es, es mit etwas wie mysqlslap zu benchmarken

In Bezug auf Ihre andere 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 stattdessen suchen. wird viel schneller sein als die Verwendung von %like%.

In Ihrer Situation sind alle drei Typen schlecht, wenn Sie sie verwenden LIKE (a LIKE '%string%' verwendet keinen für diese Spalte erstellten Index, unabhängig von seinem Typ). Alles andere ist nur Lärm.

Mir ist kein großer Unterschied zwischen bekannt TINYTEXT und VARCHAR bis zu 255 Zeichen und CHAR ist einfach nicht für Zeichenfolgen mit variabler Länge gedacht.

Also mein Vorschlag: abholen VARCHAR oder TINYTEXT (Ich persönlich würde mich für VARCHAR entscheiden) und den Inhalt dieser Spalte mit einer Volltextsuchmaschine wie Lucene, Sphinx oder einer anderen, die die Arbeit für Sie erledigt, indizieren. Vergiss es einfach LIKE (auch wenn das bedeutet, dass Sie die Index-Engine für die Volltextsuche aus welchen Gründen auch immer selbst erstellen müssen, dh Sie benötigen Unterstützung für eine Reihe von Funktionen, die keine Engine da draußen erfüllen kann).

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

Anstatt mit zu suchen LIKE Verwenden Sie eine spezialisierte Lösung wie Lucene, Sphinx oder Solr. Ich weiß nicht mehr welche, aber mindestens einer von ihnen kann einfach für Echtzeit- oder Fast-Echtzeit-Indizierung konfiguriert werden.

BEARBEITEN

Mein Vorschlag, Text in einer anderen Tabelle zu speichern, reduziert die für die Haupttabelle erforderliche E / A, aber wenn Daten eingefügt werden, muss ein zusätzlicher Index beibehalten und Join-Overhead in Auswahlen hinzugefügt werden. Dies ist also nur gültig, wenn Sie Ihre Tabelle verwenden, um einige Beschreibungen gleichzeitig zu lesen und andere Daten aus der Tabelle werden häufiger verwendet.