MySQL-Abfrage redundanter Indizes und ungenutzter Indexoperationen

MySQL 5.7 und höhere Versionen ermöglichen die direkte Abfrage redundanter Indizes, doppelter Indizes und Ansichten, die nicht verwendete Indizes haben. Sie können direkt abfragen.

Abfragen redundanter Indizes und doppelter Indizes

wählen Sie * sys.from schema_redundant_indexes;

Abfragen nicht verwendeter Indizes

Wählen Sie * aus sys.schema_unused_indexes;

Wenn Sie es in den Versionen 5.6 und 5.5 verwenden möchten, konvertieren Sie die Ansicht einfach in eine SQL-Anweisungsabfrage

Abfragen redundanter Indizes und doppelter Indizes

select a.`table_schema`,a.`table_name`,a.`index_name`,a.`index_columns`,b.`index_name`,b.`index_columns`,concat('ALTER TABLE `',a.`table_schema`,'`.`',a.`table_name`,'` DROP INDEX `',a.`index_name`,'`') from ((select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`) a join (select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`) b on(((a.`table_schema` = b.`table_schema`) and (a.`table_name` = b.`table_name`)))) where ((a.`index_name` <> b.`index_name`) and (((a.`index_columns` = b.`index_columns`) and ((a.`non_unique` > b.`non_unique`) or ((a.`non_unique` = b.`non_unique`) and (if((a.`index_name` = 'PRIMARY'),'',a.`index_name`) > if((b.`index_name` = 'PRIMARY'),'',b.`index_name`))))) or ((locate(concat(a.`index_columns`,','),b.`index_columns`) = 1) and (a.`non_unique` = 1)) or ((locate(concat(b.`index_columns`,','),a.`index_columns`) = 1) and (b.`non_unique` = 0))));

Abfragen nicht verwendeter Indizes

Wählen Sie `Information_schema`.`statistics`.`table_schema` AS` table_schema`, `Information_schema`.`statistics `Non_unique`, max (if (isnull (` Information_schema`.`statistics`.`sub_Part`), 0,1) als `subpart_exists olumns` aus "Information_schema ".`statistics" WHERE ("Information_schema`.`.'Index_type" = 'BTree') und ("Information_schema ".`.`statistics ".`table_schema", nicht in ('mysqlsql'), 'scisinformation', ',', ',', ',', ',', ',', ',', ',', ',', ',', ',', ',', ',', In Information_Schema '', ',', ',', ',', ', In Information_Schema' ',', ',', ',', ',', ', In Information_Schema' ',', ',', ',', ',', In Information_Schema '', ',', ',', ',', ',', In Information_Schema '', ',', ',', ',', ',', Info. iStics`.`table_schema`, `Information_schema`.`statistics`.`table_name`,` Information_Schema`.`statistics`.`index_name`

Ergänzung: MySQL-ID-Restindex_MySQL doppelter Index, redundanter Index, unbenutzte Indexdefinition und Suche

1. Redundante und doppelte Indizes

MySQL ermöglicht das Erstellen mehrerer Indizes für dieselbe Spalte. Ob beabsichtigt oder unbeabsichtigt, MySQL muss doppelte Indizes separat verwalten, und der Optimierer muss sie bei der Optimierung von Abfragen auch einzeln berücksichtigen, was sich auf die Leistung auswirkt. Doppelte Indizes beziehen sich auf Indizes desselben Typs, die für dieselben Spalten in derselben Reihenfolge erstellt wurden. Die Erstellung solcher Duplikate sollte vermieden und sofort nach Entdeckung gelöscht werden. Es ist jedoch möglich, verschiedene Indextypen für dieselbe Spalte zu erstellen, um unterschiedliche Abfrageanforderungen zu erfüllen.

Es gibt einige Unterschiede zwischen redundanten und doppelten Indizes. Wenn Sie einen Index (a,b) erstellen, ist der erstellte Index (a) ein redundanter Index, da er nur ein Präfixindex des vorherigen Indexes ist. Daher kann (a,b) auch als (a) verwendet werden. (b,a) ist jedoch kein redundanter Index, und Index (b) ist es auch nicht, da b nicht die am weitesten links stehende Präfixspalte des Indexes (a,b) ist. Darüber hinaus sind andere Indextypen, die auf denselben Spalten erstellt werden (wie Hash-Indizes und Volltext-Indizes), keine redundanten Indizes des B-Baum-Indexes.

Außerdem gilt: Beim Sekundärindex (a, id) ist id der Primärschlüssel. Bei innodb ist die Primärschlüsselspalte bereits im Sekundärindex enthalten, daher handelt es sich auch hier um einen redundanten Index. In den meisten Fällen sind redundante Indizes nicht erforderlich. Sie sollten versuchen, vorhandene Indizes zu erweitern, anstatt neue zu erstellen. Manchmal sind redundante Indizes jedoch aus Leistungsgründen erforderlich, da die Erweiterung eines vorhandenen Indexes dazu führt, dass dieser zu groß wird und sich somit auf die Leistung anderer Abfragen auswirkt, die den Index verwenden. Wenn Sie beispielsweise einen Index für eine Integer-Spalte haben und nun eine zusätzliche lange Varchar-Spalte hinzufügen müssen, um den Index zu erweitern, kann die Leistung erheblich sinken, insbesondere wenn es Abfragen gibt, die diesen Index als überlagernden Index verwenden, oder wenn es sich um eine MyISAM-Tabelle handelt und viele Bereichsabfragen vorhanden sind (aufgrund der MyISAM-Präfixkomprimierung).

Beispielsweise hat die Tabelle userinfo, Myisam-Engine, 1 Million Zeilen, jeder state_id-Wert hat etwa 20.000 Zeilen und es gibt einen Index für die state_id-Spalte, der für die folgende Abfrage nützlich ist: Beispiel: select count(*) from userinfo where state_id=5; Getestet mit 115 QPS pro Sekunde

Der Index der Spalte state_id ist für die folgende Abfrage mit einer QPS von 10 pro Sekunde nicht sehr nützlich:

Wählen Sie State_ID, Stadt und Adresse aus den Benutzerinformationen, wobei State_ID = 5 ist.

Wenn der state_id-Index auf (state_id, city, address) erweitert wird, ist die Leistung der zweiten Abfrage schneller, die erste Abfrage jedoch langsamer. Wenn beide Abfragen schnell sein sollen, muss der state_id-Spaltenindex redundant gemacht werden. Handelt es sich jedoch um eine InnoDB-Tabelle, ist die Auswirkung des nicht redundanten State_ID-Spaltenindex auf die erste Abfrage nicht offensichtlich, da InnoDB keine Indexkomprimierung verwendet. Die QPS-Testergebnisse ausgewählter Abfragen mit unterschiedlichen Indexstrategien für MyISAM- und InnmodB-Tabellen (die folgenden Testdaten dienen nur als Referenz) lauten wie folgt:

Nur die Spalte state_id ist indiziert. Nur die Spalte state_id_2 ist indiziert. Es gibt zwei Indizes gleichzeitig.

myisam, erste Abfrage 114,96 25,40 112,19

myisam, zweite Abfrage 9,97 16,34 16,37

innodb, erste Abfrage 108,55 100,33 107,97

innodb, zweite Abfrage 12.12 28.04 28.06

Wie aus der obigen Abbildung ersichtlich ist, besteht der Nachteil darin, dass die Kosten höher sind, wenn beide Indizes verwendet werden. Im Folgenden ist die Geschwindigkeit aufgeführt, mit der 1 Million Datenzeilen unter verschiedenen Indexstrategien in die InnoDB- und MyISAM-Tabellen eingefügt werden (die folgenden Testdaten dienen nur als Referenz):

Nur der Index der Spalte state_id hat zwei Indizes gleichzeitig

InnoDB, 80 Sekunden 136 Sekunden, wenn beide Indizes genügend Inhalt haben

MyISAM, wenn nur ein Index genügend Inhalt hat 72 Sekunden 470 Sekunden

Wie Sie sehen, ist die Einfügegeschwindigkeit unabhängig von der Engine umso langsamer, je mehr Indizes vorhanden sind, insbesondere wenn nach dem Hinzufügen neuer Indizes der Speicherengpass erreicht wird. Die Lösung für redundante und doppelte Indizes ist einfach. Löschen Sie sie einfach. Aber zuerst müssen Sie solche Indizes finden. Sie können sie durch einige komplexe Abfragen finden, um auf die Tabelle information_schema zuzugreifen. Es gibt jedoch zwei einfachere Methoden. Verwenden Sie einige Ansichten in common_schema von Shlomi Noach, um sie zu finden. Sie können auch das Tool pt-dupulicate-key-checker im Percona-Toolkit verwenden. Dieses Tool findet redundante und doppelte Indizes, indem es die Tabellenstruktur analysiert. Für große Server ist es besser, externe Tools zu verwenden. Wenn sich auf dem Server eine große Datenmenge oder eine große Anzahl von Tabellen befindet, kann das Abfragen der Tabelle information_schema zu Leistungsproblemen führen. Es wird empfohlen, das Tool pt-dupulicate-key-checker zu verwenden.

Seien Sie beim Löschen von Indizes sehr vorsichtig:

Wenn es eine Abfrage wie „where a=5 order by id“ in der InnoDB-Engine-Tabelle gibt, ist der Index (a) sehr nützlich. Der Index (a,b) ist eigentlich der Index (a,b,id). Für Abfragen wie „where a=5 order by id“ kann dieser Index nicht zum Sortieren verwendet werden, sondern nur zum Sortieren von Dateien. Daher wird empfohlen, das Tool pt-upgrade aus der Percona Toolbox zu verwenden, um die geplanten Indexänderungen noch einmal zu überprüfen.

2. Unbenutzte Indizes

Zusätzlich zu redundanten und doppelten Indizes gibt es möglicherweise einige Indizes, die vom Server nie verwendet werden. Solche Indizes sind völlig redundant und es wird empfohlen, sie zu löschen. Es gibt zwei Tools, die beim Auffinden nicht verwendeter Indizes helfen können:

A: Aktivieren Sie zunächst die Servervariable userstat=ON in Percona Server oder MariaDB. Sie ist standardmäßig deaktiviert. Lassen Sie den Server dann eine Weile laufen und fragen Sie dann information_schema.index_statistics ab, um die Nutzungshäufigkeit jedes Indexes zu ermitteln.

B: Verwenden Sie das pt-index-usage-Tool im Percona-Toolkit. Dieses Tool kann das Abfrageprotokoll lesen, jede Abfrage im Protokoll erklären und dann einen Bericht über den Guanyu-Index und die Abfrage ausdrucken. Dieses Tool kann nicht nur herausfinden, welche Indizes nicht verwendet werden, sondern auch den Ausführungsplan der Abfrage verstehen. Beispielsweise werden in einigen Fällen einige ähnliche Abfragen unterschiedlich ausgeführt, was dabei helfen kann, die Abfragen zu lokalisieren, die gelegentlich eine schlechte Serverqualität aufweisen. Das Tool kann die Ergebnisse auch in eine MySQL-Tabelle schreiben, um die Abfrageergebnisse zu erleichtern.

Das Obige ist meine persönliche Erfahrung. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden. Sollten dennoch Fehler oder unvollständige Überlegungen vorliegen, freue ich mich über eine Korrektur.