用惨痛教训换来的156条MySQL设计规约
怎么才能很好地避免低级故障?以下规范在大型互联网公司经过了充分验证,尤其适用于并发量大、数据量大的业务场景。
在设计数据库技术方案时,我们是有自己的设计理念或者原则,还是更多依据直觉去设计?是否曾经懊悔线上发生过的一次低级故障?是否思考过怎样才能避免?设计规范的价值在于提供了一份工作检查清单,我们不断从错误中积累有效经验,指导未来的工作。
以下规范在大型互联网公司经过了充分的验证,尤其适用于并发量大、数据量大的业务场景。安全无小事,很多公司都曾经因为数据泄露导致用户损失惨痛,所以将安全规范放到了第一位。
一、安全规范
1.【强制】禁止在数据库中存储明文密码,需把密码加密后存储。
说明:对于加密操作建议由公司的中间件团队基于如mybatis的扩展,提供统一的加密算法及密钥管理,避免每个业务线单独开发一套,同时也与具体的业务进行了解耦。
2.【强制】禁止在数据库中明文存储用户敏感信息,如手机号等。
说明:对于手机号建议公司搭建统一的手机号查询服务,避免在每个业务线单独存储。
3.【强制】禁止开发直接给业务同学导出或者查询涉及到用户敏感信息的数据,如需要需上级领导审批。
4.【强制】涉及到导出数据功能的操作,如包含敏感字段都需加密或脱敏。
5.【强制】跟数据库交互涉及的敏感数据操作都需有审计日志,必要时要做告警。
6.【强制】对连接数据库的IP需设置白名单功能,杜绝非法IP接入。
7.【强制】对重要sql(如订单信息的查询)的访问频率或次数要做历史趋势监控,及时发现异常行为。
8.【推荐】线上连接数据库的用户名、密码建议定期进行更换。
二、基础规范
1.【推荐】尽量不在数据库做运算,复杂运算需移到业务应用里完成。
2.【推荐】拒绝大sql语句、拒绝大事务、拒绝大批量,可转化到业务端完成。
说明:大批量操作可能会造成严重的主从延迟,binlog日志为row格式会产生大量的日志。
3.【推荐】避免使用存储过程、触发器、函数等,容易造成业务逻辑与DB耦合。
说明:数据库擅长存储与索引、要解放数据库CPU,将计算转移到服务层、也具备更好的扩展性。
4.【强制】数据表、数据字段必须加入中文注释。
说明:后续维护的同学看到后才清楚表是干什么用的。
5.【强制】不在数据库中存储图片、文件等大数据。
说明:大文件和图片需要储在文件系统。
6.【推荐】对于程序连接数据库账号,遵循权限最小原则。
7.【推荐】数据库设计时,需要问下自己是否对以后的扩展性进行了考虑。
8.【推荐】利用 pt-query-digest 定期分析slow query log并进行优化。
9.【推荐】使用内网域名而不是ip连接数据库。
10.【推荐】如果数据量或数据增长在前期规划时就较大,那么在设计评审时就应加入分表策略。
11.【推荐】要求所有研发SQL关键字全部是小写,每个词只允许有一个空格
三、命名规范
1.【强制】库名、表名、字段名要小写,下划线风格,不超过32个字符,必须见名知意,建议使用名词而不是动词,词义与业务、产品线等相关联,禁止拼音英文混用。
2.【强制】普通索引命名格式:idx_表名_索引字段名(如果以首个字段名为索引有多个,可以加上第二个字段名,太长可以考虑缩写),唯一索引命名格式:uk_表名_索引字段名(索引名必须全部小写,长度太长可以利用缩写),主键索引命名:pk_ 字段名。
3.【强制】库名、表名、字段名禁止使用MySQL保留字。
4.【强制】临时库表名必须以tmp为前缀,并以日期为后缀。
5.【强制】备份库表必须以bak为前缀,并以日期为后缀。
6.【推荐】用HASH进行散表,表名后缀使用16进制数,下标从0开始。
7.【推荐】按日期时间分表需符合YYYY格式。
8.【推荐】散表如果使用md5(或者类似的hash算法)进行散表,表名后缀使用16进制,比如user_ff。
9.【推荐】使用CRC32求余(或者类似的算术算法)进行散表,表名后缀使用数字,数字必须从0开始并等宽,比如散100张表,后缀从00-99。
10.【推荐】使用时间散表,表名后缀必须使用特定格式,比如按日散表user_20110209、按月散表user_201102。
11.【强制】表达是与否概念的字段,使用 is _ xxx 的方式进行命名。
四、库设计规范
1.【推荐】数据库使用InnoDB存储引擎。
说明:支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。
2.【推荐】数据库和表的字符集统一使用UTF8。
说明:utf8号称万国码,其无需转码、无乱码风险且节省空间。若是有字段需要存储emoji表情之类的,则将表或字段设置成utf8mb4,utf8mb4向下兼容utf8。
3.【推荐】不同业务,使用不同的数据库,避免互相影响。
4.【强制】所有线上业务库均必须搭建MHA高可用架构,避免单点问题。
五、表设计规范
1.【推荐】建表规范示例:
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
CREATE TABLE `student_info` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `stu_name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名', `stu_score` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '总分', `stu_num` int(11) NOT NULL COMMENT '学号', `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `status` tinyint(4) DEFAULT '1' COMMENT '1代表记录有效,0代表记录无效', PRIMARY KEY (`id`), UNIQUE KEY `uk_student_info_stu_num` (`stu_num`) USING BTREE, KEY `idx_student_info_stu_name` (`stu_name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';
2.【强制】禁止使用外键,如果有外键完整性约束,需要应用程序控制。
3.【强制】每个Innodb 表必须有一个主键。
说明:Innodb 是一种索引组织表,其数据存储的逻辑顺序和索引的顺序是相同的。每张表可以有多个索引,但表的存储顺序只能有一种,Innodb 是按照主键索引的顺序来组织表的,因此不要使用更新频繁的列如UUID、MD5、HASH和字符串列作为主键,这些列无法保证数据的顺序增长,主键建议使用自增ID 值。
4.【推荐】单表列数目最好小于50。
5.【强制】禁止使用分区表。
说明:分区表在物理上表现为多个文件,在逻辑上表现为一个表,谨慎选择分区键,跨分区查询效率可能更低,建议采用物理分表的方式管理大数据。
6.【推荐】拆分大字段和访问频率低的字段,分离冷热数据。
7.【推荐】采用合适的分库分表策略,例如千库十表、十库百表等(建议表大小控制在2G)。
8.【推荐】单表不超过50个int字段;不超过20个char字段,不超过2个text字段。
9.【推荐】表默认设置创建时间戳和更改时间戳字段。
10.【推荐】日志类型的表可以考虑按创建时间水平切割,定期归档历史数据。
11.【强制】禁止使用order by rand()。
说明:order by rand()会为表增加一个伪列,然后用rand()函数为每一行数据计算出rand()值,基于该行排序,这通常都会生成磁盘上的临时表,因此效率非常低。
12.【参考】可以结合使用hash、range、lookup table进行散表。
13.【推荐】每张表数据量建议控制在500w以下,超过500w可以使用历史数据归档或分库分表来实现(500万行并不是MySQL数据库的限制。过大对于修改表结构,备份,恢复都会有很大问题。MySQL没有对存储有限制,取决于存储设置和文件系统)。
14.【强制】禁止在表中建立预留字段。
说明:预留字段的命名很难做到见名识义,预留字段无法确认存储的数据类型,所以无法选择合适的类型;对预留字段类型的修改,会对表进行锁定。
六、字段设计规范
1.【强制】必须把字段定义为NOT NULL并且提供默认值。
说明:NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效。
2.【强制】禁止使用ENUM,可使用TINYINT代替。
3.【强制】禁止使用TEXT、BLOB类型(如果表的记录数在万级以下可以考虑)。
4.【强制】必须使用varchar(20)存储手机号。
5.【强制】禁止使用小数存储国币、使用“分”作为单位,这样数据库里就是整数了。
6.【强制】用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。
7.【推荐】使用UNSIGNED存储非负整数。
说明:同样的字节数,存储的数值范围更大。
8.【推荐】建议使用INT UNSIGNED存储IPV4。
说明:用UNSINGED INT存储IP地址占用4字节,CHAR(15)则占用15字节。另外,计算机处理整数类型比字符串类型快。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址,通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数,需要使用DECIMAL或两个BIGINT来存储。例如:
[*]
SELECT INET_ATON('192.168.172.3'); 3232279555 SELECT INET_NTOA(3232279555); 192.168.172.3
9.【推荐】字段长度尽量按实际需要进行分配,不要随意分配一个很大的容量。
10.【推荐】核心表字段数量尽可能地少,有大字段要考虑拆分。
11.【推荐】适当考虑一些反范式的表设计,增加冗余字段,减少JOIN。
12.【推荐】资金字段考虑统一*100处理成整型,避免使用decimal浮点类型存储。
13.【推荐】使用VARBINARY存储大小写敏感的变长字符串或二进制内容。
说明:VARBINARY默认区分大小写,没有字符集概念,速度快。
14.【参考】INT类型固定占用4字节存储。
说明:INT(4)仅代表显示字符宽度为4位,不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系,比如INT(3)默认显示3位,空格补齐,超出时正常显示,Python、Java客户端等不具备这个功能。
15.【参考】区分使用DATETIME和TIMESTAMP。
说明:存储年使用YEAR类型、存储日期使用DATE类型、存储时间(精确到秒)建议使用TIMESTAMP类型。
DATETIME和TIMESTAMP都是精确到秒,优先选择TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节,同时TIMESTAMP具有自动赋值以及⾃自动更新的特性。
补充:如何使用TIMESTAMP的自动赋值属性?
自动初始化,而且自动更新:column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP 只是自动初始化:column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 自动更新,初始化的值为0:column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 初始化的值为0:column1 TIMESTAMP DEFAULT 0
16.【推荐】将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。
说明:有利于有效利用缓存,防⽌读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提⾼高缓存命中率。
17.【参考】VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N。
18.【参考】VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存。
19.【推荐】VARCHAR(N),N>5000时,使用BLOB类型。
20.【推荐】使用短数据类型,比如取值范围为0~80时,使用TINYINT UNSIGNED。
21.【强制】存储状态,性别等,用TINYINT。
22.【强制】所有存储相同数据的列名和列类型必须一致(在多个表中的字段如user_id,它们类型必须一致)。
23.【推荐】优先选择符合存储需要的最小数据类型。
24.【推荐】如果存储的字符串长度几乎相等,使用 char 定长字符串类型。
七、索引设计规范
1.【推荐】单表索引建议控制在5个以内。
说明:索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率,所以不是越多越好。
2.【强制】禁止在更新十分频繁,区分度不高的属性上建立索引。
3.【强制】建立组合索引必须把区分度高的字段放在前面。
4.【推荐】对字符串使用索引,如果字符串定义长度超过128的,可以考虑前缀索引。
5.【强制】表必须有主键,并且是auto_increment及not null的,根据表的实际情况定义无符号的tinyint,smallint,int,bigint。
6.【强制】禁止更新频繁的列作为主键。
7.【强制】禁止字符串列作为主键。
8.【强制】禁止UUID MD5 HASH这些作为主键(数值太离散了)。
9.【推荐】默认使用非空的唯一键作为主键。
10.【推荐】主键建议选择自增或发号器。
11.【推荐】核心SQL优先考虑覆盖索引。
12.【参考】避免冗余和重复索引。
13.【参考】索引要综合评估数据密度和分布以及考虑查询和更新比例。
14.【强制】不在索引列进行数学运算和函数运算。
15.【推荐】研发要经常使用explain,如果发现索引选择性差,必须要学会使用hint。
16.【推荐】能使用唯一索引就要使用唯一索引,提高查询效率。
17.【推荐】多条字段重复的语句,要修改语句条件字段的顺序,为其建立一条联合索引,减少索引数量。
18.【强制】索引字段要保证不为NULL,考虑default value进去。NULL也是占空间,而且NULL非常影响索引的查询效率。
19.【强制】新建的唯一索引不能和主键重复。
20.【推荐】尽量不使用外键、外键用来保护参照完整性,可在业务端实现。
说明:避免对父表和子表的操作会相互影响,降低可用性。
21.【强制】字符串不应做主键。
22.【强制】表必须有无符号int型自增主键,对应表中id字段。
说明:必须得有主键的原因:采用RBR模式复制,无主键的表删除,会导致备库夯住 ;使用自增的原因:数据写入可以提高插入性能,避免page分裂,减少表碎片。
23.【推荐】对长度过长的VARCHAR字段建立索引时,添加crc32或者MD5 Hash字段,对Hash字段建立索引。
说明:下面的表增加一列url_crc32,然后对url_crc32建立索引,减少索引字段的长度,提高效率。
[*]
CREATE TABLE url( ... url VARCHAR(255) NOT NULL DEFAULT 0, url_crc32 INT UNSIGNED NOT NULL DEFAULT 0, ... index idx_url(url_crc32) )
24.【推荐】WHERE条件中的非等值条件(IN、BETWEEN、=)会导致后面的条件使用不了索引。
25.【推荐】索引字段的顺序需要考虑字段值去重之后的个数,个数多的放在前面。
26.【推荐】ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面。
27.【参考】合理创建联合索引(避免冗余),如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
28.【推荐】复合索引中的字段数建议不超过5个。
29.【强制】不在选择性低的列上建立索引,例如"性别", "状态", "类型"。
30.【推荐】对于单独条件如果走不了索引,可以使用force –index强制指定索引。
31.【强制】禁止给表中的每一列都建立单独的索引。
32.【推荐】在varchar字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。
八、SQL使用规范
1.【强制】禁止使用SELECT *,只获取必要的字段,需要显示说明列属性。
说明:按需获取可以减少网络带宽消耗,能有效利用覆盖索引,表结构变更对程序基本无影响。
2.【强制】禁止使用INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性。
3.【强制】WHERE条件中必须使用合适的类型,避免MySQL进行隐式类型转化。
说明:因为MySQL进行隐式类型转化之后,可能会将索引字段类型转化成=号右边值的类型,导致使用不到索引,原因和避免在索引字段中使用函数是类似的,例子 select uid from t_user where phone=15855550101(phone为 varchat 类型,此时查询中使用数字查询,会导致索引失效)。
4.【强制】禁止在WHERE条件的属性上使用函数或者表达式。
5.【强制】禁止负向查询,以及%开头的模糊查询。
6.【强制】应用程序必须捕获SQL异常,并有相应处理。
7.【推荐】sql语句尽可能简单、大的sql想办法拆成小的sql语句。
说明:简单的SQL容易使用到MySQL的querycache、减少锁表时间特别是MyISAM、可以使用多核cpu。
8.【推荐】事务要简单,整个事务的时间长度不要太长。
9.【强制】避免在数据库中进行数学运算或者函数运算(MySQL不擅长数学运算和逻辑判断,也容易将业务逻辑和DB耦合在一起)。
10.【推荐】sql中使用到OR的改写为用IN() (or的效率没有in的效率高)。
11.【参考】SQL语句中IN包含的值不应过多,里面数字的个数建议控制在1000个以内。
12.【推荐】limit分页注意效率。Limit越大,效率越低。可以改写limit。
说明:改写例子:
1)改写方法一
延迟回表写法 select xx,xx from t t1, (select id from t where .... limit 10000,10) t2 where t1.id = t2.id
2)改写方法二
select id from t limit 10000, 10; 应该改为 => select id from t where id > 10000 limit 10;
13.【推荐】尽量使用union all替代union。
14.【参考】避免使用大表JOIN。
15.【推荐】对数据的更新要打散后批量更新,不要一次更新太多数据。
16.【推荐】使用合理的SQL语句减少与数据库的交互次数。
17.【参考】注意使用性能分析工具 Sql explain / showprofile / mysqlsla。
18.【推荐】能不用NOT IN就不用NOT IN,坑太多了,会把空和NULL给查出来。
19.【推荐】关于分页查询,程序里建议合理使用分页来提高效率,limit、offset较大要配合子查询使用。
20.【强制】禁止在数据库中跑大查询。
21.【强制】禁止单条SQL语句同时更新多个表。
22.【推荐】统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1)。
说明:count( * ) 会统计值为 NULL 的行,而 count( 列名 ) 不会统计此列为 NULL 值的行。
23.【推荐】INSERT语句使用batch提交(INSERT INTO tableVALUES(),(),()……),values的个数不应过多。
24.【推荐】获取大量数据时,建议分批次获取数据,每次获取数据少于2000条,结果集应小于1M。
25.【推荐】在做开发时建议使用数据库框架(如mybatis)或prepared statement,可以提升性能并避免SQL注入。
26.【强制】禁止跨库查询(为数据迁移和分库分表留出余地,降低耦合度,降低风险)。
27.【推荐】尽量避免使用子查询,可以把子查询优化为join操作(子查询的结果集无法使用索引,子查询会产生临时表操作,如果子查询数据量大会影响效率,消耗过多的CPU及IO资源)。
28.【强制】超过三个表禁止 join(需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能)。
29.【推荐】SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好。
30.【推荐】尽量不要使用物理删除(即直接删除,如果要删除的话提前做好备份),而是使用逻辑删除,使用字段delete_flag做逻辑删除,类型为tinyint,0表示未删除,1表示已删除。
31.【强制】在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句。
32.【强制】程序连接不同的数据库要使用不同的账号。
33.【推荐】使用 ISNULL()来判断是否为 NULL 值。
九、行为规范
1.【强制】禁止使用应用程序配置文件内的帐号手工访问线上数据库。
2.【强制】禁止非DBA对线上数据库进行写操作,修改线上数据需要提交工单,由DBA执行,提交的SQL语句必须经过测试。
3.【强制】禁止在线上做数据库压力测试。
4.【强制】禁止从测试、开发环境直连线上数据库。
5.【强制】禁止在主库进行后台统计操作,避免影响业务,可以在离线从库上执行后台统计。
十、流程规范
1.【强制】所有的建表操作需要提前告知该表涉及的查询sql。
2.【强制】所有的建表需要确定建立哪些索引后才可以建表上线。
3.【强制】所有的改表结构、加索引操作都需要将涉及到所改表的查询sql发出来告知DBA等相关人员。
4.【强制】在建新表加字段之前,要求至少要提前3天邮件出来,给dba们评估、优化和审核的时间。
5.【强制】批量导入、导出数据需要DBA进行审查,并在执行过程中观察服务。
6.【强制】禁止有super权限的应用程序账号存在。
7.【强制】推广活动或上线新功能必须提前通知DBA进行流量评估。
8.【强制】不在业务高峰期批量更新、查询数据库。
9.【强制】隔离线上线下环境(开发测试程序禁止访问线上数据库)。
10.【强制】在对大表做表结构变更时,如修改字段属性会造成锁表,并会造成从库延迟,从而影响线上业务,必须在凌晨后业务低峰期执行,另统一用工具pt-online-schema-change避免锁表且降低延迟执行时间。
11.【强制】核心业务数据库变更需在凌晨执行。
12.【推荐】汇总库开启Audit审计日志功能,出现问题时方可追溯。
13.【强制】给业务方开权限时,密码要用MD5加密,至少16位。权限如没有特殊要求,均为select查询权限,并做库表级限制。
14.【推荐】如果出现业务部门人为误操作导致数据丢失,需要恢复数据,请在第一时间通知DBA,并提供准确时间,误操作语句等重要线索。
15.【强制】批量更新数据,如update,delete 操作,需要DBA进行审查,并在执行过程中观察服务。
16.【强制】业务部门程序出现bug等影响数据库服务的问题,请及时通知DBA便于维护服务稳定。
17.【强制】线上数据库的变更操作必须提供对应的回滚方案。
18.【强制】批量清洗数据,需要开发和DBA共同进行审查,应避开业务高峰期时段执行,并在执行过程中观察服务状态。
19.【强制】数据订正如删除和修改记录时,要先 select ,确认无误才能执行更新语句,避免出现误删除。
作者丨修冶
来源:https://www.cnblogs.com/88223100/p/156-MySQL-design-protocols-exchanged-for-painful-lessons.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!
页:
[1]