MySQL索引详解

前言

MySQL索引就像是一本书的目录，它让数据库能够快速找到所需数据，而不用扫描整个表。合理的索引设计是数据库性能优化的核心技能之一。

1. 索引基础概念

1.1 什么是索引？

索引是一种特殊的数据结构，存储着表中一列或多列的值以及对应的物理地址引用。就像图书馆的目录系统，让我们能够快速找到目标图书的位置。

1.2 索引的工作原理

原始表数据：
┌─────┬─────────┬─────────┐
│  id │   name  │  email  │
├─────┼─────────┼─────────┤
│  1  │  张三   │zhang@1  │
│  2  │  李四   │li@2     │
│  3  │  王五   │wang@3   │
│  4  │  赵六   │zhao@4   │
└─────┴─────────┴─────────┘

索引结构（B+Tree）：
┌─────────┬─────────┐
│  key值  │  指针   │
├─────────┼─────────┤
│    1    │  指向1  │
│    2    │  指向2  │
│    3    │  指向3  │
│    4    │  指向4  │
└─────────┴─────────┘

查找过程：查找key=3 → 找到指针 → 直接访问目标记录

1.3 索引的核心优势

• 加速查询：大幅减少数据扫描量
• 减少I/O操作：通过索引直接定位数据位置
• 提高排序性能：索引天然有序，可加速ORDER BY操作
• 加速表连接：在JOIN操作中，索引能显著提升性能

2. 索引分类

2.1 按数据结构分类

B+Tree 索引（默认类型）

-- 创建B+Tree索引（MySQL默认）
CREATE INDEX idx_name ON users(name);

特点：

• 支持等值查询：WHERE name = '张三'
• 支持范围查询：WHERE id > 10 AND id < 100
• 支持排序：ORDER BY name
• 支持前缀匹配：WHERE name LIKE '张%'
• 所有数据存储在叶子节点，查询效率稳定

哈希索引

-- 创建哈希索引（Memory和InnoDB支持）
CREATE TABLE test_hash (
    id INT,
    name VARCHAR(20),
    INDEX USING HASH (name)
) ENGINE = MEMORY;

特点：

• 查询速度极快：O(1)时间复杂度
• 不支持范围查询：无法进行 >, < 操作
• 不支持排序：哈希无序
• 只支持等值查询：WHERE name = '张三'

全文索引

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

特点：

• 专为文本搜索设计
• 支持自然语言搜索
• 不支持中文（需要特殊配置）

2.2 按逻辑特性分类

逻辑索引类型对比表

索引类型	唯一性	允许NULL	查询性能	适用场景	数量限制
主键	唯一	不允许	最优	主键查询	每表1个
唯一	唯一	允许	很好	防止重复数据	每表多个
普通	可重复	允许	一般	普通查询加速	每表多个
复合	可重复	允许	依赖列数	多条件查询	每表多个

主键索引（Primary Key）

-- 创建表时指定主键索引
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 自动创建主键索引
    name VARCHAR(50),
    email VARCHAR(100)
);

特点：

• 唯一且非空：每个表只能有一个主键
• 自动创建：主键列自动创建唯一索引
• 查询最快：主键索引是最优的索引

唯一索引（Unique Index）

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

特点：

• 值唯一：不允许重复值（除了NULL）
• 加速查询：优化等值查找
• 数据完整性：防止重复数据

普通索引（Normal Index）

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

特点：

• 无唯一性限制：值可以重复
• 加速查询：优化一般查询
• 适用性广：最常用的索引类型

复合索引（Composite Index）

-- 创建复合索引
CREATE INDEX idx_name_email ON users(name, email);

-- 查询优化建议
-- 最优：使用完整索引
SELECT * FROM users WHERE name = '张三' AND email = '[email protected]';

-- 部分使用：只使用name列
SELECT * FROM users WHERE name = '张三';

-- 无法使用：跳过name列
SELECT * FROM users WHERE email = '[email protected]';

最左前缀原则：

• WHERE name = 'xxx' - 使用索引
• WHERE name = 'xxx' AND email = 'xxx' - 使用索引
• WHERE email = 'xxx' - 不使用索引

3. 索引的创建和管理

3.1 创建索引

创建表时创建索引

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    category_id INT,
    created_at TIMESTAMP,
    -- 索引定义
    INDEX idx_name (name),           -- 普通索引
    UNIQUE INDEX idx_category (category_id), -- 唯一索引
    INDEX idx_price_category (price, category_id), -- 复合索引
    INDEX idx_created_at (created_at),
    
    -- 外键约束
    FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;

单独创建索引

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

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

-- 复合索引
CREATE INDEX idx_user_status ON users(name, status);

3.2 修改和删除索引

-- 删除索引
DROP INDEX idx_name ON users;
DROP INDEX idx_email ON users;

-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;

-- 添加主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

-- 删除唯一索引
ALTER TABLE users DROP INDEX idx_email;

-- 修改索引（需先删除再添加）
ALTER TABLE users DROP INDEX idx_name, ADD INDEX idx_name_new (name);

3.3 查看索引信息

-- 查看表的索引信息
SHOW INDEX FROM users;

4. 索引使用最佳实践

4.1 什么时候应该创建索引？

适合创建索引的场景

1. 主键列：自动创建，无需手动操作
2. 外键列：加速JOIN操作
3. WHERE条件频繁使用的列：

   -- 频繁查询用户状态
   WHERE status = 'active'

4. ORDER BY经常使用的列：

   -- 经常按创建时间排序
   ORDER BY created_at DESC

5. GROUP BY经常使用的列：

   -- 按分类统计
   GROUP BY category_id

不适合创建索引的场景

1. 小表：表记录少于几百行
2. 频繁更新的列：UPDATE操作会同步更新索引
3. 重复值多的列：如性别字段，只有'男'、'女'两个值
4. TEXT、BLOB等大字段：占用空间大，维护成本高
5. 几乎不查询的列：不会带来性能提升

4.2 索引设计原则

1. 遵循最左前缀原则

-- 创建复合索引时考虑最左前缀
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- 最佳使用方式
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
SELECT * FROM users WHERE status = 'active';  -- 仍可使用索引
SELECT * FROM users WHERE created_at > '2023-01-01';  -- 不使用索引

2. 选择性高的列优先

-- 好的索引选择
CREATE INDEX idx_email ON users(email);  -- 唯一性高

-- 差的索引选择
CREATE INDEX idx_gender ON users(gender);  -- 只有'男'、'女'两个值，值种类很少

3. 避免重复索引

-- 错误做法：创建重复索引
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_email ON users(email);  -- 重复

-- 正确做法：使用唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

4.3 索引性能优化技巧

前缀索引

-- 当列值很长时，使用前缀索引节省空间
CREATE INDEX idx_name_prefix ON users(name(10));

-- 索引无法命中情况
-- 后缀模糊查询（%在前面，无法利用前缀索引）
EXPLAIN SELECT * FROM sys_user WHERE user_name LIKE '%san123';
-- 中间模糊查询（%在中间，无法匹配前缀）
EXPLAIN SELECT * FROM sys_user WHERE user_name LIKE 'zhang%san';
-- 函数操作字段（索引失效）
EXPLAIN SELECT * FROM sys_user WHERE SUBSTR(user_name, 2) = 'hangsan123';

覆盖索引

-- 覆盖索引：包含查询所需所有列的索引
CREATE INDEX idx_user_info ON users(id, name, email, status);

-- 查询可以直接从索引中获取所有数据，无需回表
SELECT id, name, email, status FROM users WHERE name = '张三';

5. 索引性能分析

5.1 使用EXPLAIN分析查询性能

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三' ORDER BY created_at;

EXPLAIN输出字段：

字段	含义	优化目标
type	连接类型	越靠右越好：const > eq_ref > ref > range > index > all
key	实际使用的索引	不为NULL
rows	扫描行数	越小越好
Extra	额外信息	避免Using filesort

5.2 索引失效的情况

使用函数导致索引失效

原因分析：
在索引列上使用函数会使列值发生变化，MySQL无法预知函数结果，只能对每行数据计算函数值后再比较。

-- 索引失效：函数操作列
SELECT * FROM users WHERE YEAR(created_at) = 2023;
-- 需要对created_at列的每个值计算YEAR()函数

-- 索引生效：范围查询替代
SELECT * FROM users WHERE created_at >= '2023-01-01' 
    AND created_at < '2024-01-01';
-- 直接比较列值，可以利用索引的范围查询能力

类型转换导致索引失效

原因分析：
当比较不同类型时，MySQL会进行隐式类型转换，导致索引列的值被转换，无法使用索引进行快速定位。

-- 假设name列是VARCHAR类型
-- 索引失效：字符串转数字
SELECT * FROM users WHERE name = 123;
-- MySQL会将name列的值转换为数字进行比较，导致全表扫描

-- 索引生效：字符串比较
SELECT * FROM users WHERE name = '123';
-- 类型匹配，可以直接使用索引

LIKE通配符前置导致索引失效

原因分析：
B+Tree索引是按照字典顺序排序的，前置通配符（如%张）意味着匹配任意前缀，无法利用索引的有序性进行范围定位。

-- 索引失效：前置通配符
SELECT * FROM users WHERE name LIKE '%张%';
-- 需要扫描所有记录，检查每个值是否包含"张"

-- 索引生效：后置通配符
SELECT * FROM users WHERE name LIKE '张%';
-- 可以利用索引的有序性，直接定位到以"张"开头的记录范围

OR条件中有非索引列

原因分析：
OR条件需要满足任意一个条件即可，如果其中一个条件无法使用索引，MySQL通常会选择全表扫描来确保结果正确性。

-- 索引失效：OR条件混用
SELECT * FROM users WHERE name = '张三' OR status = 'active';
-- status列无索引，需要全表扫描检查status='active'的记录

-- 分成两次查询或使用UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE status = 'active';
-- 分别利用各自的索引，然后合并结果

运算表达式导致索引失效

原因分析：
与函数类似，对索引列进行运算会改变列值的比较基准，无法直接使用索引的有序性。

-- 索引失效：列参与运算
SELECT * FROM users WHERE age + 10 = 30;
-- 需要对每行数据的age列进行加法运算

-- 索引生效：移项变换
SELECT * FROM users WHERE age = 20;
-- 直接比较列值，可以利用索引

7. 不同存储引擎的索引特点

7.1 InnoDB存储引擎

-- InnoDB支持B+Tree和自适应哈希索引
-- 自适应哈希索引会自动创建热点数据的哈希索引

-- 查看自适应哈希索引状态
SHOW ENGINE INNODB STATUS;

-- 强制使用B+Tree索引（禁止自适应哈希）
SET GLOBAL innodb_adaptive_hash_index = OFF;

InnoDB索引特点：

• 聚簇索引：主键决定数据的物理存储顺序
• 二级索引：非主键索引存储主键值作为指针
• 自动优化：自适应哈希索引提升查询速度
• 事务安全：支持ACID特性

7.2 MyISAM存储引擎

-- MyISAM只支持B+Tree索引
CREATE TABLE users_myisam (
    id INT,
    name VARCHAR(50),
    INDEX idx_name (name)
) ENGINE=MyISAM;

MyISAM索引特点：

• 📍 非聚簇索引：索引和数据分离存储
• 查询速度：某些情况下比InnoDB快
• 不支持事务：没有事务安全特性
• 不支持外键：无法保证参照完整性