注释：字段含义改变时，及时更新注释；
合理冗余：多读少写、长度短、非唯一索引的字段可以冗余，以降低连表查询的次数。
关联字段类型：要关联查询的两个字段，数据类型必须一致。如果不一致会导致索引失效，索引和索引失效场景具体可以参考顶部导航文章中的“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（每秒发送的请求数）

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

一、建表规约

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 映射）

热门文章

最新文章

相关课程

相关电子书

相关实验场景