Der Unterschied zwischen redundanten und doppelten Indizes in MySQL

MySQL ermöglicht das Erstellen mehrerer Indizes für eine einzelne Spalte. Ob beabsichtigt oder unbeabsichtigt, MySQL muss diese doppelten Indizes separat verwalten, und der Optimierer muss sie bei der Optimierung von Abfragen auch einzeln berücksichtigen, was sich auf die MySQL-Leistung auswirkt.

Konzepterklärung

Doppelte Indizes: Indizes desselben Typs, die für dieselben Spalten in derselben Reihenfolge erstellt wurden. Die Erstellung solcher doppelten Indizes sollte vermieden und nach Entdeckung sofort entfernt werden.

Redundante Indizes: Zwei Indizes decken dieselben Spalten in derselben Reihenfolge ab.

Grund für die Erstellung

Im Allgemeinen erstellen wir manchmal versehentlich doppelte Indizes, wie im folgenden Beispiel:

CREATE TABLE-Test (
 ID INT NICHT NULL PRIMÄRSCHLÜSSEL,
 Ein INT NOT NULL,
 B INT NICHT NULL,
 EINZIGARTIGE ID,
 INDEX(ID)
)ENGINE=InnoDB;

Da die eindeutigen Einschränkungen und Primärschlüsselbeschränkungen von MySQL über Indizes implementiert werden, erstellt die mit dem obigen Code erstellte Tabelle tatsächlich drei Indizes für die ID-Spalte. Normalerweise gibt es hierfür keinen Grund, außer dass verschiedene Indextypen für die gleiche Spalte erstellt werden sollen, um unterschiedliche Abfrageanforderungen zu erfüllen. Beispielsweise gibt es zwei Indizes: KEY(col) und FULLTEXT KEY(col).

Redundante Indizes unterscheiden sich etwas von doppelten Indizes. Wenn Sie den Index (A, B) erstellen, ist die Erstellung des Index (A) ein redundanter Index, da es sich lediglich um einen Präfixindex des vorherigen Index handelt. Wenn jedoch der Index (B,A) erstellt wird, handelt es sich nicht um einen redundanten Index, und der Index (B) ist kein redundanter Index, da er nicht die äußerste linke Präfixspalte des Index (A,B) ist. Darüber hinaus sind Indizes unterschiedlichen Typs bei B-Baum-Indizes nicht redundant, unabhängig von den abgedeckten Indexspalten.

In den meisten Fällen sind redundante Indizes nicht erforderlich und vorhandene Indizes sollten so weit wie möglich erweitert werden, anstatt neue Indizes zu erstellen. Manchmal wird der Index jedoch aus Leistungsgründen (z. B. beim Erweitern eines vorhandenen Indexes) zu groß und beeinträchtigt dadurch die Leistung anderer Abfragen, die den Index verwenden.

Beeinflussen

Das Erstellen redundanter Indizes als abdeckende Indizes kann unsere QPS für einige Abfragen verbessern, aber das Vorhandensein von zwei Indizes hat auch den Nachteil höherer Indexkosten.

Wenn einer Tabelle immer mehr Indizes hinzugefügt werden, verringert sich die Einfügegeschwindigkeit in die Tabelle. Im Allgemeinen verlangsamt das Hinzufügen neuer Indizes INSERT-, UPDATE-, DELETE- und andere Vorgänge, insbesondere wenn die neuen Indizes Speicherengpässe verursachen.

Die Lösung für redundante und doppelte Indizes ist einfach: Löschen Sie sie einfach. Zunächst müssen Sie jedoch solche Indizes finden.

Ergänzung: Bereinigen doppelter Indizes und doppelter Fremdschlüssel in MySQL

MySQL erlaubt das Erstellen doppelter Indizes für dieselbe Spalte, dies schadet der Datenbank jedoch eher, als dass es ihr nützt. Um die Leistung der Datenbank zu verbessern, müssen Sie solche doppelten Indizes regelmäßig überprüfen.

Es kann den Speicherplatzverbrauch reduzieren, die Datenträger-E/A verringern, die Anzahl der Indizes verringern, die verglichen werden müssen, wenn der Optimierer Abfragen optimiert, den verschiedenen Aufwand für die Pflege redundanter Indizes in der Datenbank verringern und die Datenbankleistung verbessern (Einfügen, Aktualisieren, Löschen).

Erkennung doppelter Indizes

pt-duplicate-key-checker: Erkennen Sie doppelte oder redundante Indizes oder Fremdschlüssel in MySQL-Tabellen mithilfe der von SHOW CREATE TABLE ausgegebenen Tabellendefinitionen.

Erkennbare redundante/doppelte Indextypen: Wenn ein Index dieselben Spalten in derselben Reihenfolge enthält wie ein anderer Index oder wenn die im Index enthaltenen Spalten die äußersten linken Präfixspalten eines anderen Index sind, wird dies als doppelter/redundanter Index betrachtet.

Standardmäßig werden nur Indizes desselben Typs (z. B. BTREE-Indizes) verglichen. Indizes unterschiedlichen Typs werden nicht als dupliziert/redundant betrachtet, selbst wenn sie die obige Beschreibung erfüllen. Dieses Verhalten kann jedoch über Parameter geändert werden.

Darüber hinaus können doppelte Fremdschlüssel erkannt werden, also Fremdschlüssel, die auf dieselbe Tabelle und dieselben Spalten verweisen. Bei einer Tabelle mit einem gruppierten Index wird das Hinzufügen eines Indexes der Primärschlüsselspalte nach dem Sekundärindex ebenfalls als redundant angesehen, da in diesem Fall das Ende des Sekundärindexes selbst die Primärschlüsselinformationen enthält.

Die grundlegende Verwendung und die Beispielausgabe lauten wie folgt

[root@VM_8_180_centos-Pakete]# pt-duplicate-key-checker A=utf8, F=/etc/my.cnf, h=localhost, u=root, P=3306 –ask-pass

Beispielausgabe:

# ########################################################################
# dcf.privilege                              
# ########################################################################
# Eindeutigkeit von UQI_IDX_1 wird ignoriert, da PRIMARY eine Duplikatsbeschränkung ist
# UQI_IDX_1 ist ein Duplikat von PRIMARY
# Wichtige Definitionen:
# EINZIGARTIGER SCHLÜSSEL `UQI_IDX_1` (`privilege_id`),
# PRIMÄRSCHLÜSSEL (`privilege_id`),
# Spaltentypen:
# `privilege_id` varchar(50) collate utf8_bin not null comment 'Privilege id'
# Um diesen doppelten Index zu entfernen, führen Sie Folgendes aus:
ALTER TABLE `dcf`.`privilege` DROP INDEX `UQI_IDX_1`;
# ########################################################################
# dcf.t_game_config                            
# ########################################################################
# Eindeutigkeit des pkey wird ignoriert, da PRIMARY eine Duplikatsbeschränkung ist
# pkey ist ein Duplikat von PRIMARY
# Wichtige Definitionen:
# EINZIGARTIGER SCHLÜSSEL `pkey` (`pkey`)
# PRIMÄRSCHLÜSSEL (`pkey`),
# Spaltentypen:
# `pkey` bigint(20) nicht null auto_increment
# Um diesen doppelten Index zu entfernen, führen Sie Folgendes aus:
ALTER TABLE `dcf`.`t_game_config` DROP INDEX `pkey`;
# ########################################################################
#dcf.t_projektinstitution                        
# ########################################################################
# index_1 ist ein linkes Präfix von index_2
# Wichtige Definitionen:
# SCHLÜSSEL `index_1` (`project_id`),
# SCHLÜSSEL `index_2` (`Projekt-ID`,`Institutions-ID`,`Flagge_löschen`)
# Spaltentypen:
# `project_id` bigint(20) nicht null Kommentar 'Projekt-ID'
# `institution_id` varchar(20) nicht null Kommentar 'Institutions-ID'
# `delete_flag` tinyint(4) nicht null
# Um diesen doppelten Index zu entfernen, führen Sie Folgendes aus:
ALTER TABLE `dcf`.`t_project_institution` DROP INDEX `index_1`;
# ########################################################################
# dcf_commons.bank_cnaps                         
# ########################################################################
# idx ist ein Duplikat von PRIMARY
# Wichtige Definitionen:
# SCHLÜSSEL `idx` (`cnaps`)
# PRIMÄRSCHLÜSSEL (`cnaps`),
# Spaltentypen:
# `cnaps` varchar(255) nicht null Kommentar ‚Elektronische Bankleitzahl‘
# Um diesen doppelten Index zu entfernen, führen Sie Folgendes aus:
ALTER TABLE `dcf_commons`.`bank_cnaps` DROP INDEX `idx`;
# ########################################################################
# dcf_vertrag.kundenbankkonto                   
# ########################################################################
# IDX_CUSTOMER_ID ist ein linkes Präfix von UQI_IDX_1
# Wichtige Definitionen:
# SCHLÜSSEL `IDX_CUSTOMER_ID` (`Kunden-ID`)
# EINZIGARTIGER SCHLÜSSEL `UQI_IDX_1` (`Kunden-ID`,`Kontonr.`,`Filiale`,`Kontotyp`,`Kontoname`) MIT BTREE,
# Spaltentypen:
# `customer_id` varchar(20) collate utf8_bin not null comment 'Kunden-ID'
# `account_no` varchar(40) collate utf8_bin default null comment 'Bankkontonummer'
# `branch_bank` varchar(100) collate utf8_bin default null comment 'Filiale wird geöffnet'
# `account_type` tinyint(4) default null comment 'Kontotyp: z. B. Empfangskonto, Rückzahlungskonto usw.\n0-Empfangskonto\n1-Rückzahlungskonto'
# `account_name` varchar(100) collate utf8_bin default null comment 'Bankkontoname'
# Um diesen doppelten Index zu entfernen, führen Sie Folgendes aus:
ALTER TABLE `dcf_contract`.`customer_bank_account` DROP INDEX `IDX_CUSTOMER_ID`;
# ########################################################################
# dcf_vertrag.t_vertragskonto                     
# ########################################################################
# IDX_CONTRACT_ID ist ein linkes Präfix von t_contract_account_uq1
# Wichtige Definitionen:
# SCHLÜSSEL `IDX_CONTRACT_ID` (`Vertrags-ID`)
# EINZIGARTIGER SCHLÜSSEL `t_contract_account_uq1` (`contract_id`,`account_type`),
# Spaltentypen:
# `contract_id` bigint(20) nicht null Kommentar 'Vertrags-ID'
# `account_type` tinyint(4) nicht null Kommentar 'Kontotyp: globalconstant.bankaccounttypec konstant\n0-Empfängerkonto\n1-Rückzahlungskonto usw.'
# Um diesen doppelten Index zu entfernen, führen Sie Folgendes aus:
ALTER TABLE `dcf_contract`.`t_contract_account` DROP INDEX `IDX_CONTRACT_ID`;
......
......
# ########################################################################
# Zusammenfassung der Indizes                           
# ########################################################################
# Größe Duplikatindizes 173317386
# Gesamtzahl doppelter Indizes 18
# Indizes gesamt 562

Es werden der doppelte/redundante Typ, die Index-/Fremdschlüsseldefinition, die im Index enthaltenen Spaltentypen, SQL zum Entfernen doppelter/redundanter Index-/Fremdschlüssel und schließlich Statistiken zum Index angegeben.

Entfernen doppelter Indizes

Sie können die ALTER TABLE-Anweisung direkt in den Ausgabeergebnissen des Tools ausführen, achten Sie jedoch darauf, die möglichen Auswirkungen vor der Ausführung sorgfältig zu bewerten. Wenn die Tabelle beispielsweise sehr groß ist, kann dies zu Verzögerungen bei der Master-Slave-Replikation führen. Wenn das SQL Indexhinweise enthält, kann das direkte Löschen des Index zu SQL-Syntaxfehlern führen. Am besten prüfen Sie im Voraus, ob solches SQL enthalten ist (Sie können das SQL über allgemeine Protokoll- oder TCPDump-Tools abrufen und analysieren).

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.