Zusammenfassung von 5 MySQL-Optimierungslimit-Abfrageanweisungen-MySQL-Tutorial-php.cn

Zusammenfassung von 5 MySQL-Optimierungslimit-Abfrageanweisungen

伊谢尔伦

Freigeben： 2017-06-28 14:12:43

Original

1566 Leute haben es durchsucht

In diesem Artikel werden hauptsächlich 5 Methoden zur Optimierung von MySQL-LimitsAbfragenAnweisungen vorgestellt. Dabei handelt es sich um Unterabfrage-Optimierungsmethoden, Umkehrtabellen-Optimierungsmethoden, Reverse-Search-Optimierungsmethoden, Limit-Limit-Optimierungsmethoden und Überprüfen Sie nur den -Index -Methode. Freunde, die sie benötigen, können sich auf die

-Paging-Methode von

von MySQL beziehen. Sie benötigen nur einen Grenzwert für den Offset und die Länge, um die Daten zu erhalten ist relativ groß, die Leistung von MySQL nimmt offensichtlich ab

1. Unterabfrage-Optimierungsmethode

Suchen Sie zuerst das erste Datenelement und dann die Daten, die größer sind als oder gleich diesem Datenelement Die ID ist die abzurufende Daten.

Nachteile: Die Daten müssen kontinuierlich sein. Man kann sagen, dass es keine Where-Bedingung geben kann, die die Daten filtert Kontinuität verlieren. Informationen zur spezifischen Methode finden Sie im folgenden Abfragebeispiel:

Aus den Ergebnissen lässt sich erkennen, dass die Unterabfrage

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Member;
+----------+
| count(*) |
+----------+
|   169566 | 
+----------+
1 row in set (0.00 sec)
mysql> pager grep !~-
PAGER set to &#39;grep !~-&#39;
mysql> select * from Member limit 10, 100;
100 rows in set (0.00 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 1000, 100;
100 rows in set (0.01 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 100000, 100;
100 rows in set (0.10 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
100 rows in set (0.02 sec)
mysql> nopager
PAGER set to stdout

mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00003300
   Query: select count(*) from Member
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00167000
   Query: select * from Member limit 10, 100
*************************** 3. row ***************************
Query_ID: 3
Duration: 0.00112400
   Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.00263200
   Query: select * from Member limit 1000, 100
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.00134000
   Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.09956700
   Query: select * from Member limit 100000, 100
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.02447700
   Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

Nach dem Login kopieren

verwendet werden kann, wenn der Offset mehr als 1000 beträgt Leistung effektiv verbessern.

2. Invertierte Tabellenoptimierungsmethode

Die invertierte Tabellenmethode ähnelt der Erstellung eines Index, wobei eine Tabelle verwendet wird, um die Anzahl der Seiten zu verwalten, und dann durch effiziente Verbindungen Daten abrufen

Nachteile: Nur für eine feste Anzahl von Daten geeignet, Daten können nicht

gelöscht werden

und es ist schwierig, Seitentabellen zu pflegen

Einführung in die invertierte Tabelle : (Und der invertierte Index heißt Es ist der Algorithmus-Eckpfeiler der Suchmaschine

)

Invertierte Tabelle bezieht sich auf einen im Speicher gespeicherten invertierten Index, der invertierte Datensätze anhängen kann. Eine invertierte Liste ist ein kleiner invertierter Index.

Temporäre invertierte Datei bezieht sich auf einen invertierten Index, der in Form einer Datei gespeichert wird und nicht an invertierte Datensätze angehängt werden kann. Die temporäre invertierte Datei ist ein mittelgroßer invertierter Index.

Die endgültige invertierte Datei bezieht sich auf den invertierten Index, der durch Zusammenführen der temporären invertierten Dateien erhalten wird, die in Form von Dateien auf der Festplatte gespeichert sind. Die endgültige invertierte Datei ist ein größerer invertierter Index.

Invertierter Index ist ein abstraktes Konzept, und invertierte Liste, temporäre invertierte Datei und endgültige invertierte Datei sind drei verschiedene Erscheinungsformen des invertierten Index.

3. Methode zur Optimierung der umgekehrten Suche

Wenn der Offset die Hälfte der Datensätze überschreitet, wird zuerst sortiert, sodass der Offset umgekehrt wird

Nachteile: Die Reihenfolge nach Optimierung ist mühsam, was sich auf die Effizienz der Datenänderung auswirkt, und die Gesamtzahl der Datensätze muss bekannt sein, und der Versatz beträgt mehr als die Hälfte der Daten Limit-Offset-Algorithmus:

Vorwärtssuche: (Aktuelle Seite – 1) * Seitenlänge

Rückwärtssuche: Gesamtzahl der Datensätze – Aktuelle Seite * Seitenlänge

Führen Sie ein Experiment durch, um zu sehen, wie die Leistung ist

Gesamtzahl der Datensätze: 1.628.775
Anzahl der Datensätze pro Seite: 40

Gesamtzahl der Seiten: 1.628.775 / 40 = 40720

Anzahl der Mittelseiten: 40720 / 2 = 20360

Seite 21000
Forward-Lookup-SQL:

Zeit: 1,8696 Sekunden

Reverse-Lookup-SQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40

Nach dem Login kopieren

Zeit : 1,8336 Sekunden

Seite 30000

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

Nach dem Login kopieren

Vorwärtssuche SQL:

Zeit: 2,6493 Sekunden

Rückwärtssuche SQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

Nach dem Login kopieren

Zeit: 1,0035 Sekunden

Beachten Sie, dass das Ergebnis der umgekehrten Suche in absteigender Reihenfolge desc vorliegt und InputDate die Einfügezeit des Datensatzes ist. Sie können auch einen Primärschlüssel-Joint-Index verwenden. aber es ist unbequem.

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

Nach dem Login kopieren

4.Limit-Optimierungsmethode

Begrenzen Sie den Limit-Offset unter eine bestimmte Zahl. . Wenn diese Zahl überschritten wird, sind keine Daten vorhanden. Ich erinnere mich, dass Alibabas DBA dies getan hat.

Nur die Indexmethode nachschlagen besteht darin, zuerst n Datensätze zu lesen, dann die ersten n Datensätze zu verwerfen und m gewünschte Datensätze zu lesen. Je größer n ist, desto schlechter ist die Leistung.

SQL vor der Optimierung:

SQL nach der Optimierung:

Der Unterschied besteht darin, dass SQL vor der Optimierung mehr E/A-Verschwendung erfordert, weil Zuerst wird der Index gelesen, dann werden die Daten gelesen und dann werden unnötige Zeilen verworfen. Der optimierte schreibgeschützte SQL-Index (Unterabfrage) (Cover-Index) reicht aus, und dann werden die erforderlichen Spalten über member_id gelesen.

Das obige ist der detaillierte Inhalt vonZusammenfassung von 5 MySQL-Optimierungslimit-Abfrageanweisungen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!