1. 为什么数据库需要索引想象一下你在图书馆找一本书。如果没有图书分类系统你需要从第一排书架开始一本本翻找直到找到目标书籍。这种全表扫描的方式在数据库查询中同样低效而索引就是数据库世界的图书目录。MySQL的InnoDB引擎采用B树作为索引数据结构这绝非偶然。B树在1972年由Rudolf Bayer和Edward M. McCreight提出经过半个世纪的验证仍然是关系型数据库索引的最佳选择。与哈希索引相比B树支持范围查询与二叉树相比它的高度更低磁盘I/O次数更少与B树相比它的非叶子节点不存储数据能容纳更多键值。提示虽然B树索引查询时间复杂度是O(log n)但在超大数据量下单次查询仍可能需要3-4次磁盘I/O。这就是为什么需要控制索引层级深度。2. B树的内部构造解析2.1 B树的物理存储结构InnoDB中每个索引都是一棵独立的B树。以主键索引为例其叶子节点存储完整记录非叶子节点仅存储键值和指向子节点的指针。每个节点大小默认16KB可通过innodb_page_size调整这与磁盘块大小对齐确保每次I/O能完整读取一个节点。节点内部采用有序数组存储键值通过二分查找定位数据。当节点超过填充因子默认为15/16时会分裂这是B树保持平衡的关键机制。分裂过程如下原节点保留前半部分键值新节点存储后半部分键值中间键值提升到父节点2.2 聚簇索引与二级索引的区别聚簇索引主键索引的叶子节点直接包含行数据因此主键查询只需一次索引查找。如果没有定义主键InnoDB会优先使用非空的唯一索引都没有则自动创建6字节的隐藏row_id作为主键二级索引辅助索引的叶子节点存储的是主键值而非完整数据这导致回表现象通过二级索引查询时需要先查到主键再回主键索引查完整数据。例如-- name是二级索引 SELECT * FROM users WHERE name 张三;实际执行了两棵B树的查找操作。3. 索引的实战应用场景3.1 索引覆盖优化避免回表的关键是让查询只需访问索引树。例如-- 需要回表 SELECT * FROM orders WHERE user_id 100; -- 索引覆盖user_id和status建立联合索引 SELECT user_id, status FROM orders WHERE user_id 100;实测案例某用户表2000万数据查询SELECT id FROM users WHERE phone13800138000无索引2.3秒仅有phone单列索引0.8秒需回表创建(phone,id)联合索引0.02秒索引覆盖3.2 最左前缀原则联合索引(a,b,c)实际相当于建立了(a)(a,b)(a,b,c)三种索引。以下查询能利用索引WHERE a1 AND b2 WHERE a1 ORDER BY b WHERE a5但以下情况无法充分利用索引WHERE b2 -- 缺少最左列 WHERE a1 AND c3 -- 跳过了b列注意范围查询、、BETWEEN会停止索引匹配。例如WHERE a1 AND b2只能用到a列的索引部分。4. 索引失效的典型陷阱4.1 隐式类型转换当索引列与查询条件类型不匹配时-- phone是varchar类型 SELECT * FROM users WHERE phone 13800138000; -- 失效 SELECT * FROM users WHERE phone 13800138000; -- 有效MySQL会进行隐式转换相当于对索引列使用了函数导致无法走索引。常见于字符串与数字比较字符集不匹配utf8与utf8mb4日期格式不一致4.2 索引选择性不足索引选择性 不重复值数量 / 总行数。选择性低的索引如性别、状态字段优化器可能直接忽略。经验值高于30%适合建索引低于10%通常无效可以通过计算验证SELECT COUNT(DISTINCT status)/COUNT(*) AS selectivity FROM orders;4.3 索引碎片化问题随着数据增删改B树会出现空洞表现为索引体积远大于数据体积查询性能逐渐下降维护方法-- 重建表锁表 ALTER TABLE orders ENGINEInnoDB; -- 在线重建5.6 ALTER TABLE orders ALGORITHMINPLACE, LOCKNONE;5. 高级索引优化策略5.1 索引下推ICPMySQL 5.6引入的优化将WHERE条件推到存储引擎层过滤。对比无ICP存储引擎检索数据→Server层过滤有ICP存储引擎直接过滤通过执行计划查看EXPLAIN SELECT * FROM users WHERE name LIKE 张% AND age 20; -- 出现Using index condition表示启用ICP5.2 MRR多范围读取传统方式按索引顺序访问磁盘可能产生大量随机I/O。MRR优化先收集索引键值按主键排序批量顺序读取数据启用设置SET optimizer_switchmrron,mrr_cost_basedoff;5.3 索引跳跃扫描MySQL 8.0新特性即使不满足最左前缀也能利用联合索引。例如索引(a,b)-- 8.0可能利用索引 SELECT * FROM tbl WHERE b1;原理是先扫描a列的不同值再在这些值下查找b1的记录。但要求a列的不同值较少如性别、地区等。6. 生产环境索引管理实践6.1 索引设计checklist为所有外键创建索引为ORDER BY/GROUP BY字段建索引避免在更新频繁的列建过多索引联合索引列顺序高选择性→低选择性控制单表索引数量通常不超过5-6个6.2 索引监控方法通过performance_schema查看索引使用情况-- 查看未使用的索引 SELECT * FROM sys.schema_unused_indexes; -- 索引统计信息 SHOW INDEX FROM orders;6.3 在线索引变更方案MySQL 8.0支持instant ADD COLUMN但添加索引仍可能锁表。推荐方案主从架构先在从库添加切换主从pt-online-schema-change工具gh-ost工具无触发器方案我在实际运维中遇到过添加索引导致生产事故的案例一个200GB的表添加索引耗时2小时期间所有写操作被阻塞。后来改用gh-ost工具业务完全无感知。
