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 laufe

SELECT * FROM table WHERE id = '1'; 

Der Code läuft gut, da das ID-Feld der primäre Index ist. In letzter Zeit für eine Entwicklung im Projekt muss ich die database nach einem anderen Feld durchsuchen. Beispielsweise

 SELECT * FROM table WHERE product_id = '1'; 

Dieses Feld wurde noch nicht indiziert, ich habe es jedoch als Index hinzugefügt, aber wenn ich versuche, die obige Abfrage auszuführen, sind die Ergebnisse sehr langsam. Eine EXPLAIN-Abfrage zeigt, dass es keinen Index für das Feld product_id gibt, wenn ich bereits einen hinzugefügt habe und die Abfrage zwischen 20 Minuten und 30 Minuten dauert, 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 könnte hilfreich sein, zu beachten, dass ich gerade einen Blick darauf geworfen habe und das ID-Feld als INT gespeichert wurde, während das PRODUCT_ID-Feld als VARCHAR gespeichert wurde. Könnte dies die Ursache des Problems sein?

 ALTER TABLE `table` ADD INDEX `product_id` (`product_id`) 

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

 ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME); 

Sie können diese Syntax verwenden, um einen Index hinzuzufügen und die Art des Indexes zu steuern (HASH oder BTREE).

 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 über Unterschiede zwischen BTREE und HASH Indizes hier lernen: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

Beachten Sie, dass mehrere Feldindizes die Abfrageleistung drastisch verbessern können. Im obigen Beispiel nehmen wir an, dass ProductID das einzige zu suchende Feld ist, aber die Abfrage ProductID = 1 AND Category = 7 lautet, dann hilft ein Index mit mehreren Spalten. Dies wird mit folgendem erreicht:

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

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

Indizes zweier Typen können hinzugefügt werden: Wenn Sie einen Primärschlüssel definieren, wird er von MySQL standardmäßig als Index verwendet.

Erläuterung

Primärschlüssel als Index

tbl_student Sie, dass Sie eine Tabelle tbl_student und student_id als Primärschlüssel verwenden möchten:

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

Die obige statement fügt einen Primärschlüssel hinzu, was 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 student_index statement erstellt einen gewöhnlichen Index mit student_index Namen student_index .

Erstellen Sie einen eindeutigen Index

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

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

Volltext-Option

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

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

Wie entferne ich Indizes?

 DROP INDEX `student_index` ON `tbl_student` 

Wie überprüft man die verfügbaren Indizes?

 SHOW INDEX FROM `tbl_student` 

Sie sagen, Sie haben einen Index, die Erklärung sagt etwas anderes. Wie auch immer, wenn Sie das wirklich tun:

Wenn Sie einen Index für die Spalte haben und MySQL sich entscheidet, ihn nicht zu verwenden, kann dies folgende Gründe haben:

  1. Es gibt einen weiteren Index in der Abfrage, den MySQL für geeigneter hält, und er kann nur einen verwenden. Die Lösung ist normalerweise ein Index, der sich über mehrere Spalten erstreckt, wenn ihre normale Methode des Abrufens nach einem Wert von mehr als einer Spalte besteht.
  2. MySQL entscheidet, dass es viele übereinstimmende Zeilen gibt und denkt, dass ein Tabellenscan wahrscheinlich schneller ist. Wenn dies nicht der Fall ist, hilft manchmal eine ANALYZE TABLE .
  3. Bei komplexeren Abfragen entscheidet es sich, es nicht auf der Grundlage eines extrem intelligenten durchdachten Voodoo im Abfrageplan zu verwenden, der aus irgendwelchen Gründen Ihren aktuellen Anforderungen nicht gerecht wird.

Im Fall von (2) oder (3) könnten Sie MySQL dazu bringen, den Index nach Indexhinweissytax zu verwenden , aber wenn Sie dies tun, sollten Sie einige Tests durchführen, um zu ermitteln, ob die Performance tatsächlich verbessert wird, wenn Sie den Index verwenden .