mysql 规范

要求主键为id，类型为int或bigint，且为auto_increment，主键选择非业务相关列。 
解读： 如果设为主键且主键值为随机插入，则会导致innodb内部page分裂和大量随机I/O，性能下降。 

 

【强制】表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。 
解读： 
1）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化； 
2）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多； 
3）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识； 
4）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录； 

【建议】核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题。 

【建议】对于DDL操作，必须经过DBA审核，并在业务低峰期执行。 

【建议】单实例表数目必须小于500。 

【建议】单表列数目必须小于30。 

 

 

 

 

MySQL数据库设计规范

目录

1. 规范背景与目的 
   2.1 数据库设计 
   2.2 库名 
   2.3 表结构 
   2.4 列数据类型优化 
   2.5 索引设计 
   2.6 字符集
2. SQL编写 
   3.1. DML语句 
   3.2 排序和分组 
   3.3 线上禁止使用的SQL语句
3. 一个规范的建表语句示例
   1.规范背景与目的 
   MySQL数据库与oracle、sqlserver等数据库相比，有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范，扬长避短。 
   本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。 
   在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范，从而为公司业务系统稳定、健康地运行提供保障。
   
   

2.1 数据库设计 
以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注，遵守优先级从高到低。 
对于不满足【高危】和【强制】两个级别的设计，DBA会强制打回要求修改。 
2.2. 库名 
1. 【强制】库的名称必须控制在32个字符以内，相关模块的表名与表名之间尽量提现join的关系禁止拼音英文混用，如user表和user_login表。 
2. 【强制】库的名称格式：业务系统名称_子系统名，同一模块使用的表名尽量使用统一前缀。 
3. 【强制】创建数据库时必须显式指定字符集，并且字符集utf8或utf8mb4 
创建数据库SQL举例： 
Create database db1 default character set utf8mb4; 
2.3 表结构 
1. 【强制】表和列的名称必须控制在32个字符以内，表名只能使用字母、数字和下划线，一律小写。 
2. 【强制】表名要求模块名强相关，如师资系统采用”sz”作为前缀，渠道系统采用”qd”作为前缀等。 
3. 【强制】创建表时必须显式指定字符集为utf8mb4 
4. 【强制】创建表时必须显式指定表存储引擎类型，如无特殊需求，一律为InnoDB。当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时，必须通过DBA审核才能在生产环境中使用。 
5. 【强制】建表必须有comment 
6. 【强制】要求主键为id，类型为int或bigint，且为auto_increment，主键选择非业务相关列。 
解读： 如果设为主键且主键值为随机插入，则会导致innodb内部page分裂和大量随机I/O，性能下降。 
7.【强制】禁止使用“tmp_”开头，“bak_”开头存在库里。 
9.【强制】表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。 
解读： 
1）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化； 
2）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多； 
3）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识； 
4）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录； 
8. 【建议】核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题。 
10.【建议】对于DDL操作，必须经过DBA审核，并在业务低峰期执行。 
11.【建议】单实例表数目必须小于500。 
12.【建议】单表列数目必须小于30。 
2.4 列数据类型优化 
1.【建议】.必须使用varchar(20)存储手机号； 
解读： 
1）涉及到区号或者国家代号，可能出现+-() 
2）手机号会去做数学运算么？ 
3）varchar可以支持模糊查询，例如：like“138%”； 
2.【建议】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间。 
3.【建议】业务中IP地址字段推荐使用无符号int类型，不推荐用char(15) 
解读： 
int只占4字节，可以用如下函数相互转换，而char(15)占用至少15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空间！ 
SQL：select inet_aton(‘192.168.2.12’); select inet_ntoa(3232236044); 
Php: ip2long(‘192.168.2.12’); long2ip(3530427185); 
4.【禁止】使用enum，set，推荐tinyint 
解读： 
1） 因为它们浪费空间，且枚举值写死了，变更不方便。 
2）增加新的ENUM值要做DDL操作； 
3）ENUM的内部实际存储就是整数，实际上并不是自定义的字符串； 
5.【禁止】使用blob，text等类型 
解读： 
它们都比较浪费硬盘和内存空间。在加载表数据时，会读取大字段到内存里从而浪费内存空间，影响系统性能。建议和PM、RD沟通，是否真的需要这么大字段？ 
Innodb中当一行记录超过8098字节时，会将该记录中选取最长的一个字段将其768字节放在原始page里，该字段余下内容放在overflow-page里。不幸的是在compact行格式下，原始page和overflow-page都会加载。 
6.【建议】存储金钱的字段，建议用int，程序端乘以100和除以100进行存取。 
解读： 
因为int占用4字节，而double占用8字节，空间浪费。 
7.【建议】文本数据尽量用varchar存储 
解读： 
1）varchar是变长存储，比char更省空间。MySQL server层规定一行所有文本最多存65535字节，因此在utf8字符集下最多存21844个字符，超过会自动转换为mediumtext字段。而text在utf8字符集下最多存21844个字符，mediumtext 最多存2^24/3个字符，longtext最多存2^32个字符 
2）一般建议用varchar类型，字符数不要超过2700 
8.【建议】时间类型尽量选取datetime 
解读： 
5.6.4之后datetime 支持更新当前时间，大小从8字节变为了5字节，和timestamp性能相差无几，datetime的范围更大，无时区问题。 
2.5 索引设计 
1. 【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新。 
2. 【建议】主键的名称以“pk_”开头，唯一键以“uk_”或“uq_”开头，普通索引以“idx_”开头，一律使用小写格式，以表名/字段的名称或缩写作为后缀。 
3. 【建议】单个表上的索引个数不能超过7个 
4. 【禁止】在更新十分频繁、区分度不高的属性上建立索引 
解读： 
1）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能 
2）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似 
5. 【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。 
6. 【建议】建表或加索引时，保证表里互相不存在冗余索引 
解读： 
对于MySQL来说，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。 
2.6 字符集

1. 【强制】数据库本身库、表、列所有字符集必须保持一致，为utf8或utf8mb4。
2. 【强制】前端程序字符集或者环境变量中的字符集，与数据库、表的字符集必须一致，统一为utf8 
   3 SQL编写 
   3.1 DML语句 
   1.【强制】SELECT语句必须指定具体字段名称，禁止写成“*” 
   解读： 
   1）读取不需要的列会增加CPU、IO、NET消耗 
   2）不能有效的利用覆盖索引 
   3）使用SELECT *容易在增加或者删除字段后出现程序BUG 
   2.【强制】insert语句指定具体字段名称，不要写成insert into t1 values(…)，道理同上。 
   解读: 
   容易在增加或者删除字段后出现程序BUG； 
   3.【禁止】使用属性隐式转换； 
   解读： 
   举例：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次）建立组合索引，必须把区分度高的字段放在前面； 
   4.【禁止】在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’) 
   5.【禁止】负向查询，以及%开头的模糊查询； 
   解读： 
   1）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描； 
   2）%开头的模糊查询，会导致全表扫描； 
   6.【强制】DML语句必须有where条件。 
   7.【强制】WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引。 
   8.【建议】减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引。如where a=1 or b=2优化为where a=1… union …where b=2, key(a),key(b)

3.2 排序和分组 
1.【建议】减少使用order by，能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。 
2.【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by可以利用key(a,b)。 
3.【建议】包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。 
4.【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。 
3.3 线上禁止使用的SQL语句 
1.【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。 
解读： 
因为会导致主从不一致，导致数据错乱。建议加上order by PK 
2.【高危】禁止使用关联子查询，如update t1 set … where name in(select name from user where…);效率极其低下并且事后不可查。 
3.【强制】禁用procedure、function、trigger、views、event、外键约束。 
解读： 
因为他们消耗数据库资源，降低数据库集群可扩展性。推荐都在程序端实现。 
4.【强制】禁止联表更新语句，如update t1,t2 where t1.id=t2.id…

1. 一个规范的建表语句示例 
   一个较为规范的建表语句为： 
   CREATE TABLE user ( 
   id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键’, 
   user_id bigint(11) NOT NULL DEFAULT ‘0’ COMMENT ‘用户id’, 
   username varchar(45) NOT NULL DEFAULT ” COMMENT ‘真实姓名’, 
   email varchar(30) NOT NULL DEFAULT ” COMMENT ‘用户邮箱’, 
   nickname varchar(45) NOT NULL DEFAULT ” COMMENT ‘昵称’, 
   avatar int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘头像’, 
   birthday date NOT NULL DEFAULT ‘1901-01-01’ COMMENT ‘生日’, 
   short_introduce varchar(150) NOT NULL DEFAULT ” COMMENT ‘一句话介绍，最多50个汉字’, 
   user_resume varchar(300) NOT NULL DEFAULT ” COMMENT ‘用户提交的简历存放地址’, 
   user_register_ip int NOT NULL DEFAULT ‘0’ COMMENT ‘用户注册时的源ip’, 
   create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’, 
   update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’ , 
   user_review_status tinyint(4) NOT NULL DEFAULT ‘4’ COMMENT ‘用户资料审核状态，1通过，2审核中，3未通过，4还未提交审核’, 
   PRIMARY KEY (id), 
   UNIQUE KEY idx_user_id (user_id), 
   KEY idx_username(username), 
   KEY idx_create_time(create_time,user_review_status) 
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’网站用户基本信息’;

每个表必须有字段，且字段定义必须和上表一致： 
create_time 
update_time