精华内容
下载资源
问答
  • 2022-04-08 09:50:46

    在要执行的sql前面加explain可以显示其sql的执行计划,由此可得知该sql执行了那些操作、执行操作的开销、是否使用索引等,下面将介绍执行计划的参数解读。

    先在sql前面加explain,执行就会出现以下内容:

    列说明:

    id列:执行算子的编号(id相同,从上执行到下;id不同,id大的先执行,如上面图的id都不同,所以执行顺序为6、5、4、3、2、1)

    operation列:执行算子的名称

    E-rows列:算子估计的输出行数

    E-memory列:算子估算的内存使用量

    E-width列:算子输出元组(表的一行数据就是一个元组)估计的宽度

    E-costs列:算子执行的估计代价(包括它所有子节点的开销)

    是否用索引说明:

    Seq Scan:没有走索引,全表顺序扫描。如上图条件只有currency_type,没有走索引,所以是全表扫描

    Index Scan:查询走索引,正常走索引如下所示:

    Bitmap Index Scan代表使用的是位图索引

     

    更多相关内容
  • MYSQL explain 执行计划

    2020-12-15 14:58:08
    使用方法,在select语句前加上explain就可以了: 如:explain select * from test1 EXPLAIN列的解释: table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型...
  • MySQL的EXPLAIN执行计划

    2017-06-14 11:41:26
    MySQL的EXPLAIN执行计划.pdf
  • 执行效果4.1. 命令行4.2. 可视化工具–`HeidiSQL Portable 9.4`五. Explain 字段详解5.1. 一览全局字段5.2. Id字段5.2.1. id相同5.2.2. id不相同5.2.3. id相同,又不相同5.3. `select_type`字段5.3.1.类型如下5.3.2....
  • 目录一、explain执行计划使用的前提条件二、explain执行计划的语法三、explain执行计划的示例3.1、数据准备3.2、执行explain执行计划指定queryPlanner参数3.3、执行explain执行计划指定executionStats参数3.4、执行...

    一、explain执行计划使用的前提条件

    • 查询是否使用了索引
    • 索引是否减少了扫描的记录数量
    • 是否存在低效的内存排序

    二、explain执行计划的语法

    • explain()方法的形式

      db.collection.find().explain(<verbose>)
      
    • verbose 可选参数
      表示执行计划的输出模式,默认queryPlanner

      模式名字描述
      queryPlanner执行计划的详细信息,包括查询计划、集合信息、查询条件、最佳执行计划、查询方式和 MongoDB 服务信息等
      exectionStats最佳执行计划的执行情况和被拒绝的计划等信息
      allPlansExecution选择并执行最佳执行计划,并返回最佳执行计划和其他执行计划的执行情况

    三、explain执行计划的示例

    3.1、数据准备

    • 准备数据集,执行脚本

      var tags = ["nosql","mongodb","document","developer","popular"];
      var types = ["technology","sociality","travel","novel","literature"];
      var books=[];
      for(var i=0;i<50;i++){
      	var typeIdx = Math.floor(Math.random()*types.length);
      	var tagIdx = Math.floor(Math.random()*tags.length);
      	var tagIdx2 = Math.floor(Math.random()*tags.length);
      	var favCount = Math.floor(Math.random()*100);
      	var username = "xx00"+Math.floor(Math.random()*10);
      	var age = 20 + Math.floor(Math.random()*15);
      	var book = {
      		title: "book-"+i, 
      		type: types[typeIdx],
      		tag: [tags[tagIdx],tags[tagIdx2]],
      		favCount: favCount, 
      		author: {name:username,age:age}
      	};
      	books.push(book)
      }
      db.books1.insertMany(books);
      

      在这里插入图片描述

    • 查看初始化的数据

      db.books1.find()
      

      在这里插入图片描述

    3.2、执行explain执行计划指定queryPlanner参数

    • 未创建title的索引,执行explain指定queryPlanner参数

      db.books1.find({title:"book-1"}).explain("queryPlanner")
      

      在这里插入图片描述

    • explain执行中字段的解释

      字段名称描述
      plannerVersion执行计划的版本
      namespace查询的集合
      indexFilterSet是否使用索引
      parsedQuery查询条件
      winningPlan最佳执行计划
      stage查询方式
      filter过滤条件
      direction查询顺序
      rejectedPlans拒绝的执行计划
      serverInfomongodb服务器信息

    3.3、执行explain执行计划指定executionStats参数

    • executionStats 模式的返回信息中包含了 queryPlanner 模式的所有字段,并且还包含了最佳执行计划的执行情况

    • 创建title索引

      db.books1.createIndex({title:1})
      

      在这里插入图片描述

    • 执行explain指定executionStats参数

      db.books1.find({title:"book-1"}).explain("executionStats")
      

      在这里插入图片描述
      在这里插入图片描述
      在这里插入图片描述

    • explain执行中字段的解释

      字段名称描述
      winningPlan.inputStage用来描述子stage,并且为其父stage提供文档和索引关键字
      winningPlan.inputStage.stage子查询方式
      winningPlan.inputStage.keyPattern所扫描的index内容
      winningPlan.inputStage.indexName索引名
      winningPlan.inputStage.isMultiKey是否是Multikey。如果索引建立在array上,将是true
      executionStats.executionSuccess是否执行成功
      executionStats.nReturned返回的个数
      executionStats.executionTimeMillis这条语句执行时间
      executionStats.executionStages.executionTimeMillisEstimate检索文档获取数据的时间
      executionStats.executionStages.inputStage.executionTimeMillisEstimate扫描获取数据的时间
      executionStats.totalKeysExamined索引扫描次数
      executionStats.executionStages.isEOF是否到达 steam 结尾,1 或者 true 代表已到达结尾
      executionStats.executionStages.works工作单元数,一个查询会分解成小的工作单元
      executionStats.executionStages.advanced优先返回的结果数
      executionStats.executionStages.docsExamined文档检查数

    3.4、执行explain执行计划指定allPlansExecution参数

    • allPlansExecution返回的信息包含 executionStats 模式的内容,且包含allPlansExecution:[]块

      "allPlansExecution" : [
        {
          "nReturned" : <int>,
          "executionTimeMillisEstimate" : <int>,
          "totalKeysExamined" : <int>,
          "totalDocsExamined" :<int>,
          "executionStages" : {
           	"stage" : <STAGEA>,
           	"nReturned" : <int>,
           	"executionTimeMillisEstimate" : <int>,
           	...
           	}
          }
        },
        ...
      ]
      

    3.5、 stage状态

    状态描述
    COLLSCAN全表扫描
    IXSCAN索引扫描
    FETCH根据索引检索指定文档
    SHARD_MERGE将各个分片返回数据进行合并
    SORT在内存中进行了排序
    LIMIT使用limit限制返回数
    SKIP使用skip进行跳过
    IDHACK对_id进行查询
    SHARDING_FILTER通过mongos对分片数据进行查询
    COUNTSCANcount不使用Index进行count时的stage返回
    COUNT_SCANcount使用了Index进行count时的stage返回
    SUBPLA未使用到索引的$or查询的stage返回
    TEXT使用全文索引进行查询时候的stage返回
    PROJECTION限定返回字段时候stage的返回

    3.6、执行计划的返回结果中尽量不要出现以下stage

    • COLLSCAN(全表扫描)
    • SORT(使用sort但是无index)
    • 不合理的SKIP
    • SUBPLA(未用到index的$or)
    • COUNTSCAN(不使用index进行count)
    展开全文
  • 如何查看执行计划 官方文档执行计划介绍 我们先创建三张表。一张课程表,一张老师表,一张老师联系方式表(没有任何索引)。 我们先创建三张表。一张课程表,一张老师表,一张老师联系方式表(没有任何索引)。 DROP...

    如何查看执行计划

    官方文档执行计划介绍

    我们先创建三张表。一张课程表,一张老师表,一张老师联系方式表(没有任何索引)。

    我们先创建三张表。一张课程表,一张老师表,一张老师联系方式表(没有任何索引)。

    DROP TABLE
    IF
    	EXISTS course;
    
    CREATE TABLE `course` ( `cid` INT ( 3 ) DEFAULT NULL, `cname` VARCHAR ( 20 ) DEFAULT NULL, `tid` INT ( 3 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
    
    DROP TABLE
    IF
    	EXISTS teacher;
    
    CREATE TABLE `teacher` ( `tid` INT ( 3 ) DEFAULT NULL, `tname` VARCHAR ( 20 ) DEFAULT NULL, `tcid` INT ( 3 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
    
    DROP TABLE
    IF
    	EXISTS teacher_contact;
    
    CREATE TABLE `teacher_contact` ( `tcid` INT ( 3 ) DEFAULT NULL, `phone` VARCHAR ( 200 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
    
    INSERT INTO `course`
    VALUES
    	( '1', 'mysql', '1' );
    
    INSERT INTO `course`
    VALUES
    	( '2', 'jvm', '1' );
    
    INSERT INTO `course`
    VALUES
    	( '3', 'juc', '2' );
    
    INSERT INTO `course`
    VALUES
    	( '4', 'spring', '3' );
    
    INSERT INTO `teacher`
    VALUES
    	( '1', 'bobo', '1' );
    
    INSERT INTO `teacher`
    VALUES
    	( '2', '老严', '2' );
    
    INSERT INTO `teacher`
    VALUES
    	( '3', 'dahai', '3' );
    
    INSERT INTO `teacher_contact`
    VALUES
    	( '1', '13688888888' );
    
    INSERT INTO `teacher_contact`
    VALUES
    	( '2', '18166669999' );
    
    INSERT INTO `teacher_contact`
    VALUES
    	( '3', '17722225555' );
    
    explain 的结果有很多的字段,我们详细地分析一下。
    
    先确认一下环境:
    
    select version(); 
    show variables like '%engine%';
    
    1. id
    id 是查询序列编号。
    

    id 值不同

    id 值不同的时候,先查询 id 值大的(先大后小)。
    
    -- 查询 mysql 课程的老师手机号
    EXPLAIN SELECT
    	tc.phone 
    FROM
    	teacher_contact tc 
    WHERE
    	tcid = ( SELECT tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM course c WHERE c.cname = 'mysql' ) );
    
    查询顺序:course c——teacher t——teacher_contact tc。
    

    1

    先查课程表,再查老师表,最后查老师联系方式表。子查询只能以这种方式进行,只有拿到内层的结果之后才能进行外层的查询。
    

    id 值相同(从上往下)

    -- 查询课程 ID 为 2,或者联系表 ID 为 3 的老师 
    EXPLAIN SELECT
    	t.tname,
    	c.cname,
    	tc.phone 
    FROM
    	teacher t,
    	course c,
    	teacher_contact tc 
    WHERE
    	t.tid = c.tid 
    	AND t.tcid = tc.tcid 
    	AND ( c.cid = 2 OR tc.tcid = 3 );
    

    2

    id 值相同时,表的查询顺序是
    

    从上往下顺序执行。例如这次查询的 id 都是 1,查询的顺序是 teacher t(3 条)——course c(4 条)——teacher_contact tc(3 条)。

    既有相同也有不同

    如果 ID 有相同也有不同,就是 ID 不同的先大后小,ID 相同的从上往下。
    
    2. select type 查询类型
    这里并没有列举全部(其它:DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION)。
    
    下面列举了一些常见的查询类型:
    

    SIMPLE

    简单查询,不包含子查询,不包含关联查询 union。
    
    EXPLAIN SELECT * FROM teacher;
    

    3

    再看一个包含子查询的案例:

    -- 查询 mysql 课程的老师手机号 
    EXPLAIN SELECT
    	tc.phone 
    FROM
    	teacher_contact tc 
    WHERE
    	tcid = ( SELECT tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM course c WHERE c.cname = 'mysql' ) );
    

    4

    PRIMARY

    子查询 SQL 语句中的主查询,也就是最外面的那层查询。
    

    SUBQUERY

    子查询中所有的内层查询都是 SUBQUERY 类型的。
    

    DERIVED

    衍生查询,表示在得到最终查询结果之前会用到临时表。例如:
    
    -- 查询 ID 为 1 或 2 的老师教授的课程
    EXPLAIN SELECT
    	cr.cname 
    FROM
    	( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2 ) cr;
    

    5

    对于关联查询,先执行右边的 table(UNION),再执行左边的 table,类型是DERIVED
    

    UNION

    用到了 UNION 查询。同上例。
    

    UNION RESULT

    主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。同上例。
    
    3. type 连接类型

    https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

    所有的连接类型中,上面的最好,越往下越差。
    
    在常用的链接类型中:system > const > eq_ref > ref > range > index > all
    
    这 里 并 没 有 列 举 全 部 ( 其 他 : fulltext 、 ref_or_null 、 index_merger 、unique_subquery、index_subquery)。
    

    以上访问类型除了 all,都能用到索引。

    const

    主键索引或者唯一索引,只能查到一条数据的 SQL。
    
    DROP TABLE
    IF
    	EXISTS single_data;
    CREATE TABLE single_data ( id INT ( 3 ) PRIMARY KEY, content VARCHAR ( 20 ) );
    INSERT INTO single_data
    VALUES
    	( 1, 'a' );
    EXPLAIN SELECT
    	* 
    FROM
    	single_data a 
    WHERE
    	id = 1;
    

    system

    system 是 const 的一种特例,只有一行满足条件。例如:只有一条数据的系统表。
    
    EXPLAIN SELECT * FROM mysql.proxies_priv;
    

    6

    eq_ref

    通常出现在多表的 join 查询,表示对于前表的每一个结果,,都只能匹配到后表的一行结果。一般是唯一性索引的查询(UNIQUE 或 PRIMARY KEY)。
    
    eq_ref 是除 const 之外最好的访问类型。
    
    先删除 teacher 表中多余的数据,teacher_contact 有 3 条数据,teacher 表有 3条数据。
    
    DELETE 
    FROM
    	teacher 
    WHERE
    	tid IN ( 4, 5, 6 );
    COMMIT;
    -- 备份
    INSERT INTO `teacher`
    VALUES
    	( 4, '老严', 4 );
    INSERT INTO `teacher`
    VALUES
    	( 5, 'bobo', 5 );
    INSERT INTO `teacher`
    VALUES
    	( 6, 'seven', 6 );
    COMMIT;
    
    为 teacher_contact 表的 tcid(第一个字段)创建主键索引。
    
    -- ALTER TABLE teacher_contact DROP PRIMARY KEY; 
    ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
    
    为 teacher 表的 tcid(第三个字段)创建普通索引。
    
    -- ALTER TABLE teacher DROP INDEX idx_tcid;
    ALTER TABLE teacher ADD INDEX idx_tcid (tcid);
    
    执行以下 SQL 语句:
    
    select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
    

    7

    此时的执行计划(teacher_contact 表是 eq_ref):
    

    8

    小结:

    以上三种 system,const,eq_ref,都是可遇而不可求的,基本上很难优化到这个状态。

    ref

    查询用到了非唯一性索引,或者关联操作只使用了索引的最左前缀。
    
    例如:使用 tcid 上的普通索引查询:
    
    explain SELECT * FROM teacher where tcid = 3;
    

    9

    range

    索引范围扫描。
    
    如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 这些,type 类型就为 range。
    
    不走索引一定是全表扫描(ALL),所以先加上普通索引。
    
    -- ALTER TABLE teacher DROP INDEX idx_tid; 
    ALTER TABLE teacher ADD INDEX idx_tid (tid);
    
    执行范围查询(字段上有普通索引):
    
    EXPLAIN SELECT * FROM teacher t WHERE t.tid <3; 
    -- 或
    EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;
    

    10

    IN 查询也是 range(字段有主键索引)
    
    EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);
    

    11

    index

    Full Index Scan,查询全部索引中的数据(比不走索引要快)。
    
    EXPLAIN SELECT tid FROM teacher;
    

    15

    all

    Full Table Scan,如果没有索引或者没有用到索引,type 就是 ALL。代表全表扫描。
    

    小结:

    一般来说,需要保证查询至少达到 range 级别,最好能达到 ref。
    
    ALL(全表扫描)和 index(查询全部索引)都是需要优化的。
    
    4. possible_key、key
    可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。
    
    possible_key 可以有一个或者多个,可能用到索引不代表一定用到索引。
    
    反过来,possible_key 为空,key 可能有值吗?
    
    表上创建联合索引:
    
    ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
    ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
    
    执行计划(改成 select name 也能用到索引):
    
    explain select phone from user_innodb where phone='126';
    

    12

    结论:是有可能的(这里是覆盖索引的情况)。
    
    如果通过分析发现没有用到索引,就要检查 SQL 或者创建索引。
    
    5. key_len
    索引的长度(使用的字节数)。跟索引字段的类型、长度有关。
    
    表上有联合索引:KEY
    

    comidx_name_phone (name,phone)

    explain select * from user_innodb where name ='jim';
    
    6. rows
    MySQL 认为扫描多少行才能返回请求的数据,是一个预估值。一般来说行数越少越好。
    
    7. filtered
    这个字段表示存储引擎返回的数据在 server 层过滤后,剩下多少满足查询的记录数量的比例,它是一个百分比。
    
    8. ref
    使用哪个列或者常数和索引一起从表中筛选数据。
    
    9. Extra
    执行计划给出的额外的信息说明。
    

    using index

    用到了覆盖索引,不需要回表。
    
    EXPLAIN SELECT tid FROM teacher ;
    

    using where

    使用了 where 过滤,表示存储引擎返回的记录并不是所有的都满足查询条件,需要在 server 层进行过滤(跟是否使用索引没有关系)。
    
    EXPLAIN select * from user_innodb where phone ='13866667777';
    

    13

    using filesort

    不能使用索引来排序,用到了额外的排序(跟磁盘或文件没有关系)。需要优化。(复合索引的前提)
    
    ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
    ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
    
    EXPLAIN select * from user_innodb where name ='jim' order by id;
    
    (order by id 引起)
    

    14

    using temporary

    用到了临时表。例如(以下不是全部的情况):
    
    1、distinct 非索引列
    
    EXPLAIN select DISTINCT(tid) from teacher t;
    
    2、group by 非索引列
    
    EXPLAIN select tname from teacher group by tname;
    
    3、使用 join 的时候,group 任意列
    
    EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;
    
    需要优化,例如创建复合索引。
    

    总结一下:

    模拟优化器执行 SQL 查询语句的过程,来知道 MySQL 是怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。

    分析出问题之后,就是对 SQL 语句的具体优化。

    展开全文
  • Explain 执行计划详解

    2021-05-30 02:05:32
    什么是执行计划 我们往往通过慢查询日志,可以找到慢查询的sql具体是怎么写的。我们往往可以通过表的索引,执行引擎等等去自己判断sql为什么执行缓慢的原因。但是这明显不是高效的。 我们的MySQL中为我们提供了...

    什么是执行计划

    我们往往通过慢查询日志,可以找到慢查询的sql具体是怎么写的。我们往往可以通过表的索引,执行引擎等等去自己判断sql为什么执行缓慢的原因。但是这明显不是高效的。

    我们的MySQL中为我们提供了EXPLAN关键字来协助我们分析SQL。

    一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划,这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划,我们需要搞懂 EPLATNEXPLAIN 的各个输出项都是干嘛使的,从而可以有针对性的提升我们查询语句的性能。

    通过使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。分析查询语句或是表结构的性能瓶颈,总的来说通过 EXPLAIN 我们可以帮助我们分析:

    • 表的读取顺序
    • 数据读取操作的操作类型
    • 哪些索引可以使用limit
    • 哪些索引被实际使用
    • 表之间的引用
    • 每张表有多少行被优化器查询

    Explain语法的使用

    执行计划的语法其实非常简单: 在 SQL 查询的前面加上 EXPLAIN 关键字就 行。比如:EXPLAIN select * from table1

    重点的就是 EXPLAIN 后面你要分析的 SQL 语句

    除了以 SELECT 开头的查询语句,其余的 DELETE、INSERT、REPLACE 以 及 UPOATE 语句前边都可以加上 EXPLAIN,用来查看这些语句的执行计划,不 过我们这里对 SELECT 语句更感兴趣,所以后边只会以 SELECT 语句为例来描述 EsxPLAIN 语句的用法。

    Explain不会真的执行sql,只是告诉你"自己将会按照这个方式执行sql",让你做参考优化的。

    执行计划详解

    为了让大家先有一个感性的认识,我们把 EXPLAIN 语句输出的各个列的作 用先大致罗列一下:

    explain select * from order_exp;

    image.png

    id: 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id

    select_type: SELECT 关键字对应的哪个查询的类型

    table:表名

    partitions:匹配的分区信息

    type:针对单表的访问方法

    possible_keys:可能用到的索引

    key:实际上使用的索引

    key_len:实际使用到的索引长度

    ref:当使用索引列等值查询时,与索引列进行等值匹配的对象信息

    rows:预估的需要读取的记录条数

    filtered:某个表经过搜索条件过滤后剩余记录条数的百分比

    Extra:—些额外的信息

    table列

    不论我们的查询语句有多复杂,里边包含了多少个表,到最后也是需要对每个表进行单表访问的,MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单 表的访问方法,该条记录的 table 列代表着该表的表名。

    image.png

    image.png

    可以看见,只涉及对 s1 表的单表查询,所以 EXPLAIN 输出中只有一条记录, 其中的 table 列的值是 s1,而连接查询的执行计划中有两条记录,这两条记录的 table 列分别是 s1 和 s2。

    id列

    如上所述,我们连表查询的本质其实就是多个单表查询。id就代表将一条大sql拆分成多个小sql的序号。

    单表select查询(同表同select)

    比如下边这个查询中只有一个 SELECT 关键字,所以 EXPLAIN 的结果中也就 只有一条 id 列为 1 的记录∶

    EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

    image.png

    连接查询(不同表同一个select)

    对于连接查询来说,一个 SELEOT 关键字后边的FROM 子句中可以跟随多个 表,所以在连接查询的执行计划中,每个表都会对应一条记录,但是这些记录的 id 值都是相同的,比如:

    EXPLAIN SELECT * FROM s1 INNER JOIN s2;

    image.png

    诶?这里的id为什么都是1呢?因为每一张表都对应一条记录,但每个select都对应一个id。

    多表select查询

    包含子查询(不同表用不同select)

    对于包含子查询的查询语句来说,就可能涉及多个 SELECT 关键字,所以在 包含子查询的查询语句的执行计划中,每个 SELECT 关键字都会对应一个唯一的 id 值,比如这样:

    EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a';

    image.png

    优化器将包含子查询自动改写连接子查询(优化后不同表用同一个select)

    查询优化器可能对涉及子查询的查询语句进行重写,从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询 的语句是否进行了重写,直接查看执行计划就好了,比如说:

    EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE order_no = 'a');

    image.png

    可以看到,虽然我们的查询语句是一个子查询,但是执行计划中 s1 和 s2 表 对应的记录的 id 值全部是 1,这就表明了查询优化器将子查询转换为了连接查询(也就是id有几个,实际sql中的select语句就有几个)。

    包含 UNION 子句(不同表用不同select)

    对于包含子查询的查询语句来说,就可能涉及多个 SELECT 关键字,所以在 包含子查询的查询语句的执行计划中,每个 SELECT 关键字都会对应一个唯一的 id 值,比如这样:

    EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

    image.png

    内部临时表(负责去重)

    这个语句的执行计划的第三条记录为什么这样?UNION 子句会把多个查询的结果集合并起来并对结果集中的记录进行去重,怎么去重呢? MySQL 使用的是内部的临时表。正如上边的查询计划中所示,UNION 子句是为了把 id 为 1 的查 询和id为2的查询的结果集合并起来并去重,所以在内部创建了一个名为<union1, 2>的临时表(就是执行计划第三条记录的 table 列的名称),id 为 NULL 表明这个 临时表是为了合并两个查询的结果集而创建的。

    包含 UNION ALL 子句(不同表用不同select)

    跟 UNION 对比起来,UNION ALL 就不需要为最终的结果集进行去重,它只 是单纯的把多个查询的结果集中的记录合并成一个并返回给用户,所以也就不需 要使用临时表。所以在包含 UNION ALL 子句的查询的执行计划中。因此就没有那个 id 为 NULL 的记录,如下所示:

    EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

    image.png

    总结

    我们写的一条sql语句中,可能包含了多个select小语句。每一个select语句都包含了一个或多个不同的表。

    1. 以表为单位,会产生不同的行。
    2. 同一个select所包含的表id是相同的。

    select_type 列(查询类别)

    • SIMPLE:简单的 select 查询,不使用 union 及子查询
    • PRIMARY:最外层的 select 查询
    • UNION:UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果 集
    • UNION RESULT:UNION 结果集
    • SUBQUERY:子查询中的第一个 select 查询,不依赖于外 部查询的结果集
    • DEPENDENT UNION:UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集 DEPENDENT SUBQUERY:子查询中的第一个 select 查询,依赖于外部查询的 结果集
    • DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些 子查询, 把结果放在临时表里。 MATERIALIZED:物化子查询
    • UNCACHEABLE SUBQUERY: 结果集不能被缓存的子查询,必须重新为外层查 询的每一行进行评估,出现极少。
    • UNCACHEABLE UNION:UNION 中的第二个或随后的 select 查询,属于不可缓 存的子查询,出现极少

    SIMPLE

    单表查询

    EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

    image.png

    连接查询

    EXPLAIN SELECT * FROM s1 INNER JOIN s2;

    image.png

    PRIMARY

    对于包含 UNION、UNION ALL 或者子查询的大查询来说,它是由几个小查询组成的,其中最左边的那个查询的 select_type 值就是 PRIMARY,比方说:

    EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

    image.png

    从结果中可以看到,最左边的小查询 SELECT * FROMN s1 对应的是执行计划中的第一条记录,它的 select_type 值就是 PRIMARY。

    UNION

    对于包含 UNION 或者 UNION ALL 的大查询来说,它是由几个小查询组成的, 其中除了最左边的那个小查询以外,其余的查询的 select_type 值就是 UNION, 可以对比上一个例子的效果。

    UNION RESULT

    MySQL 选择使用临时表来完成 UNION 查询的去重工作,针对该临时表的查询select_type 就是 UNION RESULT,例子上边有。

    SUBQUERY

    查询语句中包含了子查询,比如下面这个sql:

    EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a';

    image.png

    DEPENDENT UNION、DEPENDENT SUBQUERY

    在包含 UNION 或者 UNION ALL 的大查询中,如果各个小查询都依赖于外层 查询的话,那除了最左边的那个小查询之外,其余的小查询的 select_type 的值 就是 DEPENDENT UNION。比方说下边这个查询:

    EXPLAIN SELECT * FROM s1 WHERE id IN (
    SELECT id FROM s2 WHERE id = 716 
    UNION 
    SELECT id FROM s1 WHERE id = 718);

    这个查询比较复杂,大查询里包含了一个子查询,子查询里又是由 UNION 连起来的两个小查询。从执行计划中可以看出来,SELECT id FROM s2 WHERE id = 716 这个小查询由于是子查询中第一个查询,所以它的 select_type 是 OEPENDENT SUBOUERY,而 SELECT id FROM s1 WHERE id = 718 这个查询的 select_type 就是 DEPENDENT UNION。

    是不是很奇怪这条语句并没有依赖外部的查询?MySQL 优化器对 IN 操作符 的优化会将 IN 中的非关联子查询优化成一个关联子查询。我们可以在执行上面 那个执行计划后,马上执行 show warnings\G,可以看到 MySQL 对 SQL 语句的大 致改写情况:

    image.png

    DERIVED

    对于采用物化的方式执行的包含派生表的查询,该派生表对应的子查询的 select_type 就是 DERIVED。

    EXPLAIN SELECT * FROM 
    (SELECT id, count(*) as c FROM s1 GROUP BY id) AS derived_s1 where c >1;

    image.png

    从执行计划中可以看出, id 为 2 的记录就代表子查询的执行方式,它的 select_type 是 DERIVED ,说明该子查询是以物化的方式执行的。id 为 1 的记录 代表外层查询,大家注意看它的 table 列显示的是<derived2>,表示该查询是针 对将派生表物化之后的表进行查询的。

    MATERIALIZED

    当查询优化器在执行包含子查询的语句时,选择将子查询物化之后与外层查询进行连接查询时,该子查询对应的 select_type 属性就是 MATERIALIZED,比如 下边这个查询︰

    EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2);

    image.png

    执行计划的第三条记录的 id 值为 2,从它的 select_type 值为 MATERIALIED 可 以看出,查询优化器是要把子查询先转换成物化表。 然后看执行计划的前两条记录的 iad 值都为 1,说明这两条记录对应的表进 行连接查询,需要注意的是第二条记录的 table 列的值是<subquery2>,说明该表 其实就是 id 为 2 对应的子查询执行之后产生的物化表,然后将 s1 和该物化表进行连接查询。

    UNCACHEABLE SUBQUERY、UNCACHEABLE UNION

    出现极少,不做深入讲解,比如:

    explain select * from s1 where id = ( select id from s2 where order_no=@@sql_log_bin);

    image.png

    partitions列

    和分区表有关,一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL

    type列(索引类别)

    我们前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时 的访问方法/访问类型,其中的 type 列就表明了这个访问方法/访问类型是个什么东西(一般用来判断走没走索引),是较为重要的一个指标。

    type结果值排序

    结果值从最好到最坏依次是:

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    system(非InnoDB)

    MyISAM引擎

    当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的,比如 MyISAM、Memory,那么对该表的访问方法就是 system。

    explain select * from test_myisam;

    image.png

    InnoDB引擎

    当然,如果改成使用 InnoDB 存储引擎,试试看执行计划的 type 列的值是什么

    image.png

    只有一条数据也全表扫描。

    const(唯一查询)

    就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时,对单表的访问方法就是 const。因为只匹配一行数据,所以很快。

    const,意思是常数级别的,代价是可以忽略不计的。

    主键索引

    EXPLAIN SELECT * FROM s1 WHERE id = 716;

    image.png

    唯一二级索引

    H){DJXEZW)IM4F]6)A(KTYD.png

    唯一二级索引就是UNIQUE修饰的列索引,在进行等值操作时也是const类型。由于只有一条数据,因此即使回表速度也很快。

    唯一二级索引特殊的null

    对于唯一二级索引来说,查询该列为 NULL 值的情况比较特殊,因为唯一二级索引列并不限制 NULL 值的数量。这和我们的UNIQUE定义是违背的。

    所以上述语句可能访问到多条记录,也就是说唯一二级索引条件为 is null 不可以使用 const 访问方法来执行。

    非叶子节点在内存中有缓存

    我们一般非叶子节点的索引往往在内存中存在缓存。因此实际IO次数可能更少。

    eq_ref(关联查询,被驱动表条件为唯一索引)

    在连接查询时,如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话,所有的索引列 都必须进行等值比较),则对该被驱动表的访问方法就是 eq_ref。

    驱动表与被驱动表

    A 表和 B 表 join 连接查询,如果通过 A 表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到 B 表中查询数据,然后合并结果。那么我们称 A 表为驱动表,B 表为被驱动表。

    实例

    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

    image.png

    ref(普通索引查询)

    当通过普通的二级索引列与常量进行等值匹配时来查询某个表,那么对该表的访问方法就可能是 ref。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他属于查找和扫描的混合体

    EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

    image.png

    ref和All查询方式的选择

    对于这个查询,我们当然可以选择全表扫描来逐一对比搜索条件是否满足要求,我们也可以先使用二级索引找到对应记录的id值,然后再回表到聚簇索引中查找完整的用户记录。

    由于普通二级索引并不限制索引列值的唯一性,所以可能找到多条对应的记录,也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少,则回表的代价还是比较低的,所以 MySQL可能选择使用索引而不是全表扫描的方式来执行查询。这种搜索条件为二级索引列与常数等值比较,采用二级索引来执行查询的访问方法称为:ref。

    普通索引为null的情况

    普通索引列允许为null的情况下,数量必然是不受限制的,所以我们采用 key IS NULL 这种形式的搜索条件最多只能使用 ref 的访问方法,而不是 const 的访问方法。

    fulltext(全文索引)

    全文索引,略过。

    ref_or_null(普通二级索引+null值)

    有时候我们不仅想找出某个二级索引列的值等于某个常数的记录,还想把该列的值为 NULL 的记录也找出来,就像下边这个查询:

    explain SELECT * FROM order_exp_cut WHERE order_no= 'abc' OR order_no IS NULL;

    image.png

    这个查询相当于先分别从 order_exp_cut 表的 idx_order_no 索引对应的 B+树 中找出 order_no IS NULL 和 order_no= 'abc'的两个连续的记录范围,然后根据这 些二级索引记录中的 id 值再回表查找完整的用户记录。

    index_merge(使用多个索引)

    一般情况下对于某个表的查询只能使用到一个索引,在某些场景下可以使用索引合并的方式来执行查询:

    EXPLAIN SELECT * FROM s1 WHERE order_no = 'a' OR insert_time = '2021-03-22 18:36:47';

    image.png

    什么是索引合并,我们以后再说。

    unique_subquery(主键列子查询)

    unique _subquery 是针对在一些包含IN子查询的查询语句中,如果查询优化器决定将IN子查询转换为EXISTS子查询,而且子查询可以使用到主键进行等值匹配的话,那么该子查询执行计划的 type 列的值就是 unique_subquery,比如下边的这个查询语句:

    EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 where s1.insert_time = s2.insert_time) OR order_no = 'a';

    可以看到执行计划的第二条记录的 type 值就是 unique_subquery,说明在执行子查询时会使用到 id 列的索引

    index_subquery(普通列子查询)

    index_subquery 与 unique_subquery 类似,只不过访问⼦查询中的表时使的是普通的索引:

    EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where s1.insert_time = s2.insert_time) OR order_no = 'a';

    这个语句和 unique_subquery 章节中的唯一不同是什么?就是 in 子句的查询字段由id 变成了 order_no。

    18FOIM~G6A)IF376OC4AC_E.png

    range(范围)

    如果使用索引获取某些范围区间的记录,那么就可能使用到range访问方法, 一般就是在你的 where 语句中出现了 between、<、>、in 等的查询。 这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点, 而结束语另一点,不用扫描全部索引。

    EXPLAIN SELECT * FROM s1 WHERE order_no IN ('a', 'b', 'c');
    EXPLAIN SELECT * FROM s1 WHERE order_no > 'a' AND order_no < 'b';

    image.png

    这种利用索引进行范围匹配的访问方法称之为:range。 此处所说的使用索引进行范围匹配中的 `索引可以是聚簇索引,也可以是二级索引。

    index(扫描整个索引并使用索引覆盖)

    当我们可以使用索引覆盖,但需要扫描全部的索引记录时,该表的访问方法就是 index。

    EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = '2021-03-22 18:36:47';

    image.png

    all(全表扫描)

    最熟悉的全表扫描,将遍历全表以找到匹配的行

    EXPLAIN SELECT * FROM s1;

    image.png

    possible_keys(可能索引) 与 key(实际索引)

    possible_keys 列表示在某个查询语句中, 对某个表执行单表查询时可能用到的索引有哪些,key 列表示实际用到的索引有 哪些,如果为 NULL,则没有使用索引。比方说下边这个查询:

    EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = '2021-03-22 18:36:47';

    上述执行计划的 possible keys 列的值表示该查询可能使用到 u_idx_day_status,idx_insert_time 两个索引,然后 key 列的值是 u_idx_day_status, 表示经过查询优化器计算使用不同索引的成本后,最后决定使用 u_idx_day_status 来执行查询比较划算

    possible_keys为空而key不为空?

    不过有一点比较特别,就是在使用 index 访问方法来查询某个表时,可能会出现 possible_keys 列是空的,而 key 列展示的是实际使用到的索引,比如这样:

    索引结构如下:

    image.png

    EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = '2021-03-22 18:36:47';

    image.png

    表中,expire_time没有独立的索引,只存在于联合索引的最右边。

    那么这种情况可能发生于覆盖索引的情况下。possible_keys为null说明用不上索引的树形查找(expire_time没有独立的索引,并且也不符合联合索引的最左前缀原则)。

    但是,通过遍历整个联合索引,再通过联合索引定位到expire_time的区间,我们就可以轻松获得insert_time的值。没想到联合索引还可以这么用。只能说明这是一种特殊优化吧。

    key_len(表示索引最大长度)

    key_len的计算方式

    key_len 列表示当优化器决定使用某个索引执行查询时,该索引记录的最大长度,计算方式是这样的: 对于使用固定长度类型的索引列来说,它实际占用的存储空间的最大长度就 是该固定值,对于指定字符集的变长类型的索引列来说,比如某个索引列的类型 是 VARCHAR(100),使用的字符集是 utf8,那么该列实际占用的最大存储空间就 是 100 x 3 = 300 个字节。

    如果该索引列可以存储 NULL 值,则 key_len 比不可以存储 NULL 值时多 1 个 字节。

    对于可变长度(varchar)字段来说,都会有 2 个字节的空间来存储该变长列的实际长度。

    非可变长度字段实例

    EXPLAIN SELECT * FROM s1 WHERE id = 718;

    image.png

    由于 id 列的类型是 bigint,并且不可以存储 NULL 值,所以在使用该列的索引时 key_len 大小就是 8。

    可变长度字段实例

    EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

    image.png

    由于 order_no 列的类型是 VARCHAR(50),所以该列实际最多占用的存储空间就是 50*3 字节,又因为该列是可变长度列,所以 key_len 需要加 2,所以最后 ken_len 的值就是 152。

    判断复合索引具体使用索引长度

    执行计划的生成是在 MySQL server 层中的功能,并不是针对具体某个存储引擎的功能,MySQL 在执行计划中输出 key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列(复合索引有最左前缀的特性,如果复合索引能全部使用上,则是复合索引字段的索引长度之和,这也可以用来判定复合索引是否部分使用,还是全部使用),而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用 1 个字节还是 2 个字节。

    Key_len尽量控制小

    Key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。

    key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len 是根据表定义计算而得,不是通过表内检索出的。 注意:char 和 varchar 跟字符编码也有密切的联系,比如 latin1 占用 1 个字节, gbk 占用 2 个字节,utf8 占用 3 个字节。

    ref(做等值匹配时的对象类型)

    当使用索引列等值匹配的条件去执行查询时,也就是在访问方法是 const、 eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery 其中之一时,ref 列展示的就是与索引列作等值匹配的是谁,比如只是一个常数或者是某个列。比如:

    const(常量)

    EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

    可以看到 ref 列的值是 const,表明在使用 idx_order_no 索引执行查询时,与 order_no 列作等值匹配的对象是一个常数(a),当然有时候更复杂一点,我们接着看。

    其他表字段

    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

    可以看到对被驱动表 s2 的访问方法是 eg_ref,而对应的 ref 列的值是 mysqladv.s2.id,这说明在对被驱动表进行访问时会用到 PRIMARY 索引,也就是 聚簇索引与一个列进行等值匹配的条件,与 s2 表的 id 作等值匹配的对象就是 mysqladv.s2.id 列(注意这里把数据库名也写出来了。

    函数

    有的时候与索引列进行等值匹配的对象是一个函数,比如:

    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.order_no= UPPER(s1.order_no);

    可以看到在查询计划的 ref 列⾥输出的是 func。

    image.png

    rows(预估符合行数)

    如果查询优化器决定使用全表扫描的方式对某个表执行查询时,执行计划的 rows 列就代表预计需要扫描的行数,如果使用索引来执行查询时,执行计划的 rows 列就代表预计扫描的索引记录行数。比如下边两个查询:

    EXPLAIN SELECT * FROM s1 WHERE order_no > 'z';
    EXPLAIN SELECT * FROM s1 WHERE order_no > 'a';

    image.png

    我们看到执行计划的 rows 列的值是分别是 1 和 10573,这意味着查询优化 器在经过分析使用 idx_order_no 进行查询的成本之后,觉得满足 order_no> ' a ' 这个条件的记录只有 1 条,觉得满足 order_no> ' a '这个条件的记录有 10573 条。

    filtered(索引命中率)

    单表命中率

    查询优化器预测索引命中率。如下:

    EXPLAIN SELECT * FROM s1 WHERE id > 5890 AND order_note = 'a';

    此处 filtered 列的值是 10.0,说明查询优化器预测在 5286 条记录中有 10.00%的记录满足 order_note = 'a'这个条件。

    多表命中率

    对于单表查询来说,这个 filtered 列的值没什么意义,我们更关注在连接查 询中驱动表对应的执行计划记录的 filtered 值,比方说下边这个查询:

    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.order_no = s2.order_no WHERE s1.order_note > '你好,李焕英';

    从执行计划中可以看出来,查询优化器打算把 s1 当作驱动表,s2 当作被驱动表。

    假设驱动表 s1 表的执行计划的 rows 列为 10573,filtered 列为 33.33 ,这意味着驱动表 s1 的扇出值就是 10573 x 33.33 % = 3524.3,这说明还要对被驱动表执行大约 3524 次查询。

    Extra(其他信息)

    几十种信息,列举部分常见的:

    No tables used

    当查询语句的没有 FROM 子句时将会提示该额外信息。

    Impossible WHERE

    查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息。

    No matching min/max row

    当查询列表处有 MIN 或者 MAX 聚集函数,但是并没有符合 WHERE 子句中 的搜索条件的记录时,将会提示该额外信息。

    Using index(覆盖索引无需回表)

    当我们的查询列表以及搜索条件中只包含属于某个索引的列,也就是在可以 使用索引覆盖的情况下,在 Extra 列将会提示该额外信息。比方说下边这个查询 中只需要用到 idx_order_no 而不需要回表操作:

    EXPLAIN SELECT expire_time FROM s1 WHERE insert_time = '2021-03-22 18:36:47';

    Using index condition(索引下推)

    有些搜索条件中虽然出现了索引列,但却不能使用到索引,比如下边这个查询:

    SELECT * FROM s1 WHERE order_no > 'z' AND order_no LIKE '%a';

    MySQL5.6前执行策略

    其中的 order_no> 'z'可以使用到索引,但是 order_no LIKE '%a'却无法使用到 索引,在以前版本的 MySQL 中,是按照下边步骤来执行这个查询的:

    1. 先根据 order_no> 'z'这个条件,从二级索引 idx_order_no 中获取到对应的 二级索引记录。、
    2. 根据上一步骤得到的二级索引记录中的主键值进行回表(因为是 select *), 找到完整的用户记录再检测该记录是否符合 key1 LIKE '%a'这个条件,将符合条件 的记录加入到最后的结果集。

    MySQL5.6后执行策略,索引下推

    1、先根据 order_no> 'z'这个条件,定位到二级索引 idx_order_no 中对应的二级索引记录。

    2、对于指定的二级索引记录,先不着急回表,而是先检测一下该记录是否满足 order_no LIKE '%a'这个条件,如果这个条件不满足,则该二级索引记录压根儿就没必要回表。

    3、对于满足 order_no LIKE '%a'这个条件的二级索引记录执行回表操作。 我们说回表操作其实是一个随机 IO,比较耗时,所以上述修改可以省去很多回表操作的成本。这个改进称之为索引条件下推(英文名:ICP ,Index Condition Pushdown)。

    索引下推常出现在

    如果在查询语句的执行过程中将要使用索引条件下推这个特性,在 Extra 列 中将会显示 Using index condition,比如这样:

    image.png

    索引下推的场景

    常见的索引下推还有联合索引。总之联合索引的产生条件就是:在走了某个索引需要回表前,可以通过该索引再次筛查其它条件则再次进行筛查后回表,减少操盘IO。

    Using where(仅表示对查询条件进行了过滤)

    全表扫描

    当我们使用全表扫描来执行对某个表的查询,并且该语句的 WHERE 子句中有针对该表的搜索条件时,在 Extra 列中会提示上述额外信息。

    EXPLAIN SELECT * FROM s1 WHERE order_note = 'a';

    image.png

    走了索引,但需要回表并再次过滤(多条件情况下)

    当使用索引访问来执行对某个表的查询,并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时,在 Extra 列中也会提示上述信息。

    比如下边这个查询虽然使用 idx_order_no 索引执行查询,但是搜索条件中除 了包含 order_no 的搜索条件 order_no = 'a',还有包含 order_note 的搜索条件, 此时需要回表检索记录然后进行条件判断,所以 Extra 列会显示 Using where 的 提示:

    EXPLAIN SELECT * FROM s1 WHERE order_no = 'a' AND order_note = 'a';

    image.png

    Using where出现的原理

    出现了 Using where,只是表示在 server 层根据 where 条件进行了过滤,和是否全表扫描或读取了索引文件没有关系,网上有不少文章把 Using where 和是否读取索引进行关联,是不正确的,也有文章把 Using where 和回表进行了关联,这也是不对的。

    很明显,Using where 只是表示 MySQL 使用 where 子句中的条件对记录进行了过滤。

    Using join buffer (Block Nested Loop)(使用关联临时缓存)

    在连接查询执行过程中,当被驱动表不能有效的利用索引加快访问速度, MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度:

    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.order_note = s2.order_note;

    image.png

    我们看到,在对 s1 表的执行计划的 Extra 列显示了两个提示:

    Using join buffer (Block Nested Loop)

    因为查询到s1中的order_note后,还需要再继续依次对s2中的order_note依次进行判断。因此需要将s1的值先临时缓存起来,可以减少对s1表的访问次数。

    Using where

    可以看到查询语句中有一个 s1.order_note = s2.order_note 条 件,因为 s2 是驱动表,s1 是被驱动表,所以在访问 s1 表时,s1.order_note 的 值已经确定下来了,所以实际上查询 s1 表的条件就是 s1.order_note = 一个常数, 所以提示了 Using where 额外信息。

    Not exists(关联查询中是否为Null不匹配)

    当我们使用左(外)连接时,如果 WHERE 子句中包含要求被驱动表的某个 列等于 NULL 值的搜索条件,而且那个列又是不允许存储 NULL 值的,那么在该 表的执行计划的 Extra 列就会提示 Not exists 额外信息,比如这样:

    EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.order_no = s2.order_no WHERE s2.id IS NULL;

    image.png

    上述查询中 s1 表是驱动表,s2 表是被驱动表,s2.id 列是主键而且不允许存储 NULL值的,而 WHERE 子句中又包含 s2.id IS NULL 的搜索条件。

    Using intersect(...)、Using union(...)和 Using sort_union(...)(索引合并)

    如果执行计划的 Extra 列出现了 Using intersect(...)提示,说明准备使用 Intersect 索引合并的方式执行查询,括号中的...表示需要进行索引合并的索引名称;如果出现了 Using union(...)提示,说明准备使用 Union 索引合并的方式执行 查询;出现了 Using sort_union(...)提示,说明准备使用 Sort-Union 索引合并的方 式执行查询。什么是索引合并,我们后面会单独讲。

    Zero limit(limit 0)

    当我们的 LIMIT 子句的参数为 0 时,表示压根儿不打算从表中读出任何记录, 将会提示该额外信息。

    Using filesort(不走索引的排序)

    索引排序

    有一些情况下对结果集中的记录进行排序是可以使用到索引的,比如下边这个查询:

    EXPLAIN SELECT * FROM s1 ORDER BY order_no LIMIT 10;

    image.png

    这个查询语句可以利用idx_order_no索引直接取出order_no列的10条记录,然后再进行回表操作就好了。

    非索引排序

    但是很多情况下排序操作无法使用到索引,只能在内存中(记录较少的时候)或者磁盘中(记录较多的时候)进行排序,MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序。如果某个查询需要使用文件排序的方式执行查询,就会在执行计划的 Extra 列中显示 Using filesort 提示:

    EXPLAIN SELECT * FROM s1 ORDER BY order_note LIMIT 10;

    image.png

    需要注意的是,如果查询中需要使用 filesort 的方式进行排序的记录非常多,那么这个过程是很耗费性能的,我们最好想办法将使用文件排序的执行方式改为使用索引进行排序。

    Using temporary(临时表)

    在许多查询的执行过程中,MySQL 可能会借助临时表来完成一些功能,比如去重、排序之类的,比如我们在执行许多包含 DISTINCT、GROUP BY、UNION 等子句的查询过程中,如果不能有效利用索引来完成查询,MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表,在执行计划的 Extra 列将会显示 Using temporary 提示:

    EXPLAIN SELECT DISTINCT order_note FROM s1;

    image.png

    再比如:

    EXPLAIN SELECT order_note, COUNT(*) AS amount FROM s1 GROUP BY order_note;

    上述执行计划的 Extra 列不仅仅包含 Using temporary 提示,还包含 Using filesort 提示,可是我们的查询语句中明明没有写 ORDER BY 子句呀?这是因为 MySQL 会在包含 GROUP BY 子句的查询中默认添加上 ORDER BY 子句,也就是说上述查询其实和下边这个查询等价:

    EXPLAIN SELECT order_note, COUNT(*) AS amount FROM s1 GROUP BY order_note order by order_note;

    也就是说GROUP BY默认会进行排序。如果我们并不想为包含 GROUP BY 子句的查询进行排序,需要我们显式的写 上 ORDER BY NULL:

    EXPLAIN SELECT order_note, COUNT(*) AS amount FROM s1 GROUP BY order_note order by null;

    image.png

    很明显,执行计划中出现 Using temporary 并不是一个好的征兆,因为建立与维护临时表要付出很大成本的,所以我们最好能使用索引来替代掉使用临时表:

    EXPLAIN SELECT order_no, COUNT(*) AS amount FROM s1 GROUP BY order_no;

    image.png

    从 Extra 的 Using index 的提示里我们可以看出,上述查询只需要扫描 idx_order_no 索引就可以搞定了,不再需要临时表了。

    总的来说,发现在执行计划里面有using filesort或者Using temporary的时候, 特别需要注意,这往往存在着很大优化的余地,最好进行改进,变为使用 Using index 会更好。

    Start temporary, End temporary

    有子查询时,查询优化器会优先尝试将 IN 子查询转换成 semi-join(半连接优 化技术,本质上是把子查询上拉到父查询中,与父查询的表做 join 操作),而 semi-join 又有好多种执行策略,当执行策略为 DuplicateWeedout 时,也就是通 过建立临时表来实现为外层查询中的记录进行去重操作时,驱动表查询执行计划 的 Extra 列将显示 Start temporary 提示,被驱动表查询执行计划的 Extra 列将显 示 End temporary 提示。

    LooseScan

    在将 In 子查询转为 semi-join 时,如果采用的是 LooseScan 执行策略,则在 驱动表执行计划的 Extra 列就是显示 LooseScan 提示。

    FirstMatch(tbl_name)

    在将 In 子查询转为 半连接查询(semi-join) 时,如果采用的是 FirstMatch 执行策略,则在被驱动表执行计划的 Extra 列就是显示 FirstMatch(tbl_name)提示。

    展开全文
  • explain执行计划包含的信息其中最重要的字段为:id、type、key、rows、Extra各字段详解idselect查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序三种情况:1、id相同:执行顺序由上至下2、id...
  • 如何写出效率高的SQL语句,提到这必然离不开Explain执行计划的分析,至于什么是执行计划,如何写出高效率的SQL,本篇文章将会一一介绍。 执行计划 执行计划是数据库根据 SQL 语句和相关表的统计信息作出的一个查询...
  • Mysql explain 执行计划之type详解

    千次阅读 2021-09-12 22:29:24
    EXPLAIN执行计划中type字段分为以下几种: ALL INDEX RANGE REF EQ_REF CONST,SYSTEM NULL 自上而下,性能从最差到最好 type = ALL,全表扫描,MYSQL扫描全表来找到匹配的行 (因为film表中rating...
  • MySQL explain执行计划详解

    千次阅读 2019-04-25 15:11:33
    一、Explain基本概念 ...· EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的,从而知道Mysql是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈。 · explain显示了mysql如何使用索引来处理selec...
  • MYSQL explain执行计划解读

    千次阅读 2021-03-04 02:53:53
    Explain 查看SQL语句的执行计划:分析SQL执行计划,优化SQL及索引策略,run faster.EXPLAIN SELECT * from user_info WHEREid = 1;Explain查看查询计划主要包含如下信息列:查询id、查询类型、查询表、扫描访问类型...
  • explain执行计划详解

    万次阅读 多人点赞 2018-05-24 14:12:41
    explain执行计划详解1. Explain表的读取顺序,数据读取操作的类型,哪些索引可以使用,哪些索引实际使用了,表之间的引用,每张表有多少行被优化器查询等信息。下面是使用explain 的例子: 1.1. explain执行计划...
  • 内容目录Hive优化:Explain执行计划一:Explain执行计划二:Explain的语法三:Explain的用法四、图示Explain执行计划 一:Explain执行计划 想要做好hive优化,你可以启用一些hive配置,压缩文件等等,但是羊毛出在羊...
  • MySQL EXPLAIN执行计划详解

    千次阅读 2021-08-28 16:11:07
    详细介绍了MySQL EXPLAIN执行计划的各个字段的含义以及使用方式。
  • MySQL服务器程序分为server层和存储引擎层,sever层在生成执行计划后,是按照下面的步骤来执行这个查询的: 步骤1:server层首先调用存储引擎的接口定位到满足key1>'z’条件的第一条二级索引记录。 步骤2:存储引擎...
  • 【进阶】explain 执行计划详解

    千次阅读 2019-03-17 09:15:19
    MySql使用explain关键字可以模拟优化器执行sql语句,我们就能够知道MySql会如何处理咱们的sql, 可以根据explain的分析结果和MySql底层数据结构优化sql。文章内容基于MySql 5.7.24分析, 不同MySql版本可能有差别...
  • MySQL高级 之 explain执行计划详解

    万次阅读 多人点赞 2017-05-09 22:55:10
    explain执行计划包含的信息其中最重要的字段为:id、type、key、rows、Extra各字段详解idselect查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 三种情况: 1、id相同:执行顺序由上至下 2...
  • 面试官:不会看 Explain执行计划,简历敢写 SQL 优化?.mhtml
  • Explain执行计划key_len详解

    千次阅读 2020-11-05 01:06:58
    我们在使用Explain查看SQL执行计划时,其中有一列为key_ken EXPLAIN select * FROM user WHERE id = 1; key_len表示使用的索引长度,key_len可以衡量索引的好坏,key_len越小索引效果越好,那么key_len的长度是...
  • 主要介绍了MongoDB性能篇之创建索引,组合索引,唯一索引,删除索引和explain执行计划的相关资料,需要的朋友可以参考下
  • Explain 执行计划

    2022-01-03 20:43:26
    EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划,我们需要搞懂EPLATNEXPLAIN的各个输出项都是干嘛使的,从而可以有针对性的提升我们查询语句的性能。 通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语.
  • HIVE中explain执行计划

    2022-04-30 22:19:37
    Hive SQL的执行计划描述SQL实际执行的整体轮廓,通过执行...要想学SQL执行计划,就需要学习查看执行计划的命令:explain,在查询语句的SQL前面加上关键字explain是查看执行计划的基本方法。 学会explain,能够给我们工
  • Mysql中有专门负责优化Select语句的优化器模块,主要功能是:通过计算分析系统中手记到的统计信息,为客户端请求的Query提供Mysql认为最优化的执行计划,认为最优的检索方式,但是不一定是DBA认为是最优的,所...
  • 三、 const级别 查询结果最多有一个匹配行,一般是把主键或唯一索引作为唯一条件的查询都是const 四、eq_ref级别 唯一性索引扫描, 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或者唯一索引扫描 explain ...
  • 关于MySQL的explain执行计划的结果解析
  • mysql explain执行计划详解

    千次阅读 2021-01-28 02:18:25
    1)、id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行,id列为null的就表是这是一个结果集,不需要使用它来进行查询。2)、select_type列常见的有:A:simple:表示不需要union操作或者不包含子查询...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 109,564
精华内容 43,825
关键字:

explain执行计划