MYSQL数据库规范

 

一、命名规范：

规则：
1、数据库名、表名、字段名、索引名必须全部小写，禁止使用mysql保留关键字，并用下划线分割。表名使用单数形式。

2、所有存储相同数据的列名和列类型必须一致。

说明：一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低。

3、非唯一索引按照“idx_字段名称[_字段名称]”进行命名。例如idx_age_name。

4、唯一索引按照“uq/uniq_字段名称[_字段名称]”进行命名。例如uniq_age_name。

5、组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add。

二、索引设计

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

规则：

1、禁止在查询条件左侧出现表达式或函数。

2、单张表中索引数量不超过5个。适当使用多列索引。选择合适的索引列顺序。

说明：多列索引可以减少索引数目；合适的索引列顺序，避免不正确的索引造成性能低下的查询。

3、单个索引中的字段数不超过5个。

4、禁止使用外键。

5、联表查询时，JOIN列的数据类型必须相同,并且要建立索引。

6、不在低基数列上建立索引,例如“性别”。

7、选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。

8、如有可能，尽量使用覆盖索引。

说明：覆盖索引减少IO,避免排序；

覆盖索引是select的数据列只用从索引中就能获取，即查询列被所建的索引覆盖。

9、避免建立冗余索引和重复索引

说明：因为这样会增加查询优化器生成执行计划的时间。

重复索引示例：primary key(id)、index(id)、unique index(id)

冗余索引示例：index(a,b,c)、index(a,b)、index(a)

10、超过1000条数据的表查询，必须按索引查询 explain 对语句进行分析确保rows小于200

 

建议：
1、常见索引列建议

1）出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列；
2）包含在ORDER BY、GROUP BY、DISTINCT中的字段，并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好；
3）多表join的关联列；

2、选择索引列建议

1）区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）；
2）尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）；
3）使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

三、数据库设计

规则：

1、所有表必须使用Innodb存储引擎

没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好

2、数据库和表的字符集统一使用UTF8（特殊可以使用utf8mb4）

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效

3、不使用memory做数据缓存，使用redis。

说明：memory表所有数据都是存储在内存上的，服务器重启会影响数据的完整性。

四、表设计

规则：

1、表必须有主键，可以是单个字段或联合主键。

2、将大字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据，减小表的宽度

说明：更新频率较高的字段和更新频率较低的字段分离可加快更新效率；

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节，但最好控制在8098字节以内，实在表数据较宽必须控制在16196字节以下。新增字段超过16196字节的必须垂直分出新表。减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）

3、日期存储选择，TIMESTAMP（4个字节）或DATETIME类型（8个字节），不选varchar类型

说明：TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07；

DATETIME存储的时间范围 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59；

日期存储效率和时间效率

timestamp＞datetime＞varchar

用varchar存储日期，无法用日期函数进行计算和比较。用字符串存储日期要占用更多的空间。

4、尽量避免使用blog json text类型。

说明：如果存储的二进制文件过大，会增加数据库磁盘的使用，并会降低数据库的性能，可以考虑nosql结构化存储或使用文件服务器，如一定需要可以将大字段、访问率低的字段拆分到单独表，分离冷热数据。另5.7版本后MySQL的json请使用原生json，减少text保存json带来的程序侧解析错误问题

5、禁止使用null

说明：支持null的列比不支持null的列占用更多空间，索引的效率也会较低，可以设置默认值''或0

6、单表字段不要超过30个

当单表字段数量过多的时候，加载大量数据也会拖慢查询性能。

如果字段超过30个，不用看，肯定是表设计的不合理。

这时候，可以拆成多张表，用垂直分表的方式，进行冷热字段分离。

7、使用unsigned存储非负数值和自增主键

说明：无符号比非unsigned存储范围大一倍；

自增主键不需要负数应使用unsigned整数。

8、同财务相关的金额类数据必须使用decimal类型

说明：

非精准浮点：float,double

精准浮点：decimal

Decimal类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。

五、SQL规范及数据库行为规范

规则：

1、数据表超过1000万时分表处理，数据库容量超过150G时进行分库处理

2、高并发的互联网应用禁止在MySQL中使用外键

说明：可以使用事务达到主从表完整的目的、触发器（触发器产生问题时一般问题隐藏较深也影响问题排查）、定时任务（需要定时请在程序侧实现）、减少存储过程使用（简化逻辑）、禁止使用视图

3、禁止使用*号查询，避免查询不必要的字段

4、切分大的查询（或更新、删除）

说明：大的sql查询可以分成多个查询，由应用发起多次请求，多次可以提高mysql缓存命中率，也可以更方便的使用应用层缓存。同时避免大查询产生的慢查询。比如更新或删除一次10万行数据，可以考虑分批次，每次更新或删除1000行数据。

5、限制关联表的最大数量为3（关联key需要有索引）

说明：关联数量过多会导致扫描行数大幅度增加，不适合互联网应用高并发场景。

6、使用in代替or，in包含的值应少于1000。

7、sql中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。

说明：不确定函数会影响mysql主从数据传输。

8.禁止隐式转换，数值类型禁止加引号，字符串类型必须加引号。

说明：隐式转换通常隐藏较深bug。

 

【版权声明】本文为原创内容，转载时必须标注文章的来源（月牙弯弯博客）、文章链接、文章作者等基本信息， 否则作者有权追究责任。如果您发现本内容有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本作者将立刻删除涉嫌侵权内容，举报邮箱：540961290@qq.com