【MySQL】性能优化之 覆盖索引

一个包含查询所需的字段的索引称为 covering index 覆盖索引。MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提供效率。

   当你对一个sql 使用explain statement 查看一个sql的执行计划时，在EXPLAIN的Extra列出现Using Index提示时，就说明该select查询使用了覆盖索引。

【使用场景】

生产过程中遇到的一个例子，且先不讨论 count(字段)还是 count(*)  

root@yang 06:38:34>select count(o.order_id) as cnt from `order` o  WHERE o.settle_type = 2   and o.order_status = 2  and o.buyer_id=1979459339672858;        

+------+

| cnt  |

+------+

| 7574 |

+------+

1 row in set (0.22 sec)

root@yang 06:36:38>explain select count(o.order_id) as cnt from `order` o  WHERE o.settle_type = 2   and o.order_status = 2  and o.buyer_id=1979459339672858; 

+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+

| id | select_type | table | type        | possible_keys         | key                   | key_len | ref  | rows | Extra                                               |

+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+

|  1 | SIMPLE      | o     | index_merge | buyer_id,order_status | buyer_id,order_status | 9,1     | NULL | 3852 | Using intersect(buyer_id,order_status); Using where |

+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+

1 row in set (0.00 sec)

上述select语句的执行计划使用了index_merge 索引聚合，整体sql执行花费0.22s 。根据查询条件对表结构进行如下调整：

root@yang 06:33:14>alter table `ordert` add key ind_od_bid_st_ty_oid(`buyer_id`,`order_status`,`settle_type`,`order_id`);

Query OK, 0 rows affected (3.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

root@yang 06:38:50>explain select count(o.order_id) as cnt from `ordert` o  WHERE o.settle_type = 2   and o.order_status = 2  and o.buyer_id=1979459339672858;

+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+

| id  | select_type | table | type | possible_keys        | key                  | key_len | ref               | rows  | Extra                    |

+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+

|  1 | SIMPLE      | o     | ref  | ind_od_bid_st_ty_oid | ind_od_bid_st_ty_oid | 11      | const,const,const | 15242 | Using where; Using index |

+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+

1 row in set (0.00 sec)

执行计划使用了 using index --覆盖索引而且执行时间由0.22s，下降到小于0.01s。

root@yang 06:39:06>select count(o.order_id) as cnt from `ordert` o  WHERE o.settle_type = 2   and o.order_status = 2  and o.buyer_id=1979459339672858;        

+------+

| cnt  |

+------+

| 7574 |

+------+

1 row in set (0.00 sec)

【覆盖索引的限制】

遇到以下情况，执行计划不会选择覆盖查询：

1select选择的字段中含有不在索引中的字段 ，也即索引没有覆盖全部的列。

2 where 条件中不能含有对索引进行like的操作。

root@odbsyunying 08:18:15>explain select count(*) as cnt from `ordert` o  WHERE o.settle_type = 2 

 > and o.order_status = 2  

 > and o.buyer_id like '1979459339672858' \G

*************************** 1. row ***************************

                id: 1

  select_type: SIMPLE

           table: o

           type: index

possible_keys: ind_od_bid_st_ty_oid

            key: ind_od_bid_st_ty_oid

      key_len: 19

             ref: NULL

            rows: 767466  ---覆盖索引扫描

           Extra: Using where; Using index

1 row in set (0.00 sec)

  MySQL之高效覆盖索引  2010-10-25 22:04:25

分类： Mysql/postgreSQL

mysql中的一种十分高效有用的索引---覆盖索引。

覆盖索引用通俗的话讲就是在select的时候只用去读取索引而取得数据，无需进行二次select相关表。这样的索引的叶子节点上面也包含了他们索引的数据。

select * from table_name；

select id，name from table_name;

在多数情况下，我们只应该去查询我们有必要知道的列，这样一来网络之间传送的数据包小了，减少了网络通信，你查询的速度自然会得到提升。

select a  from table_name where b ·····；这样的一个查询，都知道索引应该加在b上面，

查询的处理过程：

首先去检索b索引找到与其对应的索引，

然后根据索引区检索正确的数据行。

这样一来一去就是两次检索，能不能通过一次检索而得到数据呢?

如果希望通过一次检索得到数据，那么索引上面就应该包含其索引相对的数据，这样可能吗?

读到这，当然知道可能.

alter table_name add index (b,a);

添加一个这样的索引就能实现了,

查看是否使用了覆盖索引;

explain select ·····;

·······

·······

······

extra:use index

如果出现了红色的字体部分，就表示使用了覆盖索引。

INNODB引擎在覆盖索引上面更进一步：

innodb引擎的所有储存了主键ID，事务ID，回滚指针,非主键ID，

他的查询就会是非主键ID也可覆盖来取得主键ID。

3.2、覆盖索引(Covering Indexes)
如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：
(1)索引项通常比记录要小，所以MySQL访问更少的数据；
(2)索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；
(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。
覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。
对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到“Using index”。例如，在sakila的inventory表中，有一个组合索引(store_id,film_id)，对于只需要访问这两列的查询，MySQL就可以使用索引，如下：

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G *************************** 1. row ***************************            id: 1  select_type: SIMPLE         table: inventory          type: index possible_keys: NULL           key: idx_store_id_film_id       key_len: 3           ref: NULL          rows: 5007         Extra: Using index 1 row in set (0.17 sec)

在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖。但是，InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了primary key的值。因此，sakila.actor表使用InnoDB，而且对于是last_name上有索引，所以，索引能覆盖那些访问actor_id的查询，如：

 

mysql> EXPLAIN SELECT actor_id, last_name     -> FROM sakila.actor WHERE last_name = 'HOPPER'\G *************************** 1. row ***************************            id: 1  select_type: SIMPLE         table: actor          type: ref possible_keys: idx_actor_last_name           key: idx_actor_last_name       key_len: 137           ref: const          rows: 2         Extra: Using where; Using index