前言
最近项目有个接口特别慢，一查日志发现是SQL查询太慢
一条简单的查询居然要几秒钟，肯定是索引没建好
所以系统性地学习了一下MySQL索引优化，记录一下实战经验
慢查询定位
开启慢查询日志
1
2
3
4
5
6
7
8
9
10
11
|
SHOW VARIABLES LIKE 'slow_query_log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SHOW VARIABLES LIKE 'slow_query_log_file';
|
分析慢查询日志
可以用mysqldumpslow工具分析：
1
2
3
4
5
|
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log
|
使用EXPLAIN分析
1
| EXPLAIN SELECT * FROM user WHERE username = 'admin';
|
关键字段：
- type：访问类型，ALL是全表扫描，index是索引扫描，const、eq_ref、ref是比较好的类型
- possible_keys：可能用到的索引
- key：实际用到的索引
- rows：扫描的行数，越小越好
- Extra：额外信息，Using filesort、Using temporary都需要优化
索引基础
索引类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
PRIMARY KEY (id)
UNIQUE KEY uk_username (username)
KEY idx_email (email)
KEY idx_name_age (name, age)
FULLTEXT KEY ft_content (content)
|
创建和删除索引
1
2
3
4
5
6
7
8
9
10
11
|
CREATE INDEX idx_username ON user(username);
CREATE INDEX idx_name_age ON user(name, age);
DROP INDEX idx_username ON user;
SHOW INDEX FROM user;
|
索引优化规则
1. 最左前缀原则
组合索引查询时要符合最左前缀：
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE INDEX idx_a_b_c ON table(a, b, c);
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3
|
2. 避免索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
WHERE YEAR(create_time) = 2024
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
WHERE SUBSTRING(username, 1, 3) = 'adm'
WHERE username LIKE 'adm%'
WHERE username LIKE '%admin%'
WHERE username LIKE 'admin%'
WHERE phone = 13800138000
WHERE phone = '13800138000'
WHERE username = 'admin' OR age = 18
WHERE username = 'admin'
UNION
WHERE age = 18
|
3. 合理使用覆盖索引
如果查询的字段都在索引中，可以直接从索引获取数据，不用回表：
1
2
3
4
5
|
CREATE INDEX idx_username_age_email ON user(username, age, email);
SELECT username, age, email FROM user WHERE username = 'admin';
|
4. 控制索引数量
不是索引越多越好，每个索引都会占用存储空间，而且会影响INSERT、UPDATE、DELETE的性能
建议：
实战案例
案例1：分页查询优化
1
2
3
4
5
6
7
8
9
10
11
|
SELECT * FROM user ORDER BY id LIMIT 1000000, 10;
SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 10;
SELECT * FROM user
INNER JOIN (
SELECT id FROM user ORDER BY id LIMIT 1000000, 10
) AS tmp ON user.id = tmp.id;
|
案例2：ORDER BY优化
1
2
3
4
5
6
7
8
9
10
11
|
CREATE INDEX idx_age ON user(age);
SELECT * FROM user ORDER BY age;
SELECT id, age FROM user ORDER BY age;
CREATE INDEX idx_age_id ON user(age, id);
|
案例3：COUNT优化
1
2
3
4
5
6
7
8
9
10
11
|
SELECT COUNT(*) FROM user;
CREATE TABLE table_counter (
table_name VARCHAR(50) PRIMARY KEY,
row_count BIGINT DEFAULT 0
);
EXPLAIN SELECT * FROM user LIMIT 1;
|
案例4：JOIN优化
1
2
3
4
5
6
|
SELECT * FROM order o
INNER JOIN user u ON o.user_id = u.id;
CREATE INDEX idx_user_id ON order(user_id);
|
索引监控
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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_star = 0
AND object_schema = 'your_database'
ORDER BY object_schema, object_name;
SELECT
table_name,
index_name,
cardinality,
seq_in_index
FROM information_schema.statistics
WHERE table_schema = 'your_database'
ORDER BY table_name, index_name, seq_in_index;
|
索引设计原则
- 经常用于WHERE、JOIN、ORDER BY的字段适合建索引
- 区分度高的字段适合建索引（如手机号、用户名）
- 频繁更新的字段不适合建索引
- 区分度低的字段不适合建索引（如性别，只有两个值）
- 文本字段用前缀索引
- 不在区分度低的字段上建索引
1
2
|
CREATE INDEX idx_content ON article(content(100));
|
总结
索引优化是个持续的过程，需要根据实际查询情况调整
关键点：
- 开启慢查询日志，找到慢SQL
- 用EXPLAIN分析执行计划
- 根据查询场景创建合适的索引
- 遵守索引优化规则，避免索引失效
- 定期检查索引使用情况，删除无用索引
- 平衡查询性能和写入性能
暂时就先记录这么多