IT评测·应用市场-qidao123.com

标题: 【推荐】MySQL数据库设计SQL规范 [打印本页]

作者: 笑看天下无敌手 时间: 2023-2-4 06:59
标题: 【推荐】MySQL数据库设计SQL规范
1 命名规范

1、【强制】库名、表名、字段名必须使用小写字母并采用下划线分割，禁止拼音英文混用；（禁用-，-相当于运算符）
2、【建议】库名、表名、字段名在满足业务需求的条件下使用最小长度；

如information --> info；address --> addr等

3、【强制】库名、表名、字段名禁止使用MySQL保留关键字，如from,table等

详见https://dev.mysql.com/doc/refman/5.7/en/keywords.html

4、【强制】临时库、临时表名必须以tmp为前缀并以日期为后缀，例如tmp_user_20201231;
5、【强制】备份库、备份表名必须以bak为前缀并以日期为后缀，例如bak_user_20201231;
6、【强制】非唯一索引命名idx_字段1_字段名2，唯一索引uniq_字段名1_字段名2；
2 基本规范

1、【强制】使用INNODB存储引擎

支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高；

2、【强制】使用UTF8或UTF8MB4字符集;

万国码，无需转码，无乱码风险，节省空间；

3、【强制】表、字段必须有comments（中文注释）;

中文注释信息必须保证完整、明确和准确；表和字段含义发生变更时，comments（中文注释）必须做同步修改；

4、【强制】不在数据库中存储图片、文件等大数据；

系统对数据库的读/写速度 < 系统对文件的直接处理速度

数据库对大数据字段的处理，效率不高

5、【强制】禁止在线上做数据库压力测试；
6、【强制】禁止使用存储过程、视图、触发器、Event;

跨库查询，视图等可以考虑用宽表查询

3 库表设计规范

1、【强制】表必须有主键，例如自增主键，使用int或bigint，具体看预估业务量；

主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用；

主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率；

无主键的表删除，在row模式的主从架构，会导致备库夯住；

2、【建议】单表字段数目建议不要过多，建议不要超过64

单表字段数太多会使得MySQL处理InnoDB返回数据之间的映射成本太高。

3、【强制】禁止使用外键，如果有外键完整性约束，需要应用程序控制

外键用来保护参照完整性，可在业务端实现，对父表和子表的操作会相互影响，降低可用性，甚至会造成死锁。

4.【建议】所有表要有如下系统字段，且按照如下顺序

Name	Code	DataType	Length	Not Null	Default
主键	id	Bigint或int		是	（表中的第一个字段）
……				是	其他业务字段
删除标识	is_delete	Tinyint	1	是	0（未删除）
创建时间	create_time	DateTime		是	记录创建时间
更新时间	update_time	DateTime		是	记录更新时间
创建人	create_user	Varchar（50）	50	是
更新人	update_user	Varchar（50）	50	是
时间戳	ts	timestamp		是	当前时间：数据库自动维护

4 索引设计规范

索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间
1、【建议】单张表中索引数量不超过5个（不包括主键）

索引不是越多越好，按实际需要进行创建，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能；

2、【建议】单个索引中的字段数不超过5个

对字符串使用前缀索引，前缀索引长度不超过10个字符；如果有一个CHAR(200)列，如果在前10个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10个字符进行索引能够节省大量索引空间，也可能会使查询更快；

3、【强制】创建复合索引时, 必须把区分度高的字段放在前面
4、【建议】不建议在更新十分频繁、区分度不高的属性上建立索引，特殊场景除外，例如只有0和1，1只占非常小的部分，只会去查询1的情况。
5、【强制】避免冗余或重复索引

合理创建联合索引（避免冗余），index(a、b、c)相当于index(a)、index(a、b)、index(a、b、c)；

5 字段设计规范

1、【建议】不建议使用TEXT、BLOB类型

会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能；如果实在有某个字段过长需要使用 TEXT、BLOB 类型，则建议独立出来一张表，用主键来对应，避免影响原表的查询效率。

2、【强制】用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；浮点数的缺点是会引起精度问题

3、【强制】字段必须定义合适的数据类型

只存储数字的字段定义成数字类型，只存储字符的字段定义成字符类型, 定长的字符定义成char ，尽可能用存储空间小的类型，只存储日期的字段定义成日期类型，以减少使用过程中的数据类型转换

4、【强制】禁止使用ENUM，可使用TINYINT代替

增加新的ENUM值要做DDL操作

5、【建议】字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量

VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N；

VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存；

6、【建议】如果可能的话所有字段均定义为not null且提供默认值

null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化;

null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多;

null值需要更多的存储空间，无论是表还是索引中每行中的null的列都需要额外的空间来标识;

<blockquote>对null 的处理时候，只能采用is null或is not null，而不能采用=、in、

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)