Begrenzen Sie die Optimierung in MySQL

咔咔

Freigeben： 2021-01-19 08:57:44

Original

2651 Leute haben es durchsucht

"
Abschließend beginnen wir mit der Optimierung von MySQL. In diesem Artikel wird die Optimierung von Paging erläutert. Ich hoffe, ich kann eine Lösung finden, die zu Ihnen passt
"

Vorwort

Das Thema Paging ist bereits ein Klischee, aber wie viele Freunde möchten ihre eigenen Systeme optimieren und gleichzeitig ihre eigene einzigartige Persönlichkeit in Projekten bewahren?

Begrenzen Sie die Optimierung in MySQL — Persönliche

Optimierung erfordert, dass Sie selbst die Initiative ergreifen und Ihre eigenen Testdaten erhalten. Erst auf dem Weg zum Testen werden Sie weitere Dinge entdecken, die Sie nicht wissen.

In diesem Artikel wird auch das Thema Paginierungsoptimierung erläutert.

1. Tabellenstruktur

Diese Datenbankstruktur ist die Tabelle von Kakas aktuellen Online-Projekten, mit der Ausnahme, dass Kaka die Feldnamen geändert und das Zeitfeld gestrichen hat.

Die Datenbankstruktur ist wie folgt

<span style="display: block; background: url(https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; padding-top: 15px; background: #282c34; border-radius: 5px;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">CREATE</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">TABLE</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">`tp_statistics`</span> (<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_id`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">int</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> AUTO_INCREMENT,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field1`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field2`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field3`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  PRIMARY <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">KEY</span> (<span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_id`</span>)<br/>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">ENGINE</span>=<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">InnoDB</span> AUTO_INCREMENT=<span class="hljs-number" style="color: #d19a66; line-height: 26px;">3499994</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">CHARSET</span>=utf8 <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">COLLATE</span>=utf8mb4_general_ci ROW_FORMAT=<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">COMPACT</span>;<br/><br/></code>

Nach dem Login kopieren

Anhand der obigen Informationen können wir sehen, dass die Tabelle derzeit 3,5 Millionen Datensätze enthält. Als nächstes werden wir die Abfrage für diese 3,5 Millionen Datensätze optimieren .

2. Vorläufige Untersuchung der Abfrageeffizienz

Schreiben wir zunächst eine Abfrage-SQL-Anweisung und werfen wir einen Blick auf die von der Abfrage verbrauchte Zeit.

Anhand der folgenden Abbildung können Sie sehen, dass die Abfragezeit grundsätzlich ignoriert wird. Was jedoch beachtet werden sollte, ist der Offsetwert des Grenzwerts.

Wir müssen also den Offset schrittweise erhöhen und dann testen. Ändern Sie zunächst den Offset auf 10000.

Sie sehen, dass die Abfragezeit immer noch sehr ideal ist.

Um Zeit zu sparen, klicken Sie auf und passen Sie den Offset-Wert direkt auf 340 W an.

Zu diesem Zeitpunkt sind sehr deutliche Änderungen zu erkennen, die Abfragezeit hat sich auf 0,79 Sekunden erhöht.

Wenn eine solche Situation auftritt, muss sie unbedingt optimiert werden. Nehmen Sie einfach die Tastatur in die Hand und führen Sie sie aus. 3. Analysieren der Gründe, warum Abfragen Zeit brauchen Schauen Sie sich den grundlegenden Teil von MySQL an.

Wie Sie auf dem Bild unten sehen können, wurden für alle drei Abfrageanweisungen Tabellenscans durchgeführt.

Wir alle wissen, dass es eine Sortierung geben muss, solange Paging vorhanden ist. Fügen Sie also eine Sortierung hinzu und prüfen Sie die Abfrageeffizienz.

Analysieren und betrachten Sie dann die sortierten Aussagen.

Hier können Sie sehen, dass bei Verwendung der Sortierung die Anzahl der von der Datenbank gescannten Zeilen dem Offset plus der Anzahl der erforderlichen Abfragen entspricht.

Was Sie an dieser Stelle wissen können, ist, dass es sich bei einem sehr großen Versatz wie bei der Grenzabfrage 3400000,12 im obigen Fall verhält.

Zu diesem Zeitpunkt muss MySQL 3400012 Datenzeilen abfragen und dann die letzten 12 Daten zurückgeben.

Die zuvor abgefragten 340-W-Daten werden verworfen. Solche Ausführungsergebnisse sind nicht das, was wir wollen.

Kaka, ich habe schon früher verwandte Artikel gesehen, in denen stand, dass die Lösung für dieses Problem darin besteht, entweder die Anzahl der Paging direkt zu begrenzen oder die Leistung zu optimieren, wenn der Offset sehr groß ist.

Wenn Sie diesen Artikel bis hierhin gelesen haben, wie könnte er Sie dann enttäuschen? Es muss ein Leistungsproblem bei der Optimierung großer Offsets sein.

4. Optimierung

Da die Optimierung erwähnt wird, gibt es nur zwei Punkte: Index hinzufügen und andere Lösungen verwenden, um diese Lösung zu ersetzen.

Die von Kaka bereitgestellten Informationen zur Datentabellenstruktur können vollständig als Ausleihdatensatz der Bibliothek verstanden werden, und Sie müssen sich um nichts in den Feldern kümmern.

Beim Sortieren wird in diesem Szenario nicht die Zeit sortiert, sondern der Primärschlüssel, und das Zeitfeld wird aufgrund des Hinzufügens von Testdaten gelöscht.

接下来使用覆盖索引加inner join的方式来进行优化。

<span style="display: block; background: url(https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; padding-top: 15px; background: #282c34; border-radius: 5px;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> ss_id,ss_field1,ss_field2,ss_field3 <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> tp_statistics <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">inner</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">join</span> ( <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> ss_id <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> tp_statistics <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">order</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">by</span> ss_id <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">limit</span> <span class="hljs-number" style="color: #d19a66; line-height: 26px;">3000000</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">10</span>) b <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">using</span> (ss_id);<br/></code>

Nach dem Login kopieren

从上图可以看到查询时间从0.8s优化到了0.4s，但是这样的效果还是不尽人意。

于是只能更换一下思路再进行优化。

既然优化最大偏移量这条路有点坎坷，能不能从其它方面进行入手。

估计有很多同学已经知道咔咔将要抛出什么话题了。

没错，就是使用where > id 然后使用limit。

先来测试一波结果，在写具体实现方案。

根据上图可以看到这种方式是十分可行的，分页在300W条数据以后的查询时间也基本忽略不计。

那么这种方案要怎么实现呢！

五、方案落地

其实这个方案真的很简单，只需要简单的转换一下思路即可。

Wenn der Client zum ersten Mal Daten erhält, werden die Offset- und Limit-Parameter normal übergeben.

Die ersten zurückgegebenen Daten werden mithilfe des vom Client übergebenen Offsets und Limits ermittelt.

Wenn die erste Datenrückgabe erfolgreich ist.

Wenn der Client zum zweiten Mal Daten abruft, haben sich die Parameter geändert und können nicht mehr ausgeglichen und begrenzt werden.

Der Parameter, der zu diesem Zeitpunkt übergeben werden sollte, ist die ID des letzten Datenelements, das zum ersten Mal erfasst wurde.

Die Parameter sind derzeit last_id und limit.

Nachdem Sie die last_id im Hintergrund erhalten haben, können Sie die where-Bedingung in der SQL-Anweisung

Als nächstes wird Kaka Ihnen anhand eines Falles eine direkte und klare Erklärung geben.

Praktischer Fall

Das Folgende ist ein Fall, der in der Praxis demonstriert wird. Beispielsweise werden die Daten durch die erstmalige Verwendung von Seite und Limit ermittelt.

Die ID des letzten Datenelements im zurückgegebenen Ergebnis lautet

3499984

Wie unten gezeigt

Zu diesem Zeitpunkt wird die Where-Bedingung verwendet, um die Daten direkt zu filtern. Die Bedingung ist, dass die ID kleiner als die letzte ID der letzten Daten ist.

Zeitvergleich Änderung der Abfragezeit

Abfrage nach Optimierung

Sie können Offset und Limit nicht für Paging verwenden, wenn die Datenmenge groß ist, denn je größer der Offset, desto länger dauert die Abfrage. Natürlich kann nicht gesagt werden, dass kein Paging möglich ist. Wenn Ihre Daten nur Tausende oder Zehntausende von Elementen umfassen, spielt das keine Rolle, verwenden Sie es einfach beiläufig.

Der Implementierungsplan ist der obige Kaka. Verwenden Sie Offset und Limit, um Daten zum ersten Mal zu erhalten, und verwenden Sie zum zweiten Mal die Where-Bedingung, um Daten bis zur letzten ID der ersten Daten zu erhalten.

“
Beharrlichkeit beim Lernen, Beharrlichkeit beim Bloggen und Beharrlichkeit beim Teilen sind die Überzeugungen, die Kaka seit seiner Karriere immer vertreten hat. Ich hoffe, dass Kakas Artikel im riesigen Internet Ihnen ein wenig helfen können. Ich bin Kaka, bis zum nächsten Mal
“

Das obige ist der detaillierte Inhalt vonBegrenzen Sie die Optimierung in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!