第一部分：索引基础概念

索引分类

• 主键索引：唯一、非空、聚集索引（InnoDB），关键字 PRIMARY KEY。
• 唯一索引：值唯一，允许 NULL，关键字 UNIQUE INDEX。
• 普通索引：无唯一限制，关键字 INDEX 或 KEY。
• 全文索引：MyISAM 专用，FULLTEXT，针对 CHAR/VARCHAR/TEXT。
• 组合索引：多列索引，遵循最左前缀原则。

聚集 vs 非聚集

特性	聚集索引	非聚集索引
存储方式	数据行与主键同文件	索引与数据分离
InnoDB	主键即聚集索引	二级索引叶子存主键值
MyISAM	不支持	叶子存物理地址指针

第二部分：B+Tree 与引擎索引结构

B-Tree vs B+Tree

• B-Tree：节点存键值+数据，非叶子节点也存数据。
• B+Tree：非叶子节点仅存键值+指针，叶子节点链表相连，范围查询极快，MySQL 统一采用 B+Tree。

MyISAM 索引结构

• .MYI 存索引，.MYD 存数据。
• 主键/辅助索引叶子节点存数据物理地址。
• 主键与辅助索引结构几乎相同。

InnoDB 索引结构

• 索引即数据，数据即索引：.ibd 文件内索引与数据共存。
• 主键索引（聚集索引）叶子节点存完整行数据（含隐藏列：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID）。
• 辅助索引叶子节点存主键值，查询需“回表”两次 B+Tree 查找。
• 主键应短小（推荐自增整型），否则辅助索引膨胀，写入放大。

MyISAM vs InnoDB 索引对比

维度	MyISAM	InnoDB
全文索引	支持	5.6+ 支持
事务	不支持	支持
索引/数据存储	分离	共存
缓存机制	key_buffer_size 仅缓索引，数据依赖 OS	buffer_pool 缓索引+数据
基数统计	精确，持久化	估算，内存中
前缀压缩	支持字符串前缀压缩	不支持

第三部分：SQL 优化实战

最左前缀原则

组合索引 (a,b,c) 仅当 WHERE 条件从最左列连续出现时生效：

• ✅ a、a,b、a,b,c
• ❌ b、b,c、a,c（断层）
• ❌ 范围查询（>、<、LIKE '%...'）后的列失效

索引下推与覆盖索引

• 覆盖索引：SELECT id FROM t WHERE id > 100 仅扫描索引，无回表。
• 索引下推 (ICP)：5.6+ 在存储引擎层过滤 WHERE 条件，减少回表次数。

隔离列原则

索引列不可参与表达式或函数运算：

-- ❌ 失效
WHERE last_login + 1 = 8388606
WHERE DATE(created_at) = '2023-01-01'

-- ✅ 生效
WHERE last_login = 8388605
WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'

排序优化

• WHERE 满足最左前缀，且 ORDER BY 列为索引子集 → 使用索引排序，Extra 无 Using filesort。
• 混合 ASC/DESC 仍可用索引（MySQL 8.0+ 支持降序索引）。

索引选择性

索引选择性 = COUNT(DISTINCT col) / COUNT(*)
-- 唯一索引选择性 = 1（最优）
-- 选择性 < 0.2 优化器常放弃索引，改全表扫描

长字符串列可用前缀索引：INDEX(name(10))，通过测试不同长度逼近 1。

冗余与重复索引

• INDEX(a) 与 INDEX(a,b) 共存 → 前者冗余。
• InnoDB 每次写入需维护所有二级索引，冗余索引增加写放大、碎片、锁竞争。

第四部分：系统配置与维护

关键参数

参数	引擎	建议
key_buffer_size	MyISAM	物理内存 25%-30%，过大反不稳
innodb_buffer_pool_size	InnoDB	物理内存 60%-80%，核心参数
sort_buffer_size	通用	按会话分配，避免全局过大
read_rnd_buffer_size	通用	大查询会话级调大，优化 ORDER BY
join_buffer_size	通用	无索引 JOIN 时生效

维护命令

• OPTIMIZE TABLE：MyISAM 整理碎片；InnoDB 等同 ALTER TABLE ... ENGINE=InnoDB 重建表。
• ANALYZE TABLE：更新统计信息，数据量大幅变化后执行。
• CHECK TABLE：校验表损坏，MyISAM 同步更新统计信息。
• REPAIR TABLE：仅 MyISAM 支持，修复损坏表。

大表 DDL 策略

1. 低峰期直接 ALTER（5.6+ Online DDL 支持大部分操作）。
2. 影子表法：建新表结构 → 导数据 → RENAME TABLE 原子切换（pt-online-schema-change 自动化）。
3. 先导数据后建索引：批量导入时先禁用索引，导完再建，速度提升数倍。

第五部分：查询执行流程图解

（注：原文图片链接已保留，建议替换为本地或 CDN 加速地址）