MySQL的索引详细介绍(全网最新整理)

MySQL 索引全面解析（2024最新整理）

一、索引基础概念

1.1 什么是索引

索引是帮助MySQL高效获取数据的数据结构，类似于书籍的目录。它能加快数据检索速度，但会增加存储空间和维护成本。

1.2 索引的工作原理

B+树结构：MySQL主要使用B+树作为索引结构
二分查找：通过树形结构快速定位数据
减少全表扫描：避免逐行查找，提升查询效率

二、MySQL索引类型详解

2.1 按存储结构分类

B+树索引（最常用）

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 主键索引（自动创建）
ALTER TABLE users ADD PRIMARY KEY (id);

特点：

平衡树结构，查询稳定
叶子节点存储实际数据或指针
支持范围查询和排序

哈希索引

-- Memory引擎默认使用哈希索引
CREATE TABLE hash_table (
    id INT,
    data VARCHAR(100)
) ENGINE=MEMORY;

CREATE INDEX idx_hash USING HASH ON hash_table(id);

特点：

只支持等值查询（=, IN）
不支持范围查询和排序
查询速度O(1)

全文索引（FULLTEXT）

-- 适用于文本搜索
CREATE TABLE articles (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT(content)
);

-- 使用MATCH AGAINST查询
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库优化');

空间索引（R-Tree）

-- 适用于地理空间数据
CREATE SPATIAL INDEX sp_index ON locations(coordinates);

2.2 按物理存储分类

聚簇索引（Clustered Index）

InnoDB的主键索引
数据行存储在叶子节点
每张表只有一个聚簇索引

非聚簇索引（Secondary Index）

叶子节点存储主键值
需要回表查询

2.3 按使用方式分类

覆盖索引

-- 索引包含查询所需的所有字段
CREATE INDEX idx_covering ON users(name, age, city);

-- 查询只需扫描索引，无需回表
SELECT name, age FROM users WHERE name = 'John';

复合索引（联合索引）

-- 多列组合索引
CREATE INDEX idx_multi ON users(last_name, first_name, age);

-- 最左前缀原则示例
-- ✅ 可使用索引：WHERE last_name = 'Smith'
-- ✅ 可使用索引：WHERE last_name = 'Smith' AND first_name = 'John'
-- ❌ 不可用索引：WHERE first_name = 'John'
-- ❌ 不可用索引：WHERE age > 25

三、索引优化策略

3.1 索引设计原则

选择合适的索引列

-- 适合建索引的列：
-- 1. WHERE子句中的列
-- 2. JOIN连接的列
-- 3. ORDER BY/GROUP BY的列
-- 4. SELECT中的覆盖索引列

CREATE INDEX idx_optimize ON orders(
    customer_id,      -- WHERE条件
    order_date,       -- 排序和范围查询
    status           -- 等值查询
);

索引选择策略

场景	推荐索引类型	说明
等值查询	哈希索引/B+树	哈希更快但有限制
范围查询	B+树索引	哈希不支持
模糊查询	B+树索引	LIKE 'prefix%'
全文搜索	全文索引	MATCH AGAINST
排序分组	B+树索引	天然有序

3.2 复合索引优化技巧

索引列顺序原则

-- 原则：高选择性列在前，低选择性列在后
-- 选择性 = 不同值数量 / 总行数

-- 示例：城市（低选择性）在前，年龄（高选择性）在后 ❌
CREATE INDEX idx_bad ON users(city, age);

-- 优化：年龄在前，城市在后 ✅
CREATE INDEX idx_good ON users(age, city);

索引下推（ICP）优化

-- MySQL 5.6+ 自动启用
-- 在索引层面过滤数据，减少回表次数

-- 原始查询
SELECT * FROM users 
WHERE last_name LIKE '张%' 
AND age > 25;

-- 使用复合索引(last_name, age)时
-- ICP会在索引中直接过滤age>25的记录

3.3 索引使用限制和注意事项

索引失效场景

-- 1. 函数操作导致失效 ❌
SELECT * FROM users WHERE UPPER(name) = 'JOHN';

-- 优化 ✅
CREATE INDEX idx_upper ON users(UPPER(name));
-- 或
SELECT * FROM users WHERE name = 'JOHN';

-- 2. 隐式类型转换 ❌
SELECT * FROM users WHERE phone = 13800138000;
-- phone是VARCHAR类型，比较时发生类型转换

-- 3. OR条件部分未使用索引 ❌
SELECT * FROM users 
WHERE name = 'John' 
OR age > 25;  -- age无索引

-- 4. 使用NOT、!=、<> ❌
SELECT * FROM users WHERE status != 'active';

-- 5. LIKE以通配符开头 ❌
SELECT * FROM users WHERE name LIKE '%son';

四、高级索引技术

4.1 自适应哈希索引（Adaptive Hash Index）

InnoDB自动创建
监控频繁访问的索引页
自动在内存中创建哈希索引
参数控制：innodb_adaptive_hash_index

4.2 索引合并（Index Merge）

-- MySQL使用多个索引然后合并结果
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);

-- 可能触发索引合并
SELECT * FROM users 
WHERE name = 'John' 
OR age = 30;

4.3 降序索引（Descending Indexes）

-- MySQL 8.0+ 支持
CREATE INDEX idx_desc ON users(created_at DESC, score ASC);

-- 优化降序排序查询
SELECT * FROM users 
ORDER BY created_at DESC, score ASC;

4.4 函数索引（Functional Indexes）

-- MySQL 8.0+ 支持
CREATE INDEX idx_func ON users((UPPER(name)));

-- 支持JSON路径索引
CREATE INDEX idx_json ON users((CAST(data->>'$.score' AS SIGNED)));

五、性能监控与维护

5.1 索引使用情况分析

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 查看索引统计信息
SHOW INDEX FROM users;

-- 查看索引使用统计（Performance Schema）
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

5.2 索引维护命令

-- 重建索引（InnoDB）
ALTER TABLE users ENGINE=InnoDB;
-- 或
OPTIMIZE TABLE users;

-- 分析索引统计信息
ANALYZE TABLE users;

-- 强制使用/忽略索引
SELECT * FROM users USE INDEX (idx_name) WHERE ...;
SELECT * FROM users IGNORE INDEX (idx_name) WHERE ...;

5.3 监控指标

指标	说明	建议值
索引大小	索引占用的空间	不超过数据大小60%
索引命中率	索引使用效率	>95%
重复索引	冗余索引数量	0
未使用索引	从未使用的索引	考虑删除

六、实战最佳实践

6.1 索引创建决策树

是否需要索引？
├── 是 → 查询频率高吗？
│   ├── 是 → 数据选择性高吗？
│   │   ├── 是 → 创建索引
│   │   └── 否 → 考虑复合索引
│   └── 否 → 不创建
└── 否 → 结束

6.2 索引优化检查清单

设计阶段

[ ] 主键选择短整型（INT/BIGINT）
[ ] 避免UUID等随机值做主键
[ ] 为外键创建索引
[ ] 考虑覆盖索引减少回表

开发阶段

[ ] 避免SELECT *，只查询需要的列
[ ] 使用EXPLAIN分析查询计划
[ ] 注意隐式类型转换
[ ] 合理使用联合索引

维护阶段

[ ] 定期检查未使用索引
[ ] 监控索引大小增长
[ ] 避免频繁索引重建
[ ] 使用pt-duplicate-key-checker检查重复索引

6.3 分区表索引策略

-- 分区表索引类型
-- 1. 本地索引（每个分区独立）
CREATE INDEX idx_local ON orders(order_date)
LOCAL;  -- 每个分区都有该索引

-- 2. 全局索引（跨分区）
CREATE INDEX idx_global ON orders(customer_id)
GLOBAL; -- 整个表一个索引

七、MySQL 8.0+ 新特性

7.1 隐藏索引（Invisible Indexes）

-- 临时禁用索引（测试删除影响）
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;

-- 恢复索引
ALTER TABLE users ALTER INDEX idx_name VISIBLE;

7.2 倒序索引优化

-- MySQL 8.0优化了倒序索引扫描
-- 支持双向扫描，不再需要专门创建DESC索引

7.3 函数索引增强

-- 支持更复杂的表达式
CREATE INDEX idx_expression ON users((SUBSTRING(name, 1, 10)));

八、常见问题与解决方案

Q1: 索引越多越好吗？

答：不是。索引会增加：

写操作开销（INSERT/UPDATE/DELETE需要维护索引）
存储空间占用
优化器选择负担

Q2: 如何选择索引字段顺序？

答：遵循原则：

等值查询字段在前，范围查询字段在后高选择性字段在前经常查询的字段在前

Q3: 什么情况下应该删除索引？

答：考虑删除：

从未被查询使用的索引冗余索引（已有复合索引覆盖）选择性极低的索引（<5%）

九、性能测试工具

-- 1. 使用EXPLAIN ANALYZE（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- 2. 查询优化器跟踪
SET optimizer_trace="enabled=on";
SELECT * FROM users WHERE ...;
SELECT * FROM information_schema.optimizer_trace;

-- 3. 使用sys schema分析
USE sys;
SELECT * FROM schema_index_statistics;