本文主要概述mysql的覆盖索引,以及几种常见的优化场景
内容概要
- 聚集索引和辅助索引
- 什么是覆盖索引
- 几种优化场景
- 总体建议
聚集索引和辅助索引
- 聚集索引(主键索引)
—innodb存储引擎是索引组织表,即表中的数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的即为整张表的记录数据
—聚集索引的叶子节点称为数据页,数据页,数据页!重要的事说三遍。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。
- 辅助索引(二级索引)
—非主键索引
—叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值
辅助索引检索数据图示如下
![02e814446da64610faadb2047447dac00bf1241e](https://i-blog.csdnimg.cn/blog_migrate/33c5fa6960804a20f578e68c0e8b8397.png)
由于检索数据时,总是先获取到书签值(主键值),再返回查询,因此辅助索引也被称之为二级索引
什么是覆盖索引
覆盖索引(covering index)指一个查询语句的执行只需要从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录。也可以称之为实现了
索引覆盖。
那么,优点显而易见。辅助索引不包含一整行的记录,因此可以大大减少IO操作。覆盖索引是mysql dba常用的一种SQL优化手段
先看一个简单示例
![f9f63889fcd0c60d5f837b1e0a633e8ad4b903a5](https://i-blog.csdnimg.cn/blog_migrate/de158144b379c7eb5b8d5c03a44302d6.png)
从执行计划看到,这个SQL语句只通过索引,就取到了所需要的数据,这个过程,就称为索引覆盖
几种优化场景
1、无where条件的查询优化
如下这个查询
![5c0aee6dcacf2845c0b2824b5d2b808fe65daa95](https://i-blog.csdnimg.cn/blog_migrate/15cf39bcdfade4ded7cad33a0eb66eb7.png)
执行计划中,type为ALL,代表进行了全表扫描,扫描的行数也与表的行数一致
如何改进?优化措施很简单,就是对这个查询列建立索引。如下,
mysql> alter table t1 add key(staff_id);
我们再看一下优化之后的效果
- 执行计划
mysql> explain select sql_no_cache count(staff_id) from t1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: staff_id
key_len: 1
ref: NULL
rows: 1023849
Extra: Using index
1 row in set (0.00 sec)
Using index表示使用到了索引
- 查询消耗
![80af484e7e92311c289f0a473c6f684fb244effc](https://i-blog.csdnimg.cn/blog_migrate/c3cdc339125545590b518f1ce18c9342.png)
从时间消耗上来看,才100W的数据,已经有了比较明显的差别了
执行计划解读如下:
Possible_keys为null,说明没有where条件时优化器无法通过索引检索数据;
但是这里使用了索引的另外一个优点,即从索引中获取数据,减少了读取的数据块的数量
无where条件的查询,可以通过索引来实现索引覆盖查询,但前提条件是,查询返回的字段数足够少,更不用说select *之类的了。毕竟,建立key length过长的索引,始终不是一件好事情。
2、二次检索优化
如下这个查询,
mysql> select sql_no_cache rental_date from t1 where inventory_id<80000;
…
…
| 2005-08-23 15:08:00 |
| 2005-08-23 15:09:17 |
| 2005-08-23 15:10:42 |
| 2005-08-23 15:15:02 |
| 2005-08-23 15:15:19 |
| 2005-08-23 15:16:32 |
+---------------------+
79999 rows in set (0.13 sec)
执行计划:
mysql> explain select sql_no_cache rental_date from t1 where inventory_id<80000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: inventory_id
key: inventory_id
key_len: 3
ref: NULL
rows: 153734
Extra: Using index condition
1 row in set (0.00 sec)
从执行计划,我们看到,这个SQL其实是使用到了索引的,虽然查询的数据量很大,但是相对比全表扫描的性能消耗,优化器还是选择了索引。
更优的改进措施?
从上面执行计划中,我们发现Extra信息为Using index condition而不是Using index,这说明,使用的检索方式为二级检索,即79999个书签值被用来进行回表查询。可想而知,还是会有一定的性能消耗的
尝试针对这个SQL建立联合索引,如下
mysql> alter table t1 add key(inventory_id,rental_date);
这个联合索引前置列为where子句的检索字段,第二个字段为查询返回的字段。下面来看下效果如何。
为避免优化器对索引的选择出现偏差,我们首先收集一下统计信息
mysql> analyze table t1\G
*************************** 1. row ***************************
Table: sakila.t1
Op: analyze
Msg_type: status
Msg_text: OK
1 row in set (0.03 sec)
执行计划
mysql> explain select sql_no_cache rental_date from t1 where inventory_id<80000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: inventory_id,inventory_id_2
key: inventory_id_2
key_len: 3
ref: NULL
rows: 162884
Extra: Using index
1 row in set (0.00 sec)
同样是使用索引,但这里的提示信息为Using index而不是Using index condition。这代表没有了回表查询的过程,也就是实现了索引覆盖
查询消耗
mysql> select sql_no_cache rental_date from t1 where inventory_id<80000;
…
…
| 2005-08-23 15:08:00 |
| 2005-08-23 15:09:17 |
| 2005-08-23 15:10:42 |
| 2005-08-23 15:15:02 |
| 2005-08-23 15:15:19 |
| 2005-08-23 15:16:32 |
+---------------------+
79999 rows in set (0.09 sec)
从执行时间上来看,快了大约40ms,虽然只有40ms,但在实际的生产环境下,却可能会因系统的总体负载被无限放大。
和前面场景限制类似,当where条件与查询字段总数较少的情况下,使用这种优化建议,是个不错的选择。
3、分页查询优化
分页查询的优化,相信大部分的DBA同学都碰到过,通常比较常规的优化手段就是查询改写,这里主要介绍一下新的思路,就是通过索引覆盖来优化
如下这个查询场景
mysql> select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid | return_date |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.75 sec)
在未优化之前,我们看到它的执行计划是如此的糟糕
mysql> explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1023675
Extra: Using filesort
1 row in set (0.00 sec)
全表扫描,加上额外的排序,相信产生的性能消耗是不低的
如何通过覆盖索引优化呢?
我们创建一个索引,包含排序列以及返回列,由于tid是主键字段,因此,下面的复合索引就包含了tid的字段值
那么,效果如何呢?
mysql> alter table t1 add index liu(inventory_id,return_date);
Query OK, 0 rows affected (3.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> analyze table t1\G
*************************** 1. row ***************************
Table: sakila.t1
Op: analyze
Msg_type: status
Msg_text: OK
1 row in set (0.04 sec)
mysql> select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid | return_date |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.03 sec)
可以发现,添加复合索引后,速度提升0.7s!
我们看一下改进后的执行计划
mysql> explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: liu
key_len: 9
ref: NULL
rows: 50010
Extra: Using index
1 row in set (0.00 sec)
执行计划也可以看到,使用到了复合索引,并且不需要回表
接下来,我们对比一下,索引覆盖与常规优化手段的效果差异
为了让结果更明显,我将查询修改为 limit 800000,10
现在看一下,通过覆盖索引查询的性能消耗
mysql> select tid,return_date from t1 order by inventory_id limit 800000,10;
+--------+---------------------+
| tid | return_date |
+--------+---------------------+
| 800001 | 2005-08-24 13:09:34 |
| 800002 | 2005-08-27 11:41:03 |
| 800003 | 2005-08-22 18:10:22 |
| 800004 | 2005-08-22 16:47:23 |
| 800005 | 2005-08-26 20:32:02 |
| 800006 | 2005-08-21 14:55:42 |
| 800007 | 2005-08-28 14:45:55 |
| 800008 | 2005-08-29 12:37:32 |
| 800009 | 2005-08-24 10:38:06 |
| 800010 | 2005-08-23 12:10:57 |
+--------+---------------------+
10 rows in set (0.26 sec)
与之对比的是如下改写SQL方式
改写后的sql如下,思想是通过索引消除排序
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;
mysql> alter table t1 add index idx_inid(inventory_id),drop index liu;
然后收集统计信息。
查询消耗如下
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;
+--------+---------------------+
| tid | return_date |
+--------+---------------------+
| 800001 | 2005-08-24 13:09:34 |
| 800002 | 2005-08-27 11:41:03 |
| 800003 | 2005-08-22 18:10:22 |
| 800004 | 2005-08-22 16:47:23 |
| 800005 | 2005-08-26 20:32:02 |
| 800006 | 2005-08-21 14:55:42 |
| 800007 | 2005-08-28 14:45:55 |
| 800008 | 2005-08-29 12:37:32 |
| 800009 | 2005-08-24 10:38:06 |
| 800010 | 2005-08-23 12:10:57 |
+--------+---------------------+
10 rows in set (0.40 sec)
可以看到,这种优化手段较前者时间消耗多了大约140ms。
这种优化手段虽然使用索引消除了排序,但是还是要通过主键值回表查询。因此,在select返回列较少或列宽较小的时候,我们可以通过建立复合索引的方式优化分页查询,效果更佳,因为它不需要回表!
总体建议
索引具有以下两大用处:
1、通过索引检索仅需要数据
2、从索引中直接获取查询结果
覆盖索引的优势,就是利用到索引的第二大用处,在某些场景下,具有意想不到的优化效果。个人总结如下:
- Select查询的返回列包含在索引列中
- 有where条件时,where条件中要包含索引列或复合索引的前导列
- 查询结果的总字段长度可以接受