MySql调优（三）Query SQL优化（2）explain优化

explain执行计划出现以下情况，均需要优化：

一、Using temporary

查询执行过程中出现Using temporary提示，通常意味着MySQL需要创建一个临时表来存储中间结果。这种情况多发生在数据库优化器无法通过现有的索引直接有效地执行查询时，尤其在复杂的查询条件下。以下是几种索引设计或查询场景可能导致Using temporary出现的情况：

1、复杂的查询条件，尤其是GROUP BY语句

在使用GROUP BY时，如果查询涉及到复杂的表达式，或者GROUP BY列没有适当的索引，MySQL可能需要创建一个临时表来聚集数据。例如，当GROUP BY依据的字段在索引中不是第一个字段时，可能会触发临时表的使用。

2、DISTINCT

在执行DISTINCT操作时，如果涉及到的列没有相应的索引，MySQL会创建临时表来存储结果并执行去除重复的处理，这也会触发Using temporary警告。

3、某些函数和表达式的使用

当查询中包含COUNT(DISTINCT ...), MIN(...), MAX(...)等聚合函数时，如果没有适当的索引，可能会触发临时表的使用。特别是当这些函数涉及到表达式计算或不被索引覆盖的列时。

4、ALL, ANY, SOME子查询

在进行这些子查询操作时，如果子查询中的求最大值或最小值等操作无法用索引覆盖，MySQL可能会创建临时表来辅助运算。

5、UNION

6、多列的排序或分组

当在ORDER BY 或者 GROUP BY 后的列不是一个单一列，而是一个表达式，或者列没有合适的索引时，MySQL可能使用临时表存储中间结果，再对临时表进行排序或分组。

7、表连接中ORDER BY的列不在驱动表中的

如何避免using temporary:

1. 创建合适的索引：尽可能为GROUP BY、ORDER BY、DISTINCT涉及的列创建索引。确保索引覆盖到查询中所有使用的列或表达式，尤其是GROUP BY及相关表达式的字段。
2. 优化查询：避免在GROUP BY或DISTINCT中使用复杂表达式，尽量简化查询逻辑。
3. 使用覆盖索引：建立多列索引时，确保按照查询中最常涉及到的字段顺序排列，以达到覆盖索引的效果，减少数据读取。
4. 数据分析与索引维护：定期分析数据分布和查询模式，调整索引结构，确保索引对常见查询模式最优化。
5. 检查查询的WHERE条件和JOIN语句：确保任何用于过滤或连接的列都是高效的，并且有适当的索引支持。

二、Using filesort

Using filesort是MySQL执行计划Extra字段中的一个重要值，表示查询出所有数据再进行排序。此排序方式为文件排序，没有走索引排序using index.
一般来说，执行计划中如果Extra字段中值为Using filesort时，那么type字段(查询类型)一般为index或ALL。（两者都是查询所有数据，index与ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）。order by和group by都可能产生Using filesort。

2.1、order by 引起的Using filesort

demo：

create index idx_cate_buy on course
(
category_id
,
buy_times
);

以下情况会出现Using filesort：

（1）索引顺序不符合最左前缀原则：

explain select id from course where categoryid>1 order by buytimes;

order by后面的字段是缺少了最左边的category_id，所以会产生 using filesort

explain select id from course where categoryid>1 order by buytimes,category_id;

order by后面的字段顺序不符合组合索引中的顺序，所以order by后面的不会走索引，即会产生using filesort

explain select id from course order by buytimes desc,categoryid asc;

根据最最左前缀原则，order by后面的字段顺序和索引中的不符合，则会产生using filesort

（2）同时使用了desc和asc

explain select id from course order by categoryid desc,buytimes asc;

这一条虽然order by后面的字段和索引中字段顺序相同，但是一个是降序，一个是升序，所以也会产生using filesort，同时升序和同时降序就不会产生using filesort了。

2.2、group by引起的Using filesort

默认情况下，mysql在使用group by之后，会产生临时表，而后进行排序（此处排序默认是快排），这会消耗大量的性能。group by本质是先分组后排序【而不是先排序后分组】。

如果使用 group by 语句的时候避免出现 filesort, 那么只需在其后追加 order by null 语句即可, 即：group by ... order by null，group by column 默认会按照column分组, 然后根据column升序排列;  group by column order by null 则默认按照column分组,然后根据标的主键ID升序排列。

3、rows过多

或者几乎是全表的记录数

4、key 是 (NULL)

5、possible_keys 出现过多（待选）索引

四、demo

1、单表查询举例：

1.1、准备工作：如现有表：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `year` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

2、查询：

下面查看此查询语句的执行计划：

3、给name加上索引：

ALTER TABLE `test`
ADD INDEX `index_name` (`name`) ;

再看下执行计划：

 只扫描了一行，性能比较高。

2、连接查询举例：

课程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10))
数据200条-------------------------

学生表:
create table Student(
id int PRIMARY KEY,
name varchar(10))
数据80000条-----------------------

学生成绩表SC
CREATE table SC(
    sc_id int PRIMARY KEY,
    s_id int,
    c_id int,
    score int)
数据80w条--------------------------

select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

执行时间：30248.271s（29.648s）
为什么这么慢，先来查看下查询计划：
EXPLAIN  select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

发现没有用到索引，type全是ALL，先给sc表的c_id和score建个索引：
CREATE index sc_c_id_index on SC(c_id);     CREATE index sc_score_index on SC(score);

再次执行上述查询语句，时间为: 1.054s(2.428s)。

但是2s的时间还是太长了，还能进行优化吗，仔细看执行计划：

 接下来再次优化：这次我们用连接查询！（先删除索引）
alter table SC drop index sc_c_id_index;
alter table SC drop index sc_score_index;
SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100

再次执行上述查询语句，时间为: 0.088s

EXPLAIN SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
再看执行计划：

发现一个ALL，extra字段中显示where
所以我们尝试加索引：
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

再次执行上条sql：
SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
再次执行上述查询语句，时间为: 0.010s

再看sql执行计划：

总结：
1.mysql嵌套子查询效率确实比较低
2.可以将其优化成连接查询
3.连接表时，可以先用where条件对表进行过滤，然后做表连接（虽然mysql会对连表语句做优化）
4.建立合适的索引，必要时建立多列联合索引
5.当然我们最主要的是要学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要 

6、索引优化：上面讲到子查询的优化，以及如何建立索引，而且在多个字段索引时，分别对字段建立了单个索引，后面发现其实建立联合索引效率会更高，尤其是在数据量较大，单个列区分度不高的情况下。create index sc_c_id_score_index on SC(c_id,score);时间为: 0.008s(由于数据量有限，效果不明显，数据大的时候效率更高)。