1,'2009-03-01'),
(1,'2009-04-01');
alter table buy_log add key(userid);
alter table buy_log add key(userid,buy_date);
#===========验证==============
mysql> show create table buy_log;
| buy_log | CREATE TABLE `buy_log` (
`userid` int(10) unsigned NOT NULL,
`buy_date` date DEFAULT NULL,
KEY `userid` (`userid`),
KEY `userid_2` (`userid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
#可以看到possible_keys在这里有两个索引可以用,分别是单个索引userid与联合索引userid_2,但是优化器最终选择了使用的key是userid因为该索引的叶子节点包含单个键值,所以理论上一个页能存放的记录应该更多
mysql> explain select * from buy_log where userid=2;
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid | 4 | const | 1 | |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
row in set (0.00 sec)
#接着假定要取出userid为1的最近3次的购买记录,用的就是联合索引userid_2了,因为在这个索引中,在userid=1的情况下,buy_date都已经排序好了
mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3;
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid_2 | 4 | const | 4 | Using where; Using index |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
row in set (0.00 sec)
#ps:如果extra的排序显示是Using filesort,则意味着在查出数据后需要二次排序(如下查询语句,没有先用where userid=3先定位范围,于是即便命中索引也没用,需要二次排序)
mysql> explain select * from buy_log order by buy_date desc limit 3;
+----+-------------+---------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | buy_log | index | NULL | userid_2 | 8 | NULL | 7 | Using index; Using filesort |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-----------------------------+
#对于联合索引(a,b),下述语句可以直接使用该索引,无需二次排序
select ... from table where a=xxx order by b;
#然后对于联合索引(a,b,c)来首,下列语句同样可以直接通过索引得到结果
select ... from table where a=xxx order by b;
select ... from table where a=xxx and b=xxx order by c;
#但是对于联合索引(a,b,c),下列语句不能通过索引直接得到结果,还需要自己执行一次filesort操作,因为索引(a,c)并未排序
select ... from table where a=xxx order by c;
View Code
2,覆盖索引
上面讲了,利用辅助索引拿到辅助索引对应字段的值,不需要从聚集索引中拿整行数据的操作叫覆盖索引。辅助索引不包含整行数据,其大小远小于聚集索引,因此减少大量的IO操作。
2.1对于统计问题而言,相较于聚集索引,还是会选择辅助索引,这是优化器的操作结果
mysql> explain select count(*) from buy_log;
+----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--- |