体育视频网站建设,最新百度快速排名技术,大学生网站建设实训报告,地方网站推广explain执行计划出现以下情况#xff0c;均需要优化#xff1a;
一、Using temporary
查询执行过程中出现Using temporary提示#xff0c;通常意味着MySQL需要创建一个临时表来存储中间结果。这种情况多发生在数据库优化器无法通过现有的索引直接有效地执行查询时#xf…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:
创建合适的索引尽可能为GROUP BY、ORDER BY、DISTINCT涉及的列创建索引。确保索引覆盖到查询中所有使用的列或表达式尤其是GROUP BY及相关表达式的字段。优化查询避免在GROUP BY或DISTINCT中使用复杂表达式尽量简化查询逻辑。使用覆盖索引建立多列索引时确保按照查询中最常涉及到的字段顺序排列以达到覆盖索引的效果减少数据读取。数据分析与索引维护定期分析数据分布和查询模式调整索引结构确保索引对常见查询模式最优化。检查查询的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 categoryid1 order by buytimes; order by后面的字段是缺少了最左边的category_id所以会产生 using filesort explain select id from course where categoryid1 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 nullgroup 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) ) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4; 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.271s29.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_id0 and sc.score100
再次执行上述查询语句时间为: 0.088s
EXPLAIN SELECT s.* from Student s INNER JOIN SC sc on sc.s_id s.id where sc.c_id0 and sc.score100 再看执行计划 发现一个ALLextra字段中显示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_id0 and sc.score100 再次执行上述查询语句时间为: 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(由于数据量有限效果不明显数据大的时候效率更高)。
