Erfahren Sie, wie MySQL die Abfragegeschwindigkeit optimiert

Im vorherigen Kapitel haben wir vorgestellt, wie man den optimierten Datentyp auswählt und wie Um dies effizient zu tun, verwenden Sie Indizes, die für leistungsstarkes MySQL unerlässlich sind. Dies reicht jedoch nicht aus und es ist auch eine vernünftige Gestaltung der Abfragen erforderlich. Wenn die Abfrage schlecht geschrieben ist, kann sie keine hohe Leistung erzielen, egal wie sinnvoll die Tabellenstruktur und der Index sind.

Wenn es um die Leistungsoptimierung von MySQL geht, ist die Abfrageoptimierung die Quelle der Optimierung und kann auch am besten widerspiegeln, ob ein System schneller ist. Dieses und die folgenden Kapitel konzentrieren sich auf die Optimierung der Abfrageleistung und stellen einige Techniken zur Abfrageoptimierung vor, um Ihnen ein tieferes Verständnis dafür zu vermitteln, wie MySQL Abfragen tatsächlich ausführt, wo es langsam ist und wie Sie es schneller machen können Gründe für hohe Effizienz und Ineffizienz, die Ihnen helfen, Abfrage-SQL-Anweisungen besser zu optimieren.

Verwandte Lernempfehlungen: MySQL-Video-Tutorial p>

Dieses Kapitel beginnt mit „Warum ist die Abfragegeschwindigkeit so langsam?“, damit Sie klar erkennen können, wo die Abfrage langsam sein kann. Dies wird Ihnen helfen, die Abfrage besser zu optimieren und sich dessen bewusst zu sein einen Schritt voraus.

1. Wo ist die Langsamkeit?

Der eigentliche Maßstab für die Abfragegeschwindigkeit ist die Antwortzeit. Wenn Sie sich eine Abfrage als Aufgabe vorstellen, besteht sie aus einer Reihe von Unteraufgaben, die jeweils eine bestimmte Zeit in Anspruch nehmen. Wenn Sie eine Abfrage optimieren möchten, möchten Sie eigentlich auch ihre Unteraufgaben optimieren. Eliminieren Sie also einige dieser Unteraufgaben, reduzieren Sie dann die Anzahl der Ausführungen der Unteraufgaben oder sorgen Sie dafür, dass die Unteraufgaben schneller ausgeführt werden.

Welche Unteraufgaben gibt es, wenn MySQL eine Abfrage ausführt, und welche Unteraufgaben nehmen die meiste Zeit in Anspruch? Dies erfordert den Einsatz einiger Tools oder Methoden (z. B. Ausführungspläne), um die Abfrage zu analysieren und herauszufinden, wo die Langsamkeit liegt.

Im Allgemeinen kann der Lebenszyklus einer Abfrage grob in der folgenden Reihenfolge betrachtet werden: Vom Client zum Server, dann auf dem Server analysiert, Ausführungsplan generiert, ausgeführt und an den Client zurückgegeben. Unter diesen kann „Ausführung“ als die wichtigste Phase im gesamten Lebenszyklus angesehen werden, die eine große Anzahl von Aufrufen an die Speicher-Engine zum Abrufen von Daten und die Datenverarbeitung nach den Aufrufen, einschließlich Sortieren, Gruppieren usw., umfasst.

Bei der Ausführung dieser Aufgaben müssen Abfragen Zeit an verschiedenen Orten in verschiedenen Phasen verbringen, einschließlich Netzwerk- und CPU-Berechnungen, der Generierung statistischer Informationen und Ausführungspläne, des Wartens auf Sperren und anderer Vorgänge, insbesondere des Abrufens von Daten aus der zugrunde liegenden Speicher-Engine Diese Aufrufe erfordern Speicheroperationen und CPU-Operationen und können auch eine große Anzahl von Kontextwechseln und Systemaufrufen generieren.

Die oben genannten Vorgänge werden viel Zeit in Anspruch nehmen und es werden einige unnötige zusätzliche Vorgänge erforderlich sein. Einige davon können viele Male wiederholt werden, einige Vorgänge können sehr langsam ausgeführt werden usw. Hier können Abfragen sehr langsam sein. Der Zweck der Abfrageoptimierung besteht darin, den Zeitaufwand für diese Vorgänge zu reduzieren und zu eliminieren.

Durch die obige Analyse verfügen wir über ein umfassendes Verständnis des Abfrageprozesses und können klar erkennen, wo bei der Abfrage möglicherweise Probleme auftreten, die letztendlich dazu führen, dass die gesamte Abfrage sehr langsam ist, was eine Richtung für die tatsächliche Abfrageoptimierung vorgibt.

Mit anderen Worten: Die Abfrageoptimierung kann aus den folgenden zwei Perspektiven angegangen werden:

• Reduzieren Sie die Anzahl der Unterabfragen
• Reduzieren Sie zusätzliche und wiederholte Vorgänge

Ein häufiger Grund für eine geringe Abfrageleistung ist, dass auf zu viele Daten zugegriffen wird. Wenn die Datenmenge klein ist, ist die Abfragegeschwindigkeit ziemlich gut. Sobald die Datenmenge zunimmt, ändert sich die Abfragegeschwindigkeit drastisch, was die Leute verrückt macht und ihnen ein sehr schlechtes Erlebnis beschert. Zur Abfrageoptimierung können Sie unter folgenden Aspekten prüfen:

• Ob unnötige Daten abgefragt werden
• Ob zusätzliche Datensätze gescannt werden

2. Werden unnötige Daten abgefragt

Bei tatsächlichen Abfragen werden häufig die tatsächlich erforderlichen Daten abgefragt Anschließend werden die überschüssigen Daten von der Anwendung verworfen. Dies stellt einen zusätzlichen Overhead für MySQL dar und beansprucht außerdem die CPU- und Speicherressourcen des Anwendungsservers.
Einige typische Fälle sind wie folgt:

1. Abfragen unnötiger Datensätze

Dies ist ein häufiger Fehler, bei dem MySQL nur die erforderlichen Datensätze zurückgibt Daten aufzeichnet, tatsächlich gibt MySQL zuerst die gesamte Ergebnismenge zurück und führt dann Berechnungen durch.

Entwickler verwenden üblicherweise die SELECT-Anweisung, um eine große Anzahl von Ergebnissen abzufragen, und verwenden dann die Anwendungsabfrage oder die Front-End-Anzeigeschicht, um die vorherigen N Datenzeilen abzurufen. Fragen Sie beispielsweise 100 Datensätze auf einer Nachrichten-Website ab , aber nur auf der Seite.

Die effektivste Lösung besteht darin, so viele Datensätze abzufragen, wie Sie benötigen. LIMIT wird normalerweise nach der Abfrage hinzugefügt, das heißt: Paging-Abfrage.

2. Geben Sie alle Spalten zurück, wenn mehrere Tabellen verknüpft sind

Wenn Sie alle Schauspieler abfragen möchten, die im Film Academy Dinosaur aufgetreten sind, führen Sie die Abfrage nicht auf die folgende Weise durch:

select * fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

Dadurch werden alle Datenspalten der drei Tabellen zurückgegeben. Die eigentliche Anforderung besteht darin, die Akteurinformationen abzufragen:

select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

3. 总是查询出全部列

每次看到select *的时候一定要用异样的目光来审视它，是不是真的需要返回全部数据列？

在大部分情况下，是不需要的。 select *会导致进行全表扫描，会让优化器无法完成索引扫描这类优化，过多的列还会为服务器带来额外的I/O、内存和CPU的消耗。 即使真的需要查询出全部列，应该逐个罗列出全部列而不是*。

4. 重复查询相同的数据

如果你不太留意，很容易出现这样的错误： 不断地重复执行相同的查询，然后每次都返回完全相同的数据。

例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复来查询这个数据。 比较好处理方法是，在初次查询的时候将这个数据缓存起来，后续使用时直接从缓存中取出。

三、是否扫描了额外的记录

确 定查询只查询了需要的数据以后，接下来应该看看查询过程中是否扫描了过多的数据。 对于MySQL，最简单衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

没有哪个指标能够完全来衡量查询的开销，但它们能够大致反映MySQL内部执行查询时需要访问多少数据，并可以大概推算出查询运行的实际。 这三个指标都会记录到MySQL的慢日志中，所以 检查慢日志记录是找出扫描行数过多查询的办法 。

慢查询： 用于记录在MySQL中响应时间超过阈值(long_query_time,默认10s)的语句，并会将慢查询记录到慢日志中。 可通过变量slow_query_long来开启慢查询，默认是关闭状态，可以将慢日志记录到表slow_log或文件中，以供检查分析。

1. 响应时间

响应时间是两个部分之和： 服务时间和排队时间。 服务时间是指数据库处理这个查询真正花费了多长时间。 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间，可能是等待I/O操作，也可能是等待 行 锁等等。

在不同类型的应用压力下，响应时间并没有什么一致的规律或者公式。 诸如存储引擎的锁（表锁，行锁），高并发资源竞争，硬件响应等诸多因素都会影响响应时间，所以，响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。

2. 扫描的行数和返回的行数

在分析查询时，查看该查询扫描的行数是非常有帮助的，在此之上也能够分析是否扫描了额外的记录。

对于找出那些糟糕查询，这个指标可能还不够完美，因为并不是所有行的访问代价都是相同的。 较短的行的访问速度相当快，内存中的行也比磁盘中的行的访问速度要快的多。

理想的情况下，扫描的行数和返回的行数应该是相同的。 但实际上这种美事并不多，例如在做一个关联查询的时候，扫描的行数和对返回的行数的比率通常都很小，一般在1:1和10：1之间，不过有时候这个值也可能非常大。

3. 扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。 MySQL有好几种访问方式可以查找并返回一行结果。 这些访问方式可能需要访问很多行才能返回一条结果，也有些访问方式可能无需扫描就能返回结果。

在执行计划EXPLAIN语句中的type列反映了访问类型。 访问类型有很多种，从全表扫描到索引扫描，范围扫描，唯一索引，常数索引等。 这里列的这些，速度是从慢到快，扫描的行数也是从多到少。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，这也是我们之前讨论索引的问题。 现在应该明白为什么索引对于查询优化如此重要了。 索引让MySQL以最高效，扫描行数最少的方式找到需要的记录 。

如果发现查询扫描了大量的数据但只返回少数的行，通常可以尝试下面的技巧去优化它：

• Verwenden Sie das Scannen der Indexabdeckung, um alle erforderlichen Spalten in den Index aufzunehmen, sodass die Speicher-Engine Ergebnisse zurückgeben kann, ohne zur Tabelle zurückkehren zu müssen, um die entsprechenden Zeilen abzurufen.
  
• Optimieren Sie die Tabellenstruktur. Verwenden Sie beispielsweise eine separate Übersichtstabelle, um die Abfrage abzuschließen.
• Komplexe Abfragen neu schreiben, damit der MySQL-Optimierer die Abfrage optimierter ausführen kann.

Verwandte Empfehlungen: Programmiervideokurs

Das obige ist der detaillierte Inhalt vonErfahren Sie, wie MySQL die Abfragegeschwindigkeit optimiert. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!