mysql explain详解，type性能排名system＞const＞eq_ref＞ref＞fulltext＞range＞index＞ALL，索引失效情况总结(未完)

1.explain说明

在这里插入图片描述

序号列名描述
1id在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
2select_typeSELECT关键字对应的那个查询的类型
3table表名
4partitions匹配的分区信息
5type ★针对单表的访问方法
6possible_keys可能用到的索引
7key实际上使用的索引
8key_len ★实际使用到的索引长度
9ref当使用索引列等值查询时，与索引列进行等值匹配的对象信息
10rows ★预估的需要读取的记录条数
11filtered某个表经过搜索条件过滤后剩余记录条数的百分比
12Extra ★一些额外的信息

1.1.type说明

  1. 从最好到最坏依次如下表；
  2. 加粗的是比较重要的；
  3. SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）
序号列名描述
1.1system当表中只有一条记录并且该表使用的在储引擎的统计数据是精确的，比如MyISAM、Memory
1.2const当我们根据主键或者唯一索引列与常数进行等值匹配时，对单表的访问方法就是const
1.3eq_ref在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq ref
1.4ref当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
2.1fulltext略
2.2ref_or_null当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null
2.3index_merge单表访问方法时在某些场景下可以使用Intersection、Union、sort-Union这三种索引合并的方式来执行查询
2.4unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到唯一索引进行等值匹配的话。
2.5index_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到普通索引进行等值匹配的话。
2.6range如果使用索引获取某些范围区间的记录，那么就可能使用到`range '访问方法
3.1index当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index
3.2ALL未使用索引（各种索引失效情况）的情况

1.1.1.总结sql

-- 结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）
-- 1.等值匹配
-- 1.1.`system`。当表中只有一条记录并且该表使用的在储引擎的统计数据是精确的，比如MyISAM、Memory
explain select * from demo where id = '1';
-- 1.2.`const`。当我们根据主键或者唯一索引列与常数进行等值匹配时，对单表的访问方法就是`const`
explain select * from demo where id2 = '1';
-- 1.3.`eq_ref`。在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是`eq ref`
explain select * from demo a,demo b where a.id2  = b.id2;
-- 1.4.`ref`。当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
explain select * from demo where id3 = '1';
explain select * from demo where id2 is null;                               -- 对比上面的1.2.
explain select * from demo a,demo b where a.id3 = b.id3;                    -- 对比上面的1.3.
-- 2.模糊、范围查询等
-- 2.1.`fulltext`略
-- 2.2.`ref_or_null`。当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法就可能是`ref_or_null`
explain select * from demo where id2 = '1' or id2 is null;		            -- 如果字段有not null约束，则为const（唯一索引时）、ref（普通索引时）
explain select * from demo where id3 = '1' or id3 is null;		            -- 如果字段有not null约束，则为const（唯一索引时）、ref（普通索引时）
-- 2.3.`index_merge`。单表访问方法时在某些场景下可以使用`Intersection`、`Union`、`sort-Union`这三种索引合并的方式来执行查询
explain select * from demo where id2 = '1' or id3 = '2';
explain select * from demo where id2 = '1' or id3 in ('2','3');
-- 2.4.`unique_subquery`。是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询转换为`EXISTS`子查询，而且子查询可以使用到唯一索引进行等值匹配的话。
explain select * from demo a where id4 in (select id2 from demo where id4 = a.id4) or id4 ='1';
-- 2.5.`index_subquery`。是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询转换为`EXISTS`子查询，而且子查询可以使用到普通索引进行等值匹配的话。
explain select * from demo a where id4 in (select id3 from demo where id4 = a.id4) or id4 ='1';
-- 2.6.`range`。如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range '访问方法
explain select * from demo where id in ('1','2');
explain select * from demo where id > '1';
explain select * from demo where id between '1' and '2';
explain select * from demo where id like '1%';						        -- 注意：id为数字类型，索引失效
-- 3.全表扫描
-- 3.1.`index`。当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`
explain select distinct id3 from demo;                                      --  如果id3有大量重复数据时，则为range
explain select id from demo;
-- 3.2.`ALL`各种未使用索引（索引失效情况）的情况
explain select distinct id4 from demo;
explain select id4 from demo;
explain select * from demo where id is not null;

1.1.2.造数据测试（java）

1.1.2.1.编写代码
  1. 建表
drop table if exists demo;
create table demo
(
    `id`  bigint not null auto_increment comment '8个字节。单列索引、聚簇索引（一级索引）、主键索引。',
    `id2` bigint default null comment '8个字节。单列索引、非聚簇索引（二级索引）、唯一索引。',
    `id3` bigint default null comment '8个字节。单列索引、非聚簇索引（二级索引）、普通索引。',
    `id4` bigint default null comment '8个字节。无索引。',
    primary key (`id`),
    unique key `id2` (`id2`),
    key `id3` (`id3`)
) default charset = gbk comment ='演示';
  1. 批量插入1000条数据。详细java代码见码云 lishuoboy-mysql
@Slf4j
@RestController
public class IndexController {
    @Autowired
    DataSource ds;
    @Autowired
    DemoService demoService;

    @GetMapping("/saveBatchDemo/{count}")
    Object saveBatchDemo(@PathVariable int count) {
        TimeInterval timeInterval = new TimeInterval();
        List<Demo> demoList = new ArrayList(count);
        for (long i = 1; i <= count; i++) {
            Demo demo = Demo.builder()
                    .id(i).id2(i).id3(i).id4(i)
                    .build();
            demoList.add(demo);
        }
        //持久化。
        demoService.saveBatch(demoList);
        log.warn("总用时=={}s", timeInterval.intervalSecond());
        return demoService.count();
    }
}
  1. 发送请求

http://localhost:8080/saveBatchDemo/1000

2. 索引失效

-- 对于所有本应该走索引的，当mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描
-- 1. =走索引，!=、<>也走索引
explain select id2 from user where varchar_short4  = '0';            						--  = 范围小，肯定走索引。ref。key_len=11
explain select id2 from user where varchar_short4  = '0000';										--  = 范围大，肯定走索引。ref
explain select id2 from user where varchar_short4 != '0000';										-- != 范围小，    走索引。range
explain select id2 from user where varchar_short4 != '0';												-- != 范围大，  不走索引。
-- 2. <、>、>=、<= 和 between、not between均走索引
explain select id2 from user where varchar_short4 <=  '0';   								  	-- <= 范围小，  走索引。range
explain select id2 from user where varchar_short4 <= '0000';   									-- <= 范围大，不走索引。
explain select id2 from user where varchar_short4 	  between 'a' and 'z';  		--     between 范围小，  走索引。range
explain select id2 from user where varchar_short4 		between '0' and 'z';			--     between 范围大，不走索引。
explain select id2 from user where varchar_short4 not between '0' and 'z';			-- not between 范围小，  走索引。range
explain select id2 from user where varchar_short4 not between 'a' and 'z';			-- not between 范围大，不走索引。
-- 3.in走索引，not in走索引，in 1个值时同=。in 1个变量，自动转为=。
explain select id2 from user where varchar_short4     in ('0');									--     in 范围小，肯定走索引。ref
explain select id2 from user where varchar_short4     in ('0000');							--     in 范围大，肯定走索引。ref
explain select id2 from user where varchar_short4     in ('0','1');							--     in 范围小，    走索引。range
explain select id2 from user where varchar_short4     in ('0000','1');					--     in 范围大，  不走索引。
explain select id2 from user where varchar_short4 not in ('0000');	   					-- not in 范围小，    走索引。range
explain select id2 from user where varchar_short4 not in ('0');			    				-- not in 范围大，  不走索引。
-- 4.exists走索引，not exists不走索引【需细化】
explain select id2 from user o where exists (select 1 from user where id =o.id and varchar_short4 ='0');		--     exists范围小，    走索引。ref
explain select id2 from user o where exists (select * from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0');	--     exists范围小，   走索引。ref
explain select id2 from user o where exists (select * from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0000');	--     exists范围大，  不走索引。
explain select id2 from user o where not exists (select id2 from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0000');		-- not exists肯定不走索引。
-- 5.null,同=
explain select id2 from user where varchar_short6 is     null;									-- is     null 范围小，  走索引。ref
explain select id2 from user where varchar_short5 is     null;									-- is     null 范围大，  走索引。ref
explain select id2 from user where varchar_short5 is not null;									-- is not null 范围小，  走索引。range，
explain select id2 from user where varchar_short6 is not null;									-- is not null 范围大，不走索引。
-- like 
explain select id2 from user where varchar_short4     like 'a';									--        like前缀 范围小，  走索引。range
explain select id2 from user where varchar_short4     like '0000';							--        like前缀 范围大，不走索引。
explain select id2 from user where varchar_short4     like 'a%';								--        like前缀 范围小，  走索引。range
explain select id2 from user where varchar_short4     like '0000%';							--        like前缀 范围大，不走索引。
explain select id2 from user where varchar_short4     like '%a';								--        like后缀 范围小，不走索引。
explain select id2 from user where varchar_short4 not like '0000%';							--        like前缀 范围小，不走索引。
-- distinct
explain select distinct varchar_short4 from user;																-- 范围小，走索引。range
explain select distinct varchar_mid2 from user;																						-- 范围大，走索引。range
explain select distinct varchar_short3 from user;
-- order by
explain select id2 from user order by varchar_short4 limit 1000;								-- 走索引。index
explain select id2 from user order by varchar_short3 limit 1000;
-- group by
explain select varchar_short4 from user group by varchar_short4 limit 1000;			-- 走索引。range
explain select varchar_short3 from user group by varchar_short3 limit 1000;
-- having。having是group by后，属于聚合函数，肯定不走索引
explain select varchar_short2 from user group by varchar_short2 having varchar_short2 = '00';			-- 不走索引
explain select varchar_short1 from user group by varchar_short1 having varchar_short1 = '00';			
-- and/or
explain select id2 from user where varchar_short4 = '0' and varchar_short5 = '0';		-- 走索引。index_merge 。key_len=11,11
explain select id2 from user where varchar_short4 = '0' or  varchar_short5 = '0';		-- 走索引。index_merge 。key_len=11,11
-- 类型隐式转换
explain select id2 from user where varchar_short4 = '11';						-- 无类型转换，  走索引
explain select id2 from user where varchar_short4 =  11 ;						-- 有类型转换，不走索引。对于varchar，查的数据也是错的
explain select id2 from user where id = '11';												-- 无类型转换，  走索引
explain select id2 from user where id =  11;												-- 无类型转换，  走索引
-- 函数、运算、两列比较
explain select id2 from user where lower(varchar_short4) = '00';		-- 函数，不走索引
explain select id2 from user where varchar_short4 || '0' = '00';		-- 函数，不走索引
explain select id2 from user where id + 1 = 100;										-- 运算，不走索引
explain select id2 from user where id = 100 - 1;										-- 不运算，走索引
explain select id2 from user where varchar_short4 = varchar_short5;	-- 两列比较，不走索引
-- 联合索引。最左匹配原则。与where 后面的字段顺序无关，sql优化器会自动调整。其它情况与单字段索引类type、失效原则一致
explain select id2 from user2 where varchar_short7 = '0' and varchar_short8 = '0' and varchar_short9 = '0';				--   走索引,key_len=33
explain select id2 from user2 where varchar_short9 = '0' and varchar_short8 = '0' and varchar_short7 = '0';				--   走索引,key_len=33
explain select id2 from user2 where varchar_short9 = '0' and varchar_short8 = '0' and varchar_short7 = '0';				--   走索引,key_len=33
explain select id2 from user2 where varchar_short7 = '0' and varchar_short8 = '0';																--   走索引,key_len=22
explain select id2 from user2 where varchar_short7 = '0' and varchar_short9 = '0';																--   走索引,key_len=11
explain select id2 from user2 where varchar_short7 = '0';																													--   走索引,key_len=11
explain select id2 from user2 where varchar_short8 = '0' and varchar_short9 = '0';																-- 不走索引
explain select id2 from user2 where varchar_short8 = '0';																													-- 不走索引
explain select id2 from user2 where varchar_short9 = '0';																													-- 不走索引
explain select id2 from user2 where varchar_short7 in( '0','1');																									--   走索引,key_len=11
explain select id2 from user2 where varchar_short7 in( '0','1') and varchar_short8 = '0' and varchar_short9 = '0';--   走索引,key_len=33
explain select id2 from user2 where varchar_short7 > '1';																													--   走索引,key_len=11
explain select id2 from user2 where varchar_short7 > '1'        and varchar_short8 = '0' and varchar_short9 = '0';--   走索引,key_len=33
explain select id2 from user2 where varchar_short7 like '1%'																											--   走索引,key_len=11
explain select id2 from user2 where varchar_short7 like '1%' and varchar_short8 = '0' and varchar_short9 = '0';		--   走索引,key_len=33
explain select id2 from user2 where varchar_short7 = '0' or  varchar_short8 = '0';																-- 不走索引

explain select id2 from user ;

explain select * from user where bigint2 = 1 limit 1;
explain select distinct id2 from user where id2  > '1';
explain select id2 from user limit 100,1;

select * from user;

explain select id2 from user where id2   = '1';            										--  =范围小，肯定走索引。const
explain select id2 from user where id2  != '1';            										--  =范围小，肯定走索引。const

posted on 2023-08-12 23:52  小石头小祖宗  阅读(2540)  评论(0)    收藏  举报  来源

导航