Découvrez comment MySQL optimise la vitesse des requêtes

Dans les chapitres précédents, nous avons présenté comment choisir des types de données optimisés et comment utiliser efficacement les index, qui sont essentiels pour MySQL hautes performances. Mais cela ne suffit pas, et une conception raisonnable des requêtes est également nécessaire. Si la requête est mal écrite, aussi raisonnable que soit la structure de la table et l'adéquation de l'index, elle ne pourra pas atteindre des performances élevées.

En matière d'optimisation des performances MySQL, l'optimisation des requêtes est la source de l'optimisation, et elle peut également mieux refléter si un système est plus rapide. Ce chapitre et les chapitres suivants se concentreront sur l'optimisation des performances des requêtes et présenteront quelques techniques d'optimisation des requêtes pour vous aider à mieux comprendre comment MySQL exécute réellement les requêtes, où il est lent et comment le rendre plus rapide, et comprendre le. raisons de haute efficacité et d'inefficacité, qui vous aideront à mieux optimiser les instructions SQL de requête.

Recommandations d'apprentissage associées : Tutoriel vidéo MySQL

Ce chapitre commence par "Pourquoi la vitesse des requêtes est-elle si lente", afin que vous puissiez clairement comprendre Cela vous aidera à mieux optimiser la requête et à savoir où la requête peut être lente, afin que vous puissiez avoir une longueur d'avance.

1. Où est la lenteur ?

La véritable mesure de la vitesse des requêtes est le temps de réponse. Si vous considérez une requête comme une tâche, elle consiste en une série de sous-tâches, dont chacune prend un certain temps. Si vous souhaitez optimiser une requête, vous souhaitez en fait optimiser ses sous-tâches, donc éliminer certaines de ces sous-tâches, puis réduire le nombre de fois où les sous-tâches sont exécutées ou accélérer l'exécution des sous-tâches.

Lorsque MySQL exécute une requête, quelles sous-tâches y a-t-il et quelles sous-tâches prennent le plus de temps ? Cela nécessite l'utilisation de certains outils ou méthodes (tels que des plans d'exécution) pour analyser la requête afin de localiser et découvrir où se situe la lenteur.

De manière générale, le cycle de vie d'une requête peut être grossièrement visualisé dans l'ordre : Du client au serveur, puis analysé sur le serveur, généré un plan d'exécution, exécuté et renvoyé les résultats au client . Parmi elles, « l'exécution » peut être considérée comme l'étape la plus importante de tout le cycle de vie, qui comprend un grand nombre d'appels au moteur de stockage pour récupérer les données et le traitement des données après les appels, y compris le tri, le regroupement, etc.

Lors de l'exécution de ces tâches, la requête doit passer du temps à différents endroits à différentes étapes, notamment le réseau, les calculs du processeur, la génération de statistiques et de plans d'exécution, l'attente de verrouillage et d'autres opérations, notamment la récupération de données du moteur de stockage sous-jacent. Ces appels nécessitent des opérations de mémoire, des opérations sur le processeur et peuvent également générer un grand nombre de changements de contexte et d'appels système.

Les opérations ci-dessus prendront beaucoup de temps et il y aura des opérations supplémentaires inutiles. Certaines d'entre elles pourront être répétées plusieurs fois, certaines opérations seront exécutées très lentement, etc. C'est là que les requêtes peuvent vraiment être lentes. Le but de l'optimisation des requêtes est de réduire et d'éliminer le temps passé sur ces opérations .

Grâce à l'analyse ci-dessus, nous avons une compréhension globale du processus de requête et pouvons clairement savoir où la requête peut rencontrer des problèmes, ce qui finira par ralentir l'ensemble de la requête, fournissant ainsi une orientation pour l'optimisation réelle de la requête. .

En d'autres termes, l'optimisation des requêtes peut être abordée sous les deux angles suivants :

• Réduire le nombre de sous-requêtes
• Réduire les opérations supplémentaires et répétées

Une raison courante des mauvaises performances des requêtes est l'accès à un trop grand nombre de données. Lorsque la quantité de données est faible, la vitesse de requête est plutôt bonne. Une fois que la quantité de données augmente, la vitesse de requête change radicalement, rendant les gens fous et leur offrant une très mauvaise expérience. Pour l'optimisation des requêtes, vous pouvez vérifier sous les aspects suivants :

• Si des données inutiles sont interrogées
• Si des enregistrements supplémentaires sont analysés

2. Si des données inutiles sont interrogées

Dans les requêtes réelles, plusieurs fois, les données réelles requises seront interrogées, puis ces données redondantes seront utilisées par l'application. Il s'agit d'une surcharge supplémentaire pour MySQL et consomme également les ressources CPU et mémoire du serveur d'applications.
Certains cas typiques sont les suivants :

1. Interroger les enregistrements inutiles

C'est une erreur courante que MySQL ne renvoie que les enregistrements requis. En fait, MySQL renvoie d'abord l'ensemble des résultats, puis effectue les calculs.

Les développeurs utilisent habituellement l'instruction SELECT pour interroger un grand nombre de résultats, puis utilisent la requête d'application ou la couche d'affichage frontale pour obtenir les N lignes de données précédentes. Par exemple, interroger 100 enregistrements dans une actualité. site Web, mais uniquement dans Les 10 premiers éléments sont affichés sur la page.

La solution la plus efficace consiste à interroger autant d'enregistrements que nécessaire. LIMIT est généralement ajouté après la requête, c'est-à-dire : requête de pagination.

2. Renvoyez toutes les colonnes lors de la corrélation de plusieurs tables

Si vous souhaitez interroger tous les acteurs apparus dans le film Academy Dinosaur, ne le faites pas de la manière suivante Requête :

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';

Cela renverra toutes les colonnes de données des trois tables. L'exigence réelle est d'interroger les informations sur l'acteur. La méthode d'écriture correcte doit être :

.

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以最高效，扫描行数最少的方式找到需要的记录 。

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

• Utilisez l'analyse de couverture d'index pour placer toutes les colonnes requises dans l'index, afin que le moteur de stockage puisse renvoyer les résultats sans revenir à la table pour obtenir les lignes correspondantes.
  
• Optimisez la structure du tableau. Par exemple, utilisez un tableau récapitulatif distinct pour terminer la requête.
• Réécrivez les requêtes complexes afin que l'optimiseur MySQL puisse exécuter la requête de manière plus optimisée.

Recommandations associées : Cours vidéo de programmation

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!