Résumé des méthodes d'optimisation de la base de données Mysql (à lire absolument)

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>

ID

Nom de l'album

相册表

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

Vues d'album

照片表

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

1

ViePhotos

100

2

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’;

2. temps de requête :

set long_query_time=2;

3. Testez via la fonction suivante :

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

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 valeurs​​de 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属于这种类型。

表锁的演示：

1.对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的操作。

2.表添加读锁后，其他进程对该表只能查询操作，修改时会被阻塞。

3.当前进程，能够执行查询操作，不能执行修改操作。不能对没有锁定的表进行操作。

4.锁表的语法：

lock table 表名 read|write

5.也可以锁定多个表

6.对myisam表的写操作（加写锁），会阻塞其他进程对锁定表的任何操作，不能读写，

7.表加写锁后，则只有当前进程对锁定的表，可以执行任何操作。其他进程的操作会被阻塞。

 行锁的演示：

1.innodb存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，innodb才会使用行级锁，否则，innodb使用表锁。

2.开启行锁后，当前进程在针对某条记录执行操作时，其他进程不能操作和当前进程相同id的记录。

php里面有文件锁，在实际的项目中多数使用文件锁，因为表锁，会阻塞，当对一些表添加写锁后，其他进程就不能操作了。这样会阻塞整个网站，会拖慢网站的速度。

相关推荐：

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!