跳到主要内容

MySQL 索引

索引是数据库优化的重要手段，本章将详细介绍 MySQL 索引的类型、创建、使用和优化。

什么是索引？​

索引是数据库中用于加速数据检索的数据结构，类似于书籍的目录。通过索引，数据库可以快速定位到需要的数据，而不必扫描整个表。

索引的作用​

  • 加速查询：大幅提高 SELECT 查询速度
  • 加速排序：减少 ORDER BY 操作的排序时间
  • 加速分组：优化 GROUP BY 操作
  • 强制唯一性：UNIQUE 索引保证数据唯一

索引的代价​

  • 存储空间：索引需要占用磁盘空间
  • 写入性能：INSERT、UPDATE、DELETE 需要维护索引
  • 维护成本：需要定期优化和重建

索引类型​

按数据结构分类​

B+Tree 索引​

MySQL 默认的索引类型，适用于大多数场景：

-- 创建 B+Tree 索引
CREATE INDEX idx_username ON users(username);

-- 或在创建表时定义
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    INDEX idx_username (username)
);

B+Tree 索引特点：

  • 所有数据存储在叶子节点
  • 叶子节点使用双向链表连接
  • 支持范围查询、排序、分组

适用场景：

  • 全值匹配：WHERE name = '张三'
  • 范围查询：WHERE age > 20
  • 前缀匹配：WHERE name LIKE '张%'
  • 排序：ORDER BY name

Hash 索引​

基于哈希表实现，只支持 Memory 引擎：

-- Memory 表默认使用 Hash 索引
CREATE TABLE sessions (
    session_id VARCHAR(32) PRIMARY KEY,
    data TEXT,
    INDEX USING HASH (session_id)
) ENGINE = Memory;

Hash 索引特点：

  • 只支持等值查询
  • 不支持范围查询、排序
  • 冲突时性能下降

全文索引（FULLTEXT）​

用于全文搜索，支持中文分词：

-- 创建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (content) WITH PARSER ngram
);

-- 全文搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

空间索引（SPATIAL）​

用于地理空间数据：

CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position POINT,
    SPATIAL INDEX idx_position (position)
);

-- 空间查询
SELECT * FROM locations 
WHERE ST_Distance_Sphere(position, POINT(116.4, 39.9)) < 10000;

按功能分类​

普通索引​

基本的索引类型，无特殊约束：

CREATE INDEX idx_email ON users(email);

唯一索引​

索引列的值必须唯一，允许 NULL：

CREATE UNIQUE INDEX uk_email ON users(email);

主键索引​

特殊的唯一索引，不允许 NULL：

CREATE TABLE users (
    id INT PRIMARY KEY,  -- 自动创建主键索引
    name VARCHAR(50)
);

复合索引​

多列组合的索引：

CREATE INDEX idx_name_age ON users(last_name, first_name, age);

创建索引​

CREATE INDEX 语法​

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (列名[(长度)] [ASC|DESC], ...);

创建索引示例​

-- 创建普通索引
CREATE INDEX idx_status ON users(status);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_phone ON users(phone);

-- 创建复合索引
CREATE INDEX idx_name_status ON users(last_name, status);

-- 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

-- 创建降序索引（MySQL 8.0+）
CREATE INDEX idx_created_desc ON orders(created_at DESC);

在建表时创建索引​

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id INT NOT NULL,
    status VARCHAR(20),
    created_at DATETIME,
    
    -- 唯一索引
    UNIQUE INDEX uk_order_no (order_no),
    
    -- 普通索引
    INDEX idx_user_id (user_id),
    
    -- 复合索引
    INDEX idx_status_created (status, created_at),
    
    -- 外键
    FOREIGN KEY (user_id) REFERENCES users(id)
);

使用 ALTER TABLE 创建索引​

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_age (age);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

-- 添加复合索引
ALTER TABLE users ADD INDEX idx_name_age (last_name, first_name, age);

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

删除索引​

-- 方式一：DROP INDEX
DROP INDEX idx_status ON users;

-- 方式二：ALTER TABLE
ALTER TABLE users DROP INDEX idx_status;

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

查看索引​

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

-- 查看索引详细信息
SHOW INDEX FROM users WHERE Key_name = 'idx_status';

-- 从 information_schema 查看
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';

输出字段说明：

字段说明
Key_name索引名称
Column_name索引列名
Seq_in_index列在索引中的位置
Non_unique是否允许重复（0=唯一，1=不唯一）
Cardinality索引中唯一值的估计数量
Index_type索引类型（BTREE、HASH 等）

索引使用原则​

最左前缀原则​

复合索引按照定义顺序使用，从左边开始匹配：

-- 假设有索引：idx_name_age (last_name, first_name, age)

-- ✅ 使用索引
WHERE last_name = '张'
WHERE last_name = '张' AND first_name = '三'
WHERE last_name = '张' AND first_name = '三' AND age = 25

-- ❌ 不使用索引
WHERE first_name = '三'          -- 跳过了 last_name
WHERE age = 25                   -- 跳过了前两列

-- ✅ 部分使用索引（只使用 last_name）
WHERE last_name = '张' AND age = 25

避免索引失效​

-- ❌ 索引列使用函数
WHERE YEAR(created_at) = 2024

-- ✅ 范围查询
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- ❌ 索引列使用计算
WHERE age + 1 = 26

-- ✅ 等价改写
WHERE age = 25

-- ❌ 使用 NOT 操作
WHERE status NOT IN ('active')

-- ❌ 使用 OR（可能失效）
WHERE status = 'active' OR age > 30

-- ✅ 使用 UNION
SELECT * FROM users WHERE status = 'active'
UNION
SELECT * FROM users WHERE age > 30

-- ❌ LIKE 以通配符开头
WHERE name LIKE '%三'

-- ✅ 前缀匹配
WHERE name LIKE '张%'

-- ❌ 隐式类型转换
WHERE phone = 13800138000  -- phone 是 VARCHAR 类型

-- ✅ 使用字符串
WHERE phone = '13800138000'

覆盖索引​

查询的列都在索引中，不需要回表查询：

-- 假设有索引：idx_name_email (name, email)

-- ✅ 使用覆盖索引
SELECT name, email FROM users WHERE name = '张三';

-- ❌ 需要回表（查询了 id）
SELECT id, name, email FROM users WHERE name = '张三';

使用 EXPLAIN 查看 Extra 字段：

  • Using index：使用覆盖索引
  • Using index condition：使用索引条件下推
  • Using filesort：文件排序，需要优化
  • Using temporary：使用临时表，需要优化

EXPLAIN 分析查询​

EXPLAIN SELECT * FROM users WHERE status = 'active';

重要字段说明：

字段说明
id查询标识符，相同表示同时执行
select_type查询类型（SIMPLE、PRIMARY、SUBQUERY 等）
table访问的表
type访问类型（性能从好到差）
possible_keys可能使用的索引
key实际使用的索引
key_len使用的索引长度
ref索引比较的列或常量
rows预估扫描的行数
Extra额外信息

type 类型（性能从好到差）：

类型说明
system单行表（系统表）
const主键或唯一索引常量查询
eq_ref主键或唯一索引关联查询
ref非唯一索引查询
range索引范围扫描
index索引全扫描
ALL全表扫描（需要优化）

索引优化策略​

选择合适的列建立索引​

-- ✅ 高选择性列（唯一值多）
CREATE INDEX idx_email ON users(email);

-- ✅ 常用于 WHERE 条件的列
CREATE INDEX idx_status ON orders(status);

-- ✅ 常用于 JOIN 的列
CREATE INDEX idx_user_id ON orders(user_id);

-- ✅ 常用于 ORDER BY 的列
CREATE INDEX idx_created_at ON orders(created_at);

-- ❌ 低选择性列（唯一值少）
-- status 只有几个值，单独建索引效果差
CREATE INDEX idx_status ON users(status);

-- ✅ 改为复合索引
CREATE INDEX idx_status_created ON users(status, created_at);

复合索引设计​

-- 复合索引顺序原则：
-- 1. 等值查询的列放前面
-- 2. 范围查询的列放后面
-- 3. 排序的列可考虑加入

-- 常见查询：WHERE status = ? AND created_at > ? ORDER BY created_at
CREATE INDEX idx_status_created ON orders(status, created_at);

前缀索引​

对长字符串列，使用前缀索引节省空间：

-- 选择合适的前缀长度
SELECT 
    COUNT(DISTINCT email) AS total,
    COUNT(DISTINCT LEFT(email, 5)) AS prefix_5,
    COUNT(DISTINCT LEFT(email, 10)) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 15)) AS prefix_15
FROM users;

-- 选择选择性接近完整列的前缀长度
CREATE INDEX idx_email_prefix ON users(email(10));

前缀索引的限制：

  • 不能用于 ORDER BY 和 GROUP BY
  • 不能作为覆盖索引

索引维护​

-- 分析表，更新索引统计信息
ANALYZE TABLE users;

-- 优化表，重建表和索引
OPTIMIZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 重建索引
ALTER TABLE users ENGINE = InnoDB;

索引监控​

-- 查看索引使用情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mydb'
ORDER BY COUNT_READ DESC;

-- 查看未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_READ = 0
  AND OBJECT_SCHEMA = 'mydb';

索引与 NULL 值​

-- MySQL 可以对包含 NULL 值的列建立索引
CREATE INDEX idx_age ON users(age);

-- 查询 NULL 值可以使用索引
SELECT * FROM users WHERE age IS NULL;

-- 但以下情况索引可能失效
SELECT * FROM users WHERE age IS NOT NULL;  -- 可能全表扫描

隐藏索引（MySQL 8.0+）​

隐藏索引不会被优化器使用，可用于测试索引影响：

-- 创建隐藏索引
CREATE INDEX idx_test ON users(test_column) INVISIBLE;

-- 查看索引是否隐藏
SHOW INDEX FROM users;

-- 切换索引可见性
ALTER TABLE users ALTER INDEX idx_test INVISIBLE;
ALTER TABLE users ALTER INDEX idx_test VISIBLE;

函数索引（MySQL 8.0+）​

可以基于函数或表达式创建索引：

-- 基于函数创建索引
CREATE INDEX idx_year ON orders((YEAR(created_at)));

-- 可以优化这类查询
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 基于表达式创建索引
CREATE INDEX idx_full_name ON users((CONCAT(first_name, ' ', last_name)));

最佳实践总结​

  1. 索引创建原则

    • 选择高选择性列
    • 遵循最左前缀原则设计复合索引
    • 避免在小表上创建过多索引

  2. 避免索引失效

    • 不在索引列上使用函数或计算
    • 避免隐式类型转换
    • LIKE 查询不以 % 开头

  3. 监控和优化

    • 定期分析索引使用情况
    • 删除未使用的索引
    • 更新索引统计信息

  4. 测试和验证

    • 使用 EXPLAIN 验证索引使用
    • 测试索引对查询性能的影响

小结​

本章我们学习了：

  1. 索引类型：B+Tree、Hash、全文、空间索引
  2. 索引分类：普通、唯一、主键、复合索引
  3. 索引创建：CREATE INDEX、ALTER TABLE
  4. 索引使用：最左前缀原则、避免索引失效
  5. 索引优化：覆盖索引、前缀索引、索引维护

练习​

  1. 为用户表创建合适的索引
  2. 使用 EXPLAIN 分析查询是否使用了索引
  3. 创建复合索引并测试最左前缀原则
  4. 分析并优化一个慢查询

参考资源​