# 阿里开发手册之 MySQL 编程规范借鉴

TIP

站在巨人的肩膀上，你会少走很多弯路。--鲁迅

# 建表规约

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

表名使用单数形式。

字段名不能使用 MySQL 保留字。如 asc, desc, match 等。

主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。

数据库字段修改的代价很大，无法进行预发布，所以建表时需认真对待，慎重考虑。

表达是否或者开关等概念的字段，必须使用 is_xxx 的命名方式。数据类型使用 unsigned tinyint(1)。

比如软删除字段: is_deleted 1 表示删除（正向，肯定）；2 表示未删除（负向，否定）。

小数使用 decimal

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

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

MySQL5.0 以上版本，varchar 最大可以存储 65535 字节数据（其中1-2个字节存储长度信息，还有 null 标志位）。此外，还受到行大小的限制，行长度不能超过 65535 字节。

在 UTF-8 编码下，一个汉字占 3 个字节，那么最大字符数不能超过 21845.

表定义中的 varchar(10) 表示的是 10 个字符，而不是字节数。

通用字段：

个人觉得创建时间和更新时间还是 created_at 和 updated_at 更好些，简单易理解。

表名命名采用 “业务名称+表的作用”形式。如 alipay_task。

库名与应用名称尽量一致。

如果更新表字段含义或对状态字段追加枚举值时，需要更新字段注释。

字段允许适当冗余，以提高查询性能，但必须考虑数据一致性，冗余字段应遵循：

不是频繁修改的字段。
不能是 varchar 超长字段，更不能是 text 类型字段。正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。

单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。例如年龄字段或者状态枚举字段，在明确知道其范围的情况下，完全可以限定其类型长度。

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

说明：唯一索引是保证业务数据唯一性的强有力保障，单纯靠应用层代码控制，很难做到 100% 唯一性。虽然索引会影响 insert 速度，但几乎可以忽略，它提高查询的速度更明显。

禁止超过 3 个表 join，需要 join 关联的字段，数据类型必须一致，必须有索引。

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

对于blob,text,varchar的列必须使用前缀索引。

禁止左模糊或全模糊匹配。如果有需要，请走搜索引擎解决。

如果有 order by 的场景，注意索引的有序性，避免出现 file sort 的情况影响查询性能。

说明：where a=? and b=? order by c; 可以用到索引 a_b_c。如果索引中有范围查找，那么索引有序性无法利用，如 where a > 10 order by b; 索引 a_b 无法使用。

尽量利用覆盖索引来避免回表。

关于分页优化，MySQL 的 limit 查询并不是跳过 offset 行，而是取 offset + N 行，然后舍弃前 offset 行。当 offset 很大的时候，效率自然就低下。可以利用子查询以索引覆盖从而提升性能：

select t1.* from t1, (select id from t1 limit 1000000, 20) as t2 where t1.id = t2.id;

查询的类型，效率从低到高依次为：all -> index -> range -> ref -> eq_ref -> const。

sql 优化的目标：至少要达到 range 级别，const 最好。

建索引时，区分度高的字段放在左边。

防止因字段数据类型不同造成的隐式转换，造成命不中索引。