Detaillierte Erläuterung der MySQL-Indexauswahl und -Optimierung

// Nehmen wir eine Datenseite von 16 KB, eine Datenzeile von 1 KB, einen Indexzeiger von 6 Bytes und ein Indexfeld vom Typ Bigint (8 Bytes) an.

// Anzahl der Indizes K = 16*1024/(8+6) = 1170

// Anzahl der Datensätze in einem einzelnen Blattknoten N = 16/1 = 16

// Schicht 3 B+ Datensatznummer V = K*K*N = 21902400

-- Zeigen Sie die aktuelle Indexbasis an mysql> show index from test;
+-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+
| Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Unterteil | Gepackt | Null | Indextyp | Kommentar | Indexkommentar |
+-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+
| Test | 0 | PRIMARY | 1 | ID | A | 100256 | NULL | NULL | | BTREE | | |
| Test | 1 | index_a | 1 | a | A | 98199 | NULL | NULL | JA | BTREE | | |
+-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+

mysql> Variablen wie „%innodb_stats_persistent%“ anzeigen;
+--------------------------------------+-------------+
| Variablenname | Wert |
+--------------------------------------+-------------+
-- Ob die Aktualisierung der Statistiken automatisch ausgelöst werden soll. Wenn die geänderten Daten 10 % überschreiten, werden die Statistiken neu berechnet | innodb_stats_auto_recalc | EIN |
-- Steuert, ob zum Löschen markierte Datensätze bei der Neuberechnung von Statistiken berücksichtigt werden | innodb_stats_include_delete_marked | OFF |
-- Statistische Methode für Nullwerte. Wenn die Variable auf nulls_equal gesetzt ist, werden alle NULL-Werte als gleich betrachtet | innodb_stats_method | nulls_equal | 
-- Ob die Aktualisierung der Statistiken beim Bearbeiten von Metadaten ausgelöst werden soll | innodb_stats_on_metadata | OFF |
--Ob Statistiken dauerhaft gespeichert werden | innodb_stats_persistent | EIN |
-- innodb_stats_persistent=on, Anzahl der Beispielseiten für die dauerhafte Statistikstichprobenerhebung | innodb_stats_persistent_sample_pages | 20 |
-- Veraltet, ersetzt durch innodb_stats_transient_sample_pages | innodb_stats_sample_pages | 8 |
-- Vorübergehende Beispielseitennummer | innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+

-- Indexinformationen neu berechnen mysql> analyze table t;

-- Erstellen Sie eine Tabelle mysql> CREATE TABLE `t` (
`id` int(11) NICHT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
Primärschlüssel (`id`),
SCHLÜSSEL `a` (`a`),
SCHLÜSSEL `b` (`b`)
)ENGINE=InnoDB;

- Definieren Sie das Testdatenspeicherverfahren mysql> Trennzeichen;
PROZEDUR ERSTELLEN idata ()
BEGINNEN

Erklären Sie, dass ich INT;
Setze i = 1;
WÄHREND (i <= 100000) TUN
 EINFÜGEN IN t
WERTE
 (ich, ich, ich);
Setze i = i + 1;
ENDE
WÄHREND ;
ENDE;
Trennzeichen ;

-- Führen Sie die gespeicherte Prozedur aus und fügen Sie Testdaten ein mysql> CALL idata ();

- Zeigen Sie den Ausführungsplan an, indem Sie den Index auf Feld a verwenden. mysql> erklären Sie „select * from t“, wobei a zwischen 10000 und 20000 liegt;
+----+--------------+-------+-----------+---------------+-----+-----------+------+----------+-----------+---------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+-------+-----------+---------------+-----+-----------+------+----------+-----------+---------------------------+
| 1 | SIMPLE | t | Bereich | a | a | 5 | NULL | 10000 | Indexbedingung verwenden |
+----+--------------+-------+-----------+---------------+-----+-----------+------+----------+-----------+---------------------------+

-- Da Feld b sortiert werden muss, wird es geordnet, obwohl Index b mehr Zeilen scannen muss. Unter Berücksichtigung der Anzahl der zu scannenden Zeilen und der Sortierung wählt der Optimierer Index b, da er glaubt, dass die Kosten geringer sind. mysql> erklärt select * from t where (a zwischen 1 und 1000) und (b zwischen 50000 und 100000) order by b limit 1;
+----+--------------+-------+-----------+---------------+-----+------+------+------+------+------+------------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+-------+-----------+---------------+-----+------+------+------+------+------+------------------------------------+
| 1 | SIMPLE | t | Bereich | a,b | b | 5 | NULL | 50128 | Indexbedingung verwenden; Where verwenden |
+----+--------------+-------+-----------+---------------+-----+------+------+------+------+------+------------------------------------+

-- Lösung 1: Verwenden Sie „Force Index“, um Index a zu erzwingen und die falsche Auswahl des Optimierers zu korrigieren. Dies wird nicht empfohlen (nicht universell, und die Anweisung muss geändert werden, wenn der Indexname geändert wird).
mysql> erklären Sie „select * from t force index(a), wobei (a zwischen 1 und 1000) und (b zwischen 50000 und 100000) sortieren nach b, Grenze 1;
+----+-----------+----------+-----------+---------------+-----+------+------+------+----------------------------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+----------+-----------+---------------+-----+------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | Bereich | a | a | 5 | NULL | 999 | Indexbedingung verwenden; Where verwenden; Filesort verwenden |
+----+-----------+----------+-----------+---------------+-----+------+------+------+----------------------------------------------------+

-- Lösung 2: Weisen Sie MySQL an, den gewünschten Index zu verwenden und nach b, a zu sortieren. Der Optimierer muss die Kosten für die Sortierung von a berücksichtigen. mysql> erläutern Sie „select * from t“, wobei (a zwischen 1 und 1000) und (b zwischen 50000 und 100000) nach b, a, Limit 1 sortiert ist;
+----+-----------+----------+-----------+---------------+-----+------+------+------+----------------------------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+----------+-----------+---------------+-----+------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | Bereich | a,b | a | 5 | NULL | 999 | Indexbedingung verwenden; Where verwenden; Filesort verwenden |
+----+-----------+----------+-----------+---------------+-----+------+------+------+----------------------------------------------------+

-- Lösung 3: In einigen Szenarien können wir einen geeigneteren Index erstellen, um ihn dem Optimierer zur Auswahl bereitzustellen, oder den falsch verwendeten Index löschen ALTER TABLE `t`
DROP INDEX `a`,
DROP INDEX `b`,
INDEX HINZUFÜGEN `ab` (`a`,`b`);

- Selektiver Index des Felds xxx in Tabelle t: select count(distinct xxx)/count(id) from t;

-- Sie müssen nur den Wert der ID überprüfen, und der Wert der ID befindet sich bereits im k-Indexbaum, sodass Sie das Abfrageergebnis direkt bereitstellen können, ohne zur Tabelle zurückkehren zu müssen. Wählen Sie ID aus T aus, wobei k zwischen 3 und 5 liegt.

-- Feld V hinzufügen. V muss für jede Abfrage zurückgegeben werden. Sie können erwägen, k und v zu einem gemeinsamen Index zusammenzufassen. select ID,V from T, wobei k zwischen 3 und 5 liegt.

-- Erstellen Sie einen gemeinsamen Index für die drei Spalten „ID“, „Name“ und „Alter“. -- Erfüllen Sie das Prinzip des ganz linken Präfixes, und sowohl Name als auch Alter werden indiziert. Wählen Sie „* aus T“, wobei „Name“ = „xxx“ und „Alter“ = „12“ lautet.

-- Mysql optimiert und passt die Reihenfolge von Name und Alter automatisch an. Sowohl Name als auch Alter werden indiziert. select * from T where age=12 and name='xxx'

-- Wenn der Name das Prinzip des am weitesten links stehenden Präfixes erfüllt, wird der Index verwendet. MySQL 5.6 führt eine Pushdown-Optimierung für Indexbedingungen ein, d. h. die Datensätze, die age=12 nicht erfüllen, werden zuerst im Index herausgefiltert und dann an die Tabelle zurückgegeben. select * from T where name like 'xxx%' and age=12

-- Erfüllt nicht das Prinzip des ganz linken Präfixes, es wird kein Index verwendet. Wählen Sie * aus T aus, wobei der Name beispielsweise „%xxx%“ und das Alter 12 ist.

-- Erfüllt das Prinzip des ganz linken Präfixes. Der Name verwendet den Index select * from T, wobei name='xxx' ist.

-- Erfüllt nicht das Prinzip des ganz linken Präfixes und verwendet nicht den Index „select * from T where age=12“

mysql> Tabelle SUser erstellen(
ID Bigint vorzeichenloser Primärschlüssel,
Name varchar(64),  
E-Mail varchar(64),
...
)Engine=innodb;

-- Das folgende Abfrageszenariomysql> select name from SUser where email='xxx';

-- Lösung 1: Volltextindex. Die Anzahl der Tabellenrückgaben wird durch die Datenmenge bestimmt, die die Bedingungen erfüllt. mysql> alter table SUser add index index1(email);

-- Lösung 2: Präfixindex. Die Anzahl der Tabellenrückgaben wird durch das Präfixübereinstimmungsergebnis bestimmt. mysql> alter table SUser add index index2(email(6));

-- Stellen Sie ein akzeptables Unterscheidungsverlustverhältnis vor und wählen Sie die kleinste Präfixlänge, die die Bedingungen erfüllt. Wählen Sie count(distinct left(email,n))/count(distinct email) von SUser.

-- String-Umkehrabfrage während der Abfrage mysql> select field_list from t where id_card = reverse('input_id_card_string');

- Erstellen Sie ein Integer-Feld zum Speichern des ID-Karten-Verifizierungscodes und erstellen Sie einen Index für dieses Feld mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

-- Verwenden Sie das Hash-Feld, um nach Index zu suchen, und verwenden Sie dann die ursprüngliche Feldgenauigkeit, um MySQL zu filtern. Wählen Sie „field_list“ aus t aus, wobei id_card_crc=crc32('input_id_card_string') und id_card='input_id_card_string'

-- Wenn Sie einen Präfixindex verwenden, benötigen Sie keinen überdeckenden Index, um die Abfrageleistung zu optimieren. select id,email from SUser where email='xxx';