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外部条件不能下推的详细解释说明请参考文章：
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