MySQL数据库规范（详细）

MySQL数据库规范

一、基础规范

【强制】使用InnoDB存储引擎

解读：InnoDB存储引擎是MySQL默认存储引擎，支持事务和行级锁，并发性能更好，CPU及内存缓存页优化使得资源利用率更高

【强制】使用utf8字符集，如果有字段需要存储emoji表情之类的，则需要将字段或表设置成utf8mb4

解读：万国码，无需转码，无乱码风险，节省空间，utf8mb4向下兼容utf8

【强制】数据表、数据字段必须加入中文注释

解读：便于识别表和字段的用途

【强制】禁止使用存储过程、视图、触发器、Event

解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧

【强制】禁止存储大文件或者大照片

解读：大文件和照片存储在文件系统，数据库里存URI多好

二、命名规范

【强制】库名、表名、字段名：小写字母，下划线风格，禁止数字开头，禁止两个下划线中间只出现数字，禁止复数名词。

解读：正例：getter_admin，task_config，level3_name 反例：GetterAdmin，taskConfig，level_3_name

【强制】命名中不允许出现MYSQL数据库中的保留字。如desc、range、match、delayed等，请参考MySQL官方保留字。

【强制】索引命名格式为：索引类型_字段名。普通索引名idx_xxx，唯一索引名uniq_xxx

三、表设计规范

【强制】单实例表数目必须小于500，单表列数目必须小于30

【建议】无特殊情况下，建议显式指定一个无业务用途的自增unsigned bigint型主键

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

【建议】表必须有create_time和update_time两个字段，并指定datetime类型

解读：

建表时建议按照以下格式设计：

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

加字段时建议按照以下格式设计：

ADD COLUMN `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

ADD COLUMN `update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP;

timestamp的使用有以下的限制，所以建议统一使用datetime类型

a) timestamp支持的时间范围小，范围是从’1970-01-01 00:00:01′ 到 ’2038-01-19 03:14:07′ UTC;

b) timestamp的插入和查询受当地时区的影响;

c) timestamp的属性会受到MySQL版本和SQL mode的影响。

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

解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先

【建议】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。

解读：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

四、字段设计规范

【强制】把字段定义为NOT NULL并且提供默认值

解读：

以下只是建议的默认值，可根据业务需要设置成其它默认值

tinyint/int/bigint 类型默认值：0

char/varchar 类型默认值：' '

date 类型默认值：'0000-00-00'

time 类型默认值：'00:00:00'

datetime 类型默认值：'0000-00-00 00:00:00'

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

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

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

d)对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

【建议】禁止使用TEXT、BLOB类型，可以做垂直拆分到子表中

解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

【强制】存储货币用decimal或整数类型，禁止使用float和double

解读：float和double在存储的时候，存在精度损失的问题

【建议】小数类型用decimal，禁止使用float和double

解读：float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。

【建议】枚举类型禁止使用ENUM，可使用TINYINT代替

解读：

a)增加新的ENUM值要做DDL操作

b)ENUM的内部实际存储就是整数

【建议】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint( 1表示是，0表示否)

【建议】使用varchar(20)存储手机号

解读：

a)涉及到区号或者国家代号，可能出现+-()

b)手机号不会做数学运算

c)varchar可以支持模糊查询，例如：like“138%”

【建议】如果存储的字符串长度几乎相等，使用char定长字符串类型。

【建议】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000。

【建议】字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。

解读：冗余字段应遵循：不是频繁修改的字段，不是varchar超长字段，更不能是text字段。正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。

【建议】选择合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

五、索引设计规范

【建议】单表索引建议控制在5个以内

解读：索引过多会增加存储开销和增删改的开销

【建议】禁止在更新十分频繁、区分度不高的属性上建立索引

解读：

a)更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能

b)“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似

【建议】尽量使用组合索引，建立组合索引时必须把区分度高的字段放在前面，字段数不允许超过5个

解读：能够更加有效的过滤数据，索引上字段超过5个时，实际已经起不到有效过滤数据的作用了

【建议】在排序、分组、取唯一字段上创建索引，经常与其他表进行关联的表，在关联字段上应该建立索引，经常出现在Where子句中的字段，特别是大表的字段，应该建立索引

【建议】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

解读：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的;另外，即使在应用层做了非常完善的校验和控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

【建议】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

解读：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

六、SQL使用规范

【强制】禁用select *，必须指定列

解读：

a)读取不需要的列会增加CPU、IO、NET消耗

b)不能有效的利用覆盖索引

c)使用SELECT *容易在增加或者删除字段后出现程序BUG

【强制】insert语句需指定列，INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

解读：避免在增加或者删除字段后出现程序BUG

【强制】禁止使用属性隐式转换

解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，因为phone是varchar类型，需要添加引号

【建议】禁止在WHERE条件的属性上使用函数或者表达式

解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描

正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

【强制】禁止负向查询，以及%开头的模糊查询

解读：

a)负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描

b)%开头的模糊查询，会导致全表扫描

【建议】禁止大表使用JOIN查询，禁止大表使用子查询

解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能

【建议】禁止使用OR条件，必须改为IN查询

解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢?

【强制】应用程序必须捕获SQL异常，并有相应处理

【建议】超过三个表禁止join。需要join的字段，数据类型保持绝对一致;多表关联查询时，保证被关联的字段需要有索引。

解读：即使双表join也要注意表索引、SQL性能。

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

解读：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

【建议】使用order by请注意利用索引的有序性。

解读：order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

正例：where a=? and b=? order by c; 索引：a_b_c

反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。

【建议】利用覆盖索引来进行查询操作，来避免回表操作。

解读：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗?目录浏览一下就好，这个目录就是起到覆盖索引的作用。 正例：能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的一种效果，用explain的结果，extra列会出现：using index。

【建议】利用延迟关联或者子查询优化超多分页场景。

解读：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。 正例：先快速定位需要获取的id段，然后再关联： SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

【建议】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。

解读：

1)consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。 2)ref 指的是使用普通的索引(normal index)。

3)range 对索引进行范围检索。

反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。