MySQL 覆盖索引

╰+攻爆jí腚メ 2022-06-05 02:53 284阅读 0赞

本文主要概述mysql的覆盖索引，以及几种常见的优化场景

内容概要

  • 聚集索引和辅助索引

  • 什么是覆盖索引

  • 几种优化场景

  • 总体建议

聚集索引和辅助索引

  • 聚集索引（主键索引）

—innodb存储引擎是索引组织表，即表中的数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据

—聚集索引的叶子节点称为数据页，数据页，数据页！重要的事说三遍。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

  • 辅助索引（二级索引）

—非主键索引

—叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值

辅助索引检索数据图示如下

02e814446da64610faadb2047447dac00bf1241e

由于检索数据时，总是先获取到书签值(主键值)，再返回查询，因此辅助索引也被称之为二级索引

什么是覆盖索引

覆盖索引（covering index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。也可以称之为实现了 索引覆盖。

那么，优点显而易见。辅助索引不包含一整行的记录，因此可以大大减少IO操作。覆盖索引是mysql dba常用的一种SQL优化手段

先看一个简单示例

f9f63889fcd0c60d5f837b1e0a633e8ad4b903a5

从执行计划看到，这个SQL语句只通过索引，就取到了所需要的数据，这个过程，就称为索引覆盖

几种优化场景

1、无where条件的查询优化

如下这个查询

5c0aee6dcacf2845c0b2824b5d2b808fe65daa95

执行计划中，type为ALL，代表进行了全表扫描，扫描的行数也与表的行数一致

如何改进？优化措施很简单，就是对这个查询列建立索引。如下，

  1. mysql> alter table t1 add key(staff_id);

我们再看一下优化之后的效果

  • 执行计划 
  1. mysql> explain select sql_no_cache count(staff_id) from t1\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.          type: index
  7. possible_keys: NULL
  8.           key: staff_id
  9.       key_len: 1
  10.           ref: NULL
  11.          rows: 1023849
  13. 1 row in set (0.00 sec)

Using index表示使用到了索引

  • 查询消耗

80af484e7e92311c289f0a473c6f684fb244effc

从时间消耗上来看，才100W的数据，已经有了比较明显的差别了

执行计划解读如下：

Possible_keys为null，说明没有where条件时优化器无法通过索引检索数据；

但是这里使用了索引的另外一个优点，即从索引中获取数据，减少了读取的数据块的数量

无where条件的查询，可以通过索引来实现索引覆盖查询，但前提条件是，查询返回的字段数足够少，更不用说select *之类的了。毕竟，建立key length过长的索引，始终不是一件好事情。

2、二次检索优化

如下这个查询，

  1. mysql> select sql_no_cache rental_date from t1 where inventory_id<80000;
  2. …
  3. …
  4. | 2005-08-23 15:08:00 |
  5. | 2005-08-23 15:09:17 |
  6. | 2005-08-23 15:10:42 |
  7. | 2005-08-23 15:15:02 |
  8. | 2005-08-23 15:15:19 |
  9. | 2005-08-23 15:16:32 |
  10. +---------------------+
  11. 79999 rows in set (0.13 sec)

执行计划：

  1. mysql> explain select sql_no_cache rental_date from t1 where inventory_id<80000\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.          type: range
  7. possible_keys: inventory_id
  8.           key: inventory_id
  9.       key_len: 3
  10.           ref: NULL
  11.          rows: 153734
  12.         Extra: Using index condition
  13. 1 row in set (0.00 sec)

从执行计划，我们看到，这个SQL其实是使用到了索引的，虽然查询的数据量很大，但是相对比全表扫描的性能消耗，优化器还是选择了索引。

更优的改进措施？

从上面执行计划中，我们发现Extra信息为Using index condition而不是Using index，这说明，使用的检索方式为二级检索，即79999个书签值被用来进行回表查询。可想而知，还是会有一定的性能消耗的

尝试针对这个SQL建立联合索引，如下

  1. mysql> alter table t1 add key(inventory_id,rental_date);

这个联合索引前置列为where子句的检索字段，第二个字段为查询返回的字段。下面来看下效果如何。

为避免优化器对索引的选择出现偏差，我们首先收集一下统计信息

  1. mysql> analyze table t1\G
  2. *************************** 1. row ***************************
  3.    Table: sakila.t1
  4.       Op: analyze
  5. Msg_type: status
  6. Msg_text: OK
  7. 1 row in set (0.03 sec)

执行计划

  1. mysql> explain select sql_no_cache rental_date from t1 where inventory_id<80000\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.          type: range
  7. possible_keys: inventory_id,inventory_id_2
  8.           key: inventory_id_2
  9.       key_len: 3
  10.           ref: NULL
  11.          rows: 162884
  13. 1 row in set (0.00 sec)

同样是使用索引，但这里的提示信息为Using index而不是Using index condition。这代表没有了回表查询的过程，也就是实现了索引覆盖

查询消耗

  1. mysql> select sql_no_cache rental_date from t1 where inventory_id<80000;
  2. …
  3. …
  4. | 2005-08-23 15:08:00 |
  5. | 2005-08-23 15:09:17 |
  6. | 2005-08-23 15:10:42 |
  7. | 2005-08-23 15:15:02 |
  8. | 2005-08-23 15:15:19 |
  9. | 2005-08-23 15:16:32 |
  10. +---------------------+
  11. 79999 rows in set (0.09 sec)

从执行时间上来看，快了大约40ms，虽然只有40ms，但在实际的生产环境下，却可能会因系统的总体负载被无限放大。

和前面场景限制类似，当where条件与查询字段总数较少的情况下，使用这种优化建议，是个不错的选择。

3、分页查询优化

分页查询的优化，相信大部分的DBA同学都碰到过，通常比较常规的优化手段就是查询改写，这里主要介绍一下新的思路，就是通过索引覆盖来优化

如下这个查询场景

  1. mysql> select tid,return_date from t1 order by inventory_id limit 50000,10;
  2. +-------+---------------------+
  3. | tid   | return_date         |
  4. +-------+---------------------+
  5. | 50001 | 2005-06-17 23:04:36 |
  6. | 50002 | 2005-06-23 03:16:12 |
  7. | 50003 | 2005-06-20 22:41:03 |
  8. | 50004 | 2005-06-23 04:39:28 |
  9. | 50005 | 2005-06-24 04:41:20 |
  10. | 50006 | 2005-06-22 22:54:10 |
  11. | 50007 | 2005-06-18 07:21:51 |
  12. | 50008 | 2005-06-25 21:51:16 |
  13. | 50009 | 2005-06-21 03:44:32 |
  14. | 50010 | 2005-06-19 00:00:34 |
  15. +-------+---------------------+
  16. 10 rows in set (0.75 sec)

在未优化之前，我们看到它的执行计划是如此的糟糕

  1. mysql> explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.          type: ALL
  7. possible_keys: NULL
  8.           key: NULL
  9.       key_len: NULL
  10.           ref: NULL
  11.          rows: 1023675
  13. 1 row in set (0.00 sec)

全表扫描，加上额外的排序，相信产生的性能消耗是不低的

如何通过覆盖索引优化呢？

我们创建一个索引，包含排序列以及返回列，由于tid是主键字段，因此，下面的复合索引就包含了tid的字段值 

  1. mysql> alter table t1 add index liu(inventory_id,return_date);
  2. Query OK, 0 rows affected (3.11 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. mysql> analyze table t1\G
  6. *************************** 1. row ***************************
  7.    Table: sakila.t1
  8.       Op: analyze
  9. Msg_type: status
  10. Msg_text: OK
  11. 1 row in set (0.04 sec)

那么，效果如何呢？

  1. mysql> select tid,return_date from t1 order by inventory_id limit 50000,10;
  2. +-------+---------------------+
  3. | tid   | return_date         |
  4. +-------+---------------------+
  5. | 50001 | 2005-06-17 23:04:36 |
  6. | 50002 | 2005-06-23 03:16:12 |
  7. | 50003 | 2005-06-20 22:41:03 |
  8. | 50004 | 2005-06-23 04:39:28 |
  9. | 50005 | 2005-06-24 04:41:20 |
  10. | 50006 | 2005-06-22 22:54:10 |
  11. | 50007 | 2005-06-18 07:21:51 |
  12. | 50008 | 2005-06-25 21:51:16 |
  13. | 50009 | 2005-06-21 03:44:32 |
  14. | 50010 | 2005-06-19 00:00:34 |
  15. +-------+---------------------+
  16. 10 rows in set (0.03 sec)

可以发现，添加复合索引后，速度提升0.7s！

我们看一下改进后的执行计划

  1. mysql> explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.          type: index
  7. possible_keys: NULL
  8.           key: liu
  9.       key_len: 9
  10.           ref: NULL
  11.          rows: 50010
  13. 1 row in set (0.00 sec)

执行计划也可以看到，使用到了复合索引，并且不需要回表

接下来，我们对比一下，索引覆盖与常规优化手段的效果差异

为了让结果更明显，我将查询修改为 limit 800000,10

现在看一下，通过覆盖索引查询的性能消耗

  1. mysql> select tid,return_date from t1 order by inventory_id limit 800000,10;
  2. +--------+---------------------+
  3. | tid    | return_date         |
  4. +--------+---------------------+
  5. | 800001 | 2005-08-24 13:09:34 |
  6. | 800002 | 2005-08-27 11:41:03 |
  7. | 800003 | 2005-08-22 18:10:22 |
  8. | 800004 | 2005-08-22 16:47:23 |
  9. | 800005 | 2005-08-26 20:32:02 |
  10. | 800006 | 2005-08-21 14:55:42 |
  11. | 800007 | 2005-08-28 14:45:55 |
  12. | 800008 | 2005-08-29 12:37:32 |
  13. | 800009 | 2005-08-24 10:38:06 |
  14. | 800010 | 2005-08-23 12:10:57 |
  15. +--------+---------------------+

与之对比的是如下改写SQL方式

改写后的sql如下，思想是通过索引消除排序

  1. select a.tid,a.return_date from  t1 a 
  2. inner join 
  3. (select tid from t1 order by  inventory_id limit 800000,10) b on a.tid=b.tid;

并在此基础上，我们为inventory_id列创建索引，并删除之前的覆盖索引

  1. mysql> alter table t1 add index idx_inid(inventory_id),drop index liu;

然后收集统计信息。

查询消耗如下 

  1. mysql> select a.tid,a.return_date from  t1 a inner join  (select tid from t1 order by  inventory_id limit 800000,10) b on a.tid=b.tid;
  2. +--------+---------------------+
  3. | tid    | return_date         |
  4. +--------+---------------------+
  5. | 800001 | 2005-08-24 13:09:34 |
  6. | 800002 | 2005-08-27 11:41:03 |
  7. | 800003 | 2005-08-22 18:10:22 |
  8. | 800004 | 2005-08-22 16:47:23 |
  9. | 800005 | 2005-08-26 20:32:02 |
  10. | 800006 | 2005-08-21 14:55:42 |
  11. | 800007 | 2005-08-28 14:45:55 |
  12. | 800008 | 2005-08-29 12:37:32 |
  13. | 800009 | 2005-08-24 10:38:06 |
  14. | 800010 | 2005-08-23 12:10:57 |
  15. +--------+---------------------+

可以看到，这种优化手段较前者时间消耗多了大约140ms。

这种优化手段虽然使用索引消除了排序，但是还是要通过主键值回表查询。因此，在select返回列较少或列宽较小的时候，我们可以通过建立复合索引的方式优化分页查询，效果更佳，因为它不需要回表！

总体建议

索引具有以下两大用处：

1、通过索引检索仅需要数据

2、从索引中直接获取查询结果

覆盖索引的优势，就是利用到索引的第二大用处，在某些场景下，具有意想不到的优化效果。个人总结如下：

  • Select查询的返回列包含在索引列中

  • 有where条件时，where条件中要包含索引列或复合索引的前导列

  • 查询结果的总字段长度可以接受

from: https://yq.aliyun.com/articles/62419

发表评论

表情：
评论列表 （有 0 条评论，284人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql 覆盖索引

    覆盖索引：只有索引包含了where条件部分和select返回部分的所有字段，才能实现这个目的(只要查询的条件和返回字段中的 所有字段都能被一个非主键索引覆盖，就能用覆盖索引)

    柔光的暖阳◎柔光的暖阳◎/ 0 赞/ 84 阅读

    相关 MySQL 回表 & 索引覆盖

    索引类型 聚簇索引： 叶子节点存储的是行记录，每个表必须要有至少一个聚簇索引。使用聚簇索引查询会很快，因为可以直接定位到行记录 普通索引：二级索引，除聚簇索引外的索引

    Love The Way You LieLove The Way You Lie/ 0 赞/ 240 阅读

    相关 MySQL覆盖索引

    如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。 只扫描索引而无需回表的优点：     1.索引条目通常远小于数据行大小，

    深碍√TFBOYSˉ_深碍√TFBOYSˉ_/ 0 赞/ 300 阅读

    相关 mysql-覆盖索引

    什么叫做覆盖索引？ 解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。 解释二： 索引是高效找

    Myth丶恋晨Myth丶恋晨/ 0 赞/ 356 阅读

    相关 mysql 优化覆盖索引

    如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。 只扫描索引而无需回表的优点：     1.索引条目通常远小于数据行大小，

    淩亂°似流年淩亂°似流年/ 0 赞/ 332 阅读