Résumé de 5 instructions de requête de limite d'optimisation MySQL

Cet article présente principalement 5 méthodes d'optimisation de l'instruction mysql limitquery. Il s'agit de la méthode d'optimisation de sous-requête, de la méthode d'optimisation de table inversée, de la méthode d'optimisation de recherche inversée, de la méthode d'optimisation de limite et de la vérification uniquement de l'index . méthode. Les amis qui en ont besoin peuvent se référer à la

pagination de mysql qui est relativement simple. Vous n'avez besoin que de la limite de décalage et de la longueur pour obtenir les données, mais lorsque le décalage est la longueur. est relativement important, les performances de MySQL diminuent évidemment

1 Méthode d'optimisation des sous-requêtes

Trouvez d'abord la première donnée, puis trouvez les données supérieures à. ou égal à cette donnée L'identifiant est la donnée à obtenir
Inconvénients : Les données doivent être continues On peut dire qu'il ne peut pas y avoir de condition Where La condition Where filtrera les données et provoquera l'obtention des données. perdre la continuité. Pour la méthode spécifique, veuillez consulter l'exemple de requête suivant :

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Member;
+----------+
| count(*) |
+----------+
|   169566 | 
+----------+
1 row in set (0.00 sec)
mysql> pager grep !~-
PAGER set to 'grep !~-'
mysql> select * from Member limit 10, 100;
100 rows in set (0.00 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 1000, 100;
100 rows in set (0.01 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 100000, 100;
100 rows in set (0.10 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
100 rows in set (0.02 sec)
mysql> nopager
PAGER set to stdout

mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00003300
   Query: select count(*) from Member
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00167000
   Query: select * from Member limit 10, 100
*************************** 3. row ***************************
Query_ID: 3
Duration: 0.00112400
   Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.00263200
   Query: select * from Member limit 1000, 100
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.00134000
   Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.09956700
   Query: select * from Member limit 100000, 100
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.02447700
   Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

On peut savoir d'après les résultats que lorsque le décalage est supérieur à 1000 l'utilisation de la méthode de sous-requête peut améliorer efficacement les performances.

2. Méthode d'optimisation de table inversée

La méthode de table inversée est similaire à l'établissement d'un index, en utilisant une table pour maintenir le nombre de pages, puis grâce à des connexions efficaces Obtenir des données

Inconvénients : convient uniquement pour un nombre fixe de données, les données ne peuvent pas être supprimées et il est difficile de maintenir les tables de pages

Introduction au tableau inversé : (Et l'index inversé s'appelle C'est la pierre angulaire de l'algorithme du moteur de recherche)

La table inversée fait référence à un index inversé stocké en mémoire qui peut ajouter des enregistrements inversés. Une liste inversée est un mini index inversé.

Un fichier inversé temporaire fait référence à un index inversé qui est stocké sous la forme d'un fichier et ne peut pas être ajouté aux enregistrements inversés. Le fichier inversé temporaire est un index inversé de taille moyenne.

Le fichier inversé final fait référence à l'index inversé obtenu en fusionnant les fichiers inversés temporaires stockés sous forme de fichiers sur le disque. Le fichier inversé final est un index inversé plus grand.

L'index inversé est un concept abstrait, et la liste inversée, le fichier inversé temporaire et le fichier inversé final sont trois manifestations différentes de l'index inversé.

3. Méthode d'optimisation de la recherche inversée

Lorsque le décalage dépasse la moitié du nombre d'enregistrements, le tri est utilisé en premier, afin que le décalage soit inversé

Inconvénients : L'ordre par optimisation est gênant. Il faut ajouter des index, ce qui affecte l'efficacité de la modification des données, et il faut connaître le nombre total d'enregistrements, et le décalage est supérieur à la moitié des données

algorithme de décalage de limite :
Recherche avant : (Page actuelle - 1) * Longueur de la page
Recherche inversée : Total des enregistrements - Page actuelle * Longueur de la page

Faites une expérience pour voir comment sont les performances

Nombre total d'enregistrements : 1 628 775
Nombre d'enregistrements par page : 40
Nombre total de pages : 1 628 775 / 40 = 40720
Nombre de pages intermédiaires : 40720 / 2 = 20360

Page 21000
Recherche directe SQL :

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40

Durée : 1,8696 secondes

Recherche inversée SQL :

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

Temps : 1,8336 secondes

Page 30000
Recherche avant SQL :

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

Durée : 2,6493 secondes

Recherche inversée SQL :

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

Durée : 1,0035 secondes

Notez que le résultat de la recherche inversée est dans l'ordre décroissant desc, et InputDate est l'heure d'insertion de l'enregistrement. Vous pouvez également utiliser un index conjoint de clé primaire. , mais ce n'est pas pratique.

Méthode d'optimisation 4.limit

Limiter le décalage de limite en dessous d'un certain nombre. . Dépasser ce nombre signifie qu'il n'y a pas de données. Je me souviens que le DBA d'Alibaba a dit qu'il avait fait cela

5 Recherchez uniquement la méthode d'index

Le principe de fonctionnement de la limite MySQL. consiste à lire d'abord n ​​enregistrements, puis à supprimer les n premiers enregistrements et à lire les m enregistrements souhaités. Ainsi, plus n est grand, plus les performances seront mauvaises.
SQL avant optimisation :

SELECT * FROM member ORDER BY last_active LIMIT 50,5

SQL après optimisation :

SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

La différence est que SQL avant optimisation nécessite plus de gaspillage d'E/S, car l'index est lu en premier, puis les données sont lues, puis les lignes inutiles sont supprimées. L'index en lecture seule optimisé SQL (sous-requête) (index de couverture) est suffisant, puis les colonnes requises sont lues via member_id.

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!