【阿里规约】阿里开发手册解读——数据库和ORM篇

 导航：

【Java笔记+踩坑汇总】Java基础+JavaWeb+SSM+SpringBoot+SpringCloud+瑞吉外卖/黑马旅游/谷粒商城/学成在线+设计模式+面试题汇总+性能调优/架构设计+源码-CSDN博客

阿里规约PDF：

阿里巴巴开发手册.pdf - 蓝奏云

目录

一、建表规约

1.1 库

1.2 表

1.3 字段

1.3.1 基础命名规范

1.3.2 基本规范 

1.3.3 布尔型字段

1.3.4 小数

1.3.5 字符串 

1.3.5.1 基本规范

1.3.5.2 varchar和char类型的区别、适用场景

1.3.5.3 varchar和text类型的区别、适用场景

1.4 外键/级联

二、索引

2.1 命名规范  

2.2 创建规范

三、SQL语句

3.1 基本规范

3.2 查询字段

3.3 分页查询

四、对象关系映射（ORM 映射）

---

一、建表规约

1.1 库

• 命名：库名与应用名称尽量一致；

1.2 表

• 大小写：MySQL表名不能有大写字母。因为MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。
• 复数：不可使用复数。
• 不可使用保留字：例如不能命名为add,from,set等。
• 业务名称_表的作用：建议命名“业务名称_表的作用”，例如：
  • 用户信息表：user_info
  • 产品信息表：product_info
  • 客户订单关联表：customer_order_relation
  • 日志记录表：log_record
  • 文章评论表：article_comment
  • 供应商产品关联表：supplier_product_relation
  • 员工考勤记录表：employee_attendance_record

1.3 字段

1.3.1 基础命名规范

• 命名要慎重：字段名的修改代价很大，所以必须要慎重；
• 大小写：MySQL字段名不能有大写字母。因为MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写，大小写混用会出问题。
• 保留字：不可使用保留字。例如不能命名为add,from,set等。

1.3.2 基本规范 

• 注释：字段含义改变时，及时更新注释； 
• 合理冗余：多读少写、长度短、非唯一索引的字段可以冗余，以降低连表查询的次数。
• 关联字段类型：要关联查询的两个字段，数据类型必须一致。如果不一致会导致索引失效，索引和索引失效场景具体可以参考顶部导航文章中的“MySQL高级篇”；
• 分库分表依据：单表数据量五百万条数据，或者容量2GB。
• 三大必备字段：主键、创建时间、修改时间。即id, create_time（或者命名为gmt_create）, update_time（或者命名为gmt_modified）

参考：

MySQL高级篇——索引失效的11种情况_mysql索引失效的几种情况-CSDN博客

一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案-CSDN博客

1.3.3 布尔型字段

• 结构：is_xxx
• 数据类型：unsigned tinyint
• 值：1 表示是，0 表示否
• 对应实体类变量：虽然数据库必须命名成is_xxx，但是该表对应的实体类成员变量不能命名为isXxx，否则会导致序列号失败。所系需要在 resultMap 中进行
  字段与属性之间的映射。

为什么强制 boolean 类型变量不能使用 is 开头？

为了防止序列化失败。

• lombok序列化失败：javaBeans规范boolean变量的getter方法是isXXX()，其他变量的getter方法是getXXX()。lombok遵循javaBeans规范，如果一个变量是boolean isSuccess;在注解@Data或@Getter生成getter方法的时候，它会生成isSuccess()方法，而不是isIsSucess()方法。这也是lombok的一个大坑。
• rpc框架序列号失败：在一些rpc框架里面，当反向解析读取到isSuccess()方法的时候，rpc框架会“以为”其对应的属性值是success，而实际上其对应的属性值是isSuccess，导致属性值获取不到，从而抛出异常。

《阿里规约》原文：

【强制】 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned

tinyint （1 表示是，0 表示否）。

说明： 任何字段如果为非负数，必须是 unsigned 。

注意： POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在< resultMap >设置从 is_xxx

到 Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取

值含义与取值范围。

正例： 表达逻辑删除的字段名 is_deleted ，1 表示删除，0 表示未删除。

1.3.4 小数

• 类型：decimal。主要是为了防止丢失精度。

【强制】 小数类型为 decimal ，禁止使用 float 和 double 。

说明： 在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的

结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。

1.3.5 字符串 

1.3.5.1 基本规范

• 长度几乎固定字段：使用char类型。例如电话号、身份证字段类型char(11)即可，效率要比varchar(11)更高。因为实际存储时，varchar会根据实际输入的内容占用的长度进行存储，因此占用的存储空间是实际内容长度+可变长字段长度（当varchar使用长度≤255时使用一个字节记录，长度超出255时使用二个字节记录）。
• 超长字段：长度超过 5000的超长字段，一律使用text类型，并将该字段独立出一个表。因为text、blog类型会导致索引失效；不使用varchar是因为varchar(5000)太长，建索引后非聚簇索引树过于占用磁盘空间。

参考：

MySQL高级篇——存储引擎和索引-CSDN博客

【强制】 如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

【强制】 varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长

度大于此值，定义字段类型为 text ，独立出来一张表，用主键来对应，避免影响其它字段索

引效率。

1.3.5.2 varchar和char类型的区别、适用场景

长度：

• char：固定长度的字符串
• varchar：可变长度的字符串。

存储方式：

• char：长度固定不可变，未存满的值会用空格填充到固定的长度。因此char类型字符串末尾无法存储空格，当然也不需要额外字节记录字符串长度。
• varchar：varchar会使用1或2个额外字节记录字符串的长度。当列最大长度是255及以下时，varchar会使用一个字节记录可变长长度，最大长度255以上会使用两个字节记录可变长长度。因为varchar有记录长度，所以字符串末尾可以存储空格。

存储容量：

• char：最多255个字符
• varchar：理论上最多65535字节，最多65532个字符（当用utf-8编码存纯英文、且该表只有这一个字段时，字符串中的字符只占1个字节，能达到65532个字符）。但实际从性能考虑，超过5000长度时就不允许再用varchar，而是使用text类型。

思考：varchar(20) 是指字符串最大字节数是20，还是最大字符数是20？

答案：取决于MySQL版本；

• 4.0版本及以下，MySQL中varchar长度是按字节展示，如varchar(20)，指的是20字节；
• 5.0版本及以上，MySQL中varchar长度是按字符展示。如varchar(20)，指的是20字符。

思考：为什么varchar理论上最多字符数是65532？

答案：因为MySQL行默认最大65535字节，varchar还需要1或2个字节维护可变长度，1个字节标识该列是否为NULL。

回顾：各编码的占用长度

• GBK编码：一个英文字符占一个字节，中文2字节，单字符最大可占用2个字节。
• UTF-8编码：一个英文字符占一个字节，中文3字节，单字符最大可占用3个字节。
• utf8mb4编码：一个英文字符占一个字节，中文3字节，单字符最大占4个字节（如emoji表情4字节）。

性能和空间：

• char：性能更好，每次更新时不用维护长度；但存在空间浪费的可能；
• varchar：性能相对差一点，因为每次更新时要维护长度。如果更新后字符串变长后，原来的数据页正好存满，则需要耗费时间处理新字符串的存储；处理方式取决于存储引擎，例如MylSAM将行拆成多个片段存储，innoDB会分裂页。

适用场景：

• char：存储长度几乎固定的字符串适用char类型。例如电话号、身份证字段类型char(11)即可，效率要比varchar(11)更高。因为实际存储时，varchar会根据实际输入的内容占用的长度进行存储，因此占用的存储空间是实际内容长度+可变长字段长度（当varchar使用长度≤255时使用一个字节记录，长度超出255时使用二个字节记录）。 
• varchar：长度几乎不固定、不超过2000字符的字符串。

1.3.5.3 varchar和text类型的区别、适用场景

存储方式：

• VARCHAR：可变长度的字符数据类型，它需要指定最大长度。实际存储时，会根据实际输入的内容占用的长度进行存储，因此占用的存储空间是实际内容长度加上一些额外的长度信息。
• TEXT：TEXT也用于存储可变长度的字符数据，但它可以存储非常大的文本内容，通常可以存储几GB的数据。

索引和查询：

• VARCHAR：由于VARCHAR有固定的最大长度，可以建立更有效率的索引，同时在查询时会更快一些。
• TEXT：对于较大的文本数据，使用TEXT类型可能会导致一些查询性能上的损失，因为文本数据的处理通常会比较耗费资源。

使用场景：

• VARCHAR：适用于长度可预期且不会太长的文本内容，比如姓名、地址等信息。
• TEXT：适用于长度不确定或者非常长的文本内容，比如文章内容、评论等。

优缺点：

• VARCHAR：占用的存储空间相对较小，适合存储较短的字符串，而且支持索引，查询速度较快。但是最大长度的限制可能会带来一些不便。
• TEXT：可以存储非常大的文本内容，并且没有固定长度的限制，适合存储较长的文本数据。但是在查询和索引上可能会稍慢，而且在某些情况下，可能会消耗更多的存储空间。

1.4 外键/级联

• 禁用外键和级联。因为外键影响数据库的插入速度，每次插入时都要检查、更新外键；级联更新是强阻塞，也会影响性能。外键与级联更新适用于单机低并发的场景，不适合分布式、高并发集群的场景
   
  • 级联删除：创建外键时声明级联，则引用表删除数据时，被引用表也会级联删除这条数据。示例

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCAD

  • 级联更新：创建外键时声明级联，则引用表更新数据时，被引用表也会级联更新这条数据。示例

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON UPDATE CASCAD

二、索引

索引相关文章导航：

【Java笔记+踩坑汇总】Java基础+JavaWeb+SSM+SpringBoot+SpringCloud+瑞吉外卖/黑马旅游/谷粒商城/学成在线+设计模式+面试题汇总+性能调优/架构设计+源码

2.1 命名规范  

• 主键索引名：  pk_ 字段名；
• 唯一索引名：  uk _字段名 ；
• 普通索引名：  idx _字段名。

【强制】 主键索引名为 pk_ 字段名；唯一索引名为 uk _字段名 ； 普通索引名则为 idx _字段名。

说明： pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。

2.2 创建规范

• 唯一索引：唯一特性字段必须创建唯一索引。一般不需要另外创建，因为创建唯一约束的时候会自动创建唯一索引。
• 关联查询：被驱动表优先建索引，超过三个表禁止 join。
• 字符串：
  • 模糊查询：禁止左模糊、全模糊索引。因为模糊查询会使索引失效，解决方案是使用ES等搜索引擎实现页面的搜索。
  • 索引长度：必须使用前缀索引。即字符串创建索引时必须指定索引长度，具体索引长度应该在区分度较高的前提下，索引长度越短越好。区分度=count(distinct left(列名, 索引长度))/count(*)，即统计重复次数。前缀索引具体可参考顶部导航文章的“MySQL高级篇”
• 排序：保持联合索引的有序性。例如搜索条件where a=? and b=? order by c;，则创建联合索引：a_b_c
• 联合索引：区分度高的字段放左边。
• 覆盖索引：使用覆盖索引防止回表；例如查询where a=? and b=? and c=?，则创建联合索引a_b_c，而不是a_b，因为走a_b_c的时候，直接在非聚簇索引树就能获取到所有要查询的字段，不需要回表查聚簇索引树。
• 子查询优化深分页：正常情况下，深分页查询性能是很差的，例如我需要1w页第一条数据，那么就需要查出前1w条数据，性能很慢。用子查询可以优化深分页。
•  深分页查询优化：需求是返回第1000000~1000010 的记录。如果直接limit 100000,10，将会先排序前十万条数据并回表，查询速度会非常慢，甚至会超时。
  •  主键有序的表根据主键排序，先过滤再排序：直接查上页最后记录之后的几个数据。

    #自增。适用于app端和web端。由于不建议用自增策略（不安全、8.0才修复的ID回溯问题），所以此方法适用性不广。
    SELECT * FROM student WHERE id > 99999 LIMIT 10;
    #雪花。x是上页最后一条记录的id。只适用于app端上下滑动分页时候必能拿到上页记录id。
    SELECT * FROM student WHERE id > #{x.id} LIMIT 10;

  •  主键不有序的表根据主键排序，先给主键分页，然后内连接原表：当前表内连接排序截取后的主键表，连接字段是主键。因为查主键是在聚簇索引树查，不用回表，排序和分页很快

    SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 1000000,10) a WHERE t.id = a.id;
    

  •  主键有序的表根据非主键排序：得到上一页最后一条记录x（app端通过下拉翻页是肯定能获得上页最后记录的），那么目标页码的所有记录id都比x.id小（因为逆序，且排序依据其实是age,id，主键自增），目标页码的所有记录age都比x.age小或等于。

    #自增
    EXPLAIN SELECT * FROM student WHERE id<#{x.id} AND age>=#{x.age} ORDER BY age DESC LIMIT 10;
    #雪花一个思路，只是x.id通过子查询获取

• 命中索引的要求：SQL 性能优化的目标，至少要达到 range 级别（范围索引），要求是 ref 级别（非唯一索引），最好const（唯一索引）。
• 关注索引失效的11个场景：
  • 尽量全值匹配：查询age and classId and name时，(age,classId,name)索引比(age,classId)快。
  • 考虑最左前缀：联合索引把频繁查询的列放左。索引（a,b,c），只能查(a,b,c),(a,b),(a)。
  • 主键尽量有序：如果主键不有序，需要查找目标位置再插入，并且如果目标位置所在数据页满了就必须得分页，造成性能损耗。可以选择自增策略或MySQL8.0有序UUID策略。
  • 计算、函数导致索引失效：计算例如where num+1=2导致索引失效，where num=1+2不会导致索引失效。函数例如abs(num)取绝对值导致索引失效
  • 类型转换导致索引失效：例如name=123，而不是name='123'。又例如使用了不同字符集。
  • 范围条件右边的列索引失效：例如（a,b,c）联合索引，查询条件a,b,c，如果b使用了范围查询，那么b右边的c索引失效。建议把需要范围查询的字段放在最后。范围包括：(<) (<=) (>) (>=) 和 between。
  • 没覆盖索引时，“不等于（!= 或者<>）”导致索引失效：因为“不等于”不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，索引效率优于全表扫描聚簇索引树。覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。
  • 没覆盖索引时，左模糊查询导致索引失效：例如LIKE '%abc'。因为字符串开头都不能精准匹配。跟上面一个道理。
  • 没覆盖索引时，is not null、not like无法使用索引：因为不能精准匹配。跟上面一个道理。
  • “OR”前后存在非索引列，导致索引失效：MySQL里，即使or左边条件满足，右边条件依然要进行判断。
  • 不同字符集导致索引失败：建议utf8mb4，不同的字符集进行比较前需要进行 转换 会造成索引失效。

三、SQL语句

3.1 基本规范

• 更新前要先查询：删除、更新前要先查询，避免误操作。
• 禁用存储过程；
• 禁用外键、级联。

3.2 查询字段

• 禁用select *。原因：
  • 性能：多查询一些不需要的字段，性能差；
  • 失去覆盖索引的可能性：在命中联合索引时，查询的字段正好在非聚簇索引树中，就不需要回表了，而如果select *，则一定需要回表，影响性能。
  • 对比select 全部字段：即使需求是查询全部字段，也尽量用select 全部字段，而不用select *。原因：
    • 性能：select * 在系统解析的时候会多一步从系统表获取具体字段的步骤，因此会比select 全部字段多花时间，效率稍低。
    • 结果顺序：select 全部字段，查询的结果字段顺序可控；
  • 应用场景：某些特例也是可以用select *的，例如一些特定场景，在开发过程中（非生产环境），表结构、字段名频繁变化，可以暂时用select *
• 查询数量：正确区分count(*)、count(1)、count(字段)
  • count(*)：统计包括null的所有行数
  • count(1)：统计包括null的第一列的行数。因为第一列在每一列都存在，所以等同于统计了所有行，并且不需要检查各行数据， 所以性能可能略高于count(*)
  • count(字段)：统计不包括null的字段列的行数。例如学生表有100行，name列全是null，select count(name) from student查出的结果是0.
• 求和：当某一列值全是null时，count(col)的值是0，sum(col)的值是null，所以求和时要防止空指针异常。

覆盖索引详细参考：

MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计_mysql 前缀索引-CSDN博客

3.3 分页查询

• 先查数量再查询：分页查询前先查询count，如果count为0，则直接返回数据为null，不再分页查询，提高效率。

四、对象关系映射（ORM 映射）

• 禁用select *。原因：
  • 性能：多查询一些不需要的字段，性能差；
  • 失去覆盖索引的可能性：在命中联合索引时，查询的字段正好在非聚簇索引树中，就不需要回表了，而如果select *，则一定需要回表，影响性能。
  • select 全部字段：即使需求是查询全部字段，也尽量用select 全部字段，而不用select *。原因：
    • 性能：select * 在系统解析的时候会多一步从系统表获取具体字段的步骤，因此会比select 全部字段多花时间，效率稍低。
    • 结果顺序：select 全部字段，查询的结果字段顺序可控；
  • 应用场景：某些特例也是可以用select *的，例如一些特定场景，在开发过程中（非生产环境），表结构、字段名频繁变化，可以暂时用select *
• 布尔型字段：数据库表用is_xxx，实体类禁用isXxx
  • 结构：is_xxx
  • 数据类型：unsigned tinyint
  • 值：1 表示是，0 表示否
  • 对应实体类变量：虽然数据库必须命名成is_xxx，但是该表对应的实体类成员变量不能命名为isXxx，否则会导致序列号失败。所系需要在 resultMap 中进行
    字段与属性之间的映射。
• 参数：使用#{}，#param#，而不是${}。防止SQL注入。
• 返回值：强制禁用Map，虽然少去了序列号的过程，性能会快一点，但是字段类型不可控。
• 更新接口：更新时不要更新全部字段，尽量不要写一个参数为实体类的更新接口。一方面可以防止出错、另一方面可以提高性能、减少binlog存储（binlog是二进制日志文件，记录改不记录读，用于数据复制和数据恢复；在主从同步时用到）。
• 不要滥用事务：事务要尽可能的控制粒度，使粒度尽可能的小，例如一些不必要的查询可以放在事务外部，以减少锁冲突、缩短连接时长，从而提高QPS（每秒发送的请求数）