1.LIMIT语句分页查询是最常用的场景之一，但通常也是最容易出问题的地方。比如下面这个简单的语句，一般的DBA都会想到在type、name、create_time字段上加一个复合索引。这样，条件排序就可以有效地利用索引，性能得到快速提升。好吧，也许超过90%的DBA解决了这个问题就到此为止了。但是当LIMIT子句变成“LIMIT1000000,10”时，程序员还是会抱怨：为什么我只取10条记录还是慢？要知道数据库并不知道第1000000条记录从哪里开始，即使有索引也需要从头开始计算。出现这种性能问题，多数情况下是程序员偷懒了。在前端数据浏览翻页，或者大数据批量导出等场景下，可以将上一页的最大值作为参数作为查询条件。SQL重新设计如下：在新的设计下，查询时间基本固定，不会随着数据量的增长而变化。2、隐式转换SQL语句中查询变量和字段定义的类型不匹配是另一个常见的错误。例如下面的语句：其中字段bpn定义为varchar(20)，MySQL的策略是先将字符串转换为数字再进行比较。函数作用于表字段，索引无效。以上情况可能是应用程序框架自动填充的参数，并非程序员的本意。现在有很多非常复杂的应用程序框架。虽然使用起来很方便，但要小心它可能给自己挖坑。3、关联更新删除虽然MySQL5.6引入了物化特性，但需要特别注意的只是查询语句的优化。更新或删除需要手动改写为JOIN。例如，在下面的UPDATE语句中，MySQL实际上执行了一个循环/嵌套子查询（DEPENDENTSUBQUERY），其执行时间可想而知。执行计划：改写为JOIN后，子查询的选择方式由DEPENDENTSUBQUERY变为DERIVED，执行速度大大加快，从7秒缩短为2毫秒。执行计划简化为：4.混合排序MySQL不能使用索引进行混合排序。但在某些场景下，仍然有机会使用特殊的方法来提高性能。执行计划是全表扫描：由于is_reply只有0和1两种状态，我们按照下面的方法改写后，执行时间从1.58秒减少到2毫秒。5、EXISTS语句MySQL在处理EXISTS子句时，仍然采用嵌套子查询的执行方式。例如下面的SQL语句：执行计划为：removeexists，改成join，可以避免嵌套子查询，将执行时间从1.93秒减少到1毫秒。新的执行计划：6.条件下推外部查询条件不能下推到复杂视图或子查询：聚合子查询；包含LIMIT的子查询；UNION或UNIONALL子查询；输出字段查询中的子查询；如下面语句，从执行计划可以看出其条件作用在聚合子查询之后：确认查询条件可以直接语义下推后，改写如下：执行计划变为：7.缩小范围提前先上去初始SQL语句：数量90万条，耗时12秒。由于最后的WHERE条件和排序都是针对最左边的主表，所以可以先对my_order进行排序，提前减少数据量再做leftjoin。SQL改写如下，执行时间减少到1毫秒左右。再次查看执行计划：子查询物化后（select_type=DERIVED），参与JOIN。虽然估计行扫描还是90万，但是在使用了索引和LIMIT子句之后，实际执行时间变得非常少了。8、中间结果集下推，看下面这个初步优化的例子（以左连接中的主表为查询条件）：这个语句还有其他问题吗？不难看出，子查询c是对整张表的聚合查询，当表数特别多时，整个语句的性能会下降。实际上，对于子查询c，leftjoin的最终结果集只关心能匹配到主表resourceid的数据。所以我们可以改写语句如下，执行时间从2秒降到2毫秒。但是子查询a在我们的SQL语句中出现了多次。这种写法不仅有额外的开销，而且使整个语句显着复杂化。再用WITH语句重写：总结数据库编译器生成的执行计划，决定了S??QL的实际执行方式。但是编译器只是尽力而为，并不是所有的数据库编译器都是完美的。上面提到的大部分场景在其他数据库中也存在性能问题。只有了解数据库编译器的特点，才能避免其缺点，编写出高性能的SQL语句。程序员在设计数据模型和编写SQL语句时，应该带入算法的思想或意识。要编写复杂的SQL语句，请养成使用WITH语句的习惯。简洁明了的SQL语句也可以减轻数据库的负担。
