联合索引+覆盖索引使用

联合索引

联合索引是指对表上的多个列进行索引。前面讨论的情况都是只对表上的一个列进行索引。联合索引的创建方法与单个索引创建的方法一样,不同之处仅在于有多个索引列。
例如,以下代码创建了一张t表,并且索引idx_a_b是联合索引,联合的列为(a,b)。

    CREATE TABLE t(
    a INT,
    b INT,
    PRIMARY KEY (a),
    KEY idx_a_b(a,b)
    )ENGINE=INNODB;

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

从图可以观察到多个键值的B+树情况。其实和之前讨论的单个键值的B+树并没有什么不同,键值都是排序的,通过叶子节点可以逻辑上顺序地读出所有数据,就上面的例子来说,即(1,1)、(1,2)、(2,1)、(2,4)、(3,1)、(3,2)。数据按(a,b)的顺序进行了存放。

因此,对于查询 SELECT * FROM TABLE WHERE a= xxx and b=xx,显然是可以使用(a,b)这个联合索引的。对于单个的a列查询 SELECT* FROM TABLE WHERE a=xxx,也可以使用这个(a,b)索引。但对于b列的查询 SELECT * FROM TABLE WhERE b=xxx,则不可以使用这棵B+树索引。可以发现叶子节点上的b值为1、2、1、4、1、2,显然不是排序的,因此对于b列的查询使用不到(a,b)的索引。
联合索引的第二个好处是已经对第二个键值进行了排序处理。例如,在很多情况下应用程序都需要查询某个用户的购物情况,并按照时间进行排序,最后取出最近三次的购买记录,这时使用联合索引可以避免多一次的排序操作,因为索引本身在叶子节点已经排序了。来看一个例子,首先根据如下代码来创建测试表 buy_log:

    CREATE TABLE buy_log(
    userid INT UNSIGNED NOT NULL,
    buy_date DATE
    )ENGINE=InnoDB;
    INSERT INTO buy_log VALUES (1,'2009-01-01');
    INSERT INTO buy_log VALUES (2,'2009-01-01');
    INSERT INTO buy_log VALUES (3,'2009-01-01');
    INSERT INTo buy_log VALUES (1,'2009-02-01');
    INSERT INTo buy_log VALUEs (3,'2009-02-01');
    InSERT INTO buy_log VALUEs (1,'2009-03-01');
    INSERT INTo buy_log VALUEs (1,'2009-04-01');
    ALTER TABLE buy_log ADD KEY (userid);
    ALTER TABLE buy_log ADD KEY (userid,buy_date);

以上代码建立了两个索引来进行比较。两个索引都包含了 userid字段。如果只对于userid进行查询,如:
select * from buy_log where userid=2;
则优化器的选择为:

    mysql> explain select * from buy_log where userid=2;
    +----+-------------+---------+------------+------+-----------------+--------+---------+-------+------+----------+-------+
    | id | select_type | table   | partitions | type | possible_keys   | key    | key_len | ref   | rows | filtered | Extra |
    +----+-------------+---------+------------+------+-----------------+--------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | buy_log | NULL       | ref  | userid,userid_2 | userid | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+---------+------------+------+-----------------+--------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.01 sec)

可以发现, possible keys在这里有两个索引可供使用,分别是单个的userid索引和( userid, buy date)的联合索引。但是优化器最终的选择是索引 userid,因为该索引的叶子节点包含单个键值,所以理论上一个页能存放的记录应该更多。

接着假定要取出 userid为1的最近3次的购买记录,其SQL语句如下,执行计划。

    mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3;
    +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+--------------------------+
    | id | select_type | table   | partitions | type | possible_keys   | key      | key_len | ref   | rows | filtered | Extra                    |
    +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+--------------------------+
    |  1 | SIMPLE      | buy_log | NULL       | ref  | userid,userid_2 | userid_2 | 4       | const |    4 |   100.00 | Using where; Using index |
    +----+-------------+---------+------------+------+-----------------+----------+---------+-------+------+----------+--------------------------+
    1 row in set, 1 warning (0.01 sec)

同样的,对于上述的SQL语句既可以使用 userid索引,也可以使用( userid,buy_date)索引。但是这次优化器使用了( userid, buy_date)的联合索引 userid2,因为在这个联合索引中 buy date已经排序好了。根据该联合索引取出数据,无须再对 buy_date做一次额外的排序操作。若强制使用 userid索引,则执行计划如下所示。

    mysql> explain select * from buy_log force index(userid) where userid=1 order by buy_date desc limit 3;
    +----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
    | id | select_type | table   | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                                 |
    +----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
    |  1 | SIMPLE      | buy_log | NULL       | ref  | userid        | userid | 4       | const |    4 |   100.00 | Using index condition; Using filesort |
    +----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
    1 row in set, 1 warning (0.01 sec)

在Exa选项中可以看到 Using filesort,即需要额外的一次排序操作才能完成查询而这次显然需要对列 buy date排序,因为索引 userid中的 buy_date是未排序的正如前面所介绍的那样,联合索引(a,b)其实是根据列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
但是对于下面的语句,联合索引不能直接得到结果,其还需要执行一次 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存储引擎不支持覆盖索引特性。
对于 InnoDe存储引擎的辅助索引而言,由于其包含了主键信息,因此其叶子节点存放的数据为( primary key1, primary key2,...key1,key2,…)。例如,下列语句都可仅使用一次辅助联合索引来完成查询:
select key2 FROM table Where key1=xxx:
SELECT primary key2, key 2 FROM table Where key1=xxx:
SELECT primary key1, key 2 FROM table Where key1=xxx:
SELECT primary keyl,primary key2, key2 FROM table Where keyl=xxx:
覆盖索引的另一个好处是对某些统计问题而言的。还是对于上一小节创建的表buy_log要进行如下查询：

    select count(*) from buy_log;

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

    mysql> explain select count(*) from buy_log;
    +----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
    | id | select_type | table   | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | buy_log | NULL       | index | NULL          | userid | 4       | NULL |    7 |   100.00 | Using index |
    +----+-------------+---------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

可以看到, possible_keys列为NULL,但是实际执行时优化器却选择了userid索引,而列 Extra列的 Using index就是代表了优化器进行了覆盖索引操作。
此外,在通常情况下,诸如(a,b)的联合索引,一般是不可以选择列b中所谓的查询条件。但是如果是统计操作,并且是覆盖索引的,则优化器会进行选择,如下述语句:

    mysql> explain SELECT COUNT(*) FROM buy_log Where buy_date>='2011-01-01' and buy_date< '2011-02-01';
    +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
    | id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
    +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
    |  1 | SIMPLE      | buy_log | NULL       | index | NULL          | userid_2 | 8       | NULL |    7 |    14.29 | Using where; Using index |
    +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
    1 row in set, 1 warning (0.01 sec)

表 buy_log有( userid, buy_date)的联合索引,这里只根据列b进行条件查询,般情况下是不能进行该联合索引的,但是这句SQL查询是统计操作,并且可以利用到覆盖索引的信息,因此优化器会选择该联合索引。

优化器选择不使用索引的情况

在某些情况下,当执行 EXPLAI命令进行SQL语句的分析时,会发现优化器并没有选择索引去查找数据,而是通过扫描聚集索引,也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。例如:
SELECT FROM orderdetails Where orderid>10000 and orderid<102000;
上述这句SQL语句查找订单号大于10000的订单详情,通过命令 SHOW INDEX FROM orderdetails,可观察到的索引如图所示。

可以看到表 orderdetails有( OrderID, ProductID)的联合主键,此外还有对于列OrderID的单个索引。上述这句SQL显然是可以通过扫描 OrderID上的索引进行数据的查找。然而通过 EXPLAIN命令,用户会发现优化器并没有按照 OrderID上的索引来查找数据,如图所示。
在 possible keys一列可以看到查询可以使用 PRIMARY、 OrderID、 OrdersOrder_Details三个索引,但是在最后的索引使用中,优化器选择了 PRIMARY聚集索引,也就是表扫描( table scan),而非 OrderID辅助索引扫描( index scan)。
这是为什么呢?原因在于用户要选取的数据是整行信息,而 OrderID索引不能覆盖到我们要査询的信息,因此在对 OrderID索引查询到指定数据后,还需要一次书签访问来查找整行数据的信息。虽然 OrderID索引中数据是顺序存放的,但是再一次进行书签查找的数据则是无序的,因此变为了磁盘上的离散读操作。如果要求访问的数据量很小,则优化器还是会选择辅助索引,但是当访问的数据占整个表中数据的蛮大一部分时(一般是20%左右),优化器会选择通过聚集索引来查找数据。因为之前已经提到过,顺序读要远远快于离散读。因此对于不能进行索引覆盖的情况,优化器选择辅助索引的情况是,通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的,即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘,随机读操作非常快,同时有足够的自信来确认使用辅助索引可以带来更好的性能,那么可以使用关键字 FORCE INDEX来强制使用某个索引,如:
SELECt FROM orderdetails FORCE INDEX(OrderID) Where orderid>10000 and orderid<102000;
这时的执行计划如图所示。