开发者学堂课程【MySQL 实战进阶：MySQL 开发规约实战】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/83/detail/1311
MySQL 开发规约实战
6、下面是要求的一些规范，禁止项并不代表错误，在某些场景下也是建议，完全禁止也不太可能。如果开发环境能够规范，最好还是规范。select * 返回大量的无用数据，比如通过索引可以访问数据，非要访问 select * 多了没用的字段，会有过多 IO 的消耗。如果加了一个字段，在 select * 的情况下，应用接收数据时会多一个字段，会导致问题。隐式转换左右两边字段必须一致，索引列用计算，这个例子可能题干不太强，最常见的比如说表上有一个叫 credit card，创建的数据可以直接算。
前一天所有的新建的数据可能会写一个日期函数，实际上就变成了本身在 create data 上没有索引，本来是一个认知扫描，结果变成了全表扫描。在列上运算会导致索引失效，replace into 导致主备不一致，这是官方的缺陷，建议禁止。主备架构在生产环境是比较常见的，严格禁止在业务语句中带有 DDL，包括数据的删除、加减列。因为 DDL 是一个成本非常高的操作，把它放在正常的业务逻辑中，出了问题以后是很严重的。
禁止项
✓ select *，返回无用数据，过多IO消耗，以及schema变更问题。
✓ insert语句指定具体字段名称，不要写成insert into t1 values(…)，道理同上。
✓ 禁止不带WHERE，导致全表扫描以及误操作。
✓ where条件里等号左右字段类型必须一致，否则可能会产生隐式转换，无法利用索引。
✓ 索引列不要使用函数或表达式，否则无法利用索引。
如where length(name)=‘Admin’或where user_id+2=5。
✓ Replace into，会导致主备不一致。
✓ 业务语句中带有DDL操作，特别是truncate。（严格禁止）
7、建议项
建议项是一些比较标准的建议，MySQL 的优化器目前 join 方式目前只支持 next to，8.0.2 以后才支持新的 hayi join 方式。表越多证明笛卡尔积越多，数据量可能是指数级的增长，建议减少这种多表 join。✓union all 代替 union，区别是 union 去重，union all 不去重。去种意味着所有数据要排序，CPU 消耗非常高。
✓使用 join 代替子查询，子查询有时候会解释成半连接，半连接效率是有问题的，使用 join 会好一点。
✓不要使用 like ‘%abc%‘，可以使用 like ‘abc%’。（指的是索引的问题。）
✓Order by /distinct /group by 都可以利用索引有序性。如果一张表非常大，仅仅只用 order by 也是可以在列上建索引的。
✓ 减少使用event/存储过程，通过业务逻辑实现。这是维护性的开发，在 MySQL 里面本身存储过程并不能提升性能，只是业务逻辑的分装，并且非常难调试，出了问题难定位。
✓ 减小 where in() 条件数据量。
✓ 减少过于复杂的查询. & 拼串写法。（拼串写法尽量少用，会影响调优。）
8、用数据库的思维考虑 SQL
用数据库的思维考虑 SQL，实际上要处理的是数据集，并不是单片数据，与开发逻辑不同。开发里面像 oracle 有的 dbinno 会说最好用一个语句解决所有问题，但是这在 MySQL 里不是一个很好的开发 SQL 的习惯，这样会导致一个 SQL 非常大。
要秉承右边的原则，一个语句解决所有问题，但是要满足每层数据都要处理的少，处理的数据不能太大。简单的业务逻辑都能实现，这样一个语句解决所有问题是可以的。开发应用的逻辑写语句，所有的运算、判断应用逻辑都放到 SQL 实现，循环有这个问题。存储过程使用过重，难以调试、定位问题。最后一点，新 feature 要谨慎应用，因为 MySQL 本身在不断的发展，非常新的 feature，包括5.7、8.0 都已经支持 Jessie，没有经过长时间测试的情况下，尽量不要把非常新的 feature 使用到生产中，这种情况下有可能会有 bug，没有 bug 也可能会有一些性能上的问题，要充分的测试以后再使用到生产中。
9、Sql 改写
这些规范是通过日常做优化的时候，发现它可能会有一些问题，然后再做这些规范。
Sql 改写 -join
select count(a.id) from sbtest1 a left join sbtest2 b on a.id=b.id
image.png
典型的例子就是 join 的场景，它性能不好的情况下，有可能要做一些调整。join 键为 PK，右表也是join 的 PK，它实际上是一对一的关系，在 left join 的情况下可以理解成返回的数据全都是左表的数据。count a 表就可以了，执行时间大概是20秒。可以把它简化为 select* 直接 count a 表。
select count(a.id) from sbtest1 a left join sbtest2 b on a.id=b.id;
等价于
select count(a.id) from sbtest1 a ;
如下图所示，改写join后，执行时间大概是3秒左右，速度提升了几倍。
Sql 改写一般会出现在复杂查询的Join场景中，除去显式 join，还包括:
半连接：exists,in
反连接：not exists,not in
此类查询过慢时，请考虑是否可通过SQL改写优化。
10、分页统计
（1）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”，但语句冗余，且执行时间长。
（2）改写的方法有：
改写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;
如上图所示，有两种改写方式 ，执行计划上无本质区别，可以把它改成直接 count *，但它和直接框起来的执行计划无本质区别。改写成这样会变成 keys1 里面情况，可以把他继续加化，加化成第二种情况。第一种改写后执行时间为15秒，第二种改写后执行时间为0.25秒，且语句更加简单。精简语句的目的，第一，简化语句逻辑;第二，进一步寻找优化空间。
四、事务的使用与优化
1、事务是什么？
有两点要注意，一点是当前生产环境下，用的 Read Committed隔离级别比较多。官方默认是 Repeatable Read，因为 Repeatable 隔离级别下边最严重的问题是可能会导致严重阻塞，所以现在一般生产环境采用Read Committed。还有一点要强调的是大事务不等于长事务，
例如上图列的两个场景，第一个“Insert table batch” Insert 是一个大事务，但不是长事务。第二个 insert 是简单的一条数据，但 sleep 不包括 Commit 就成了一条很长的数据，如果 DDL 是原子形的，可能又大又长，再加列或建索引的话可能越来越长。
2、事务的问题
undo 在5.6之前都整合在 ibdata 表空间里，5.6拆出，默认在 ibdata 下，会导致 idbata 很大，会占空间，这也是存储成本。但是 idbate 不能像表一样做 ODS 优化，能缩下来的办法是重建实列，把数据导回去，undo 异常增长的时候指的是空间问题。同样，History list 太长，行版本太长，这会导致很严重的性能问题。binlog 异常增长，单个事务在 binlog 层面不做拆分存放，有一个语句 begin，插了一亿条数据，一亿条数据是要写在一个 binlog 下的，会导致某一个或某一些 binlog 非常大。做复制或组从的时候，会产生一些问题。
salve 延迟，DDL 是语句级的回放，在 sleeve 上执行完才能回去。包括一些锁的问题，死锁、阻塞是一些比较常见的问题。
