一、B+树核心原理：MySQL索引的基石

1.1 B+树数据结构解析

B+树是B树的变种，具有以下关键特征：

• 多叉平衡树结构，所有叶子节点位于同一层
• 非叶子节点仅存储键值（索引字段）和子节点指针
• 叶子节点存储完整数据记录，并通过双向链表连接

-- 示例：查看InnoDB页大小（默认16KB）
SHOW VARIABLES LIKE 'innodb_page_size';

1.2 为什么MySQL选择B+树？

1. 更高的扇出（Fan-out）：单个节点可存储更多键值，降低树高度
2. 顺序访问优势：叶子节点链表适合范围查询
3. 稳定的查询效率：任何查询都需要从根到叶的路径（O(log n)）

1.3 索引查找的IO过程

假设树高度为3：

1. 根节点常驻内存（1次内存访问）
2. 加载二级节点（1次磁盘IO）
3. 加载叶子节点（1次磁盘IO）
4. 获取数据记录（若未使用覆盖索引）

二、22条索引优化军规及实战案例

2.1 基础设计原则（5条）

军规1：为JOIN字段建立索引

-- 反例（全表扫描）
SELECT * FROM orders JOIN users ON orders.user_id = users.id;

-- 正解
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

原理：JOIN操作本质是嵌套循环，索引可减少内表扫描次数

**军规2：避免SELECT ***

案例：某电商查询从2s降到200ms，仅因减少了BLOB字段读取

2.2 高级优化策略（10条）

军规6：利用覆盖索引

-- 反例（需要回表）
SELECT * FROM orders WHERE user_id = 100;

-- 正解
CREATE INDEX idx_cover ON orders(user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 100;

原理：索引已包含查询字段，避免访问主键索引

军规11：索引列不要使用函数

-- 反例（索引失效）
SELECT * FROM logs WHERE DATE(create_time) = '2023-01-01';

-- 正解
SELECT * FROM logs 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

案例：某日志系统查询从8s降到0.1s

2.3 专家级技巧（7条）

军规18：索引下推优化（ICP）

-- 需要开启ICP
SET optimizer_switch = 'index_condition_pushdown=on';

-- 联合索引 (a,b)
SELECT * FROM table WHERE a > 100 AND b = 'xxx';

原理：在存储引擎层过滤数据，减少回表次数

军规22：降序索引优化

-- MySQL 8.0+ 支持
CREATE INDEX idx_desc ON orders(create_time DESC);

-- 分页查询优化
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 10000, 20;

案例：某新闻APP分页查询从5s降到0.2s

三、经典实战场景

3.1 电商商品搜索

-- 最优索引设计
CREATE INDEX idx_search ON products(
    category_id,
    price,
    status,
    stock
) COMMENT '商品搜索复合索引';

-- 典型查询
SELECT id, name, price 
FROM products
WHERE category_id = 5
  AND price BETWEEN 100 AND 500
  AND status = 1
  AND stock > 0
ORDER BY sales_volume DESC
LIMIT 20;

3.2 社交关系图谱

-- 好友关系表设计
CREATE TABLE user_relations (
    user_id BIGINT,
    friend_id BIGINT,
    relation_type TINYINT,
    PRIMARY KEY (user_id, friend_id),
    INDEX idx_reverse (friend_id, user_id)
) ENGINE=InnoDB;

-- 双向查询优化
SELECT friend_id FROM user_relations WHERE user_id = 123;
SELECT user_id FROM user_relations WHERE friend_id = 123;

四、性能验证工具

4.1 EXPLAIN结果解读

EXPLAIN FORMAT=JSON 
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid'\G

关键指标：

• type: ref > range > index > ALL
• extra: Using index > Using filesort

4.2 索引效率监控

-- 查看未使用索引
SELECT * FROM sys.schema_unused_indexes;

-- 索引统计信息
ANALYZE TABLE orders;
SHOW INDEX FROM orders;

五、总结

MySQL索引设计的本质是理解B+树的物理特性：

1. 有序存储决定最左前缀原则
2. 节点大小影响索引列选择
3. 双向链表支持高效范围查询

记住：好的索引不是越多越好，而是每个索引都有明确的查询场景支撑。