wake-up-neo.com

Wie füge ich Indizes zu MySQL-Tabellen hinzu?

Ich habe eine sehr große MySQL-Tabelle mit ungefähr 150.000 Datenzeilen. Derzeit, wenn ich versuche und renne

SELECT * FROM table WHERE id = '1';

der Code funktioniert einwandfrei, da das ID-Feld der primäre Index ist. Für eine kürzliche Entwicklung im Projekt muss ich die Datenbank jedoch nach einem anderen Feld durchsuchen. Zum Beispiel:

SELECT * FROM table WHERE product_id = '1';

Dieses Feld wurde zuvor nicht indiziert. Ich habe jedoch eine hinzugefügt, daher indiziert mysql das Feld jetzt, aber wenn ich versuche, die obige Abfrage auszuführen, wird sie sehr langsam ausgeführt. Eine EXPLAIN-Abfrage zeigt an, dass es keinen Index für das Feld product_id gibt, wenn ich bereits einen hinzugefügt habe. Infolgedessen benötigt die Abfrage 20 bis 30 Minuten, um eine einzelne Zeile zurückzugeben.

Meine vollständigen EXPLAIN-Ergebnisse sind:

| id | select_type | table | type | possible_keys| key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+
|  1 | SIMPLE      | table | ALL  | NULL         | NULL | NULL    | NULL |157211 | Using where |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+

Es kann hilfreich sein zu beachten, dass ich gerade einen Blick darauf geworfen habe und das ID-Feld als INT gespeichert ist, während das PRODUCT_ID-Feld als VARCHAR gespeichert ist. Könnte dies die Ursache des Problems sein?

mysqloptimizationindexingrow
383
Michael
ALTER TABLE `table` ADD INDEX `product_id` (`product_id`)

Vergleichen Sie niemals integer mit strings in MySQL. Wenn idint ist, entfernen Sie die Anführungszeichen.

568
zerkms
ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);
144
pabloferraz

Mit dieser Syntax können Sie einen Index hinzufügen und die Art des Index (HASH oder BTREE) steuern.

create index your_index_name on your_table_name(your_column_name) using HASH;
or
create index your_index_name on your_table_name(your_column_name) using BTREE;

Sie können sich hier über die Unterschiede zwischen BTREE- und HASH-Indizes informieren: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

73
Hieu Vo

Es ist erwähnenswert, dass mehrere Feldindizes die Abfrageleistung drastisch verbessern können. Im obigen Beispiel wird davon ausgegangen, dass ProductID das einzige Feld ist, in dem nachgeschlagen werden muss. Wenn jedoch die Abfrage ProductID = 1 AND Category = 7 lautet, hilft ein Index mit mehreren Spalten. Dies wird erreicht mit:

ALTER TABLE `table` ADD INDEX `index_name` (`col1`,`col2`)

Außerdem sollte der Index mit der Reihenfolge der Abfragefelder übereinstimmen. In meinem erweiterten Beispiel sollte der Index (ProductID, Category) sein und nicht umgekehrt.

55
Antony

Es können zwei Arten von Indizes hinzugefügt werden: Wenn Sie einen Primärschlüssel definieren, nimmt MySQL diesen standardmäßig als Index.

Erklärung

Primärschlüssel als Index

Angenommen, Sie haben eine tbl_student -Tabelle und möchten student_id als Primärschlüssel:

ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`)

Die obige Anweisung fügt einen Primärschlüssel hinzu. Dies bedeutet, dass indizierte Werte eindeutig sein müssen und nicht NULL sein dürfen.

Geben Sie den Indexnamen an

ALTER TABLE `tbl_student` ADD INDEX student_index (`student_id`)

Die obige Anweisung erstellt einen normalen Index mit dem Namen student_index.

eindeutigen Index erstellen

ALTER TABLE `tbl_student` ADD UNIQUE student_unique_index (`student_id`)

Hier ist student_unique_index der Indexname, der student_id zugewiesen wird, und erstellt einen Index, für den Werte eindeutig sein müssen (hier kann null akzeptiert werden).

Volltextoption

ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`)

Die obige Anweisung erstellt den Volltextindexnamen mit student_fulltext_index, für den Sie MyISAM Mysql Engine benötigen.

So entfernen Sie Indizes?

DROP INDEX `student_index` ON `tbl_student`

Wie überprüfe ich die verfügbaren Indizes?

SHOW INDEX FROM `tbl_student`
47
Jazzzzzz

Sie sagen, Sie haben einen Index, die Erklärung sagt etwas anderes. Wenn Sie dies jedoch wirklich tun, gehen Sie wie folgt vor:

Wenn Sie einen Index für die Spalte haben und MySQL beschließt, diesen nicht zu verwenden, kann dies folgende Ursachen haben:

  1. Es gibt einen anderen Index in der Abfrage, den MySQL für angemessener hält und der nur einen verwenden kann. Die Lösung ist normalerweise ein Index, der sich über mehrere Spalten erstreckt, wenn die normale Abrufmethode der Wert von mehr als einer Spalte ist.
  2. MySQL hat festgestellt, dass zu viele übereinstimmende Zeilen vorhanden sind, und denkt, dass ein Tablescan wahrscheinlich schneller ist. Wenn das nicht der Fall ist, hilft manchmal ein ANALYZE TABLE.
  3. Bei komplexeren Abfragen wird entschieden, es nicht auf der Grundlage eines äußerst intelligenten Voodoos im Abfrageplan zu verwenden, das aus irgendeinem Grund einfach nicht Ihren aktuellen Anforderungen entspricht.

Im Fall von (2) oder (3) könnten Sie MySQL überreden, den Index mit Indexhinweis sytax zu verwenden. Führen Sie in diesem Fall jedoch einige Tests durch, um festzustellen, ob die Leistung tatsächlich verbessert wird Verwenden Sie den Index so, wie Sie ihn andeuten.

16
Wrikken