【MYSQL数据库二】EXPLAIN、联合索引相关优化场景以及常用hint

最新推荐文章于 2023-12-19 23:48:45 发布

Dog•Li

最新推荐文章于 2023-12-19 23:48:45 发布

阅读量308

点赞数

分类专栏： Mysql从零到内核系统学习文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/xwl137487622/article/details/126134095

版权

Mysql从零到内核系统学习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

【MYSQL数据库二】EXPLAIN、联合索引相关优化场景以及常用hint

在前几篇文章陈述了一些索引的基本数据结构以及SQL执行的流程，在实际开发的情况下，很多时候遇到性能比较低下的SQL都需要借用MYSQL给我们提供的工具分析，对MYSQL的优化过程或者执行过程进一步地分析，比如Explain,profile 工具等，本文仅针对Explain概述。

一.Explain执行计划详解

执行计划简介
有了慢查询之后，就需要对慢查询进行分析。从上一篇文章大概知道了SQL大体执行流程，一条SQL在执行的过程中查询优化器会对各个索引的访问成本进行计算，最后选择最优MYSQL认为最优的索引并且生成执行计划，这个执行计划展示了查询优化器生成的结果，如:对SQL进行一定的改写，合适索引的选择，连接查询时选择驱动表与被驱动表等，后续执行器就需要按这个执行计划执行SQL语句，所以我们需要弄明白Explain各个列的含义，可以有针对性的提高查询性能。
Explain是模拟查询优化器的SQL语句执行，从而知道MYSQL是如何处理你的SQL的，通过Explain我们可以:

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

执行计划各个列详解
id: 每个select对应一个id，id越大越先执行，id为空最后执行。
select_type: SELECT 关键字对应的那个查询的类型
table : 表名
partitions: 匹配的分区信息
type: 针对单表的访问方法
possible_keys: 可能用到的索引
key: 实际使用到的索引
key_len: 实际使用到的索引长度
ref: 表示等值匹配查询的索引中，使用到的列或者常量，常见的有const，字段名
rows: 预估扫描行数
filtered:某个表经过搜索条件过滤后剩余的百分比
extra:额外信息列，显示了MYSQL 对数据进行了什么样的过滤，以及一些查询的方式


drop table if EXISTS tb_order ;

CREATE TABLE `tb_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=214 DEFAULT CHARSET=utf8mb4;

INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('1', '1023312', '0', '99');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('2', '324343', '1', '1');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('3', '434345', '1', '3');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('4', '3232', '1', '10');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('5', '232', '1', '10');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('6', '4354', '0', '99');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('7', '43432', '0', '11');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('8', '4354', '0', '10');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('9', '32321', '0', '0');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('10', '12231', '0', '10');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('11', '324124433', '0', '10');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('12', '43547898', '0', '10');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('13', '888887666', '0', '10');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('14', '323', '0', '10');
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('15', '3324', '1', NULL);
INSERT INTO `mysql`.`tb_order` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('213', '3323', '0', NULL);


drop table if EXISTS order_task ;

CREATE TABLE `order_task` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `task_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=214 DEFAULT CHARSET=utf8mb4;


drop table if EXISTS task ;

CREATE TABLE `task` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `task_name` bigint(20) NOT NULL,
  `task_status` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=214 DEFAULT CHARSET=utf8mb4;

用法
执行Explain只需要在目标SQL执行加上explain即可，比如Explain select * from tb_order，除了select 另外的insert update delete replace都可以使用explain分析执行计划。

在5.7之前的版本，如果查询partitions分区信息和 filtered计算关联的行数，需要在explain后面加上关键字，partitions，extended两个字段才会展示partition列信息和filtered列信息，在5.7之后默认显示，加不加这两个关键字都可，在8.0之后删除这两个关键字。filtered列可以跟row结合计算出与其它表关联的行数: Row * (filtered/100)。

EXPLAIN EXTENDED  select * from tb_order1 where id in (select id from tb_order);

EXPLAIN PARTITIONS select * from tb_order1 where id in (select id from tb_order);

如果想查看MYSQL优化器对SQL做了何种程度的改写，可以在Explain + SQL语句的最后加上SHOW WARNINGS;关键字，在5.7之前查看SQL被优化器进行了何种改写，需要通过SHOW WARNINGS查看的话在EXPLAIN之后需要加上extended
在这里插入图片描述
在这里将不相关的包含子查询改写成了连接查询，因为连接查询可以决定驱动表和被驱动表，会默认将小表当成驱动表，从而优化查询的成本。

Explain各个字段详解:

mysql> explain select * from (select id,task_id from tb_order) tb;

在这里插入图片描述

mysql> explain select * from tb_order where id = 1;

在这里插入图片描述

1.id列

从上面的两个查询中可以看到，一个查询中出现了多个SELECT就有多少个id，涉及到多少个表的查询explain就会有几条结果，如果id越大越先执行，id越小越后面执行。如果id相同，那么从上往下执行，id为空，则最后执行。

1.1 单SELECT查询

1.1.1 普通单SELECT查询:

对于这种查询只有一个SELECT关键字，MYSQL只为其分配了一个ID，所以Explain的结果也只有一条。在这里插入图片描述

1.1.2 单SELECT-连接查询

Join查询往往在第一个表的查询会有一个SELECT，在关键字join后面会跟有一个或多个表的关联，如果是两个表的关联，可以看到连接查询explain的结果有两条记录，但是只有一个id，都为1。

mysql> EXPLAIN select * from tb_order join order_task using (`task_id`);

在这里插入图片描述
可以看到tb_order 和 order_task表都对应了一条记录，但是这些记录所分配的id都是相同的，在连接查询中，不管join后面跟了几个表的连接，id都是用的同一个。

1.2 多SELECT关键字查询

下面三种情况下在一条语句可能有两个SELECT关键字:

1.2.1 查询包含(衍生)子查询的情况

mysql> EXPLAIN select * from tb_order1 where id in (select id from tb_order) or task_id = 1;
mysql> EXPLAIN select * from tb_order1 where id in (select id from tb_order);
mysql> EXPLAIN select * from (select id,task_id from tb_order) tb;

对于下面这个包含子查询来说，可能一条SQL就包含了多个SELECT关键字，针对每个SELECT，MYSQL都会为其分配一个Id，可以看到下图，这两个SELECT查询就对应了1, 2 两个ID。
在这里插入图片描述
但是对于一些子查询，MYSQL的优化器会对SQL语句进行一定程度的改写，比如下面这个子查询有两个SELECT关键字，但是ID结果仅仅只有一个，这个情况和上面所说的join连接查询生成相同ID的情况类似，说明优化器对这种子查询做了子查询转join连接的改写优化，可以通过show warnings看出，对于in子查询来说，MYSQL对其做了大量的优化，如果看到这类子查询的ID相同，那么很可能优化器将in子查询改转换成了join。
在这里插入图片描述

对于下面这种子查询，需要先关闭MYSQL对衍生表的优化，在5.7之后默认会开启，会将派生表融合到外部查询中(from 后的查询会融入到外部查询之中)，可以看到对于下图来说，MYSQL同样的生成了两个不同的ID。

 set session optimizer_switch="derived_merge=off";

在这里插入图片描述

1.2.2.union的多SELECT

mysql> EXPLAIN select * from tb_order1 union select * from tb_order;

对于下面这种union子查询，对于每个SELECT都会生成一个对应的ID。但有所不同的是union查询会生成临时表，可以从第三条结果看出，生成的临时表为<union1, 2> ，将前面两个id 1， 2的查询进行了结果集合并，并且在使用了临时表来去重，id为null就表示。
在这里插入图片描述

mysql> EXPLAIN select * from tb_order1 union all select * from tb_order;

对于union all来说同样也是生成两个ID，但是对比union，union all查询并没有生成临时表，仅仅只是将两个id为1,2的结果集进行合并，返回给客户端。
在这里插入图片描述

1.2.3.查询包含子查询的情况

mysql> EXPLAIN  select (select  task_id from tb_order ) from order_task

在这里插入图片描述
当查询语句中每次出现一个SELECT关键字，MYSQL都会为其分配一个id，这个id对应的就是explain结果中的第一列。

2.SELECT TYPE列

子查询:也叫内部查询，当一个查询为另一个查询的条件时，这个查询就称为子查询。

通过上面的查询可以知道，一个复杂的查询SQL可以看做一个大的查询，里面包含了许多SELECT关键字，每个关键字SELECT相当于一个小的查询，from后面可以跟多张表，每个表在explain输出结果中对应一条记录，对于在同一个SELECT关键字中的表，id是相同的。
select_type就表示了每个小查询在整个查询中的查询类型。

● SIMPLE 简单查询，不使用union以及子查询
● PRIMARY 子查询最外层的复杂查询
● UNION UNION之后的select查询都是UNION子查询
● UNION RESULT 使用union建立临时表去重后的查询
● SUBQUERY SELECT后FROM之前的子查询
● DEPENDENT UNION 之后的select查询依赖于外部结果集
● DEPENDENT SUBQUERY select后并且依赖于外部结果的子查询
● DERIVED from条件之后的衍生表查询
● MATERIALIZED 物化表，当子查询结果集很大的时候，会先在内存中或者磁盘上建立临时表和相关索引，这个建立临时表的过程称为物化表
● UNCACHEABLE SUBQUERY 结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估，出现极少
● UNCACHEABLE UNION union之后的select子查询结果集不能被缓存，出现极少

● SIMPLE

● SIMPLE 简单查询，不使用union或者select后不包含子查询
在这里插入图片描述
连接也算简单查询的一种。

如果使用了union，查询会变成复杂查询。

● PRIMARY

● PRIMARY: 包含子查询的复杂查询中最外层的select查询，在union中最外层的查询(也就是union左边的查询)就是复杂查询了。
在这里插入图片描述

● UNION

● UNION :使用了union或union all 语句，除了在union/union all 左边的复杂查询，其它union之后的查询都是union查询。

在这里插入图片描述

● UNION_RESULT

● UNION_RESULT : 使用union建立合并去重后的临时表，对于该表的查询就叫 UNION_RESULT 。
在这里插入图片描述

● SUBQUERY

● SUBQUERY :
1.对于复杂查询SELECT之后from之前的查询，可以称为Subquery子查询

EXPLAIN select (select id from tb_order limit 1) from tb_order

在这里插入图片描述

利用存储过程往tb_order、tb_order1各插入一万条数据:
tb_order1:



drop procedure if exists tbOrder1;
delimiter $$
create procedure tbOrder(i int)
begin
    declare s int default 1;
    declare orderId BIGINT DEFAULT ROUND((RAND()*1000000) + 100000);
		DECLARE stat int(11) DEFAULT ROUND(RAND()*1);
		DECLARE taskId int(11) DEFAULT ROUND(RAND()*10000);

    while s<=i do
     set orderId = ROUND((RAND()*1000000) + 100000);
		 set	stat = stat + 1;
		 set 	taskId = taskId + 1;
        start transaction;
        INSERT INTO `tb_order1` (`order_id`, `order_status`, `task_id`) VALUES ( orderId, stat, taskId);
        commit;
        set s=s+1;
    end while;
end$$
delimiter

tb_order:


drop procedure if exists tbOrder;
delimiter $$
create procedure tbOrder(i int)
begin
    declare s int default 1;
    declare orderId BIGINT DEFAULT ROUND((RAND()*1000000) + 100000);
		DECLARE stat int(11) DEFAULT ROUND(RAND()*1);
		DECLARE taskId int(11) DEFAULT ROUND(RAND()*10000);

    while s<=i do
     set orderId = ROUND((RAND()*1000000) + 100000);
		 set	stat = stat + 1;
		 set 	taskId = taskId + 1;
        start transaction;
        INSERT INTO `tb_order` (`order_id`, `order_status`, `task_id`) VALUES ( orderId, stat, taskId);
        commit;
        set s=s+1;
    end while;
end$$
delimiter

alter table tb_order1 add index idx_order_id (order_id);

2.如果包含了in子查询的查询语句，并且是不相关子查询，需要被优化器改写的时候，如果in中数据量很大或者in子查询与外部连接的字段无索引，整个查询无法有效的利用索引，并且不满足转半连接semi-join的条件(可能是半连接成本比物化表高，子查询包含union或者group by，或者并列in + or,semi-join的结果是左表的子集，在右边中匹配到多条记录依然只返回一条)，这种SUBQUERY会先进行物化，再通过外层查询判断条件是否满足。

不相关子查询的查询方式都是先往子查询中取第一条数据，拿关联字段与外部查询匹配是否存在满足该字段的记录，需要多次匹配子查询，如果子查询涉及的记录数越多，性能越低下。针对这种情况MYSQL优化器尽量会将其转成半连接semi-join从而提升性能，如果不能转成半连接，再考虑物化表的方式，对子查询建立基于内存或者磁盘的临时表，将子查询结果的列写入到临时表并且去重，针对子查询的所有列建立唯一或者主键索引，如果结果集不大一般会建立memory的hash索引，如果结果集的字节数大于tmp_table_size或者max_heap_table_size，就会建立基于磁盘的B+树索引，在磁盘上建立临时表
可以转换semi-join的通用格式
SELECT …
FROM ot1 … otN
WHERE (oe1, … oeM) IN (SELECT ie1, …, ieM
FROM it1 … itK
[WHERE inner-cond])
[AND outer-cond]
[GROUP BY …] [HAVING …] [ORDER BY …]
对应的sj为:
SELECT …
FROM (ot1 … otN) SJ (it1 … itK)
ON (oe1, … oeM) = (ie1, …, ieM)
[AND inner-cond]
[WHERE outer-cond]
[GROUP BY …] [HAVING …] [ORDER BY …]

为了提高性能，可以将innodb_flush_log_at_trx_commit设置成0，或者2(不直接刷盘) ，如果开启了binlog可以将sync_binlog的值改成N，可以适当的调大日志的size，不管是缓冲区还是磁盘文件innodb_log_file_size磁盘文件(因为WAL机制是可以在事务没提交的时候将数据刷盘到ibd文件中的，有多种机制，比如磁盘文件的write pos指针写到了check point的位置，会强制刷盘)，innodb_log_buffer_size适当调大也可以减少刷盘次数(其中有个日志的刷盘机制就是DML达到了缓冲区的一半，会执行刷盘)
调用call tbOrder(10000); ，call tbOrder1(10000);

EXPLAIN select * from tb_order1 where id in (select task_id from tb_order) or tb_order1.order_id = "1023312";
show WARNINGS;

在这里插入图片描述
从执行的结果可以看出，id为2的SUBQUERY先进行了MATERIALIZED物化，对于不相关子查询来说，可以转成semi-join 执行的正常流程的是：1.先将子查询进行物化，物化后的得到去重之后临时表。2.取出临时表中第一条数据，根据取得的一条数据判断在外层查询中是否存在，直到将临时表中最后一条数据判断完毕，那么整个流程就走完了。也就是说如果转semi-join的查询并不需要将外层查询全表都扫描完，判断完内部临时表中的每个关联字段是否在外层查询中就算执行完了，如果有or的情况是无论如何外层查询都是需要走全表扫描的，因为不能确定不满足关联查询中的结果是否满足or的条件。

● DEPENDENT UNION、DEPENDENT SUBQUERY

● DEPENDENT UNION、DEPENDENT SUBQUERY:
在包含UNION 或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询，除了union前的第一个子查询，所有union后面的查询的select_type的值都是DEPENDENT UNION ，如下面的子查询:

EXPLAIN select * from tb_order where id in(select id from tb_order where id = 1 union select id from tb_order1 where id = 722);

在这里插入图片描述
从这个SQL上看，子查询中第一个select，也就是select id from tb_order where id = 1，这个子查询的select_type是DEPENDENT SUBQUERY，是依赖了外部查询的第一个in中的select子查询。而UNION之后的select查询select id from t_order1 where id = 722，这是UNION之后的子查询，select_type为DENPENDENT UNION，仅看查询类型同样也依赖了外部查询，但是从SQL语句上看这是一个不相关的子查询，那么优化器必然对这样的SQL进行了一定程度的改写。可以通过SHOW WARNINGS;来查看
在这里插入图片描述

● DERIVED

● DERIVED: 采用物化表的方式来执行包含派生表的查询，这种查询叫 DERIVED，select_type为DERIVED。SQL如下:
EXPLAIN select * from (select * from tb_order GROUP BY id) as drived where id > 1
在这里插入图片描述
从执行计划可以看出，id为2的记录代表的就是子查询的查询方式，它的select_type就为DERIVED，说明该子查询执行的方式是先进行了物化，id为1的查询table为《derived2》，查询的是id为2的派生表子查询物化之后的结果。

● MATERIALIZED

● MATERIALIZED: 当MYSQL优化器优化不相关子查询的时候，都会先将in中的子查询进行物化，建立基于内存或者磁盘的临时表，并且建立对应的索引，再进行去重，对应的tb_order1物化的结果对应id为2的记录，select_type为MATERIALIZED，第二行表示从物化后的临时表中取的相关结果，其id以及外层查询相同，意味着MYSQL内部进行了子查询转连接的优化。
explain select * from tb_order where id in (select order_id from tb_order1)
在这里插入图片描述
可以看到，这个SQL的执行过程是先将in中的子查询进行物化，再去重，对应的步骤就是id为2的记录。然后再查询物化后的临时表，对应id为1，table为<-subquery >的记录，再与外层查询进行关联，取外层查询的一条记录到内部子查询物化后的产生临时表判断记录是否存在，这种执行方式就跟join类似了。id相同就表示MYSQL优化器对生成的物化表<-MATERIABLIZED> 与tb_order的关联方式转换成了join的方式，

● UNCACHEABLE SUBQUERY

● UNCACHEABLE SUBQUERY: 对于外层查询来说，子查询不可以被物化，只能通过内部子查询的一条条结果集，逐行到外层查询中判断是否满足关联条件。

EXPLAIN select * from tb_order where id = (select id  from tb_order1 where order_id = @@sql_log_bin)

在这里插入图片描述

● UNCACHEABLE UNION

● UNCACHEABLE UNION: 对于外层查询来说，in子查询中的UNION后的select不可以被物化，与上面类似，这两种情况出现的特别少。
在这里插入图片描述

3.TABLE列

对于一个SQL查询，不管里面包含了多少个表，到了最后也是需要对每个表进行单表访问的，**EXPLAIN都会将对每个表的访问通过结果输出出来，每一条输出的结果对应某一个单表的访问方式，**对应的table就是访问的表名。
比如单表的访问:
在这里插入图片描述
join涉及到多表的访问:

当涉及到DERIVED衍生表的查询时，FROM前的SELECT对应的输出table列就会表现为:<.derived N>，代表查询需要依赖ID为N的衍生查询的结果。

当涉及到union关联子查询时，会创建临时表<.UNION M,N> ，为NULL的行表示的就是对临时表的访问，table显示<-UNION M,N>，其中M N代表的是使用了M,N来创建临时表，并且去重。
在这里插入图片描述

4.PARTITIONS列

这一行表示了分区信息，一般explain的结果展示都为NULL。

5.TYPE列

type列表示了一条SQL访问存储引擎表的访问类型或者访问类型，可以通过type列来查看一条SQL访问表的性能如何，type代表的是一条SQL访问表的一个重要指标，type性能从优高到到低为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all 。出现比较多的是system > const > eq_ref > ref > range > index > all ，一般来说查询的级别控制在range之上，最好可以达到ref级别。

● system

查询使用了主键或者唯一性索引与常数进行等值匹配，或者查询了MYISAM或者Memory引擎的表，并且只有一条记录与之匹配，这种查询类型称为system，这种查询类型只在MYISAM，MEMORY引擎的表存在。

CREATE TABLE `tb_order2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4

INSERT INTO `tb_order2` (`id`, `order_id`, `order_status`, `task_id`) VALUES ('1', '2', '2', '32121');

EXPLAIN select * from tb_order2 where id = 1;

在这里插入图片描述

● const

在innodb的单表访问中，查询的条件使用了主键或者是唯一性索引时，并且与常数进行等值匹配，只返回一条结果集给客户端，这种查询类型type为const。因为只匹配一行数据，所以查询的速度很快。
在这里插入图片描述
因为innodb的叶子节点数据都是以索引列创建的顺序来进行排列的，比如聚集索引的数据，就是按照主键id来排序，二级索引就是按照创建的二级索引顺序来进行排序。因为B+树的特性，树的高度一般为3-4，通过主键ID访问聚簇索引的B+树，仅仅需要几次io，速度很快。类似的如果使用唯一二级索引来定位数据，同样的速度很快:
对tb_order创建唯一索引:
alter table tb_order add UNIQUE idx_ord_status_task(order_id, order_status, task_id);

select * from tb_order where order_id = 858183 and order_status = 6 and task_id = 7060;

这个查询过程分成两步，第一步通过索引树idx_ord_status_task，找到唯一一条与where条件匹配的记录对应的ID，再通过ID到聚簇索引树上去找对应的记录。这种通过主键或者唯一索引定位一条记录的访问类型type为const，意思是常数级别，性能损耗忽略不计，可以通过show warnings看出，相当于常量表的查询。
在这里插入图片描述
不过这种const的情况，仅仅只有在单值唯一索引(主键)和常数等值匹配时才会出现，如果是联合唯一索引(主键)，那么需要每个列都和常数等值匹配才会出现const的情况。
但是对于唯一性二级索引(主键不能为null)来说，因为唯一性二级索引并没有限制null的数量，null值在叶子节点的存储要么都是聚集在左边部分的叶子节点上，或者右边的叶子节点上进行集中存储，所以如果使用了唯一性二级索引与null等值匹配，并不会出现const的情况，比如 select * from tb_order4 where task_id is null 其中task_id是唯一性索引，type就变成了ref。 select * from tb_order4 where task_id is not null ， type就变成了range。

● eq_ref

在innodb对表的访问中，对于join类型的连接查询来说，如果通过驱动表的查询结果集，取一条记录与被驱动表进行匹配，如果被驱动表的关联字段是主键或者是唯一索引，那么这种访问类型type就为eq_ref。如果使用的是多列唯一索引，那么就需要唯一索引的每个索引列都进行等值匹配。注意，被驱动表的关联唯一索引必须是非空的才会出现type为eq_ref的访问类型，如果不是非空的唯一索引访问类型会变成ref，因为唯一索引不限制null的数量。

如果A表关联B表，从A表中的查询结果集循环取出数据，一条条地到B表中作为过滤条件查询相关的数据，然后再合并结果，逐行增量的返回给客户端，那么A表就称为驱动表，B表就称为被驱动表。

CREATE TABLE `tb_order4` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_task` (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20031 DEFAULT CHARSET=utf8mb4

CREATE TABLE `tb_order5` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_task` (`task_id`)
) AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4

CREATE TABLE `tb_order6` ( -- join测试
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ord_status_task` (`order_id`,`order_status`,`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10031 DEFAULT CHARSET=utf8mb4

创建task_id分别可以为null和不为null的tb_order4和tb_order5，并且对照上面的存储过程插入1w条数据。
在这里插入图片描述
可以看到相同类型的SQL，相同的表结构和数据量，只是表中唯一索引task_id是否为null的不同，task_id可以为null的访问类型为ref，task_id不为null的访问类型为eq_ref。从下面这条SQL来看，tb_order是驱动表，tb_order5为被驱动表，执行器会取查询出的tb_order结果集的一条记录到tb_order5查询结果集进行合并，tb_order5的关联task_id是唯一且不为null的索引，所以type的访问类型为eq_ref，合并一条结果集然后返回给客户端，再取tb_order结果集的下一条记录，继续关联直至结束。

如果联合唯一索引进行关联，需要所有索引列进行全值匹配关联，并且联合索引的每一列都不为空，才会出现type为eq_ref的情况。
alter table tb_order4 add UNIQUE idx_ord_status_task(order_id, order_status, task_id);
在这里插入图片描述

tb_order6和tb_order4唯一的区别就是联合索引部分task_id，tb_order6中的task_id不为null，tb_order4的task_id可以为null。结果就是不能为null的联合唯一索引的关联是eq_ref，而可以为null的联合唯一索引的关联就是ref。

● ref

当通过普通的二级索引列和常量进行等值匹配的时候来访问某个表时，可能一次性可以匹配到多个值，那么对于这种查询情况type就为ref。
这种访问类型本质上也是一种索引的快速访问，它返回的是所有匹配单独值的行，可能回匹配到多个
符合条件的行，它属于查找和扫描的混合体(查找: 通过where条件从索引树的根节点遍历找到叶子节点的记录，扫描:从叶子节点定位到一条记录之后再匹配下一条是否满足条件)。

为tb_order添加一个普通二级索引idx_task_id：
alter table tb_order add index idx_task_id(task_id);

EXPLAIN select * from tb_order where task_id = 12021;

在这里插入图片描述
对于这种查询，我们可以选择全表扫描来逐一比对记录是否满足搜索条件，也可以通过二级索引来快速的定位主键id的值，再通过回表操作，到聚簇索引树上定位到完整的数据记录。

由于普通索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引的查询代价取决于等值匹配到的二级索引的记录条数。如果匹配到的记录数越多，那么根据CBO成本计算，可能查询二级索引树以及回表的成本会很高，MYSQL优化器会选择全表扫描的方式来执行SQL。如果匹配到的记录比较少，那么回表的代价会很低，MYSQL就会选择走索引的方式来执行SQL。这种使用普通索引和常量等值匹配，并且选择了采用二级索引来执行SQL的访问方式type就为ref。
对于普通的二级索引，通过索引列进行等值匹配可以匹配到多条连续的记录，而不是跟主键或者唯一索引一样定位到唯一一条记录之后就停止匹配，除非是唯一性二级索引与null等值匹配，这种匹配也会出现ref的情况。所以ref性能上来看会比const差一些，返回多条结果集，回表的次数会稍微多一些，回表的成本会高一些，相反地，如果返回的记录比较少，这种SQL性能还是比较高的。
需要注意的有两种情况:
1.如果是唯一性二级索引与null匹配，where index_key is null / index_key is not null，最高能达到的访问方式就是ref，不管是唯一性二级索引或者是普通的二级索引，它们的索引列对包含null值的数量不限制，匹配的null值记录就不是唯一一条了，那么type就不可能达到const，而是返回多条记录的ref。
2.如果是多列的二级索引，不管是唯一性联合索引还是普通联合二级索引，只要左边的连续列是与常数进行等值比较就可能是ref，
比如有多列唯一性二级索引的select * from tb_order where order_id = 100063。或者select * from tb_order where order_id = 100063 and order_status = 2485 。或者是多列普通二级索引的全职匹配访问方式都是ref。
在这里插入图片描述
从图中可以看出，不管是使用了唯一性联合索引前缀idx_ord_status_task中的order_id，还是普通索引task_id is null或者和常数等值匹配，tb_order4中的唯一性单值索引task_id和null值匹配，这些访问类型都是const。但是如果联合多列索引中，某个列不是等值匹配了，那么访问类型就不是ref了。
在这里插入图片描述
当然也可能出现在join中，如果被驱动表关联的字段不是唯一性索引或者主键，那么type也可能为ref。

● fulltext

创建相关的表，并且建立全文索引，ft_product_name。


CREATE TABLE `tb_order8` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) DEFAULT NULL,
  `pro_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_task_id` (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10031 DEFAULT CHARSET=utf8mb4

ALTER TABLE `tb_order8` ADD FULLTEXT INDEX ft_product_name(`pro_name`);
INSERT INTO `mysql`.`tb_order8` (`id`, `order_id`, `order_status`, `task_id`, `pro_name`) VALUES ('1', '2', '0', '324', 'adsaddsa');
--插入测试数据
drop procedure if exists tbOrder8;
delimiter $$
create procedure tbOrder8(i int)
begin
    declare s int default 1;
    declare orderId BIGINT DEFAULT ROUND((RAND()*1000000) + 100000);
		DECLARE stat int(11) DEFAULT ROUND(RAND()*1);
		DECLARE taskId int(11) DEFAULT ROUND(RAND()*10000);
		DECLARE proName VARCHAR(50) DEFAULT "asce";
    while s<=i do
     set orderId = ROUND((RAND()*1000000) + 100000);
		 set	stat = stat + 1;
		 set 	taskId = taskId + 1;
		 set  proName = SUBSTR(MD5(RAND()), 1, 10);
        start transaction;
        INSERT INTO `tb_order8` (`order_id`, `order_status`, `task_id`,`pro_name`) VALUES ( orderId, stat, taskId, proName);
        commit;
        set s=s+1;
    end while;
end$$
delimiter

call tbOrder8(10002)

全文索引的布尔搜索模式
【+】----------必须包含此字符串
【-】----------必须不包含此字符串
【“ ”】--------双引号内作为整体不能拆词
【> 】--------提高该词的相关性，查询的结果靠前
【< 】--------降低该词的相关性，查询的结果靠后
【*】---------通配符，只能接在词后面
等

比如 select * from tb_order8 where MATCH(pro_name) AGAINST(‘+adsad*’ in boolean mode)，查询包含adsad为前缀的记录。这种访问类型为fulltext，性能比ref低一些。
在这里插入图片描述

● ref_or_null

如果本来使用索引进行等值匹配的类型就是ref，但是又想把null的记录也查询出来，这样的访问类型就是ref_or_null，需要查询的数据量会比ref的多一些，回表的成本会更高，性能会比ref差一点。但是需要注意，进行匹配的索引列必须是可以为null才会出现这种类型，否则只会为ref。
在这里插入图片描述
这个查询相当于将is null和 2343的两个记录范围查询出来，然后再根据查询出来的id进行回表。

● index_merge

一般情况下，如果一个表存在多个索引，一个查询就算使用到了多个索引，也只会走其中一个索引。但是某些情况可能出现索引合并的现象。比如or 或者and连接的等值匹配，可能会走索引合并。
在这里插入图片描述

● unique_subquery

如果是in子查询，并且是相关子查询，这种查询不能转换成semi-join的形式进行优化，MYSQL优化器需要将SQL改写成exist的形式更利于走索引，并且子查询与外部查询涉及in关联的索引是主键或者非空唯一索引，那么这种查询可以称为是unique_subquery。

在这里插入图片描述

其中tb_order5中的task_id为非空唯一索引，那么优化器改写成exist后就可以使用唯一索引task_id作为两个表查询的关联条件了。

● index_subquery

index_subquery和unique_subquery类似，MYSQL优化器同样会将该相关in子查询改写成exist形式，从而好利用索引，只不过index_subquery指的是改写后的子查询与外部查询in关联的字段不是主键，而是普通索引或者可为空的唯一索引。
在这里插入图片描述

● range

如果使用索引访问了区间的记录，不管是主键还是唯一索引，亦或是非唯一索引，只要是通过索引的方式访问数据并且不是等值匹配，即出现了< , > , like between, !=等相关的关键字查询，那么type就会输出为range，代表着访问的方式是索引的区间范围访问，如果通过这种方式查询到的数据量比较小，通过CBO成本计算规则来说，可能会比全表扫描性能高，如果查询到的数据量比较大，意味着回表的成本会很高，优化器会选择以全表扫描的方式来替代索引访问。

EXPLAIN select * from tb_order where order_id > 12; -- order_id为联合索引idx_ord_status_task的前缀列
EXPLAIN select * from tb_order8 where task_id > 12; -- task_id为表tb_order8中的普通索引
EXPLAIN select * from tb_order where id > 12;  -- id为tb_order的主键
EXPLAIN select * from tb_order where id < 12; -- id为tb_order的主键
EXPLAIN select * from tb_order where id BETWEEN 12 and 20;  -- id为tb_order的主键
EXPLAIN select * from tb_order8 where pro_name like "adsad%";  -- pro_name为tb_order8的普通索引
EXPLAIN select * from tb_order where id != 12;  -- id为tb_order的主键

在这里插入图片描述

有一种情况比较特殊，比如EXPLAIN select * from tb_order where task_id > 12;，在表中有索引idx_ord_status_task，在这里出现了> ，但是type并不会显示range，而是直接走二级索引全索引扫描，这棵索引树包括了表中所有的列，这种查询执行完扫描就交给执行引擎进行条件过滤就好了，不需要进行回表，这也是通过CBO来计算的，MYSQL认为走全表扫描或者全索引扫描成本会比先到二级索引查找id再到聚簇索引回表查数据消耗的成本更低。
在这里插入图片描述
从上面可以看出，同一个SQL同样的索引，只是SQL条件的值不同，MYSQL利用索引的方式也会不一样。具体的走什么样的索引主要是由MYSQL的优化器来决定的，主要依据就是CBO成本分析，统计预估之后，再计算CPU,IO的成本。

在这之前的type访问类型都是可以或多或少的利用索引的快速访问定位数据的特性，也就是从根节点根节点开始的查找，从这里之后的type访问类型就是从叶子节点开始对叶节点的数据进行全部扫描。

● index

如果使用了覆盖索引，需要查找的结果集数据是通过二级索引树的叶子节点来全量获取的，这种访问类型称为index。
在这里插入图片描述

● all

全表扫描，必须从聚簇索引的第一个叶子结点的第一条数据开始，逐条遍历获取数据是否满足条件，一直到最后一个叶子结点的最后一条。
在这里插入图片描述

6.possible keys和key列

在EXPLAIN中语句输出的执行计划中，possible keys列表示在某个查询语句中，对某个表执行查询可能利用到哪些索引，key表示的是对某个表执行SQL实际利用到的索引，如下图:可能利用到的索引有idx_ord_status_task, idx_task_id，实际利用的索引有idx_ord_status_task，这就是根据CBO基于成本的优化器来计算各个可能利用的索引以及全表扫描的成本高低，从而判断使用哪个索引。

在这里插入图片描述
有一种特殊情况: 可能碰到possible kyes为空，而key不为null的情况，如:

EXPLAIN select * from tb_order where  order_status > 12;

在这里插入图片描述
这种情况表示查询优化器并没有利用索引的快速定位的特性(自根节点起快速定位一个小范围的叶子节点数据)，而是以扫描的方式从叶子结点的最左元素扫描到叶子节点的最后一位元素，type为index就可以看出。

7.ref列

表示当前SQL使用了索引进行等值匹配时，利用到的某个表中的哪个列，常常展现在访问方法为const，eq_ref，ref ，ref_or_null，unique_subquery，index_subquery其中之一的时候。
比如const:表示与id列匹配的对象是一个常数。
在这里插入图片描述
或者是eq_ref或者ref中的join，在被驱动表ref列表现为使用了驱动表t1中的order_id字段:

或者使用了函数，ref表现为func.

8.key_len列

key_len表示当前优化器决定使用某个索引执行查询时，使用该索引记录的最大长度，而不是真实的索引记录的长度。对于联合索引来说，key_len可以表示使用了多少个联合索引列。
比如: 该表使用了utf8mb4的编码，pro_name类型为varchar(N)，其中N为50 ，所以4 * 50 = 200，因为varchar是可变长字符串，最长会用2个字节来表示它的字节大小，并且可以为null，需要1个字节来表示，所以key_len为203。
注意:因为可变长字符串在行格式中，最大用2个字节来表示varchar类型列的字节大小，并不是它的长度，所以2个字节16位最大可以表示2^16 - 1 = 65535字节大小的varchar类型列，所以varchar(N)中的N代表的是字符串的长度，由于utf8mb4会用4个字节表示一个字符，65535 / 4 = 16383
在这里插入图片描述

key_len计算规则如下：
● 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个字符占3个字节，如果是utf8mb4，那么一个字符占用4个字节。

InnoDB有它的一套规则，我们引入W、M和L这几个符号：
假设某个字符集中「最多」需要W字节来表示一个字符
utf8mb4字符集中的W就是4
utf8字符集中W就是3
gbk字符集中的W就是2
ascii字符集中的W就是1。

对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节）所以这个类型能表示的字符串最多占用的字节数就是M × W。

假设它实际存储的字符串占用的字节数是L。
◆ char(n)：如果字符长度是n，在utf8编码情况下占用3n 字节
◆ varchar(n)：如果字符长度是n，在utf8编码情况下占用3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
● 数值类型
◆ tinyint：1字节
◆ smallint：2字节
◆ int：4字节
◆ bigint：8字节　
● 时间类型
◆ date：3字节
◆ timestamp：4字节
◆ datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的768字符提取出来做索引，超过的部分称为溢出页。MYSQL默认的格式是Dynamic，对于数据溢出的处理往往都是开辟一个新页面并在原始记录页的真实数据列处用20个字节存储新页面的地址，并不会存储768字节的前缀字符，和Compressed格式类似，如果是Compact和~~Redundant~~ 格式的话只会存储前面的768字节，后面溢出的字符串就会开辟新的页存储并且在原始的页面相关列中存储新页面的地址。

SHOW VARIABLES LIKE “innodb_default_row_format” 查看MYSQL默认使用的行格式

9.rows列

如果查询优化器决定使用全表扫描对某个表来进行查询的时候，就代表执行引擎预计需要扫描的记录行数，如果使用了索引来执行查询，那么就代表执行引擎需要扫描的索引行数。
在这里插入图片描述

从上图的执行过程可以看出，以全表扫描的方式执行，满足task_id > 1的需要扫描10093条记录，而以走索引的方式执行，满足task_id > 98877的预计只有一条记录满足条件。

10.filtered列

这一列一般表示，如果以为type的访问方式过滤出/扫描了多少条结果集，满足剩余条件(与type访问方式不相关的条件)的记录预计需要扫描多少条的百分比。
在这里插入图片描述
这里就表示全表扫描了10093条记录，满足剩余条件task_id > 1的记录预计还需要扫描百分之50，也就是10093 * (50/100)。

这里表示的就是以task_id为索引的方式访问，满足task_id > 14331条件需要扫描了3条记录，其中满足order_id = 8283的占了百分之10.
对于单表查询来说，这个列并没有很大的意义，但是对于多表的连接查询来说，filtered列可以计算出访问了多少次的被驱动表(也就是扇出数，后面会提及到)
在这里插入图片描述
从图中可以看出，如果想要计算出访问了多少次被驱动表也就是扇出数，就可以用9907 * (37.65 / 100) =3729.98，其实也容易理解，全表扫描驱动表预计会扫描9907行，需要再取9907条记录中百分之37.65的task_id到被驱动表根据索引关联，每一个驱动表的task_id在被驱动表都只能在被驱动表找到一条记录匹配，预计需要从驱动表扫描3729条记录带入到被驱动表查询这么多次。

10.Extra列

Extra列就是用来说明一些SQL的额外信息的，我们可以通过这些额外信息来更准确地理解MYSQL到底将如何执行给定的查询语句，MYSQL提供的额外信息有很多种类，几十个，下面仅仅只介绍常见的Extra列类型。

● No tables used

当前查询语句from子句没有涉及到表的查询时，或者select * from dual从dual空表中获取数据的时候。会显示No tables used.
在这里插入图片描述

● Impossible where

当执行SQL中where条件恒为false，Extra列会显示impossible where.
在这里插入图片描述

● No matching row in const table

当执行的SQL查询的where条件不可能包含有查询的结果集时，会显示该列。
在这里插入图片描述

● No matching min/max row

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

● Using index

当执行的查询SQL，查询的结果集的列和where条件都在同一颗索引树之上，也就是一次在存储引擎层的过滤就能获得到结果，不需要serve层再次进行过滤，更需要回表操作，这种情况下Extra列就会显示using index ，也是我们常常说的覆盖索引。
在这里插入图片描述

● Using index condition

在Extra列出现了这种情况，在MYSQL中有一个特有名词叫ICP(index condition push)，在MYSQL5.6之前并不支持ICP，Server层和引擎层之间是不允许传递条件的，也就意味着一旦在server层确定了使用哪些索引条件那就无法再进一步地使用其它索引条件进行过滤了。从实现的角度来看，在MYSQL5.6之前只有index key(用于确定SQL查询在索引中的一个连续范围，包括了起始范围和结束范围，也叫index first key和index last key，用于确定索引搜索的上界[<, <= , = ，用index last key收集]和下界[>, >= , =，用index first key收集]) 和 table filter(用于索引条件筛选完之后的回表操作，不能使用索引的就只能进行回表了) ，使用完index key进行索引的估计匹配，确认范围之后，剩余不能走索引的条件就直接使用table filter进行回表了。
注意: index key 至少包含一个起始范围或者终止范围，通过index key 确定的范围在执行计划中通过explain中的key_len列可以很好地表示出来，表示优化器通过index key的匹配规则，确认了哪些索引或者索引的哪些列是可以被使用的。

CREATE TABLE `tb_order9` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) DEFAULT NULL,
  `pro_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ord_status_task` (`order_id`,`order_status`,`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8mb4

TRUNCATE tb_order9
drop procedure if exists tbOrder9;
delimiter $$
create procedure tbOrder9(i int)
begin
    declare s int default 1;
    declare orderId BIGINT DEFAULT ROUND((RAND()*1000000) + 100000);
		DECLARE stat int(11) DEFAULT ROUND(RAND()*1);
		DECLARE taskId int(11) DEFAULT ROUND(RAND()*10000);
		DECLARE proName VARCHAR(50) DEFAULT "asce";
    while s<=i do
     set orderId = ROUND((RAND()*1000000) + 100000);
		 set	stat = stat + 1;
		 set 	taskId = taskId + 1;
		 set  proName = SUBSTR(MD5(RAND()), 1, 10);
        start transaction;
        INSERT INTO `tb_order9` (`order_id`, `order_status`, `task_id`,`pro_name`) VALUES ( orderId, stat, taskId, proName);
        commit;
        set s=s+1;
    end while;
end$$
delimiter

call tbOrder9(10002)

如果有索引idx_ord_status_task (order_id,order_status,task_id)，在执行select * from tb_order9 where order_id > 1099088 and task_id = 21332的时候，执行步骤就是:
1.MYSQL优化器会根据index key确定了起始范围之后，执行器会调用引擎层的API，先从idx_ord_status_task 二级索引树上根据order_id找到对应的id范围记录，返回给执行器，因为二级索引只根据创建的索引顺序排序，所以这些id都是无序的。
2.因为task_id无法走索引，不满足最左前缀原则，执行器通过上一步骤获取到的id进行table filter回表操作(因为是select *，查询的结果集不都在索引树上 )，再将获取到的结果返回server层根据task_id过滤出满足所有where条件的结果。
在MYSQL5.6版本之前，通过index first key 和index last key判定的索引范围记录，直接回表读取完整记录，再到server层筛选无法走索引的where条件的记录得到结果，很明显这样做带来的后果就是回表的数量会比较大。
在MYSQL5.6版本之后，提出了ICP优化技术，从上面的SQL语句来看，task_id和order_id是在同一颗索引树上的，在5.6版本之后提出了index filter的概念，意思是将server层的index filter从table filter抽离了出来，下降到了引擎层面，对于index key确定的索引范围，还有一些不符合where条件的，本来是通过table filter确定的直接回表的数据，如果这些条件可以使用索引过滤，那么会提前在引擎层面的index filter过滤一次，将位于同一颗索引树的索引的where条件使用index filter进一步筛选，然后才进行table filter。
ICP技术也就是index filter 技术，并没有很新奇的点，这么做的好处就是，减少了回表的次数，减少了server层和引擎层面的交互次数。

1). index key
用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件，被称之为Index Key。由于一个范围，至少包含一个起始与一个终止，因此Index Key也被拆分为Index First Key和Index Last Key，分别用于定位索引查找的起始，以及索引查询的终止条件。也就是说根据索引来确定扫描的范围。
2). index filter
在使用 index key 确定了起始范围和介绍范围之后，在此范围之内，还有一些记录不符合where 条件，如果这些条件可以使用索引进行过滤，那么就是 index filter。也就是说用索引来进行where条件过滤。
3). table filter
where 中的条件不能使用索引进行处理的，只能通过访问表来进行条件过滤了。

使用ICP之后，整个SQL的执行过程就变为:
1.MYSQL优化器会根据index key确定了起始范围之后，执行器会调用引擎层的API，先从idx_ord_status_task 二级索引树上根据order_id找到对应的id范围记录。
2.对于满足order_id>1099088的记录，先不着急回表，而是在索引树上判断是否满足task_id = 21332，如果满足这些条件就返回给执行器，因为二级索引只根据创建的索引顺序排序，所以这些id都是无序的
3.执行器通过上一步骤获取到的id进行table filter回表操作(因为是select *，查询的结果集不都在索引树上 )，再将获取到的结果返回server层，过滤那些无法走索引的条件。
后面会有更详细的文章针对ICP进一步探讨。

● Using where

结论1: 当我们查询的结果集不在索引树或者不全在索引树之上时候，并且如果不是全表扫描的情况下需要进行回表，还需要根据非索引条件到server层进行过滤。侧重是否在索引树上。
结论2: 无论查询的字段是否在索引树之上，只要没有使用到索引，那么Extra就会显示using where。侧重是否使用索引。
其实上面这两个结论或多或少都有一些错误，常见的出现where的情况如下，查询的列(select * )不在索引之上，并且未使用到索引条件进行过滤。
在这里插入图片描述
反证结论1错误: 如下图所示，查询的列不管在还是不在索引树idx_ord_status_task之上，都可能出现using where的情况

在这里插入图片描述
反证结论2错误: 从图中可以看出，而该SQL使用了order_id索引，但是pro_name字段是无索引的，MYSQL需要在server层根据pro_name字段进行过滤。

task_id和prod_name都是索引字段，但是由不同的索引树构建。

所以从上面的一些错误的结论反证可以得出一条正确的结论:
1.在没有使用到索引的情况下，进行全表扫描，如果在server层使用了字段过滤，不管是非索引还是索引，那么就会出现Extra为using where的情况。
2.使用了索引的情况下，如果在Server层还使用了其它的非索引条件字段或者使用了不在同一棵索引树的其它索引过滤，那么Extra为using where。
所以，在Extra列中，出现了using where的现象和全表扫描，查询的结果集是否在索引树上，条件是否走了索引无关，从官方的话术表明的意思是using where只表示MYSQL是否在server层面进行了非索引条件/非同索引条件字段的过滤

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_extra
在这里插入图片描述

● Using join buffer (Block Nested Loop)

如果使用了连接查询执行过程中，当被驱动表的关联字段为非索引字段的时候，会使用BNL算法，将驱动表满足where条件的结果集取出，放到一个名为join buffer的内存块中，如果放不下，会将驱动表的结果集数据分segment段放到join buffer中，当join buffer 快满的时候，就开始取被驱动表的一条数据到内存中比对，满足条件则加入到返回的结果集列表中，逐条返回给客户端，再重复取被驱动表的下一条数据，直到扫描完被驱动表的最后一条数据为止。假设驱动表N条数据，被驱动表M条数据，不考虑预读的情况下，整个过程对驱动表来说进行了N次磁盘IO，对被驱动表来说进行了M次磁盘IO，整个过程访问了驱动表和被驱动表M+N次，内存判断了M * N次。

● Select tables optimized away

如果使用了聚合函数，查询的
在这里插入图片描述

Dog•Li

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【MYSQL数据库二】EXPLAIN、联合索引相关优化场景以及常用hint

在前几篇文章陈述了一些索引的基本数据结构以及SQL执行的流程，在实际开发的情况下，很多时候遇到性能比较低下的SQL都需要借用MYSQL给我们提供的工具分析，对MYSQL的优化过程或者执行过程进一步地分析，比如Explain,profile 工具等，本文仅针对Explain概述。执行计划简介有了慢查询之后，就需要对慢查询进行分析。从上一篇文章大概知道了SQL大体执行流程，一条SQL在执行的过程中查询优化器会对各个索引的访问成本进行计算，最后选择最优MYSQL认为最优的索引并且生成执行计划，这个执行计划展示
复制链接

扫一扫

专栏目录