深入浅出MySQL规范

阿里巴巴规范

建表规约

表结构使用is_xxx形式命名，数据类型无符号tinyint，1是0否。映射java DO对象一定不要带is前缀。

表名、字段名必须使用小写字母或数字。

表名不使用复数。

给字段取名之前搜索一下官方文档保留字。

关键字和保留字

主键索引 pk_
唯一索引 uk_
普通索引 idx_

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

长度差不多的字符串用 char 定长。

varchar 超过5000定义为text，独立建表并使用主键关联。

表必备字段
id bigint 自增
create_time datetime CURRENT_TIMESTAMP
update_time datetime CURRENT_TIMESTAMP 勾选根据时间戳更新。

CREATE TABLE `base` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

表命名：业务名称_表的作用

库名与应用名称一致。

及时更新注释。

字段冗余提升查询性能。冗余字段原则。
不是频繁修改。
不是唯一索引。
不是varchar或text。
例如：冗余商品名称，避免查询调用服务。

分库分表原则：单表500万行或者单表容量2GB。

根据业务选择合适的类型，例如无符号可以扩大表示范围。

索引规约

使用唯一索引来保证业务唯一特性。

多表关联保证被关联字段有索引。

varchar字段建立索引指定索引长度。根据实际文本区分度决定索引长度。

根据区分度来决定索引长度。

SELECT
	count(
	DISTINCT LEFT ( 字段名, 索引长度 )) AS count,
	count(*) AS total,
	count(
	DISTINCT LEFT ( 字段名, 索引长度 )) / count(*) AS percent 
FROM
	表

结果图

MySQL索引只可以处理右模糊，对于其他模糊如果数据量大建议使用搜索引擎如ES。小数据量就不必引入。

file_sort，order by 最后的字段是组合索引的一部分。

explain结果为using index

MySQL分页是取offset+N行。

SQL性能优化 range、ref、consts。

组合索引区分度最高的最左边。

防止字段类型不同造成的隐式转换。

避免一个查询一个索引、避免不创建索引、避免抵制唯一索引。

SQL 语句

count（*）统计null，而列名不会统计null。

统计除null外不重复行数

	SELECT count(distinct 列名) from 表名

使用 SUM 函数要注意 NPE 问题。

使用 ISNULL(expr) 判断是否为 null。

分页逻辑，count为0直接返回。

外键概念在应用层解决。

禁止使用存储过程。

update和delete语句先select避免误删除。

使用 as 取别名。

as t1 t2 t3

in集合元素控制在 1000个以内。

utf8和utf8mb4的区别。

不建议在开发代码使用 truncate table。

ORM 映射

select * 替换为具体的字段，减少io。

映射是与否字段数据库必须加is_，实体类不能加is。

$ 和 # 区别，前者会导致SQL注入。

不推荐使用 queryForList

结果集不能使用 Map 接收。

更新记录更新update_time字段。

不要写一个大而全的数据更新接口。

事务注解不要滥用。