我的Mysql查询SQL优化总结
当我们遇到一个慢查询语句时，首先要做的是检查所编写的 SQL 语句是否合理，优化 SQL 语句从而提升查询效率。所以对 SQL 有一个整体的认识是有必要的。
MySQL 服务器接收到一条 SQL 语句时，其处理过程为
mysql执行过程
当然，作为一个开发，更应该关心的是 SQL 解析后的执行情况，这时还需要用到 EXPLAIN 命令，了解数据库执行 SQL 时是怎么做的。
1、SQL 执行顺序
理解 SQL 执行顺序有助于找出查询慢的原因。
以下为 MySQL SELECT 官方给出的语句格式。
SELECT
[ALL | DISTINCT | DISTINCTROW ]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[[LEFT]JOIN joined_table_references]
[ON search_condition | USING (join_column_list)]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING having_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]]
SQL 语句被解释后，按照关键字的信息开始逐步执行，每个步骤都会存在一些中间结果，称之为虚表 VT （虚表指的是一个逻辑上存在的数据集合，在实际上不一定存在）。
- FROM | JOIN : 获取 FROM 指定的表（或分区表）。如果为联表查询，将对相关表进行联表计算。产生虚表 VT₁ 。
- WHERE : 对虚表 VT₁ 根据 where_condition 进行过滤，过滤后的结果为虚表 VT₂ 。
- GROUP BY & WINDOW : 根据 GROUP BY 和 WINDOW 的子句，对 VT₂ 进行聚合统计计算，得到的结果为虚表 VT₃ 。
- WITH : 对 VT₃ 进行相关的 CUBE 或是 ROLLUP 操作。
- HAVING : 对 VT₃ 根据 having_condition 进行过滤，过滤后的结果为虚表 VT₄ 。
- SELECT : 执行 SELECT 操作，根据 select_expr 对 VT₄ 选择列，根据结果产生虚表 VT₅ 。
- ALL | DISTINCT | DISTINCTROW :对 VT₅ 进行过滤操作，ALL 可以理解为一个空操作，即什么也不做。DISTINCT 和 DISTINCTROW 将会进行去重操作。产生表虚表 VT₆ 。
- ORDER BY : 根据 ORDER BY 子句给出的信息对 VT₆ 进行排序，产生虚表 VT₇ 。
- LIMIT : 根据 LIMIT 子句给出的信息对 VT₇ 取出指定行的记录，产生虚表 VT₈ ，该结果将会返回给客户端。
清楚 SQL 的执行顺序后，接下来可以看一下在日常查询使用中，常见的拖慢查询的 SQL 使用，这些原因可以通过改写 SQL 来进行优化。
2、联表查询
过于复杂的联表查询通常是导致查询效率低下的原因。MySQL 的联表查询实现主要还是嵌套循环算法，效率实在不高，所以有很多关于数据库的优化知识都会限制 JOIN 表的数量，如《阿里巴巴 Java 开发手册》就禁止超过三个表的 JOIN 。如果确实需要多表的关联查询，可以考虑分解关联查询，在应用端进行数据的关联处理。不过分解关联查询是否提高了效率还是需要进行比较检验。
3、子查询
在 MySQL 5.6 版本后对子查询进行了优化，但是优化器的优化始终是有限的，在某些场景下子查询仍然是会称为导致查询效率低下的一个点。根据 MySQL 官方手册中的子查询优化章节，子查询的优化主要有以下三种方式：
- Semi-join : 半联接，即有左表和右表进行联接，联接结果只显示左表的结果而不显示右表
- Materialization : 物化，即使用临时表去存储子查询的查询结果
- EXISTS strategy : 使用 EXISTS 去代替子查询
- Merging : 合并查询，即合并子查询与外（父）查询，针对于派生表的一种优化方式。
根据子查询的使用方式，能够使用的优化方案也是不同的。 如果使用子查询作为查询条件（即跟在 WHERE 后边），如 WHERE IN (subquery_expr) 或者是 WHERE NOT IN (subquery_expr) ，如果是 IN (或 = ANY) 的话，Mysql能够根据实际查询来选择除 Merging 之外的三种优化方案，而 NOT IN (或 <> ANY) 只能选择 Materialization 和 EXISTS strategy 两种优化方案。 实际上 MySQL 对于子查询的优化最好方案为将其转化为联表查询，所以如果能够使用 JOIN 则尽量使用 JOIN 。 如果为使用 Materialization 或是 EXISTS strategy 优化方案，子查询的查询类型可能为 SUBQUERY 或者是 DEPENDENT SUBQUERY ，这是一种性能不好的查询方式。这时候就要去优化掉子查询的使用。
如果子查询跟在 FROM 后面，即子查询为派生表，能够使用 Materialization 或是 Merging 优化方案优化。 使用 Materialization 优化方案很好理解，将子查询的结果存储到临时表中，将该临时表作为被查询表。而 Merging 即是将子查询提上一级，成为外（父）查询。
当然也不需要将子查询视为洪水猛兽，子查询比起联表查询具有更好的可读性，在修改维护 SQL 时更加友好，而且在特定场景下可以作为一个优化的手段使用。
一般的子查询使用，常为 WHERE IN 或是派生表的使用。一般情况优化器下会帮我们转为联表查询以提高效率，兼备了可读性与效率。 而在一定场景下，派生表还可以提升查询的效率。可以通过子查询派生表实现“延迟关联”，在查询时，先通过子查询和覆盖索引快速查询构建出一个数据量较小的派生表，然后派生表再去与实际要查询的表做关联操作，可以使整体的查询执行速度会有所提升（当然并不总是这样，还需要通过实际场景和构建派生表的子查询做实际的分析、实践，因为派生表也是有成本的）。
覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
如果慢查询中存在子查询，不要想当然的去优化掉子查询，使用 EXPLAIN 确认清楚执行情况，如果子查询为 DEPENDENT SUBQUERY ，那么问题确实就出在子查询上（如果 SQL 语句为非 SELECT 的子查询，总是 DEPENDENT SUBQUERY）。而如果问题没有出在子查询上，那就是查询本身的复杂导致的，这时同样应该考虑分解关联查询。
4、分页
在范围检索数据时，没有分页是可怕的，如果数据量庞大不仅会使数据库查询很慢，还会大量消耗应用端的内存，影响到应用端的运行效率，严重的还会使应用挂掉（真事儿，当时阿里云Mysql应用飙的老高了）。所以在对数据库进行范围查询时，进行分页是很有必要的。
分页的实现基于 LIMIT 关键字的使用。LIMIT 的使用方式为 LIMIT offset num ，每次从 offset + 1 条记录开始获取 num 条记录。而当 offset 非常大时，就有可能影响到查询性能。因为 LIMIT 每次都需要查找获取到 offset + num 条记录，然后再进行记录的截取。
好在一般情况下，也没有需要翻到一千页，一万页以后，但若是硬要说有这大分页的需求，同样可以利用覆盖索引优化，即利用索引查询并且返回符合条件的列，这样可以提升大分页的查询效率。 查询结果默认将以主键排序，这时使用的是主键索引：先只 SELECT 主键列并且分页，获取到的主键值是通过覆盖索引获取的，再利用查询获取到的主键进行回表查询。如下：
SELECT * FROM t1 JOIN (SELECT id FROM t1 ORDER BY id LIMIT 100000,10) AS temp USING(id);
除此之外，还有一种分页的方式，可以称为游标翻页。在一些网站或者 APP 中，我们有时候会发现没有页数可以选择，只有下一页（不断下拉）的选项，这大概率就是利用游标实现的翻页。 假如有表 t1 ，有自增主键 id （或是其他非空可排序列），就可以利用 id 作为游标进行翻页。如下：
SELECT * FROM t1 WHERE id > ${cursor} ORDER BY id LIMIT 1000;
之后获取最大的 id 值，更新游标 cursor ，再次进行查询即可，这样就可以一页页的将整个数据表遍历，如果有遍历整个数据表的需求，这是一种很好的实现方法。
5、赘述：in、or与union all的性能对比
在开发过程中，总会遇到这种情况，比如项目中存在两个数据源，一个sql查询语句中的筛选条件是从另外一个数据库中查询获得，这时就不得不采用以上这3种方式（in/or/union all）中的一种方式。比如最近线上有一张90w+数据的库表，小明编写的sql查询语句在开发环境执行地好好的，一但部署到线上，阿里云数据库CPU飙到老高，前端请求后端一次响应时长也不忍直视。下面对这3种sql查询效率进行对比：
前提：为了方便查询，查询字段索引存在的情况
in、or与union all的性能对比
对于某大表的查询
使用or：
SELECT * FROM article
WHERE article_category=2
OR article_category=3
// 执行时间：11.0777
使用 in：
SELECT * FROM article
WHERE article_category IN (2,3)
// 执行时间：11.2850
使用union all：
SELECT * FROM article
WHERE article_category=2
UNION ALL
SELECT * FROM article
WHERE article_category=3
// 执行时间：0.0261
显而易见，性能上union all的方式完胜， 以上主要针对的是单表，而多表联合查询来说，考虑的地方就比较多了，比如连接方式，查询表数据量分布、索引等，再结合单表的策略选择合适的关键字。 但不管哪种情况都不要迷信union all 就比 or及in 快，要结合实际情况分析到底使用哪种情况。