一、揪出慢查询的元凶——问题定位三板斧
当我们的外卖订单查询突然变慢时，就像在暴雨天等骑手送餐——既焦虑又无奈。让我们先打开MySQL的慢查询日志：
-- 开启慢查询日志（MySQL 5.7+）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 超过2秒的查询记为慢查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
查看最近半小时的慢查询：
SELECT * FROM mysql.slow_log
WHERE start_time > NOW() - INTERVAL 30 MINUTE
ORDER BY start_time DESC
LIMIT 10;
真实生产案例：某电商平台的商品搜索功能，在促销期间出现5秒以上的响应延迟。通过慢日志定位到以下罪魁祸首：
SELECT * FROM products
WHERE category_id = 123
AND price BETWEEN 100 AND 500
ORDER BY sales_volume DESC
LIMIT 100 OFFSET 0;
二、索引设计的艺术——从青铜到王者的进阶之路
2.1 索引选择的黄金法则
给上述慢查询创建复合索引：
ALTER TABLE products
ADD INDEX idx_search (category_id, price, sales_volume);
但注意索引陷阱：当WHERE条件使用OR连接不同字段时，复合索引可能失效：
-- 错误示例：索引无法同时覆盖两个字段
SELECT * FROM users
WHERE mobile = '13800138000' OR email = 'service@example.com';
2.2 覆盖索引的魔法
让查询完全在索引中完成：
-- 原始查询（需要回表）
SELECT product_name, price FROM products WHERE category_id = 123;
-- 优化后（使用覆盖索引）
ALTER TABLE products
ADD INDEX idx_covering (category_id, product_name, price);
三、执行计划深度解读——数据库的"X光片"
给慢查询拍个CT：
EXPLAIN SELECT o.order_no, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.create_time > '2023-01-01';
重点关注：
- type列：ALL（全表扫描）→ 需优化
- Extra列：Using filesort（文件排序）→ 需添加排序索引
- rows列：预估扫描行数超过1万 → 需优化条件过滤
四、关联查询优化实战——JOIN操作的三十六计
4.1 小表驱动大表原则
错误做法：
SELECT * FROM 10w_rows_table
JOIN 1k_rows_table ON ... -- 大表在前导致多次全表扫描
正确姿势：
SELECT * FROM 1k_rows_table
JOIN 10w_rows_table ON ... -- 小表作为驱动表
4.2 分页查询的终极方案
传统分页的致命缺陷：
SELECT * FROM orders
ORDER BY id
LIMIT 10 OFFSET 100000; -- 需要扫描前100010行
优化方案：
SELECT * FROM orders
WHERE id > 上一页最后一条ID
ORDER BY id
LIMIT 10;
五、高阶优化技巧——那些教科书不会告诉你的黑科技
5.1 冷热数据分离
将历史订单归档：
-- 创建归档表
CREATE TABLE orders_archive LIKE orders;
-- 迁移数据
INSERT INTO orders_archive
SELECT * FROM orders
WHERE create_time < '2022-01-01';
-- 删除原表数据
DELETE FROM orders
WHERE create_time < '2022-01-01';
5.2 巧用内存临时表
处理复杂计算：
CREATE TEMPORARY TABLE temp_stats ENGINE=MEMORY
AS (
SELECT user_id, SUM(amount) total
FROM orders
GROUP BY user_id
);
SELECT u.username, t.total
FROM temp_stats t
JOIN users u ON t.user_id = u.id;
六、关联技术生态——站在巨人的肩膀上
6.1 查询缓存的双刃剑
适合场景：读多写少的配置表
-- 查看缓存状态
SHOW STATUS LIKE 'Qcache%';
-- 典型配置（my.cnf）
query_cache_type = 1
query_cache_size = 64M
6.2 连接池的正确姿势
Java应用配置示例（HikariCP）：
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20); // 建议公式：(core_count * 2) + effective_spindle_count
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
七、应用场景与选型指南
7.1 OLTP vs OLAP优化差异
- 交易系统：优先考虑索引覆盖和行锁效率
- 分析系统：侧重全表扫描优化和列式存储
7.2 云数据库的特殊考量
阿里云RDS优化建议：
- 使用性能洞察功能定位锁等待
- 开启SQL审计日志分析全量请求
- 合理设置自动扩容策略
八、避坑指南与经验总结
8.1 经典错误案例
- 在WHERE子句中对字段进行函数操作
- 滥用SELECT * 导致网络IO暴增
- 事务未及时提交引发锁等待
- UTF8MB4字段的索引长度陷阱
- 错误估计NULL值对索引的影响
8.2 性能优化checklist
□ 是否使用执行计划分析 □ 索引区分度是否足够 □ 是否避免隐式类型转换 □ 连接池配置是否合理 □ 慢查询日志是否定期分析