Résoudre les problèmes d'optimisation des performances de MySQL

Problèmes complets de l'optimisation Mysql :

A. Le tableau est conçu pour être rationalisé (selon 3 paradigmes)

Ajoutez des index appropriés [quatre types : Ordinaire. index, index de clé primaire, index unique, index unique en texte intégral]

C. Technologie de fractionnement de table (fractionnement horizontal, fractionnement vertical)

Lire et écrire [écrire : mettre à jour/ delete/add ] Séparation

E. Procédures stockées [programmation modulaire, qui peut augmenter la vitesse]

F. Optimiser la configuration mysql [configurer le nombre maximum de simultanéités, ajuster la taille du cache de mon. ini]

Mise à niveau recommandée du serveur Mysql

H. Effacez régulièrement les données inutiles et défragmentez régulièrement

Tutoriels vidéo recommandés sur Mysql : https://www. php.cn/course/list/51/type/2.html

1. Conception des tables de base de données

Première forme normale : 1NF est l'atomicité des attributs Contraintes exiger que les attributs (colonnes) soient atomiques et ne puissent pas être décomposés ; (tant que la base de données relationnelle satisfait à 1NF)

Deuxième forme normale : 2NF est une contrainte unique sur les enregistrements, exigeant que les enregistrements aient des identifiants uniques. , l'unicité de l'entité

Troisième forme normale : 3NF est une contrainte sur la redondance des champs, qui impose que les champs ne soient pas redondants. Aucune conception de base de données redondante ne peut faire cela.

2. Étapes générales pour l'optimisation SQL

Étapes de fonctionnement :

1. Utilisez la commande show status pour comprendre la fréquence d'exécution des différents SQL.

2. Localisez les instructions SQL avec une faible efficacité d'exécution - (sélection par clé)

3. Analysez l'exécution des instructions SQL inefficaces en expliquant

4. mesures d'optimisation correspondantes

MySQL peut fournir des informations sur l'état du serveur en utilisant la commande show [session|global] status.

session représente les résultats statistiques de la connexion actuelle, et global représente les résultats statistiques depuis le dernier démarrage de la base de données. La valeur par défaut est le niveau de la session.

afficher le statut comme 'Com_%';

où Com_XXX représente le nombre de fois que l'instruction XXX a été exécutée. Ex : Com_insert, Com_Select...
Remarque importante : Com_select, Com_insert, Com_update, Com_delete. Grâce à ces paramètres, vous pouvez facilement comprendre si l'application de base de données actuelle est principalement basée sur des opérations d'insertion et de mise à jour ou des opérations de requête, ainsi que différents types de Quel est le taux d'exécution approximatif de SQL.
Connexions : le nombre de tentatives de connexion au serveur MySQL
Uptime : la durée pendant laquelle le serveur fonctionne (en secondes)
Slow_queries : le nombre de requêtes lentes (la valeur par défaut est une durée de requête lente de 10 s)

Afficher le statut comme 'Handler_read%' utilise le nombre de requêtes

Localisation des requêtes lentes :

Par défaut, MySQL n'enregistre pas les requêtes complètes logs. Il doit être démarré au démarrage. Spécifiez

lors de la spécification de binmysqld.exe- -safe-mode – slow-query-log [mysql5.5 peut être spécifié dans my.ini]

binmysqld.exe- -log-slow- queries=d:bac.log

L'opération spécifique est la suivante :

Si la requête lente est activée, elle sera stockée ici dans le mysql. ini par défaut

1. Redémarrez MySQL, recherchez le chemin d'accès à datadir et utilisez cmd pour entrer dans le répertoire de données de niveau supérieur

2. Exécutez la commande binmysqld.exe –safe-mode –slow-query-log (remarque avant l'exécution, fermez d'abord le service mysql)

3. Le fichier journal généré enregistre toutes les informations d'enregistrement

Afficher le temps de requête lente : Afficher les variables comme 'long_query_time' ;

Réinitialiser le temps pour une requête complète : Définir long_query_time=2;

Modifier le symbole de fin de commande : (Pour que le processus stocké exécuter normalement, nous devons modifier le symbole de fin de commande)

Delimiter $$

Comment enregistrer les instructions SQL des requêtes lentes dans nos journaux (mysql ne les enregistrera pas par défaut, vous devez spécifiez la requête lente au démarrage de MySQL).

3. Index

♥ Types d'index :

★Quatre types d'index ① Index de clé primaire ② Index unique ③ Index ordinaire ④ Index de texte intégral

1. Ajouter

1.1 Ajouter

à l'index de clé primaire Lorsqu'une colonne d'une table est définie comme clé primaire, la colonne est l'index de clé primaire.

                         Créable aaa(id int clé primaire non signée,

                                                                                                                                                                                                                        Créable aaa 🎜>

De manière générale, pour les index ordinaires, vous créez d'abord une table puis créez un index ordinaire.

Par exemple :

Créer un nom d'index à partir du nom de la table

1.3 Créer un index de texte intégral

Index de texte intégral, principalement pour les fichiers, tels que indexation en texte intégral des articles Pour myISAM, il n'est pas utilisé pour InnoDB

Créer des articles de table (

ID INT UNSIGNEDAUTO_INCREMENT NOT NULL Clé primaire,

Titre Varchar (20). ),

🎜 🎜 🎜 🎜 🎜 🎜 🎜 " Corps du texte,

Texte intégral (titre, corps)

)engine=myisam charsetutf8;

Utilisation incorrecte :

%mysql%'[L'index de texte intégral ne sera pas utilisé]

Preuve :

Utilisation correcte : 2. Efficace pour l'anglais, la technologie àsphinx (coreseek) gère le chinois

3. La méthode utilisée, match(field name,...)against('keyword ')

                                                                                                                                                                                                                      Mots vides. Parce que dans un texte, un livre infini est créé comme un index, donc certains mots et caractères courants ne seront pas créés. Ces mots sont appelés mots vides

1.4 Créer un index unique

Lorsqu'un. la colonne de la table est spécifiée comme contrainte UNIQUE, cette colonne est le seul index

le premier, Create Table DDD (ID Int Primary Keyauto_increment, nom varchar (32) UNIQUE

); À l'heure actuelle, le nom est le seul index par défaut

La seconde, Create Table Eee Primary Key = Unique+Not Null

Le champ UNIQUE peut être nul et peut avoir plusieurs NULL, mais s'il s'agit d'un contenu spécifique, il ne peut pas être répété

champs clés principaux, non nuls, ou non. Répétez

2. Requête

1. Nom de la table Desc [inconvénient de cette méthode, le nom de l'index ne peut pas être réalisé]

2. Afficher l'index du nom de la table ; afficher les clés du nom de la table

3. Supprimer

Le nom de la table altérable supprime le nom de l'index,

Le nom de la table altérable supprime la clé primaire. (Supprimez le nom de l'index de la clé primaire)

4. Modifiez

Supprimez d'abord, puis supprimez tout

2. Il est relativement pratique d'optimiser le SQL lent provoqué par l'écriture SQL . Comme mentionné dans la section précédente, l'utilisation correcte des index peut accélérer les requêtes, nous devons donc prêter attention aux règles liées aux index lors de l'écriture SQL :

1. La conversion de type de champ ne nécessite pas d'index. , tels que les types de chaînes N'utilisez pas de guillemets, utilisez des guillemets pour les types numériques, etc. Cela peut ne pas utiliser l'index et provoquer une analyse complète de la table

2.mysql ne prend pas en charge la conversion de fonctions, donc les fonctions ; ne peut pas être ajouté devant les champs, sinon il ne sera pas utilisé dans l'index

3. Ne pas ajouter ou soustraire devant le champ

4. long, vous pouvez envisager d'en indexer une partie pour réduire la taille du fichier d'index et améliorer l'efficacité d'écriture ;

5.like % L'index n'est pas nécessaire au début

6. non nécessaire pour les requêtes séparées basées sur le deuxième champ et les champs suivants de l'index conjoint ;

7. Ne pas utiliser select * ; 8. Veuillez essayer d'utiliser l'ordre croissant pour le tri ; 🎜>

9. Essayez d'utiliser union (Innodb) pour les requêtes

10. Haute sélectivité de l'index composé

11. Les champs groupby sont inclus dans l'index pour réduire le tri et l'efficacité sera plus élevée.

En plus des règles d'utilisation des index ci-dessus, vous devez prêter une attention particulière aux points suivants lors de l'écriture de SQL :

1 Essayez d'éviter le SQL de transactions volumineuses, qui affectera la concurrence. performances et performances de la base de données. Synchronisation maître-esclave ;

2. Problème avec la limite des instructions de pagination

3. Veuillez utiliser tronquer pour supprimer tous les enregistrements de la table, n'utilisez pas delete ;

4. Ne laissez pas MySQL sécher les choses redondantes, telles que les calculs ;

5. Entrez et écrivez du SQL avec des champs pour éviter les problèmes causés par les modifications ultérieures des tables. (cela implique une analyse du dictionnaire de données, veuillez interroger les informations vous-même) ;

6. Utilisez select count(*) sur Innodb, car Innodb stockera les informations statistiques

7. () avec prudence.

3. Afficher le nombre de requêtes lentes : afficher le statut comme 'slow_queries' ;

HEAP est une version mysql antérieure

4. Expliquez l'analyse des instructions SQL inefficaces :

produira les informations suivantes :
select_type : signifie que type de requête.

table : La table qui génère l'ensemble de résultats.

type : Indique le type de connexion de la table

possible_keys : Indique les index qui peuvent être utilisés lors de l'interrogation Touche

 : indique l'index réel utilisé

Key_len : La longueur du champ d'index

ROWS : Le nombre de lignes analysées (estimé)

Extra : La description et la description de la situation d'exécution

Select_type tapez :

primaire : la requête la plus externe dans la sous-requête

sous-requête : la première sélection dans la couche interne de la sous-requête, le résultat ne dépend pas de la requête externe

sous-requête dépendante : sous-requête La première sélection dans la couche interne dépend de la requête externe

union : la deuxième sélection dans l'instruction union commence par toutes les sélections suivantes

simple : mode simple

résultat de l'union : union résultat de l'union Fusionner les résultats dans

type Type :

tous : une analyse complète de la table n'est généralement pas bonne

système : la table n'en a qu'un row (= table système) Il s'agit d'un type de jointure const Un cas particulier

const : La table a au plus une ligne correspondante

type supplémentaire :

aucune table : de dual est utilisé dans l'instruction de requête ou ne contient aucune clause from

UTILISATION DE FILESORT : lorsque la requête contient l'opération Order By, elle ne peut pas être utilisée pour terminer le tri

Impossible là où cela est remarqué après ReadingConst Tables : MySQL Query Optimizer

Il est impossible de déposer des informations statistiques en collectant des informations statistiques Au résultat

Utilisation de temporaire : Certaines opérations doivent utiliser des tables temporaires, groupe commun par, tri par

Utilisation dewhere : vous n'avez pas besoin de lire toutes les informations du tableau, vous pouvez obtenir les données requises uniquement via l'index

4 Pourquoi la vitesse de requête devient-elle plus rapide après l'utilisation de l'index ?

S'il n'y a pas d'index pour une requête normale, elle sera exécutée tout le temps et la requête continuera après l'obtention d'une correspondance opportune. Il n'y a aucune garantie que la requête suivra. se renseigner ? L'indexation du texte intégral est requise.

■ Choses à noter lors de l'utilisation des index

Coût de l'indexation :

1. Occupant Espace disque

2. Il a un impact sur les opérations DML (insertion, mise à jour, création) et ralentit

■Résumé : les index ne doivent être créés que lorsque les conditions suivantes sont remplies

A. Certainement

est souvent utilisé dans où B. Le contenu de ce champ n'est pas une valeur unique (sexe)

C. 🎜>

weight Précautions d'utilisation des index ：

alter table dept add index myind (dname,loc); // dname est la colonne de gauche, loc est la colonne de droite

Dans les situations suivantes, l'index peut être utilisé

a Pour l'index multi-colonnes créé, tant que la condition de requête utilise la colonne la plus à gauche, l'index sera généralement utilisé pour expliquer select * from dept. où dname='aaa';

b. Pour ceux qui utilisent like Query, si la condition de requête est '%aaa', l'index ne sera pas utilisé et 'aaa%' utilisera l'index

L'index ne sera pas utilisé dans les situations suivantes :

a S'il existe ou, même s'il existe un index conditionnel, il ne sera pas utilisé Autrement dit, tous les champs utilisés sont obligatoires. pour créer des index : Essayez d'éviter d'utiliser le mot-clé ou

b Pour les index multi-colonnes, ce n'est pas la première partie utilisée, l'index ne sera pas utilisé

expliquez select. * from deptwhere loc='aaa';// Lors d'une indexation multi-colonnes, loc est la colonne de droite, l'index ne sera pas utilisé

c.like La requête commence par % Si elle doit être utilisée. , utilisez l'index de texte intégral pour interroger

d. Si le type de colonne est une chaîne, les données doivent être placées entre guillemets dans la condition, sinon l'index ne sera pas utilisé

e. . Si MySQL estime qu'il est préférable d'utiliser une analyse complète de la table plutôt que d'utiliser un bloc d'index, alors n'utilisez pas d'index

Comment choisir le moteur de stockage pour MySQL

1 : myISAM

. Si le tableau est correct, les exigences pour les affaires ne sont pas élevées. Les collègues les interrogent et les ajoutent principalement, comme la publication et la réponse dans BBS.

2 : InnoDB

                                                                                                                                                                                                                                                 et il est stocké dans l'entrepôt et saisi pour interrogation et modification en même temps.

La différence entre myISAM et InnoDB :

1. L'insertion par lots MyISAM est rapide, l'insertion InnoDB est lente et myISAM ne trie pas lors de l'insertion.

2. InnoDB prend en charge les transactions, mais myISAM ne prend pas en charge les transactions.

3. MyISAM prend en charge l'index de texte intégral,

4. Mécanisme de verrouillage, myISAM est un verrou de table, InnoDB est un verrou de ligne

5. clés, InnoDB prend en charge les clés étrangères Jian

① Dans les applications avec des exigences de progression élevées, il est recommandé d'utiliser des données à virgule fixe pour stocker les valeurs, le groupe U pour garantir l'exactitude des données, la progression deciaml est supérieure à float , essayez d'utiliser

② pour le stockage. Si la base de données myISAM du moteur nécessite des opérations de suppression et de modification, la fonction optimise_table_name doit être exécutée régulièrement pour défragmenter la table.

③ Le type de date doit choisir le premier type avec la plus petite référence de stockage en fonction des besoins réels

Sauvegarder manuellement la base de données :

Entrez cmd

.

2. Mysqldump –uroot –proot base de données [nom de la table 1, nom de la table 2…] > Chemin du fichier

Par exemple : mysqldump -uroot -proot temp >

Restaurer les données du fichier de sauvegarde :

Source d:/temp.bak (dans la console MySQL)

Ressources matérielles et système d'exploitation raisonnables

Maître

Slave1

Slave2

Slave3

La base de données principale est utilisée pour l'écriture et slave1-slave3 est utilisée pour la sélection. Chaque base de données

partage moins. beaucoup de pression.

Pour mettre en œuvre cette méthode, le programme doit être spécialement conçu. Le maître est utilisé pour l'écriture et l'

l'esclave est utilisé pour la lecture, ce qui ajoute une charge supplémentaire au développement du programme. Bien sûr, il existe déjà un middleware pour implémenter ce

proxy, qui est transparent pour le programme pour lire et écrire quelles bases de données. Il existe un proxy mysql officiel, mais

est toujours une version alpha. Sina a amobe pour mysql, qui peut également atteindre cet objectif. La structure

est la suivante :

5. Segmentation des tables

. Segmentation horizontale ：

Lorsque nous proposons la récupération de tableaux contenant de grandes quantités de données, nous devons trouver les normes du tableau en fonction des besoins de l'entreprise, restreindre la méthode de récupération de l'utilisateur sur la page de récupération et coopérer avec la pagination,

        Cas : Table utilisateur avec une grande quantité de données

Trois tables : qqlogin0, qqlogin1, qqlogin2

Mettez l'identifiant utilisateur%3 dans différentes tables en fonction des résultats

create tableqqlogin0(

id int non signé clé primaire non nulle,/* Cet identifiant ne peut pas être défini sur incrémentation automatique*/

nom varchar (32) non nul par défaut'',

pwd varchar(32)not null default''

) engine = myisam default charset = utf8;

Créer une table qqlogin1(

id int non signé clé primaire non nulle, / *Cet identifiant ne peut pas être défini sur incrémentation automatique* /

nom varchar (32) non nul par défaut'',

pwd varchar ( 32) not null default''

) engine = myisam default charset = utf8;

Créer une table qqlogin2 (

id int non signé non nul primaire key, /*Cet identifiant ne peut pas être défini sur une incrémentation automatique /

Partage vertical :

Mettez certains champs d'une table. Ces champs ne sont pas pertinents lors de l'interrogation, mais la quantité de données l'est. large Nous vous recommandons de mettre ces champs dans un tableau, améliorant ainsi l'efficacité

6. Configuration MySQL optimisée

MY.INI

port = 3306 Le port par défaut est 3306,

Si vous souhaitez modifier le port port = 3309, dans mysql_connect('localhost:3309', 'root', 'root'); notez que

query_cache_size = 15M c'est la taille du cache de requêtes

Les paramètres InnoDB peuvent également être utilisés. Augmentez les deux paramètres suivants

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size = 1G

myisam doit ajuster key_buffer_size

Le réglage des paramètres dépend également de l'état, utilisez show status Vous pouvez voir l'état actuel pour décider quels paramètres doivent être ajustés

Sauvegarde incrémentielle

Réel. case :

Comment effectuer une sauvegarde et une récupération incrémentielles

Étapes :

Comme le montre la figure 1, configurez le fichier my.ini ou my.cof, activez la sauvegarde binaire

2, redémarrez MySQL

Après le démarrage, vous trouverez quelques fichiers générés dans le répertoire mylog

Parmi eux : E : fichier d'index du journal binaire mylog.index, quels sont les fichiers de sauvegarde

E : Le journal binaire mylog.000001 stocke le fichier pour les opérations de la base de données d'objets utilisateur

3 Lorsque nous opérons (sélectionnons)

vérifiez le bac qui doit entrer dans le. Répertoire d'installation de MySQL, puis exécutez le fichier mysqlbinlog et ajoutez le chemin du fichier

comme indiqué dans la figure 4 pour restaurer au point temporel d'une certaine déclaration

4,1 Répondre en fonction du moment

Mysqlbinlog -stop-datetime="2013-01-17 12:00:23"d:/binlog/mylog.000001 | mysq -uroot - p

(Restaurer toutes les données avant l'heure d'arrêt)

Mysqlbinlog-start-datetime="2013-01-17 12:00:23" d:/binlog/ mylog.000001 | mysq -uroot -p

(restaurer toutes les données une fois l'heure de début écoulée)

4,2Restaurer en fonction de la position

Mysqlbinlog-stop-position= "234"d : /binlog/mylog.000001 | mysq -uroot -p

(Restaurer toutes les données avant l'heure d'arrêt)

Mysqlbinlog-start-position="234" d:/ binlog/mylog.000001 | mysq -uroot -p

(restaurer toutes les données après l'heure de début)

Pour plus de questions connexes, veuillez visiter le site Web PHP chinois : https:/ /www.php.cn/

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!