建议收藏:阿里巴巴祖传完整版MySQL规范

指尖上的架构 2024-09-14 04:32:31

之前分享了阿里巴巴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社群欢迎广大技术人员投稿,投稿邮箱:editor@dbaplus.cn

活动推荐

为了和大家一起探索AI相关技术在大数据、数据资产管理、数据库、运维等领域的最佳落地方式,挖掘由此激发的软件发展和技术进步,第九届DAMS中国数据智能管理峰会将于2024年11月29日在上海举办,携手一众产学研界技术领跑单位,带来新思路、重实践、可落地的全日干货盛宴。

活动详情:https://www.bagevent.com/event/8805002?bag_track=WAZ

0 阅读:0

指尖上的架构

简介:感谢大家的关注