Schema的优化和索引 - 高性能的索引策略 - 覆盖索引(Covering Indexes)

索引是高效找到行的一个方法，但是MySQL也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引（covering indexex）

 

覆盖索引是非常强大的工具并且可以大幅度提升性能。考虑下仅仅读取索引的好处：

 

• 索引的实体往往小于整个行的大小。如果MySQL仅仅读取索引，意味着访问的数据就非常少了。这对于缓存的工作非常有用，这样相应的时间基本都是来自复制数据而已。对于IO限制也非常有用，因为索引要比数据更小并且更容易的写入内存中。（这对于MyISAM尤其有效，它可以对索引进行压缩，这样索引就变得更小了）。
• 索引是根据索引值的来排序的，因此IO限制范围的访问相对比从随机硬盘位置所需的IO是较少的。对于一些存储引擎，比如MyISAM，你甚至可以用OPTIMIZE这个表来获取全部的排序索引。这样可使简单的范围查询使用完全连续的索引的访问。
• 大部分存储引擎缓存索引要好于数据。一些存储引擎，比如MyISAM只缓存索引。因为操作系统缓存了MyISAM的数据，访问数据需要一个系统的调用。这样会导致非常严重的性能问题。尤其对于缓存来说，系统的调用是数据访问消耗最大的一部分。
• 覆盖索引对与InnoDB表有些特殊的效用。因为InnoDB是聚簇索引。InnoDB的次要索引在它们叶子节点保存了行的主键。因此，次要索引的覆盖可以避免在主键上另一个索引的查找。

在这些场景中，从索引中满足一个查询消耗要比查询行要低很多。

覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。Hash, spatial, 和full-text索引不存储值，因此MySQL只能使用B-TREE。并且不同的存储引擎实现覆盖索引都是不同的。并不是所有的存储引擎都支持它们。

当一个查询被索引所覆盖。（an 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: 4673

        Extra: Using index

覆盖索引的语句有些诡异的是会关闭优化。MySQL语句优化器在执行语句之前会决定是否有一个索引覆盖它。假使这个索引覆盖了一个WHERE条件，但是并不是整个查询。如果这个条件评估为false，MySQL51以及以前版本都会取出行。

让我们来看看为什么会这样。以及怎样重写这个查询来解决上面所说的问题。

mysql> EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY'

    -> AND title like '%APOLLO%'\G

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

   id: 1

  select_type: SIMPLE

  table: products

  type: ref

  possible_keys: ACTOR,IX_PROD_ACTOR

  key: ACTOR

  key_len: 52

  ref: const

  rows: 10

Extra: Using where

这个索引不能覆盖这个查询有如下两条原因：

• 没有索引覆盖这个查询，因为我们选择了这个表的所有列，并且没有一个索引覆盖这些列。理论上来说MySQL还能使用一个捷径：WHERE条件中有一列被索引覆盖，因此MySQL会使用索引先找到这个actor并且检查title是否匹配，之后再取整个行。
• MySQL不能在索引中执行LIKE操作符。这个受限于底层的存储引擎API。在索引的操作，这只能支持简单的比较。MySQL可以LIKE中匹配前缀，因为把它们转为简单的比较了。但是这个例子中前缀的通配符使使用索引查询变为了不可能。最终。MySQL服务器将会获取和匹配这个行的值，而不是索引的值。

我们可以使用加索引（artist, title, prod_id）和重写查询来解决上面的问题。重写的查询如下

mysql> EXPLAIN SELECT *

    -> FROM products

    ->    JOIN (

    ->       SELECT prod_id

    ->       FROM products

    ->       WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'

    ->    ) AS t1 ON (t1.prod_id=products.prod_id)\G

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

           id: 1

  select_type: PRIMARY

        table: <derived2>

               ...omitted...

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: products

               ...omitted...

*************************** 3. row ***************************

           id: 2

  select_type: DERIVED

        table: products

         type: ref

possible_keys: ACTOR,ACTOR_2,IX_PROD_ACTOR

          key: ACTOR_2

      key_len: 52

          ref:

         rows: 11

        Extra: Using where; Using index

现在MySQL在第一阶段的查询使用了覆盖索引，当它找到了在子查询匹配的行的时候。它不会在这个执行语句使用索引。但是总比没有强。

这个优化的有效性完全依靠于有多少行被找到。假使products表包含了1百万行。让我们来看看不同数据集下这两个查询的表现。

1. 第一个，Sean Carrey有30000个产品并且title包含Apollo有2000行。
2. 第二个，分别是30000和40
3. 第三个，分别是50和10

测试结果如下

Dataset     Original query                 Optimized query

Example1  5 queries per sec            5 queries per sec

Example2  7 queries per sec            35 queries per sec

Example3  2400 queries per sec      2000 queries per sec

让我们解释下结果

• 在例子一中，返回的大数据集。并没有看到优化效果。大部分时间花在读取和发送数据上了。
• 例子二中，在索引过滤后，第二个条件过滤了很少的数据，让我们看看语句的油画效果：是没优化的语句的5倍之多！效率高的原因是第二个语句仅仅需要查找40行而不是30000行。
• 第三个例子中，知道了子查询是低效的。在索引过滤数据非常小的情况下，子查询的消耗大于了直接从整张表查询所有数据的消耗。

在MySQL5.1以及之前的版本中，这个优化有的时候可以避免读取没有必要的行。MySQL6.0就会避免一些额外的语句。如果升级的话，就不必优化了。

在大多数的存储引擎中，一个索引所覆盖仅仅是访问列是索引的一部分的查询语句。然而，InnoDB可以使优化更进一步。回忆一下，InnoDB的次要索引在叶子节点中保存了主键的值。意味着InnoDB次要索引可由一个额外的列了。InnoDB就可以使用这一特性来覆盖查询语句了。

举个例子，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

评论

1 楼 fancylee 2013-02-16  

  sakila库中没有找到products表，请问这个表的结构是怎么样的呢？