MySQL联合索引、索引下推Demo

 1.联合索引

测试SQL语句如下：表test中共有4个字段(id, a, b, c)，id为主键

drop table test;

#建表
create table test(
	id bigint primary key auto_increment,
	a int,
	b int,
	c int
)

#表中插入数据
insert into test(a, b, c) values(1,2,3),(2,3,4),(4,5,6);
insert into test(a, b, c) values(1,2,3),(2,3,4),(4,5,6);
insert into test(a, b, c) values(1,2,3),(2,3,4),(4,5,6);

# 建立联合索引(a,b,c)
create index index_abc ON test (a, b, c);

# 删除索引
drop index index_abc on test;

# 查询a = 1 and c = 3，观察这条语句是否走了索引
explain 
select a,b,c from test where a = 1 and c = 3; # 存在回表的可能

explain 
select a,b,c from test where a = 1; # 完全使用索引

首先不建立联合索引(a,b,c)，执行下面的执行

explain 
select a from test where a = 1 and c = 3; # 存在回表的可能

结果下如下： 使用主键的覆盖索引查询，没有走索引

建立联合索引(a,b,c)后，同样执行上面语句，结果如下:

通过explain，我们发现，这条语句可以走索引，同时还存在Using where，说明存在回表的可能，但是这条语句是可以走索引的。（部分索引）

如果我们执行下面这条语句：（符合最左前缀原则）

explain 
select a,b,c from test where a = 1; # 完全使用索引

 结果如下：说明完全使用索引来查询。

 2.索引下推ICP

ICP可以减少存储引擎必须访问基本表的次数以及服务器必须访问存储引擎的次数，这是是否使用ICP的最可靠的判断条件

SQL语句如下：

create table test01(
	`id` bigint primary key auto_increment,
	`name` varchar(64),
	`age` int,
	`desc` varchar(64)
)

# 插入测试数据
insert into test01(name, age) values('张三1', 18);
insert into test01(name, age) values('张三2', 21);
insert into test01(name, age) values('张三3', 22);
insert into test01(name, age) values('李四', 18);
insert into test01(name, age) values('王五', 13);

# 为name和age建立联合索引
create index index_na on test01(name, age);

# 观察name like '张%' and age = 18
explain
select * from test01 where `name` like '张%' and `age` = 18;

explain
select name, age from test01 where `name` like '张%' and `age` = 18;

首先给name和age建立联合索引

然后执行下面这条语句

explain
select * from test01 where `name` like '张%' and `age` = 18;

结果如下：

Using index condition说明使用了索引下推，并且存在回表的现象，因为select *