Améliorer l'efficacité du travail : conseils d'optimisation MySQL

Le contenu de cet article concerne l'amélioration de l'efficacité du travail : les compétences d'optimisation MySQL, qui ont une certaine valeur de référence, peuvent s'y référer.

1. Ajouter des index sur toutes les colonnes utilisées pour où, trier par et regrouper par

1) En plus de garantir qu'un enregistrement est marqué de manière unique, l'index peut C'est également le serveur MySQL qui obtient plus 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.

2) 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.

3) Supposons que vous exécutiez l'instruction de requête suivante dans 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 les données des résultats de recherche.

4) 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.

5) 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  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

2. Utilisez Union pour optimiser l'instruction Like

1) 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 est liée à la requête suivante qui utilise l'union pour fusionner les deux les requêtes qui utilisent pleinement l'instruction de requête par rapport à, la vitesse est beaucoup plus lente.

mysql> select * from students where first_name like 'Ade%' union all select * from students wherelast_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 student 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 :

 | 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. Optimiser l'architecture de la base de données

1) 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.

5. Utilisez le meilleur type de données

1) 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.

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!