开发者学堂课程【MySQL 实战进阶：MySQL 开发规约实战】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/852/detail/14063
MySQL 开发规约实战
内容介绍：
一、语句规范要建立在结构规范的基础上
二、SQL 语句编写规范
三、事务的使用与优化
四、开发中常见问题与最佳实践
阿里集团对 Java 开发作出一系列相对比较标准的规范，其中一部分涉及到了 MySQL 相关的一些内容，包括建表规约、索引规约、SQL 语句怎么写、针对数据库操作 ORM，这堂课主要针对规则里的前三项做出更细的说明，更偏原理。整个开发手册更大一部分内容是针对 Java 开发，MySQL 是其中一部分内容，现在用到的数据库有多种多样的开发工具，实际上后台的数据库 MySQL 为多，所以 MySQL 去写有一个比较通用性的标准。
一、语句规范要建立在结构规范的基础上
1、字符集
字符集大家经常用 UTF8，虽然图片里写的是常用 latin1、gbk、utf8、utf8mb4，但是目前互联网实际是以 utf8为主，因为 mb4在近些年 emoji 这种表情符号多了起来，大家用的更多，有一个问题就是包括之前有的版本像5.5、5.6、5.7，默认的时候建的表都是 utf8，但是到了8.0以后会考虑 emoji 这种新的业务会上到8.0，所以是更通用，mb4是更通用字符集。
但是在8.0之前默认的排序规则是 utf8_general，到了8.0以后变成 utf8mb4_0900字符集，这个字符集是8.0独有的，也是它能默认的规则，但是经常性的会在这个问题上出现不兼容的情况，就是字符集不一样，比较不出来，所以要注意。
2、字段
字段有几个比较大的规则。第一，实际上是一个业务上对需求，不光是字段名，还有表名，这块是说统一字段名、统一类型，包括表名肯定是要跟业务相关的，字段也是业务相关的。实际上是有一个解决业务上歧义的问题，像 MySQL 内部的系统，information 下边的 tables 表，如果按照一些不太规范的命名，像 table name 可能会命名上 table，在写查询的时候可能会导致意义上的混乱。还有一个是类型的统一的问题，就是隐式转换的问题，包括表格连接可能会导致这种隐式转换。
长度字段长度，varchar(225)是比较常见写法，不知道业务将来要存多少东西，就先给一个 varchar(225)，这种情况只能是在开发阶段方便自己建表的测试，实际上到真实的生产业务后，它有很严重的性能隐患，包括索引评估，假设在 varchar(225)场地的字段上建一个索引，索引评估会考虑列长度，包括字段太多的时候，比如有两个225或者三个225的时候要建一个复合索引，真实的值可能每个字段里只存了10或者20，但是索引是建不出来的，这时对线上维护还有一些后继的业务开发都是有影响的。
定义组件的问题，首先 PK 就是 primary key，必须要有，在没有组件的时候可能会有很多的问题，包括一致性、复制时的性能问题。另外一个是无符号定义组件，这个定义是 id int primary key 直接定义的组件，一般在一个表里的 id 是一个自增，但是这时候定义一个 int，首先 int 是一个永符号，就会导致 int 可用的值会少一半，int 值在永符合是21亿，如果定义成无符号，实际上是42亿，在数据量增长很快的时候，有一半的数据范围没有用上，经常会有 ID 或者某个自增长满了这种问题。
禁止 Null 值，就是因为默认间断有很多字段，不给一个有意义的值默认空值，空值有什么问题呢？就是包括一些排序、建索引、做表整理、查询的时候，Null & Null =？拿排序的方面来说，两个行做比较的时候会导致随机的顺序，变成业务上的乱序。还有一些也是像这种无主键表一样，会导致一些复制、数据不一致的问题，所以尽量要避免空值，而且还有一个场景是业务上经常会允许空值，但是去查的时候经常会用 is null 去返回默认值，is null 等于什么或者等于什么，这个谓词是有问题的，包括索引的利用都可能会出现问题，这是一个相对严格的要求。
3、索引
80%的语句性能问题都可以靠索引解决，但是有一个问题是用不用所有单列出现索引，实际上，真实生产环境里很多这种情况，比如有20个列，每个列都有一个单列索引，还有一个是定期 review 索引里是不是用到了，在 MySQL 里可能不太去思考定位，所以这块经常检查的是随着生产环境，还有整个业务的发展会建很多新的索引出来，这时候要看覆盖索引已 cover 旧索引，旧索引是没有用的，如果再去维护还要多一个 io 的成本，所以尽量删掉，或者直接评估哪个索引最优，留有效的。
不要走极端，比如这张表有七八个列，只对一列有索引，索引有回表、不回表的区别，直接建立一个复合索引出来，是这张表的所有列，但是这种东西是比较不可取的，因为等于它又另外维护了一张表。所有列都建单列，实际上跟上面复合索引所有列是一样的。
二、SQL 语句编写规范
1、规范语法
MySQL 就是相对成熟的产品，但是它支持的语法不是标准的 SQL 语法。
Select * from sbtest.sbtest1 group by id，实际上在一些标准的，比如传统的数据库经常见的比如 Only_ full groupby，这是非法的语法，根本不支持。
还有8.0已经淘汰的语法，Select id,count(*) from sbtest.sbtest1 group by id desc，这两个语法都不是一个标准语法，不建议在生产环境里去用，因为包括升级，MySQL 逐渐在做自己的规范化，像这种不兼容语法，版本升级以后会带来大量的应用报错、语义报错，所以尽量要把语法一开始就写的比较规范，符合收费标准。
2、别名
如果建一个子查询，把查询框起来返回，不给别名根本不能执行。还有一种是在列上的别名，有一些应用是获取的，按照数组数据获取的，返回的列值，但是如果不给别名会语义不清，返回了一个值不给名，是什么意思呢？另外，标准的写法要 as 一个别名，如果不写，实际上支持，但是语义特别复杂的时候，可能会造成语句有点乱，建议从一开始就标准。
3、执行顺序
这是一些经常见到的关键词：
(1)FROM, including JOINs
(2)WHERE
(3)GROUP BY
(4)HAVING
(5)WINDOW functions
(6)SELECT
(7)DISTINCT
(8)UNION
(8)ORDER BY
(10)LIMIT and OFFSET
从数据流上来看，首先做的是数据筛选 from，然后 where、group by，然后从 select 以下开始执行的是在做运放，包括一些取重，union、order，limit，取数据，要注意数据流是怎么流的，比如 order by、limit 这种场景就在最后的运行。
limit 300000，20取20条数据，明明取20条数据为什么这么慢呢？实际上是把所有的数据取出来以后，最后才去做运载，数据量非常大。还有就是数据的筛选机制，很长一段时间都会认为是 where 先去做数据筛选，然后再去做 join，这个说法其实都不成立。因为从这里看是先做 join，然后做 where，但是在做 in the join 的时候，会把 where 条件带进去，在这种情况下 in the join 跟 where 的条件是一样的，它都是有一个标准的筛选去做这个事，但是在做 left join 还有 right join 的时候，先去引出索引数据，然后再通过 where 去筛选数据。要注意数据流向，然后才能说语句怎么最优的写出来。
4、如何判断语句是否已最优 explain
开发手册要求是最起码要到认知范围扫描，避免了所有的全部扫描，包括 index 全索引扫描，从性能角度来说，就是从下往上越来越次，形势越来越差。
ALL:Full Table Scan,全表扫描
index:Full Index Scan,索引扫描
range:范围扫描
ref:表示连接匹配条件
eq_ ref:类似 ref,区别就在使用的索引是唯一索引
const:常量查询，比如 pk 等值
system 是 const 类型的特例，查询的表只有一行的情况下使用 system
反过来说，如果只看上列 TYPE，实际上看下面这张图是 type，
另外怎么知道这些真实执行是怎么去执行呢？实际上还差 Extra 列。
Using filesort，排序
Using index，使用索引可以返回请求列
Using index condition，通过索引初步过滤，回表再过滤其它条件
Using temporary，临时表
Using where，单独出现时，一般代表表上出现全表扫描过滤
Using index & Using where使用索引返回数据，同时通过索引过滤
可以结合上面这张图来看执行计划是不是对的，首先从上往下看，第一个驱动表用了一个 PK，同时用的是 Using index condition，where，extra 里还能打出更多的东西，但是比较主要的是这里列出的几个，Using index condition 用了索引的初步过滤，有一些数据是回表的，有一些语句没有列出来，因为接下来还有其他一些物质条件。Using where 是通过索引过滤那些数据，然后从外边取出去开始向里边做循环，Using index、Using where 扫描的列就是 key_lem，这种情况可以说明计划还可以。
5、禁止项
禁止项并不在错误，只是在某些场景下如果完全禁止也不太可能，只能在开发环境里面能够规范，最好是禁止掉，包括 select*,返回大量的无用数据，比如通过索引返回数据，非要返回 select*，多了一些没用的字段，有一些过多的 IO 的消耗，如果加一个字段，在 select*的情况下，应用接收这个数据时是莫名其妙多了一个字段的。Insert 语句指定具体字段名称，不要写成 insert into，t1 values(...)道理同上。禁止不带 WHERE,导致全表扫描以及误操作，where 条件里等号左右字段类型必须一致，否则会产生隐式转换，无法利用索引。索引列不要使用函数或表达式，否则无法利用索引。如 where length(name)= 'Admin' 或 where user id+2=5。Replace into,会导致主备不一致。严格禁止业务语句中带有 DDL 操作，特别是 truncate，因为 DDL 是一个成本非常高的操作，把它放到一个正常业务逻辑里，出了问题会很严重。
6、建议项
减小三表以上 Join
用 union all 替代 union
使用 join 替代子查询
不要使用 like '%abc% '，可以使用 like 'abc%'
Order by /distinct /group by 都可以利用索引有序性
减少使用 event/存储过程，通过业务逻辑实现
减小 where in()条件数据量
减少过于复杂的查询. &拼串写法
7、用数据库的思维考虑 SQL
数据库要处理的是数据集，并不是单数据，它和开发的逻辑可能不太一样，开发里面会有比如最好用一个语句解决所有问题，但是在 MySQL 里它不是一个特别好的开发 sever 的习惯，因为这样会导致 sever 非常大，最大可能几万行，要秉承一个语句解决所有问题的原则，但是每层数据要处理的少，处理的数据不能太大，一个简单的业务逻辑都可以实现。开发应用的逻辑性语句也是比较典型的，比如循环可能是有这个问题。最后，新 feature 谨慎应用，因为 MySQL 本身也是在不断的发展，有一些非常新的 feature，大家可能觉得变成文档的数据库可以把它用到生产里，但是没有经过长时间测试的情况下，尽量不要把一些非常新的规则应用到生产环境里，这种情况下有可能会有一些性能上的问题，所以一定要充分的测试以后，然后再上到生产。
8、Sql 改写-join
编辑规范是通过一些日常做优化的时候，发现它有可能会有问题，然后再去做一些规范，比较典型的例子就是现在 join 场景的性能有可能要做一些调整，包括顺序不一样或者条件不一样，就导致语句性能是完全不一样。注意 join 键为 PK，也就是左表右表应该是1对1的关系，在 left join 的情况下，可以理解成返回的数据全部是左表的数据，只要 count 一下 a 表就OK。
select count(a.id) from sbtest1 a left join sbtest2 b on a.id=b.id;
mysql>
select count(a.id) from sbtesti a left join sbtest2 b on a.id=b.id;
count(a.id)I
20000000|
1 row in set (19.34 sec)
直接跑的语句时间是大概20秒，可以直接简化为下边这个 style*。
第二次执行是三秒左右，速度翻了几倍，但这种 Sql 改写一般会出现在复杂查询的 Join 场景中，除去显式 join,还包括半连接 exists,in，反连接 not exists,not in，此类查询过慢时，请考虑是否可通过 SQL 改写优化。
9、Sql 改写-分页统计
select a.id from sbtest1 a left join sbtest2 b on a.id=b.id limit 200,20;
取总数据量:
select count(*) from
(select a.id from sbtest1 a left join sbtest2 b on a.id=b.id) as a;
比如最经常见的一种业务逻辑，有这么一个分页的语句，但是要在页面上显示所有的数据量，比如有1万条数据要分页，会经常性出现把上面所有语句逻辑框起来，然后直接外面加 count，实际上可以把它改成 count*，当然它和本身框起来执行计划是没有区别的，但是改写成这样后会变成 keys1里面的情况，可以再继续简化到第二种情况，就是用一个十几秒执行的语句，或者直接变成了执行零点几秒。
改写1:
select count(a.id) from sbtest1 a left join sbtest2 b on a.id=b.id;
改写2:
select count(a.id) from sbtest1 a;
两个对比，一个15秒，一个0.25秒，实际上是这个逻辑，也就是精简语句目的是第一要简化业务逻辑，因为框来框去的容易看乱，第二是能不能再找一个进一步优化的余地，只有简化了以后，实现业务逻辑简化，才能更简单的看优化的空间。
三、事务的使用与优化
1、事务是什么
并发控制的单位
4个属性:
- Atomicity
- Consistency
- Isolation
- Durability
Isolation 下的隔离级别 Read Uncommitted、Read Committed (一般采用)、Repeatable Read (官方默认)、Serializable
只强调一点:大事务不等于长事务。例如:
(1)Insert table batch，它可能是一个大事务，但是它可能不太长。
(2)Begin
insert single data
sleep(3600)
Commit
Insert 一条非常简单的数据，但是直接 sleep 了，就不 commit，就变成非常长的事务了。
2、事务的问题
(1)Undo 异常增长
在5.6之前整合在 ibdata 表空间里，导致 ibdata 撑的很大，有一些无效的空间占着空间，也是存储成本，但是 ibdata 无法像表一样做优化，唯一能做的办法是重建一次实例，把数据导过去。同样还有就是 Hitory list 过长，会导致很严重的性能问题。
(2)binlog 异常增长
单个事务不拆分存放，假如有语句 begin，开始插入一条数据，到最后一条数据要写的 binlog 下，会导致某一个或者某一些 binlog 非常大，在去做复制、组成的时候会产生一定的问题。
(3)Slave 延迟
DDL 类是语句集的回放，在 Slave 上要等，等待执行完了才能再继续。
(4)锁问题
死锁、阻塞是比较常见的一些问题。
3、优化
大事务拆分为小事务，大事务比如数据清理、delete，要删一条数据直接 delete 一条，一定是一个非常大的事务，一般会导致一些锁的问题，所以建议是拆成分批的。DDL 拆分是一个大事务，包括一些加列，在估价的时候是要锁表的，这块可以使一些无所变更的一些方式，比如三方开源工具。
长事务合并为大事务(特别是写入场景)，好像和第一个大事务不拆小事务有冲突了，实际上不冲突。事务分解(不必要的请求摘除)包括一些写，同时有些读，但是这个读和事务本身关系不太大，将读从事务中摘掉，这个事务会短很多。因为长事务会做一些拆分，所以在应用侧最好可以做一些一致性的校验。
事务使用基本原则：在保证业务逻辑的前提下，尽可能缩短。
4、事务问题定位
长事务:
Information_ schema.innodb _trx
例如:
SELECT trx.trx id, trx.trx started,trx.trx mysql _thread id FROM INFORMATION SCHEMA.INNODB TRX trx WHERE trx.trx started < CURRENT TIMESTAMP - INTERVAL 1 SECOND
锁问题
8.0以前:
information_ schema.innodb lock_ waits. innodb locks
8.0
performance_ schema. data lock_ waits、data_ locks
四、开发中常见问题与最佳实践
1、分页问题
传统写法 select * from sbtest1 order by id limit M,N
问题点:需要扫描大量无效数据后，返回请求数据
select * from sbtest1 where id > #max_ id# order by id limit n 适用顺序翻页的场景，每次记录上一页#max_ id#带入下一次查询中
select * from sbtest1 as a inner join (select id from sbtest1 order by id limit m, n) as b on a.id = b.id order by a.id 适用只按照 id 进行分页,无 where 条件
select * from sbtest1 as a inner join (select id from sbtest1 where col= xxxx order by id limit m, n) as b on a.id = b.id order by a.id 适用于带 where 条件，同时按照 id 顺序分页，此时，需要在 where 条件上创建二级索引
2、大表数据清理
数据清理场景:
(1)历史数据清理
通常按照条件(比如:时间范围) delete 历史数据
问题：单次 delete 行数过多，容易导致锁堵塞、主从复制延迟、影响线上业务，易失败，死锁、超时等。
建议方案：
伪代码
Select min(id),max(id) from t where gmt create< $date
For lin "max(id)-min(id)/1000"
Delete from t where id> =min(id) and id <min(id)+ 1000 and gmt_ create < $date
......
定期 optimize table 回收碎片
(2)全表数据清理
Truncate 整张表的数据
问题：大表(如: >100G) ，truncate 期间会造成 io 持续抖动。
建议方案：
硬连接方式后 truncate,异步 trim 文件
3、隐式转换问题
Create table testtb(id varchar(10) primary key);
Select * from testtb where id= 1;
隐式转换发生在比较值类型不一值的场景下，除去一些规定情况最终都是转换为浮点数进行比较。
此类问题在编写时很难发现，上线导致严重性能问题。
4、循环
(1)外部循环
应用侧实现，主要问题来自每次请求的 rt，例如:
for i=0;i+ +;i<500
insert (db 交互)
next
rt=single rt* total count
建议 batch 一次写入。
(2)内部循环
一般常用在存储过程，事务无法保证，
While do
insert;
Commit;
end while
频繁 commit 会造成 io 上的冲击。
或:
Begin tran
While do
insert;
end while
Commit
会导致数据一致性以及长事务。
5、存储过程中的事务处理
create procedure insertTest(IN num int)
BEGIN
DECLARE errno int;
declare i int;
declare continue HANDLER for sqlexception set errno=1;
start transaction;
set i=0;
while i<num do
INSERT testfor VALUES();
set i=i+1;
end while;
if errno=1 then
rollback;
else
commit;
end if;
end;
6、常见问题-1
(1)Where 后面的列顺序是不是要符合最左原则?
Where a=1 and b=2等价于 Where b=2 and a=1
最左原则指的是索引顺序，不是谓词顺序，以上两个条件都匹配(a,b)复合索引
(2)Join 的顺序是不是指定了左边为驱动表
inner join 场景下，在执行计划中按预估自动选中驱动表，left join ,right join 时左右写的顺序才有显式意义
(3)业务上有随机返回的需求，可不可以用 order by rand0
一般不建议，如果结果集非常小，勉强可用，但结果集大时由于随机数排序，会产生 sort操作，甚至溢出到磁盘，有很大性能损耗
此类需求可以考虑伪随机算法，具体不在此提供
(4)Delete 数据之后，为什么磁盘空间占用反而大了?
Delete 数据并不能清理数据文件空间，反而会导致 undo,binlog 文件的增长，使用 optimize 收缩
7、常见问题-2
(1) Binlog 是否一定要 row 格式
在主从场景下，Binlog 使用 row 格式是为了保证主从数据一致性
单机场景下，Binlog 做为增长数据备份使用，同时也包括一些语句级数据恢的功能
(2)死锁、阻塞的区别
通常说的阻塞，主要是由于锁获取不到，产生的请求被阻塞了，一般需要手动解锁(kill 或等待)
死锁不等于阻塞，虽然死锁中阻塞是必现的，但是会自动回滚事物解锁，不用手动处理，但需要业务判断语句逻辑
以上两种情况都是由于业务侧逻辑出现，并非内核原因
(3)做 DDL 时是否会锁表
参考官方文档:
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
