mysql 创建覆盖索引_Mysql覆盖索引 covering index 或者 index coverage

组合索引

提到组合索引，大家都知道“最左前缀”原则。例如，创建索引 idx_name_age (name,age) ,通常情况下，where age=50 或者 where age>50 之类的，是不会使用到idx_a_b的。那有没有特殊情况呢？

假设表是：

CREATE TABLE users (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

name varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

email varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

remember_token varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

created_at timestamp NULL DEFAULT NULL,

updated_at timestamp NULL DEFAULT NULL,

age int(11) DEFAULT NULL,

PRIMARY KEY (id),

UNIQUE KEY users_email_unique (email),

KEY idx_name_age (name,age)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

覆盖索引

innodb存储引擎支持覆盖索引(covering index)，或称索引覆盖(index coverage)，即从辅助索引中就能查到的记录，而不需要查询聚集索引中的记录。

使用覆盖索引的好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

几个sql语句的explain

explain SELECT name FROM test.users where age>50

possible_keys是null，表示确实没啥索引可用。

key却是idx_name_age，表示优化器出动了，它选择了idx_name_age这个二级索引。

注意select的字段是name，在idx_name_age这个二级索引中就能完成where的查找以及拿到select 的字段 name

explain SELECT name,age FROM test.users where age>50

同上

explain SELECT name,age,id FROM test.users where age>50

同上(注意二级索引都包含了主键[ 样例表主键字段是id]以便通过主键去聚簇索引查找其他字段。但是显然上述SQL语句并不需要，因为要select的字段在idx_name_age里都有了。)

explain SELECT name,age,id,email FROM test.users where age>50

这次没有使用到idx_name_age，因为要select的字段包含了email，在idx_name_age里面是没有的

explain SELECT count(1) FROM test.users where age>50

对于(a,b)这样的联合索引，对于b列的查询条件进行统计，如果是覆盖索引的，优化器也会选择该联合索引。