1、极限语句

分页查询是最常用的场景之一，但也通常也是最容易出问题的地方.比如对于下面简单的语句，一般dba想到的办法是在类型、名称、CREATE_TIME字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升.

选择*
自操作
其中type=‘SQLStats’
和名称=“Slowlog”
按创建时间排序
限制1000，10；

好吧，可能90%以上的dba解决该问题就到此为止.但当限制子句变成“限制1000000，10”时，程序员仍然会抱怨:我只取10条记录为什么还是慢？

要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次.出现这种性能问题，多数情形下是程序员偷懒了.

在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的.sql重新设计如下：

选择*
自操作
其中type=‘SQLStats’
和名称=“Slowlog”
和创建时间‘2017-03-16：00：00’
按创建时间限制10顺序；

在新设计下查询时间基本固定，不会随着数据量的增长而发生变化.

2、隐式转换

sql语句中查询变量和字段定义类型不匹配是另一个常见的错误.比如下面的语句：

MySQL>ExtendedSELECT*
>从我的余额b
>其中b.bpn=14000000123
>和b.is验证为空；
MySQL>显示警告；
由于字段“bpn”上的类型或排序规则转换，因此不能对索引“bpn”使用ref访问

其中字段bpn的定义为varchar(20)，mysql的策略是将字符串转换为数字之后再比较.函数作用于表字段，索引失效.

上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意.现在应用框架很多很繁杂，使用方便的同时也小心它可能给自己挖坑.

3、关联更新、删除

虽然MySQL5.6引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化.对于更新或删除需要手工重写成加入。

比如下面更新语句，mysql实际执行的是循环/嵌套子查询(依赖SUBQUERY)，其执行时间可想而知。

更新操作o
设置状态=“应用”
其中o.id in(选择id)
从(选择o.id，
o.status
从O行动
其中o.group=123
状态不属于(“完成”)
家长的命令，
o.id
限制1)t)；
执行计划：

±—±-±-±-±-±-±-±-±-±
\x{e76f}\x{e76f}
±—±-±-±-±-±-±-±-±-±
使用WHERE，使用
在读取Const表后被注意到的地方，不可能被注意到的、依赖于SUBQUERY的SUBQUERY
使用WHERE，使用文件长度为3，x_5，IDX_5，Inx_5，8，Const
±—±-±-±-±-±-±-±-±-±
重写为加入之后，子查询的选择模式从依赖的SUBQUERY变成衍生，执行速度大大加快，从7秒降低到2毫秒.

更新操作o
加入(选择o.id，
o.status
从O行动
其中o.group=123
状态不属于(“完成”)
家长的命令，
o.id
极限1)t
on o.id=t.id
设置状态=“应用”

执行计划简化为：

±—±-±-±-±-±-±-±-±-±--+
\x{e76f}\x{e76f}
±—±-±-±-±-±-±-±-±-±--+
在读取Const表后不可能注意到的地方
使用WHERE，使用文件长度为2，x_5，IDX_5，Inx_5，COST，COST，1
±—±-±-±-±-±-±-±-±-±--+

4、混合排序

mysql不能利用索引进行混合排序.但在某些场景，还是有机会使用特殊方法提升性能的.

选择*
从我的订单上
内部加入我的评估a on.orderid=o.id
A.is_respond.ASC的命令，
A.评估时间
限制0，20

执行计划显示为全表扫描：

±—±-±-±-±-±-±-±-±-±+
\x{e76f}\x{e76f}
±—±-±-±-±-±-±-±-±-±+
使用
\x{e76f}\x
±—±-±-±-±-±-±-±-±-±+
由于is_Reply只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒.

选择*
来自(选择*)
从我的订单上
内部加入我的评价
on.orderid=o.id
和is_respond=0
按时间排序
0，20)
联合所有
(选择*
从我的订单上
内部加入我的评价
on.orderid=o.id
并答复=1
按时间排序
极限0，20)t
由IS_REPLY ASC命令，
评价时间DESC
限制20；

5、存在语句

Mysql对待存在子句时，仍然采用嵌套子查询的执行方式。如下面的sql语句：

选择*
从我的邻居那里
左加入我的邻居应用SRA
关于n.id=sra.nebor_id
和sra.user_id=‘xxx’
其中n.Topic_Status<4
并存在(选择1
消息信息m
其中n.id=M-邻居_id
和m.inuser=‘xxx’)
和n.title_type<>5

执行计划为：

±—±-±-±-±-±-±-±-±-+-+
\x{e76f}\x{e76f}
±-±
使用WHERE_
\x{e76f}\x{e76f}\
使用索引条件下的2维依赖于SUBQUERY的SUBQUERY函数，使用索引条件；使用WHERE
±-±
去掉存在更改为连接，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。

选择*
从我的邻居那里
内部连接消息_INFO m
关于n.id=M-邻居id
和m.inuser=‘xxx’
左加入我的邻居应用SRA
关于n.id=sra.nebor_id
和sra.user_id=‘xxx’
其中n.Topic_Status<4
和n.title_type<>5

新的执行计划：

±-±
\x{e76f}\x{e76f}
±-±
\x{e76f}\x{e76f}\
\x{e76f}\x
\x{e76f}\x
±-±

6、条件下推

外部查询条件不能够下推到复杂的视图或子查询的情况有：

聚合子查询；

含有极限的子查询；

联合或联合所有子查询；

输出字段中的子查询；

如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：

选择*
从(选择目标)，
计数(*)
自操作
按目标分组)t
其中Target=‘rm-xxxx’

执行计划如下：
±—±-±-±-±-±-±-±-±-±-+
\x{e76f}\x{e76f}
±—±-±-±-±-±-±-±-±-±-+
使用WHERE\x
使用INDEX\x={##**$}{##**$
±—±-±-±-±-±-±-±-±-±-+
确定从语义上查询条件可以直接下推后，重写如下：

选择目标，
计数(*)
自操作
其中Target=‘rm-xxxx’
按目标分组

执行计划变为：

±—±-±-±-±-±-±-±-±-±-+
\x{e76f}\x{e76f}
±—±-±-±-±-±-±-±-±-±-+
使用WHERE、使用INDEX_4、使用INDEX_4、
±—±-±-±-±-±-±-±-±-±-+
关于mysql外部条件不能下推的详细解释说明请参考文章：

http://mysql.taobao.org/monthly/2016/07/08

7、提前缩小范围

先上初始sql语句：

选择*
从我的订单上
左加入我的userinfo u
关于o.uid=uuid
左加入我的产品信息p
关于o.pid=p.pid
其中(o.display=0)
和(o.ostaus=1)
由o.selltime DESC订购
限制0，15

该sql语句原意是:先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒.

±—±-±-±-±-±-±-±-±-±
\x{e76f}\x{e76f}
±—±-±-±-±-±-±-±-±-±
使用WHERE；使用临时；使用文件
*
使用WHERE，使用JOIN缓冲区(块嵌套循环)
±—±-±-±-±-±-±-±-±-±

由于最后条件以及排序均针对最左主表，因此可以先对_条件以及排序均针对最左主表，因此可以先对_Order排序提前缩小数据量再做左连接。SQL重写后如下，执行时间缩小为1毫秒左右.

选择*
来自(
选择*
从我的订单上
其中(o.display=0)
和(o.ostaus=1)
由o.selltime DESC订购
限制0，15
)o
左加入我的userinfo u
关于o.uid=uuid
左加入我的产品信息p
关于o.pid=p.pid
由o.selltime DESC订购
限制0，15

再检查执行计划:子查询物化后(SELECT_TYPE=派生)参与联接。虽然估算行扫描仍然为90万，但是利用了索引以及限制子句后，实际执行时间变得很小。

±—±-±-±-±-±-±-±-±-±
\x{e76f}\x{e76f}
±—±-±-±-±-±-±-±-±-±
使用临时方法，使用文件长度为1；使用文件.
*
使用WHERE；使用联接缓冲区(块嵌套循环)
\x=2，x，x
±—±-±-±-±-±-±-±-±-±

8、中间结果集下推

再来看下面这个已经初步优化过的例子(左连接中的主表优先作用查询条件)：

选择a.*，
c.allocated
来自(
选择资源
从我的分发d
其中isdelete=0
和座垫代码=‘1234567’
按销售限制订购20)a
左联接
(
选择Resourcesid，sum(ifnull(分配，0)*12345)已分配
从我的资源
按资源分组d)c
关于a.Resoureid=c.Resourcesid

那么该语句还存在其它问题吗？不难看出子查询c是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降.

其实对于子查询c.，左连接最后结果集只关心能和主表Resoureid能匹配的数据.因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒.

选择a.*，
c.allocated
来自(
选择资源
从我的分发d
其中isdelete=0
和座垫代码=‘1234567’
按销售限制订购20)a
左联接
(
选择Resourcesid，sum(ifnull(分配，0)*12345)已分配
从我的资源里，
(
选择资源
从我的分发d
其中isdelete=0
和座垫代码=‘1234567’
按销售限制订购20)a
其中r.Resourcesid=a.Resourcesid
按资源分组d)c
关于a.Resoureid=c.Resourcesid

但是子查询a在我们的sql语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂.使用与语句再次重写：

带着AS
(
选择资源
从我的分发d
其中isdelete=0
和座垫代码=‘1234567’
按销售限制订购20)
选择a.*，
c.allocated
从
左联接
(
选择Resourcesid，sum(ifnull(分配，0)*12345)已分配
从我的资源里，
a
其中r.Resourcesid=a.Resourcesid
按资源分组d)c
关于a.Resoureid=c.Resourcesid