MySQL-Optimierung: Vertiefte Kenntnisse über Speicher-Engines und Indexoptimierung

Vergleichen Sie die Speichermethoden der beiden Speicher-Engines in der Datenbank:

MyIsam: Wenn Sie genau hinschauen, werden Sie feststellen, dass Datenbanken, die diese Engine verwenden, im Allgemeinen mindestens drei Dateien enthalten, **.frm,* *.myi, (Put-Index) **.myd (Put-Daten), suchen Sie über den Index (**.myi-Datei) die Zeile in der Datendatei, in der die Daten gespeichert sind, wodurch eine Zeilenrückgabe generiert wird. Wenn es keine Zeilenrückgabe gibt, also keine Indexabdeckung, ist die Geschwindigkeit sehr hoch

InnoDb: Es handelt sich um eine Datei, der Index und die Daten werden zusammengestellt, was einen Clustered-Index darstellt Die Datei ist groß, die Festplatte dreht sich und die Suche ist relativ gering. Dies erzeugt auch eine ausgelagerte Blockdatei

Tabelle erstellen: Sie enthält den Primärschlüsselindex und den gemeinsamen Index unter Verwendung der Myisam-Engine bzw. der Innodb-Engine

Tabelle erstellen (
id int auto_increment ,
ver int(11) default null,
content varchar(1000) not null,
intro varchar(1000) not null,
Primary Key(id) ,
key idver(id,ver)

)engine = myisam default charset = utf8;

Tabelle smth1 erstellen (
id int auto_increment ,
ver int(11) default null,
content varchar(1000) not null,
intro varchar(1000) not null,
primär key(id),
key idver(id,ver)

)engine = innodb default charset = utf8;
Die Dateiverzeichnisanalyse erfolgt wie oben:

Erstellen Sie nun zwei gespeicherte Prozeduren, um 10.000 Daten einzufügen

Erstellen Sie die Prozedur smthTest()
begin
deklarieren Sie num int default 1050 ;
while num set num := num +1;
insert into smth value (num ,num,'I am step','who am I');
end while ;

end;


create procedure smthTest1()
begin
num int default 0 deklarieren;
while num set num := num +1;
in smth1-Werte einfügen (num ,num,'Ich bin Schritt','Wer bin ich');
end while ;

end;

Profilerstellung = 1; detaillierte Informationen anzeigen

Ergebnisanalyse ausführen:

Ergebnisse der Aussageanalyse:

mysql> erklären 
 wählen Sie ID, Version, Inhalt aus Smth aus, sortiert nach ID;  
+----+-------------+-------+------+------------ ---+------+---------+------+------+--------------- -+
| id | select_type | Tisch | Typ | mögliche_schlüssel | Schlüssel  | key_len | ref  | Reihen | Extra          |
+----+------------+-------+------+---------- -----+------+---------+------+------+------------- ---+
|  1 | EINFACH      | etw  | ALLE  | NULL          | NULL | NULL    | NULL | 9946 | Filesort verwenden |
+----+-------------+-------+------+--------- ------+------+---------+------+------+------------ ----+
1 Zeile im Satz
mysql> erklären
 wählen Sie id,ver,content aus smth Reihenfolge nach id,ver;
+----+-------------+-------+- -----+---------------+------+---------+------+---- ---+----------------+
| id | select_type | Tisch | Typ | mögliche_schlüssel | Schlüssel  | key_len | ref  | Reihen | Extra          |
+----+------------+-------+------+---------- -----+------+---------+------+------+------------- ---+
|  1 | EINFACH      | etw  | ALLE  | NULL          | NULL | NULL    | NULL | 9946 | Filesort verwenden |
+----+-------------+-------+------+--------- ------+------+---------+------+------+------------ ----+
1 Zeile im Satz
mysql> erklären
 wählen Sie ID, Version, Inhalt aus smth1 aus, sortieren Sie nach ID;
+----+-------------+-------+--- ----+---------------+---------+---------+------+-- ----+-------+
| id | select_type | Tisch | Geben Sie  | ein mögliche_schlüssel | Schlüssel     | key_len | ref  | Reihen | Extra |
+----+-------------+-------+-------+--------- ------+---------+---------+------+------+-------+
|  1 | EINFACH      | smth1 | Index | NULL          | PRIMÄR | 4       | NULL | 9932 | NULL  |
+----+-------------+-------+-------+--------- ------+---------+---------+------+------+-------+
1 Zeile im Satz
MySQL> erklären, id auswählen
,ver,Inhalt aus smth1 sortieren nach id,ver;
+----+-------------+-------+- -----+---------------+------+---------+------+---- ---+----------------+
| id | select_type | Tisch | Typ | mögliche_schlüssel | Schlüssel  | key_len | ref  | Reihen | Extra          |
+----+------------+-------+------+---------- -----+------+---------+------+------+------------- ---+
|  1 | EINFACH      | smth1 | ALLE  | NULL          | NULL | NULL    | NULL | 9932 | Filesort verwenden |
+----+-------------+-------+------+--------- ------+------+---------+------+------+------------ ----+

mysql> erklären
 ID aus etw auswählen, Reihenfolge nach ID;
+----+-------------+-------+------- +---------------+---------+---------+------+------ +-------------+
| id | select_type | Tisch | Geben Sie  | ein mögliche_schlüssel | Schlüssel     | key_len | ref  | Reihen | Extra       |
+----+-------------+-------+-------+--------- ------+---------+---------+------+------+--------- ----+
|  1 | EINFACH      | etw  | Index | NULL          | PRIMÄR | 4       | NULL | 9946 | Index verwenden |
+----+-------------+-------+-------+-------- -------+---------+---------+------+------+-------- -----+
1 Zeile im Satz
mysql> erklären
 Wähle die ID aus der Reihenfolge nach ID aus, ver;
+----+-------------+-------+----- --+---------------+-------+---------+------+------ +-------------+
| id | select_type | Tisch | Geben Sie  | ein mögliche_schlüssel | Schlüssel   | key_len | ref  | Reihen | Extra       |
+----+-------------+-------+-------+--------- ------+-------+---------+------+------+----------- --+
|  1 | EINFACH      | etw  | Index | NULL          | idver | 9       | NULL | 9946 | Index verwenden |
+----+-------------+-------+-------+-------- -------+-------+---------+------+------+---------- ---+
1 Zeile im Satz
mysql> erklären
 ID aus smth1 auswählen und nach ID sortieren;
+----+-------------+-------+------- +---------------+---------+---------+------+------ +-------------+
| id | select_type | Tisch | Geben Sie  | ein mögliche_schlüssel | Schlüssel     | key_len | ref  | Reihen | Extra       |
+----+-------------+-------+-------+--------- ------+---------+---------+------+------+--------- ----+
|  1 | EINFACH      | smth1 | Index | NULL          | PRIMÄR | 4       | NULL | 9932 | Index verwenden |
+----+-------------+-------+-------+-------- -------+---------+---------+------+------+-------- -----+
1 Zeile im Satz
mysql> erklären
 ID aus smth1 auswählen, Reihenfolge nach ID,ver;
+----+-------------+-------+----- --+---------------+-------+---------+------+------ +-------------+
| id | select_type | Tisch | Geben Sie  | ein mögliche_schlüssel | Schlüssel   | key_len | ref  | Reihen | Extra       |
+----+-------------+-------+-------+--------- ------+-------+---------+------+------+----------- --+
|  1 | EINFACH      | smth1 | Index | NULL          | idver | 9       | NULL | 9932 | Index verwenden |
+----+-------------+-------+-------+-------- -------+-------+---------+------+------+---------- ---+
1 Reihe im Satz