1. LIMIT语句
分页查询是最常用的场景之一，但通常也是最容易出现问题的地方。 例如，对于下面的简单语句，DBA通常会想到添加类型、名称、字段的组合索引。 这样，条件排序就可以有效利用索引，性能可以得到快速提升。
也许90%以上的DBA解决这个问题就到此为止了。 但当 LIMIT 子句变成“LIMIT,10”时，程序员仍然会抱怨：为什么我只取 10 条记录仍然很慢？
您必须知道数据库不知道第一条记录从哪里开始。 即使有索引，也需要从头开始计算。 当出现这种性能问题时，大多数情况下程序员都是偷懒的。
在前端数据浏览翻页，或者大数据批量导出等场景下，可以将上一页的最大值作为参数作为查询条件。
SQL重新设计如下：
新的设计下，查询时间基本固定，不会随着数据量的增加而改变。
2.隐式转换
SQL 语句中查询变量的类型和字段定义不匹配是另一个常见错误。 例如，以下语句：
字段bpn定义为(20)，MySQL的策略是先将字符串转换为数字再进行比较。 该函数作用于表字段，索引失效。
上述情况可能是应用框架自动填写的参数，而不是程序员的本意。 现在有很多应用程序框架，而且非常复杂。 虽然它们很容易使用，但要小心它们可能会为您挖坑。
3.关联更新与删除
虽然.6引入了物化功能，但需要注意的是，它目前仅针对优化查询语句。 对于更新或删除，需要手动重写为JOIN。
例如，在下面的语句中，MySQL实际上执行了一个循环/嵌套子查询（ ），其执行时间可想而知。
实施计划：
重写为JOIN后，子查询的选择方式由
执行计划简化为：
4. 混合排序
MySQL 不能使用索引进行混合排序。 但在某些场景下，仍然有机会使用特殊方法来提高性能。
执行计划显示全表扫描：
由于只有两种状态：0和1，我们按照下面的方法重写后，执行时间从1.58秒减少到2毫秒。
5. 声明
MySQL在处理子句时，仍然采用嵌套子查询的执行方式。 比如下面的SQL语句：
执行计划是：
删除对 join 的更改可以避免嵌套子查询，并将执行时间从 1.93 秒减少到 1 毫秒。
新的执行计划：
6. 条件下推
外部查询条件无法下推到复杂视图或子查询的情况包括：
聚合子查询；
包含 LIMIT 的子查询；
UNION 或 UNION ALL 子查询；
输出字段中的子查询；
如下语句所示，从执行计划可以看出，其条件是在聚合子查询之后应用的
确认查询条件可以直接语义下推后，改写如下：
执行计划变为：
7.提前缩小范围
首先是初始的SQL语句：
数量为90万，耗时12秒。
由于最终的WHERE条件和排序都是针对最左边的主表，所以可以提前减少排序的数据量，然后进行左连接。 SQL改写如下后，执行时间减少到1毫秒左右。
再次检查执行计划：子查询物化后（=）参与JOIN。
虽然预计行扫描仍然是 900,000，但使用索引和 LIMIT 子句后，实际执行时间变得非常小。
8. 中间结果集下推
我们看下面这个初步优化过的例子（左连接中主表优先于查询条件）：
那么这个说法还有其他问题吗？ 不难看出，子查询c是全表聚合查询，当表数量特别多时，会导致整个语句的性能下降。
事实上，对于子查询c来说，左连接的最终结果集只关心能与主表匹配的数据。 因此我们可以将语句改写如下，执行时间从原来的2秒下降到2毫秒。
但是子查询a在我们的SQL语句中出现了多次。 这种写法不仅会带来额外的开销，而且会让整个语句变得复杂。 使用WITH语句再次重写：
总结
数据库编译器生成执行计划，该执行计划决定SQL实际如何执行。 但编译器只是尽力服务，所有数据库编译器都不是完美的。
上面提到的大多数场景在其他数据库中也存在性能问题。 只有了解数据库编译器的特点，才能避免其缺点，编写出高性能的SQL语句。
程序员在设计数据模型和编写SQL语句时必须引入算法思想或意识。
在编写复杂的SQL语句时，一定要养成使用WITH语句的习惯。 简单清晰的SQL语句也可以减轻数据库的负担。
文章首次发表：
更多文章请关注本公众号，与小编畅聊前沿IT技术，共同学习交流，共同提高进步！ 最后，感谢您的支持！
