开发者学堂课程【云数据库优化经典案例：SQL 优化】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/67/detail/1159
SQL 优化
内容简介：
一、分页优化案例
二、子查询优化案例
三、SQL 优化最佳实践
一、分页优化案例
SQL 优化主要是两个案例，第一个是分页优化案例。
上图就是一个案例，可以看到上述数据库，它的 IOPS 是百分之百，但是 CPU、QPS 和连接数值都非常低。因为处理了 IO，它也是被 SQL 占用的。这时去看数据库到底运营行怎样的 SQL ?可以看到数据库运行的 SQL 已经有51秒，一直在 Sending data，这个 SQL其实就是商家导订单的一个 SQL，在分页去导订单。卖家可能是一个非常大的卖家，订单非常多，所以去通过分页去导订单。其实可以看到 SQL 执行的非常慢，数据库中堆积非常多的 SQL。那 SQL 是否可以优化？由上图可以看到此 SQL，前提条件是表中已经有 sellerId 和 gmt_ modified 的索引。
普通写法：
select * from buyer where sellerid=100 limit 10000o , 5000
普通 limit M，N 的翻页写法，在越往后翻页的过程中速度越慢，原因是 mysql 会读取表中的前 M+N 条数据，M 越大，性能就越差。
这个 SQL 其实是一个普通分页写法，在普通的翻页写法里可能会有的问题；普通limit M，N 的翻页写法存在一个问题，就是越往后翻页的过程中速度越慢，因为 MySQL 翻译时不需要把前面 limit 中第一个数据（m）读出来，然后再去读（n）真要去翻译函数的数据，所以随着 M 越大，就是前面翻页第一位的数字越大，后面的性能可能就会越来越差。在以前 BBS 论坛较火的时候，那时其实很多论坛就是翻页写法，采用 limit M,N 的写法。如果论坛前面的几个帖子是热帖，它前面几页访问都是非常快的，但是到后面随着越往后翻页，性能就越来越慢。问题就是最终的 SQL 写法采用了普通写法。随着M，也就是翻译的第一个数字越大，性能会越来越差，那这个 SQL 写法如何优化？
优化写法：
select t1.* from buyer t1.
(select id from buyer sellerid=100 limit 100000，5o00) t2where t1.id=t2.id;
在淘宝以前前台的翻页，比如商品详情页面，其实也有类似的翻译，普通的翻页写法是不能在淘宝前端的，写法上必须经过优化的，那下面这个写法就是淘宝 SQL 翻页的规范。这个规范是怎样实施的？我们可以看到 SQL 首先是查出 id ，这个 id 是真正要去翻译一个5000行的 id。那这里有什么好处？就是说查询这个 id，真正要翻译的。取得5000行的数据，这个数据是在索引里查询，因为这个表里已经有对应 id 的索引，所以查询这5000行 id 时不需要查询前面的10万行数据，先查询5000行 id，再和主表做关联。优化后的写法和原写法的代价有所区别，原来需要查询前面的十万行数据，再去查需要的五千行数据，优化后只需查询这5000行 id 再和原表做关联。这就体现出了分页的优势。需要注意的就是，子查询里的 id 一定在索引里，覆盖索引去查询，不能回表。
注意:需要在 t 表的 sellerid 字段上创建索引，id 为表的主键create index ind_sellerid on buyer(sellerid);
上图是一个分页优化的案例，上面是原写法，下面是优化后的写法，先查 id 再去和主表关联，这样他的执行时间就可以从60秒降低到0.2秒。所以我们可以看到前面这种普通写法查询了大量无效数据，通过优化后的写法扫描的真实的数据大量减少。
这就是我们 SQL 优化里的第一个案例，就是分页优化，这是淘宝数据库开发里很重要的一个点。
二、子查询优化案例
下面一个就是子查询的一个案例，这个也是一个非常典型的案例。
典型子查询：
SELECT first_name
FROM employees
WHERE emp_no IN
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000);
MySQ L的处理逻辑是遍历 employees 表中的每一条记录，代入到子查询中中去
改写子查询：
SELECT first_name
FROM employees emp,
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal
WHERE emp.emp_no = sal.emp_no;
在2014年时，一个大项目中把系统从 orcle 迁到 mysql 上面，时间是在周六，选在周六是因为访客人数相对少一点，系统迁到 mysql 后，当时的系统是5.5，在迁移后，发现 MySQL 集群全部挂掉了，经过分析，发现集群出现 SQL 堆积的问题，因为 MySQL 里处理子查询和 orcle 里处理子查询是完全不一样的原理，导致了当时集群瘫痪这样一个问题。在2010年去 IOE 的时候，也是从大型的 orcle 迁移到 mysql 里面。
可以看到子查询的原理。这个 SQL 是查询薪水为5000块的员工名字，所以，他先去查薪水表，再和 employee 表做关联。所以，这个子查询，正常逻辑是把5000块钱薪水的员工号查出再和主表 employee 表做关联。但在 MySQL 里面，它的优化器支持嵌套循环，通过 A 表驱动 B 表，如果 A 表非常大，那么这个循环次数就非常多，所以这个时候 mysql 的优化器决定了sql 执行的的顺序，在执行 SQL 的时候，从外部employee 表循环去带对应值到子查询里面所以这个 SQL 不是同我们想象的执行顺序，查出5000块薪水的员工号再和 employee 表做关联。当外部的驱动表非常大就会导致遍历到子查询里的次数非常多进而导致性能下降。所以当时把 SQL 进行了改造，把子查询数据提出来，先算出结果级，把子查询往前提和 employee 表放成同一个位置，这样 SQL 的执行速度就快了。
所以在遇到这样的子查询时，要做 SQL 的改写，这里要特别注意。那这个问题通过一个改写，从1200秒缩短为0.1秒，执行速度迅速提升。子查询是在5.1、5.5版本中存在一个较大风险，需要把这个子查询改写为关联，5.6版本后，则不需要再改写子查询。
三、SQL优化最佳实践
1、分页优化
采用高效的 Limit 写法，避免分页查询给数据库带来性能影响
2、子查询优化
子查询在5.1，5.5版本中都存在较大风险，将子查询改为关联使用 Mysql 5.6 的版本，可以避免麻烦的子查询改写
3、查询需要的字段
避免用 SELECT* 查询所有字段数据，只查询需要的字段数据
分页优化，也需要一个高效的分页写法，避免普通的写法给数据库带来较低的性能。第三，经常有很多 SQL 做一个 SELECT * 操作，然后再做一个外层，在包一层做 COUNT * 写法，此时 MySQL 就不得不把 SELECT * 子查询中的结果查出来。比如在外面再套一层条件，那外面的条件就不能只传递到查询里面，进而导致性能的低价低效。所以第一个不要去省 * ，只需要查询需要查询的字段；第二个就是尽量把子查询外面的条件带入到查询里面。
