Comparable à construire l'index bibliographique Bibliothèque de l'Université, vous pouvez améliorer l'efficacité d'extraction de données, de réduire les coûts base de données IO. les performances de MySQL a commencé à décliner à environ 3 millions de disques, bien que la documentation officielle dit 500 ~ 800W dossier, de sorte que la grande quantité d'indexation de données est très nécessaire. MySQL fournit Expliquer, des informations détaillées sont affichées pour la mise en uvre de SQL, peut être optimisé index.

Tout d'abord, les raisons qui ont conduit à la mise en uvre lente de SQL

1. Problème matériel. Comme le réseau est lent espace, mémoire insuffisante, faible débit d'E / S, le disque est plein et ainsi de suite.

2. Il n'y a pas d'index ou de l'indice ne. (En général, dans les sociétés Internet, DBA verrouille la table au milieu de la nuit pour rétablir l'index à nouveau, parce que lorsque vous supprimez des données, l'arborescence d'index n'est pas complet, si les données de l'entreprise Internet à faire est faux supprimé. la première consiste à faire l'analyse des données, et d'autre part, afin de ne pas détruire l'indice)

3. Trop de données (sous-bibliothèque sous-tableau)

4. Le serveur et chaque paramètre de réglage (le réglage de la my.cnf)

D'autre part, l'analyse des causes, il faut trouver un point d'entrée

1. La première observation, tournez le journal des requêtes lentes, réglez le dans un environnement de production de valeur seuil correspondant (par exemple, plus de trois secondes est SQL lent), a couru un jour après pour voir ce que SQL est plus lent.

2.Explain et l'analyse lente SQL. Par exemple, pour écrire des instructions SQL pourris, pas d'index ou de l'échec, associé à la requête beaucoup (parfois défaut conception ou non la demande) et ainsi de suite.

3.Show profil est un peu plus de mise en uvre que Expliquer les détails, vous pouvez interroger SQL pour effectuer ont chacun quelque chose faire, ces choses sont combien de secondes il a fallu.

4. Obtenir ou opération DBA et la maintenance d'un des paramètres de réglage de serveur MySQL.

Troisièmement, quel est l'indice?

MySQL est la définition officielle de l'indice: l'indice (Index) pour obtenir l'aide de MySQL efficacement la structure de données des données. Nous pouvons être simplement compris que: trouver rapidement une structure de données triées. Il existe deux principaux structure de l'indice de Mysql: B + index Arbre et l'indice de Hash. Nous disons habituellement l'indice, si non spécifié, sont généralement fait référence l'indice (B + index Arbre) arborescence B de l'organisation. Comme le montre la figure Index:

Bleu clair disque la plus extérieure 1, il y a des blocs de données 17, 35 (bleu foncé) et des pointeurs P1, P2, P3 (jaune). Pointeur P1 indique les blocs de disque de moins de 17, P2 est comprise entre 17-35, point P3 de plus de 35 blocs de disque. données réelles existe dans le nud de cotylédons qui est la couche inférieure de 3,5,9,10,13 ...... noeuds non-feuilles ne stockent pas les données réelles, seuls les éléments de données stockés dans le sens de la recherche des lignes directrices, telles que 17, 35.

Recherche procédé: par exemple, rechercher des données du point 28, le premier disque est chargé dans un bloc de mémoire 1, se produire une fois E / S, une recherche binaire est déterminé par le pointeur de P2. Ensuite, 28 trouvé entre 26 et 30, pour charger le bloc de disque 3 par le pointeur d'adresse mémoire P2, l'apparition de la seconde I / O. De la même façon de trouver le bloc de disque 8, l'apparition de la troisième I / O.

La vérité est, la couche supérieure de B 3 + arbre peut représenter des millions de données, des millions de données ne se produit que trois E / S au lieu d'un million d'E / S, est un bon moment pour mettre à niveau.

Quatre, Expliquer l'analyse

Literie préalablement complété en partie d'opération pratique, le premier nécessaire d'insérer des données de test:

CREATE TABLE `user_info` (

 `Id` BIGINT (20) NOT NULL AUTO_INCREMENT,

 `Name` VARCHAR (50) NOT NULL DEFAULT '',

 `Age` INT (11) NULL DEFAULT,

 Clé primaire ( `de id`),

 KEY `name_index` (` name`)

) MOTEUR = InnoDB DEFAULT CHARSET = UTF-8;

INSERT INTO user_info (nom, âge) VALUES ( 'XYS', 20);

INSERT INTO user_info (nom, âge) VALUES ( 'a', 21);

INSERT INTO user_info (nom, âge) VALUES ( 'b', 23);

INSERT INTO user_info VALEURS (nom, âge) ( 'c', 50);

INSERT INTO user_info (nom, âge) VALUES ( 'd', 15);

INSERT INTO user_info (nom, âge) VALUES ( 'e', 20);

INSERT INTO user_info VALEURS (nom, âge) ( 'f', 21);

INSERT INTO user_info (nom, âge) VALUES ( 'g', 23);

INSERT INTO user_info (nom, âge) VALUES ( 'h', 50);

INSERT INTO user_info (nom, âge) VALUES ( 'i', 15);

CREATE TABLE `order_info` (

 `Id` BIGINT (20) NOT NULL AUTO_INCREMENT,

 `User_id` BIGINT (20) NULL DEFAULT,

 `Product_name` VARCHAR (50) NOT NULL DEFAULT '',

 `Productor` VARCHAR (30) NULL DEFAULT,

 Clé primaire ( `de id`),

 KEY `user_product_detail_index` (` user_id`, `product_name`,` productor`)

) MOTEUR = InnoDB DEFAULT CHARSET = UTF-8;

INSERT INTO Info_Commandes (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');

INSERT INTO Info_Commandes (user_id, product_name, productor) VALUES (1, 'p2', 'WL');

INSERT INTO Info_Commandes (user_id, product_name, productor) VALUES (1, 'p1', 'DX');

INSERT INTO Info_Commandes (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');

INSERT INTO Info_Commandes (user_id, product_name, productor) VALUES (2, 'p5', 'WL');

INSERT INTO Info_Commandes (user_id, product_name, productor) VALEURS (3, 'p3', 'MA');

INSERT INTO Info_Commandes (user_id, product_name, productor) VALEURS (4, 'p1', 'WHH');

INSERT INTO Info_Commandes (user_id, product_name, productor) VALEURS (6, 'p1', 'WHH');

INSERT INTO Info_Commandes (user_id, product_name, productor) VALEURS (9, 'p8', 'TE');

Première expérience, l'exécution Expliquer l'effet de:

l'utilisation des index dans possible_keys, clés et key_len trois, nous devons expliquer de gauche à droite.

1.id

Même --id, ordre d'exécution de haut en bas

u * expliquer sélectionner, o * u de user_info, Info_Commandes o où u.id = o.user_id ..;

Différents --id, des valeurs plus grandes est effectuée en premier

expliquer select * from user_info où id = (sélectionner user_id de Info_Commandes où product_name = 'p8');

2.select_type

Vous pouvez voir des exemples de la mise en uvre de l'identifiant, un total des types suivants:

SIMPLE: indique que cette requête ne contient pas de requête ou d'un sous-requête UNION

PRIMAIRE: Cette requête est exprimée requête externe

Sous-requête: la première sous-requête SELECT

UNION: indique que cette requête est les requêtes deuxième fois ou plus d'un UNION

UNION DEPENDANT: UNION est le deuxième ou derrière la requête, en fonction de l'extérieur de l'enquête

RÉSULTAT DE L'UNION, UNION résultats

DEPENDANT :. Première sous-requête SELECT sous-requête, en fonction de l'extérieur de la requête dépend des résultats de la sous-requête requête externe.

DERIVE: dérivé, représente table d'exportation SELECTION (à partir de sous-requêtes de la clause)

3.table

Il représente une table de consultation table ou tables dérivées selon:

expliquer select tt. * de (sélectionner u. * de user_info u, o Info_Commandes où u.id = o.user_id et u.id = 1) tt

id de 1 < derived2 > 2 est représenté comme identifiant de u et o tables dérivées.

4.Saisissez

champ de type est plus important, il fournit une requête pour déterminer si une base importante pour base efficace. Par champ de type, on juge cette requête est une analyse complète de la table ou balayage index et ainsi de suite.

Introduisez les valeurs couramment utilisées sont les suivantes:

système: seulement une donnée dans le tableau, ce type est un type const.

const: la requête pour l'équivalent d'une clé primaire ou de l'indice de scanner à retourner une seule ligne de données. la vitesse de requête const est très rapide, car il est lu qu'une seule fois. Par exemple, la requête suivante, il utilise l'index de clé primaire, et est donc le type de type const: expliquer select * from user_info où id = 2;

eq_ref: Ce type se produit généralement dans un environnement multi-table de jointure requête, les résultats indiquent pour chaque table devant une rangée ne peut être adapté à la table des résultats. Et comparer l'opération de requête est généralement élevée = efficacité de la recherche. Par exemple: expliquer select * from user_info, Info_Commandes où user_info.id = order_info.user_id;

ref: Ce type se produit habituellement dans plusieurs tables requête de jointure, ou dirigé vers un index de clé non primaire, ou utilisez l'index de règle préfixe requête gauche. Par exemple, l'exemple suivant, il est utilisé pour ref type de requête: expliquer select * from user_info, Info_Commandes où user_info.id = order_info.user_id ET order_info.user_id = 5

plage: requête plage d'index d'indication, la section d'acquisition de données de table d'enregistrement par intervalle de champ d'index. Ce type se produit habituellement = < > , > , > = < , < =, IS NULL, < = > ENTRE, le fonctionnement IN (). Par exemple, l'exemple suivant est une requête de plage: expliquer select * from user_info où id entre 2 et 8;

index: un indice montrant une analyse complète (full scan d'index), et des types similaires de ALL, mais un type scan de table est ALL, seul l'index analyse tous les types de données d'index sans balayage. type d'index apparaît généralement: les données à interroger directement dans l'arborescence d'index peut être obtenu sans avoir besoin de données d'analyse. Si tel est le cas lorsque, champ supplémentaire affiche l'aide index.

ALL: représente l'analyse complète de la table, ce type de performances des requêtes de requête est l'un des pires. D'une manière générale, notre requête est pas tous les types de requêtes doivent se poser, car une telle grande quantité de données dans une requête, les performances de la base de données est une catastrophe énorme. TOUS les types comme une requête est une requête, vous pouvez ajouter l'index général pour le champ correspondant à éviter.

D'une manière générale, les différents types de type de relation de performance comme suit:

TOUS <  index <  gamme ~ index_merge <  ref <  eq_ref <  const <  système

ALL est parce que le type d'analyse complète de la table, donc dans la même requête, il est le plus lent. Le type d'index de requête, mais pas une analyse complète de la table, il scanne tous les index, légèrement plus rapide que derrière plusieurs types de types tous l'utilisation d'un index pour interroger les données, il est possible de filtrer une partie ou la plupart des données, donc l'efficacité de la requête est relativement élevée.

5.possible_keys

Il représente mysql dans la requête, vous pouvez utiliser l'index. Notez que même si certains apparaissent dans possible_keys dans l'index, mais l'index ne signifie pas que cela va vraiment utiliser MySQL. Lorsque vous utilisez la requête mysql quel indice spécifique, déterminé par le champ clé.

6.key

Ce champ est dans la requête en cours de MySQL pour utiliser réellement l'index. Par exemple, une partie de dîner, possible_keys est combien de personnes doivent aller, la clé est réelle pour beaucoup de gens. Lorsque nous ne sommes pas indexés:

. Expliquer sélectionnez o * de Info_Commandes o où o.product_name = 'p1' et o.productor = 'whh';

créer un index sur idx_name_productor Info_Commandes (productor);

drop index idx_name_productor sur Info_Commandes;

Et puis construire requête d'index composite:

7.key_len

Indique que la requête optimiseur utilise le nombre d'octets de l'indice, le champ d'index peut déterminer si la combinaison est complètement utilisée.

8.ref

Cette colonne indique l'indice représente qui est utilisé, si possible, est une constante. type d'attribut ci-dessus a également ref, notez la différence.

9.rows

des lignes est également un domaine important, l'optimiseur de requêtes MySQL à des informations statistiques, estimés à vous voulez trouver ensemble sql résultat à lire des lignes de données doivent être numérisées, cette valeur est très intuitive efficacité sql d'affichage est bonne ou mauvaise, dans les lignes principales que possible. Peut être comparé dans l'exemple clé d'un argent non indexé, les lignes 9, après la mise en place de l'indice, les lignes 4.

10.extra

expliquer beaucoup d'informations supplémentaires seront affichées dans le champ supplémentaire, il y a plusieurs éléments communs:

en utilisant filesort: mysql représente une des opérations de tri supplémentaires, le tri ne peut pas atteindre l'ordre de l'indice d'effet. recommandons généralement en utilisant l'optimisation de filesort retiré, en raison de la grande consommation de ces requêtes ressources cpu.

index à l'aide: couvrant balayage d'index, indique que la requête peut trouver les données dans l'index, sans l'analyse du fichier de données de table, illustrent souvent une bonne performance.

en utilisant temporaire: il y a des requêtes en utilisant des tables temporaires, apparaissent généralement dans le tri, le regroupement, et multi-tables rejoignent le cas, l'efficacité de la requête est pas élevé, l'optimisation proposée.

en utilisant où: utilisation de la table où le filtrage.

Cinquième, cas d'optimisation

u * expliquer sélectionner, o * u de user_info LEFT JOIN Info_Commandes o sur u.id = o.user_id ..;

Les résultats, le type ont tous, et aucun indice:

Commencer à optimiser, créer un index sur la colonne associée, tapez la colonne voir la TOTALITÉ devenir ref et utiliser l'index, les lignes est devenu une ligne de la ligne de balayage 9:

Il y a généralement une loi: l'index gauche ainsi que des liens au-dessus du tableau à droite, l'index de lien à gauche et à droite ajouté au tableau ci-dessus.

Six, si vous avez besoin pour créer un index?

Bien que l'indice très efficace peut améliorer la vitesse requête, alors qu'il réduira la table de mise à jour de la vitesse. En fait, l'indice est également une table qui contient les clés primaires et les champs index et points pour enregistrer une table solide, de sorte que la colonne d'index est de prendre l'espace.