MySQL的联合索引和覆盖索引

关于MySQL的联合索引，覆盖索引一直蒙蒙哒，所以写点东西来熟悉一下
首先创建一个表orders，结构如下：

create table orders(
order_id int unsigned auto_increment,
order_status int not null,
total_price int unsigned,
settle_type int,
store_id int,
buyer_id int,
goods varchar(20),
create_time datetime default current_timestamp,
primary key(order_id)
);

然后我们创建一个存储过程，向这个表中插入一些数据：

drop procedure if exists load_orders;
delimiter //
create procedure load_orders(count int unsigned)
begin
declare status int default 0;
declare type int default 0;
declare store int default 133;
declare price int default 85;
declare buyer int default 1001;
declare goods varchar(20) ;
declare i int unsigned default 0;
SET goods= 'al';
while i<count DO
SET status=mod (status,77);
SET type=mod(type,29);
IF buyer>2000 THEN SET buyer=1001; end IF;
IF price>1000 THEN SET price=200; end IF;
IF length(goods)>18 THEN SET goods= 'al'; end IF;
insert into orders(order_status,total_price,settle_type,store_id,goods,buyer_id) values (status, price, type, store, goods, buyer);
SET count=count+1;
SET status=status+1;
SET type=type+1;
SET store=store+1;
SET price=price+43;
SET buyer=buyer+1;
SET goods=concat(goods, char(round(rand()*25)+97));
SET i=i+1;
end while;
end //
delimiter ;

call load_orders(99999);#调用存储过程

然后我们有表有数据啦，做一些查询操作：

select count(store_id) as cnt from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;
select count(order_id) as cnt from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;
select count(*) as cnt from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;

结果如图所示：
MySQL的联合索引和覆盖索引
我们发现，此时count(普通列)，count(主键索引列)，count(*)耗时都是差不多的，需要7秒多，当然还是count(*)最快。

从查询计划中我们可以看到是全表扫描的，Using where。
我们改变表的结构，给where后的三列添加一个联合索引：

alter table orders add key union_key(settle_type,order_status,buyer_id);

然后我们再做一下上面的那三个查询：
MySQL的联合索引和覆盖索引
我们发现，此时count(普通列)需要用时最久，
count(主键索引列)，count(*)耗时都是很短。

desc select count(store_id) as cnt from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;
desc select count(order_id) as cnt from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;
desc select count(*) as cnt from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;

MySQL的联合索引和覆盖索引

select *  from orders WHERE settle_type = 1   and order_status = 2  and buyer_id=1990;
#4 rows in set (0.00 sec)
select *  from orders WHERE settle_type = 1   and buyer_id=1990;
#243 rows in set (0.53 sec)
select *  from orders WHERE order_status = 2  and buyer_id=1990;
#91 rows in set (10.44 sec)

desc select * from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;
desc select * from orders WHERE settle_type = 1 and buyer_id=1990;
desc select * from orders WHERE order_status = 2 and buyer_id=1990;

MySQL的联合索引和覆盖索引
由上图我们看到当使用索引的第二列和第三列时根本就用不到索引。
联合索引中是放着主键索引的值的。
上面的Using index condition就是使用到了ICP哟，where会在存储引擎中过滤后再传递给server层。

desc select settle_type from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;
desc select settle_type from orders WHERE settle_type = 1 and buyer_id=1990;
desc select settle_type from orders WHERE order_status = 2 and buyer_id=1990;

desc select buyer_id from orders WHERE settle_type = 1 and order_status = 2 and buyer_id=1990;
desc select buyer_id from orders WHERE settle_type = 1 and buyer_id=1990;
desc select buyer_id from orders WHERE order_status = 2 and buyer_id=1990;

秒客网

MySQL的联合索引和覆盖索引

相关文章