day2-MySQL规约

前言

如果没有统一的约定，那么必然会带来许多额外的工作。例如：JDBC规范。如果没有这个规范，每个数据库厂商都各自为政，开发自己的一套与数据库交互的驱动，那可想而知，对于开发者而言无疑会增加负担和工作。这里，我们来聊一聊，编码规范中的MySQL规范。

MySQL规约

建表规约

表或字段命名

1. 必须使用小写字母或数字。
2. 禁止以数字开头。
3. 禁止两个下划线之间只有数字。
4. 不使用重复名词。
   即使以逗号分隔的，有集合意义的字段也不要这样取名。这类字段不应太多。同时如果存储的值太大，应当作为大字段对待。
5. 禁止使用保留关键字。
   避免在某些情况下发生冲突。
6. 是否概念字段，应使用is_xxx命名。
   这个需要注意：在做ORM映射时，字段不要使用isXxx，而应当把is干掉，避免某些框架做Bean解析造成错误。而java类型，可以用boolean.

数据类型

类型含义	类型	备注
小数	decimal	避免计算上的错误（小数和整数是单独存储的）
货币	bigint	用最小单位-分，避免小数运算
字符型	char	值域长度几乎相同（不够长度，自动填空格）
字符型	varchar	需要用1-2个字节来记录长度，最长不要超过5000.与char的区别

表中必要字段

id主键
create_time创建时间
udpate_time更新时间

推荐规约

1. 表名：业务名称_表的作用
2. 库名应当与应用名字保持一致
3. 修改字段含义时，应及时修改该字段的注释
4. 字段允许冗余，以提高查询性能，但数据类型必须一致。
   注意，如果单表数据量太大了，因为此时查询效率更多的受数据量的影响。数据量大的表，可以考虑只做单表查询，再把其关联字段通过索引来查询其他表，如果频繁查询的字段可以考虑缓存。
5. 单表超过500万行，或容量超过2GB，可以考虑分库分表。

索引规约

索引的特点：持久性（占用存储空间）、有序性。
索引（Index）是帮助数据库高效获取数据的数据结构

索引分类

按是否存储数据分：

• 聚簇索引：叶子节点存储的是数据。
• 非聚簇索引：叶子节点存储的是主键。

按是否带有唯一约束：

• 唯一索引
• 普通索引

主键是唯一标识一个实体的一个属性或属性集。
数据库会默认给主键子弹建立一个比较特殊的索引，既在叶子节点存储数据，也会带有一个唯一约束。所以主键索引既是聚簇索引，也是唯一索引。通常一个表只有一个聚簇索引，因为数据只存储一份。

索引命名规约

主键索引名：pk_表名_字段名
唯一索引名：uk_表名_字段名
普通索引名：idx表名_字段名

创建规约

1. 有唯一特性的字段应当设置唯一约束，基于此，如果该字段还没有唯一约束，应当建立唯一索引。
2. 在varchar字段上建立索引时，必须指定索引长度。
   说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
3. 创建组合索引时，区分度最高的在最左边。

小结

索引会占用存储空间
索引可以提高查询效率
索引会影响记录更新和插入效率

在创建索引时，应当综合权衡对查询效率和更新插入的效率，合理创建索引。因为创建索引需要先排序，所以优先使用天然有序的数据类型。推荐使用数字型数据类型，这样一来减少空间占用，而来效率高。

SQL规约

1. 查询时，应当避免字段隐式类型转换。想象一下把字段值转换之后再来做比较，原索引还能用吗？
2. 利用索引覆盖，从而避免回表。
3. 需要排序的情况，应尽量利用索引字段的有序性。
4. 禁左模糊、全模糊查询。真的需要，则使用搜索引擎。
5. 超过三个表禁止 join
   知乎上有大佬说因为MySQL的优化器和执行器都比较弱。
6. 推荐使用count(*)，让优化器来优化。
7. 当某一列全部都是NULL，count(col)返回0，sum(col)返回NULL
8. 分页查询时，count=0，应提前返回。
9. 调页查询优化，通过延迟关联或子查询优化

回表，是指对于像MySQL-innodb引擎这样的通过聚簇索引来存储数据的，通过普通索引查询数据时，需要再更加其叶子节点的主键再到主键索引中查找数据的过程。
而像PostgreSQL这样使用堆表存储数据的，所有的索引的叶子节点存储的都是数据在堆表中的坐标，不管是主键索引还是普通索引，都是要回表才能取出数据。

避坑指南

1. 不使用外键与级联，关联在应用层解决。
2. 禁止使用存储过程。其拓展性差。
3. 更新/删除前应先查询，避免误删除。
4. 涉及多张表时，应使用别名。
5. 表的别名前面要有as，并且以t1、t2、t3命名。
6. in的元素个数不宜太多，应当控制好选择率，避免索引失效。经验值不超过1000个。

优化目标

结合Explain，将SQL优化到range级别以上。

ORM映射规约

1. 在表查询中，一律不要使用 * 作为查询的字段列表
2. POJO类的布尔类型，不要使用is前缀。
3. 查询返回结果都要使用ResultMap映射，避免增加字段时出现遗漏。
4. 不要使用{}，避免SQL注入。
5. 不要使用Mybatis的queryForList。据说有坑，对内存不友好。
6. 不使用HashMap接受数据集。
7. 更新表时，必须同时更新update_time。
8. 不要写一个大而全的接口
   避免更新不需要更新的字段。其一容易出错，其二效率低，其三增加binlog存储负担。