6.7.3 查询与索引优化策略

围绕查询与索引优化，先以业务高频路径与慢查询为入口，建立“先量化、后优化、再验证”的闭环。优化目标需明确：降低响应时间、减少扫描行数、降低锁等待与资源消耗，并确保对写入与维护成本的影响可控。

查询优化要点
- 规范SQL：避免SELECT *，仅取必要列；条件尽量命中索引列；使用确定性函数，避免对索引列做函数或隐式类型转换。
- 缩小结果集：合理使用WHERE、LIMIT、覆盖索引与精确匹配，避免全表扫描与大范围回表。
- 关联查询：优先小表驱动大表，确保连接列有索引；避免在ON/WHERE中对连接列做计算；必要时拆分为多次查询。
- 排序与分组：ORDER BY、GROUP BY优先走索引；避免Using filesort与大GROUP BY临时表；适当改写为预聚合或分段统计。
- 子查询与IN/EXISTS：根据数据分布选择EXISTS或IN；可改写为JOIN提升性能，但需评估结果集放大风险。
- 分页优化：深分页使用“延迟关联”或“基于索引的条件翻页”，避免大量跳过扫描。
- 事务与锁：尽量缩短事务范围，控制锁粒度，避免长事务叠加造成性能抖动。

索引优化要点
- 索引选择：优先为高选择性、高过滤度的列建立索引；避免对低基数列盲目建索引。
- 复合索引：遵循最左前缀原则；将过滤性最强的列放前，结合排序/分组需求设计列顺序。
- 覆盖索引：高频查询尽量覆盖，减少回表；兼顾写入成本与索引大小。
- 前缀索引与全文索引：长字符串列可用前缀索引平衡性能与空间；文本检索场景考虑全文索引或外部搜索方案。
- 索引数量控制：避免“索引滥用”，定期清理低使用率索引，降低写放大与维护成本。
- 统计信息：关注统计信息准确性，必要时更新统计或分析表，提升优化器选择。

安装与准备环境（用于示例）
以下以 Linux 安装 MySQL 8.x 为例，建立测试库与样例数据，便于演示查询与索引优化：

# 安装 MySQL（RHEL/CentOS）
sudo yum -y install mysql-server
sudo systemctl enable --now mysqld

# 初始化安全设置（按提示设置root密码）
sudo mysql_secure_installation

-- 连接数据库
mysql -uroot -p

-- 创建测试库与表
CREATE DATABASE ops_demo;
USE ops_demo;

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  note VARCHAR(255) DEFAULT NULL,
  KEY idx_user_status_time (user_id, status, created_at)
) ENGINE=InnoDB;

-- 批量插入示例数据（简化示例）
INSERT INTO orders(user_id,status,amount,created_at,note)
VALUES (1,1,99.99,'2024-01-01 10:00:00','new'),
       (2,2,10.00,'2024-01-02 10:00:00','paid'),
       (1,2,20.00,'2024-01-03 10:00:00','paid');

关键命令与示例（带解释与预期）

1) 查看执行计划与索引命中

EXPLAIN SELECT id, amount
FROM orders
WHERE user_id = 1 AND status = 2
ORDER BY created_at DESC
LIMIT 10;

预期效果：type=range或ref，rows较小，Extra包含Using index或无Using filesort。
说明：复合索引idx_user_status_time可同时支持过滤与排序。

2) 覆盖索引优化

-- 原查询回表：需要读取note列
EXPLAIN SELECT id, amount, note
FROM orders
WHERE user_id = 1 AND status = 2;

-- 优化：只取索引包含列，覆盖索引
EXPLAIN SELECT id, amount
FROM orders
WHERE user_id = 1 AND status = 2;

说明：覆盖索引减少回表，降低IO与延迟。

3) 避免索引失效（隐式转换）

-- 错误示例：user_id是BIGINT，使用字符串导致隐式转换
EXPLAIN SELECT id FROM orders WHERE user_id = '1';

-- 正确示例
EXPLAIN SELECT id FROM orders WHERE user_id = 1;

预期效果：正确示例命中索引，rows显著减少。

4) 深分页优化（延迟关联）

-- 低效深分页
SELECT id, amount FROM orders
WHERE status = 2
ORDER BY created_at DESC
LIMIT 100000, 20;

-- 优化：先用索引定位最后一条
SELECT id, amount FROM orders
WHERE status = 2 AND created_at < '2024-01-03 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

说明：基于索引条件翻页避免大量跳过扫描。

5) 统计信息更新

ANALYZE TABLE orders;

预期效果：优化器使用最新统计信息选择更优执行计划。

慢查询与验证闭环（示例）
启用慢查询日志，结合EXPLAIN验证优化效果：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# 查看慢查询日志
sudo tail -n 50 /var/log/mysql/slow.log

排错指南（常见问题与定位）

问题1：EXPLAIN显示Using filesort
排查：检查ORDER BY是否与索引顺序一致；考虑建立复合索引。
命令：
sql SHOW INDEX FROM orders;
问题2：type=ALL全表扫描
排查：条件列是否无索引、隐式转换、函数导致索引失效。
命令：
sql EXPLAIN SELECT * FROM orders WHERE DATE(created_at)='2024-01-01';
优化：
sql EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';
问题3：索引过多导致写入慢
排查：统计索引使用率，删除低频索引。
命令：
sql SELECT * FROM sys.schema_unused_indexes WHERE object_schema='ops_demo';

练习
1) 为orders表设计一个满足条件查询与排序的复合索引，并用EXPLAIN验证是否消除Using filesort。
2) 制造一个索引失效场景（函数、隐式类型转换或OR条件），写出优化前后执行计划对比。
3) 使用慢查询日志抓取耗时SQL，优化后给出执行计划与耗时对比截图或结果。

本节的目标是建立“规范SQL + 合理索引 + 执行计划验证”的优化路径，在不牺牲稳定性的前提下持续提升查询性能。