MySQL索引类型：主键/唯一/覆盖/全文索引优化指南

在数据库系统中，索引是提升查询效率的核心机制。它通过建立特定数据结构（如B+树），避免全表扫描，将查询复杂度从O(n)降为O(log n)。MySQL支持多种索引类型，每种针对不同场景设计。理解其差异能显著优化数据库性能，尤其在高并发或大数据量环境下。以下是详细解析：

---

一、索引基础与数据结构

1.1 索引工作原理

索引本质是数据的映射表，存储字段值与物理位置的对应关系。例如：

CREATE INDEX idx_name ON users(name);  -- 创建name字段的索引

执行SELECT * FROM users WHERE name='Alice'时：

• 无索引：扫描全表100万行（假设）
• 有索引：通过B+树3-5次查找定位数据（树高通常<5）

1.2 B+树结构（MySQL默认）

层数	作用	特点
根节点	存储索引范围	常驻内存
中间节点	路由查找路径	仅存键值
叶子节点	存储实际数据/指针	双向链表连接

优势：

• 范围查询高效（WHERE age > 20）
• 磁盘IO少（节点大小=磁盘页大小16KB）
• 查询稳定性（所有路径等长）

---

二、主键索引（PRIMARY KEY）

2.1 定义与特性

• 唯一标识：每张表仅一个主键，值不可重复且非空
• 聚簇索引：叶子节点直接存储行数据（InnoDB引擎）
• 自动创建：建表时PRIMARY KEY(id)隐式生成索引

2.2 创建与使用

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,  -- 显式主键
    name VARCHAR(50),
    dept VARCHAR(20)
);

查询优化场景：

SELECT * FROM employees WHERE emp_id = 101; -- 直接定位数据页

2.3 设计陷阱

• 错误选择：用UUID做主键导致页分裂（随机插入）
• 最佳实践：自增INT/BIGINT，保持顺序写入

---

三、唯一索引（UNIQUE INDEX）

3.1 与主键索引的区别

特性	主键索引	唯一索引
数量限制	1个/表	多个/表
NULL值	不允许	允许（但仅1个NULL）
聚簇性	是（InnoDB）	否

3.2 适用场景

• 防止重复：ALTER TABLE users ADD UNIQUE (email);
• 替代主键：当主键无业务意义时

3.3 性能影响

INSERT INTO orders (order_code, product) VALUES ('A123', 'Phone'); 
-- 触发唯一约束检查（额外B+树查找）

高频写入场景需评估开销

---

四、覆盖索引（Covering Index）

4.1 核心原理

避免回表：索引包含查询所需全部字段，无需访问数据行

CREATE INDEX idx_cover ON orders(order_date, customer_id, amount);

执行：

SELECT order_date, amount FROM orders 
WHERE customer_id = 1001 AND order_date > '2023-01-01';

• 普通索引：先查索引→再回表取amount
• 覆盖索引：索引直接返回order_date, amount

4.2 性能对比

查询类型	磁盘IO	耗时示例(100万行)
回表查询	2次/行	200ms
覆盖索引	1次/行	50ms

4.3 设计策略

1. 将SELECT字段加入索引：(col1, col2)覆盖SELECT col1, col2
2. 利用最左前缀：(a,b,c)可覆盖WHERE a=1 AND b=2
3. 避免SELECT *：减少不必要的字段

---

五、全文索引（FULLTEXT INDEX）

5.1 解决模糊查询痛点

传统LIKE '%keyword%'问题：

• 无法用B+树优化（最左前缀原则）
• 全表扫描效率极低

5.2 倒排索引机制

CREATE TABLE articles (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT ft_index (content)  -- 全文索引
) ENGINE=InnoDB;

倒排索引结构： | 关键词 | 文档ID列表 | |--------|------------| | MySQL | 1, 3, 7 | | 索引 | 1, 5, 9 |

5.3 查询语法

SELECT * FROM articles 
WHERE MATCH(content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

• +：必须包含
• -：排除
• 自然语言模式：计算相关性得分

5.4 配置优化

[mysqld]
ft_min_word_len = 2   # 最小词长（默认4）
innodb_ft_enable_stopword = 0 # 禁用停用词（如"the","and"）

---

六、索引优化实战技巧

6.1 索引失效的常见原因

1. 函数操作：WHERE YEAR(create_time)=2023 → 改范围查询
2. 隐式类型转换：WHERE phone=13800138000（phone是varchar）
3. OR条件未全覆盖：WHERE a=1 OR b=2（需单独索引a,b）

6.2 联合索引最左前缀原则

索引(col1, col2, col3)生效场景：

• ✅ WHERE col1=1 AND col2=2
• ✅ WHERE col1=1 ORDER BY col3
• ❌ WHERE col2=2 （跳过col1）

6.3 EXPLAIN诊断工具

执行计划关键列：

• type：index（索引扫描） > ref（索引查找） > ALL（全表）
• Extra：Using index（覆盖索引） | Using filesort（需优化）

---

七、索引维护与监控

7.1 重建索引

ALTER TABLE orders REBUILD PARTITION ALL;  -- InnoDB在线重建
ANALYZE TABLE orders;  -- 更新统计信息

7.2 空间占用分析

SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats 
WHERE stat_name = 'size';

7.3 慢查询日志配置

[mysqld]
slow_query_log = 1
long_query_time = 1  # 记录超过1秒的查询
log_queries_not_using_indexes = 1

---

八、索引选择决策树

graph TD
    A[需要唯一标识？] -->|是| B[主键索引]
    A -->|否| C[需要文本搜索？]
    C -->|是| D[全文索引]
    C -->|否| E[查询字段<20%数据？]
    E -->|是| F[普通/B+树索引]
    E -->|否| G[覆盖索引优化]

经验法则：写操作频繁的表应减少索引数量，读密集型表可适当增加。

---

九、真实案例：电商平台优化

问题：订单查询页响应超时（平均800ms）
表结构：

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id INT,
    status TINYINT,
    create_time DATETIME,
    INDEX idx_user (user_id)
);

优化步骤：

1. 分析慢日志：SELECT * FROM orders WHERE user_id=123 AND status=2 ORDER BY create_time DESC LIMIT 10;
2. 添加覆盖索引：ALTER TABLE orders ADD INDEX idx_cover(user_id, status, create_time);
3. 结果：查询耗时降至35ms，TPS提升5倍

---

十、总结：索引设计四原则

1. 精准匹配：主键/唯一索引保障数据完整性
2. 覆盖优先：减少回表提升吞吐量
3. 前缀压缩：联合索引控制字段顺序
4. 持续监控：定期检查索引使用率（sys.schema_unused_indexes）

索引是双刃剑：添加前需评估写入开销，避免“过度索引”导致性能下降。