阿里-Java开发手册手记(mysql数据库)

基于Java开发手册 2020泰山版

建表规约

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

• 任何字段如果为非负数，必须是 unsigned

表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字

• MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝

小数类型为 decimal，禁止使用 float 和 double

• 在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储

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

varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率

表必备三字段：id, gmt_create, gmt_modified

• 其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。gmt_create, gmt_modified的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新

单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度

• 无符号值可以避免误存负数，且扩大了表示范围

类型	字节	表示范围
tinyint unsigned	1	无符号值: 0-255
smallint unsigned	2	无符号值: 0-65535
int unsigned	4	无符号值: 0-43亿
bigint unsigned	8	无符号值: 0-10的19次方

索引规约

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引

• 不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生

超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引

• 即使双表 join 也要注意表索引、SQL 性能

在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度

• 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

• 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引

如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能

• where a=? and b=? order by c; 索引：a_b_c

建组合索引的时候，区分度最高的在最左边

• 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c

防止因字段类型不同造成的隐式转换，导致索引失效

SQL 语句

不要使用 count(列名)或 count(常量)来替代 count()，count()跟数据库无关，跟 NULL 和非 NULL 无关

• count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行

count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0

当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE 问题

• 避免 sum 的 NPE 问题SELECT IFNULL(SUM(column), 0) FROM table;

使用 ISNULL()来判断是否为 NULL 值

• NULL 与任何值的直接比较都为 NULL

代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句

不得使用外键与级联，一切外键概念必须在应用层解决

• 学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性

SQL 语句中表的别名前加 as，并且以 t1、t2、t3、…的顺序依次命名

• 别名可以是表的简称，或者是根据表出现的顺序，以 t1、t2、t3 的方式命名。2）别名前加 as 使别名更容易识别

因国际化需要，所有的字符存储与表示，均采用 utf8 字符集，那么字符计数方法需 要注意

SELECT LENGTH("轻松工作")； 返回为 12
SELECT CHARACTER_LENGTH("轻松工作")； 返回为 4

ORM 映射

POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行字段与属性之间的映射

sql.xml 配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL 注入