mysql索引底层B+树

B+树胜出的关键特性：

矮胖树结构：3-4层高度即可存储2000万条记录（假设每页存1000条）
叶子链表：所有数据存储在叶子节点，并通过双向链表连接
非叶导航：非叶子节点仅存储键值，不保存数据，提升节点容量
2. 实战案例：索引如何加速查询？
2.1 案例1：超市储物柜系统
2.1.1 场景描述
表结构：2000个储物柜，字段包括柜号（主键）、手机号、使用时间等。
高频查询：用户通过手机号查找柜号（SELECT * FROM lockers WHERE phone='13812345678'）。
2.1.2 无索引的代价
全表扫描2000条数据，平均需访问1000次磁盘页（假设每页20条记录）。
2.1.3 创建索引后的优化
CREATE INDEX idx_phone ON lockers(phone);  
查询过程：
从根节点定位手机号138所在的页。
中间层定位到1381234的分支。
叶子层找到13812345678对应的柜号。
根据柜号直接访问目标数据页。
磁盘I/O次数：3次（树高3层） vs 全表扫描的100次。
2.2 案例2：医院叫号系统的联合索引
2.2.1 联合索引设计
ALTER TABLE patients ADD INDEX idx_dept_status_time(department, status, register_time);  
最左前缀原则：
有效查询：WHERE department='心血管科' AND status='待就诊'（使用前两列）。
无效查询：WHERE status='待就诊'（跳过第一列，触发全表扫描）。
2.2.2 索引覆盖优化
SELECT id, department FROM patients WHERE department='心血管科';  
若索引包含所有查询字段，直接返回索引数据，无需回表。
3. 索引设计与避坑指南
3.1 设计原则
三星索引标准：

一星：WHERE条件匹配索引列。
二星：ORDER BY/GROUP BY使用索引排序。
三星：SELECT字段全部在索引中。
选择性原则：

优先为区分度高的列建索引（如性别字段区分度低，手机号区分度高）。
计算公式：选择性 = 不同值数量 / 总行数。
3.2 常见陷阱
1. 隐式类型转换
SELECT * FROM users WHERE phone = 13812345678; -- phone为varchar类型  
导致索引失效，需强制转换：WHERE phone = '13812345678'。
2. 函数操作破坏索引
SELECT * FROM orders WHERE DATE_FORMAT(create_time,'%Y-%m')='2025-03';  
改写为范围查询：WHERE create_time >= '2025-03-01' AND create_time < '2025-04-01'。
4. 进阶：索引的底层维护
4.1 页分裂与合并
插入触发页分裂：当叶子页已满时，分裂为两个页，父节点新增指针。
删除触发页合并：当页利用率低于阈值时，合并相邻页并更新指针。
4.2 索引统计信息
MySQL定期更新 INDEX_STATISTICS，优化器根据数据分布选择索引。
手动更新命令：ANALYZE TABLE patients;