MySQL优化-explain

最新推荐文章于 2023-08-15 00:42:53 发布

pc彭诚诚的博客

最新推荐文章于 2023-08-15 00:42:53 发布

阅读量176

点赞数

分类专栏： mysql java基础 java

本文链接：https://blog.youkuaiyun.com/pc915968220/article/details/102703599

版权

java 同时被 3 个专栏收录

45 篇文章

订阅专栏

java基础

31 篇文章

订阅专栏

mysql

11 篇文章

订阅专栏

explain

explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用

-- 实际SQL，查找用户名为admin的员工
SELECT * FROM `sys_user` WHERE login_name='admin';
-- 查看SQL是否使用索引，前面加上explain即可
EXPLAIN  SELECT * FROM `sys_user` WHERE login_name='admin';

在这里插入图片描述

类目	描述
id	SELECT识别符,是SELECT的查询序列号. 我的理解是SQL执行的顺序的标识，SQL从大到小的执行 1. id相同时，执行顺序由上至下. 2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
select_type	表示查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询 (1) SIMPLE(简单SELECT，不使用UNION或子查询等) (2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY) (3) UNION(UNION中的第二个或后面的SELECT语句) (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询) (5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select) (6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询) (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询) (8) DERIVED(派生表的SELECT, FROM子句的子查询) (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table	输出的行所引用的表
type	type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种（从左到右，性能从差到好） 1.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行 2.index: Full Index Scan，index与ALL区别为index类型只遍历索引树,这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的） 3.range:只检索给定范围的行，使用一个索引来选择行 4.ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 5.eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件 6.const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system 7.NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
possible_keys	指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
key	key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
ref	显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows	估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
Extra	1. Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。 2.Using temporary：使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3.Using index: 表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 4.Using where: 表明使用了where过滤 5.Using join buffer: 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些 Impossible where: where子句的值总是false，不能用来获取任何元组 6.Select tables optimized away: 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 7.Distinct: 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作 8.Not exists: MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

SQL

想要优化SQL，必须清楚知道SQL的执行顺序，这样再配合explain才能事半功倍

select distinct 
        <select_list>
from
    <left_table><join_type>
join <right_table> on <join_condition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>

SQL执行顺序

1、from <left_table><join_type>
2、on <join_condition>
3、<join_type> join <right_table>
4、where <where_condition>
5、group by <group_by_list>
6、having <having_condition>
7、select
8、distinct <select_list>
9、order by <order_by_condition>
10、limit <limit_number>

在这里插入图片描述

extend
extended关键字：仅对select语句有效，在explain后使用extended关键字，可以显示filtered列显示了通过条件过滤出的行数的百分比估计值。
也可以通过show warnings显示扩展信息，输出中的 Message值SHOW WARNINGS显示优化程序如何限定SELECT语句中的表名和列名，
SELECT应用重写和优化规则后的外观，以及可能有关优化过程的其他说明。