1.explain
说明

序号 | 列名 | 描述 |
---|
1 | id | 在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id |
2 | select_type | SELECT关键字对应的那个查询的类型 |
3 | table | 表名 |
4 | partitions | 匹配的分区信息 |
5 | type ★ | 针对单表的访问方法 |
6 | possible_keys | 可能用到的索引 |
7 | key | 实际上使用的索引 |
8 | key_len ★ | 实际使用到的索引长度 |
9 | ref | 当使用索引列等值查询时,与索引列进行等值匹配的对象信息 |
10 | rows ★ | 预估的需要读取的记录条数 |
11 | filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
12 | Extra ★ | 一些额外的信息 |
1.1.type
说明
- 从最好到最坏依次如下表;
- 加粗的是比较重要的;
- SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,最好是 consts级别。(阿里巴巴开发手册要求)
序号 | 列名 | 描述 |
---|
1.1 | system | 当表中只有一条记录并且该表使用的在储引擎的统计数据是精确的,比如MyISAM、Memory |
1.2 | const | 当我们根据主键或者唯一索引列与常数进行等值匹配时,对单表的访问方法就是const |
1.3 | eq_ref | 在连接查询时,如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话,所有的索引列都必须进行等值比较),则对该被驱动表的访问方法就是eq ref |
1.4 | ref | 当通过普通的二级索引列与常量进行等值匹配时来查询某个表,那么对该表的访问方法就可能是ref |
2.1 | fulltext | 略 |
2.2 | ref_or_null | 当对普通二级索引进行等值匹配查询,该索引列的值也可以是NULL 值时,那么对该表的访问方法就可能是ref_or_null |
2.3 | index_merge | 单表访问方法时在某些场景下可以使用Intersection 、Union 、sort-Union 这三种索引合并的方式来执行查询 |
2.4 | unique_subquery | 是针对在一些包含IN 子查询的查询语句中,如果查询优化器决定将IN 子查询转换为EXISTS 子查询,而且子查询可以使用到唯一索引进行等值匹配的话。 |
2.5 | index_subquery | 是针对在一些包含IN 子查询的查询语句中,如果查询优化器决定将IN 子查询转换为EXISTS 子查询,而且子查询可以使用到普通索引进行等值匹配的话。 |
2.6 | range | 如果使用索引获取某些范围区间 的记录,那么就可能使用到`range '访问方法 |
3.1 | index | 当我们可以使用索引覆盖,但需要扫描全部的索引记录时,该表的访问方法就是index |
3.2 | ALL | 未使用索引(各种索引失效情况)的情况 |
1.1.1.总结sql
explain select * from demo where id = '1';
explain select * from demo where id2 = '1';
explain select * from demo a,demo b where a.id2 = b.id2;
explain select * from demo where id3 = '1';
explain select * from demo where id2 is null;
explain select * from demo a,demo b where a.id3 = b.id3;
explain select * from demo where id2 = '1' or id2 is null;
explain select * from demo where id3 = '1' or id3 is null;
explain select * from demo where id2 = '1' or id3 = '2';
explain select * from demo where id2 = '1' or id3 in ('2','3');
explain select * from demo a where id4 in (select id2 from demo where id4 = a.id4) or id4 ='1';
explain select * from demo a where id4 in (select id3 from demo where id4 = a.id4) or id4 ='1';
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%';
explain select distinct id3 from demo;
explain select id from demo;
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.编写代码
- 建表
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 ='演示';
- 批量插入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();
}
}
- 发送请求
http://localhost:8080/saveBatchDemo/1000
2. 索引失效
explain select id2 from user where varchar_short4 = '0';
explain select id2 from user where varchar_short4 = '0000';
explain select id2 from user where varchar_short4 != '0000';
explain select id2 from user where varchar_short4 != '0';
explain select id2 from user where varchar_short4 <= '0';
explain select id2 from user where varchar_short4 <= '0000';
explain select id2 from user where varchar_short4 between 'a' and 'z';
explain select id2 from user where varchar_short4 between '0' and 'z';
explain select id2 from user where varchar_short4 not between '0' and 'z';
explain select id2 from user where varchar_short4 not between 'a' and 'z';
explain select id2 from user where varchar_short4 in ('0');
explain select id2 from user where varchar_short4 in ('0000');
explain select id2 from user where varchar_short4 in ('0','1');
explain select id2 from user where varchar_short4 in ('0000','1');
explain select id2 from user where varchar_short4 not in ('0000');
explain select id2 from user where varchar_short4 not in ('0');
explain select id2 from user o where exists (select 1 from user where id =o.id and varchar_short4 ='0');
explain select id2 from user o where exists (select * from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0');
explain select id2 from user o where exists (select * from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0000');
explain select id2 from user o where not exists (select id2 from user where varchar_short4 =o.varchar_short4 and varchar_short4 ='0000');
explain select id2 from user where varchar_short6 is null;
explain select id2 from user where varchar_short5 is null;
explain select id2 from user where varchar_short5 is not null;
explain select id2 from user where varchar_short6 is not null;
explain select id2 from user where varchar_short4 like 'a';
explain select id2 from user where varchar_short4 like '0000';
explain select id2 from user where varchar_short4 like 'a%';
explain select id2 from user where varchar_short4 like '0000%';
explain select id2 from user where varchar_short4 like '%a';
explain select id2 from user where varchar_short4 not like '0000%';
explain select distinct varchar_short4 from user;
explain select distinct varchar_mid2 from user;
explain select distinct varchar_short3 from user;
explain select id2 from user order by varchar_short4 limit 1000;
explain select id2 from user order by varchar_short3 limit 1000;
explain select varchar_short4 from user group by varchar_short4 limit 1000;
explain select varchar_short3 from user group by varchar_short3 limit 1000;
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';
explain select id2 from user where varchar_short4 = '0' and varchar_short5 = '0';
explain select id2 from user where varchar_short4 = '0' or varchar_short5 = '0';
explain select id2 from user where varchar_short4 = '11';
explain select id2 from user where varchar_short4 = 11 ;
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;
explain select id2 from user2 where varchar_short7 = '0' and varchar_short8 = '0' and varchar_short9 = '0';
explain select id2 from user2 where varchar_short9 = '0' and varchar_short8 = '0' and varchar_short7 = '0';
explain select id2 from user2 where varchar_short9 = '0' and varchar_short8 = '0' and varchar_short7 = '0';
explain select id2 from user2 where varchar_short7 = '0' and varchar_short8 = '0';
explain select id2 from user2 where varchar_short7 = '0' and varchar_short9 = '0';
explain select id2 from user2 where varchar_short7 = '0';
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');
explain select id2 from user2 where varchar_short7 in( '0','1') and varchar_short8 = '0' and varchar_short9 = '0';
explain select id2 from user2 where varchar_short7 > '1';
explain select id2 from user2 where varchar_short7 > '1' and varchar_short8 = '0' and varchar_short9 = '0';
explain select id2 from user2 where varchar_short7 like '1%'
explain select id2 from user2 where varchar_short7 like '1%' and varchar_short8 = '0' and varchar_short9 = '0';
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';
explain select id2 from user where id2 != '1';
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 全网最简单!3分钟用满血DeepSeek R1开发一款AI智能客服,零代码轻松接入微信、公众号、小程