MySQL索引详解（上）（结构/分类/语法篇）
一、索引概述
索引本质是帮助MySQL高效获取数据的排序数据结构（类似书籍目录），通过减少磁盘I/O次数提升查询效率。其核心价值体现在大数据量场景下的快速定位能力，但同时带来存储和维护成本。
核心特点：
- 优点：
- 减少数据检索量（时间复杂度从O(n)降至O(log n)）
- 加速排序和分组操作（ORDER BY/GROUP BY）
- 保证数据唯一性（唯一索引）
- 缺点：
- 占用额外磁盘空间（索引文件独立存储）
- 降低写操作性能（INSERT/UPDATE/DELETE需维护索引树）
- 不恰当的索引设计可能引发性能劣化
二、索引结构
1. B+Tree（主流结构）
- 层级结构：非叶子节点仅存索引键（Key），叶子节点存储数据指针且形成双向链表 。
- 优势：
- 树高可控（一般3-4层支撑千万级数据）
- 范围查询高效（叶子节点链表直接遍历）
- 适合磁盘存储（节点大小=磁盘页大小，减少I/O）
2. Hash索引
- 基于哈希表实现，O(1)时间复杂度的等值查询 。
- 局限性：
- 不支持范围查询和排序
- 哈希冲突影响性能（链表或红黑树处理）
- 仅Memory引擎原生支持，InnoDB提供自适应哈希（AHI）
3. 全文索引（Full-Text）
- 基于倒排索引实现，针对TEXT类型字段进行关键词搜索 。
- 支持自然语言查询（MATCH...AGAINST语法）
- 仅InnoDB/MyISAM引擎支持
三、索引分类
按数据结构划分
| 类型 | 特点 | 适用场景 |
|---|---|---|
| B+Tree | 支持范围查询、排序，磁盘友好 | 90%以上的索引场景 |
| Hash | 等值查询极快，不支持范围操作 | 内存表、精确匹配场景 |
| Fulltext | 文本关键词搜索 | 文章内容检索 |
按物理存储划分
| 类型 | 特点 | 示例 |
|---|---|---|
| 聚集索引 | 数据行存储在叶子节点（InnoDB主键索引） | PRIMARY KEY |
| 非聚集索引 | 叶子节点存储主键值或数据地址，需二次查找 | 普通单列/组合索引 |
按字段特性划分
| 类型 | 特点 | 语法示例 |
|---|---|---|
| 主键索引 | 唯一且非空，InnoDB的表数据按主键顺序存储 | PRIMARY KEY (id) |
| 唯一索引 | 列值唯一，允许NULL | UNIQUE INDEX (email) |
| 普通索引 | 无唯一性约束，加速查询 | INDEX (name) |
| 全文索引 | 文本内容分词检索 | FULLTEXT (content) |
按字段数量划分
| 类型 | 特点 | 优化规则 |
|---|---|---|
| 单列索引 | 单字段索引 | INDEX (age) |
| 联合索引 | 多字段组合索引，遵循最左前缀原则 | INDEX (name,age) |
特殊类型：
- 覆盖索引：索引包含查询所需全部字段，避免回表
（如SELECT id,name FROM users WHERE name='A'，若索引是(name,id)） - 前缀索引：对长字符串前N字符创建索引，节省空间
（如INDEX (title(10))）
四、索引语法
1. 通用操作
-- 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col1 [ASC|DESC], ...);
-- 修改表添加索引
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT] INDEX index_name (col1, ...);
-- 删除索引
DROP INDEX index_name ON table_name;2. 分类示例
主键索引：
-- 建表时指定
CREATE TABLE users (
id INT AUTO_INCREMENT,
name VARCHAR(50),
PRIMARY KEY (id) -- 聚集索引 );
-- 修改添加
ALTER TABLE orders ADD PRIMARY KEY (
order_id);联合索引：
-- 优化多条件查询
CREATE INDEX idx_name_age ON employees (
last_name, hire_date);全文索引：
-- 支持文本搜索
CREATE FULLTEXT INDEX ft_content ON articles (content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库');前缀索引：
-- 长字段优化
CREATE INDEX idx_city_prefix ON customers (city(10));五、设计原则
- 高频查询字段优先（WHERE/JOIN/ORDER BY）
- 区分度高字段前置（如性别字段不宜单独建索引）
- 避免过度索引（超过5个索引需谨慎评估）
- 联合索引左前缀匹配（
INDEX(a,b,c)适用a=1 AND b>2，不适用b>2） - 长文本使用前缀/全文索引