Les spécifications de conception et de développement MySQ les plus détaillées [collection recommandée]

L'article suivant triera pour vous les spécifications de conception et de développement MySQL les plus détaillées. J'espère qu'il vous sera utile.

                                                                                                           

Convention de dénomination des objets de base de données

Objets de base de données

Les objets de base de données sont des composants courants de la base de données : table, index, vue et diagramme, valeur par défaut (par défaut), règle (règle). , déclencheur (Trigger), procédure stockée (Stored Procedure), utilisateur (User), etc. La convention de dénomination fait référence à la convention de dénomination des objets de base de données tels que la base de données (SCHEMA), la table (TABLE), l'index (INDEX), les contraintes (CONSTRAINTS), etc. [Recommandation : Tutoriel vidéo MySQL]

Convention de dénomination globale pour les objets de base de données

1. Utilisez des mots anglais significatifs pour nommer, séparés par des traits de soulignement au milieu des mots

2. Uniquement des lettres, des chiffres et des chiffres anglais. les traits de soulignement peuvent être utilisés pour nommer. Commencez par une lettre anglaise

3. Évitez d'utiliser des mots réservés MySQL tels que sauvegarde, appel, groupe, etc.

4. Tous les objets de base de données utilisent des lettres minuscules. définissez s'il faut être sensible à la casse, afin de garantir l'uniformité, nous standardisons ici toutes les expressions minuscules.

Convention de dénomination de la base de données

1. Le nom de la base de données ne doit pas dépasser 30 caractères.

2. Le nom de la base de données est généralement le nom du projet + l'abréviation représentant la signification de la bibliothèque. Par exemple, la base de données workflow du projet IM peut être im_flow.

3. Les clauses de jeu de caractères et de règle de classement par défaut doivent être ajoutées lors de la création de la base de données. Le jeu de caractères par défaut est UTF8 (dumbo a été migré pour utiliser utf8mb4)

4 ​​Le nom doit être en minuscules.

Convention de dénomination des tables

1. Les noms de tables normaux commencent par t_, t représente la signification de la table, et la règle de dénomination est t + module (contenant l'abréviation de la signification du module) + table (contenant l'abréviation de la signification du module). signification de la table), telle que la table d'informations sur l'éducation pour le module utilisateur : t_user_eduinfo.

2. Table temporaire (une table utilisée par les étudiants RD, QA ou DBA pour le traitement temporaire des données), règle de dénomination : préfixe temporaire + module + table + suffixe de date : temp_user_eduinfo_20210719temp_user_eduinfo_20210719

3、备份表（用于保存和归档历史数据或者作为灾备恢复的数据）命名规则，bak前缀+模块+表+日期后缀：bak_user_eduinfo_20210719

4、同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义

5、多个单词以下划线 _ 分隔

6、常规表表名尽量不超过30个字符，temp表和bak表视情况而定，也尽量简短为宜，命名应使用小写

字段命名规范

1、字段命名需要表示其实际含义的英文单词或简写，单词之间用下划线 _ 进行连接，如 service_ip、service_port。

2、各表之间相同意义的字段必须同名，比如a表和b表都有创建时间，应该统一为create_time，不一致会很混乱。

3、多个单词以下划线 _ 分隔

4、字段名尽量不超过30个字符，命名应该使用小写

索引命名规范

1、唯一索引使用uni + 字段名 来命名：create unique index uni_uid on t_user_basic(uid) 。

2、非唯一索引使用idx + 字段名 来命名：create index idx_uname_mobile on t_user_basic(uname,mobile) 。

3、多个单词以下划线 _ 分隔。

4、索引名尽量不超过50个字符，命名应该使用小写，组合索引的字段不宜太多，不然也不利于查询效率的提升。

5、多单词组成的列名，取尽可能代表意义的缩写，如 test_contact表member_id和friend_id上的组合索引：idx_mid_fid

3. (Utilisé pour enregistrer et archiver des données historiques ou comme données de reprise après sinistre) règles de dénomination, préfixe bak + module + table + suffixe de date : bak_user_eduinfo_20210719

4 Les tables du même module doivent utiliser le même nom que. autant que possible Préfixe, le nom de la table exprime la signification autant que possible

5. Plusieurs mots sont séparés par un trait de soulignement_

6. Les noms de table normaux ne doivent pas dépasser 30 caractères. être aussi court que possible. Le nom doit être en minuscules

Convention de dénomination des champs

1 Le nom du champ doit être un mot anglais ou une abréviation qui représente sa signification réelle, et les mots sont connectés par des traits de soulignement _, tels que <. code>service_ip, service_port.

2. Les champs ayant la même signification entre les tables doivent avoir le même nom. Par exemple, la table a et la table b ont toutes deux une heure de création, qui doit être unifiée sous le nom create_time. 🎜🎜3. Plusieurs mots sont séparés par un trait de soulignement _ 🎜🎜4. Les noms de champs ne doivent pas contenir plus de 30 caractères et le nom doit être en minuscules 🎜🎜🎜🎜Convention de dénomination des index🎜🎜1. nom du champ : créer un index unique uni_uid sur t_user_basic(uid). 🎜🎜2. Les index non uniques sont nommés en utilisant idx + nom du champ : créer l'index idx_uname_mobile sur t_user_basic(uname,mobile). 🎜🎜3. Plusieurs mots sont séparés par un trait de soulignement _. 🎜🎜4. Le nom de l'index ne doit pas dépasser 50 caractères. Le nom doit être en minuscules. Les champs de l'index combiné ne doivent pas être trop nombreux, sinon cela ne permettra pas d'améliorer l'efficacité des requêtes. 🎜🎜5. Pour les noms de colonnes contenant plusieurs mots, utilisez des abréviations qui représentent autant de sens que possible, comme l'index combiné sur les tables test_contact member_id et friend_id. :idx_mid_fid. 🎜🎜6. Comprenez le principe du préfixe le plus à gauche des index combinés et évitez de créer des index à plusieurs reprises. Si (a,b,c) est créé, cela équivaut à créer (a), (a,b), (a,b,c). ). 🎜🎜🎜🎜Convention de dénomination des vues🎜🎜1. Le nom de la vue commence par v, ce qui signifie vue. La structure complète est v + l'abréviation de la signification du contenu de la vue. 🎜🎜2. Si la vue ne provient que d'une seule table, il s'agit de v+nom de la table. Si la vue est générée par l'association de plusieurs tables, utilisez v + trait de soulignement (_) pour relier plusieurs noms de tables. Le nom de la vue ne doit pas dépasser 30 caractères. S'il dépasse 30 caractères, utilisez l'abréviation. 🎜🎜3. Il est strictement interdit aux développeurs de créer des vues sans besoins particuliers. 🎜🎜4. Les noms doivent être en minuscules. 🎜

Convention de dénomination des procédures stockées

1. Le nom de la procédure stockée commence par sp, ce qui signifie procédure stockée (procédure de stockage). Plusieurs mots sont connectés par des traits de soulignement (_). La fonction de la procédure stockée doit être reflétée dans son nom. Le nom de la procédure stockée ne doit pas dépasser 30 caractères. storage procedure）。之后多个单词以下划线（_）进行连接。存储过程命名中应体现其功能。存储过程名尽量不能超过30个字符。

2、存储过程中的输入参数以i_开头，输出参数以o_开头。

3、命名应使用小写。

create procedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100))

函数命名规范

1、函数名以func开始，表示function。之后多个单词以下划线（_）进行连接，函数命名中应体现其功能。函数名尽量不超过30个字符。

2、命名应使用小写。

create function func_format_date(ctime datetime)

触发器命名规范

1、触发器以trig开头，表示trigger 触发器。

2、基本部分，描述触发器所加的表，触发器名尽量不超过30个字符。

3、后缀（_i,_u,_d）,表示触发条件的触发方式（insert,update或delete）。

4、命名应使用小写。

DROP TRIGGER IF EXISTS trig_attach_log_d;CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW;

约束命名规范

1、唯一约束：uk_表名称_字段名。uk是UNIQUE KEY的缩写。比如给一个部门的部门名称加上唯一约束，来保证不重名，如下：

ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name);

2、外键约束：fk_表名，后面紧跟该外键所在的表名和对应的主表名（不含t_）。子表名和父表名用下划线(_)分隔。如下：

ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);

3、非空约束：如无特殊需要，建议所有字段默认非空(not null)，不同数据类型必须给出默认值(default)。

1 `id` int(11) NOT NULL,2 `name` varchar(30) DEFAULT '',3 `deptId` int(11) DEFAULT ,4 `salary` float DEFAULT NULL,

4、出于性能考虑，如无特殊需要，建议不使用外键。参照完整性由代码控制。这个也是我们普遍的做法，从程序角度进行完整性控制，但是如果不注意，也会产生脏数据。

5、命名应使用小写。

用户命名规范

1、 生产使用的用户命名格式为 code_应用

2、 只读用户命名规则为 read_应用

数据库对象设计规范

存储引擎的选择

1、如无特殊需求，必须使用innodb存储引擎。

可以通过 show variables like ‘default_storage_engine‘ 来查看当前默认引擎。主要有MyISAM 和 InnoDB，从5.5版本开始默认使用 InnoDB 引擎。点击这里进行刷题。

基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。

字符集的选择

1、如无特殊要求，必须使用utf8或utf8mb4。

在国内，选择对中文和各语言支持都非常完善的utf8格式是最好的方式，MySQL在5.5之后增加utf8mb4编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。

所以utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

可以使用如下脚本来查看数据库的编码格式

1 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';2 -- 或3 SHOW VARIABLES Like '%char%';

表设计规范

1、不同应用间所对应的数据库表之间的关联应尽可能减少，不允许使用外键对表之间进行关联，确保组件对应的表之间的独立性，为系统或表结构的重构提供可能性。目前业内的做法一般 由程序控制参照完整性。

2、表设计的角度不应该针对整个系统进行数据库设计，而应该根据系统架构中组件划分，针对每个组件所处理的业务进行数据库设计。

3、表必须要有PK，主键的优势是唯一标识、有效引用、高效检索，所以一般情况下尽量有主键字段。

4、一个字段只表示一个含义。

5、表不应该有重复列。

6、禁止使用复杂数据类型(数组,自定义等)，Json类型的使用视情况而定。

7、需要join

2. Les paramètres d'entrée dans la procédure stockée commencent par i_ et les paramètres de sortie commencent par o_.

3. Les noms doivent être en minuscules. 🎜

1 PRIMARY KEY (`id`),2 UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)

🎜🎜🎜Convention de dénomination des fonctions🎜🎜1. Le nom de la fonction commence par func, qui signifie fonction. Après cela, plusieurs mots sont connectés par des traits de soulignement (_) et leurs fonctions doivent être reflétées dans le nom de la fonction. Essayez de conserver les noms de fonctions ne dépassant pas 30 caractères. 🎜🎜2. Les noms doivent être en minuscules. 🎜

1 select brand_id,brand_name from  ds_brand_system where status=?  and define_id=?  and app_id=?

🎜🎜🎜Convention de dénomination des déclencheurs🎜🎜1. Les déclencheurs commencent par trig, ce qui signifie déclencheur trigger. 🎜🎜2. La partie de base décrit le tableau ajouté au déclencheur. Le nom du déclencheur ne doit pas dépasser 30 caractères. 🎜🎜3. Suffixe (_i, _u, _d), indiquant la méthode de déclenchement de la condition de déclenchement (insérer, mettre à jour ou supprimer). 🎜🎜4. Les noms doivent être en minuscules. 🎜

1 select brand_id,brand_name from  ds_brand_system where app_id=? and define_id=?  and  status=?

🎜🎜🎜Convention de dénomination des contraintes🎜🎜1. Contrainte unique : uk_table name_field name. uk est l'abréviation de UNIQUE KEY. Par exemple, ajoutez une contrainte unique au nom de département d'un département pour vous assurer qu'il n'y a pas de noms en double, comme suit : 🎜rrreee🎜2. Contrainte de clé étrangère : nom de fk_table, suivi du nom de la table où se trouve la clé étrangère et le nom de la table principale correspondante (à l'exclusion de t_). Le nom de la table enfant et le nom de la table parent sont séparés par un trait de soulignement (_). Comme suit : 🎜rrreee🎜 3. Contraintes non nulles : S'il n'y a pas de besoins particuliers, il est recommandé que tous les champs ne soient pas nuls par défaut et que différents types de données doivent recevoir des valeurs par défaut. 🎜rrreee🎜4. Pour des raisons de performances, il est recommandé de ne pas utiliser de clés étrangères sauf en cas de besoins particuliers. L'intégrité référentielle est contrôlée par le code. C'est également notre pratique courante pour contrôler l'intégrité du point de vue du programme, mais si vous n'y faites pas attention, des données sales seront également générées. 🎜🎜5. Les noms doivent être en minuscules. 🎜🎜🎜🎜Convention de dénomination d'utilisateur🎜🎜1. Le format de dénomination d'utilisateur utilisé en production est code_application🎜🎜2 La convention de dénomination d'utilisateur en lecture seule est read_application🎜

🎜🎜Spécification de conception d'objet de base de données

🎜🎜 🎜 Sélection du moteur de stockage🎜🎜1. S'il n'y a pas d'exigences particulières, le moteur de stockage innodb doit être utilisé. 🎜🎜Vous pouvez afficher le moteur par défaut actuel via show variables comme 'default_storage_engine'. Il existe principalement MyISAM et InnoDB A partir de la version 5.5, le moteur InnoDB est utilisé par défaut. Cliquez ici pour pratiquer des quiz. 🎜🎜La différence fondamentale est la suivante : le type MyISAM ne prend pas en charge le traitement avancé tel que le traitement des transactions, contrairement au type InnoDB. La table de type MyISAM met l'accent sur les performances et sa vitesse d'exécution est plus rapide que le type InnoDB, mais ne fournit pas de support pour les transactions, tandis que InnoDB fournit un support pour les transactions. et des fonctionnalités de base de données avancées telles que les clés étrangères. 🎜🎜🎜🎜Sélection du jeu de caractères🎜🎜1. S'il n'y a pas d'exigences particulières, utf8 ou utf8mb4 doit être utilisé. 🎜🎜En Chine, le meilleur moyen est de choisir le format utf8 qui prend parfaitement en charge le chinois et diverses langues. MySQL a ajouté l'encodage utf8mb4 après la version 5.5, mb4 <.> signifie <code>la plupart des octets 4, spécialement conçu pour être compatible avec le unicode à quatre octets. 🎜🎜Donc utf8mb4 est un sur-ensemble de utf8, et aucune autre conversion n'est requise sauf changer l'encodage en utf8mb4. Bien entendu, pour gagner de la place, il suffit généralement d'utiliser utf8. 🎜🎜Vous pouvez utiliser le script suivant pour afficher le format d'encodage de la base de données🎜rrreee🎜🎜🎜Spécifications de conception des tables🎜🎜1 La corrélation entre les tables de base de données correspondant à différentes applications doit être minimisée autant que possible et l'utilisation de données étrangères. les clés entre les tables ne sont pas autorisées. L'association est effectuée pour assurer l'indépendance entre les tables correspondant aux composants et offrir la possibilité de reconstruire le système ou la structure des tables. La pratique actuelle de l’industrie implique généralement un contrôle programmatique de l’intégrité référentielle. 🎜🎜2. Du point de vue de la conception des tables, la conception de la base de données ne doit pas être basée sur l'ensemble du système, mais doit être basée sur la division des composants dans l'architecture du système, et la conception de la base de données doit être basée sur les activités gérées par chacun. composant. 🎜🎜3. La table doit avoir un PK. Les avantages de la clé primaire sont une identification unique, une référence efficace et une récupération efficace, donc en général, essayez d'avoir un champ de clé primaire. 🎜🎜4. Un champ ne représente qu'une seule signification. 🎜🎜5. Le tableau ne doit pas avoir de colonnes en double. 🎜🎜6. Il est interdit d'utiliser des types de données complexes (tableau, personnalisé, etc.). L'utilisation du type Json dépend de la situation. 🎜🎜7. Les types de données des champs (clés de jointure) qui nécessitent join doivent être absolument cohérents pour éviter une conversion implicite. Par exemple, les champs associés sont tous de type int. 🎜🎜8. La conception doit au moins répondre au troisième paradigme et minimiser la redondance des données. Certains scénarios spéciaux permettent une conception de dénormalisation, mais la conception des champs redondants doit être expliquée lors de l'examen du projet. 🎜

9. Le champ TEXT est stocké sous forme d'une grande quantité de texte et doit être placé dans une table indépendante et associé à la table principale à l'aide de PK. Sans besoins particuliers, l'utilisation des champs TEXT et BLOB est interdite. TEXT字段作为大体量文本存储，必须放在独立的表中 , 用PK与主表关联。如无特殊需要，禁止使用TEXT、BLOB字段。

10、需要定期删除(或者转移)过期数据的表，通过分表解决，我们的做法是按照2/8法则将操作频率较低的历史数据迁移到历史表中，按照时间或者则曾Id做切割点。

11、单表字段数不要太多，建议最多不要大于50个。过度的宽表对性能也是很大的影响。

12、MySQL在处理大表时，性能就开始明显降低，所以建议单表物理大小限制在16GB，表中数据行数控制在2000W内。

业内的规则是超过2000W性能开始明显降低。但是这个值是灵活的，你可以根据实际情况进行测试来判断，比如阿里的标准就是500W，百度的确是2000W。实际上是否宽表，单行数据所占用的空间都有起到作用的。

13、如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略，后续会有专门的文章来分析数据拆分的做法：垂直拆分（垂直分库和垂直分表）、水平拆分（分库分表和库内分表）；

14、无特殊需求，严禁使用分区表

字段设计规范

1、INT：如无特殊需要，存放整型数字使用UNSIGNED INT型，整型字段后的数字代表显示长度。比如 id int(11) NOT NULL

2、DATETIME：所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型。

对于TIMESTAMP，它把写入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

另外DATETIME存储的范围也比较大：

timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。

datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。

但是特殊情况，对于跨时区的业务，TIMESTAMP更为合适。

3、VARCHAR：所有动态长度字符串 全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替；VARCHAR(N)，

N表示的是字符数而不是字节数。比如VARCHAR(255)，可以最大可存储255个字符（字符包括英文字母，汉字，特殊字符等）。但N应尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，且存储字符个数由所选字符集决定。

如UTF8存储一个字符最大要3个字节，那么varchar在存放占用3个字节长度的字符时不应超过21845个字符。同时，在进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。(如无特殊需要，原则上单个varchar型字段不允许超过255个字符)

4、TEXT：仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。

所有使用TEXT类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放，与大文本字段的隔离，目的是。如无特殊需要，不使用MEDIUMTEXT、TEXT、LONGTEXT类型

5、对于精确浮点型数据存储，需要使用DECIMAL，严禁使用FLOAT和DOUBLE。

6、如无特殊需要，尽量不使用BLOB类型

7、如无特殊需要，字段建议使用NOT NULL属性，可用默认值代替NULL

8、自增字段类型必须是整型且必须为UNSIGNED，推荐类型为INT或BIGINT，并且自增字段必须是主键或者主键的一部分。

索引设计规范

1、索引区分度

索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为:  selecttivity = count(distinct c_name)/count(*)

10. Les tables qui doivent supprimer (ou transférer) régulièrement des données expirées peuvent être résolues en fractionnant les tables. Notre approche consiste à migrer les données historiques à faible fréquence de fonctionnement vers les tables historiques selon la règle des 2/8, basée sur. temps ou Id Faire des points de coupe.

11. Le nombre de champs dans une seule table ne doit pas être trop grand, et il est recommandé de ne pas dépasser 50 au maximum. Des tables trop larges ont également un impact important sur les performances. 🎜🎜12. Lorsque MySQL traite de grandes tables, ses performances commencent à diminuer considérablement, il est donc recommandé de limiter la taille physique d'une seule table à 16 Go et de contrôler le nombre de lignes de données dans la table dans les 2 000 W. 🎜🎜La règle dans l'industrie est que les performances commencent à diminuer considérablement au-dessus de 2000W. Mais cette valeur est flexible et vous pouvez la tester en fonction de la situation réelle. Par exemple, la norme d'Alibaba est de 500 W, et celle de Baidu est bien de 2 000 W. En fait, le fait que le tableau soit large ou non et l'espace occupé par une seule ligne de données jouent tous un rôle. 🎜🎜13. Si la quantité de données ou la croissance des données est importante au début de la planification, alors la stratégie de fractionnement des tables doit être ajoutée lors de la revue de conception. Il y aura un article spécial plus tard pour analyser la pratique de fractionnement des données : fractionnement vertical (vertical). fractionnement) Base de données et partitionnement vertical), fractionnement horizontal (partition de base de données et partitionnement intra-base de données) 🎜🎜14 Sans exigences particulières, l'utilisation de tables de partition est strictement interdite🎜

Spécifications de conception de terrain

🎜1. INT : S'il n'y a pas de besoin particulier, utilisez le type UNSIGNED INT pour stocker des nombres entiers , après le champ entier Le nombre représente la longueur d'affichage. Par exemple, id int(11) NOT NULL🎜🎜2, DATETIME : tous les champs qui doivent être précis par rapport à l'heure (heures, minutes et secondes) utilisez DATETIME, n'utilisez pas le type TIMESTAMP. 🎜🎜Pour TIMESTAMP, il convertit l'heure écrite du fuseau horaire actuel en UTC (Temps universel coordonné) pour le stockage. Lors de l'interrogation, il est converti dans le fuseau horaire actuel du client et renvoyé. Pour DATETIME, aucune modification n'est apportée et l'entrée et la sortie sont fondamentalement les mêmes. 🎜🎜De plus, la plage de stockage de DATETIME est également relativement large : 🎜🎜La plage de temps que timestamp peut stocker est : '1970-01-01 00:00 : 01.000000' à' 2038-01-19 03:14:07.999999'. 🎜🎜La plage horaire que datetime peut stocker est : '1000-01-01 00:00:00.000000' à '9999-12-31 23:59:59.999999'. 🎜🎜Mais dans des cas particuliers, pour les entreprises sur plusieurs fuseaux horaires, TIMESTAMP est plus adapté. 🎜🎜3. VARCHAR : toutes les chaînes de longueur dynamique utilisent le type VARCHAR, similaire à des catégories limitées de champs tels que le statut, et utilisent également des caractères qui peuvent exprimer clairement la signification réelle. . Les chaînes ne doivent pas être remplacées par des nombres tels que INT ; VARCHAR(N), 🎜🎜N représente le nombre de caractères plutôt que le nombre d'octets. Par exemple, VARCHAR(255) peut stocker jusqu'à 255 caractères (les caractères incluent les lettres anglaises, les caractères chinois, les caractères spéciaux, etc.). Mais N doit être aussi petit que possible, car la longueur maximale de tous les champs VARCHAR dans une table MySQL est de 65 535 octets et le nombre de caractères stockés est déterminé par le jeu de caractères sélectionné. 🎜🎜Par exemple, UTF8 nécessite un maximum de 3 octets pour stocker un caractère, donc varchar ne doit pas dépasser 21 845 caractères lors du stockage de caractères occupant 3 octets. Dans le même temps, lors de l'exécution d'opérations de mémoire telles que le tri et la création de tables temporaires, la longueur N sera utilisée pour demander de la mémoire. (S'il n'y a pas de besoin particulier, en principe, un seul champ varchar ne peut pas dépasser 255 caractères)🎜🎜4 TEXT : Uniquement lorsque le nombre de caractères peut. dépasse 20 000, seul le type TEXT peut être utilisé pour stocker des données de caractères, car toutes les bases de données MySQL utilisent le jeu de caractères UTF8. 🎜🎜Tous les champs utilisant le type TEXT doivent être séparés de la table d'origine et combinés avec la clé primaire de la table d'origine pour former une autre table de stockage, afin de les isoler des grands champs de texte. S'il n'y a pas de besoin particulier, n'utilisez pas les types MEDIUMTEXT, TEXT, LONGTEXT🎜🎜5. devez utiliser DECIMAL, l'utilisation de FLOAT et DOUBLE est strictement interdite. 🎜🎜6. S'il n'y a pas de besoin particulier, essayez de ne pas utiliser le type BLOB 🎜🎜7 S'il n'y a pas de besoin particulier, il est recommandé d'utiliser le type NOT NULL pour le champ, et la valeur par défaut peut être utilisée à la place de <code>NULL🎜🎜8. Le type de champ à incrémentation automatique doit être un entier et doit être UNSIGNED. Le type recommandé est INT ou BIGINT , et le champ d'auto-incrémentation doit être la clé primaire ou une partie de la clé primaire. 🎜

Spécifications de conception de l'index

🎜1 Distinction de l'index 🎜🎜L'index doit être créé là où la sélectivité (discrimination) de l'index est élevée. Sur la colonne, la méthode de calcul de la sélectivité est : selecttivity = count(distinct c_name)/count(*); Si le résultat de la discrimination est inférieur à 0,2, il est déconseillé de créer un index sur cette colonne. , sinon ce sera grand La probabilité ralentira l'exécution de SQL🎜🎜2 Suivez le préfixe le plus à gauche🎜.

对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。

3、禁止使用外键，可以在程序级别来约束完整性

4、Text类型字段如果需要创建索引，必须使用前缀索引

5、单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。

6、ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引

7、正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

8、正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。

9、联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。

如：depno=1 and empname>'' and job=1</p> 如果建立(depno,empname,job)顺序的索引，job是用不到索引的。

10、应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

11、正确判断是否使用联合索引（上面联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。

12、避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

13、避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

14、模糊查询’%value%’会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%’是可以有效利用索引。

15、索引覆盖排序字段，这样可以减少排序步骤，提升查询效率

16、尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

举例子：比如一个品牌表，建立的的索引如下，一个主键索引，一个唯一索引

1 PRIMARY KEY (`id`),2 UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)

当你同事业务代码中的检索语句如下的时候，应该立即警告了，即没有覆盖索引，也没按照最左前缀原则：

1 select brand_id,brand_name from  ds_brand_system where status=?  and define_id=?  and app_id=?

建议改成如下：

1 select brand_id,brand_name from  ds_brand_system where app_id=? and define_id=?  and  status=?

约束设计规范

1、PK应该是有序并且无意义的，由开发人员自定义，尽可能简短，并且是自增序列。

2、表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uk_”作为前缀的唯一约束索引。

3、PK字段不允许更新。

4、禁止创建外键约束，外键约束由程序控制。

5、如无特殊需要，所有字段必须添加非空约束，即not null。

6、如无特殊需要，所有字段必须有默认值。

SQL使用规范

select 检索的规范性

1、尽量避免使用select *，join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。

一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。

2、严禁使用 select * from t_name ，而不加任何where条件，道理一样，这样会变成全表全字段扫描。

3、MySQL中的text类型字段存储：

3.1、不与其他普通字段存放在一起,因为读取效率低，也会影响其他轻量字段存取效率。

3.2、如果不需要text类型字段，又使用了select *，会让该执行消耗大量io，效率也很低下

4. Les fonctions associées peuvent être utilisées pour extraire des champs, mais now() , rand() , sysdate() doivent être évitées autant dans la mesure du possible Pour les fonctions dont les résultats sont incertains, il est strictement interdit d'utiliser toute fonction, y compris les fonctions de conversion de type de données, sur le champ de condition de filtre dans la condition Where. Un grand nombre de calculs et de conversions entraîneront une inefficacité, également décrite dans l'index. now() , rand() , sysdate() 等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数。大量的计算和转换会造成效率低下，这个在索引那边也描述过了。

5、分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序

6、用in()/union替换or，效率会好一些，并注意in的个数小于300

7、严禁使用%前缀进行模糊前缀查询:如：select a,b,c from t_name where a like ‘%name’; 可以使用%模糊后缀查询如：select a,b from t_name where a like ‘name%’;

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

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

子查询性能差的原因：

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

· 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；

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

操作的规范性

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

如：insert into values ('a','b','c');  应使用 insert into t_name(c1,c2,c3) values ('a','b','c'); 。

2、大批量写操作（UPDATE、DELETE、INSERT），需要分批多次进行操作

· 大批量操作可能会造成严重的主从延迟，特别是主从模式下，大批量操作可能会造成严重的主从延迟，因为需要slave从master的binlog中读取日志来进行数据同步。

· binlog日志为row

5. Toutes les instructions de requête de pagination doivent avoir des conditions de tri, sinon cela provoquera facilement du désordre 🎜🎜6 Utilisez in()/union pour remplacer ou, ce qui le fera. Augmentez l'efficacité Mieux, et veuillez noter que le nombre de in est inférieur à 300🎜🎜7. Il est strictement interdit d'utiliser le préfixe % pour une requête de préfixe floue : telle que : select a,b,c from t_name which a. like '%name' ; OK Utilisez une requête de suffixe flou telle que : sélectionnez a,b à partir de t_name où a like 'name%' ;🎜🎜8. optimiser les sous-requêtes dans joinOperation🎜🎜Habituellement, la sous-requête est dans la clause in et la sous-requête est du SQL simple (à l'exclusion de union, group by , order by, limit clause), la sous-requête peut être convertie en une requête associée pour l'optimisation. 🎜🎜Raisons des mauvaises performances de la sous-requête : 🎜🎜· L'ensemble de résultats de la sous-requête ne peut pas utiliser d'index. Habituellement, l'ensemble de résultats de la sous-requête sera stocké dans une table temporaire, qu'il s'agisse d'une table temporaire. de la mémoire ou un disque Les tables temporaires n'auront pas d'index, donc les performances des requêtes seront affectées dans une certaine mesure ; 🎜🎜· Surtout pour les sous-requêtes qui renvoient des ensembles de résultats plus grands, l'impact sur les performances des requêtes sera plus important ; 🎜🎜· Étant donné que la sous-requête générera un grand nombre de tables temporaires et aucun index, elle consommera trop de ressources CPU et IO et générera un grand nombre de requêtes lentes. 🎜

Normalisation des opérations

🎜1 Il est interdit d'utiliser des instructions INSERT sans listes de champs🎜. 🎜Par exemple : insérer dans les valeurs ('a', 'b', 'c') ; doit utiliser insérer dans les valeurs t_name(c1,c2,c3) (' a','b ','c');. 🎜🎜2. Les opérations d'écriture par lots volumineux (UPDATE, DELETE, INSERT) doivent être effectuées plusieurs fois par lots🎜🎜· Les opérations à grande échelle peuvent entraîner de sérieux retards maître-esclave, en particulier en mode maître-esclave. Les opérations à grande échelle peuvent entraîner de sérieux retards maître-esclave car esclave doit être asservi à master Lire le journal dans <code>binlog de pour la synchronisation des données. 🎜🎜· binlog Lorsque le journal est au format ligne, un grand nombre de journaux sera généré🎜

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!