mysql数据库联合索引的例子_Python MySQL数据库之联合索引与覆盖索引

一、 联合索引与覆盖索引

一 联合索引

联合索引是指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处仅在于有多个索引列，如下

mysql> create table student2(

->     id int,

->     name varchar(20),

->     age int,

->     primary key(id),

->     key index_id_age(id,age)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> desc student2;

那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引就是一棵B+树，不同的是联合索引的键值得数量不是1，而是>=2。接着来讨论两个整型列组成的联合索引，假定两个键值得名称分别为a、b如图

可以看到这与我们之前看到的单个键的B+树并没有什么不同，键值都是排序的，通过叶子结点可以逻辑上顺序地读出所有数据，就上面的例子来说，即(1,1)，(1,2)，(2,1)，(2,4)，(3,1)，(3,2)，数据按(a,b)的顺序进行了存放。

因此，对于查询select * from table where a=xxx and b=xxx, 显然是可以使用(a,b) 这个联合索引的，对于单个列a的查询select * from table where a=xxx,也是可以使用(a,b)这个索引的。

但对于b列的查询select * from table where b=xxx,则不可以使用(a,b) 索引，其实你不难发现原因，叶子节点上b的值为1、2、1、4、1、2显然不是排序的，因此对于b列的查询使用不到(a,b) 索引

联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，例如在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以帮我们避免多一次的排序操作，因为索引本身在叶子节点已经排序了，如下

#===========准备表==============

mysql> create table buy_log(

->     userid int unsigned not null,

->     buy_date date

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> insert into buy_log values

-> (1,'2009-01-01'),

-> (2,'2009-01-01'),

-> (3,'2009-01-01'),

-> (1,'2009-02-01'),

-> (3,'2009-02-01'),

-> (1,'2009-03-01'),

-> (1,'2009-04-01');

Query OK, 7 rows affected (0.01 sec)

Records: 7  Duplicates: 0  Warnings: 0

mysql>

mysql> alter table buy_log add key(userid);

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table buy_log add key(userid,buy_date);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql>

#===========验证==============

mysql> show create table buy_log;

#可以看到possible_keys在这里有两个索引可以用，分别是单个索引userid与联合索引userid_2,但是优化器最终选择了使用的key是userid因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多

mysql> explain select * from buy_log where userid=2;

#接着假定要取出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;

#ps：如果extra的排序显示是Using filesort，则意味着在查出数据后需要二次排序(如下查询语句，没有先用where userid=3先定位范围，于是即便命中索引也没用，需要二次排序)

mysql> explain select * from buy_log order by buy_date desc limit 3;

#对于联合索引(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;

二 覆盖索引

InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作

注意：覆盖索引技术最早是在InnoDB Plugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性

对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为(primary key1，priamey key2，...,key1，key2，...)。例如

select age from s1 where id=123 and name = 'egon'; #id字段有索引，但是name字段没有索引,该sql命中了索引，但未覆盖，需要去聚集索引中再查找详细信息。

最牛逼的情况是，索引字段覆盖了所有，那全程通过索引来加速查询以及获取结果就ok了

mysql> desc student1;

mysql> explain select name from student1 where id=1000; #没有任何索引

mysql> create index idx_id on student1(id);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select name from student1 where id=1000; #命中辅助索引，但是未覆盖索引，还需要从聚集索引中查找name

mysql> explain select id from student1 where id=1000; #在辅助索引中就找到了全部信息，Using index代表覆盖索引

覆盖索引的另外一个好处是对某些统计问题而言的。基于上一小结创建的表buy_log,查询计划如下

mysql> explain select count(*) from buy_log;

innodb存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择如上key为userid辅助索引

对于(a,b)形式的联合索引，一般是不可以选择b中所谓的查询条件。但如果是统计操作，并且是覆盖索引，则优化器还是会选择使用该索引，如下

#联合索引userid_2(userid,buy_date),一般情况，我们按照buy_date是无法使用该索引的，但特殊情况下：查询语句是统计操作，且是覆盖索引，则按照buy_date当做查询条件时，也可以使用该联合索引

mysql> explain select count(*) from buy_log where buy_date >= '2011-01-01' and buy_date 

合并索引：

mysql> explain select count(email) from student1 where id = 1000000 or email='eva100000@oldboy';