MySQL 索引核心知识点总结
一、索引基本概念
- 定义：索引是帮助 MySQL 高效获取数据的数据结构（如 B+树、Hash），通过减少磁盘 I/O 次数提升查询效率。
- 类比：类似于书籍的目录，通过快速定位数据行实现高效检索。
- 存储引擎差异：
- InnoDB/MyISAM 默认使用 B+树索引。
- Memory 引擎使用 Hash 索引（仅支持等值查询）。
二、索引类型
- 按功能分类：
- 主键索引（PRIMARY KEY）：唯一且非空，InnoDB 中为聚簇索引（数据与索引存储在一起）。
- 唯一索引（UNIQUE）：列值唯一，允许空值。
- 普通索引（INDEX）：无唯一性限制，仅加速查询。
- 复合索引：包含多列，需遵循最左前缀匹配原则。
- 全文索引（FULLTEXT）：适用于文本类型字段的模糊匹配（如
LIKE '%keyword%'）。
- 前缀索引：对文本字段前 N 个字符建立索引，节省空间。
- 按存储结构分类：
- 聚簇索引：数据行与索引存储在一起（如 InnoDB 主键索引）。
- 非聚簇索引：索引与数据行分离（如 MyISAM 索引）17
三、索引的优缺点
- 优点：
- 降低查询的 I/O 成本，提升检索效率。
- 减少排序和分组操作的 CPU 消耗。
- 缺点：
- 占用额外磁盘空间，索引文件可能比数据文件更大。
- 增删改操作需维护索引，降低写性能。
四、索引底层结构（B+树）
- 特点：
- 多路平衡搜索树，树高度低，适合磁盘存储。
- 叶子节点存储实际数据或主键值（非聚簇索引需回表查询）。
- 支持范围查询和排序操作。
五、索引失效场景
- 违反最左前缀法则：复合索引未从第一列开始使用。
- 例：索引
(a, b, c)，查询条件为 b=1 或 c=1 时失效。
- 范围查询右侧列失效：范围查询后的索引列无法使用。
- 例：索引
(a, b)，条件 a>1 AND b=2 中 b 可能失效。
- 对索引列进行运算或函数操作：如
WHERE YEAR(date_column)=2025。
- 头部模糊匹配：如
LIKE '%abc'。
- OR 连接非索引列：若 OR 条件中包含未索引字段，全表扫描。
- 隐式类型转换：如字符串字段未加引号（
WHERE id='123' vs WHERE id=123）。
六、使用建议
- 适用场景：
- 频繁作为查询条件的字段（WHERE、JOIN）。
- 需要排序或分组的字段（ORDER BY、GROUP BY）。
- 避免滥用：
- 数据量小的表无需索引。
- 频繁更新的字段谨慎建索引。
- 优化策略：
- 优先选择区分度高的字段（如唯一性高的列）。
- 控制复合索引的列数（一般不超过 5 列）。
- 定期分析慢查询，调整索引策略。
七、操作语法示例
sqlCopy Code-- 创建索引
CREATE INDEX idx_name ON table_name(column1, column2);
-- 查看索引
SHOW INDEX FROM table_name;
-- 删除索引
DROP INDEX idx_name ON table_name;