MySQL高性能的索引策略（三）

最新推荐文章于 2025-08-15 10:38:51 发布

weixin_34405332

最新推荐文章于 2025-08-15 10:38:51 发布

阅读量73

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/xinxingegeya/blog/495800

本文介绍了MySQL中覆盖索引的概念及其如何提高查询性能。通过使用包含查询所需所有数据的索引，可以减少I/O操作并加快检索速度。文章还探讨了覆盖索引的适用条件及限制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

MySQL高性能的索引策略（三）

使用覆盖索引

如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

(1)索引项通常比记录要小，所以MySQL访问更少的数据；

(2)索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；

(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。

覆盖索引的实际应用，如下表结构，

CREATE TABLE people (
   id int not null AUTO_INCREMENT,
   last_name varchar(50)    not null,
   first_name varchar(50)    not null,
   dob        date           not null,
   gender     enum('m', 'f') not null,
   primary key (id),
   key(last_name, first_name, dob)
);

如下查询，explain，

> explain select id , last_name , first_name from people

******************** 1. row *********************
           id: 1
  select_type: SIMPLE
        table: people
         type: index
possible_keys: 
          key: last_name
      key_len: 107
          ref: 
         rows: 1
        Extra: Using index
1 rows in set

可以看到Extra列是 Using index，所以只在二级索引中就找到了需要的数据，不要再去聚簇索引中根据主键查找需要的数据行。

但是要注意的是不是所有的类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等都不存储索引列的值，索引mysql只能使用

B－Tree索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

我们再来看下面这种情况。假设索引覆盖了WHERE条件中的字段，但不是整个查询涉及的字段。

> explain
select * from people where last_name = 'hello' and first_name like '&hello&'

******************** 1. row *********************
           id: 1
  select_type: SIMPLE
        table: people
         type: range
possible_keys: last_name
          key: last_name
      key_len: 104
          ref: 
         rows: 1
        Extra: Using index condition
1 rows in set

好，看到了吧，出现了using index condition，先把mysql的ICP（索引条件推送）优化关掉，

set optimizer_switch = 'index_condition_pushdown=off';

> explain
select * from people where last_name = 'hello' and first_name like '&hello&'

******************** 1. row *********************
           id: 1
  select_type: SIMPLE
        table: people
         type: range
possible_keys: last_name
          key: last_name
      key_len: 104
          ref: 
         rows: 1
        Extra: Using where
1 rows in set

这里的索引无法覆盖该查询，有两个原因：