Les programmeurs doivent connaître le manuel de spécification d'utilisation de MySQL

Tutoriel MySQLIntroduction en colonne Manuel de spécification d'utilisation de MySQL

Autres recommandations d'apprentissage gratuites associées : Tutoriel MySQL (Vidéo)

À mesure que le temps passe et que le nombre d'utilisateurs d'une plateforme ou d'un système augmente, les opérations de base de données ont tendance à ralentir et dans le développement d'applications Java, la base de données est particulièrement importante, absolument ; Dans la plupart des cas, les performances de la base de données déterminent les performances du programme. S'il y a plus de fosses enterrées au début, la base de données deviendra le goulot d'étranglement de l'ensemble du système à un stade ultérieur, par conséquent, une utilisation plus standardisée de MySQL est nécessaire ; indispensable au développement.

1. Convention de dénomination de la base de données MySQL

1. Tous les préfixes de table de la base de données utilisent l'acronyme du nom du projet

2. les lettres et les mots sont séparés par des traits de soulignement ;

3. Les mots et mots-clés réservés MySQL sont interdits dans tous les noms d'objets de base de données. Les requêtes SQL impliquant des mots-clés doivent mettre les mots-clés entre guillemets simples

4 ; . Les noms de tous les objets de la base de données ne doivent pas dépasser 32 caractères, et la dénomination doit suivre le principe de connaître le nom après l'avoir vu

5. La table temporaire de la base de données doit être préfixée par pro_tmp_ et suffixée par ; la date 20190917, sauvegarde La table doit être préfixée par pro_bac et suffixée par timestamp (pro est l'acronyme du nom du projet)

6. Tous les noms de colonnes et types de colonnes dans la base de données qui stockent les mêmes données doivent être cohérents.

2. Spécifications de conception de base pour la base de données MySQL

1. Sauf indication contraire, le moteur de stockage Innodb sera utilisé lors de la création des tables.

Choisir le bon moteur peut améliorer les performances de la base de données, comme InnoDB et MyISAM sont les deux types de tables les plus couramment utilisés par de nombreuses personnes lors de l'utilisation de MySQL. Les deux types de tables ont leurs propres avantages et inconvénients, selon. en fonction de la situation spécifique. Cela dépend de l'application ;

La différence fondamentale est la suivante : le type MyISAM ne prend pas en charge le traitement avancé tel que le traitement des transactions, mais le type InnoDB met l'accent sur les performances et ses performances. les temps d'exécution sont plus rapides que le type InnoDB, mais il ne fournit pas de prise en charge des transactions, tandis qu'InnoDB fournit une prise en charge des transactions et des fonctions de base de données avancées telles que les clés étrangères

Par conséquent, il prend en charge le traitement des transactions, prend en charge les clés étrangères, prend en charge les crash ; capacités de réparation et de contrôle de concurrence, et constitue notre stockage préféré lors de la création du moteur de tables.

2. Les jeux de caractères des bases de données et des tables utilisent UTF8.

Les jeux de caractères des bases de données et des tables utilisent utf8. S'il y a des champs qui doivent stocker des expressions emoji, etc. table ou champ Il devient utf8mb4 car utf8 est connu comme code universel, qui ne nécessite pas de transcodage, ne présente aucun risque de caractères tronqués et économise de l'espace, et utf8mb4 est rétrocompatible avec utf8.

3. Les commentaires doivent être ajoutés à toutes les tables et champs lors de la conception de la base de données.

Utilisez la clause Comment pour ajouter des commentaires sur les tables et les colonnes, ou ajoutez des commentaires directement dans la colonne de commentaires de la base de données. outil de connexion, dès le début du projet Effectuer la maintenance du dictionnaire de données.

Utilisez la clause Comment pour ajouter des commentaires tels que :

-- 1、创建表：
CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number);
-- 2、添加表注释：
Comment on table t1 is '个人信息';
-- 3、添加字段注释：
comment on column t1.id is 'id';
comment on column t1.nameis '姓名';
comment on column t1.age is '年龄';

Utilisez l'outil de connexion à la base de données pour ajouter des commentaires :

4. quantité de données dans une seule table Contrôlez-la dans les 5 millions

Essayez de contrôler la taille des données dans une seule table Il est recommandé de les contrôler dans les 5 millions. 5 millions n'est pas la limite de MySQL ; base de données, mais trop de données ne permettent pas de modifier et de sauvegarder la structure de la table et de restaurer les données, et d'utiliser de manière appropriée des méthodes telles que la sous-base de données et la sous-table pour contrôler la taille des données dans une seule table.

5. Soyez prudent lorsque vous utilisez des tables partitionnées MySQL

Le partitionnement consiste à diviser les données d'une table en plusieurs plus petites qui sont plus faciles à gérer d'une certaine manière, par exemple par mois dans le temps. . partie, mais constitue toujours une table logiquement ; une table partitionnée apparaît physiquement sous la forme de plusieurs fichiers, mais apparaît toujours logiquement comme la même table, et la clé de partition doit être soigneusement sélectionnée. L'efficacité des requêtes entre partitions peut être inférieure, et elle l'est ; Il est recommandé d'utiliser des partitions physiques. Gérez le Big Data via des tables et d'autres méthodes.

6. Essayez de séparer les données chaudes et froides et de réduire la largeur des tables

MySQL limite chaque table pour stocker jusqu'à 4096 colonnes, et la taille de chaque ligne de données ne dépasse pas 65535 octets. Afin de réduire la surcharge des threads d'E/S du disque, il faut un contrôle approprié de la largeur de la table, car plus la table est large, plus la mémoire occupée lors du chargement de la table dans le pool de mémoire tampon est grande, et plus de threads d'E/S seront consommés ; De plus, afin de garantir le taux de réussite du cache mémoire des données chaudes, d'utiliser le cache plus efficacement et d'éviter de lire des données froides inutiles, essayez de placer les colonnes fréquemment utilisées dans la même table pour éviter les opérations de corrélation inutiles.

7. Soyez prudent lors de la création de champs réservés

Lors de la conception des tables de base de données, certains amis conçoivent non seulement les champs actuellement requis, mais mettent également de côté plusieurs champs comme sauvegarde. Par exemple, j'ai conçu une table de personnes (Person), qui a ajouté divers champs nécessaires, notamment le nom (Nom), le sexe (Sexe), la date de naissance (anniversaire), etc.

Juste au cas où, par exemple, la table Personne pourrait impliquer des études supérieures, un lieu de travail, un statut matrimonial, des photos, etc., donc cinq champs varchar2 ont été ajoutés, appelés Text1, Text2...Text5 ; mesure de précaution, mais en fait ce n'est pas nécessaire, car un grand nombre de champs réservés gaspilleront de l'espace, les champs réservés ne peuvent pas être connus par leur nom, le type de données stockées ne peut pas être confirmé et il est difficile de modifier le type de champ. peut entraîner des problèmes tels que le verrouillage de la table.

Pour cette situation, vous pouvez vous référer aux deux solutions suivantes :

1. Si le nombre est petit et que la nature de l'information est étroitement liée au tableau d'origine, vous pouvez ensuite l'ajouter directement au tableau d'origine. Ajoutez des champs au tableau et mettez à jour les données pertinentes

2. Si le nombre est grand ou s'il ne s'agit pas d'un attribut crucial de l'original ; objet table, vous pouvez alors ajouter une nouvelle table, puis les connecter via des valeurs clés

8 Il est interdit de stocker des données binaires volumineuses telles que des images et des fichiers dans la base de données <🎜. >

Si vous stockez des fichiers dans la table de la base de données et que les fichiers sont généralement très volumineux, lorsque la base de données effectue une opération de lecture, un grand nombre d'opérations d'E/S aléatoires seront effectuées. Les fichiers volumineux nécessitent du temps et des performances. -consommateur, entraînant une croissance rapide de la quantité de données sur une courte période de temps ; par conséquent, les images et les fichiers sont généralement stockés sur le serveur de fichiers, la base de données n'est utilisée que pour stocker les informations sur l'adresse des fichiers.

3. Spécifications de conception des champs de base de données MySQL

1. Donnez la priorité au plus petit type de données qui répond aux besoins de stockage.

Considérez principalement les performances de l'index, car plus le champ de la colonne est grand, plus l'espace requis pour construire l'index est grand, donc le nombre de nœuds d'index pouvant être stockés dans une page sera plus petit. Lors du parcours Plus il faut de temps d'E/S, plus les performances de l'index seront mauvaises.

2. Évitez d'utiliser les types de données TEXT et BLOB

Évitez d'utiliser les types de données TEXT et BLOB. Le type TEXT le plus courant peut stocker 64 Ko de données dans la mémoire MySQL. Types de données volumineuses. Si la requête contient de telles données, vous ne pouvez pas utiliser de tables temporaires de mémoire lors de l'exécution d'opérations telles que le tri. Vous devez utiliser des tables temporaires de disque pour effectuer des opérations.

Les types TEXT et BLOB ne peuvent utiliser que des index de préfixe (). Lorsque l'index est une très longue séquence de caractères, l'index prendra beaucoup de mémoire et sera très lent. À ce stade, un index de préfixe sera utilisé. Ce qu'on appelle l'index de préfixe consiste à utiliser les premières lettres de. l'index comme un index, mais cela nécessite de réduire le taux de répétition de l'index, nous devons donc également juger du taux de répétition de l'index du préfixe ;), parce que MySQL a une longueur limitée pour le champ d'index, le type TEXT ne peut utiliser que le index de préfixe, et il ne peut pas y avoir de valeur par défaut sur la colonne TEXT

Si vous devez l'utiliser, il est recommandé de séparer les colonnes BLOB ou TEXT dans une table étendue distincte et de ne pas utiliser

lors de l'interrogation, supprimez simplement les colonnes nécessaires. select *

3. Évitez d'utiliser le type d'énumération ENUM

La modification de la valeur ENUM nécessite l'utilisation de l'instruction ALTER

L'opération ORDER BY du type ENUM est inefficace ; >

Interdit Utiliser des valeurs numériques comme valeurs d'énumération pour ENUM.

4. La valeur par défaut de toutes les colonnes est définie comme NON NULL

Toutes les colonnes NULL de la base de données nécessitent un espace supplémentaire pour être stockées, elles occuperont donc plus d'espace

<🎜 ; > La base de données doit gérer les valeurs NULL spécialement lors des comparaisons et des calculs.

5. Utilisez le type TIMESTAMP (4 octets) ou DATETIME (8 octets) pour stocker l'heure

TIMESTAMP La plage de temps stockée est : 1970-01-01 00:00:01 ~ 2038 - 01-19-03:14:07;

TIMESTAMP occupe les mêmes 4 octets que INT, mais est plus lisible que le type INT S'il dépasse la plage de valeurs TIMESTAMP, utilisez le stockage de type DATETIME ;< 🎜. >

Inconvénients de l'utilisation du type chaîne pour stocker l'heure : la fonction date ne peut pas être utilisée pour les calculs de comparaison et le stockage de chaîne prend plus de place.

6. Les données relatives aux montants financiers doivent utiliser le type décimal

Point flottant précis : décimal

Point flottant non précis : float, double

Décimal Le le type est un nombre à virgule flottante précis, qui ne perdra pas en précision lors du calcul ; l'espace occupé est déterminé par la largeur définie. Tous les 4 octets peuvent stocker 9 chiffres, et le point décimal occupe également un octet en plus, le type Decimal peut être utilisé. être utilisé pour le stockage Un type de données plus grand que bigint.

4. Spécifications de conception des index MySQL

1. Le nombre d'index pour chaque table ne doit pas dépasser 5

Les index peuvent augmenter l'efficacité des requêtes, mais ils réduiront également l'insertion et update L'efficacité peut même réduire l'efficacité des requêtes dans certains cas, donc plus n'est pas toujours mieux et le nombre doit être contrôlé.

2. Chaque table Innodb doit avoir une clé primaire

Innodb est une table organisée en index, et l'ordre logique de stockage des données est le même que l'ordre de l'index ;

Chaque table peut avoir plusieurs index, mais l'ordre de stockage de la table ne peut être qu'un seul. Innodb organise la table dans l'ordre des index de clé primaire, n'utilisez donc pas de colonnes fréquemment mises à jour, UUID, MD5, HASH et chaînes de caractères. en tant que clés primaires. , ces colonnes ne peuvent pas garantir la croissance séquentielle des données et il est recommandé d'utiliser des valeurs d'ID à incrémentation automatique pour les clés primaires.

3. Essayez d'éviter d'utiliser des contraintes de clé étrangère

Il n'est pas recommandé d'utiliser des contraintes de clé étrangère (clé étrangère), mais vous devez créer un index sur la clé associée entre les tables <🎜 ; >

Bien que les clés étrangères puissent garantir l'intégrité référentielle des données, les clés étrangères affecteront également les opérations d'écriture de la table parent et de la table enfant, réduisant ainsi les performances et rendant les tables plus couplées. Il est recommandé de l'implémenter sur le. côté affaires.

五、MySQL数据库SQL开发规范

1、建议使用预编译语句进行数据库操作

预编译语句可以重复使用，相同的SQL语句可以一次解析，多次使用，减少SQL编译所需要的时间，提高处理效率；此外，还可以有效解决动态SQL带来的SQL注入问题。

2、避免数据类型的隐式转换

隐式转换如：SELECT 1 + "1"；数值型 + 字符型 的隐式转换有可能会导致索引失效，以及一些意想不到的结果等。

3、充分利用表中存在的索引

1）避免使用双%号的查询条件

如 WHERE first_name like '%James%'，若无前置%，只有后置%，则执行SQL语句时会用到列上的索引，双%号则不会使用列上的索引。

2）一条SQL语句只能使用复合索引中的一列进行范围查询

例如有weight、age、sex三列的联合索引，在查询条件中有weight列的范围查询，则在age和sex列上的索引将不会被使用；因此，在定义联合索引时，若某列需要用到范围查询，则将该列放到联合索引的右侧。

3）使用not exists 代替not in

因为not in 在SQL语句中执行时会导致索引失效。

4、杜绝使用SELECT * ，必须使用SELECT <字段列表> 查询

因为使用SELECT * 查询会消耗更多的CPU、IO和网络宽带资源，并且查询时无法使用覆盖索引。

5、禁止使用不含字段列表的INSERT 语句

如：INSERT into table_name values ('1','2','3'); 改为带字段列表的INSERT 语句：INSERT into table_name('c1','c2','c3') values ('1','2','3');

6、避免使用子查询，可以把子查询优化为join 关联操作

但是，通常子查询在in 子句中，且子查询中为简单SQL(即不包含union、group by、order by、limit从句)时，才可以把子查询转化为join关联查询进行优化；

子查询性能差的原因：

• 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；

• 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

7、避免使用JOIN 关联太多表

1）在Mysql中，对于同一个SQL关联（join）多个表，每个join 就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大；

2）如果程序中大量的使用了多表关联的操作，同时join_buffer_size(MySQL允许关联缓存的个数)设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响服务器数据库性能的稳定性；

3）此外，对于关联操作来说，会产生临时表影响查询效率，而Mysql最多允许关联61个表，建议不超过5个；

8、对同一列对象进行or 判断时，使用in 替代or

in 的值只要涉及不超过500个，则in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

9、禁止使用order by rand() 进行随机排序

10、禁止在WHERE 从句中对列进行函数转换和计算

因为在WHERE 从句中对列进行函数转换或计算时会导致索引无法使用。

No推荐：

where date(end_time)='20190101'

推荐：

where end_time >= &#39;20190101&#39; and end_time < &#39;20190102&#39;

11、在明显不会有重复值时使用UNION ALL 而不是UNION

1）UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作；

2）UNION ALL 不会再对结果集进行去重操作；

12、把复杂、较长的SQL 拆分为为多个小SQL 执行

1）大SQL在逻辑上比较复杂，是需要占用大量CPU 进行计算一条SQL语句；

2）在MySQL中，一条SQL 语句只能使用一个CPU 进行计算；

3）SQL拆分后可以通过并行执行来提高处理效率。

六、MySQL数据库行为规范

1、超过100万行数据的批量操作(update delete insert)，分多次进行

大批量操作可能回造成严重的主从延迟；

binlog日志为row格式时会产生大量的日志；

避免产生大事物操作。

2、对于大表使用pt-online-schema-change 修改表结构

1）避免大表修改产生的主从延迟、避免在对表字段进行修改时进行锁表；

2) pt-online-schema-change Il créera d'abord une nouvelle table avec la même structure que la table d'origine, modifiera la structure de la table sur la nouvelle table, puis copiera les données de la table d'origine dans la nouvelle table , et ajoutez quelques déclencheurs à la table d'origine ; puis copiez les nouvelles données de la table d'origine dans la nouvelle table. Après avoir copié toutes les données de la ligne, nommez la nouvelle table la table d'origine et supprimez la table d'origine. décomposez l'opération DDL d'origine en plusieurs petits lots pour l'exécution.

3. Il est interdit d'accorder une super autorisation au compte utilisé par le programme

Lorsque le nombre maximum de connexions est atteint, exécuter un utilisateur avec une super autorisation pour se connecter à une super autorisation ne peut être laissé au DBA le soin de gérer le compte problématique à utiliser.

4. Pour que le programme se connecte au compte de base de données, suivez le principe du minimum d'autorisations

Le compte de base de données utilisé par le programme ne peut être utilisé que sous une seule base de données, et en principe, la Le compte utilisé par le programme n'accorde pas d'autorisations de suppression.

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!