之前分享了阿里巴巴MySQL【强制】规范,大伙反馈不过瘾,希望补充【推荐】规范与【参考】规范。好滴,满足大家的心愿。
画外音:补充了很多信息,文章较长,建议收藏。
规范内容取自“阿里巴巴MySQL规范”(黄山版),发布时间为2022.2.3,开源。
第一部分:建表规范
【强制】规范一:是否字段
1. 表达是否概念的字段,必须使用is_xxx的形式命名;
2. 数据类型必须是 unsigned tinyint;
3. 1表示是,0表示否;
举例:是否删除
正确:使用is_deleted,1表示删除,0表示未删除
错误:deleted, if_deleted, delete_or_not
【强制】规范二:字母与数字
1. 表名,字段名禁止出现大写;
画外音:MySQL在Windows下不区分大小写,Linux下虽然区分,但为了避免节外生枝,统一禁止大写。
2. 禁止数字开头,禁止两个下划线中间只有数字;
举例:
正确:aliyun_admin,level3_name
错误:AliyunAdmin,level_3_name
【强制】规范三:表名禁止使用复数
表名表示实体内容,不是实体数量,禁止使用复数。
【强制】规范四:禁止使用保留字
常见的例如:desc,range,match,delayed…
【强制】规范五:主键,唯一索引,普通索引命名规范
1. 主键索引名:pk_xxx
画外音:primary key
2. 唯一索引名:uk_xxx
画外音:unique key
3. 普通索引名:idx_xxx
画外音:index
【强制】规范六:小数类型规范
1. 小数类型使用decimal;
2. 禁止使用float和double;
画外音:float和double存在精度损失,比较的时候,可能得到意想不到的结果。
3. 如果范围超过decimal,可以拆成整数与小数分开存储;
【强制】规范七:字符串长度非常相近,必须使用定长char
画外音:预先分配存储空间,不会触发重新分配。
【强制】规范八:可变字符串规范
1. 如果字符串长度较长,且内容长度差异较大,使用varchar;
画外音:不预先分配存储空间,比较节省空间。
2. 如果字符串长度大部分超过5000,使用text,独立出一张表单独存储;
画外音:避免影响主表其他字段索引效率。
【强制】规范九:强制字段
1. 必须具备id字段:类型为bigint unsigned,单表时自增,步长为1,不具备业务含义;
2. 必须具备create_time字段:类型为datetime(除非记录时区信息,使用timestamp);
3. 必须具备update_time字段:同上;
【强制】规范十:禁止进行物理删除操作
画外音:逻辑操作保留了数据资产的同时,能够追溯操作行为。
【推荐】规范十一:表名建议
1. 表名建议遵循“业务名称_表的作用”;
举例:alipay_task
【推荐】规范十二:库名建议
1. 库名建议与应用名称一致;
【推荐】规范十三:字段修改
1. 如果修改字段含义,或者追加字段状态,建议同步更新注释;
【推荐】规范十四:数据冗余
允许通过数据冗余来提高查询性能,但要考虑数据一致性,冗余的字段建议遵循:
1. 非频繁修改;
2. 非唯一索引;
3. 非vahcar超长字段;
4. 非text字段;
【推荐】规范十五:分库分表
以下情况建议分库分表:
1. 单表数据超过500W行;
2. 单表容量超过2GB;
画外音:预计3年内达不到1或2,不建议分库分表。
【参考】规范十六:使用恰当的数据类型
1. 无负数可使用无符号类型,还能扩大表示范围;
2. 以下是一些典型业务场景的类型参考:
画外音:选择合适的类型,能节约表空间,节约索引空间,提升检索速度。
第二部分:索引规范
【强制】规范一:唯一索引规范
1. 业务上具备唯一特性的字段,即使是组合字段,也必须建立成唯一索引。
画外音:
1. 唯一索引虽然影响插入速度,但针对于互联网大数据量高并发量的数据存储场景来说,插入的影响可以忽略不计,查询效率的提升是主要矛盾;
2. 应用层的唯一检查是不够的;
【强制】规范二:join规范
1. 超过三个表时,禁止join;
2. 需要join的字段,数据类型必须绝对一致;
3. 被关联的字段必须要有索引;
画外音:
1. 针对于互联网大数据量高并发量的数据存储场景来说,join对性能的潜在影响较大;
2. 数据类型不对,没有索引,对性能的潜在影响较大;
【强制】规范三:varchar规范
1. 没有必要对过长的varchar全字段建立索引;
2. varchar字段上的索引必须指定索引长度;
3. 索引长度可参考文本区分度,索引长度N可用count(distinct left(column, N))/count(*)来测试;
画外音:基于性能考虑;
【强制】规范四:模糊搜索规范
1. 禁止左模糊或者全模糊查询;
2. 如果有相关业务需求,必须走搜索引擎方案解决;
画外音:基于性能考虑;
【推荐】规范五:order by规范
1. order by场景要注意组合索引的顺序,order by的字段应该放在组合索引的最后;
举例:
where a=? and b=? order by c
可以使用a_b_c索引
但是要注意:
where a>? order by b
无法使用a_b索引
【推荐】规范六:利用索引覆盖来进行查询,可以避免回表
说明:
索引分为主键索引、唯一索引、普通索引三种,覆盖索引只是一种查询效果,explain时,extra会出现using index。
【推荐】规范七:利用延迟关联或者子查询,可以优化分页场景
举例,先快速定位id,再关联:
select t1.* from biao1 as t1,
(select id from biao1 where XXX limit 100000, 20) as t2
where t1.id = t2.id
画外音:MySQL并不跳过offset行,而是先取offset+N行,然后放弃前面offset行,再返回N行。如果offset特别大,效率就非常低。常见的优化手段,是通过id对SQL进行改写。
【推荐】规范八:大表的性能需要优化
SQL优化目标为:至少达到range级别,要求达到ref级别,如果是const级别那最好。
画外音补充说明:explain结果中的type字段代表什么意思?
MySQL的官网解释非常简洁,只用了3个单词:连接类型(the join type)。它描述了找到所需数据使用的扫描方式。
最为常见的扫描方式有:
(1)system:系统表,少量数据,往往不需要进行磁盘IO;
(2)const:常量连接;
(3)eq_ref:主键索引(primary key)或者非空唯一索引(unique not null)等值扫描;
(4)ref:非主键非唯一索引等值扫描;
(5)range:范围扫描;
(6)index:索引树扫描;
(7)ALL:全表扫描(full table scan);
这些是最常见的,大家去explain自己工作中的SQL语句,95%都是上面这些类型。explain详见《MySQL性能调优,必须掌握这一个工具!》。
【推荐】规范九:组合索引
1. 建立组合索引时,区分度高的列放在左边;
2. 混合条件时,等号条件的列放在左边;
举例:
where a=? and b=?
a列区分度高,建立a_b索引
如果a接近唯一,可以只建立a索引
举例:
whare a>? and b=?
应该建立b_a组合索引
【推荐】规范十:避免字段类型不同产生隐式转换,导致索引失效
【参考】规范十一:避免极端
1. 认为一个查询就需要一个索引;
2. 认为索引占Buffer Pool就不建立索引;
3. 认为可以通过应用层“先查询再插入”的方式变相实现唯一索引;
第三部分:SQL规范
【强制】规范一:count规范
1. 不要使用count(column)或者count(1),请使用count(*)
画外音:
1. count(*)是SQL92标准定义的统计行的语法,与数据库无关,与值无关;
2. count(*)会统计值为NULL的行,count(column)不会;
【强制】规范二:count规范
1. 如果要计算排除NULL值的不重复行计数,请使用count(distinct column);
画外音,请避坑:使用count(distinct column1, column2)时,如果一列全为NULL,另一列即使有不同值,也会返回0;
【强制】规范三:NULL规范
1. 如果一列全是NULL,sum(column)返回的是NULL,因此在使用sum时,应用程序务必考虑NPE问题;
画外音:NPE,NullPointerException
【强制】规范四:NULL规范
1. 使用ISNULL(column)判断列是否为空,不要使用column is null 或者column is not null;
画外音:
1. NULL与任何值比较都是NULL;
2. column is (not) null可能导致换行,影响可读性,而ISNULL(column)是一个整体;
3. ISNULL(column)的执行效率更高;
【强制】规范五:分页规范
1. 应用层分页查询逻辑,必须加上count为0时直接返回的判断;
画外音:避免执行分页语句提高性能;
【强制】规范六:外键规范
1. 禁止使用外键;
2. 外键约束问题必须在应用层解决;
3. 禁止使用级联查询;
画外音:
1. 针对于互联网大数据量高并发量的数据存储场景来说,外键与级联查询对性能的潜在影响较大;
2. 外键与级联查询存在更新风暴的风险;
级联查询是指,一个查询的结果依赖于另一个查询的结果,通常是通过子查询或者嵌套查询实现的。
更新风暴问题是指,由于某些约束,例如外键约束或者触发器约束,当一条记录被更新时,相关约束的记录也会被更新,引发一系列连锁反应,导致短时间大量更新操作引发数据库性能下降甚至死锁的问题。因此,分布式场景一般禁止使用外键约束,或者触发器约束。
【强制】规范七:存储过程规范
1. 禁止使用存储过程;
画外音:
1. 但针对于互联网大数据量高并发量的数据存储场景来说,存储过程对性能的潜在影响较大;
2. 难以调试;
3. 无可移植性;
【强制】规范八:别名规范
1. SQL中对于列的查询与修改,如果涉及多个表,必须使用表名(或者别名)对列进行限定;
画外音:如果不进行限定,未来对表DDL时,不同表可能出现同名列,使得原本正常的程序在DDL后突然异常;
【强制】规范九:线上操作规范
1. 线上数据库进行update/delete操作时,必须先同查询条件select执行,确认结果后再update/delete;
画外音:懂的都懂
【推荐】规范十:表别名
1. 表的别名前加as,并以t1, t2, t3, …依次命名;
【推荐】规范十一:in规范
1. 尽量避免in,实在避免不了,也建议将集合元素个数控制在1000个以内;
【参考】规范十二:字符编码
1. 因国际化需要,字符编码建议采用utf8mb4字符集;
【参考】规范十三:删除规范
truncate在功能上与不带where的delete相同,但速度更快,使用的系统资源与日志资源更少,但避免在代码中使用此语句。
画外音:truncate不触发触发器,MySQL删除数据时需要注意:
《MySQL删除数据的三种方式!(有超级大坑)》
规范背后的原理,比规范本身,更有价值。
希望大家有收获。
来源丨公众号:架构师之路(ID:road5858)
dbaplus社群欢迎广大技术人员投稿,投稿邮箱:[email protected]
活动推荐
为了和大家一起探索AI相关技术在大数据、数据资产管理、数据库、运维等领域的最佳落地方式,挖掘由此激发的软件发展和技术进步,第九届DAMS中国数据智能管理峰会将于2024年11月29日在上海举办,携手一众产学研界技术领跑单位,带来新思路、重实践、可落地的全日干货盛宴。
活动详情:
https://www.bagevent.com/event/8805002?bag_track=WAZ
责任编辑: