索引失效底层原理是什么

发布时间：2021-10-22 15:52:55 作者：iii
来源：亿速云阅读：160

# 索引失效底层原理是什么

## 引言

在数据库性能优化中，索引是提升查询效率的关键工具。然而，当索引失效时，查询性能会急剧下降，甚至引发全表扫描。理解索引失效的底层原理，对于数据库调优和SQL编写至关重要。本文将深入探讨索引失效的常见场景及其背后的工作机制。

---

## 一、索引的基本工作原理

### 1.1 索引的数据结构
大多数关系型数据库（如MySQL）使用B+树作为索引的默认数据结构：
- **有序性**：数据按键值排序存储
- **多路平衡**：支持高效的范围查询和等值查询
- **叶子节点链表**：加速范围扫描

### 1.2 索引的查询流程
1. 从根节点开始二分查找
2. 沿非叶子层向下定位
3. 在叶子层找到目标键值或范围
4. 通过指针访问实际数据（聚簇索引直接包含数据）

---

## 二、索引失效的常见场景及原理

### 2.1 不符合最左前缀原则
**场景**：复合索引`(a,b,c)`条件下仅使用`b`或`c`查询  
**底层原理**：
- B+树按照`(a,b,c)`的顺序构建
- 缺失最左列时无法利用索引的有序性
- 优化器判定全表扫描比"跳跃扫描"更高效

**示例**：
```sql
-- 索引生效
SELECT * FROM table WHERE a = 1 AND b > 2 

-- 索引失效
SELECT * FROM table WHERE b = 2

2.2 使用函数或表达式

场景：WHERE YEAR(create_time) = 2023
底层原理： - 索引存储的是原始值而非函数计算结果 - 需要对每行数据计算后才能比较 - 破坏B+树的有序性特征

特殊案例：

-- 索引可能生效（依赖优化器）
SELECT * FROM table WHERE create_time = DATE_ADD(NOW(), INTERVAL 1 DAY)

2.3 隐式类型转换

场景：字符串字段使用数字条件WHERE varchar_col = 123
底层原理： 1. 数据库执行类型转换 2. 转换后的值不与索引原值匹配 3. 转换过程需要逐行计算

类型转换优先级：

INT > DECIMAL > FLOAT > BIGINT > VARCHAR

2.4 使用`!=`或`NOT IN`

底层原理： - B+树适合定位特定值或范围 - 否定条件需要检查所有索引项 - 成本估算常高于全表扫描

例外情况：

-- 可能使用索引（覆盖索引情况）
SELECT id FROM table WHERE id NOT IN (1,2,3)

2.5 `LIKE`以通配符开头

场景：WHERE name LIKE '%张'
底层原理： - B+树按前缀组织数据 - 后缀匹配无法利用有序性 - 必须扫描全部叶子节点

优化方案：

-- 使用反转函数建立特殊索引
CREATE INDEX idx_reverse_name ON table(REVERSE(name));
SELECT * FROM table WHERE REVERSE(name) LIKE REVERSE('%张');

三、深度解析：优化器如何决策索引失效

3.1 成本估算模型

数据库通过以下因素计算执行成本： 1. IO成本：读取页面的数量 2. CPU成本：处理行数的计算量 3. 内存成本：临时缓冲区的使用

3.2 统计信息的影响

基数（Cardinality）：索引列不同值的数量
直方图（Histogram）：数据分布统计（MySQL 8.0+）
采样比例：影响统计准确性

3.3 优化器决策流程

解析所有可能的访问路径
估算每种路径的成本
选择成本最低的执行计划
当全表扫描成本 < 索引查询成本时放弃索引

四、索引失效的特殊情况

4.1 索引合并（Index Merge）

现象：多个单列索引的组合使用
限制条件： - 仅适用于OR条件的特定情况 - 需要额外的排序合并操作 - 合并成本常高于全表扫描

4.2 覆盖索引的例外

即使WHERE条件不符合索引规则，但当查询仅需索引列时：

-- 使用索引（覆盖索引）
SELECT a,b FROM table WHERE b = 2 ORDER BY a

4.3 ICP优化（Index Condition Pushdown）

MySQL 5.6+特性： - 将WHERE条件”下推”到存储引擎层 - 减少回表次数 - 部分解决索引失效问题

五、诊断索引失效的方法

5.1 执行计划分析

EXPLN FORMAT=JSON SELECT * FROM table...

关键指标： - possible_keys vs key - rows 估算值 - Extra 中的Using filesort/Using temporary

5.2 性能监控工具

MySQL Slow Query Log
Performance Schema
SHOW PROFILE

5.3 索引使用统计

SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'db_name';

六、优化建议

遵循最左前缀原则设计索引
避免在索引列上使用函数
使用EXPLN验证执行计划
定期更新统计信息ANALYZE TABLE
考虑使用覆盖索引优化查询

结语

索引失效的本质是查询条件无法利用B+树的有序性特征，导致优化器选择效率更低的执行计划。深入理解存储引擎的工作原理，结合数据库的优化器特性，才能编写出高效的SQL语句。随着数据库版本的演进，新的优化技术（如MySQL 8.0的不可见索引、降序索引等）不断出现，需要持续跟进学习。 “`

注：本文以MySQL为例进行说明，其他数据库原理类似但实现细节可能不同。实际长度约1500字，可根据需要补充具体案例或扩展特定数据库的实现细节。