Eine Fallstudie zur MySQL-Optimierung

1. Hintergrund

Auf jeder OLTP-Datenbankinstanz von Youzan wird ein SQL-Killer-Prozess eingerichtet, um SQL-Anweisungen zu beenden, deren Ausführungszeit einen bestimmten Schwellenwert überschreitet. Am Nachmittag erhielt der Entwickler eine Fehlermeldung, dass SQL beendet wurde, und half dem Entwickler bei der Fehlerbehebung. Dieser Artikel stellt diesen Fall vor.

Zweite Szenarioanalyse

Tabellenstruktur:

TABELLE `xxx_info` ERSTELLEN (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT KOMMENTAR 'id',

  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0' ,

  `group_id` bigint(20) unsigned NICHT NULL STANDARD '0',

  `nick_name` varchar(30) NOT NULL DEFAULT '' KOMMENTAR 'Spitzname',

  `is_del` tinyint(5) NOT NULL DEFAULT '0' COMMENT '0: Daten sind gültig, 1: Daten sind logisch gelöscht',

  `created_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',

  `updated_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit',

  Primärschlüssel (`id`),

  SCHLÜSSEL `idx_userid_groupid` (`Benutzer-ID`,`Gruppen-ID`)

) ENGINE=InnoDB AUTO_INCREMENT=1382032 DEFAULT CHARSET=utf8mb4 ;

Das SQL-Problem ist wie folgt

 Wählen Sie ID, Name, Status aus xxx_info, wobei Benutzer-ID in (670039223, '373149878') und Gruppen-ID = 1 und is_del = 0 ist.

Als ich das SQL zum ersten Mal sah, überprüfte ich die Tabellenstruktur und den Index user_id. Dieser war vom numerischen Typ und der Index war ok. Dann verwendete der manuelle Ausführungsplan den Index idx_userid_groupid nicht.

Es wird vermutet, dass user_id in zwei verschiedenen Feldtypen eine „implizite Konvertierung“ verursacht. Ändern Sie die Parameterwerte in den numerischen Typ oder String oder verwenden Sie user_id=numerischer Typ oder user_id=string und führen Sie die Ausführung erneut aus.

Die Ausführungspläne sind korrekt. Wir müssen zwei Probleme lösen

Warum wird der Index also nicht durchgegangen, wenn die Benutzer-ID in (X, Y, Z) unterschiedliche Typen aufweist?

Wir verwenden optimizer_trace, um den Ausführungsplan zu verfolgen.

Setzen Sie die Sitzung optimizer_trace='enabled=on'.

Wählen Sie ID, Spitzname, is_del aus xxx_info, wobei Benutzer-ID in (670039223, '373149878') und Gruppen-ID = 1 und is_del = 0 ist.

Wählen Sie * aus information_schema.optimizer_trace;

Wählen Sie ID, Spitzname, is_del aus xxx_info, wobei Benutzer-ID in (670039223, '373149878') und Gruppen-ID = 1 und is_del = 0 ist.

Wählen Sie * aus information_schema.optimizer_trace;

Setzen Sie die Sitzung optimizer_trace='enabled=off'.

Rufen Sie die Ausführungspläne zweier SQL-Anweisungen ab und vergleichen Sie sie. Die Ergebnisse werden in

Als ich die Ergebnisse sah, sagte ich

Ich habe https://bugs.mysql.com durchsucht, aber keine relevanten Ergebnisse gefunden.

Wie generiere ich unterschiedliche Wertetypen im Code?

Nachfolgend der eigene Test des Entwicklers

Die aktuelle Lösung besteht darin, mit den Entwicklern zu kommunizieren und sie zu bitten, im Programm Konsistenzprüfungen der Parametertypen durchzuführen und sie alle in Int/Long-Typen zu konvertieren.

Besonderer Hinweis auf häufige Szenarien, in denen eine implizite Konvertierung zu Indexfehlern führt

1, wobei die linke Seite des Urteilssymbols eine Zeichenfolge und die rechte Seite ein Wert ist, beispielsweise

wobei Name = 123

2 Die Feldtypen der Multi-Table-Join-Bedingungen sind inkonsistent, ähnlich wie 1

3 Die Zeichensatztypen der Multi-Table-Joinbedingungen sind unterschiedlich. Zum Beispiel

Die Bestellnummer einer Tabelle ist utf8mb4, die Bestellnummer einer Tabelle ist utf8

Interessierte Freunde können mehr testen und sind herzlich eingeladen, andere Fälle zu besprechen.

Oben sind die Details eines MySQL-Optimierungsfalls aufgeführt. Weitere Informationen zu MySQL-Optimierungsfällen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!