阿里巴巴MySQL规范，五千字版，这次全了（建议收藏）
共 8374字，需浏览 17分钟
·
2024-07-27 20:58
程序员的成长之路
互联网/程序员/技术/资料共享
阅读本文大概需要 10 分钟。
来自：架构师之路
推荐一个程序员编程资料站：
http://cxyroad.com
tips：
2024年IDEA最新激活方法教程，后台回复：激活码
CSDN免登录复制代码插件下载：CSDN复制插件
以下是正文。
之前分享了阿里巴巴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. 基于性能考虑；
【强制】规范四：模糊搜索规范
1. 禁止左模糊或者全模糊查询；
2. 如果有相关业务需求，必须走搜索引擎方案解决；
画外音：
1. 基于性能考虑；
【推荐】规范五：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。
画外音：
索引覆盖详见《被查询的列，为啥要放到索引里？》explain详见《MySQL性能调优，必须掌握这一个工具！》《MySQL性能调优，Explain-extra》
【推荐】规范七：利用延迟关联或者子查询，可以优化分页场景
举例，先快速定位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. 避免执行分页语句提高性能；
【强制】规范六：外键规范
1. 禁止使用外键；
2. 外键约束问题必须在应用层解决；
3. 禁止使用级联查询；
画外音：
1. 针对于互联网大数据量高并发量的数据存储场景来说，外键与级联查询对性能的潜在影响较大；
2. 外键与级联查询存在更新风暴的风险；
级联查询是指，一个查询的结果依赖于另一个查询的结果，通常是通过子查询或者嵌套查询实现的。
更新风暴问题是指，由于某些约束，例如外键约束或者触发器约束，当一条记录被更新时，相关约束的记录也会被更新，引发一系列连锁反应，导致短时间大量更新操作引发数据库性能下降甚至死锁的问题。因此，分布式场景一般禁止使用外键约束，或者触发器约束。
【强制】规范七：存储过程规范
1. 禁止使用存储过程；
画外音：
1. 但针对于互联网大数据量高并发量的数据存储场景来说，存储过程对性能的潜在影响较大；
2. 难以调试；
3. 无可移植性；
【强制】规范八：别名规范
1. SQL中对于列的查询与修改，如果涉及多个表，必须使用表名（或者别名）对列进行限定；
画外音：
1. 如果不进行限定，未来对表DDL时，不同表可能出现同名列，使得原本正常的程序在DDL后突然异常；
【强制】规范九：线上操作规范
1. 线上数据库进行update/delete操作时，必须先同查询条件select执行，确认结果后再update/delete；
画外音：
1. 懂的都懂；
【推荐】规范十：表别名
1. 表的别名前加as，并以t1, t2, t3, ...依次命名；
【推荐】规范十一：in规范
1. 尽量避免in，实在避免不了，也建议将集合元素个数控制在1000个以内；
【参考】规范十二：字符编码
1. 因国际化需要，字符编码建议采用utf8mb4字符集；
【参考】规范十三：删除规范
truncate在功能上与不带where的delete相同，但速度更快，使用的系统资源与日志资源更少，但避免在代码中使用此语句。
画外音：truncate不触发触发器，MySQL删除数据时需要注意：
<END>
推荐阅读：
为什么 idea 建议去掉 StringBuilder，使用“+”拼接字符串
程序员在线工具站：cxytools.com
推荐一个我自己写的工具站：http://cxytools.com，专为程序员设计，包括时间日期、JSON处理、SQL格式化、随机字符串生成、UUID生成、随机数生成、文本Hash...等功能，提升开发效率。
⬇戳阅读原文直达！ 朕已阅
评论