什么是mysql覆盖索引和索引下推？

数据库查询是一个常见的操作，但是如果不注意优化，可能会导致性能下降，资源浪费，甚至系统崩溃。为了提高数据库查询效率，我们需要了解一些数据库优化的技术和原理，其中两个重要的概念就是覆盖索引和索引下推。

什么是覆盖索引？

覆盖索引是指一个索引包含了查询所需的所有字段，而不需要回表查询原始数据。例如，假设我们有一个用户表，包含了用户的id，姓名，年龄，性别等字段，我们想要查询所有年龄大于18岁的用户的姓名和性别，那么我们可以创建一个包含了姓名，性别和年龄的索引，这样就可以直接从索引中获取查询结果，而不需要再访问用户表。

覆盖索引的优点是：

- 减少了磁盘I/O，提高了查询速度

- 减少了内存占用，节省了资源

- 减少了锁的竞争，提高了并发能力,它可以避免回表查询原始数据，从而减少了对数据行的访问和锁定。如果使用索引覆盖，那么只需要对索引记录加锁，而不需要对数据记录加锁。这样就可以减少锁的范围和冲突，提高并发能力

覆盖索引的缺点是：

- 增加了索引的大小，占用了更多的磁盘空间

- 增加了数据更新的开销，因为每次更新数据都需要更新索引

因此，在创建覆盖索引时，我们需要权衡利弊，选择合适的字段和顺序，避免创建过多或过大的索引。

什么是索引下推？

索引下推是指在使用联合索引时，将过滤条件下推到索引层，从而减少回表的次数。例如，假设我们有一个订单表，包含了订单的id，用户id，商品id，价格等字段，我们想要查询所有用户id为1且价格大于1000的订单的商品id和价格，那么我们可以创建一个包含了用户id，价格和商品id的联合索引，这样在扫描索引时就可以先过滤掉不满足用户id为1或价格大于1000的记录，而不需要回表查询订单表。

与联合索引不同的是，联合索引只能在查询条件中包含索引的前缀部分时才能生效，而索引下推可以在查询条件中包含任意部分的索引时生效。 例如，如果一个联合索引是（a,b,c），那么只有当查询条件中包含a或者a和b时才能使用该索引，而如果查询条件中只包含b或者c时则不能使用该索引。 但是如果使用了索引下推，那么即使查询条件中只包含b或者c，也可以将这些条件下推到索引层面上进行过滤，从而减少回表的次数和范围。

我们来具体看一下，在没有使用索引下推的情况下，MySQL的查询：

• 存储引擎读取索引记录；

• 根据索引中的主键值，定位并读取完整的行记录；

• 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用索引下推的情况下，查询过程：

• 存储引擎读取索引记录（不是完整的行记录）；

• 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；

• 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；

• 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

索引下推的优点是：

- 减少了回表的次数和范围，提高了查询速度

- 减少了数据传输量和网络开销

索引下推的缺点是：

- 增加了索引扫描的复杂度和开销

- 可能会导致误判或漏判，因为索引层无法处理一些复杂或模糊的过滤条件

因此，在使用索引下推时，我们需要注意一些限制和条件，比如：

- 索引下推只适用于联合索引

- 索引下推只适用于部分数据类型和操作符

- 索引下推可能会受到优化器的影响

总结

覆盖索引和索引下推是两种提高数据库查询效率的技术。覆盖索引是指一个索引包含了查询所需的所有字段，而不需要回表查询原始数据。索引下推是指在使用联合索引时，将过滤条件下推到索引层，从而减少回表的次数。这两种技术都有各自的优缺点和适用场景，在使用时需要根据实