Optimisation des requêtes de réglage des performances MySQL

Cet article vous apporte des connaissances pertinentes sur mysql. Il présente principalement des problèmes liés à l'optimisation des performances, y compris l'optimisation des requêtes et d'autres contenus. J'espère qu'il sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo MySQL

Avant d'écrire une requête rapide, vous devez être clair sur le fait que ce qui compte vraiment est le temps de réponse, et vous devez savoir combien de temps prend chaque étape lors de l'exécution de la requête. instruction SQL entière Combien de temps faut-il pour savoir quelles sont les étapes clés qui ralentissent l'efficacité de l'exécution ? Pour ce faire, vous devez connaître le cycle de vie de la requête, puis l'optimiser. Différents scénarios d'application ont des méthodes d'optimisation différentes. Ne généralisez pas. Soyez précis. Une analyse détaillée de la situation.

1. Raisons de la requête lente

1. Réseau

2. CPU

3. Changement de contexte

5.

2. Optimiser l'accès aux données

1. La principale raison des faibles performances des requêtes est que certaines requêtes doivent inévitablement filtrer une grande quantité de données. Nous pouvons optimiser en réduisant la quantité de données consultées

(. 1 ) Confirmez si l'application récupère une grande quantité de données plus que nécessaire

(2) Confirmez si la couche serveur MySQL analyse une grande quantité de lignes de données plus que nécessaire

2 Si des données inutiles sont demandées à la base de données.

(1) La requête ne nécessite pas d'enregistrements (nous pensons souvent à tort que MySQL ne renverra que les données requises. En fait, MySQL renvoie d'abord tous les résultats, puis effectue des calculs. Dans les habitudes de développement quotidiennes, nous utilisons souvent des instructions select pour interroger un grand nombre de résultats, puis obtenez Fermez l'ensemble de résultats après les N premières lignes. La méthode d'optimisation consiste à ajouter une limite après la requête)

(2) Renvoie toutes les colonnes lorsque plusieurs tables sont associées (sélectionnez * dans la jointure interne de l'acteur film_actor using (actor_id) inner join film using (film_id) Where film.title='Academy Dinosaur';select Actor.* from Actor...;)

(3) Supprimez toujours toutes les colonnes (dans les exigences d'entreprise de l'entreprise, le l'utilisation de select * est interdite, bien que cette méthode puisse simplifier le développement, mais elle affectera les performances de la requête, alors essayez de ne pas l'utiliser)

(4) Interrogez à plusieurs reprises les mêmes données (si vous devez exécuter la même chose à plusieurs reprises interroger et renvoyer exactement les mêmes données à chaque fois, par conséquent, sur la base de tels scénarios d'application, nous pouvons mettre en cache cette partie des données, ce qui peut améliorer l'efficacité des requêtes.)

3. Optimisation du processus d'exécution

1.

Avant d'analyser une instruction de requête, si le cache de requêtes est activé, mysql vérifiera d'abord si la requête atteint les données dans le cache de requêtes. Si la requête atteint le cache de requêtes, les autorisations de l'utilisateur seront vérifiées avant. renvoyer les résultats. S'il n'y a pas de problème avec les autorisations, alors mysql ignorera toutes les étapes et obtiendra directement les résultats du cache et les renverra au client

2. Traitement d'optimisation des requêtes

Après que mysql ait interrogé le cache, il passera par les étapes suivantes : analyse SQL, prétraitement, optimisation du plan d'exécution SQL, ces étapes apparaissent. Toute erreur peut mettre fin à la requête.

(1) Analyseur de grammaire et prétraitement

mysql analyse l'instruction SQL à l'aide de mots-clés et génère un arbre d'analyse. L'analyseur mysql utilisera les règles de grammaire mysql pour vérifier et analyser la requête, par exemple pour vérifier l'utilisation de mauvais mots-clés. ou l'ordre est correct, etc., le préprocesseur vérifiera en outre si l'arbre d'analyse est légal, par exemple si le nom de la table et le nom de la colonne existent, s'il y a une ambiguïté, et vérifiera également les autorisations, etc. (2) Requête Optimiseur

Lorsque l'arbre syntaxique ne pose aucun problème, l'optimiseur le convertira en un plan d'exécution. Une instruction de requête peut utiliser de nombreuses méthodes d'exécution, et les résultats correspondants peuvent finalement être obtenus. Cependant, l'efficacité apportée par différentes. les méthodes d'exécution sont différentes. Optimisation L'objectif principal du processeur est de sélectionner le plan d'exécution le plus efficace.

MySQL utilise un optimiseur basé sur les coûts. Lors de l'optimisation, il tentera de prédire le coût d'une requête en utilisant un certain plan de requête et sélectionnera celui avec le coût le plus bas.

a. select count(*) from film_actor; Come.

(a) Le nombre de pages dans chaque table ou index

(b) La cardinalité de l'index

(c) La longueur de l'index et des données rows

(d) La distribution de l'index

b. Dans de nombreux cas, mysql choisira le mauvais plan d'exécution pour les raisons suivantes :

(a) Informations statistiques inexactes (InnoDB ne peut pas maintenir des informations statistiques précises sur le nombre de lignes dans une table de données en raison de son architecture mvcc)

(b) L'estimation du coût du plan d'exécution n'est pas égale au coût d'exécution réel (parfois même si un plan d'exécution doit lire plus de pages, son coût est plus petit, car si ces pages sont lues séquentiellement ou si ces pages sont déjà en mémoire, alors son coût d'accès sera très faible. Le niveau mysql ne sait pas quelles pages sont en mémoire et lesquelles sont sur le disque, il est donc impossible de savoir combien de fois IO sont nécessaires lors de l'exécution de la requête.)

(c ) La valeur optimale de MySQL peut être différente de ce que vous pensez (l'optimisation de MySQL est basée sur l'optimisation du modèle de coût, mais ce n'est peut-être pas l'optimisation la plus rapide)

(d) MySQL ne prend pas en compte les autres requêtes exécutées simultanément

( e) MySQL ne prendra pas en compte les coûts d'exploitation qui ne sont pas sous son contrôle (le coût d'exécution des procédures stockées ou des fonctions définies par l'utilisateur)

c Stratégie d'optimisation de l'optimiseur

(a) Optimisation statique (analyse directe de l'arbre d'analyse). ) , et terminer l'optimisation)

(b) Optimisation dynamique (l'optimisation dynamique est liée au contexte de la requête, et peut également être liée à la valeur et au nombre de lignes correspondant à l'index)

(c) MySQL il suffit d'optimiser statiquement la requête une seule fois, mais l'optimisation dynamique doit être réévaluée à chaque fois qu'elle est exécutée, et le type d'optimisation de l'optimiseur

(a) Redéfinir l'ordre des tables associées (l'association des tables de données n'est pas toujours effectué dans l'ordre spécifié dans la requête, une fonction très importante de l'optimiseur pour décider de l'ordre des associations)

(b) Convertir les jointures externes en jointures internes Les jointures internes sont plus efficaces que les jointures externes

(c) Utilisation. règles de transformation équivalentes, mysql peut utiliser quelques équivalents Modifications pour simplifier et planifier les expressions

(d) Optimiser count(), min(), max() (l'index et si la colonne peut être nulle peut souvent aider mysql à optimiser ce type d'expression : par exemple, pour trouver le minimum d'une certaine valeur de colonne, il vous suffit d'interroger l'enregistrement le plus à gauche de l'index, pas besoin d'analyse et de comparaison en texte intégral)

(e) Estimez-le et convertissez-le en une expression constante. Lorsque MySQL détecte qu'une expression peut être convertie en constante, l'expression est toujours traitée comme une constante. (expliquez sélectionnez film.film_id,film_actor.actor_id à partir de la jointure interne du film film_actor en utilisant (film_id) où film.film_id = 1)

(f) Analyse de la couverture de l'index, lorsque les colonnes de l'index contiennent toutes les colonnes qui doivent être utilisées dans la requête, vous pouvez utiliser l'index de couverture

(g) optimisation des sous-requêtes (mysql peut convertir les sous-requêtes en une forme plus efficace dans certains cas, réduisant ainsi plusieurs requêtes accédant aux données plusieurs fois, comme les requêtes fréquemment interrogées. Les données sont mises dans le cache . )

(h) Propagation équivalente (si les valeurs de deux colonnes sont liées par égalité, alors mysql peut transmettre la condition Where d'une colonne à l'autre :

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

Utilisez ici Le champ film_id est lié de manière équivalente. La colonne film_id est non seulement applicable à la table film mais aussi à la table film_actor

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

e, requête associée

la requête associée de mysql est très importante, mais en fait, la stratégie d'exécution des requêtes associées est relativement simple : mysql est très important pour toutes les associations, toutes effectuent des opérations d'association de boucles imbriquées, c'est-à-dire que MySQL boucle d'abord une seule donnée dans une table, puis l'imbrique dans la table suivante pour trouver les lignes correspondantes, et ainsi de suite, jusqu'à ce que les comportements correspondent dans tous les tables sont trouvées. Les lignes correspondantes dans la table renverront les colonnes requises dans la requête. MySQL essaiera de trouver toutes les lignes correspondantes dans la dernière table associée. Si aucune autre ligne ne peut être trouvée dans la dernière table associée, MySQL reviendra à. le tableau associé au niveau précédent. Vérifiez si d'autres enregistrements correspondants peuvent être trouvés, et ainsi de suite. L'idée générale est la suivante, mais veuillez noter qu'il existe plusieurs variantes dans le processus d'exécution réel :

f, optimisation du tri

Quoi qu'il en soit. , le tri est une opération très coûteuse, donc du point de vue des performances, le tri doit être évité autant que possible ou de grandes quantités de données doivent être triées autant que possible

Il est recommandé d'utiliser des index pour le tri, mais lorsque le tri est effectué. L'index ne peut pas être utilisé, MySQL doit le trier lui-même. Si la quantité de données est petite, cela sera effectué en mémoire. Si la quantité de données est importante, le disque sera utilisé dans MySQL. la quantité de données à trier est inférieure au tampon de tri (afficher les variables comme '%sort_buffer_size%';), mysql. Utilisez la mémoire pour effectuer des opérations de tri rapides, si la mémoire n'est pas suffisante pour le tri, alors MySQL divisera d'abord l'arborescence. en blocs, triez chaque bloc indépendant à l'aide du tri rapide, stockez les résultats du tri de chaque bloc sur le disque, puis triez chaque bloc. Les blocs bien ordonnés sont fusionnés et enfin le résultat du tri est renvoyé. :

(a) Tri à deux transmissions

La première lecture des données consiste à lire les champs qui doivent être triés, puis à les trier, la deuxième fois consiste à lire les lignes de données en fonction des résultats triés selon les besoins.

Cette méthode est relativement inefficace. La raison en est que lors de la deuxième lecture des données, parce qu'elles ont été triées, tous les enregistrements doivent être lus à ce moment-là, davantage d'E/S aléatoires sont utilisées, et le coût de lecture des données sera plus élevé. être plus élevé

deux fois L'avantage de la transmission est de stocker le moins de données possible lors du tri, afin que le tampon de tri puisse accueillir autant de lignes que possible pour les opérations de tri

(b) Tri à transmission unique

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序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视频教程

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!