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