Partagez des idées d'optimisation MySQL

1. Idée d'optimisation globale

Tout d'abord, créez un script pour observer le nombre de requêtes, le nombre de connexions et d'autres données, déterminez les raisons environnementales et les raisons d'exécution SQL internes, et puis effectuer un traitement spécifique en fonction des raisons spécifiques.

Recommandé : "Tutoriel vidéo MySQL"

2. Statut d'observation du script de construction

mysqladmin -uroot -p  ext \G

Cette commande peut obtenir des informations telles que le nombre actuel de requêtes, interroger régulièrement et rediriger les résultats vers du texte, puis les traiter sous forme de graphiques.

3. Contre-mesures

1. Si des requêtes lentes se produisent régulièrement, considérez le problème d'avalanche de cache.

Pour ce problème, il vous suffit de gérer le délai d'expiration du cache afin qu'il n'expire pas à des heures similaires en même temps. Le délai d'expiration doit être aussi discret que possible, ou concentré jusqu'à minuit.

2. Si les requêtes non régulières sont lentes, considérez que la conception n'est pas optimisée.

Méthode de traitement :

a : Activer le profilage pour enregistrer les opérations de requête et obtenir les détails d'exécution des instructions.

show variables like '%profiling%';
set profiling=on;
select count(*) from user; 
show profiles;
show profile for query 1;
>>>
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000060 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000049 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000192 |
| init                           | 0.000006 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000014 |
| preparing                      | 0.000017 |
| executing                      | 0.001111 |
| end                            | 0.000006 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000015 |
| closing tables                 | 0.000011 |
| freeing items                  | 0.000085 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+

b : utilisez expliquer pour afficher l'exécution de l'instruction, l'utilisation de l'index, la plage d'analyse, etc.

mysql> explain select count(*) from goods \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: index   
possible_keys: NULL
          key: gid
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index

c : techniques d'optimisation associées

Optimisation des tables et sélection du type de colonne

Principes de sélection des colonnes :

1 : Type de champ priorité entier > date, heure > char, varchar > > Raison : type entier, le fonctionnement du temps est rapide, économise de l'espace

char/varchar doit prendre en compte la conversion du jeu de caractères et du jeu de relecture pendant le tri, et est lent

blob ne peut pas utiliser la mémoire temporaire table

2 : Juste assez, ne soyez pas généreux (comme smallint, varchar(N))

Raison : Les grands champs gaspillent de la mémoire et affectent la vitesse

Utiliser varchar (10), varchar( 300) stocke le même contenu, mais lors de l'interrogation des tables, varchar(300) prend plus de mémoire

3 : essayez d'éviter d'utiliser NULL

Raison : NULL n'est pas propice à l'indexation, utilisez donc des octets spéciaux pour marquer.

prend en fait plus d'espace sur le disque

Stratégie d'optimisation de l'index

1. 🎜>

Index B-tree 1.1 (structure de recherche rapide triée)

Remarque : Dans Myisam, innodb, l'index B-tree est utilisé par défaut

Indice de hachage 1.2

Dans la table mémoire, la valeur par défaut est l'index de hachage, et la révision théorique du temps de requête du hachage est O(1)

Question : Puisque l'index de hachage est si efficace, pourquoi ne pas l'utiliser ?

Le résultat calculé par la fonction a.hash est aléatoire. Si les données sont placées sur le disque, en prenant la clé primaire comme identifiant comme exemple, alors à mesure que l'identifiant grandit, la ligne correspondant à l'identifiant est. placé au hasard sur le disque.

b. La requête de plage ne peut pas être optimisée

c. L'index de préfixe ne peut pas être utilisé. Par exemple, dans b-tree, la valeur de la colonne de champ est "helloworld", et la requête d'index. xx=hello/xx =helloworld peut utiliser l'index (index de préfixe de gauche), mais l'index de hachage ne peut pas le faire, car hash(hello) et hash(helloworld) ne sont pas liés.

d. Le tri ne peut pas être optimisé

e. Les lignes doivent être renvoyées pour obtenir l'emplacement des données via l'index, et les données doivent être renvoyées dans la table

2. b-tree Malentendus courants sur les index

2.1 Ajouter des index aux colonnes couramment utilisées dans les conditions Where

Exemple : où cat_id=3 et price>100 //Interroger la troisième colonne, plus de 100; yuan Le produit

est incorrect : cat_id et price sont indexés. En fait, un seul index peut être utilisé, ce sont tous des index indépendants

2.2 Après avoir créé un index sur plusieurs colonnes, l'index fonctionnera quelle que soit la colonne interrogée

2.2 Créer un index. index sur plusieurs colonnes Après l'indexation, quelle que soit la colonne interrogée, l'index jouera un rôle

Bonne réponse : pour qu'un index multi-colonnes fonctionne, l'index doit répondre à l'exigence de préfixe gauche (index en couches )

Avec index(a, b, c) Par exemple :

语句 索引是否发挥作用
where a=3 是
where a=3 and b=5 是
where a=3 and b=5 and c=4 是
where b=3 or where c=4 否
where a=3 and c=4 a列能发挥索引作用，c列不能
where a=3 and b>10 and c=7 a,b能发挥索引作用，c列不能

Stratégie d'indice haute performance

1 Pour innodb, car il y en a. fichiers de données sous le nœud, la division du nœud deviendra plus lente. Pour la clé primaire d'innodb, essayez d'utiliser un type entier, et c'est un type entier croissant.

2. La longueur de l'index affecte directement la taille du fichier d'index, affecte la vitesse des ajouts, des suppressions et des modifications, et affecte indirectement la vitesse des requêtes (occupant plus de mémoire).

3. Pour les valeurs de la colonne, interceptez les parties de gauche à droite pour construire un index.

a. Plus la coupe est courte, plus la répétition est élevée, plus la distinction est petite, et plus l'effet d'indexation est mauvais

b. Plus la coupe est longue, bien que la distinction soit améliorée, l'index. le fichier devient plus gros Affecte la vitesse

Essayez donc de trouver un point d'équilibre en longueur pour maximiser les performances Méthode courante : intercepter différentes longueurs pour tester la distinction d'index

Test de discrimination :

select count(distinct left(word, 1)) / count(*) from table;

Une fois le test terminé, vous pouvez créer un index en fonction de la longueur optimale obtenue à partir du test

alter table table_name add index word(word(4));

Indice idéal

Requêtes fréquentes2. Discrimination élevée

3. Petite longueur

4. Essayez de couvrir les champs de requête courants

.

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!