mysql高效索引之覆盖索引

╰+哭是因爲堅強的太久メ 2022-06-05 02:06 157阅读 0赞

转载自：https://www.cnblogs.com/chenpingzhao/p/4776981.html

**概念**

如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作

**判断标准**

使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为**using index**,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

**注意**

1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值

2、Hash 和full-text索引不存储值，因此MySQL只能使用B-TREE

3、并且不同的存储引擎实现覆盖索引都是不同的

4、并不是所有的存储引擎都支持它们

5、如果要使用覆盖索引，一定要注意SELECT 列表值取出需要的列，不可以是SELECT \*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降，不能为了利用覆盖索引而这么做

**InnoDB**

1、覆盖索引查询时除了除了索引本身的包含的列，还可以使用其默认的**聚集索引列**

2、这跟INNOB的索引结构有关系，主索引是B+树索引存储，也即我们所说的数据行即索引，索引即数据

3、对于INNODB的辅助索引，它的叶子节点存储的是索引值和指向主键索引的位置，然后需要通过主键在查询表的字段值，所以辅助索引存储了主键的值

4、覆盖索引也可以用上INNODB 默认的聚集索引

5、innodb引擎的所有储存了主键ID，事务ID，回滚指针,非主键ID，他的查询就会是非主键ID也可覆盖来取得主键ID

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点  
1、索引项通常比记录要小，所以MySQL访问更少的数据  
2、索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O  
3、大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引  
4、覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

在sakila的inventory表中，有一个组合索引(store\_id,film\_id)，对于只需要访问这两列的查询，MySQL就可以使用索引，如下

表结构

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>CREATE TABLE `inventory` (</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`film_id` smallint(5) unsigned NOT NULL,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`store_id` tinyint(3) unsigned NOT NULL,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>PRIMARY KEY (`inventory_id`),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>KEY `idx_fk_film_id` (`film_id`),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>KEY `idx_store_id_film_id` (`store_id`,`film_id`),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE</code> 
     </div> 
     <div> 
      <code>) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8 |</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

查询语句

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt;&nbsp; EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G</code> 
     </div> 
     <div> 
      <code>*************************** 1. row ***************************</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>id: 1</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>select_type: SIMPLE</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>table: inventory</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>type: index</code> 
     </div> 
     <div> 
      <code>possible_keys: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key: idx_store_id_film_id</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key_len: 3</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>ref: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>rows: 4581</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Extra: Using index</code> 
     </div> 
     <div> 
      <code>1 row in set (0.03 sec)</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt; EXPLAIN SELECT actor_id, last_name&nbsp; FROM sakila.actor WHERE last_name = </code> 
      <code>'HOPPER'</code> 
      <code>\G</code> 
     </div> 
     <div> 
      <code>*************************** 1. row ***************************</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>id: 1</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>select_type: SIMPLE</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>table: actor</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>type: ref</code> 
     </div> 
     <div> 
      <code>possible_keys: idx_actor_last_name</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key: idx_actor_last_name</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key_len: 137</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>ref: </code> 
      <code>const</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>rows: 2</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Extra: Using where; Using index</code> 
     </div> 
     <div> 
      <code>1 row in set (0.00 sec)</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**使用索引进行排序**

MySQL中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描

利用索引进行排序操作是非常快的，而且可以利用同一索引同时进 行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序，如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引，其它情况都会使用filesort

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>CREATE TABLE `actor` (</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`actor_id` int(10) unsigned NOT NULL AUTO_INCREMENT,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`name` varchar(16) NOT NULL DEFAULT </code> 
      <code>''</code> 
      <code>,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`password` varchar(16) NOT NULL DEFAULT </code> 
      <code>''</code> 
      <code>,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>PRIMARY KEY (`actor_id`),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>KEY `name` (`name`)</code> 
     </div> 
     <div> 
      <code>) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;</code> 
     </div> 
     <div> 
      <code>insert into actor(name,password) values (</code> 
      <code>'cat01'</code> 
      <code>,</code> 
      <code>'1234567'</code> 
      <code>),(</code> 
      <code>'cat02'</code> 
      <code>,</code> 
      <code>'1234567'</code> 
      <code>),(</code> 
      <code>'ddddd'</code> 
      <code>,</code> 
      <code>'1234567'</code> 
      <code>),(</code> 
      <code>'aaaaa'</code> 
      <code>,</code> 
      <code>'1234567'</code> 
      <code>);</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

1、 explain select actor\_id from actor order by actor\_id \\G

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt; explain select actor_id from actor order by actor_id \G</code> 
     </div> 
     <div> 
      <code>*************************** 1. row ***************************</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>id: 1</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>select_type: SIMPLE</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>table: actor</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>type: index</code> 
     </div> 
     <div> 
      <code>possible_keys: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key: PRIMARY</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key_len: 4</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>ref: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>rows: 4</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Extra: Using index</code> 
     </div> 
     <div> 
      <code>1 row in set (0.00 sec)</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

2、explain select actor\_id from actor order by password \\G

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt; explain select actor_id from actor order by password \G</code> 
     </div> 
     <div> 
      <code>*************************** 1. row ***************************</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>id: 1</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>select_type: SIMPLE</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>table: actor</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>type: ALL</code> 
     </div> 
     <div> 
      <code>possible_keys: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key_len: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>ref: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>rows: 4</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Extra: Using filesort</code> 
     </div> 
     <div> 
      <code>1 row in set (0.00 sec)</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

3、explain select actor\_id from actor order by name \\G

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt; explain select actor_id from actor order by name \G</code> 
     </div> 
     <div> 
      <code>*************************** 1. row ***************************</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>id: 1</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>select_type: SIMPLE</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>table: actor</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>type: index</code> 
     </div> 
     <div> 
      <code>possible_keys: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key: name</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>key_len: 50</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>ref: NULL</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>rows: 4</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Extra: Using index</code> 
     </div> 
     <div> 
      <code>1 row in set (0.00 sec)</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）

对于filesort，MySQL有两种排序算法

**1、两遍扫描算法(Two passes)**

实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort\_buffer\_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns

注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小

**2、 一次扫描算法(single pass)**

该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出  
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max\_length\_for\_sort\_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max\_length\_for\_sort\_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。  
  
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”

*  作者：[踏雪无痕][Link 1]
 *  出处：[http://www.cnblogs.com/chenpingzhao/][http_www.cnblogs.com_chenpingzhao]
 *  本文版权归作者和博客园共有，如需转载，请联系**  [pingzhao1990\#163.com][pingzhao1990_163.com]**

[Link 1]: http://weibo.com/chenpingzhao
[http_www.cnblogs.com_chenpingzhao]: http://www.cnblogs.com/chenpingzhao/
[pingzhao1990_163.com]: mailto:pingzhao1990@163.com