Résumé des méthodes d'optimisation de la base de données Mysql (à lire absolument)-tutoriel mysql-php.cn

Le contenu de cet article est un résumé des méthodes d'optimisation des bases de données Mysql. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

L'apprentissage n'a pas de fin, et l'optimisation des bases de données est divisée en différents aspects. Ici, j'ai fait un résumé relativement complet et je l'ai partagé avec des collègues qui travaillent ou étudient.

L'optimisation de la base de données est divisée en sept aspects suivants :

1 , La conception du tableau doit être conforme à Trois formes normales (Trois formes normales inverses appropriées peuvent également être utilisées

Ajoutez des index appropriés, qui ont un grand impact sur la vitesse des requêtes. , des index doivent être ajoutés (index de clé primaire, index unique, index ordinaire, index de texte intégral

3.Ajouter des procédures stockées, des déclencheurs appropriés); transactions, etc.;

4 , séparation en lecture et en écriture (base de données maître-esclave)

5. Quelques optimisations des instructions SQL (instructions SQL avec une vitesse d'exécution des requêtes relativement lente) ; >

6. Partitionnement de table (

Division de table : divisez une grande table en plusieurs tables. Partition : allouez le contenu d'une table à différentes zones pour le stockage

); le matériel du serveur MySQL. Ensuite, j'expliquerai la méthode d'optimisation en détail.

Première et troisième forme normale

Première forme normale :

Atomicité : Les champs du tableau ne peuvent plus être divisés. Tant qu'il s'agit d'une base de données relationnelle, elle répondra naturellement à la première forme normale

Base de données relationnelle (avec les notions de lignes et de colonnes). ) : mysql , sql server, oracle, db2, infomix, sybase, postgresql, lors de la conception, avoir d'abord l'enregistrement spécifique bibliothèque->table->champ->(contenu) : lors du stockage des données, les champs doivent être conçus .

Base de données non relationnelle (généralement appelée base de données nosql) : memcache, redis, momgodb, etc.

Deuxième forme normale :

Il n'y a pas d'enregistrements identiques dans une table, qui peuvent être résolus à l'aide d'une clé primaire

Troisième forme normale :

Les données redondantes ne peuvent pas être stockées dans la table

Conception à trois paradigmes inversés :

Tableau des albums tr>

Nom de l'album

相册表
ID	相册名称	相册浏览量
1	生活照	100
2	工作照	100

Vues d'album

照片表
ID	照片名称	相册ID	浏览量
1	我的小狗	1	49
2	我的小猫	1	51
3	我的同事	2	100

ViePhotos

100

Photo de travail

100

Si nous voulons calculer les vues d'un album, nous pouvons ajouter le champ des vues de l'album au tableau des albums et mettre à jour les vues de l'album en même temps lors de la navigation dans les photos.

2. Activer les requêtes lentes

Les requêtes lentes MySQL sont désactivées par défaut et les instructions SQL qui dépassent 10 secondes sont enregistré par défaut.

1. Afficher la durée d'enregistrement des requêtes lentes :

show variables like ‘long_query_time’;

Copier après la connexion

2. temps de requête :

set long_query_time=2;

Copier après la connexion

3. Testez via la fonction suivante :

benchmark(count,expr)   函数可以测试执行count次expr操作需要的时间

Copier après la connexion

3. Créez un index

1. Caractéristiques de l'index de clé primaire :

(1) Il y a au plus un index de clé primaire dans une table

(2) Un index de clé primaire peut pointer à plusieurs colonnes

(3) Les colonnes de l'index de clé primaire ne peuvent pas avoir de valeurs en double, ni avoir des valeurs nulles

(4) L'index de clé primaire est très efficace.

2. Caractéristiques des index uniques :

(1) Il peut y avoir plusieurs index uniques dans une table

(2) Un index unique peut pointer vers plusieurs colonnes,

(3) Si non null n'est pas spécifié sur l'index unique, la colonne peut être vide et il peut y avoir plusieurs valeurs nulles en même temps ,

(4) L'index unique est plus efficace.

3. Index ordinaire :

L'utilisation d'un index ordinaire vise principalement à améliorer l'efficacité des requêtes

4 , Index de texte intégral

L'index de texte intégral mysql5.5 fourni avec mysql ne prend pas en charge le chinois, il prend en charge l'anglais et le moteur de stockage de la table doit être myisam. Si vous souhaitez prendre en charge le chinois, il existe deux options :

(1) Utiliser la version chinoise d'aphinx coreseek (pour remplacer l'index de texte intégral)

(2) Plug-in mysqlcft.

Principaux problèmes liés à l'ajout d'un index :

(1) Les index doivent être créés pour les champs fréquemment utilisés comme conditions de requête <🎜. >L'unicité est trop élevée Les champs médiocres ne conviennent pas à la création d'index séparément, même s'ils sont fréquemment utilisés comme conditions de requête, les champs mis à jour très fréquemment ne conviennent pas à la création d'index

(2) n'apparaîtra pas dans la clause WHERE. Les champs ne doivent pas être indexés. Bien que la vitesse des requêtes soit améliorée,

affectera l'efficacité des ajouts et des suppressions . Et le fichier d'index prendra de la place.

4. Tables et partitions

Table de partitionnement verticale (table principale de contenu + table supplémentaire) :

Table principale de contenu : stocke certaines informations publiques de diverses données, telles que le nom des données, l'heure ajoutée, etc.,

Vous pouvez utiliser plusieurs tables supplémentaires, qui stockent des informations uniques sur certaines données.

La raison principale : les données de la table principale de contenu sont fréquemment consultées.

Caractéristiques : différentes structures de table

Sous-table horizontale :

Traduire la table Les données existent dans différentes tables .

Caractéristiques : Même structure de table

Partition :

consiste à stocker une table dans différentes zones du disque, mais il s'agit toujours d'une seule table.

Concepts de base :

(1)Plage – Ce mode permet de diviser les données en différentes portées. Par exemple, une table peut être divisée en plusieurs partitions par année.

(2)Liste (liste prédéfinie) – Ce mode permet au système de diviser les données selon la valeur d'une liste prédéfinie.

(3)Hash (Hash) – Ce mode permet le calcul de la Hash Key d'une ou plusieurs colonnes du tableau, et enfin les données correspondant aux différentes valeursde ces zones de code de hachage sont divisées. Par exemple, vous pouvez créer une table qui partitionne la clé primaire de la table.

(4)Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

分区表的限制：

(1)只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列。

(2)最大分区数目不能超过1024。

(3)如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。

(4)按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多。

五、并发处理的锁机制

锁机制：在执行时，只有一个用户获得锁，其他用户处于阻塞状态，需要等待解锁。

mysql 的锁有以下几种形式：

表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam引擎属于这种类型。

行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb属于这种类型。

表锁的演示：