mysql 优化器是如何选择索引的？

MySQL 选择索引的过程是基于优化器的成本估算来决定的，它会根据索引的区分度、查询条件、统计信息、数据量等因素来选择最优的执行计划。以下是 MySQL 选择索引的核心逻辑和优化器决策过程。 ## 1. MySQL 索引选择策略 MySQL 的优化器会评估可用的索引，并选择成本最低的索引进行查询。优化器主要基于以下因素做决策： 1. **索引的选择范围（索引过滤率）** - 索引的区分度（Cardinality）越高，索引越可能被选择 - `SHOW INDEX FROM person;` 可以查看 `Cardinality` 值 - 计算方式： ``` 索引区分度 = 唯一键值数量 / 总记录数 ``` - 通常，索引区分度低的索引不容易被选中，因为它无法有效过滤数据 2. **查询条件与索引匹配** - **单列索引**：如果 `WHERE` 条件命中索引列，优化器可能选择它 - **联合索引**：索引遵循最左前缀原则，只有前导列匹配时，索引才会被选择 - **范围查询 (`>`, `<`, `BETWEEN`)** 可能导致索引的后续字段失效 3. **索引 vs. 全表扫描的成本对比** - **CPU 成本**：扫描行数越多，CPU 成本越高，默认成本约为 `0.2` - **I/O 成本**：索引扫描涉及的磁盘页读取，默认每页的 I/O 成本约为 `1` - 成本计算公式： ``` 总成本 = 索引扫描成本 + 回表查询成本 ``` - 如果索引扫描的成本高于全表扫描，则 MySQL 可能会选择全表扫描 ## 2. MySQL 选择索引案例分析 ### 案例 1：查询导致全表扫描 #### SQL 语句 ```sql EXPLAIN SELECT * FROM person WHERE NAME > 'name84059' AND create_time > '2023-04-15 13:00:00'; ``` #### 执行计划 ``` type=ALL key=NULL ``` - `type=ALL` 表示全表扫描，说明没有选择索引 - 可能的原因： 1. `NAME > 'name84059'` 和 `create_time > '2023-04-15 13:00:00'` 组合导致索引区分度不高 2. 估算后，MySQL 认为全表扫描比索引扫描更高效 ### 案例 2：修改查询条件后选择索引 #### SQL 语句 ```sql EXPLAIN SELECT * FROM person WHERE NAME > 'name84059' AND create_time > '2023-04-15 15:00:00'; ``` #### 执行计划 ``` type=range key=create_time ``` - `type=range` 表示索引范围扫描 - `key=create_time` 说明优化器选择 `idx_create_time` 索引 - 为什么这次选择索引？ - `create_time > '2023-04-15 15:00:00'` 过滤的数据更少，索引扫描的行数减少 - 索引的成本降低，比全表扫描更优 ## 3. 追踪 MySQL 索引选择过程 ### 使用 OPTIMIZER_TRACE MySQL 提供 `OPTIMIZER_TRACE` 来跟踪优化器的决策： ```sql SET OPTIMIZER_TRACE="enabled=on"; EXPLAIN SELECT * FROM person WHERE NAME > 'name84059' AND create_time > '2023-04-15 13:00:00'; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET OPTIMIZER_TRACE="enabled=off"; ``` ### OPTIMIZER_TRACE 结果分析 #### 方案 1：使用 `name_score` 索引 ```json { "index": "idx_name_score", "rows": 26420, "cost": 31705, "chosen": false } ``` - 需要扫描 **26420** 行，成本 **31705** - 回表查询的成本太高，导致索引未被选择 #### 方案 2：使用 `create_time` 索引 ```json { "index": "idx_create_time", "rows": 6599, "cost": 7919.8, "chosen": true } ``` - 需要扫描 **6599** 行，成本 **7919.8** - 索引过滤率高，成本低于全表扫描，所以被选择 #### 方案 3：全表扫描 ```json { "rows": 100064, "cost": 20366, "chosen": false } ``` - 全表扫描的成本 **20366**，比 `idx_create_time` 索引高，因此被优化器放弃 ## 4. 人工干预索引选择 有时候优化器的决策不理想，可以使用以下方法强制索引： ### 强制使用 `name_score` 索引 ```sql EXPLAIN SELECT * FROM person FORCE INDEX(idx_name_score) WHERE NAME > 'name84059' AND create_time > '2023-04-15 13:00:00'; ``` - `FORCE INDEX(idx_name_score)` 强制优化器选择 `idx_name_score` 索引 - 适用场景： - 优化器误判索引成本，导致错误选择索引 - `ANALYZE TABLE person;` 重新收集统计信息 ## 5. 索引优化技巧 ### 1. 提高索引区分度 - 索引字段的唯一值越多，区分度越高，索引才会被 MySQL 选择 - 计算方法： ```sql SELECT COUNT(DISTINCT name) / COUNT(*) FROM person; ``` - 优化方案： - 选择唯一值更多的列作为索引 - 避免低区分度索引，例如 `gender` (`M/F`) 这种字段不适合作为索引 ### 2. 适当调整索引结构 - 创建更符合查询需求的索引： ```sql ALTER TABLE person ADD INDEX idx_name_create_time (name, create_time); ``` - 优化联合索引顺序： - `WHERE name=xxx AND create_time > xxx` → `(name, create_time)` 顺序合适 - `WHERE create_time > xxx AND name=xxx` → `(create_time, name)` 可能更优 ### 3. 避免索引失效 - 使用函数会导致索引失效： ```sql SELECT * FROM person WHERE LEFT(name, 3) = 'abc'; -- 索引失效 ``` **优化**： ```sql SELECT * FROM person WHERE name LIKE 'abc%'; -- 依然可以走索引 ``` - 数据类型不匹配： ```sql SELECT * FROM person WHERE name = 123; -- name 是 varchar，索引可能失效 ``` **优化**： ```sql SELECT * FROM person WHERE name = '123'; ``` ## 6. 结论 1. MySQL 通过索引区分度、查询匹配度、成本估算来选择索引 2. 使用 OPTIMIZER_TRACE 可以分析优化器的选择过程 3. 索引失效的主要原因：低区分度、范围查询、索引顺序不匹配、类型转换等 4. 人工干预索引选择：使用 `FORCE INDEX` 或 `ANALYZE TABLE` 重新计算统计信息

## MySQL 是如何选择索引的？ MySQL 优化器在多个索引中选择最优索引的目的是为了找到执行代价最低的方案。优化器选择索引的关键因素包括扫描行数、是否使用临时表、是否使用文件排序等。在这里，主要关注扫描行数的影响。 ### **一、如何选择索引** #### **影响优化器的几个因素** MySQL 查询执行的过程包括**连接器、分析器、优化器和执行器**，其中**优化器**负责选择索引。优化器在多个索引中选择最优索引的目的是为了找出执行代价最低的方案，影响优化器选择索引的主要因素包括： - **扫描行数**：扫描行数越少，访问磁盘数据的次数越少，消耗的 CPU 资源也越少。 - **是否使用临时表**：使用临时表会增加额外的开销。 - **是否使用文件排序**：文件排序也会增加额外的开销。 本文主要讨论**扫描行数**对索引选择的影响。 ### **二、扫描行数的来源** 优化器根据索引的**基数（Cardinality）**来估算扫描行数。**基数**是指一个索引上**不同值的个数**，可以使用以下命令查看索引的基数： ```sql SHOW INDEX FROM table_name; ```  ### **三、索引基数的计算方法** MySQL 使用**采样统计**的方法来计算索引基数： 1. **数据页采样**：MySQL 会选出 N 个数据页（每个数据页大小为 16KB）。 2. **统计不同值**：统计选出来的数据页上的不同值的平均数。 3. **估算基数**：用平均值乘以索引的页面数，得到索引的基数。 由于表数据是动态变化的，MySQL 并不会时时更新基数数据，只有当表的数据变更超过 `1/M` 时，才会自动触发重新计算，其中 `M` 的值由 `innodb_stats_persistent` 参数决定： - `innodb_stats_persistent=ON` 时，M = **10**。 - `innodb_stats_persistent=OFF` 时，M = **16**。 这种基于采样的计算方法虽然不是非常精准，但误差不大，能够较好地反映索引的实际情况。 ### **四、基数对索引选择的影响** **基数是优化器选择索引的重要依据之一**。高基数的索引能够显著减少扫描行数，因此更容易被优化器选中。以下是一些典型的场景： - **高基数列**：例如主键或唯一索引列，这些列的基数通常很高，优化器会优先选择这些索引。 - **低基数列**：例如**性别字段（只有男和女两种值）**，基数很低，优化器通常不会选择这样的索引，除非查询条件非常适合使用该索引。 - **数据分布不均衡**：即使某些列的基数不高，但如果查询条件针对数据分布非常集中的部分，优化器也可能选择这些索引。 ### **总结** MySQL 优化器在选择索引时，会综合考虑**扫描行数、临时表使用、文件排序**等因素，其中**扫描行数是一个重要的决定因素**。优化器通过基数估算扫描行数，并选择最优的索引方案。通过合理的索引设计，可以大大提升查询性能。在实际工作中，需要根据具体的业务场景和数据特点，动态调整索引策略，以获得最佳的查询性能。