MySQL的索引下推

一、什么事索引下推

MySQL的索引下推（Index Condition Pushdown, ICP）是一种优化查询的方式，它可以改善查询性能，特别是对于包含多个条件的查询。在没有索引下推的情况下，MySQL会先从索引中找到满足某一条件的行的指针，然后根据这些指针去表中检索完整的行，最后再应用其他的过滤条件来确定哪些行实际符合查询的要求。

引入索引下推后，MySQL能够在使用索引检索数据时就应用所有相关的过滤条件，从而减少需要从存储引擎中检索的数据行数。这意味着如果一个查询可以部分或完全通过索引来判断数据行是否符合条件，MySQL就会在索引层面就过滤掉那些不符合条件的行，避免了对它们的进一步检查。这样做的结果是减少了IO操作和提高了查询的效率。

例如，考虑一个简单的表employees，包含字段id（主键），name，和department_id，并且department_id上有一个索引。如果我们执行一个查询来找出特定部门中名字以某个字母开头的员工，如：

SELECT * FROM employees WHERE department_id = 10 AND name LIKE 'A%';
1

如果没有索引下推，MySQL会首先使用department_id索引找到所有属于部门10的员工，然后逐个检查这些员工的名字是否以’A’开头。如果使用了索引下推，MySQL会在使用department_id索引的同时，检查名字是否以’A’开头，这样就能直接过滤掉更多的不符合条件的行，减少了需要进一步检查的数据量。

简而言之，索引下推让MySQL在尽可能早的阶段就过滤掉不符合条件的数据，从而提升查询性能。

二、如何查看使用了索引下推

要确认一个SQL查询是否利用了索引下推（Index Condition Pushdown, ICP）优化，可以通过查看该查询的执行计划来确定。在MySQL中，可以使用EXPLAIN或EXPLAIN FORMAT=JSON命令来查看查询的执行计划。执行计划提供了MySQL如何执行查询的详细信息，包括是否使用了索引下推。

使用EXPLAIN

1. 执行EXPLAIN加上你的查询语句。例如：

   EXPLAIN SELECT * FROM your_table WHERE column1 = 'some_value' AND column2 LIKE 'prefix%';
   1

2. 查看输出结果中的Extra列。如果在Extra列中看到了Using index condition，这表示MySQL对这个查询使用了索引下推。

使用EXPLAIN FORMAT=JSON

对于更详细的分析，可以使用EXPLAIN FORMAT=JSON：

1. 执行EXPLAIN FORMAT=JSON加上你的查询语句。例如：

   EXPLAIN FORMAT=JSON SELECT * FROM your_table WHERE column1 = 'some_value' AND column2 LIKE 'prefix%';
   1

2. 在JSON输出结果中，查找attached_condition字段。如果查询使用了索引下推，你会在attached_condition中看到对应的过滤条件，这表明在使用索引时已经考虑了这些条件。

示例

假设有如下的EXPLAIN FORMAT=JSON输出：

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "your_table",
      "access_type": "ref",
      "possible_keys": ["index_on_column1"],
      "key": "index_on_column1",
      "used_key_parts": ["column1"],
      "attached_condition": "(`your_table`.`column2` like 'prefix%')"
    }
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

在这个例子中，attached_condition字段表明column2 LIKE 'prefix%'这个条件被用在了索引上，即使用了索引下推。

注意

• 确认SQL查询是否使用了索引下推，并理解为什么会这样，可以帮助你更好地优化数据库性能。
• 不是所有的查询都会从索引下推中受益。查询优化和索引设计需要根据实际的数据分布和查询模式来进行。
• MySQL的版本对支持的优化特性有影响，确保你的MySQL版本支持索引下推。从MySQL 5.6版本开始，ICP就已经被支持。

ICP主要用于优化那些可以部分或完全通过索引来判断数据行是否满足条件的查询。当查询的type为ref、eq_ref、或ref_or_null，并且需要访问完整表行以获取其他数据或应用额外的过滤条件时，ICP可以减少需要访问的行数，从而提升查询性能。然而，是否启用ICP还取决于MySQL优化器的成本评估。优化器会根据查询的具体情况和数据的分布，决定是否应用ICP来优化查询