MySQL覆盖索引的使用案例

oracleace 2025-11-21

119

覆盖索引（covering index）指一个SQL语句只读取索引就可以获得需要的数据，不需要访问表，这样大大提高了I/O的效率，原因如下：

不需要访问表减少了I/O的次数。
索引通常比表小很多。
由于索引是按照键值顺序存储的（至少在一个页内是这样），对于按照键值进行范围查询时使用的是顺序I/O，相对于离散I/O性能大大提高。

在使用覆盖索引时，执行计划的Extra字段中有Using index的信息，下面是一个SQL语句的执行计划：

mysql>explain select customer_id,inventory_id,rental_date from rental\G
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: index
possible_keys: NULL
key: rental_date
key_len: 10
ref: NULL
rows: 16008
filtered: 100.00
Extra: Using index
1row in set, 1 warning (0.01 sec)

这个SQL语句没有where条件，但仍然访问了rental_date索引，而且没有访问表，查询rental_date索引的构成：

mysql> select column_name from information_schema.statistics where index_name='rental_date';
+--------------+
| column_name  |
+--------------+
| rental_date  |
| inventory_id |
| customer_id  |
+--------------+
3 rows in set (0.01 sec)

发现这个SQL语句要查询的3个字段customer_id,inventory_id,rental_date都包含在这个索引中了，因此只要访问这个索引即可得到所有需要的数据，就没有必要再访问表了。由于二级索引实质上都包含主键，因此如果再加上主键，一样可以使用覆盖索引，下面是在输出字段中加上主键字段的SQL语句的执行计划：

mysql>explain select rental_id,customer_id,inventory_id,rental_date from rental\G
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: index
possible_keys: NULL
key: rental_date
key_len: 10
ref: NULL
rows: 16008
filtered: 100.00
Extra: Using index
1row in set, 1 warning (0.00 sec)

可以看到一样使用了覆盖索引。但如果要访问的字段不在这个索引中，则还需要访问表，如果在上面的查询字段中再增加任意一个其他字段就不能使用覆盖索引了，例如下面的SQL语句将无法使用覆盖索引：

mysql>explain select * from  rental where rental_date='2005-05-24 22:53:30' and inventory_id=367\G
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ref
possible_keys: rental_date,idx_fk_inventory_id
key: rental_date
key_len: 8
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1row in set, 1 warning (0.00 sec)

这个SQL的执行计划的字段Extra里没有Using index，因此没有使用覆盖索引。但可以对这个SQL语句进行改写，先用一个可以使用覆盖索引的子查询查询出主键，再通过主键查找相应的记录，这种方法称之为延迟关联（deferred join），改写后的SQL语句执行计划如下：

mysql>explain select * from rental where rental_id in (select rental_id from rental where rental_date='2005-05-24 22:53:30' and inventory_id=367)\G
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ref
possible_keys: PRIMARY,rental_date,idx_fk_inventory_id
key: rental_date
key_len: 8
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index
***************************2. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sakila.rental.rental_id
rows: 1
filtered: 100.00
Extra: NULL
2rows in set, 1 warning (0.00 sec)

可以看到在第一步的查询中使用了覆盖索引。

延迟关联的方法在分页查询中对效率的提高很明显，例如下面的SQL语句进行排序后从1000行开始查询5行，它的执行计划如下：

mysql> explain analyze select * from rental order by rental_date limit 1000,5\G
*************************** 1. row ***************************
EXPLAIN: -> Limit/Offset: 5/1000 row(s)  (cost=1625.05 rows=5) (actual time=17.487..17.488 rows=5 loops=1)
    -> Sort: rental.rental_date, limit input to 1005 row(s) per chunk  (cost=1625.05 rows=16008) (actual time=17.254..17.435 rows=1005 loops=1)
        -> Table scan on rental  (cost=1625.05 rows=16008) (actual time=0.332..11.348 rows=16044 loops=1)


1 row in set (0.02 sec)

这个SQL语句要进行全表扫描和文件排序，成本很高，使用延迟关联改写后的SQL语句执行计划如下：

mysql> explain analyze select * from rental r1 inner join (select rental_id from rental order by rental_date limit 1000,5) r2 on r1.rental_id=r2.rental_id\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=259.58 rows=5) (actual time=7.015..7.067 rows=5 loops=1)
    -> Table scan onr2  (cost=115.56 rows=1005) (actual time=0.002..0.003 rows=5 loops=1)
        -> Materialize  (cost=7.83 rows=5) (actual time=6.836..6.838 rows=5 loops=1)
            -> Limit/Offset: 5/1000 row(s)  (cost=7.83 rows=5) (actual time=6.641..6.644 rows=5 loops=1)
                -> Index scan on rental usingrental_date  (cost=7.83 rows=1005) (actual time=3.762..6.568 rows=1005 loops=1)
    -> Single-row index lookup on r1 usingPRIMARY (rental_id=r2.rental_id)  (cost=0.25 rows=1) (actual time=0.044..0.044 rows=1 loops=5)


1 row inset (0.02 sec)

改写后的SQL充分使用了索引和主键，估算成本从1625.05下降到259.58，效率大大提高。

欢迎关注我的公众号

入群，或者需要我帮您免费解读Oracle AWR报告，请加我的微信

文章转载自oracleace，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL覆盖索引的使用案例

评论