mysql数据库设计规范

在使用MySQL数据库的时候需要遵循一定规范，扬长避短。本规范旨在帮助或指导开发、测试、数据库操作人员等做出适合线上业务的数据库设计和规范化操作。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范。

基础规范

【强制】数据库表结构字符集统一使用UTF8mb4。
【强制】新建表，必须显式指定主键。
【强制】不在数据库中存储图片、大文件等大数据。
【强制】所有表名、表字段必须添加注释。
【强制】无特殊需求，必须统一使用INNODB存储引擎。

命名规则

【强制】数据库名、表名、字段名必须统一使用小写字母，采用26个英文字母和0-9的自然数组合，命名不能只含数字，加上下划线“_”组成，不建议加数字。
【强制】特殊情况下，需要数字与字母组合一起命名时，名称必须用英文字母开头，采用可体现业务模块含义的单词或缩写，单词中间用“_”分割，单词只能由英文字母、数字组成，不能只含数字。正例: service_level2
【强制】禁止使用大小写字母混合方式命名数据库名、表名、字段名。
【强制】数据库名、表名、字段名禁止使用拼音或夹带拼音。
【强制】数据库名、表名、字段名禁止使用MySQL保留字、关键字。
【强制】数据库名、表名、字段名的命名，英文单词组合个数不超过5个，字符数不超过32个字符，须见名知意，允许使用英文缩写。
【强制】数据库名，必须能体现业务方向或业务系统，其名称含义能达到见名知意效果。正例: vehicle_center（库名）。
【强制】数据库表名，必须能体现业务方向或业务系统，最好是加上“业务名称_表的作用”，其名称含义简单易懂，能达到见名知意效果。正例: user_login（表名）
【强制】表字段命名应能体现被设计对象的属性含义或作用，并且需要有完整的注释说明；
【强制】数据库索引命名标准,pk主键、uk_xx唯一键、idx_xx普通索引(xx代表字段名)；idx_xx_xx复合索引(xx代表字段名);
【强制】数据库视图命名标准,v_xx(xx代表业务主体内容)；

表结构设计规范

【强制】新建表时必须显式指定主键，类型为int或bigint，且为auto_increment。
【强制】创建表时必须显式指定字符集为utf8mb4。
【强制】创建表时必须显式指定表存储引擎类型，如无特殊需求，一律使用InnoDB。
【强制】新建表时，表里每个字段必须要有comment注释。
【强制】对已有表做表字段追加，所追加字段必须有注释说明。
【强制】表设计过程中，单表不应有过多热点字段。
【建议】核心表（如用户表，金钱相关的表）须有行数据的创建时间字段create_time和最后更新时间字段update_time。
【建议】表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。
【建议】不建议使用text/blob，如在满足长度的情况下，建议用varchar代替text，如无法避免text、blob，应独立出来一张表。
【强制】禁止存储图片。
【强制】禁止使用外键。

字段类型选择与优化

【强制】表中的自增列auto_increment属性，推荐使用int、bigint类型。
【强制】涉及金额、费率等字段,应统一使用decimal。
【强制】业务字段中存在状态status判断的、类型type等字段推荐使用tinytint。
【建议】业务中IP地址字段推荐使用int类型。
【建议】文本数据尽量用varchar存储,字符数不要超过2000。
【建议】时间类型尽量选取timestamp。
【强制】涉及地理位置信息,应统一使用geoetry。

详细存储大小参加下图：
baidu

索引规范与设计

【强制】InnoDB存储引擎表，索引类型必须为BTREE。
【强制】主键的名称以“pk_”开头，唯一键以“uk_”或“uq_”开头，普通索引以“idx_”开头，idx_xx(普通索引),idx_xx_xx(复合索引),xx代表后缀，以字段的名称或缩写作为后缀, 命名一律使用小写格式。
【强制】索引建立的规则表应遵循列数据随机率要高于%70以上，count(distinct (column))/count(*)值越接近于1，越适合建索引，索引越高效。
【建议】在建立索引时，应多考虑建立联合索引，并把区分度最高的字段放在最前面。
【建议】在多表join的SQL里，保证被驱动表的连接列上有索引，这样join执行效率最高。
【强制】业务表含有唯一约束列，必须为其创建唯一键或者复合唯一键。
【建议】不要在更新频繁的列上建索引。
【建议】建表或加索引时，保证表里互相不存在重复、冗余索引。
【强制】单表不宜建过多索引，索引个数不能超过7个。

分区表

【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列。
【强制】单个分区表中的分区（包括子分区）个数不能超过1024。
【强制】访问分区表的SQL必须包含分区键。
【强制】对于分区表执行alter table操作，必须在业务低峰期执行。

字符集

【强制】数据库本身库、表、列所有字符集必须保持一致，为utf8mb4
【强制】前端程序字符集或者环境变量中的字符集，与数据库、表的字符集必须一致，统一为utf8或utf8mb4。

规范建表语句示例

CREATE TABLE USER (
	`id` BIGINT ( 11 ) NOT NULL AUTO_INCREMENT,
	`user_id` BIGINT ( 11 ) NOT NULL COMMENT '用户 id',
	`username` VARCHAR ( 45 ) NOT NULL COMMENT '真实姓名',
	`email` VARCHAR ( 30 ) NOT NULL COMMENT '用户邮箱',
	`nickname` VARCHAR ( 45 ) NOT NULL COMMENT '昵称',
	`avatar` INT ( 11 ) NOT NULL COMMENT '头像',
	`birthday` date NOT NULL COMMENT '生日',
	`sex` TINYINT ( 4 ) DEFAULT '0' COMMENT '性别',
	`short_introduce` VARCHAR ( 150 ) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
	`user_resume` VARCHAR ( 300 ) NOT NULL COMMENT '用户提交的简历存放地址',
	`user_register_ip` INT NOT NULL COMMENT '用户注册时的源 ip',
	`create_time` TIMESTAMP NOT NULL COMMENT '用户记录创建的时间',
	`update_time` TIMESTAMP NOT NULL COMMENT '用户资料修改的时间',
	`user_review_status` TINYINT NOT NULL 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 = utf8mb4 COMMENT = '网站用户基本信息';

SQL使用规范

查询语句

【强制】SELECT语句必须指定具体字段名称，禁止写成“*”。
【强制】SELECT语句表名前必须加上库名。正例：select vin,status,manufacture_time from vcd.tm_vehicle; vcd为库名
【强制】SELECT语句查询数据输出行数比较多的情况下，需要加limit 限制输出的行数。
【建议】SELECT语句尽量避免嵌套多个语句。
【强制】禁止在生产数据库做复杂运算。
【建议】SELECT语句避免在索引列上使用函数或计算。
【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。
【建议】in值列表限制在500以内。正例：select… where userid in(….500个以内…)，这么做是为了减少底层扫描，减轻数据库压力从而加速查询。
【强制】where条件里等号左右字段类型必须一致，否则无法利用索引。
【建议】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的条件必需使用索引查找。
【强制】WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引。
【建议】索引列不要使用函数或表达式，否则无法利用索引。正例：where length(name)=’Admin’或where user_id+2=10023。
【建议】减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引。如where a=1 or b=2优化为where a=1… union …where b=2, key(a),key(b)
【建议】分页查询，当limit起点较高时，可先用过滤条件进行过滤。正例：select a,b,c from t1 limit 10000,20; 优化为:Select a,b,c from t1 where id>10000 limit 20;

多表连接

【建议】不建议跨db的join语句查询。
【强制】禁止在业务的更新类SQL语句中使用join，比如update t1 join t2…
【建议】不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询。
【建议】线上环境，多表join不要超过5个表。
【建议】多表连接查询推荐使用别名，且SELECT列表中要用别名引用字段，数据库.表格式，如“select a from db1.table1 alias1 where …”
【建议】在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表。

排序和分组

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