Optimierung der MySQL-Leistungsoptimierungsabfrage

Dieser Artikel vermittelt Ihnen relevantes Wissen über MySQL. Er stellt hauptsächlich verwandte Themen zur Leistungsoptimierung vor, einschließlich der Abfrageoptimierung. Ich hoffe, er wird für alle hilfreich sein.

Empfohlenes Lernen: MySQL-Video-Tutorial

Bevor Sie eine schnelle Abfrage schreiben, müssen Sie sich darüber im Klaren sein, dass es wirklich auf die Antwortzeit ankommt, und Sie müssen wissen, wie viel Zeit jeder Schritt während der Ausführung benötigt Gesamte SQL-Anweisung Wie lange dauert es, um herauszufinden, welche Schritte die Ausführungseffizienz verlangsamen? Dazu müssen Sie den Lebenszyklus der Abfrage kennen und sie dann optimieren. Seien Sie nicht verallgemeinert. Eine detaillierte Analyse der Situation.

1. Gründe für langsame Abfrage

3. Kontextwechsel

6

2. Optimieren Sie den Datenzugriff

1. Der Hauptgrund für die geringe Abfrageleistung ist, dass auf einige Abfragen zwangsläufig große Datenmengen gefiltert werden müssen. 1) Bestätigen Sie, ob die Anwendung mehr Datenmengen als nötig abruft

(2) Bestätigen Sie, ob die MySQL-Serverschicht mehr Datenzeilen als nötig analysiert

2 Ob unnötige Daten aus der Datenbank angefordert werden

(1) Die Abfrage erfordert keine erforderlichen Datensätze (wir glauben oft fälschlicherweise, dass MySQL nur die erforderlichen Daten zurückgibt. Tatsächlich gibt MySQL zuerst alle Ergebnisse zurück und führt dann Berechnungen durch. In den täglichen Entwicklungsgewohnheiten verwenden wir häufig Select-Anweisungen zum Abfragen eine große Anzahl von Ergebnissen und erhalten Sie dann die Ergebnismenge nach den ersten N Zeilen. Die Optimierungsmethode besteht darin, nach der Abfrage ein Limit hinzuzufügen.)

(2) Alle Spalten zurückgeben, wenn mehrere Tabellen verknüpft sind (wählen Sie * aus dem inneren Join des Akteurs aus film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';selectactor.* fromactor...;)

(3) Entfernen Sie immer alle Spalten (in den Unternehmensanforderungen des Unternehmens die Die Verwendung von select * ist verboten. Obwohl diese Methode die Entwicklung vereinfachen kann, wirkt sie sich jedoch auf die Leistung der Abfrage aus. Versuchen Sie daher, sie nicht zu verwenden.

(4) Fragen Sie wiederholt dieselben Daten ab (wenn Sie dieselben wiederholt ausführen müssen). Fragen Sie jedes Mal genau die gleichen Daten ab und geben Sie sie zurück. Basierend auf solchen Anwendungsszenarien können wir diesen Teil der Daten zwischenspeichern, was die Abfrageeffizienz verbessern kann.)

3 Optimierung des Ausführungsprozesses

1

Wenn der Abfragecache aktiviert ist, prüft MySQL vor dem Parsen einer Abfrageanweisung zunächst, ob die Abfrage auf die Daten im Abfragecache trifft. Wenn die Abfrage zufällig auf den Abfragecache trifft, werden zuvor die Benutzerberechtigungen überprüft Rückgabe der Ergebnisse. Wenn es kein Problem mit den Berechtigungen gibt, überspringt MySQL alle Phasen und ruft die Ergebnisse direkt aus dem Cache ab und gibt sie an den Client zurück

2 Abfrageoptimierungsverarbeitung

Nachdem MySQL den Cache abgefragt hat, Es werden die folgenden Schritte durchlaufen: SQL analysieren, Vorverarbeitung durchführen, SQL-Ausführungsplan optimieren. Diese Schritte werden angezeigt. Jeder Fehler kann zum Abbruch der Abfrage führen.

(1) Grammatikparser und Vorverarbeitung

MySQL analysiert die SQL-Anweisung anhand von Schlüsselwörtern und generiert einen Analysebaum. Der MySQL-Parser verwendet MySQL-Grammatikregeln, um die Abfrage zu überprüfen und zu analysieren, z. B. die Verwendung falscher Schlüsselwörter oder die Reihenfolge korrekt sind usw., prüft der Präprozessor außerdem, ob der Analysebaum zulässig ist, z. B. ob der Tabellenname und der Spaltenname vorhanden sind, ob Unklarheiten vorliegen, und überprüft auch Berechtigungen usw. (2) Abfrage Optimierer

Wenn der Syntaxbaum keine Probleme aufweist, konvertiert der Optimierer ihn in einen Ausführungsplan. Die entsprechenden Ergebnisse können jedoch am Ende unterschiedlich sein Die Ausführungsmethoden sind unterschiedlich. Der Hauptzweck des Prozessors besteht darin, den effektivsten Ausführungsplan auszuwählen.

MySQL verwendet einen kostenbasierten Optimierer. Während der Optimierung versucht es, die Kosten einer Abfrage mithilfe eines bestimmten Abfrageplans vorherzusagen und den Plan mit den geringsten Kosten auszuwählen. a. select count(*) from film_actor; Come.

(a) Die Anzahl der Seiten in jeder Tabelle oder jedem Index

(b) Die Kardinalität des Index

(c) Die Länge des Index und der Daten Zeilen

(d) Die Verteilung des Index

b In vielen Fällen wählt MySQL aus folgenden Gründen den falschen Ausführungsplan:

(a) Ungenaue statistische Informationen (InnoDB kann keine genauen statistischen Informationen über die Anzahl der Zeilen verwalten Zeilen in einer Datentabelle aufgrund der MVCC-Architektur)

(b) Die Kostenschätzung des Ausführungsplans entspricht nicht den tatsächlichen Ausführungskosten (manchmal sind die Kosten geringer, obwohl ein Ausführungsplan mehr Seiten lesen muss, denn wenn diese Seiten nacheinander gelesen werden oder diese Seiten bereits gelesen werden im Speicher, dann sind die Zugriffskosten sehr gering. Die MySQL-Ebene weiß nicht, welche Seiten sich im Speicher und welche auf der Festplatte befinden, daher ist es unmöglich zu wissen, wie viele E/A-Zeiten während der Abfrageausführung erforderlich sind.)

(c ) Der optimale Wert von MySQL kann von Ihrer Meinung abweichen (die Optimierung von MySQL basiert auf der Kostenmodelloptimierung, ist aber möglicherweise nicht die schnellste Optimierung)

(d) MySQL berücksichtigt keine anderen gleichzeitig ausgeführten Abfragen

( e) MySQL berücksichtigt keine Betriebskosten, die nicht unter seiner Kontrolle stehen (die Kosten für die Ausführung gespeicherter Prozeduren oder benutzerdefinierter Funktionen)

c Optimierungsstrategie des Optimierers

(a) Statische Optimierung (direkte Analyse des Analysebaums). ) und schließen Sie die Optimierung ab)

(b) Dynamische Optimierung (dynamische Optimierung bezieht sich auf den Kontext der Abfrage und kann sich auch auf den Wert und die Anzahl der Zeilen beziehen, die dem Index entsprechen)

(c) MySQL Die Abfrage muss nur einmal statisch optimiert werden, die dynamische Optimierung muss jedoch bei jeder Ausführung neu bewertet werden, und der Optimierungstyp des Optimierers

(a) Definieren Sie die Reihenfolge verwandter Tabellen neu (die Zuordnung von Datentabellen nicht). Wird immer in der in der Abfrage angegebenen Reihenfolge ausgeführt. Eine sehr wichtige Funktion des Optimierers bei der Bestimmung der Reihenfolge von Assoziationen.)

(b) Konvertieren Sie äußere Verknüpfungen in innere Verknüpfungen Äquivalente Transformationsregeln, MySQL kann einige Äquivalente verwenden. Änderungen zum Vereinfachen und Planen von Ausdrücken

(d) Optimieren Sie count(), min(), max() (Index und ob die Spalte null sein kann, kann MySQL oft dabei helfen, diese Art von Ausdruck zu optimieren : Um beispielsweise den Mindestwert einer bestimmten Spalte zu ermitteln, müssen Sie nur den Datensatz ganz links im Index abfragen, ohne dass ein Volltextscan und -vergleich erforderlich ist.)

(e) Schätzen Sie ihn und konvertieren Sie ihn in einen konstanten Ausdruck. Wenn MySQL erkennt, dass ein Ausdruck in eine Konstante konvertiert werden kann, wird der Ausdruck immer als Konstante behandelt. (Erklären Sie „select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1)

(f) Indexabdeckungsscan, wenn die Spalten im Index alle Spalten enthalten, die verwendet werden müssen.“ Für die Abfrage können Sie den Covering-Index verwenden

(g) Unterabfrageoptimierung (MySQL kann in einigen Fällen Unterabfragen in eine effizientere Form konvertieren, wodurch mehrere Abfragen reduziert werden, die mehrmals auf die Daten zugreifen, z. B. häufig abgefragte Daten werden in den Cache gestellt . )

(h) Äquivalente Weitergabe (wenn die Werte zweier Spalten durch Gleichheit miteinander verbunden sind, kann MySQL die Where-Bedingung einer Spalte an die andere übergeben:

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

Hier verwenden Das Feld film_id ist äquivalent verknüpft. Die Spalte film_id gilt nicht nur für die Filmtabelle, sondern auch für die Tabelle film_actor Es ist sehr wichtig, dass alle Assoziationen verschachtelte Schleifenassoziationsoperationen durchführen. Das heißt, MySQL führt zunächst ein einzelnes Datenelement in einer Tabelle aus und verschachtelt es dann in der nächsten Tabelle, um übereinstimmende Zeilen usw. zu finden, bis insgesamt übereinstimmende Verhaltensweisen vorliegen Die übereinstimmenden Zeilen in der Tabelle geben die in der Abfrage erforderlichen Spalten zurück. Wenn in der letzten verknüpften Tabelle keine weiteren Zeilen gefunden werden können, kehrt MySQL zu zurück Überprüfen Sie, ob weitere passende Datensätze gefunden werden können. Die Gesamtidee ist dieselbe, aber bitte beachten Sie, dass es im tatsächlichen Ausführungsprozess mehrere Varianten gibt:

f, Sortieroptimierung

Egal Was, Sortieren ist ein sehr kostenintensiver Vorgang, daher sollte das Sortieren so weit wie möglich vermieden werden oder große Datenmengen so weit wie möglich sortiert werden.
Es wird empfohlen, zum Sortieren Indizes zu verwenden Der Index kann nicht verwendet werden. Wenn die Datenmenge klein ist, wird sie im Speicher durchgeführt. Dies wird als Dateisortierung in MySQL bezeichnet Die zu sortierende Datenmenge ist kleiner als der Sortierpuffer (Variablen wie „%sort_buffer_size%“ anzeigen;). Wenn der Speicher nicht zum Sortieren ausreicht, teilt MySQL die Daten zunächst auf Baum in Blöcke, sortieren Sie jeden unabhängigen Block mithilfe der Schnellsortierung, speichern Sie die Sortierergebnisse jedes Blocks auf der Festplatte und sortieren Sie dann jeden Block. Die wohlgeordneten Blöcke werden zusammengeführt, und schließlich wird das Sortierergebnis zurückgegeben Sortieralgorithmus:

(a) Sortierung mit zwei Übertragungen

Beim ersten Lesen der Daten werden die Felder gelesen, die sortiert werden müssen, und beim zweiten Mal werden die Datenzeilen nach Bedarf nach den sortierten Ergebnissen gelesen.

Diese Methode ist relativ ineffizient, da beim zweiten Lesen der Daten alle Datensätze gelesen werden müssen. Zu diesem Zeitpunkt werden mehr zufällige E/A verwendet und die Kosten für das Lesen der Daten steigen höher sein

zweimal Der Vorteil der Übertragung besteht darin, beim Sortieren so wenig Daten wie möglich zu speichern, damit der Sortierpuffer möglichst viele Zeilen für Sortiervorgänge aufnehmen kann

(b) Sortierung mit einfacher Übertragung

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

（c）如何选择

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

四、优化特定类型的查询

1、优化count()查询

count()是特殊的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数。

（1）总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的。

（2）使用近似值

在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值
其实在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。

（3）更复杂的优化

一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

2、优化关联查询

（1）确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序

当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引。

（2）确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

3、优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替

4、优化limit分页

在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的话需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。
要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能。

优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列。

select film_id,description from film order by title limit 50,5;
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

5、优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化

除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高。

6、推荐使用用户自定义变量

用户自定义变量是一个容易被遗忘的mysql特性，但是如果能够用好，在某些场景下可以写出非常高效的查询语句，在查询中混合使用过程化和关系话逻辑的时候，自定义变量会非常有用。
用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都存在。

（1）自定义变量的使用

set @one :=1
set @min_actor :=(select min(actor_id) from actor)
set @last_week :=current_date-interval 1 week;

（2）自定义变量的限制

a、无法使用查询缓存

b、不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句

c、用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信

d、不能显式地声明自定义变量地类型

e、mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行

f、赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号。

g、使用未定义变量不会产生任何语法错误。

（3）自定义变量的使用案例

a、优化排名语句

在给一个变量赋值的同时使用这个变量

select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;

查询获取演过最多电影的前10名演员，然后根据出演电影次数做一个排名

select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;

b、避免重新查询刚刚更新的数据

当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么

update t1 set  lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;
update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;

c、确定取值的顺序

在赋值和读取变量的时候可能是在查询的不同阶段

（a）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;

因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期

（b）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name

当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的  。

（c）解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：

set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;

推荐学习：mysql视频教程

Das obige ist der detaillierte Inhalt vonOptimierung der MySQL-Leistungsoptimierungsabfrage. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!