Comment optimiser les performances des requêtes de base de données ? (explication détaillée)

Comment optimiser les performances des requêtes de base de données ? (Explication détaillée)

La requête est l'opération la plus couramment utilisée dans la technologie des bases de données. Le processus d'opération de requête est relativement simple.Tout d'abord, l'instruction SQL de requête est émise par le client. Après avoir reçu l'instruction SQL envoyée par le client, le serveur de base de données exécute l'instruction SQL, puis renvoie les résultats de la requête au client. Bien que le processus soit très simple, différentes méthodes de requête et paramètres de base de données auront un impact important sur les performances des requêtes.

Par conséquent, cet article traite des techniques d'optimisation des requêtes couramment utilisées dans MySQL. Les contenus abordés incluent : l'amélioration de la vitesse des requêtes grâce à la mise en mémoire tampon des requêtes ; l'optimisation automatique des requêtes par MySQL ; la détection des requêtes inaccessibles et l'utilisation de diverses options de requête pour améliorer les performances.

1. Améliorer la vitesse des requêtes grâce à la mise en mémoire tampon des requêtes
Généralement, lorsque nous utilisons des instructions SQL pour interroger, le serveur de base de données exécute cette instruction à chaque fois qu'il reçoit une instruction SQL du client. . Mais lorsque la même instruction SQL est reçue dans un certain intervalle (par exemple dans une minute), elle sera exécutée de la même manière. Bien que cela puisse garantir la nature en temps réel des données, la plupart du temps, les données ne nécessitent pas un temps réel complet, ce qui signifie qu'il peut y avoir un certain retard. Si tel est le cas, exécuter exactement le même SQL dans un court laps de temps n’en vaut pas la peine.
Heureusement, MySQL nous fournit la fonction de mise en mémoire tampon des requêtes (la mise en mémoire tampon des requêtes ne peut être utilisée que dans MySQL 4.0.1 et supérieur). Nous pouvons améliorer les performances des requêtes dans une certaine mesure grâce à la mise en mémoire tampon des requêtes.

Nous pouvons définir le tampon de requête via le fichier my.ini dans le répertoire d'installation de MySQL. Le réglage est également très simple, il suffit de définir query_cache_type sur 1. Après avoir défini cet attribut, avant que MySQL n'exécute une instruction SELECT, il vérifiera dans son tampon si la même instruction SELECT a été exécutée. Si c'est le cas et que le résultat de l'exécution n'a pas expiré, alors le résultat de la requête sera renvoyé directement au client. Mais lors de l'écriture d'instructions SQL, veuillez noter que le tampon de requête de MySQL est sensible à la casse. Les deux instructions SELECT suivantes sont les suivantes :

SELECT * from TABLE1
SELECT * FROM TABLE1

Les deux instructions SQL ci-dessus sont des SELECT complètement différents pour la mise en mémoire tampon des requêtes. De plus, le cache de requêtes ne gère pas automatiquement les espaces. Par conséquent, lors de l'écriture d'instructions SQL, vous devez essayer de réduire l'utilisation des espaces, en particulier les espaces au début et à la fin de SQL (car le cache de requêtes n'intercepte pas automatiquement les espaces au début et à la fin de SQL). le début et la fin).

Bien que ne pas configurer de tampon de requête puisse parfois entraîner des pertes de performances, certaines instructions SQL doivent interroger les données en temps réel ou ne sont pas utilisées fréquemment (peut-être exécutées une ou deux fois par jour). Cela nécessite de désactiver la mise en mémoire tampon. Bien sûr, il est possible de désactiver le cache de requêtes en définissant la valeur de query_cache_type, mais cela désactivera définitivement le cache de requêtes. MySQL 5.0 fournit une méthode pour désactiver temporairement le tampon de requête :

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

L'instruction SQL ci-dessus utilise SQL_NO_CACHE, donc que cette instruction SQL ait été exécutée ou non, le serveur ne regardera pas dans le tampon et exécutez-le à chaque fois.
Nous pouvons également définir query_cache_type dans my.ini sur 2, de sorte que le cache de requêtes ne soit utilisé qu'après l'utilisation de SQL_CACHE.

SELECT SQL_CALHE * FROM TABLE1

2. L'optimisation automatique des requêtes par MySQL

Les indices sont très importants pour la base de données. Les index peuvent être utilisés pour améliorer les performances lors des requêtes. Mais parfois, l’utilisation d’index réduira les performances. Nous pouvons consulter le tableau SALES suivant :

CREATE TABLE SALES
(
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
PRICE FLOAT NOT NULL,
SALE_COUNT INT NOT NULL,
SALE_DATE DATE NOT NULL,
PRIMARY KEY(ID)，
INDEX (NAME)，
INDEX (SALE_DATE)
)

Supposons que des millions de données soient stockées dans ce tableau et que nous souhaitons interroger le prix moyen du produit portant le numéro de produit 1000 en 2004 et 2005. On peut écrire l'instruction SQL suivante :

SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';

Si la quantité de ce produit est très importante, elle représente près de 50 % ou plus des enregistrements de la table SALES. Ensuite, utiliser l'index sur le champ SALE_DATE pour calculer la moyenne est un peu lent. Parce que si vous utilisez un index, vous devez trier l'index. Lorsqu'un très grand nombre d'enregistrements remplissent les conditions (par exemple, représentant 50 % ou plus des enregistrements dans l'ensemble de la table), la vitesse ralentira, il est donc préférable d'analyser l'intégralité de la table. Par conséquent, MySQL décidera automatiquement d'utiliser ou non l'index pour la requête en fonction de la proportion de données qui remplissent les conditions dans l'ensemble de la table.

Pour MySQL, l'index n'est pas utilisé lorsque la proportion des résultats de la requête ci-dessus par rapport aux enregistrements de la table entière est d'environ 30 %. Cette proportion est dérivée par les développeurs MySQL en fonction de leur expérience. Cependant, la valeur réelle du ratio variera en fonction du moteur de base de données utilisé

3. Tri basé sur un index

L'une des faiblesses de MySQL est son tri. Bien que MySQL puisse interroger environ 15 000 enregistrements en 1 seconde, MySQL ne peut utiliser qu'un seul index lors de l'interrogation. Par conséquent, si la condition WHERE occupe déjà l’index, celui-ci ne sera pas utilisé dans le tri, ce qui réduira considérablement la vitesse de la requête. Nous pouvons regarder l'instruction SQL suivante :

SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;

L'index sur le champ NAME a été utilisé dans la clause WHERE du SQL ci-dessus. Par conséquent, l'index ne sera plus utilisé lors du tri de SALE_DATE. . Afin de résoudre ce problème, nous pouvons créer un index composite sur la table SALES :

ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)

这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意，在使用这个方法时，要确保WHERE子句中没有排序字段，在上例中就是不能用SALE_DATE进行查询，否则虽然排序快了，但是SALE_DATE字段上没有单独的索引，因此查询又会慢下来。

四、 不可达查询的检测

在执行SQL语句时，难免会遇到一些必假的条件。所谓必假的条件是无论表中的数据如何变化，这个条件都为假。如WHERE value  200。我们永远无法找到一个既小于100又大于200的数。
如果遇到这样的查询条件，再去执行这样的SQL语句就是多此一举。幸好MySQL可以自动检测这种情况。如我们可以看看如下的SQL语句:

SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显，这是一个不可达的查询，WHERE条件一定是假。MySQL在执行SQL语句之前，会先分析WHERE条件是否是不可达的查询，如果是，就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用EXPLAIN进行测试:

EXPLAIN SELECT * FROM SALES WHERE NAME = “name1”

上面的查询是一个正常的查询，我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:

EXPLAIN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

我们可以看到，table项是空，这说明MySQL并没有对SALES表进行操作。

五、 使用各种查询选择来提高性能

SELECT语句除了正常的使用外，MySQL还为我们提供了很多可以增强查询性能的选项。如上面介绍的用于控制查询缓冲的SQL_NO_CACHE和SQL_CACHE就是其中两个选项。在这一部分，我将介绍几个常用的查询选项。

1. STRAIGHT_JOIN:强制连接顺序

当我们将两个或多个表连接起来进行查询时，我们并不用关心MySQL先连哪个表，后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估，最后得出的一个连接顺序决定的。如下列的SQL语句中，TABLE1和TABLE2并不一定是谁连接谁:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 ,TABLE2 WHERE …

如果开发人员需要人为地干预连接的顺序，就得使用STRAIGHT_JOIN关键字，如下列的SQL语句:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

2. 干预索引使用，提高性能

在上面已经提到了索引的使用。一般情况下，在查询时MySQL将自己决定是否使用索引，使用哪一个索引。但在一些特殊情况下，我们希望MySQL只使用一个或几个索引，或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。

限制使用索引的范围：

有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引，这就需要用到USE INDEX对查询语句进行设置。

SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …

从以上SQL语句可以看出，无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索引。

限制不使用索引的范围

如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNORE INDEX是选择不被考虑的索引。

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

在上面的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

强迫使用某一个索引

上面的两个例子都是给MySQL提供一个选择，也就是说MySQL并不一定要使用这些索引。而有时我们希望MySQL必须要使用某一个索引(由于MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

3. 使用临时表提供查询性能

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1

六、 结论
在程序设计中同样存在一个“二八原则”，即20%的代码用去了80%的时间。数据库应用程序的开发亦然。数据库应用程序的优化，重点在于SQL的执行效率。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。 

推荐教程：《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!