1. MySQL查询优化的重要性与核心思路
作为一名常年与MySQL打交道的数据库工程师，我见过太多因为SQL语句写得烂而导致系统崩溃的案例。上周刚处理过一个电商平台的性能问题：原本每秒能处理2000订单的系统，在促销活动时突然降到每秒不到100单。排查后发现是几个核心查询没有走索引，全表扫描拖垮了整个数据库。
SQL优化不是"高级技巧"，而是每个使用MySQL的人都必须掌握的基本功。好的优化能让查询速度提升几十倍甚至上百倍，差的写法则可能让整个系统瘫痪。根据我的经验，80%的数据库性能问题都出在SQL语句上。
优化核心思路很简单：让MySQL用最少的资源（CPU、内存、I/O）获取需要的数据。具体来说就是：
- 减少数据扫描量（避免全表扫描）
- 减少临时表使用
- 减少排序操作
- 合理利用索引
- 避免不必要的数据传输
2. 基础优化技巧：从写对SQL开始
2.1 只查询需要的列
新手常犯的错误就是SELECT *。我见过一个查询返回了50个字段，但前端实际只用了3个。这不仅浪费网络带宽，还可能导致无法使用覆盖索引。
-- 错误写法 SELECT * FROM orders WHERE user_id = 100; -- 正确写法 SELECT order_id, total_amount, status FROM orders WHERE user_id = 100;提示：即使需要多个字段，也尽量控制在5-8个以内。超过这个数量要考虑是否真的需要一次性获取。
2.2 WHERE条件的正确写法
WHERE条件的顺序和写法直接影响索引使用。基本原则：
- 等值条件(=)放前面
- 范围条件(>,<,BETWEEN)放后面
- 避免在索引列上使用函数
-- 错误写法（无法使用create_time索引） SELECT * FROM logs WHERE DATE(create_time) = '2023-01-01'; -- 正确写法 SELECT * FROM logs WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';2.3 LIMIT的高效用法
分页查询是性能杀手，特别是大偏移量时。优化方案：
-- 低效写法（偏移量10万时需要扫描10万行） SELECT * FROM products ORDER BY id LIMIT 100000, 20; -- 高效写法（记住上一页最后一条记录的ID） SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20;对于必须使用大偏移量的场景，可以改用JOIN：
SELECT p.* FROM products p JOIN (SELECT id FROM products ORDER BY id LIMIT 100000, 20) tmp ON p.id = tmp.id;3. 高级优化技巧：索引与执行计划
3.1 看懂EXPLAIN输出
EXPLAIN是优化SQL的必备工具。关键字段解读：
| 字段 | 说明 | 理想值 |
|---|---|---|
| type | 访问类型 | const, eq_ref, ref, range |
| key | 实际使用的索引 | 显示索引名 |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | 避免Using filesort, Using temporary |
3.2 创建高效索引
组合索引的黄金法则：将选择性高的列放前面，等值查询列放前面。
-- 用户表常用查询 SELECT * FROM users WHERE country = 'CN' AND age > 18 ORDER BY register_time DESC; -- 最优索引 ALTER TABLE users ADD INDEX idx_country_age_register(country, age, register_time);3.3 索引失效的常见陷阱
- 使用!=或<>操作符
- 对索引列使用函数
- 隐式类型转换（如字符串列用数字查询）
- OR条件未全部使用索引
- LIKE以通配符开头
-- 这些写法都会导致索引失效 SELECT * FROM users WHERE age != 20; SELECT * FROM users WHERE YEAR(register_time) = 2023; SELECT * FROM users WHERE mobile = 13800138000; -- mobile是varchar类型4. 复杂查询优化实战
4.1 JOIN优化三原则
- 小表驱动大表（小表放在JOIN前面）
- 确保关联字段有索引
- 合理使用STRAIGHT_JOIN强制连接顺序
-- 优化前（大表驱动小表） SELECT * FROM large_table l JOIN small_table s ON l.id = s.large_id; -- 优化后 SELECT * FROM small_table s JOIN large_table l ON s.large_id = l.id;4.2 子查询优化
MySQL对子查询处理较弱，多数情况可以改写为JOIN：
-- 低效子查询 SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE type = '电子'); -- 高效JOIN写法 SELECT p.* FROM products p JOIN categories c ON p.category_id = c.id WHERE c.type = '电子';4.3 分组统计优化
GROUP BY容易产生临时表，优化方案：
- 使用索引列分组
- 先缩小数据范围再分组
- 考虑使用汇总表
-- 低效写法 SELECT user_id, COUNT(*) FROM orders GROUP BY user_id; -- 高效写法（假设有索引(user_id, create_time)） SELECT user_id, COUNT(*) FROM orders WHERE create_time > '2023-01-01' GROUP BY user_id;5. 特定场景优化技巧
5.1 大批量导入优化
导入10万条数据时，单个INSERT比多值INSERT慢50倍以上：
-- 慢速写法（约需要50秒） INSERT INTO users(name) VALUES('a'); INSERT INTO users(name) VALUES('b'); ... -- 快速写法（约1秒） INSERT INTO users(name) VALUES('a'),('b'),...; -- 500-1000条一批5.2 随机抽样优化
ORDER BY RAND()性能极差，替代方案：
-- 低效写法 SELECT * FROM products ORDER BY RAND() LIMIT 10; -- 高效写法（假设id连续） SELECT * FROM products WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM products) LIMIT 10;5.3 全文搜索优化
LIKE '%关键词%'无法使用索引，解决方案：
- 使用FULLTEXT索引
- 使用专业搜索引擎(Elasticsearch)
- 使用ngram分词
-- 创建全文索引 ALTER TABLE articles ADD FULLTEXT INDEX idx_content(content); -- 使用全文搜索 SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库优化');6. 性能监控与持续优化
6.1 慢查询日志分析
配置my.cnf开启慢查询日志：
slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 # 超过1秒的查询 log_queries_not_using_indexes = 1使用mysqldumpslow工具分析：
mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log6.2 性能监控指标
关键指标监控阈值：
| 指标 | 警告阈值 | 危险阈值 |
|---|---|---|
| QPS | 5000 | 10000 |
| 连接数 | max_connections的80% | 90% |
| 慢查询率 | 1% | 5% |
| 缓存命中率 | 95% | 90% |
6.3 优化案例：电商订单查询
原始查询（执行时间2.8秒）：
SELECT * FROM orders WHERE status = 'paid' AND create_time BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY total_amount DESC LIMIT 100;优化步骤：
- 创建索引(status, create_time, total_amount)
- 只查询必要字段
- 使用覆盖索引技巧
优化后查询（执行时间0.02秒）：
SELECT o.order_id, o.user_id, o.total_amount FROM orders o JOIN ( SELECT order_id FROM orders WHERE status = 'paid' AND create_time BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY total_amount DESC LIMIT 100 ) tmp ON o.order_id = tmp.order_id;7. 常见误区与避坑指南
- 不是所有字段都需要索引 - 索引过多会影响写入性能
- 不要过早优化 - 先确保功能正确再优化
- 避免过度依赖ORM生成的SQL - 复杂查询建议手写
- 分区表不是万能药 - 只有特定场景有效
- 内存表(MEMORY)不适合生产环境 - 数据易丢失
我在实际工作中总结的优化检查清单：
- [ ] 是否使用了SELECT *
- [ ] WHERE条件是否能使用索引
- [ ] JOIN字段是否有索引
- [ ] GROUP BY是否使用了索引列
- [ ] ORDER BY是否会导致文件排序
- [ ] LIMIT偏移量是否过大
- [ ] 是否有不必要的子查询
- [ ] 是否可以考虑使用缓存
记住，SQL优化是一个持续的过程。随着数据量增长和业务变化，今天高效的SQL明天可能就变慢了。建议每隔3-6个月复查一次核心查询的性能。
