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

Sie sagen, Sie haben einen Index, die Erklärung sagt etwas anderes. Allerdings, wenn Sie wirklich tun, ist dies, wie Sie fortfahren:

Wenn Sie einen Index für die Spalte haben und MySQL beschließt, ihn nicht zu benutzen, kann das daran liegen, dass:

1. Es gibt einen anderen 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 die normale Abfragemethode über den Wert von mehr als einer Spalte erfolgt.
2. MySQL entscheidet, dass es zu viele übereinstimmende Zeilen gibt, und denkt, dass ein Tablescan wahrscheinlich schneller ist. Wenn das nicht der Fall ist, wird manchmal ein ANALYZE TABLE hilft.
3. Bei komplexeren Abfragen entscheidet es sich aufgrund von extrem intelligentem Voodoo im Abfrageplan, der aus irgendeinem Grund nicht zu Ihren aktuellen Anforderungen passt, gegen die Verwendung.

Im Fall von (2) oder (3) können Sie MySQL dazu bringen, den Index zu benutzen, indem Sie index hint sytax Wenn Sie dies jedoch tun, sollten Sie einige Tests durchführen, um festzustellen, ob die Verwendung des Indexes in der von Ihnen angegebenen Form tatsächlich die Leistung verbessert.

Answer 2

Eine bessere Möglichkeit ist es, die Beschränkungen direkt während der CREATE TABLE-Abfrage hinzuzufügen (vorausgesetzt, Sie haben die Informationen über die Tabellen)

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT NOT NULL,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
    REFERENCES categories(categoryId)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=INNODB;

Answer 3

Utiliser phpmyadmin ein großartiges Werkzeug für die Verwaltung von MySQL, einschließlich Indizierung