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

Question

Ich habe eine sehr große MySQL-Tabelle mit etwa 150.000 Datenzeilen. Wenn ich derzeit versuche, eine

SELECT * FROM table WHERE id = '1';

läuft der Code problemlos, da das Feld ID der Primärindex ist. Für eine neue Entwicklung im Projekt muss ich die Datenbank jedoch nach einem anderen Feld durchsuchen. Zum Beispiel:

SELECT * FROM table WHERE product_id = '1';

Dieses Feld war vorher nicht indiziert; ich habe jedoch einen hinzugefügt, so dass mysql das Feld jetzt indiziert, aber wenn ich versuche, die obige Abfrage auszuführen, läuft sie sehr langsam. Eine EXPLAIN-Abfrage zeigt, dass es keinen Index für das Feld "product_id" gibt, obwohl ich bereits einen hinzugefügt habe. Infolgedessen benötigt die Abfrage zwischen 20 und 30 Minuten, um eine einzige 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, darauf hinzuweisen, dass ich gerade einen Blick darauf geworfen habe und das Feld ID als INT gespeichert wird, während das Feld PRODUCT_ID als VARCHAR gespeichert wird. Könnte dies die Ursache für das Problem sein?

Answer 1

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

Niemals vergleichen integer a strings in MySQL. Wenn id es int , entfernen Sie die Anführungszeichen.

Answer 2

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

Answer 3

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

create index your_index_name on your_table_name(your_column_name) using HASH;

ou

create index your_index_name on your_table_name(your_column_name) using BTREE;

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

Answer 4

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

Erläuterung

Primärschlüssel als Index

Stellen Sie sich vor, Sie haben eine tbl_student Tabelle und Sie wollen 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, was bedeutet, dass die indizierten Werte eindeutig sein müssen und nicht NULL sein dürfen.

Indexname angeben

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

Die obige Anweisung erstellt einen gewöhnlichen Index mit student_index Name.

Einzigartigen Index erstellen

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

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

Volltext-Option

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

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

Wie entfernt man Indizes?

DROP INDEX `student_index` ON `tbl_student`

Wie prüft man verfügbare Indizes?

SHOW INDEX FROM `tbl_student`

Answer 5

Es ist erwähnenswert, dass mehrere Feldindizes die Leistung Ihrer Abfrage drastisch verbessern können. Im obigen Beispiel gehen wir davon aus, dass ProductID das einzige Feld ist, das abgefragt werden soll. Sollte die Abfrage jedoch ProductID = 1 AND Category = 7 lauten, hilft ein Index mit mehreren Spalten. Dies wird mit dem Folgenden erreicht:

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) lauten und nicht umgekehrt.