当前位置：首页 > news >正文

MySQL高级特性：索引优化详解

news 2026/5/10 22:56:32

MySQL高级特性：索引优化详解

1. 索引概述

索引是数据库表中一列或多列值的有序数据结构，用于加速数据检索。合理的索引设计可以显著提升查询性能，但也会增加存储空间和写入开销。

2. 索引类型

2.1 B-Tree索引

MySQL默认索引类型，适用于范围查询和等值查询：

-- 创建B-Tree索引 CREATE INDEX idx_name ON users(name);

2.2 哈希索引

适用于等值查询，Memory引擎支持：

-- 创建哈希索引 CREATE INDEX idx_email USING HASH ON users(email);

2.3 全文索引

用于文本搜索：

CREATE FULLTEXT INDEX idx_content ON articles(content);

2.4 空间索引

用于地理位置数据：

CREATE SPATIAL INDEX idx_location ON stores(location);

3. 索引设计原则

3.1 适用场景

WHERE子句中的列
JOIN操作的列
ORDER BY和GROUP BY的列
覆盖索引

3.2 注意事项

避免在频繁更新的列上建索引
选择性高的列更适合建索引
复合索引考虑列顺序
索引不是越多越好

4. 复合索引

4.1 创建复合索引

CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

4.2 最左前缀原则

-- 索引idx_user_status(user_id, status, created_at)生效的情况： SELECT * FROM orders WHERE user_id = 1; -- 使用索引 SELECT * FROM orders WHERE user_id = 1 AND status = 1; -- 使用索引 SELECT * FROM orders WHERE user_id = 1 AND status = 1 AND created_at > '2023-01-01'; -- 使用索引 -- 不使用索引的情况： SELECT * FROM orders WHERE status = 1; -- 不使用索引 SELECT * FROM orders WHERE user_id = 1 AND created_at > '2023-01-01'; -- 只使用user_id部分

5. 索引优化技巧

5.1 覆盖索引

索引包含所有查询字段，避免回表：

-- 创建覆盖索引 CREATE INDEX idx_user_cover ON users(id, name, email); -- 查询直接通过索引返回 SELECT id, name, email FROM users WHERE name = 'John';

5.2 前缀索引

对长字符串列使用前缀索引：

-- 使用前10个字符作为索引 CREATE INDEX idx_email_prefix ON users(email(10));

5.3 索引下推

MySQL 5.6+优化器使用索引下推减少回表：

-- 假设有索引(name, age) SELECT * FROM users WHERE name LIKE 'John%' AND age > 20; -- MySQL会在索引层面过滤age条件，减少回表次数

6. 索引分析

6.1 EXPLAIN分析

EXPLAIN SELECT * FROM users WHERE name = 'John'; -- 结果字段说明： -- type: 连接类型（const, ref, range, all等） -- key: 实际使用的索引 -- rows: 预计扫描的行数 -- Extra: 附加信息（Using index, Using where等）

6.2 索引统计

-- 查看表索引 SHOW INDEX FROM users; -- 更新索引统计 ANALYZE TABLE users; -- 查看表统计信息 SHOW TABLE STATUS LIKE 'users';

7. 索引维护

7.1 查看索引使用情况

SELECT object_schema, object_name, index_name, count_star, sum_timer_wait FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_name = 'users';

7.2 索引重建

-- 重建主键索引 ALTER TABLE users DROP PRIMARY KEY, ADD PRIMARY KEY(id); -- 重建普通索引 ALTER TABLE users DROP INDEX idx_email, ADD INDEX idx_email(email);

8. 常见索引问题

8.1 索引失效

-- 使用函数导致索引失效 SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 使用隐式类型转换 SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar类型 -- 使用OR连接多个条件（部分情况） SELECT * FROM users WHERE name = 'John' OR age > 20;