Mysql数据库设计规范

想不到一个好的ID
43 阅读15分钟

1、规范背景与目的

MySQL数据库与 Oracle、SQL Server 等数据库相比,有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范,扬长避短。 本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。 在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范,从而为业务系统稳定、健康地运行提供保障。 本文档以下所有规范会按照【高危】、【 强制】、【建议】三个级别进行标注,遵守优先级从高到低。 对于不满足【高危】和【强制】两个级别的设计,DBA会强制打回要求修改。

2、数据库设计规范

2.1 配置规范

  1. 【强制】ENGINE=InnoDB。指定表存储引擎为默认的InnoDB。InnoDB适合99%的MySQL应用场景,为业界使用最多的MySQL存储引擎
  2. 【强制】字符集统一,MySQL数据库的库、表字符集使用utf8, 应用连接也是utf8字符集,表中有表情类数据应使用utf8mb4字符集。
  3. 【强制】MySQL数据库事务隔离级别默认为RC,可以减少锁,对于OLTP业务更适合。
  4. 【建议】不同业务使用不同db,合理规划业务设计,单个db不超过500张表。
  1. 【强制】建表时,表上必须有comment,表和字段统一使用中文注释;每个字段必须要有comment注明其业务含义,其中status/type类型的字段必须注释每个状态值的详细业务含义。
  2. 【强制】禁止使用存储过程、视图、触发器、Event,高并发大数据的互联网业务,并发量的情况下,这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性,能够轻易实现“增机器就加性能”。数据库擅长存储与索引,CPU计算还是上移吧

2.2 建表规范

  1. 【强制】表必须有主键,主键为自增id (id bigint UNSIGNED NOT NULLAUTO_INCREMENT COMMENT '自增主键ID'),禁止自增 ID主键涉及业务含义,顺序插入,避免页分裂和空洞
  2. 【强制】禁止使用外键,可以通过程序层面保证。
  3. 【强制】表中包含create_time/update_time 的创建时间及修改时间,便于问题查询,数据删除在审计日志中追溯。
  4. 【强制】字段命名禁止使用mysql关键字
  5. 【强制】单表字段数目少于30个
  6. 【强制】对于超过100W行的大表进行alter table,必须经过DBA审核,并在业务低峰期执行。因为alter table会产生表锁,期间阻塞对于该表的所有写入,对于业务可能会产生极大影响。
  7. 【强制】存储IP字段使用INT UNSIGNED,取不到用户ip使用default 0; insert 转换函数inet_aton('255.255.255.255'),select转换函数select inet_ntoa(4294967295);
  8. 【建议】尽可能不使用TEXT、BLOB类型,如果必须使用,建议将过大字段或是不常用的描述型较大字段拆分到其他表中,不为TEXT、BLOB建立索引。
  9. 【强制】禁止存储大文件或者大照片,在数据库中存储链接。
  10. 【建议】反范式设计:把经常需要join查询的字段,在其他表里冗余一份。如user_name属性在user_account, user_login_log等表里冗余一份,减少join查询。

2.3 命名规范

  1. 【强制】名、表名、字段名:小写,下划线风格,不超过32个字符,必须见名知意,禁止拼音英文混用
  2. 【强制】相关模块的表名与表名之间尽量体现join的关系,如t_user表和t_user_login表。
  3. 【强制】库的名称格式:业务系统名称_子系统名,同一模块使用的表名尽量使用统一前缀
  4. 【强制】一般分库名称命名格式是库通配名_编号,编号从0开始递增,比如wenda_001以时间进行分库的名称格式是“库通配名_时间”
  5. 【强制】表名t_xxx,非唯一索引名idx_xxx,唯一索引名uniq_xxx
  6. 【强制】中间表用于保留中间结果集,名称必须以tmp_开头。备份表用于备份或抓取源表快照,名称必须以bak_开头。中间表和备份表定期清理。

2.4字段设计规范

  1. 【建议】必须把字段定义为NOT NULL并且提供默认值,比如0,null的列使索引索引统计/值比较都更加复杂,对MySQL来说更难优化
  2. 【建议】禁止使用TEXT、BLOB类型。InnoDB表行记录物理长度不超过8KB,InnoDB的data page默认是16KB,一个data page中需要至少存储2条记录,实际存储长度超过8k时,会造成行迁移,影响系统性能。必须使用大列(尤其是TEXT/BLOB类型)且读写频繁的话,则最好把这些列拆分到子表中,不要和主表放在一起存储。
  3. 【强制】不建议使用ENUM类型,可使用TINYINT来代替。,enum类型order by性能差,ENUM的内部实际存储就是整数。
  4. 【建议】存储金钱,单位是元使用int,单位是分,存储精确浮点数必须使用DECIMAL替代FLOAT DOUBLE.
  5. 【强制】存储时间,使用datetime。timestamp最大支持到2038年。如过指定精度,mysql> create table t5 (id int primary key,ctime datetime(2) not null default current_timestamp(2));
  6. 【建议】存储手机号,使用varchar(20)。涉及到区号或者国家代号,可能出现+-()字符,varchar支持模糊匹配
  7. 【建议】存储IP地址,用INT UNSIGNED存储IPV4地址,用INET_ATON()、INET_NTOA()进行转换,基本上没必要使用CHAR(15)来存储。 SQL:select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long('192.168.2.12'); long2ip(3530427185);
  8. 【建议】文本数据尽量用varchar存储。varchar(N), 这里的N是指字符数。一般建议用varchar不超过500字符,最多不要超过2700字符。

2.5 索引设计

  1. 【强制】在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高。
  2. 【强制】单个表上的索引个数不能超过7个。单个索引字段不超过4个。
  3. 【强制】禁止在更新十分频繁、区分度不高的属性上建立索引
  4. 【强制】单个索引中每个索引记录的长度不能超过64KB。
  5. 【建议】超过20个长度的字符串列,最好创建前缀索引而非整列索引 idx_user(user(20))。默认限制768字节,字符数 = 768/编码(e.g. utf8mb4 768/4 = 192字符 varchar(192)) . 普通索引建立前缀索引即可
  6. 【建议】在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面。WHERE中过滤条件的字段顺序无需和索引一致,但如果有排序、分组则就必须一致了。
  7. 【建议】建表或加索引时,保证表里互相不存在冗余索引。对于MySQL来说,如果表里已经存在key(a,b),则key(a)为冗余索引,需要删除。
  8. 【建议】绝大多数情况下,排序的代价通常要来的更高,因此如果看到执行计划中有 Using filesort,优先创建排序索引吧。

2.6 分库分表、分区表

我们目前的业务体量与系统模型,除写入量大的日志类型表,比如:订单日志表、用户登录记录表、短信请求记录表之类按照时间顺序写入, 其他情景,强烈建议不要使用分区表(partition)、分库分表(sharding)

  1. 分区表弊端:我们目前db结构都是基于Master-Slave模式,各业务模块非常依赖数据同步的实时性,分区表在同步时,非常容易造成System lock,导致同步延迟问题;
  2. 分库分表弊端:分库分表设计需要对业务、现状技术风险、业务方向有深入理解,若设计考虑不周,不仅会造成数据库的灵活度严重降低,跟不上业务变动需求,且维护成本非常高昂。 如果情况及其特殊,请按如下规范使用分区表、分库分表
  3. 【强制】分区表的分区字段(partition-key)必须有索引,或者是组合索引的首列。
  4. 【强制】单个分区表中的分区(包括子分区)个数不能超过1024。
  5. 【强制】上线前RD或者DBA必须指定分区表的创建、清理策略。
  6. 【强制】访问分区表的SQL必须包含分区键。
  7. 【建议】单个分区文件不超过2G,总大小不超过50G。建议总分区数不超过20个。
  8. 【强制】对于分区表执行alter table操作,必须在业务低峰期执行。
  9. 【强制】采用分库策略的,库的数量不能超过1024
  10. 【强制】采用分表策略的,表的数量不能超过4096
  11. 【建议】单个分表不超过500W行,ibd文件大小不超过2G,这样才能让数据分布式变得性能更佳。
  12. 【建议】水平分表尽量用取模方式,日志、报表类数据建议采用日期进行分表。

2.7 程序层DAO设计建议

  1. 【建议】新的代码不要用model,推荐使用手动拼SQL+绑定变量传入参数的方式。因为model虽然可以使用面向对象的方式操作db,但是其使用不当很容易造成生成的SQL非常复杂,且model层自己做的强制类型转换性能较差,最终导致数据库性能下降。
  2. 【强制】代码中sql条件禁止使用动态传参方式提交。
  3. 【建议】前端程序连接MySQL或者redis,必须要有连接超时和失败重连机制,且失败重试必须有间隔时间。
  4. 【建议】前端程序报错里尽量能够提示MySQL或redis原生态的报错信息,便于排查错误。
  5. 【建议】对于有连接池的前端程序,必须根据业务需要配置初始、最小、最大连接数,超时时间以及连接回收机制,否则会耗尽数据库连接资源,造成线上事故。
  6. 【建议】对于log或history类型的表,随时间增长容易越来越大,因此上线前RD或者DBA必须建立表数据清理或归档方案。
  7. 【建议】在应用程序设计阶段,RD必须考虑并规避数据库中主从延迟对于业务的影响。尽量避免从库短时延迟(20秒以内)对业务造成影响,建议强制一致性的读开启事务走主库,或更新后过一段时间再去读从库。
  8. 【建议】多个并发业务逻辑访问同一块数据(innodb表)时,会在数据库端产生行锁甚至表锁导致并发下降,因此建议更新类SQL尽量基于主键去更新。
  9. 【建议】业务逻辑之间加锁顺序尽量保持一致,否则会导致死锁。
  10. 【建议】对于单表读写比大于10:1的数据行或单个列,可以将热点数据放在缓存里(如mecache或redis),加快访问速度,降低MySQL压力。

2.8 一个规范的建表语句示例

一个较为规范的建表语句为:

 CREATE TABLE user (
 `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
 `user_id` bigint NOT NULL DEFAULT 0 COMMENT '用户ID',
 `nickname` varchar(10) NOT NULL DEFAULT'' COMMENT "用户昵称',
 `realname` varchar(20) NOT NULL DEFAULT'' COMMENT '真实姓名',
 `email` varchar(30) NOT NULL DEFAULT'' COMMENT "用户邮箱',
 `avatar int NOT NULL DEFAULT 0 COMMENT '头像',
 `birthday` datetime not NULL default '0000-00-00 00:00:00' COMMENT '生日',
 `sextinyint not null DEFAULT 0 COMMENT '性别',
 `short_introduce` varchar(150) not null DEFAULT COMMENT ‘一句话介绍',
 `user_resume` varchar(300) not null DEFAULT ' ' COMMENT ‘用户提交的简历',
 `user_register_ip`int not null DEFAULT 0 COMMENT ‘用户注册时的源ip',
 `user_review_status`tinyint not null DEFAULT 0 COMMENT '用户资料审核状态',
 `create_time` DATETIME not null DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'，
 `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',

 PRIMARY KEY (`id`),
 UNIQUE KEY `uniq_user_id`(`user_id`),
 KEY `idx_username`(`nickname`),
 KEY `idx_user_ctime`(`user_id`, create_time`),
 KEY `idx_ctime`(`create_time`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户表‘；

3、SQL编写

3.1 SELECT语句

  1. 【高危】禁止大表使用子查询,一般改为join查询性能比较稳定。
  2. 【强制】SELECT语句必须指定具体字段名称,禁止写成*,必须携带按预计数据输出需求带上limit;select *会将不该读的数据也从MySQL里读出来,造成网卡压力。且表字段一旦更新,但model层没有来得及更新的话,系统会报错。
  3. 【强制】禁用procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源,降低数据库实例可扩展性。推荐都在程序端实现。
  4. 【强制】禁止隐式数据类型转换。SELECT uid FROM t_user WHERE phone=13800000000。造成全表扫描。
  5. 【强制】建议尽量实现读写分离,写入和事务发往主库,只读SQL发往从库。
  6. 【强制】生产环境禁止使用hint,如sql_no_cache, force index, ignore key,straight join等。如需使用,一定和DBA进行沟通确认。
  7. 【强制】禁止直接使用负向查询(NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等),以及%开头的模糊查询
  8. 【建议】in值列表限制在100以内。例如select... where userid in(....100个以内...),这么做是为了减少底层扫描,减轻数据库压力从而加速查询。
  9. 【建议】select in 查询的个数,建议在50个以内,性能稳定可靠。
  10. 【建议】 SELECT语句不要使用UNION,推荐使用UNION ALL,并且UNION子句个数限制在5个以内。因为union all不需要去重,节省数据库资源,提高性能。

3.2 DML语句

  1. 【强制】禁止联表更新语句,如update t1,t2 where t1.id=t2.id...。
  2. 【高危】禁用update|delete t1 where a=XX limit XX; 这种带limit的更新语句。因为会导致主从不一致,导致数据错乱。建议加上order by PK。
  3. 【强制】禁止使用INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性
  4. 【建议】insert/update 批量更新,不超过100个。这里XX的值不要超过100个。值过多虽然上线很快,但会引起主从同步延迟。
  5. 【建议】事务里批量更新数据需要控制数量,进行必要的sleep,做到少量多次。
  6. 【强制】除静态表或小表(100行以内),DML语句必须有where条件,且使用索引查找。查询数据量不要超过表行数的25%,否则不会利用索引。
  7. 【建议】索引列不要使用函数或表达式,否则无法利用索引。如where length(name)='Admin'或 where user_id+2=10023。
  8. 【建议】减少使用or语句,可将or语句优化为union,然后在各个where条件上建立索引。如where a=1 or b=2优化为where a=1... union ...where b=2, key(a),key(b)
  9. 【建议】分页查询,当limit起点较高时,可先用过滤条件进行过滤。如select a,b,c from t1 limit 10000,20;优化为: select a,b,c from t1 where id>10000 limit 20;。

3.3 多表连接

  1. 【强制】禁止跨db的join语句。因为这样可以减少模块间耦合,为数据库拆分奠定坚实基础。
  2. 【强制】禁止在业务的更新类SQL语句中使用join,比如update t1 join t2...。
  3. 【建议】不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用join来代替子查询。
  4. 【建议】线上环境,多表join不要超过3个表。
  5. 【建议】多表连接查询推荐使用别名,且SELECT列表中要用别名引用字段,数据库.表格式,如 select a from db1.table1 alias1 where ...
  6. 【建议】在多表join中,尽量选取结果集较小的表作为驱动表,来join其他表。

3.4 事务

  1. 【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在2000以内
  2. 【建议】批量操作数据时,需要控制事务处理间隔时间,进行必要的sleep,一般建议值5-10秒。
  3. 【建议】对于有auto_increment属性字段的表的插入操作,并发需要控制在200以内。
  4. 【强制】程序设计必须考虑“数据库事务隔离级别”带来的影响,包括脏读、不可重复读和幻读。
  5. 【建议】事务里包含SQL不超过5个(支付业务除外)。因为过长的事务会导致锁数据较久,MySQL内部缓存、连接消耗过多等雪崩问题。
  6. 【建议】事务里更新语句尽量基于主键或unique key,如update ... where id=XX; 否则会产生间隙锁,内部扩大锁定范围,导致系统性能下降,产生死锁。
  7. 【建议】尽量把一些典型外部调用移出事务,如调用webservice,访问文件存储等,从而避免事务过长。
  8. 【建议】对于MySQL主从延迟严格敏感的select语句,请开启事务强制访问主库。

3.5 排序和分组

  1. 【建议】减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的。
  2. 【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by b可以利用key(a,b)。
  3. 【建议】包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢。
avatar
文章
阅读
粉丝