什么是覆盖索引和索引下推？一、什么是覆盖索引？在 MySQL 的查询优化过程中，覆盖索引是一种常见的优化技术。顾名思义

一、什么是覆盖索引？

mysql的innodb引擎通过搜索树方式实现索引，索引类型分为主键索引和二级索引（非主键索引），主键索引树中，叶子结点保存着主键即对应行的全部数据；而二级索引树中，叶子结点保存着索引值和主键值，当使用二级索引进行查询时，需要进行回表操作。

在 MySQL 的查询优化过程中，覆盖索引是一种常见的优化技术。顾名思义，覆盖索引是指一个查询可以仅通过索引就能够返回所需的所有列，而无需再次到表中进行查找。

传统的索引通常只包含关键字和指向实际数据的指针，因此在查找时需要再次到表中进行查找，以获取其他列的数据。而覆盖索引则将覆盖列也包含在了索引中，可以直接从索引中返回所有需要的列，从而避免了二次查找的开销，提高了查询效率。

二、覆盖索引的原理

索引结构：为了使用覆盖索引，需要创建一个包含所有需要返回的列的联合索引。联合索引是由多个列组成的索引，其顺序对查询的效率有影响。
查询语句：使用覆盖索引需要注意查询语句的书写方式。查询语句中要求返回的列必须包含在联合索引中，并且需要使用索引列进行查询条件过滤。

例如，下面的查询语句可以使用联合索引进行优化：

SELECT id, name FROM users WHERE age > 18;

其中，联合索引包含了 (age, id, name) 三个列，查询语句使用了 age 列作为过滤条件，同时需要返回 id 和 name 两个列的值。由于联合索引中包含了要返回的两个列，因此可以直接从索引中获取数据，避免了二次查找的开销。

索引下推：MySQL 还支持一种名为“索引下推”（Index Condition Pushdown，简称 ICP）的优化技术，可以进一步提高覆盖索引的效率。

索引下推指的是把 WHERE 子句中的一些条件下推到存储引擎层面进行过滤，减少要读取的行数。当查询语句使用覆盖索引时，MySQL 可以在索引中直接检测 WHERE 子句中的条件，而不必去加载表中的行，从而减少了 I/O 操作和 CPU 计算等开销。

三、覆盖索引的优缺点

优点：

（1）避免了二次查找：使用覆盖索引可以直接从索引中返回所有需要的列，避免了再次到表中进行查找的开销，提高了查询效率。

（2）减少了 I/O 操作：覆盖索引通常可以使用索引下推技术，直接在索引中进行过滤，从而减少了要读取的行数，降低了 I/O 操作的开销。

缺点：

（1）对索引的要求较高：使用覆盖索引必须创建一个包含所有需要返回的列的联合索引，而联合索引的效率和使用场景都有一定限制，否则可能会导致索引扫描的代价比较大。

（2）占用更多的空间：覆盖索引包含了所有需要返回的列，因此会占用更多的存储空间，而且在修改表数据时需要更新索引，也会带来额外的开销。

四、索引的示例

好的，以下将通过示例代码详细解释覆盖索引的使用方法和原理。

假设有一个 MySQL 数据库中的 users 表，包含以下列：id、name、age、gender 和 city。现在需要查询年龄超过 18 岁的用户的 id 和 name 两个列的值，并通过覆盖索引进行优化。

创建联合索引

为了使用覆盖索引，需要在 age、id 和 name 三个列上创建联合索引，可以使用如下的 SQL 语句进行创建：

CREATE INDEX age_id_name ON users (age, id, name);

上述语句中，age、id 和 name 三个列分别作为联合索引的前缀，用逗号隔开，表示按照这三个列的顺序创建索引。创建完成后，就可以使用覆盖索引进行查询了。

查询语句

覆盖索引的使用需要注意查询语句的书写方式。查询语句中要求返回的列必须包含在联合索引中，并且需要使用索引列进行查询条件过滤。

例如，下面的查询语句可以使用联合索引进行优化：

SELECT id, name FROM users WHERE age > 18;

需要注意的是，查询语句中要求返回的列必须包含在联合索引中，否则无法使用覆盖索引进行优化。例如，如果查询语句需要返回 age 和 name 两个列的值，就无法使用上述的联合索引进行优化。

索引下推

MySQL 还支持一种名为“索引下推”（Index Condition Pushdown，简称 ICP）的优化技术，可以进一步提高覆盖索引的效率。

例如，下面的查询语句使用了索引下推技术：

SELECT id, name FROM users WHERE age > 18 AND city = 'Beijing';

该查询语句在查询 age 大于 18 并且 city 等于 'Beijing' 的用户，并返回 id 和 name 两个列的值。由于联合索引中已经包含了 age、id 和 name 三个列，因此可以使用覆盖索引进行优化。同时，MySQL 还可以将 WHERE 子句中的 city 条件下推到存储引擎层面进行过滤，进一步减少了要读取的行数。

总之，覆盖索引和索引下推是 MySQL 查询优化中常用的技术，可以显著提高查询效率和降低数据库的负载。在使用时，需要根据具体的业务场景和查询需求进行选择和优化，以达到最佳的性能表现。

五、总结

覆盖索引：

在某个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。
覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

索引下推：

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

覆盖索引是一种优化数据库查询性能的有效方法，可以避免重复查找数据的开销，减少 I/O 操作的负担。但是，它也存在一定的限制和缺陷，需要谨慎使用。在实际应用中，需要根据具体的业务场景和查询需求进行选择和优化，以提高数据库的性能表现。