Typische Fälle von MySQL-Indexfehlern

Typische Fälle

Es gibt zwei Tabellen mit folgender Struktur:

CREATE TABLE `student_info` (
  `id` int(11) NICHT NULL,
  `name` varchar(10) DEFAULT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_name` (`Name`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8mb4

CREATE TABLE `student_score` (
  `id` int(11) NICHT NULL,
  `name` varchar(10) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_name` (`Name`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8

Eine davon ist die Infotabelle, die andere die Punktetabelle. Die Punktetabelle hat ein Punktefeld mehr als die Infotabelle.

Daten einfügen:

mysql> in student_info-Werte einfügen (1, 'zhangsan'), (2, 'lisi'), (3, 'wangwu'), (4, 'zhaoliu');
Abfrage OK, 4 Zeilen betroffen (0,01 Sek.)
Datensätze: 4 Duplikate: 0 Warnungen: 0

mysql> in student_score-Werte einfügen (1, 'zhangsan', 60), (2, 'lisi', 70), (3, 'wangwu', 80), (4, 'zhaoliu', 90);
Abfrage OK, 4 Zeilen betroffen (0,01 Sek.)
Datensätze: 4 Duplikate: 0 Warnungen: 0

mysql> wähle * aus student_info;
+----+----------+
| Ich würde | Name |
+----+----------+
| 2 | lisi |
| 3 | wangwu |
| 1 | Zhangsan |
| 4 | meizun |
+----+----------+
4 Zeilen im Satz (0,00 Sek.)

mysql> wähle * aus student_score;
+----+----------+-------+
| ID | Name | Punktzahl |
+----+----------+-------+
| 1 | zhangsan | 60 |
| 2 | lisi | 70 |
| 3 | wangwu | 80 |
| 4 | zhaoliu | 90 |
+----+----------+-------+
4 Zeilen im Satz (0,00 Sek.)

Wenn wir die folgende Anweisung ausführen:

mysql> erkläre select B.*
        aus
        Studenteninfo A, Studentennote B
        wobei A.name=B.name und A.id=1;
+----+----------+----------+---------+-----------+------------------+---------+---------+-----------+----------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+-----------+------------------+---------+---------+-----------+----------+----------+-------------+
| 1 | SIMPLE | A | NULL | const | PRIMARY,idx_name | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | B | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100,00 | Verwenden von „where“ |
+----+----------+----------+---------+-----------+------------------+---------+---------+-----------+----------+----------+-------------+
2 Zeilen im Satz, 1 Warnung (0,00 Sek.)

Warum gibt es einen Index für B.name, aber wenn Tabelle B zum zweiten Mal im Ausführungsplan ausgewählt wird, wird der Index nicht verwendet und stattdessen ein vollständiger Tabellenscan durchgeführt? ? ?

Analyse:

Dieses SQL führt drei Schritte aus:

1. Filtern Sie zunächst die Datensätze mit A.id=1, verwenden Sie den Primärschlüsselindex und scannen Sie nur eine Zeile von LA

2. Suchen Sie den Wert des Namens „zhangsan“ aus der Zeile LA.

3. Suchen Sie in Tabelle B nach dem Wert von LA.name, finden Sie den gleichen Wert zhangsan und geben Sie ihn zurück.

Der dritte Schritt lässt sich dabei wie folgt vereinfachen:

Wählen Sie * aus student_score, wobei Name=$LA.name

Hier ist LA der Inhalt in Tabelle A Info, der Zeichensatz der Tabelle Info ist utf8mb4 und der Zeichensatz der Tabelle B Score ist utf8.

Also

Bei der Ausführung entspricht dies dem Vergleich eines linken Werts vom Typ utf8 mit einem rechten Wert vom Typ utf8mb4. Da utf8mb4 den Typ utf8 vollständig enthält (lange Bytes enthalten kurze Bytes), konvertiert MySQL utf8 in utf8mb4 (keine umgekehrte Konvertierung, hauptsächlich um Datenkürzungen zu verhindern).

Daher ist es gleichbedeutend mit der Ausführung von:

Wählen Sie * aus student_score, wobei CONVERT(name USING utf8mb4)=$LA.name

Wie wir wissen, wird der Index ungültig, sobald in einem Indexfeld eine implizite Typkonvertierung verwendet wird, und der MySQL-Optimierer verwendet einen vollständigen Tabellenscan, um das SQL auszuführen.

Um dieses Problem zu lösen, gibt es zwei Möglichkeiten:

a. Ändern Sie den Zeichensatz.

b. Ändern Sie die SQL-Anweisung.

Hier ist eine Methode zum Ändern des Zeichensatzes:

mysql> Tabelle „student_score“ ändern, Namen ändern, varchar(10) Zeichensatz utf8mb4;
Abfrage OK, 4 Zeilen betroffen (0,03 Sek.)
Datensätze: 4 Duplikate: 0 Warnungen: 0

mysql> erklären Sie „select B.* from student_info A,student_score B“, wobei A.name=B.name und A.id=1;
+----+----------+----------+---------+-----------+------------------+----------+---------+---------+-------+------+------+------+------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+-----------+------------------+----------+---------+---------+-------+------+------+------+------+
| 1 | SIMPLE | A | NULL | const | PRIMARY,idx_name | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | EINFACH | B | NULL | ref | idx_name | idx_name | 43 | const | 1 | 100,00 | NULL |
+----+----------+----------+---------+-----------+------------------+----------+---------+---------+-------+------+------+------+------+
2 Zeilen im Satz, 1 Warnung (0,01 Sek.)

Sie können versuchen, die SQL-Methode selbst zu ändern.

Anhang: Häufige Indexfehlersituationen

1. Wenn Sie eine Funktion auf eine Spalte anwenden, wird der Index der Spalte nicht berücksichtigt.

2. Beim Ausführen von Operationen an einer Spalte (+, -, *, /, ! usw.) wird der Index der Spalte nicht wirksam.

3. In einigen Fällen funktioniert die LIKE-Operation für den Spaltenindex nicht.

4. In einigen Fällen funktioniert der Index der Spalte bei Verwendung umgekehrter Operationen nicht.

5. Wenn Sie OR in WHERE verwenden und eine Spalte keinen Index hat, funktionieren die Indizes der anderen Spalten nicht.

6. Implizite Konvertierung führt zur Ungültigkeitserklärung des Index. Dies sollte ernst genommen werden. Es ist auch ein häufiger Fehler bei der Entwicklung.

7. Bei der Verwendung von Aussagen wie „nicht in“ und „nicht vorhanden“.

8. Wenn die Variable eine Zeitvariable und das Feld der Tabelle eine Datumsvariable ist oder umgekehrt.

9. Wenn der B-Tree-Index null ist, schlägt dies nicht fehl. Wenn nicht null verwendet wird, schlägt dies fehl. Sowohl der Bitmap-Index null als auch der Bitmap-Index nicht null führen zu einem Fehler.

10. Der kombinierte Index ist nicht null und wird ungültig, solange die Indexspalten erstellt werden (in keiner bestimmten Reihenfolge).

Oben sind die Details eines typischen MySQL-Indexfehlers aufgeführt. Weitere Informationen zu MySQL-Indexfehlern finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!