Praktische Erfahrung bei der Optimierung von MySQL-Tabellen mit mehreren zehn Millionen Daten

Vorwort

Lassen Sie es mich hier zunächst erklären. Viele Leute im Internet sagen, dass Alibaba vorschreibt, dass 5 Millionen Daten auf verschiedene Datenbanken und Tabellen aufgeteilt werden müssen. Tatsächlich sind diese 5 Millionen kein fester Wert, sondern hängen mit der Konfiguration von MySQL und der Hardware der Maschine zusammen. Um die Leistung zu verbessern, lädt MySQL den Tabellenindex in den Speicher. Wenn die Datenmenge in der Tabelle jedoch eine bestimmte Menge erreicht, kann der Speicher diese Indizes nicht mehr speichern. Ohne die Möglichkeit, Indizes zu speichern, können nur Festplatten-E/A ausgeführt werden, was zu Leistungseinbußen führt.

Praktisches Tuning

Ich habe eine Tabelle mit 1000 W Daten und nur einem Primärschlüsselindex.

CREATE TABLE `Benutzer` (
  `id` int(10) NICHT NULL AUTO_INCREMENT,
  `uname` varchar(20) DEFAULT NULL COMMENT 'Konto',
  `pwd` varchar(20) DEFAULT NULL COMMENT 'Passwort',
  `addr` varchar(80) DEFAULT NULL COMMENT 'Adresse',
  `tel` varchar(20) DEFAULT NULL COMMENT 'Telefon',
  `regtime` char(30) DEFAULT NULL COMMENT 'Registrierungszeit',
  `Alter` int(11) DEFAULT NULL KOMMENTAR 'Alter',
  PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000003 DEFAULT CHARSET=utf8;

Abfrage für alle Personen ab 16 Jahren. Es ist ziemlich langsam. Normalerweise haben wir ein Backend-System, beispielsweise eine E-Commerce-Plattform, und dies ist eine Benutzertabelle. Das Backend-Verwaltungssystem fragt diese Benutzerinformationen im Allgemeinen ab und führt einige Vorgänge aus, z. B. das direkte Hinzufügen neuer Benutzer oder das Löschen von Benutzern.

Hier gibt es also zwei Anforderungen: Eine ist die Abfrageanzahl, die andere ist die Abfrage pro Seite

Lassen Sie uns die zum Zählen benötigte Zeit bzw. die für die Paging-Abfrage benötigte Zeit testen.

select * from user limit 1, 10 //Wird fast nicht verwendet select * from user limit 1000000, 10 //0,35 s
Wählen Sie * ab Benutzerlimit 5000000, 10 //1,7 s
Wählen Sie * ab Benutzerlimit 9000000, 10 //2,8 s
wähle count(1) vom Benutzer //1,7 s

Aus der obigen Abfragezeit können wir ersehen, dass es bei einer Paging-Abfrage umso länger dauert, je mehr Daten abgefragt werden. Außerdem dauert das Zählen der Abfragen ebenfalls 1,7 Sekunden. Dies genügt offensichtlich nicht unseren Ansprüchen. Hier besteht also Optimierungsbedarf. Versuchen wir zunächst die Indexoptimierung. Dies ist der Ausführungsplan mit nur dem Primärschlüsselindex:

Tabelle „Benutzer“ ändern, INDEX „sindex“ hinzufügen („uname“, „pwd“, „addr“, „tel“, „regtime“, „age“)

Im obigen Ausführungsplan ist zu erkennen, dass sich die Abfragegeschwindigkeit nicht wirklich ändert, obwohl der Typ von „all->index“ geändert wird und der Sindex-Index verwendet wird.

Tatsächlich dient die Erstellung eines gemeinsamen Index dazu, bedingte Abfragen schneller durchzuführen als vollständige Tabellenabfragen.

select * from user where uname='6.445329111484186' //3,5 s (kein gemeinsamer Index)
select * from user where uname='6.445329111484186' //0,003 s (mit gemeinsamem Index)

Das ist der Unterschied zwischen einem gemeinsamen Index und keinem Index.

Hier lässt sich grundsätzlich nachweisen, dass die Effizienz bei der Durchführung einer vollständigen Tabellenabfrage mit oder ohne Index sehr gering ist.

Da das Indexergebnis nicht mehr brauchbar ist, bleibt uns nur noch nach anderen Lösungen zu suchen. Nach dem, was ich in meinem vorherigen MySQL-Interview gesagt habe, kann die Anzahl separat in einer Tabelle gespeichert werden

CREATE TABLE `Attribut` (
  `id` int(11) NICHT NULL,
  `formname` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 'Tabellenname',
  `formcount` int(11) NOT NULL COMMENT 'Gesamtdaten der Tabelle',
  PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Hier möchte ich sagen, dass diese Art von Tabelle im Allgemeinen nicht alle abfragt, sondern nur eine. Wenn Sie also die Tabelle erstellen, können Sie einen Hash erstellen

select formcount from attribute where formname='user' //Fast nicht verwendet

Die Zähloptimierung ist abgeschlossen. Wenn oben Auswahlbedingungen vorliegen, können Sie einen Index erstellen und per Indexfilterung abfragen, sodass Sie die Anzahl nicht lesen müssen.

Nun, die Anzahl ist in Ordnung, aber wie lässt sich die Paging-Abfrage optimieren? Hier können wir Unterabfragen verwenden, um zu optimieren

Wählen Sie * vom Benutzer aus, wobei
id>=(wähle ID aus Benutzerlimit 9000000,1) Limit 10 //1,7 s

Tatsächlich handelt es sich beim Schreiben von Unterabfragen zum Bestimmen der ID auf diese Weise um eine Abfrage über abdeckende Indizes. Die Effizienz wird erheblich gesteigert. Mein Test hier beträgt jedoch 1,7 Sekunden. Als das Unternehmen diesen Aspekt zuvor optimierte, war die Abfragezeit kürzer. Sie können auch selbst Daten generieren und testen.

Wenn die Datenmenge jedoch zu groß ist, empfehle ich dennoch, es zu verwenden oder einige Standardauswahlen vorzunehmen. Die Anzahl kann separat aufgeführt werden.

An diesem Punkt ist die Optimierung einer Paging-Abfrage für mehrere zehn Millionen Daten abgeschlossen.

Zusammenfassen

Dies ist das Ende dieses Artikels über die Optimierung von MySQL-Zehnmillionen-Datentabellen. Weitere relevante Inhalte zur Optimierung von MySQL-Zehnmillionen-Datentabellen finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!