Comment optimiser les requêtes MYSQL ? Introduction aux méthodes d'optimisation des requêtes MySQL

Le contenu de cet article concerne le code d'implémentation simple du pool de processus en Python. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

1. Ajoutez des index sur toutes les colonnes utilisées pour where, order by et group by

En plus de garantir qu'un enregistrement est marqué de manière unique, l'index

peut également activer MySQL. serveur à mettre à jour Obtenez rapidement les résultats de la base de données. Les index jouent également un rôle très important dans le tri.

L'index de MySQL peut occuper de l'espace supplémentaire et réduire dans une certaine mesure les performances d'insertion, de suppression et de mise à jour. Cependant, si votre table contient plus de 10 lignes de données, l'indexation peut réduire considérablement le temps d'exécution de la recherche.

Il est fortement recommandé d'utiliser les « échantillons de données les plus défavorables » pour tester les requêtes MySql afin de mieux comprendre comment la requête se comportera en production.

Supposons que vous exécutiez l'instruction de requête suivante sur une table de base de données comportant plus de 500 lignes :

mysql>select customer_id, customer_name from customers where customer_id='345546'

La requête ci-dessus forcera le serveur Mysql à effectuer une analyse complète de la table pour obtenir le données recherchées. Modèle

, Mysql fournit une instruction Explain spéciale pour analyser les performances de votre instruction de requête. Lorsque vous ajoutez une instruction de requête après le mot-clé, MySql affichera toutes les informations dont dispose l'optimiseur sur l'instruction.

Si nous utilisons l'instruction expliquer pour analyser la requête ci-dessus, nous obtiendrons les résultats d'analyse suivants :

mysql> explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

Comme vous pouvez le voir, l'optimiseur affiche des informations très importantes, qui peuvent aider nous affinons les tables de la base de données. Tout d'abord, MySql effectuera une analyse complète de la table car la colonne clé est Null. Deuxièmement, le serveur MySql a clairement indiqué qu'il analyserait 500 lignes de données pour compléter cette requête.

Afin d'optimiser la requête ci-dessus, il suffit d'ajouter un index m sur la colonne customer_id :

mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Si nous exécutons à nouveau l'instruction expliquer, nous obtiendrons le résultats suivants :

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

D'après le résultat ci-dessus, il est évident que le serveur MySQL utilisera l'index customer_id pour interroger la table. Vous pouvez voir que le nombre de lignes à analyser est de 1. Bien que j'exécute cette requête uniquement sur une table de 500 lignes, l'index est encore plus optimisé lors de la récupération d'un ensemble de données plus volumineux.

2. Utilisez Union pour optimiser l'instruction Like

Parfois, vous devrez peut-être utiliser l'opérateur or dans la requête à des fins de comparaison. Lorsque le mot clé or est utilisé trop fréquemment dans la clause Where, l'optimiseur MySQL peut choisir par erreur une analyse complète de la table pour récupérer les enregistrements. La clause union peut accélérer l'exécution des requêtes, en particulier lorsque l'une des requêtes a un index optimisé et que l'autre requête a également un index optimisé.

Par exemple, lorsqu'il y a des index sur first_name et last_name respectivement, exécutez l'instruction de requête suivante :

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%'

La requête ci-dessus et la requête suivante sont fusionnées à l'aide de l'union pour utiliser pleinement les deux requêtes Par rapport à la requête de l'instruction, la vitesse est beaucoup plus lente.

mysql> select * from students where first_name like 'Ade%' union all select * from students where last_name like 'Ade%'

3. Évitez d'utiliser des expressions avec des caractères génériques de début.

Mysql ne peut pas utiliser l'index lorsqu'il y a des caractères génériques de début dans la requête. En prenant la table étudiant ci-dessus comme exemple, la requête suivante amènera MySQL à effectuer une analyse complète de la table et à ajouter un index au champ first_name à temps.

mysql> select * from students where first_name like '%Ade'

En utilisant l'analyse expliquer, les résultats suivants sont obtenus :

mysql> explain select * from students where first_name like  '%Ade'  ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

Comme indiqué ci-dessus, Mysql analysera les 500 lignes de données, ce qui rendra la requête extrêmement lente.

4. Tirez pleinement parti de la recherche en texte intégral de MySQL

Si vous êtes confronté à l'utilisation de caractères génériques pour interroger des données, mais que vous ne souhaitez pas réduire les performances de la base de données, vous devriez envisager en utilisant la recherche en texte intégral (FTS) de MySQL Parce qu'elle est beaucoup plus rapide que la requête générique. En plus de cela, FTS est en mesure de renvoyer des résultats pertinents de meilleure qualité.

L'instruction pour ajouter un index de recherche en texte intégral à l'exemple de table étudiant est la suivante :

mysql> alter table students add fulltext(first_name, last_name)';
mysql> select * from students where match(first_name, last_name) against ('Ade');

Dans l'exemple ci-dessus, nous spécifions les colonnes que nous voulons faire correspondre pour la recherche mot-clé Ade (prénom, nom). Si l'optimiseur de requête exécute l'instruction ci-dessus, les résultats suivants seront obtenus :

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

5. Optimiser la structure de la base de données

Normalisation

Tout d'abord, normalisez toutes les tables de la base de données. , Même s'il peut y avoir des pertes. Par exemple, si vous devez créer deux tables pour enregistrer les données des clients et des commandes, vous devez référencer le client par ID client dans la table des commandes, et non l'inverse. Le schéma ci-dessous montre l'architecture de la base de données conçue sans aucune redondance des données.

Sinon, utilisez la même classe de type de données pour stocker des valeurs similaires.

Utilisez les meilleurs types de données

MySQL prend en charge différents types de données, notamment entier, flottant, double, date, dateheure, varchar, texte, etc. Lors de la conception des tables de base de données, vous devez essayer d'utiliser le type de données le plus court pouvant satisfaire aux caractéristiques.

Par exemple, si vous concevez une table d'utilisateurs système et que le nombre d'utilisateurs ne dépassera pas 100, vous devez utiliser le type 'TINYINT' pour user_ud. La plage de valeurs de ce type est comprise entre -128 et 128. Si un champ doit stocker des valeurs de type date, il est préférable d'utiliser le type datetime, car il n'est pas nécessaire d'effectuer une conversion de type complexe lors de l'interrogation.

Lorsque les valeurs sont toutes de type numérique, utilisez Integer. Les valeurs de type Integer sont plus rapides que les valeurs de type Text lors de l'exécution de calculs.

Éviter NULL

NULL signifie que la colonne n'a aucune valeur. Vous devez éviter ces types de valeurs autant que possible car elles peuvent nuire aux résultats de la base de données. Par exemple, vous devez obtenir la somme des montants de toutes les commandes dans la base de données, mais le montant dans un certain enregistrement de commande est nul. Si vous ne faites pas attention au pointeur nul, il est très probable que le résultat du calcul soit nul. sera anormal. Dans certains cas, vous devrez peut-être définir une valeur par défaut pour une colonne.

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!