Instructions SQL courantes pour l'optimisation des bases de données dans MySQL (partage de résumé)

Cet article vous apporte des connaissances pertinentes sur mysql. Il résume principalement quelques instructions SQL courantes pour l'optimisation des bases de données dans MySQL. L'exemple de code dans l'article est expliqué en détail. Examinons-le ensemble, j'espère qu'il sera utile. à vous.

Apprentissage recommandé : Tutoriel vidéo MySQL

1.AFFICHER LES MOTEURS

Afficher le moteur d'exécution et le moteur par défaut.

2.SHOW PROCESSLIST

SHOW PROCESSLIST est très utile pour afficher l'utilisation de la connexion actuelle à la base de données et diverses informations d'état. SHOW PROCESSLIST ; Seuls les 100 premiers éléments sont répertoriés. Si vous souhaitez tous les lister, veuillez utiliser SHOW FULL PROCESSLIST

Colonnes d'attribut et leurs significations :

id	Un identifiant, très utile lorsque vous voulez tuer une déclaration.
user	Affichez l'utilisateur actuel Si vous n'êtes pas root, cette commande affichera uniquement les instructions SQL relevant de votre autorité.
host	Montre quelle adresse IP et quel port cette déclaration est envoyée. Peut être utilisé pour suivre l'utilisateur qui a publié la déclaration problématique.
db	Affiche à quelle base de données ce processus est actuellement connecté.
command	Affiche la commande exécutée de la connexion actuelle, généralement dormir, interroger et se connecter.

colonne d'état et sa signification, le statut listé par mysql :

Vérification de la table	Vérification de la table de données (c'est automatique).
Fermer les tables	consiste à vider les données modifiées de la table sur le disque, tout en fermant les tables qui ont été utilisées. C'est une opération rapide, mais si ce n'est pas le cas, vous devez vérifier que l'espace disque est plein ou que le disque est sous forte charge.
Connect Out	Le serveur esclave de réplication se connecte au serveur maître.
Copie vers une table tmp sur le disque	Étant donné que l'ensemble de résultats temporaires est plus grand que tmp_table_size (16 Mo par défaut), la table temporaire est convertie du stockage mémoire en stockage disque pour économiser de la mémoire.
Création d'une table tmp	Création d'une table temporaire pour stocker certains résultats de requête.
suppression de la table principale	Le serveur effectue la première partie d'une suppression multi-table et vient de supprimer la première table.

3.SHOW STATUS LIKE 'InnoDB_row_lock%'

Variable d'état de verrouillage au niveau de la ligne d'InnoDB.

La variable d'état de verrouillage au niveau de la ligne d'InnoDB enregistre non seulement le nombre d'attentes de verrouillage, mais enregistre également la durée totale du verrouillage, la durée moyenne de chaque fois et la durée maximale. variable d'état qui affiche le nombre de verrous actuellement en attente de la quantité de verrou en attente. La description de chaque quantité d'état est la suivante :

• InnoDB_row_lock_current_waits : le nombre de verrous actuellement en attente de verrous ;
• InnoDB_row_lock_time : le temps de verrouillage total depuis le démarrage du système jusqu'à maintenant ;
• InnoDB_row_lock_time_avg : le temps moyen passé à attendre à chaque fois ;
• InnoDB_row_lock_time_max : Le temps d'attente le plus courant depuis le démarrage du système jusqu'à maintenant ;
• InnoDB_row_lock_waits : le nombre total d'attentes depuis le démarrage du système jusqu'à maintenant

Pour ces 5 variables d'état, la plus importante est InnoDB_row_lock_time_avg ( temps d'attente moyen), InnoDB_row_lock_waits (nombre total d'attentes) et InnoDB_row_lock_time (temps d'attente total). Surtout lorsque le nombre d'attentes est élevé et que la durée de chaque attente n'est pas petite, nous devons analyser pourquoi il y a tant d'attentes dans le système, puis commencer à spécifier un plan d'optimisation basé sur les résultats de l'analyse.

Si vous constatez que le conflit de verrouillage est grave, par exemple que les valeurs de InnoDB_row_lock_waits et InnoDB_row_lock_time_avg sont relativement élevées, vous pouvez également configurer les moniteurs InnoDB pour observer davantage les tables et les lignes de données où se produisent des conflits de verrouillage et analyser les raisons de le conflit de verrouillage.

4.SHOW ENGINE INNODB STATUS

La commande SHW ENGINE INNODB STATUS affichera de nombreuses informations actuellement surveillées par le moniteur InnoDB. Sa sortie est une seule chaîne, sans lignes ni colonnes. Le contenu est divisé en plusieurs petits segments, chacun. Le segment correspond aux informations sur les différentes parties du moteur de stockage InnoDB, dont certaines sont très utiles aux développeurs InnoDB.

Il y a une section DERNIER DEADLOCK DÉTECTÉ, qui contient les dernières informations de blocage enregistrées, comme indiqué dans le cas suivant :

• "(1) TRANSACTION" affiche les informations de la première transaction ;
• "(1) ) EN ATTENTE DE CE VERROU À ÊTRE ACCORDÉ" affiche les informations de verrouillage que la première transaction attend
• "(2) TRANSACTION" affiche les informations de la deuxième transaction;
• "(2) HOLDS THE LOCK(S)" affiche la deuxième transaction Les informations de verrouillage détenues ;
• "(2) WAITING FOR THIS LOCK TO BE GRANTED" affiche les informations de verrouillage en attente de la deuxième transaction
• La dernière ligne indique le résultat du traitement, tel que "WE ROLL BACK TRANSACTION (2) ), indiquant un rollback La deuxième transaction

5.SHOW INDEXS

SHOW INDEXS interroge les informations d'index dans une table : SHOW INDEXES FROM table_name;

Le SQL pour créer la table est le suivant :

CREATE TABLE contacts(
    contact_id INT AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL comment 'first name',
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    PRIMARY KEY(contact_id),
    UNIQUE(email),
    INDEX phone(phone) ,
    INDEX names(first_name, last_name) comment 'By first name and/or last name'
);

La procédure stockée. insère 50 000 données :

CREATE PROCEDURE zqtest ( ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	DECLARE
		j VARCHAR ( 100 ) DEFAULT 'first_name';
	DECLARE
		k VARCHAR ( 100 ) DEFAULT 'last_name';
	DECLARE
		l VARCHAR ( 100 ) DEFAULT 'email';
	DECLARE
		m VARCHAR ( 20 ) DEFAULT '11111111111';
	
	SET i = 0;
	START TRANSACTION;
	WHILE
			i < 50000 DO
		IF
			MOD ( i, 100 ) = 0 THEN
				
				SET j = CONCAT( &#39;first_name&#39;, i );
			
		END IF;
		IF
			MOD ( i, 200 ) = 0 THEN
				
				SET k = CONCAT( &#39;last_name&#39;, i );
			
		END IF;
		IF
			MOD ( i, 50 ) = 0 THEN
				
				SET m = CONCAT( &#39;&#39;, CAST( m as UNSIGNED) + i );
			
		END IF;
		INSERT INTO contacts ( first_name, last_name, email, phone )
		VALUES
			( j, k, CONCAT(l,i), m );
		
		SET i = i + 1;
		
	END WHILE;
	COMMIT;
	
END;

Le résultat après avoir utilisé l'affichage de l'index des contacts est le suivant :

Description du champ :

Seq_in_index Numéro de séquence de la colonne, à partir de 1. Il peut également indiquer l'ordre de la colonne dans l'index conjoint Column_nameNom de la colonne de l'index, s'il s'agit d'un index conjoint. le nom d'une certaine colonne Collation La façon dont elle est stockée dans l'index signifie probablement l'ordre des caractères CardinalitéLe nombre de valeurs différentes sur un index est également appelé la "cardinalité". appelé degré de distinction. Cette cardinalité. Plus l'index est grand, meilleur est l'index. Les statistiques de cette valeur ne sont pas nécessairement exactes et peuvent être corrigées à l'aide de ANALYZE TABLE. Si la colonne n'est que partiellement indexée, elle est indexée. de caractères indexés. NULL si la valeur entière de la colonne est indexée PackedNULL si elle n'est pas compressée. Solution de colonne compressée et solution de table compressée NullSi la valeur de la colonne peut contenir null. OUIIndex_typetype de structure d'index, les plus courants sont FULLTEXT, HASH, BTREE, RTREEComment, Index_commentComments

6.ALTER TABLE xx ENGINE = INNODB

Reconstruisez la table, y compris la structure d'index. Peut éliminer les fractionnements de pages d'index et la fragmentation du disque laissée lorsque les données sont supprimées.

7.ANALYZE TABLE

ne reconstruit pas la table, il recompte simplement les informations d'index de la table sans modifier les données. Le verrou de lecture MDL est ajouté dans ce processus. Il peut être utilisé pour corriger la situation où la cardinalité de l'index statistique dans show index from tablename est une donnée anormale ;

Apprentissage recommandé : Tutoriel vidéo mysql

Table	Nom de la table
Non_unique	L'index unique est 0 et les autres index sont 1. Index unique
Key_name	Nom de l'index Si les noms sont les mêmes, cela signifie le même index et c'est un index conjoint. index.

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!