您好,登录后才能下订单哦!
# 索引失效底层原理是什么
## 引言
在数据库性能优化中,索引是提升查询效率的关键工具。然而,当索引失效时,查询性能会急剧下降,甚至引发全表扫描。理解索引失效的底层原理,对于数据库调优和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
场景:WHERE YEAR(create_time) = 2023
底层原理:
- 索引存储的是原始值而非函数计算结果
- 需要对每行数据计算后才能比较
- 破坏B+树的有序性特征
特殊案例:
-- 索引可能生效(依赖优化器)
SELECT * FROM table WHERE create_time = DATE_ADD(NOW(), INTERVAL 1 DAY)
场景:字符串字段使用数字条件WHERE varchar_col = 123
底层原理:
1. 数据库执行类型转换
2. 转换后的值不与索引原值匹配
3. 转换过程需要逐行计算
类型转换优先级:
INT > DECIMAL > FLOAT > BIGINT > VARCHAR
!=
或NOT IN
底层原理: - B+树适合定位特定值或范围 - 否定条件需要检查所有索引项 - 成本估算常高于全表扫描
例外情况:
-- 可能使用索引(覆盖索引情况)
SELECT id FROM table WHERE id NOT IN (1,2,3)
LIKE
以通配符开头场景:WHERE name LIKE '%张'
底层原理:
- B+树按前缀组织数据
- 后缀匹配无法利用有序性
- 必须扫描全部叶子节点
优化方案:
-- 使用反转函数建立特殊索引
CREATE INDEX idx_reverse_name ON table(REVERSE(name));
SELECT * FROM table WHERE REVERSE(name) LIKE REVERSE('%张');
数据库通过以下因素计算执行成本: 1. IO成本:读取页面的数量 2. CPU成本:处理行数的计算量 3. 内存成本:临时缓冲区的使用
现象:多个单列索引的组合使用
限制条件:
- 仅适用于OR条件的特定情况
- 需要额外的排序合并操作
- 合并成本常高于全表扫描
即使WHERE条件不符合索引规则,但当查询仅需索引列时:
-- 使用索引(覆盖索引)
SELECT a,b FROM table WHERE b = 2 ORDER BY a
MySQL 5.6+特性: - 将WHERE条件”下推”到存储引擎层 - 减少回表次数 - 部分解决索引失效问题
EXPLN FORMAT=JSON SELECT * FROM table...
关键指标:
- possible_keys
vs key
- rows
估算值
- Extra
中的Using filesort
/Using temporary
SHOW PROFILE
SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'db_name';
EXPLN
验证执行计划ANALYZE TABLE
索引失效的本质是查询条件无法利用B+树的有序性特征,导致优化器选择效率更低的执行计划。深入理解存储引擎的工作原理,结合数据库的优化器特性,才能编写出高效的SQL语句。随着数据库版本的演进,新的优化技术(如MySQL 8.0的不可见索引、降序索引等)不断出现,需要持续跟进学习。 “`
注:本文以MySQL为例进行说明,其他数据库原理类似但实现细节可能不同。实际长度约1500字,可根据需要补充具体案例或扩展特定数据库的实现细节。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。