找回密码
立即注册
搜索
热搜: Java Python Linux Go
云栈社区»论坛 › 技术文档「 Note & Doc 」 › MySQL索引优化与SQL调优实战：15个常见误区深度解析 ...
发回帖 发新帖
小红

193

积分

0

好友

19

主题

MySQL索引优化与SQL调优实战：15个常见误区深度解析

发表于 4 天前 | 查看: 12| 回复: 0

一、概述

1.1 背景介绍

在互联网高并发场景下，数据库性能往往是整个系统的关键瓶颈。一条执行超过1秒的慢查询，在业务高峰期可能直接导致服务瘫痪。作为最流行的关系型数据库，MySQL的性能优化是每位后端开发和DBA必须掌握的核心技能。

然而，网络上流传着大量关于MySQL优化的"军规"和"潜规则"，其中既有正确经验，也不乏以讹传讹的误解。本文从实战角度出发，深入解析15个常见的SQL优化误区，揭示其背后原理，帮助开发者真正理解适用场景。

1.2 技术特点

  • 索引原理：基于B+Tree数据结构，理解其特性才能有效运用索引
  • 查询优化器：MySQL查询优化器自动选择执行计划，但并非总是最优
  • 执行计划分析：EXPLAIN是优化基础，需掌握每个字段含义
  • 成本模型：优化器基于成本估算选择执行计划，了解模型才能理解决策逻辑

1.3 适用场景

  • 线上出现慢查询告警，需要快速定位和优化
  • 新功能上线前的SQL审核和性能优化
  • 数据库整体性能调优和架构改进
  • 面试中关于MySQL优化的深度技术考察

1.4 环境要求

组件 版本要求 说明
MySQL 5.7+ / 8.0+ 本文示例基于MySQL 8.0
操作系统 Linux主流发行版 生产环境推荐CentOS 7+ / Ubuntu 20.04+
内存 8GB+ 根据数据量调整buffer pool配置

二、15个SQL优化误区解析

2.1 误区一：SELECT * 是性能杀手

◆ 传统观点

“永远不要使用SELECT *，必须明确指定字段”

◆ 真相剖析
-- 传统认为"错误"的写法
SELECT * FROM users WHERE id = 1;

-- 传统认为"正确"的写法  
SELECT id, name, email FROM users WHERE id = 1;

实际情况：

  • 如果查询走主键索引且需要大部分字段，SELECT * 性能损失极小
  • 核心问题在于：存在覆盖索引时，SELECT * 会导致回表操作
-- 假设有索引 idx_name_email (name, email)
-- 此查询可走覆盖索引，无需回表
SELECT name, email FROM users WHERE name = 'zhangsan';

-- 此查询必须回表，因*包含索引未覆盖字段
SELECT * FROM users WHERE name = 'zhangsan';

验证方法：

EXPLAIN SELECT name, email FROM users WHERE name = 'zhangsan';
-- Extra: Using index (覆盖索引)

EXPLAIN SELECT * FROM users WHERE name = 'zhangsan';  
-- Extra: NULL (需要回表)

结论：SELECT * 的真正问题在于可能无法利用覆盖索引，而非数据传输量。

2.2 误区二：索引越多越好

◆ 传统观点

“为所有可能查询字段创建索引”

◆ 真相剖析
-- 错误示范：过度创建索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_gender ON users(gender);
CREATE INDEX idx_created_at ON users(created_at);

索引的代价：

  1. 写入性能下降：INSERT/UPDATE/DELETE需维护所有索引
  2. 存储空间增加：每个索引都需要额外存储
  3. 优化器选择困难：索引过多可能导致选错索引

正确做法：基于实际查询模式设计索引

-- 分析真实查询需求
-- 查询1: WHERE name = ? AND age > ?
-- 查询2: WHERE city = ? ORDER BY created_at DESC  
-- 查询3: WHERE gender = ? AND city = ?

-- 针对性创建联合索引
CREATE INDEX idx_name_age ON users(name, age);
CREATE INDEX idx_city_created ON users(city, created_at);
CREATE INDEX idx_gender_city ON users(gender, city);

监控无用索引：

-- MySQL 8.0+ 查看索引使用统计
SELECT 
    object_schema,
    object_name, 
    index_name,
    count_star AS use_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY use_count ASC;

2.3 误区三：联合索引必须遵循最左前缀

◆ 传统观点

“联合索引必须从最左字段开始使用，否则失效”

◆ 真相剖析
-- 假设联合索引 idx_a_b_c (a, b, c)
-- 传统认知：
WHERE a = 1                    -- 能用索引 ✓
WHERE a = 1 AND b = 2          -- 能用索引 ✓  
WHERE a = 1 AND b = 2 AND c = 3 -- 能用索引 ✓
WHERE b = 2                    -- 不能用索引 ✗
WHERE b = 2 AND c = 3          -- 不能用索引 ✗
WHERE a = 1 AND c = 3          -- 只能用到a字段

MySQL 8.0的索引跳跃扫描：

-- MySQL 8.0.13+ 支持Index Skip Scan
-- 即使不满足最左前缀，特定场景下仍可使用索引
-- 假设gender只有M/F两个值，索引为idx_gender_age (gender, age)
SELECT * FROM users WHERE age = 25;

-- MySQL 8.0可能自动转换为：
SELECT * FROM users WHERE gender = 'M' AND age = 25
UNION ALL  
SELECT * FROM users WHERE gender = 'F' AND age = 25;

验证Index Skip Scan：

EXPLAIN SELECT * FROM users WHERE age = 25;
-- Extra: Using index for skip scan

结论：最左前缀原则仍然重要，但MySQL 8.0+在特定条件下可以突破此限制。

2.4 误区四：索引列使用函数必然失效

◆ 传统观点

“在索引列上使用函数会导致索引失效”

◆ 真相剖析
-- 传统认为会导致索引失效的写法
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE UPPER(email) = 'TEST@EXAMPLE.COM';

MySQL 8.0的函数索引：

-- MySQL 8.0.13+ 支持函数索引（表达式索引）
CREATE INDEX idx_year ON orders ((YEAR(created_at)));
CREATE INDEX idx_upper_email ON users ((UPPER(email)));

-- 现在这些查询可以使用索引
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE UPPER(email) = 'TEST@EXAMPLE.COM';

传统优化方式仍然有效：

-- 范围查询替代函数调用
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';

2.5 误区五：NOT IN和!=无法使用索引

◆ 传统观点

“使用NOT IN、!=或<>会导致全表扫描”

◆ 真相剖析
-- 测试表结构
CREATE TABLE test_data (
    id INT PRIMARY KEY,
    status TINYINT,
    INDEX idx_status (status)
);

-- 插入测试数据：status有0,1,2,3,4五个值，各20万条，共100万条

实际测试：

-- 排除数据量较小时，仍可能使用索引
EXPLAIN SELECT * FROM test_data WHERE status != 0;
-- 可能走索引，取决于优化器成本估算

EXPLAIN SELECT * FROM test_data WHERE status NOT IN (0, 1);
-- 可能转换为status IN (2, 3, 4)的范围扫描

关键因素是选择性：

-- 如果status=0占90%，status!=0返回10%数据
-- 优化器可能选择使用索引

-- 如果status=0占10%，status!=0返回90%数据  
-- 优化器选择全表扫描，成本更低

结论：NOT IN和!=并非绝对无法使用索引，取决于数据分布和优化器成本估算。

2.6 误区六：JOIN必须小表驱动大表

◆ 传统观点

“JOIN时要用小表驱动大表，FROM后的是驱动表”

◆ 真相剖析
-- 传统认为的"小表驱动大表"
SELECT * FROM small_table s
JOIN big_table b ON s.id = b.small_id;

真相：

  1. MySQL优化器自动选择驱动表，不按SQL书写顺序
  2. 可通过STRAIGHT_JOIN强制指定连接顺序
-- 查看实际连接顺序
EXPLAIN SELECT * FROM small_table s
JOIN big_table b ON s.id = b.small_id;

-- 强制指定连接顺序
SELECT * FROM small_table s
STRAIGHT_JOIN big_table b ON s.id = b.small_id;

影响JOIN性能的关键因素：

-- 1. 被驱动表连接字段是否有索引
-- 好：big_table.small_id有索引
-- 差：big_table.small_id无索引

-- 2. Join Buffer大小配置
SHOW VARIABLES LIKE 'join_buffer_size';

-- 3. 能否使用Block Nested Loop或Hash Join
-- MySQL 8.0.18+ 支持Hash Join
EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;

2.7 误区七：LIMIT 1总能优化查询

◆ 传统观点

“确定只需一条数据时，加LIMIT 1可提升性能”

◆ 真相剖析
-- 场景1：主键/唯一索引查询
SELECT * FROM users WHERE id = 1 LIMIT 1;
-- LIMIT 1无意义，主键查询本就返回一条

-- 场景2：普通索引查询，确实需要一条
SELECT * FROM users WHERE status = 1 LIMIT 1;
-- LIMIT 1有意义，找到第一条就返回

-- 场景3：排序后取第一条
SELECT * FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 1;
-- 有索引(status, created_at)时效率高
-- 无合适索引时仍需排序全部数据

关键点：LIMIT 1的优化效果取决于能否"提前终止"

-- 能提前终止的情况
EXPLAIN SELECT * FROM users WHERE status = 1 LIMIT 1;
-- 找到一条就返回

-- 不能提前终止的情况  
EXPLAIN SELECT * FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 1;
-- 无合适索引时需要先排序所有符合条件数据

2.8 误区八：EXISTS总是优于IN

◆ 传统观点

“大表用EXISTS，小表用IN”

◆ 真相剖析
-- IN写法
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- EXISTS写法
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM users u 
    WHERE u.id = o.user_id AND u.status = 1
);

MySQL 5.6+的优化：

-- MySQL 5.6+ 自动将某些IN子查询转换为semi-join
EXPLAIN SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 1);
-- 查看select_type是否为SIMPLE（说明已转换为JOIN）

实际选择依据：

  1. 子查询结果集小：IN可能更好（可利用索引）
  2. 子查询结果集大：EXISTS可能更好（提前终止）
  3. 现代MySQL：优化器通常能正确选择
-- 最佳实践：改写为JOIN
SELECT DISTINCT o.*
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

2.9 误区九：OR条件导致索引失效

◆ 传统观点

“OR条件会导致索引失效，应改写为UNION”

◆ 真相剖析
-- 情况1：同一字段的OR
SELECT * FROM users WHERE status = 1 OR status = 2;
-- 优化器转换为status IN (1, 2)，可使用索引

-- 情况2：不同字段的OR，且都有索引
SELECT * FROM users WHERE name = 'zhangsan' OR email = 'test@test.com';
-- MySQL可能使用Index Merge优化

Index Merge优化：

EXPLAIN SELECT * FROM users
WHERE name = 'zhangsan' OR email = 'test@test.com';
-- type: index_merge
-- Extra: Using union(idx_name,idx_email); Using where

OR确实导致问题的场景：

-- 当其中一个条件无索引时
SELECT * FROM users WHERE name = 'zhangsan' OR age > 25;
-- 如果age无索引，可能导致全表扫描

2.10 误区十：COUNT(*)比COUNT(1)慢

◆ 传统观点

“COUNT(1)比COUNT()快，因COUNT()读取所有列”

◆ 真相剖析
-- 这三个查询在InnoDB中性能几乎相同
SELECT COUNT(*) FROM users;
SELECT COUNT(1) FROM users;  
SELECT COUNT(id) FROM users;  -- id是主键

原因：

  1. MySQL优化器对COUNT(*)和COUNT(1)处理完全相同
  2. InnoDB引擎选择最小索引统计行数
  3. COUNT(*)不读取任何列值

官方文档说明： "InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference."

真正的性能差异在COUNT(column)：

-- COUNT(column)排除NULL值，需要额外判断
SELECT COUNT(nullable_column) FROM users;  -- 需判断是否为NULL
SELECT COUNT(*) FROM users;                -- 直接计数，不判断

2.11 误区十一：ORDER BY必须匹配WHERE条件才能用索引

◆ 传统观点

“ORDER BY字段必须在WHERE条件中出现才能用索引”

◆ 真相剖析
-- 索引idx_status_created (status, created_at)

-- 情况1：WHERE + ORDER BY，索引有效
SELECT * FROM orders WHERE status = 1 ORDER BY created_at;
-- 走索引，无需额外排序

-- 情况2：只有ORDER BY，索引可能有效
SELECT * FROM orders ORDER BY status, created_at LIMIT 10;
-- LIMIT值小，优化器可能选择索引扫描

-- 情况3：ORDER BY顺序与索引不一致
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC, id ASC;
-- 排序方向不一致，可能导致filesort

MySQL 8.0的降序索引：

-- MySQL 8.0+ 支持降序索引
CREATE INDEX idx_status_created_desc ON orders(status ASC, created_at DESC);

-- 现在此查询可直接使用索引
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC;

2.12 误区十二：LIKE '%xxx%'绝对不走索引

◆ 传统观点

“LIKE以%开头的模糊查询无法使用索引”

◆ 真相剖析
-- 确实无法使用B+Tree索引
SELECT * FROM articles WHERE title LIKE '%MySQL%';

解决方案：

  1. 全文索引 
    
-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title);

-- 使用全文检索 SELECT * FROM articles WHERE MATCH(title) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);


2. **覆盖索引优化**
```sql
-- 虽然无法用于WHERE筛选，但减少回表
-- 索引idx_title_id (title, id)
SELECT id FROM articles WHERE title LIKE '%MySQL%';
-- Extra: Using where; Using index
  1. 搜索引擎方案 
    # 大量全文检索需求，建议使用专门搜索引擎
# - Elasticsearch
# - OpenSearch  
# - Meilisearch

2.13 误区十三：分页查询无需特殊优化

◆ 传统观点

“LIMIT offset, size在offset大时性能下降”

◆ 真相剖析
-- 问题查询：深度分页
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 需要扫描1000010行，只返回10行

优化方案一：基于游标的分页

-- 记住上一页最后一条记录的id
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;
-- 直接定位，效率很高

优化方案二：延迟关联

-- 先查出id，再关联获取详情
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) t ON o.id = t.id;
-- 子查询只扫描索引，减少回表次数

优化方案三：业务限制

-- 限制最大页数，引导用户使用搜索
-- 电商网站通常只显示前100页

2.14 误区十四：只需关注SELECT的执行计划

◆ 传统观点

“只需要关注SELECT语句的执行计划”

◆ 真相剖析
-- MySQL 5.7+ 支持查看UPDATE/DELETE的执行计划
EXPLAIN UPDATE orders SET status = 2 WHERE user_id = 10086;
EXPLAIN DELETE FROM logs WHERE created_at < '2023-01-01';

常见问题：

-- 问题1：UPDATE未用索引
EXPLAIN UPDATE orders SET status = 2 WHERE DATE(created_at) = '2024-01-01';
-- 索引列使用函数，导致全表扫描

-- 问题2：DELETE大量数据
DELETE FROM logs WHERE created_at < '2023-01-01';
-- 数据量大，长时间锁表

大批量DELETE优化：

-- 分批删除，避免长时间锁表
DELIMITER //
CREATE PROCEDURE batch_delete()
BEGIN
    DECLARE rows_affected INT DEFAULT 1;
    WHILE rows_affected > 0 DO
        DELETE FROM logs
        WHERE created_at < '2023-01-01'
        LIMIT 10000;
        SET rows_affected = ROW_COUNT();
        -- 可加入短暂延迟，减少主从延迟
        DO SLEEP(0.1);
    END WHILE;
END//
DELIMITER ;

CALL batch_delete();

2.15 误区十五：只为频繁查询字段建索引

◆ 传统观点

“为频繁查询的字段建索引”

◆ 真相剖析

索引选择性 = 不重复值数量 / 总行数

-- 计算字段选择性
SELECT 
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity, 
    COUNT(DISTINCT order_no) / COUNT(*) AS order_no_selectivity
FROM orders;

-- 示例结果：
-- status_selectivity: 0.00001 (低选择性，只有几个值)
-- user_id_selectivity: 0.1 (中等选择性)  
-- order_no_selectivity: 1.0 (高选择性，每个值都唯一)

低选择性字段建索引的问题：

-- status只有0,1,2,3,4五个值
CREATE INDEX idx_status ON orders(status);

-- 此查询即使有索引，优化器也可能选择全表扫描
SELECT * FROM orders WHERE status = 1;
-- 因status=1记录可能占20%，回表成本太高

联合索引字段顺序：

-- 高选择性字段放前面
CREATE INDEX idx_user_status ON orders(user_id, status);  -- 推荐
CREATE INDEX idx_status_user ON orders(status, user_id);  -- 不推荐
-- 原因：高选择性字段在前，能更快缩小范围

三、执行计划深度解读

3.1 EXPLAIN各字段含义

EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = 1;
字段 含义
id 查询序号，值越大优先级越高
select_type 查询类型：SIMPLE/PRIMARY/SUBQUERY/DERIVED等
table 访问的表
partitions 匹配的分区
type 访问类型：system > const > eq_ref > ref > range > index > ALL
possible_keys 可能使用的索引
key 实际使用的索引
key_len 使用的索引长度（字节）
ref 与索引比较的列或常量
rows 预估扫描行数
filtered 按条件过滤后的行百分比
Extra 额外信息

3.2 type字段详解

-- const: 主键或唯一索引的等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;

-- eq_ref: 连接查询中，被驱动表使用主键或唯一索引  
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ref: 普通索引的等值查询
EXPLAIN SELECT * FROM orders WHERE user_id = 10086;

-- range: 索引范围扫描
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';

-- index: 全索引扫描
EXPLAIN SELECT user_id FROM orders;

-- ALL: 全表扫描（需要优化）
EXPLAIN SELECT * FROM orders WHERE description LIKE '%test%';

3.3 Extra字段常见值

-- Using index: 覆盖索引，无需回表
-- Using where: 存储引擎返回后还需MySQL层过滤  
-- Using temporary: 使用临时表（常见于GROUP BY、DISTINCT）
-- Using filesort: 文件排序（ORDER BY无法使用索引）
-- Using index condition: 索引条件下推（ICP）
-- Using join buffer: 连接缓冲区（被驱动表无法使用索引）

3.4 EXPLAIN ANALYZE（MySQL 8.0.18+）

-- 实际执行并显示真实统计
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 10086;
-- 输出包含：
-- - 实际执行时间
-- - 实际扫描行数  
-- - 循环次数

四、最佳实践与注意事项

4.1 索引设计原则

◆ 设计检查清单
  • 高频查询优先：为80%查询设计索引
  • 考虑写入影响：索引降低写入性能
  • 联合索引设计：
    • 等值条件字段在前
    • 范围条件字段在后
    • 高选择性字段优先
  • 避免冗余索引：(a,b)已包含(a)的功能
-- 冗余索引示例
CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_a_b ON t(a, b);
-- idx_a是冗余的，可删除
◆ 字符串索引优化
-- 长字符串字段使用前缀索引
CREATE INDEX idx_url ON pages(url(50));

-- 计算合适的前缀长度
SELECT
    COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS len_10,
    COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS len_20, 
    COUNT(DISTINCT LEFT(url, 50)) / COUNT(*) AS len_50,
    COUNT(DISTINCT url) / COUNT(*) AS full_len
FROM pages;
-- 选择区分度接近完整字段的最短前缀

4.2 SQL编写规范

◆ 查询优化检查清单
-- 1. 避免SELECT *
SELECT id, name, email FROM users WHERE id = 1;

-- 2. 合理使用LIMIT  
SELECT * FROM logs WHERE status = 'error' LIMIT 100;

-- 3. 避免在循环中执行SQL
-- 错误示范
for user_id in user_ids:
    cursor.execute("SELECT * FROM orders WHERE user_id = %s", user_id)

-- 正确示范
cursor.execute("SELECT * FROM orders WHERE user_id IN (%s)" % ','.join(user_ids));

-- 4. 使用UNION ALL代替UNION（如果不需要去重）
SELECT id FROM t1 WHERE status = 1
UNION ALL
SELECT id FROM t2 WHERE status = 1;
◆ JOIN优化
-- 1. 确保被驱动表的连接字段有索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. 避免过多表JOIN
-- 单个查询JOIN表数不超过3-5个

-- 3. 使用STRAIGHT_JOIN在必要时控制连接顺序
SELECT STRAIGHT_JOIN *
FROM small_table s
JOIN big_table b ON s.id = b.small_id;

4.3 监控与告警

◆ 慢查询日志配置
# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
◆ 慢查询分析
# 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 使用pt-query-digest分析（更详细）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
◆ 实时监控
-- 查看当前执行的查询
SHOW PROCESSLIST;

-- 查看执行时间超过N秒的查询
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 5;

-- 终止慢查询
KILL QUERY <process_id>;

4.4 常见问题与解决方案

问题现象 原因分析 解决方案
执行计划显示ALL 无合适索引或索引失效 检查索引、避免索引失效场景
Using filesort ORDER BY无法使用索引 调整索引或查询条件
Using temporary GROUP BY、DISTINCT需要临时表 优化查询或增加tmp_table_size
有索引却不使用 优化器认为全表扫描成本更低 FORCE INDEX或优化数据分布
相同SQL时快时慢 数据页不在buffer pool 增加buffer pool或优化查询

五、故障排查实战

5.1 案例一：生产环境突发慢查询

◆ 问题描述
告警：订单查询接口P99延迟从50ms飙升到5s
时间：业务高峰期14:00-14:30
◆ 排查步骤
-- 1. 查看当前长时间运行的查询
SELECT
    id, user, host, db, command, time, state, info
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 2
ORDER BY TIME DESC;

-- 2. 查看慢查询日志
-- 发现大量相同慢查询：
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;

-- 3. 检查执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 ORDER BY created_at DESC LIMIT 20;
-- type: ALL, key: NULL, Extra: Using where; Using filesort
-- 全表扫描 + 文件排序！

-- 4. 检查索引
SHOW INDEX FROM orders;
-- 发现idx_user_id索引被误删除
◆ 解决方案
-- 紧急创建索引
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

-- 验证
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 ORDER BY created_at DESC LIMIT 20;
-- type: ref, key: idx_user_created, Extra: Backward index scan

5.2 案例二：索引存在但不使用

◆ 问题描述
-- 表有索引idx_status (status)
-- 但此查询不走索引
SELECT * FROM orders WHERE status = 1;
◆ 排查步骤
-- 1. 检查数据分布
SELECT status, COUNT(*) AS cnt,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders), 2) AS pct
FROM orders
GROUP BY status;

-- 结果：status=1占60%数据
-- 优化器认为全表扫描比走索引后回表更高效

-- 2. 验证优化器决策
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 1;
-- 查看cost信息
◆ 解决方案
-- 方案1：查询更具选择性的条件
SELECT * FROM orders WHERE status = 1 AND created_at > '2024-01-01';

-- 方案2：使用覆盖索引
CREATE INDEX idx_status_covering ON orders(status, id, user_id, created_at);
SELECT id, user_id, created_at FROM orders WHERE status = 1;

-- 方案3：强制使用索引（谨慎）
SELECT * FROM orders FORCE INDEX(idx_status) WHERE status = 1;

5.3 监控指标配置

# Prometheus + MySQL Exporter告警规则示例
groups:
- name: mysql_slow_query
  rules:
  - alert: MySQLSlowQueries
    expr: rate(mysql_global_status_slow_queries[5m]) > 10
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL慢查询数量过多"
      description: "实例 {{ $labels.instance }} 5分钟内慢查询数: {{ $value }}"

- alert: MySQLHighQueryTime  
    expr: mysql_global_status_queries / mysql_global_status_uptime > 1000
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL QPS过高"

六、总结

6.1 核心要点回顾

  • SELECT *的真正问题：无法使用覆盖索引，而非数据传输量
  • 索引不是越多越好：需平衡查询性能和写入性能
  • 最左前缀原则有例外：MySQL 8.0的Index Skip Scan可绕过
  • 函数导致索引失效可解决：MySQL 8.0支持函数索引
  • NOT IN/!=未必不走索引：取决于数据分布和优化器决策
  • COUNT(*)和COUNT(1)性能相同：官方确认
  • OR不一定导致全表扫描：Index Merge可优化
  • 深度分页必须优化：使用游标分页或延迟关联
  • 索引选择性很重要：低选择性字段的索引价值有限

6.2 优化方法论

  1. 先测量后优化：使用EXPLAIN和EXPLAIN ANALYZE确认问题
  2. 关注成本模型：理解优化器决策逻辑
  3. 持续监控：慢查询日志 + 实时监控 + 定期巡检
  4. 版本升级：新版本优化器通常更智能

6.3 进阶学习方向

  1. 深入理解InnoDB存储引擎

    • B+Tree索引结构
    • Buffer Pool工作原理
    • MVCC和事务隔离级别

  2. 查询优化器原理

    • 成本模型
    • 统计信息
    • 优化器Hint

  3. 分布式数据库方案

    • 分库分表
    • 读写分离
    • NewSQL（TiDB、OceanBase）

6.4 参考资料

  • MySQL 8.0官方文档 - Optimization
  • High Performance MySQL, 4th Edition
  • MySQL实战45讲 - 极客时间
  • Percona官方博客

附录

A. 常用命令速查表

-- 查看表索引
SHOW INDEX FROM table_name;

-- 查看表结构
SHOW CREATE TABLE table_name;

-- 查看执行计划
EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN ANALYZE SELECT ...;

-- 查看优化器重写后的SQL
SHOW WARNINGS;

-- 查看索引使用统计
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

-- 强制使用/忽略索引
SELECT * FROM t FORCE INDEX(idx_name) WHERE ...;
SELECT * FROM t IGNORE INDEX(idx_name) WHERE ...;

-- 分析表统计信息
ANALYZE TABLE table_name;

-- 查看InnoDB引擎状态
SHOW ENGINE INNODB STATUS;

B. 索引类型对比

索引类型 存储引擎 特点 适用场景
B+Tree InnoDB 支持范围查询、排序 大多数OLTP场景
Hash Memory 只支持等值查询，速度快 等值查询频繁场景
Full-Text InnoDB 全文检索 文本搜索
Spatial InnoDB 空间索引 GIS数据

C. 术语表

术语 英文 解释
覆盖索引 Covering Index 索引包含查询所需所有字段，无需回表
回表 Table Lookup 通过二级索引找到主键后，再到主键索引查询数据
索引下推 Index Condition Pushdown 将部分WHERE条件下推到存储引擎层过滤
索引合并 Index Merge 使用多个索引并合并结果
文件排序 Filesort 无法使用索引排序时，MySQL使用的排序算法
临时表 Temporary Table GROUP BY、DISTINCT等操作可能创建的中间表
选择性 Selectivity 索引列不重复值数量与总行数的比值
MySQL, 索引优化, SQL调优, 数据库性能, 执行计划

相关帖子
返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

手机版|小黑屋|网站地图|云栈社区(YunPan.Plus) ( 苏ICP备2022046150号-2 )

GMT+8, 2025-12-1 14:51 , Processed in 0.060504 second(s), 39 queries , Gzip On.

Powered by Discuz! X3.5

© 2025-2025 CloudStack.

快速回复 返回顶部 返回列表