MySQL 索引详解：从原理到最佳实践

原创

tcilay

发布于 2025-08-18 10:31:40

1.2K0

MySQL 索引详解：从原理到最佳实践

索引是数据库性能优化的核心要素，如同书籍的目录，能极大加快数据查询速度。然而，不合理的索引设计反而会降低数据库性能。本文将深入解析 MySQL 索引的工作原理、类型划分、创建策略及优化技巧，帮助你构建高效的索引体系。

一、索引的基本原理

1.1 什么是索引？

索引是存储在磁盘上的特殊数据结构，它包含表中一列或多列的值，并指向这些值在表中的物理位置。MySQL 索引主要基于 B + 树实现，这是一种平衡多路查找树，具有以下特点：

所有数据都存储在叶子节点，形成有序链表
非叶子节点仅存储索引关键字，不存储实际数据
层级结构，查询时平均复杂度为 O (log n)

1.2 索引的优缺点

优点：

大幅提高查询速度，尤其是大数据量表
加速 JOIN、WHERE 过滤和 ORDER BY 排序操作
唯一索引可保证数据唯一性

缺点：

占用额外存储空间（通常为数据量的 10%-30%）
降低 INSERT、UPDATE、DELETE 等写入操作性能
需要定期维护，索引失效时会影响性能

1.3 索引选择性

索引选择性是指不重复的索引值与表中记录数的比值，计算公式：

plaintext

选择性 = 不重复的索引值数量 / 表中总记录数

选择性越接近 1，索引效果越好。例如，用户表的email字段比status字段（可能只有 "active"/"inactive" 两个值）选择性更高，更适合创建索引。

二、MySQL 索引类型

2.1 按功能划分

普通索引（INDEX）

最基本的索引类型，没有任何限制

sql

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

唯一索引（UNIQUE）

确保索引列的值唯一，允许 NULL 值（但 NULL 只允许出现一次）

sql

CREATE UNIQUE INDEX idx_user_email ON users(email);

主键索引（PRIMARY KEY）

特殊的唯一索引，不允许 NULL 值，一个表只能有一个主键

sql

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

全文索引（FULLTEXT）

用于全文搜索，适用于 CHAR、VARCHAR、TEXT 类型字段

sql

CREATE FULLTEXT INDEX idx_article_content ON articles(content);

-- 使用全文索引查询
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('database mysql');

2.2 按物理实现划分

聚集索引（Clustered Index）

数据行的物理顺序与索引顺序一致
InnoDB 引擎中，主键索引就是聚集索引
一个表只能有一个聚集索引
查找速度快，因为索引和数据在一起

非聚集索引（Non-clustered Index）

索引顺序与数据物理存储顺序无关
叶子节点存储的是索引值和对应的主键值
需要二次查找（回表）才能获取完整数据
一个表可以有多个非聚集索引

2.3 按索引列数量划分

单列索引

只包含单个列的索引

sql

CREATE INDEX idx_order_date ON orders(order_date);

复合索引（多列索引）

包含多个列的索引，遵循 "最左前缀原则"

sql

CREATE INDEX idx_user_status_age ON users(status, age);

复合索引的生效规则：

能匹配索引的最左前缀（如只使用 status 查询）
能匹配索引的全部列（同时使用 status 和 age）
不能跳过中间列（如只使用 age 查询无法使用该索引）

三、索引创建与管理

3.1 创建索引的方法

CREATE INDEX 语句

sql

CREATE [UNIQUE|FULLTEXT] INDEX index_name 
ON table_name(column1[length], column2[length], ...);

ALTER TABLE 语句

sql

ALTER TABLE table_name 
ADD [UNIQUE|FULLTEXT] INDEX index_name(column_list);

CREATE TABLE 时定义

sql

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    INDEX index_name(column_list)
);

3.2 前缀索引

对于字符串类型的长字段，可以只对字段的前 n 个字符创建索引，节省空间并提高效率

sql

-- 对username字段的前10个字符创建索引
CREATE INDEX idx_username_prefix ON users(username(10));

前缀长度选择原则：

足够长以保证较高的选择性
足够短以节省空间
可通过查询找到合适的长度：

sql

SELECT COUNT(DISTINCT LEFT(username, 10))/COUNT(*) AS selectivity 
FROM users;

3.3 索引删除

sql

-- 删除指定索引
DROP INDEX index_name ON table_name;

-- 或使用ALTER TABLE
ALTER TABLE table_name DROP INDEX index_name;

3.4 索引信息查询

sql

-- 查看表中所有索引
SHOW INDEX FROM table_name;

-- 查看表结构（包含索引信息）
DESCRIBE table_name;

-- 更详细的索引信息
SELECT * FROM information_schema.statistics 
WHERE table_name = 'your_table' AND table_schema = 'your_database';

四、索引使用策略与最佳实践

4.1 适合创建索引的场景

经常出现在 WHERE 子句中的列

sql

-- 频繁按status查询，适合创建索引
SELECT * FROM orders WHERE status = 'completed';

经常用于 JOIN 的列

sql

-- user_id用于关联查询，适合在两个表都创建索引
SELECT * FROM orders 
JOIN users ON orders.user_id = users.id;

经常需要排序（ORDER BY）的列

sql

-- 对order_date创建索引可加速排序
SELECT * FROM orders ORDER BY order_date DESC;

经常需要分组（GROUP BY）的列

4.2 不适合创建索引的场景

数据量小的表：全表扫描可能比索引查询更快
更新频繁的列：索引会降低更新性能
选择性低的列：如性别（只有男 / 女），索引效果差
很少查询的列：索引只会浪费存储空间
TEXT、BLOB 等大字段：除非使用前缀索引

4.3 索引失效的常见情况

使用函数或表达式操作索引列

sql

-- 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 应改为
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

使用不等于（!=、<>）、NOT IN、NOT EXISTS

sql

-- 可能导致索引失效
SELECT * FROM users WHERE status != 'active';

使用 OR 连接包含非索引列的条件

sql

-- 如果age没有索引，整个查询可能不使用索引
SELECT * FROM users WHERE username = 'john' OR age = 30;

使用 LIKE 以通配符开头

sql

-- 索引失效
SELECT * FROM users WHERE username LIKE '%john';

-- 索引有效
SELECT * FROM users WHERE username LIKE 'john%';

隐式类型转换

sql

-- phone是字符串类型，查询时用数字会导致索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- 应改为
SELECT * FROM users WHERE phone = '13800138000';

五、索引优化工具与技巧

5.1 使用 EXPLAIN 分析查询

EXPLAIN 是分析索引使用情况的强大工具，能帮助识别性能问题：

sql

EXPLAIN SELECT * FROM users WHERE status = 'active' AND age > 25;

关键输出字段解析：

type：访问类型，从好到差依次是：const > eq_ref > ref > range > index > ALL
key：实际使用的索引
rows：估计需要扫描的行数
Extra：额外信息，如 "Using index" 表示使用覆盖索引，"Using filesort" 表示需要额外排序

5.2 覆盖索引

覆盖索引是指索引包含查询所需的所有字段，无需回表查询数据：

sql

-- 创建包含所需所有字段的复合索引
CREATE INDEX idx_user_status_age_name ON users(status, age, username);

-- 查询只使用索引中的字段，无需访问表数据
SELECT username, age FROM users WHERE status = 'active' AND age > 25;

5.3 索引维护

定期分析表：更新表的统计信息，帮助优化器做出更好决策

sql

ANALYZE TABLE users;

优化索引碎片：对于频繁更新的表，索引可能产生碎片

sql

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

-- 或优化特定索引
REBUILD INDEX idx_user_email ON users;

监控慢查询：通过慢查询日志发现未使用索引的查询

ini

# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

六、索引设计案例分析

案例 1：电商订单表设计

订单表常见查询场景：

按用户 ID 查询订单
按订单状态和创建时间查询
按订单号查询

合理的索引设计：

sql

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50) NOT NULL,
    user_id INT NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered') NOT NULL,
    create_time DATETIME NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    
    -- 唯一索引确保订单号不重复
    UNIQUE INDEX idx_order_no (order_no),
    
    -- 按用户查询订单
    INDEX idx_user_id (user_id),
    
    -- 复合索引支持按状态和时间查询
    INDEX idx_status_create_time (status, create_time)
);

案例 2：博客文章表设计

sql

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    author_id INT NOT NULL,
    category_id INT NOT NULL,
    publish_time DATETIME NOT NULL,
    views INT DEFAULT 0,
    
    -- 按作者查询文章
    INDEX idx_author_id (author_id),
    
    -- 按分类和发布时间查询
    INDEX idx_category_publish_time (category_id, publish_time),
    
    -- 全文索引支持内容搜索
    FULLTEXT INDEX idx_article_content (title, content)
);

七、总结

索引是 MySQL 性能优化的关键，但并非越多越好。优秀的索引设计需要：

理解业务查询模式，针对频繁查询创建合适索引
平衡查询性能和写入性能，避免过度索引
定期分析和优化索引，移除无用或低效索引
结合 EXPLAIN 工具不断调整和改进索引策略

记住，没有放之四海而皆准的索引方案，最佳实践是根据具体业务场景进行设计，并通过持续监控和优化来保持数据库的高性能。

希望本文能帮助你建立对 MySQL 索引的系统认知，在实际项目中设计出更高效的数据库结构。如果你有任何索引优化的经验或疑问，欢迎在评论区分享讨论！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

索引

MySQL 索引详解：从原理到最佳实践

MySQL 索引详解：从原理到最佳实践

MySQL 索引详解：从原理到最佳实践

一、索引的基本原理

1.1 什么是索引？

1.2 索引的优缺点

1.3 索引选择性

二、MySQL 索引类型

2.1 按功能划分

普通索引（INDEX）

唯一索引（UNIQUE）

主键索引（PRIMARY KEY）

全文索引（FULLTEXT）

2.2 按物理实现划分

聚集索引（Clustered Index）

非聚集索引（Non-clustered Index）

2.3 按索引列数量划分

单列索引

复合索引（多列索引）

三、索引创建与管理

3.1 创建索引的方法

3.2 前缀索引

3.3 索引删除

3.4 索引信息查询

四、索引使用策略与最佳实践

4.1 适合创建索引的场景

4.2 不适合创建索引的场景

4.3 索引失效的常见情况

五、索引优化工具与技巧

5.1 使用 EXPLAIN 分析查询

5.2 覆盖索引

5.3 索引维护

六、索引设计案例分析

案例 1：电商订单表设计

案例 2：博客文章表设计

七、总结

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐