Maison  >  Article  >  base de données  >  Tutoriel d'optimisation des performances des requêtes Big Data MySQL (image)

Tutoriel d'optimisation des performances des requêtes Big Data MySQL (image)

php是最好的语言
php是最好的语言original
2018-07-26 16:42:592609parcourir

L'optimisation des performances MySQL inclut l'optimisation des tables et la sélection du type de colonne. En quoi l'optimisation des tables peut-elle être décomposée ? 1. Séparez les champs de longueur fixe et de longueur variable ; 2. Séparez les champs couramment utilisés des champs peu courants ; 3. Ajoutez les champs redondants aux champs 1 à plusieurs qui nécessitent des statistiques de corrélation.

1. Optimisation du tableau et sélection du type de colonne

Optimisation du tableau :

1. devenant séparé par des longueurs

Si ID int, représente 4 octets, CHAR (4) représente une longueur de 4 caractères, qui est également corrigée.

Les champs principaux et couramment utilisés doivent être construits sur une longueur fixe et placés dans un tableau.

et VARCHAR, Text, Blob, les champs longs conviennent pour placer une seule table et sont associés à la clé principale de la table principale.

       

2.

               的业务来分析,分析字段的查询场景,查询频率低的字段,单Démontez-le.

3. En 1 paire, ajoutez des champs redondants aux champs qui doivent être associés aux statistiques.

Regardez les effets suivants :

Dans chaque section, il y a n posts, sur la page d'accueil, les informations de la section et les posts sous la La section est affichée en numéro. Tutoriel doptimisation des performances des requêtes Big Data MySQL (image)

                                                                                                                                                                                                                             , sélectionnez le nombre (* ) du groupe de publications par board_id et obtenez le nombre de publications dans chaque tableau.

2. Sélection du type de colonne

Tutoriel doptimisation des performances des requêtes Big Data MySQL (image)

1. Priorité du type de champ

Type entier>date

time>enum char>varchar>blob,text

Type entier : longueur fixe, pas de distinction de pays/région, pas de différence de jeu de caractères. Par exemple : tinyint 1,2,3,4,5 char(1) a,b,c,d,e

D'un point de vue spatial, ils tous occupent 1 octet, mais le tri est plus rapide. La raison peut être que le jeu de caractères et le jeu de classement (c'est-à-dire les règles de tri) doivent être pris en compte

le temps est de longueur fixe, rapide à utiliser et économise de l'espace ; Compte tenu du fuseau horaire, il n'est pas pratique d'écrire sql où > `2018-08-08`;

enum, qui peut servir de contrainte, est stocké en interne à l'aide d'entiers, mais lors d'une interrogation conjointe avec cahr , l'interne Passe par la conversion des chaînes et des valeurs ;

char est de longueur fixe, compte tenu du jeu de caractères et du jeu de relecture (de tri)

varchar est de longueur variable, et la conversion ; du jeu de caractères et du jeu de relecture lors du tri doivent être pris en compte Lent ;

le texte/blob ne peut pas utiliser les tables temporaires en mémoire (le tri et les autres opérations ne peuvent être effectués que sur le disque)

Pièce jointe : Concernant le choix de la date/heure, avis tranché du maître, choisir directement int unsgined not null, stocke l'horodatage.

Par exemple :

Sexe : prenez utf8 comme exemple

char(1), 3 octets de long

enum('Male',' Female '); Converti en interne en nombres pour le stockage, un processus de conversion supplémentaire

tinyint(), longueur fixe 1 octet

2. Utilisez-en juste assez, ne soyez pas généreux (comme as smallint varchar(N))

Raison : les octets volumineux gaspillent de la mémoire et affectent la vitesse.

En prenant l'âge comme exemple, tinyint unsigned not null peut stocker 255 ans, ce qui est suffisant. L'utilisation de int gaspille 3 octets ;

Le contenu stocké dans varchar(10) et varchar(300) est le même, mais varchar(300) consomme plus de mémoire lors de la requête de jointure de table.

3. Essayez d'éviter d'utiliser NULL()

Raison : NULL n'est pas propice à l'indexation et doit être marqué avec des caractères spéciaux.

prend en fait plus d'espace sur le disque (MySQL5.5 a amélioré null, mais les requêtes sont toujours peu pratiques)

Stratégie d'optimisation de l'index

1.Type d'index

C'est ce qu'on appelle l'index btree. D'un point de vue général, ils utilisent tous des arbres équilibrés, mais en termes d'implémentation spécifique, chaque moteur est légèrement différent. Par exemple, à proprement parler, le moteur NDB utilise T-tree.

Mais en faisant abstraction du système B-tree, cela peut être compris comme une "structure de requête rapide triée".

1.2 Index de hachage

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

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

Réponse :

1. Le résultat calculé par la fonction de hachage 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. , l'identifiant Les lignes correspondantes sont placées aléatoirement sur le disque.

2. Les requêtes de plage ne peuvent pas être optimisées.

3. L'index de préfixe ne peut pas être utilisé. Par exemple, dans btree, la valeur de la colonne du champ est "helloworld", et la requête d'index x=helloworld peut naturellement utiliser l'index, et x=hello peut le faire. utilisez également l'index (index de préfixe de gauche) .

4. Le tri ne peut pas être optimisé.

5. La ligne doit être renvoyée, c'est-à-dire que l'emplacement des données doit être obtenu via l'index et les données doivent être renvoyées à la table.

2. Malentendus courants à propos des index btree

2.1 Ajouter des index sur les colonnes couramment utilisées dans les conditions Where, par exemple :

où cat_id = 3 et prix> 100 ; Vérifiez la troisième colonne pour les produits de plus de 100 yuans.

Malentendu : ajoutez des index à cat_id et à price.

Erreur : Seuls cat_id ou price index peuvent être utilisés, car ce sont des index indépendants, et un seul peut être utilisé en même temps.

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

Malentendu : Pour que l'index fonctionne sur un index multi-colonnes, le L'exigence du préfixe gauche doit être remplie.

Prenons l'exemple de index(a,b,c), (notez que cela dépend de la commande)

Tutoriel doptimisation des performances des requêtes Big Data MySQL (image)

4.

Par exemple : sélectionnez * à partir de t4 où c1=3 et c2 = 4 et c4>5 et c3=2 ;

Quels index sont utilisés :

expliquer select * from t4 où c1=3 et c2 = 4 et c4>5 et c3=2 G

comme suit :

Tutoriel doptimisation des performances des requêtes Big Data MySQL (image)

             

5. Index clusterisé et index non clusterisé

Myisam et moteur innodb, similitudes et différences dans les fichiers d'index

Myisam : composé de news.myd et new. myi Les deux fichiers, le fichier d'index et le fichier de données, sont distincts et sont appelés index non clusterisés. L'index principal et l'index secondaire pointent tous deux vers la ligne physique (emplacement sur le disque)

innodb : L'index et les données sont regroupés ensemble, il s'agit donc d'un index clusterisé. La ligne de données est stockée directement dans le fichier d'index primaire d'innodb et l'index secondaire pointe vers une référence à l'index de clé primaire.

Remarque : Pour innodb :

1. L'index de clé primaire stocke la valeur de l'index et stocke les données de ligne dans les feuilles.

2. S'il n'y a pas de clé primaire, la clé unique sera utilisée comme clé primaire.

3. S'il n'y a pas d'unique, le système génère un rowid interne comme clé primaire.

4. Comme innodb, dans la structure d'index de clé primaire, la valeur de la clé primaire et les données de ligne sont stockées. Cette structure est appelée un index clusterisé.

Index clusterisé

Avantage : Lorsqu'il y a relativement peu d'entrées de requête basées sur la clé primaire, il n'est pas nécessaire de revenir en arrière (les données sont sous la clé primaire nœud)

Inconvénient : si des données irrégulières sont insérées, des fractionnements de pages fréquents se produisent

Articles connexes :

Optimisation des performances MySQL

Vidéos associées :

Tutoriel vidéo d'optimisation MySQL

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!

Déclaration:
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn