一. 建表规范
1、规范表、字段的命名
表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间 只出现数字。
MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
表的命名最好是遵循“业务名称_表的作用”，正例：alipay_task / force_project / trade_config
2、表名不使用复数名词
表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。
3、库名与应用名称尽量一致。
4、禁用保留字
如desc、range、match、delayed、status 等等。
具体可以参照官网：https://dev.mysql.com/doc/refman/5.7/en/keywords.html
5、规范索引命名
主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。
pk_ 即 primary key;
uk_ 即 unique key;
idx_ 即 index 的简称。
6、小数类型
小数类型为decimal，禁止使用float和double
在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的 结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
7、TIMESAMP和DATETIME的区别
TIMESAMP和DATETIME都可以存储日期和时间，精确到秒，然而TIMESTAMP只使用DATETIME一半的存储空间，效率更高，并且会根据时区变化，具有特殊的自动更新能力。但是TIMESTAMP允许的时间范围要小的多('1970-01-01 00:00:01' - '2038-01-19 03:14:07')，在特殊场景下要注意。
8、整数类型的UNSIGNED属性
UNSIGNED属性表示不允许插入负数，同时可以将整数类型的上限扩大一倍。
例如TINYINT默认范围是-128-127，而TINYINT UNSIGNED的范围是0-255。
9、char
如果存储的字符串长度几乎相等，使用char定长字符串类型，可以节省存储空间。
注：char会截取字符串末尾的空格。
10、varchar
varchar是可变长字符串，不预先分配存储空间，只指定需要的长度，如果存储长度大于5000，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
注：varchar需要额外的1个字节存储长度。
-- 《高性能MySQL》
11、及时更新注释
如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
12、字段允许适当冗余
字段允许适当冗余，以提高查询性能，但必须考虑数据一致。
冗余字段应遵循:
1) 不是频繁修改的字段。
2) 不是 varchar 超长字段，更不能是 text 字段。
3) 不是唯一索引的字段。
正例：商品类目名称使用频率高，字段长度短，名称基本一不变，可在相关联的表中冗余存储类目名 称，避免关联查询。
13、字段尽可能设置为NOT NULL
除非真的需要存NULL值，否则要将字段设置为NOT NULL。
因为可为NULL的列会使得索引、索引统计、值的比较变得更为复杂，同时也暂用更多空间。
二. 索引规范
1、善于使用唯一索引
业务上具有唯一特性的字段，即使是多个字段组成，也必须建成唯一索引，唯一索引可以显著提高查询效率（普通索引又称左前缀索引，如果字段过长（767bytes）只会索引字段的前一部分，而唯一索引则索引整个字段）。
不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外， 即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
2、JOIN
表关联建议不超过三个。需要 JOIN 的字段，数据类型必须绝对一致。
多表关联查询时，保证被关联的字段需要有索引。
3、最左前缀原则
索引文件具有B+Tree的最左前缀匹配特性，如果最左边的值未确定，那么无法使用此索引。建立联合索引时，区分度最高的字段放在最左边。
4、控制索引数量
单表索引建议控制在5个以内，联合索引不超过5个字段。
5、慎重建立索引
禁止在更新频繁、区分度不高的列上建立索引；
更新会变更B+Tree，大大降低性能；
"性别"这种区分度不大的属性，建立索引是无意义的，性能与全表扫描相似。
6、利用覆盖索引避免回表
如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗?目录浏览一下就好，这个目录就是起到覆盖索引的作用。
能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现:using index。
7、超多分页优化
利用LIMIT offset,N 进行分页时， MySQL并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
正例：
先快速定位需要获取的 id 段，然后再关联:
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id = b.id;
8、防止因字段类型不同造成的隐式转换，导致索引失效
进行关联查询时，保证被关联字段要有索引，且数据类型一致，防止因字段类型不同造成隐式转换，导致索引失效。
9、SQL 性能优化的目标
至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
1) consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
2) ref 指的是使用普通的索引(normal index)。
3) range 对索引进行范围检索。
反例：
explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。
三. SQL语句
1、慎用select *
不要使用select *，只获取必要的字段，读取不必要的列会增加CPU、IO、NET的消耗。
同时，select * 容易在增加或者删除字段后出现bug。
2、禁止使用隐式转换
where id = 1201800992222222;
id如果是字符串类型会产生隐式转换，即使id有索引也同样会全表扫描。
3、禁止在where条件属性上使用函数
where CAST(create_time AS SIGNED) < 20190708180606;
通过CAST函数，将日期转换成整数类型，导致全表扫描。
4、关于count
不要使用count(列名)或count(常量)来替代count(*)，count(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
count(*) 会统计包括值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
5、统计函数的陷阱
当某一列全为NULL 时，COUNT(col)返回0，使用SUM(col)返回null，统计函数不会将null值加入计算。
6、in与exists
已知A、B两张表：
A:100000 B:100
select * from A where A.id in (select id from B);
大表 in 小表，效率较高：小表可以被加载到内存，大表可以命中索引（in中的内容尽量不要超过200）。
A:100 B:100000
select * from A where exists (select * from B where B.id = A.id);
小表exists大表，效率较高：小表执行全表扫描，大表可以命中索引。
7、外连接需注意
A left join B时应注意：on条件是关联前的筛选，where条件是关联后的筛选；
select * from A left join B on A.id = B.id and B.name = 'z' where a.age > 20; --如果A匹配10条记录，B只有1条记录匹配，最终返回10条记录；
select * from A left join B on A.id = B.id where a.age > 20 and B.name = 'z'; --如果A匹配10条记录，B只有1条记录匹配，最终返回1条记录；
8、update或delete尽量可能锁定少的数据
update或delete尽可能按主键或唯一索引更新，如果记录不存在还会产生间隙锁，尽可能避免在复杂事务中进行批量更新，有几率产生死锁。
9、善于使用EXPLAIN
四. 事务与锁
1、尽量避免大事务
大事务的风险：
1.锁定太多的数据，容易造成大量的阻塞和锁超时，回滚所需要的时间比较长；
2.执行时间长，容易造成主从延迟。
如何处理大事务：
1.避免一次处理太多的数据；
2.从事务中移除不必要的操作，例如select操作、缓存操作、rpc调用等。
2、避免死锁
死锁用例1：
时间 | 会话A | 会话B |
1 | BEGIN； | |
2 | SELECT * FROM t WHERE a = 1 FOR UPDATE； | BEGIN； |
3 | | SELECT * FROM t WHERE a = 2 FOR UPDATE； |
4 | SELECT * FROM t WHERE a = 2 FOR UPDATE； -- 开始等待B释放锁 | |
5 | | SELECT * FROM t WHERE a = 1 FOR UPDATE； -- 等待A释放锁，产生死锁 |
解决方案：
1.可以在业务层面上保证加锁的顺序；
2.大事务拆分成小事务。
死锁用例2：
时间 | 会话A | 会话B |
1 | BEGIN； | |
2 | INSERT INTO test_copy SELECT * FROM test WHERE id =8； -- 对test表中id = 8的记录加S锁，等同于 SELECT * FROM test WHERE id = 8 LOCK IN SHARE MODE； | BEGIN； |
3 | | SELECT * FROM test WHERE id = 8 FOR UPDATE； -- 申请对id = 8的记录加X锁，开始等待 |
4 | SELECT * FROM test WHERE id = 8 FOR UPDATE； -- 等待B释放X锁，产生死锁 | |
解决方案：
将会话A中加的S锁改成X锁：INSERT INTO test_copy SELECT * FROM test WHERE id =8 FOR UPDATE；
保证会话A结束前，会话B不会加入锁的等待队列。
