Dieser Artikel enthält den Ausführungsprozess der Where-Bedingungsabfrage von MySQL, eine Bereichsabfrage, um den Abgleich des gemeinsamen Indexes zu verhindern, eine Analyse des Tabellenrückgabevorgangs, häufige Indexfehlerszenarien, zusätzliche Analysen und anderes Wissen. [Verwandte Empfehlungen:
Beim Replizieren der SQL-Anweisung wurde festgestellt, dass die Abfrage nicht den Index, sondern den gesamten Index durchlief Tabellenabfrage. Finden Sie den Grund für den Indexfehler heraus.
order_recipient_extend_tab-Tabelle enthält 60 Millionen Daten. Zu den Abfragefeldern für langsame Abfragen gehören Startdatum, Stations-ID und Status. Der Index, der tatsächlich fehlschlägt, ist:
gemeinsamer Index | Feld 1 | Feld 2 | Feld 3 |
| idx_date_station_driver
| start_date | station_id | driver_id |
Where-Bedingungsabfrage-Ausführungsprozess
Verstehen Sie, wie MySQL Where-Bedingungsabfragen ausführt, und Sie können schnellere und klarere Einblicke in die Gründe für Indexfehler erhalten. Der Index mit hohem Übereinstimmungsgrad in dieser langsamen Abfrage ist idx_date_station_driver Analysieren Sie den Ausführungsprozess der Where-Bedingungsabfrage in dieser langsamen Abfrage.
MySQL, wo Bedingungsextraktionsregeln in drei Hauptkategorien zusammengefasst werden können: Indexschlüssel (Erster Schlüssel und Letzter Schlüssel), Indexfilter, Tabellenfilter.
Indexschlüssel
Der Indexschlüssel wird verwendet, um den Umfang dieser SQL-Abfrage im Indexbaum zu bestimmen. Ein Bereich umfasst den Anfang und das Ende. Index First Key wird verwendet, um den Startbereich der Indexabfrage zu lokalisieren, und Index Last Key wird verwendet, um den Endbereich der Indexabfrage zu lokalisieren.
-
Index Erster Schlüssel
Extraktionsregeln: Überprüfen Sie ausgehend vom ersten Feld des Index, ob das Feld in der Where-Bedingung vorhanden ist. Wenn es vorhanden ist und die Bedingung =, >= ist, fügen Sie die entsprechende Bedingung zum Index hinzu Erster Schlüssel: Lesen Sie das nächste Feld des Index weiter. Wenn es vorhanden ist und die Bedingung > ist, fügen Sie die entsprechende Bedingung zum ersten Index-Schlüssel hinzu und beenden Sie dann die Extraktion des ersten Index-Schlüssels die Extraktion des Index First Key-Extrakts.
-
Index Last Key
ist genau das Gegenteil von Index First Key: Überprüfen Sie ausgehend vom ersten Feld des Index, ob er in der Where-Bedingung vorhanden ist und die Bedingung =,
Gemäß den Indexschlüssel-Extraktionsregeln lautet der in dieser langsamen Abfrage extrahierte letzte Indexschlüssel: start_date>'1628442000' und der letzte Indexschlüssel lautet: start_date
Index First Key wird nur zum Auffinden des Startbereichs des Index verwendet. Verwenden Sie die Bedingung „Index First Key“, beginnend mit dem Wurzelknoten des Index-B+-Baums, und verwenden Sie die binäre Suchmethode, um schnell zur richtigen Blattknotenposition zu indizieren . Während des Where-Abfrageprozesses wird der Index First Key nur einmal beurteilt.
Index Last Key wird verwendet, um den Endbereich des Index zu lokalisieren. Daher muss für jeden nach dem Startbereich gelesenen Indexdatensatz festgestellt werden, ob er den Bereich des Index Last Key überschreitet Abfrage endet.
Indexfilter
Im durch den Indexschlüssel bestimmten Indexbereich erfüllen nicht alle Indexdatensätze die Abfragebedingungen. Beispielsweise erfüllen in den Bereichen „Index Last Key“ und „Index Last Key“ nicht alle Indexdatensätze die Stations-ID = „1809“. Zu diesem Zeitpunkt müssen Sie den Indexfilter verwenden.
Indexfilter, auch bekannt als Index-Pushdown, wird verwendet, um Datensätze im Indexabfragebereich zu filtern, die die Abfragebedingungen nicht erfüllen. Für jeden Datensatz im Indexbereich muss er mit dem Indexfilter verglichen werden. Wenn er den Indexfilter nicht erfüllt, wird er direkt verworfen und mit dem Lesen des nächsten Datensatzes im Index fortgefahren.
Indexfilter-Extraktionsregeln: Beginnen Sie mit dem ersten Feld des Index und prüfen Sie, ob es in der Where-Bedingung vorhanden ist. Wenn es vorhanden ist und die Bedingung nur = ist, überspringen Sie das erste Feld und prüfen Sie weiterhin das nächste Feld des Index. Die nächsten Indexspalten übernehmen dieselben Extraktionsregeln (Erklärung: Felder mit der Bedingung = wurden im Indexschlüssel herausgefiltert, wenn sie vorhanden sind und die Bedingungen >=, >,
Gemäß den Extraktionsregeln des Indexfilters lautet der in dieser langsamen Abfrage extrahierte Indexfilter: station_id='1809'. Im Indexabfragebereich, der durch den Indexschlüssel bestimmt wird, muss station_id = '1809' beim Durchlaufen der Indexdatensätze verglichen werden. Wenn diese Bedingung nicht erfüllt ist, geht sie direkt verloren und der nächste Datensatz im Index wird weiter gelesen .
Tabellenfilter
Tabellenfilter wird verwendet, um Daten herauszufiltern, die nicht vom Index gefiltert werden können. Nachdem die gesamte Datensatzzeile über den Primärschlüssel „zurück zur Tabelle“ im Sekundärindex abgefragt wurde, wird beurteilt, ob der Datensatz die Bedingungen des Tabellenfilters erfüllt. Wenn er die Bedingungen nicht erfüllt, geht er verloren und der nächste Datensatz wird weiterhin beurteilt. Die Extraktionsregeln sind sehr einfach: Alle Abfragebedingungen, die nicht zu den Indexfeldern gehören, werden im Tabellenfilter klassifiziert. Gemäß den Extraktionsregeln des Tabellenfilters lautet der Tabellenfilter in dieser Abfrage: status=‘2’.
Zusammenfassung und Ergänzung
Der Indexschlüssel wird verwendet, um den Umfang des Indexscans zu bestimmen. Der Indexfilter wird zum Filtern im Index verwendet. Der Tabellenfilter muss nach der Rückgabe der Tabelle auf dem MySQL-Server gefiltert werden.
Indexschlüssel und Indexfilter erfolgen auf der InnoDB-Speicherebene und Tabellenfilter auf der MySQL-Serverebene.
Vor MySQL5.6 gab es keinen Unterschied zwischen Indexfilter und Tabellenfilter. Alle Indexdatensätze im Bereich von Index First Key und Index Last Key wurden an die Tabelle zurückgegeben, um den vollständigen Datensatz zu lesen, und dann an den MySQL-Server zurückgegeben Ebene zum Filtern.
In MySQL 5.6 und höher ist der Indexfilter vom Tabellenfilter getrennt und wird zum Filtern auf die Speicher-Engine-Schicht von InnoDB übertragen, was den Interaktionsaufwand für die Rückgabe von Tabellen und die Rückgabe von Datensätzen an die MySQL-Serverschicht reduziert und verbessert die Ausführungseffizienz von SQL.
Analysieren Sie die Ursache des Indexfehlers
Zu diesem Zeitpunkt werden durch den Platzhalter * nicht alle Spalten erweitert, sondern die Anzahl der Zeilen direkt gezählt. Wenn Sie also nur die Anzahl der Zeilen erfassen möchten, verwenden Sie am besten count().
Analysieren Sie als Nächstes die Where-Anweisung. Gehen Sie davon aus, dass diese langsame Abfrage den sekundären Index idx_date_station_driver verwendet. Gemäß dem Ausführungsprozess der obigen Where-Bedingungsabfrage ist der erste Indexschlüssel der langsamen Abfrage start_date>'1628442000' und der letzte Indexschlüssel ist: start_dateidx_date_station_driver，按照上面where条件查询的执行过程，该慢查询的Index First Key为start_date>'1628442000'，Index Last Key为： start_date提取Index First Key后在索引B+树上定位索引起始范围就是索引匹配的过程，在索引B+树上使用二分搜索方法快速定位符合查询条件的起始叶子节点。通过上文Where条件查询执行过程，我们知道该慢查询的where条件（start_date>'1628442000' and start_date，只匹配了索引<code>idx_date_station_driver(start_date, station_id, driver_id)的第一个字段，即只匹配了idx_date_station_driver(start_date)，station_id='1809‘精确查询并没有作用到匹配索引上，而是在Index Filter即索引下推过程中发挥了作用。实际上这里是因为范围查询使联合索引停止匹配。
范围查询导致联合索引停止匹配
为什么范围查询会使联合索引停止匹配？这里涉及到最左前缀匹配原理。假设建立一个联合索引 index(a, b)，会先对a进行排序，在a相等的情况下对b进行排序，如下图所示。在该索引树上，a是全局有序的，而b则处于全局无序、局部有序状态。从全局来看，b的值为1、2、1、4、1、2，只有 b=2 查询条件无法直接使用该索引；从局部来看，当a的值确定时，b则是有序状态，a=2 && b=4Nach dem Extrahieren des Index-Erstschlüssels ist das Auffinden des Index-Startbereichs im Index-B+-Baum der Prozess des Index-Matchings Verwenden Sie die binäre Suchmethode im Index-B+-Baum, um schnell den Startblattknoten zu finden, der die Abfragebedingungen erfüllt . Durch den obigen Ausführungsprozess der Where-Bedingungsabfrage kennen wir die Where-Bedingung der langsamen Abfrage (start_date>'1628442000' und start_date , Nur das erste Feld des Index <code>idx_date_station_driver(start_date, station_id, drivers_id) wird abgeglichen, d. h. nur idx_date_station_driver(start_date) wird mit der genauen Abfrage von station_id= abgeglichen '1809' wirkt nicht auf den passenden Index, spielt aber eine Rolle im Indexfilter, also im Index-Pushdown-Prozess. Was hier tatsächlich passiert, ist, dass die
Bereichsabfrage dazu führt, dass der Union-Index nicht mehr übereinstimmt . 
Bereichsabfrage führt dazu, dass der gemeinsame Index nicht mehr übereinstimmt
Warum führt die Bereichsabfrage dazu, dass der gemeinsame Index nicht mehr übereinstimmt? Hierbei handelt es sich um das Prinzip der Präfixübereinstimmung ganz links. Unter der Annahme, dass ein gemeinsamer Index (a, b) erstellt wurde, wird a zuerst sortiert, und wenn a gleich ist, wird b sortiert, wie in der folgenden Abbildung dargestellt. In diesem Indexbaum ist a global geordnet, während sich b in einem global ungeordneten und lokal geordneten Zustand befindet. Aus globaler Sicht beträgt der Wert von b 1, 2, 1, 4, 1, 2, und nur die Abfragebedingungen b=2 können diesen Index aus lokaler Sicht nicht direkt verwenden von a wird bestimmt. Wenn b ein geordneter Zustand ist, kann a=2 && b=4 diesen Index verwenden. Daher besteht der Hauptgrund, warum die Bereichsabfrage dazu führt, dass der gemeinsame Index nicht mehr übereinstimmt, darin, dass der geordnete Zustand des nicht ersten Felds im Indexbaum von der Gleichheit des vorherigen Felds abhängt und die Bereichsabfrage den lokal geordneten Zustand von zerstört Das nächste Indexfeld führt dazu, dass der Index nicht mehr übereinstimmt.
| Die Bereichsabfrage verhindert, dass der gemeinsame Index übereinstimmt, und kann die Daten, deren station_id nicht gleich „1809“ ist, nicht herausfiltern, wenn der Index übereinstimmt, was dazu führt, dass MySQL den Index-Erstschlüssel und den Index-Letztschlüssel des Index im Scanbereich scannt vollständig durch start_timestamp_of_date bestimmt werden. Die Zeit entscheidet. Die Bereichsabfrage start_timestamp_of_date kann 73 % des Datenvolumens herausfiltern, während die präzise Abfrage station_id='1809' 99 % des Datenvolumens herausfiltern kann. |
|
Abfragebedingungen
Datenvolumen
| Anteil |
|
|
Alle Daten
| 63,67, Millionen |
100 % |
|
start_timestamp_of_date> ;'1628442000' und start_timestamp_of_date17,42 Millionen | 27,35 % |
| station_id='1809'
80.000🎜🎜0,16 %🎜🎜🎜🎜Overhead des Tabellenrückgabevorgangs
Da sich das Statusfeld nicht im Indexfeld idx_date_station_driver befindet, ist es notwendig, die Tabelle zurückzugeben, um die indexgefilterten Daten abzufragen und festzustellen, ob die Daten den Anforderungen entsprechen Abfragebedingungen auf der MySQL-Dienstschicht. idx_date_station_driver字段上，所以需要回表查询索引过滤的数据，在Mysql服务层判数据是否符合查询条件。
Mysql的优化器在执行sql语句时会先估算走匹配度高的索引的开销，如果走索引的开销比查全表还大，那么Mysql会选择全表扫描。这个结论可能反常识，在我们印象中索引就是用来提高查询效率的。这里主要涉及两个因素：
做实验分析回表操作的开销是否是索引失效的直接原因？
去除status='0'查询条件，explain查看该查询是否使用到了索引idx_date_station_driver
Der MySQL-Optimierer schätzt beim Ausführen einer SQL-Anweisung zunächst die Kosten für die Indizierung mit einem hohen Übereinstimmungsgrad. Wenn die Kosten für die Indizierung höher sind als das Durchsuchen der gesamten Tabelle, wählt MySQL einen vollständigen Tabellenscan. Diese Schlussfolgerung ist möglicherweise nicht intuitiv. Nach unserem Eindruck werden Indizes zur Verbesserung der Abfrageeffizienz verwendet. Hier spielen zwei Hauptfaktoren eine Rolle: 
Wenn die Abfragebedingungen oder die durchsuchten Felder nicht auf den Feldern des Sekundärindex liegen, wird die Tabellenrückgabeoperation ausgeführt, die lautet: Sekundärindex + Primärschlüsselindex.
Die Leistung der zufälligen Festplatten-E/A ist geringer als die der sequenziellen E/A. Tabellenrückgabeabfragen sind zufällige E/A-Vorgänge für den Primärschlüsselindex und vollständige Tabellenscans sind sequentielle E/A-Vorgänge für den Primärschlüsselindex.
Führen Sie Experimente durch und analysieren Sie, ob die Kosten für Tabellenrückgabevorgänge die direkte Ursache für Indexfehler sind?
Entfernen Sie die Abfragebedingung status='0' und erklären Sie, ob die Abfrage den Index idx_date_station_driver verwendet. Das Ergebnis ist in der folgenden Abbildung dargestellt. Der Overhead der Tabellenrückgabeoperation wird reduziert und der Index wird nicht ungültig.
Zusammenfassung
In Kombination mit der obigen Analyse wird der Grund für den Indexfehler zusammengefasst: Die Bereichsabfrage führt dazu, dass der gemeinsame Index nicht mehr übereinstimmt, und die vom Index abgeglichenen und gefilterten Daten reichen nicht aus, was dazu führt Der MySQL-Optimierer schätzt, dass die Tabellenrückgabeoperationskosten des Tabellenfilters höher sind als die der gesamten Tabellentabellenabfrage, daher wurde die vollständige Tabellenabfrage ausgewählt. Die Bereichsabfrage, die dazu führt, dass der gemeinsame Index nicht mehr übereinstimmt, ist die Ursache für den Indexfehler, und die Kosten für Tabellenrückgabevorgänge sind die direkte Ursache für den Indexfehler.
Index optimieren
Der Grund für den langsamen Abfrageindexfehler ist, dass die Bereichsabfrage dazu führt, dass der gemeinsame Index nicht mehr übereinstimmt. Sie müssen lediglich die Felder der Bereichsabfrage so anpassen, dass sie hinter den Feldern der genauen Abfrage liegen ist,
String-Index ohne einfache Anführungszeichen ist ungültig.
like beginnt mit dem Platzhalterzeichen „%abc“. Beachten Sie, dass „abc%“ indiziert werden kann.
Order by verstößt gegen das Prinzip der Übereinstimmung am weitesten links und umfasst die Sortierung von Nicht-Index-Feldern, was zu einer Dateisortierung führt.
|
group by verstößt gegen das Leftmost-Matching-Prinzip und enthält eine Nicht-Index-Feldgruppierung, die zur Generierung temporärer Tabellen führt.
|
|
Explain-Analyse
|
Die Analyse langsamer Abfragen ist untrennbar mit der MySQL-Explain-Anweisung verbunden und konzentriert sich hauptsächlich auf zwei Felder: Typ und Extra.
Typ stellt die Möglichkeit dar, auf Daten zuzugreifen, und Extra stellt die Möglichkeit dar, Daten zu filtern und zu organisieren. Zur einfachen Suche hier aufgelistet. Typ: Extra | Index | Vollständiger Scan des Indexbaums | Where verwenden | Erhält Daten von der Speicher-Engine-Schicht und verwendet Where-Abfragebedingungen, um die Daten in der MySQL-Dienstschicht zu filtern.
| Bereich | Indexbaumbereichsscan | Verwenden von where; Verwenden von Index | Indexbereichsscan. Indexscans ähneln vollständigen Tabellenscans, werden jedoch auf unterschiedlichen Ebenen durchgeführt.
| ref | Nicht-eindeutiger Index-Scan, z. B. nicht-eindeutiger Index und nicht-eindeutiges Präfix eines eindeutigen Index | Verwendung der Indexbedingung | Verwenden Sie den Index-Pushdown, um die Abfrageindexfelder vollständig zu nutzen, um Daten zu filtern Speicher-Engine-Schicht
| | eq_ref | Einzigartiger Indexscan, z. B. eindeutiger Index, Primärschlüsselindex | Verwendung von temporären | Temporäre Tabelle speichert Ergebnisse zum Sortieren und Gruppieren von Abfragen
| const | Konvertieren Sie Abfragen in Konstanten | Filesort verwenden | Dateisortierung zum Sortieren
| NULL | Kein Zugriff auf Tabellen oder Indizes erforderlich | NULL | Zurück zur Tabelle
|
Weitere programmierbezogene Kenntnisse finden Sie unter: | Einführung in die Programmierung ! ! |
Das obige ist der detaillierte Inhalt vonWas soll ich tun, wenn der MySQL-Index fehlschlägt? Kurze Analyse der Fehlergründe. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
