服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - SQL调优之Explain关键字详解

SQL调优之Explain关键字详解

2023-11-11 01:01未知服务器之家 Mysql

explain关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句:explain + SQL语句。表头信息如下: explain各个字段代表的意思 id :select查询的序列号,包含一组数

SQL调优之Explain关键字详解

explain关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句:explain + SQL语句。表头信息如下:

SQL调优之Explain关键字详解

explain各个字段代表的意思

  • id :select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
  • select_type :查询类型 或者是 其他操作类型。
  • table :正在访问哪个表。
  • partitions :匹配的分区。
  • type :访问的类型。
  • possible_keys :显示可能应用在这张表中的索引,一个或多个,但不一定实际使用到。
  • key :实际使用到的索引,如果为NULL,则没有使用索引。
  • key_len :表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。
  • ref :显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值。
  • rows :根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数。
  • filtered :查询的表行占表的百分比。
  • Extra :包含不适合在其它列中显示但十分重要的额外信息。

ID 字段

select 查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序。三种情况:

id 相同: 执行顺序由上而下:

explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';

SQL调优之Explain关键字详解

id 不同: 如果是子查询,id 序号会递增,id 越大优先级越高,越先被执行。

explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));

SQL调优之Explain关键字详解

id 相同不同同时存在: id 如果相同,可以认为是一组,由上往下执行;在所有组里 id 越大,优先级越高,越先执行。

explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id;

SQL调优之Explain关键字详解

select_type 字段

主要用于区别普通查询、联合查询、子查询等的复杂程度。
SIMPLE: 简单的 select 查询,查询中不包含子查询或者 UNION。
PRIMARY: 查询中若包含任何复杂的自查询,最外层查询为 PRIMARY。

SQL调优之Explain关键字详解

SUBQUERY: 在 SELECT 或 WHERE 中包含子查询。
UNCACHEABLE SUBQUERY:一个子查询的结果不能被缓存,必须重新评估外链接的第一行。
DERIVED: 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)MySQL 会递归执行这些子查询,把结果放进临时表。

SQL调优之Explain关键字详解

UNION: 若第二个 SELECT 出现在 UNION 之后,则被标记为 UNION,若 UNION 包含在 FROM 子句的子查询,则外层SELECT 将被标记为 DERIVED。
UNION RESULT: 从 UNION表中获取结果的 SELECT。

table字段

显示这行数据是关于那张表

SQL调优之Explain关键字详解

type字段

首先说一下这个字段,要记住以下10个状态,(从左往右,越靠左边的越优秀)

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

NULL

MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。存在这样一种情况,大家都知道索引是将数据在B+Tree中进行排序了,所以你的查询速率才这么高,那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊?既然你都知道了,那MySQL比你更知道啊!当你要查询最大值或者最小值时,MySQL会直接到你的索引得分叶子节点上直接拿,所以不用访问表或者索引。

NULL的前提是你已经建立了索引。

SQL调优之Explain关键字详解

SYSTEM

表只有一行记录(等于系统表),这是const类型的特列,平时不大会出现,可以忽略。

const

表示通过索引一次就能找到,const用于比较 primary和 unique索引。因为只匹配一行数据,所以很快。

简单来说,const是直接按主键或唯一键读取。

SQL调优之Explain关键字详解

eq_ref

用于联表查询的情况,按联表的主键或唯一键联合查询。多表join时,对于来自前面表的每一行,在当前表中只能找到一行。这可能是除了system和const之外最好的类型。

SQL调优之Explain关键字详解

ref 可以用于单表扫描或者连接。如果是连接的话,驱动表的一条记录能够在被驱动表中通过非唯一(主键)属性所在索引中匹配多行数据,或者是在单表查询的时候通过非唯一(主键)属性所在索引中查到一行数据。

ref_or_null 类似ref,但是可以搜索值为NULL的行。

SQL调优之Explain关键字详解

index_merge

表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取多个索引,性能可能大部分时间都不如range。

SQL调优之Explain关键字详解

range

只检索给定范围的行,使用一个索引来选择行。一般where语句中出现between、<、>、in等的查询。这种范围扫描索引比全表扫描要好,因为只需开始索引的某一点,而结束另一点,不用扫描全部索引;

SQL调优之Explain关键字详解

index

Full Index Scan,index与 ALL区别为 index类型只遍历索引树,索引文件通常比数据文件小。index从索引中读取,而All是从硬盘读取;

SQL调优之Explain关键字详解

ALL

从磁盘中读取;如果一个查询的type是All,并且表的数据量很大,那么请解决它!!!

possible_keys字段

这个表里面存在且可能会被使用的索引,可能会在这个字段下面出现,但是一般都以key为准。

key字段

实际使用的索引,如果为null,则没有使用索引,否则会显示你使用了哪些索引,查询中若使用了覆盖索引(查询的列刚好是索引),则该索引仅出现在key列表。

ref字段

列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。

rows字段和Filter字段

rows是根据表的统计信息和索引的选用情况,优化器大概帮你估算出你执行这行函数所需要查询的行数。

Filter是查询的行数与总行数的比值。其实作用与rows差不多,都是数值越小,效率越高。

Extra字段

Using index

表示相应的 select操作中使用了覆盖索引(convering index),避免访问了表的数据行,效率不错!

SQL调优之Explain关键字详解

覆盖索引(covering index ,或称为索引覆盖)即从非主键索引中就能查到的记录,而不需要查询主键索引中的记录,避免了回表的产生减少了树的搜索次数,显著提升性能。

Using temporary

表示MySQL需要使用临时表来存储结果集,常见于排序 order by和分组查询 group by。

Using fileSort

表示当SQL中有一个地方需要对一些数据进行排序的时候,优化器找不到能够使用的索引,所以只能使用外部的索引排序。

MySQL无法利用索引完成的排序操作称为“文件排序”。导致该问题的原因一般是Where条件和order by子句作用在了不同的列上,一般可以通过合适的索引来减少或者避免。(出现表示不好)

上面提到的常见情况,SQL语句通常写成这样select * from a where type = 5 order by id,这类语句一般会产生Using filesort这个选项,即使你在type和id上分别添加了索引。我们想一下它的工作过程,先根据type的索引从所有数据信息中挑选出满足type = 5条件的,然后根据id列的索引信息对挑选的数据进行排序,所以产生了Using filesort选项。可以通过联合索引解决这个问题,即在type, id两列上建立一个联合索引。

Using where

查询的列未被索引覆盖,where筛选条件非索引的前导列,Extra 中为 Using where。

SQL调优之Explain关键字详解


所谓前导列,就是在创建复合索引语句的第一列或者连续的多列。比如通过:CREATE INDEX comp_ind ON table1(x, y, z)创建索引,那么x,xy,xyz都是前导列,而yz,y,z这样的就不是。

using where,using index

查询的列被索引覆盖,并且 where筛选条件是索引列之一但是不是索引的前导列,Extra中为Using where; Using index,意味着无法直接通过索引查找来查询到符合条件的数据;

SQL调优之Explain关键字详解

查询的列被索引覆盖,并且where筛选条件是索引列前导列的一个范围,同样意味着无法直接通过索引查询到符合条件的数据

SQL调优之Explain关键字详解

Using index condition

查询的列不全在索引中,where条件中是一个前导列的范围

SQL调优之Explain关键字详解

查询列不完全被索引覆盖,查询条件完全可以使用到索引(进行索引查找)

SQL调优之Explain关键字详解

NULL(既没有Using index,也没有Using where Using index,也没有using where)。

查询的列未被索引覆盖,并且where筛选条件是索引的前导列,意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表”来实现,不是纯粹地用到了索引,也不是完全没用到索引,Extra中为NULL(没有信息)。

SQL调优之Explain关键字详解

Using join buffer

使用了连接缓存。

小表驱动大表

在表连接过程中。一般选择小表作为驱动表,大表作为被驱动表。

驱动表(小表)的连接字段无论建立没建立索引都需要全表扫描的。被驱动表(大表)如果在连接字段建立了索引,则可以走索引。如果没有建立索引则也需要全表扫描。


两张表连接的情况

被驱动表的连接字段有索引:主键索引

对于驱动表中的每一条数据,到被驱动表的聚簇索引上寻找其对应的数据。

被驱动表的连接字段有索引:二级索引

对于驱动表上的每一条数据,到被驱动表的二次索引上寻找其对应的数据id,然后再根据数据id到聚簇索引上寻找对应的数据。

被驱动表的连接字段没有索引

对于驱动表上的每一条数据,都要到被驱动表上进行一次全表遍历,找到对应的数据。

join buffer的作用

就是针对被驱动表的连接字段没有索引的情况下需要进行全表扫描,所以引入了join buffer内存缓冲区来对这个全表扫描过程进行优化。

impossible where

where子句总是false,不能用来获取任何元素。即筛选条件没能筛选出任何数据。

select tables optimized away

在没有 GROUPBY 子句的情况下,基于索引优化 MIN/MAX操作。

distinct

优化 distinct操作。在找到第一匹配的时候就停止找同样的动作。

延伸 · 阅读

精彩推荐