MySQL优化必知的7个实战技巧（附高频面试题解析）

laowangpython

于 2025-05-17 13:09:11 发布

阅读量709

点赞数 15

CC 4.0 BY-SA版权

文章标签： mysql 数据库其他

本文链接：https://blog.youkuaiyun.com/laowangpython/article/details/148027128

一、索引优化的正确姿势

1.1 最左前缀原则的深度应用

举个真实场景：用户表有复合索引（city, age, salary）

-- 有效查询 ✅
SELECT * FROM users WHERE city='北京' AND age>25
SELECT * FROM users WHERE city='上海' AND salary>10000

-- 失效查询 ❌
SELECT * FROM users WHERE age>30 AND salary<20000

1.2 索引选择的玄机

（必考题）当出现多个可用索引时，MySQL如何选择？教大家一个实战判断方法：

查看索引基数：SHOW INDEX FROM table_name
检查WHERE条件过滤性
预估扫描行数（可以用EXPLAIN验证）

最近在排查慢查询时发现，一个本该走时间索引的查询却走了主键索引。最后发现是统计信息过期导致的，用ANALYZE TABLE刷新后恢复正常！

二、SQL语句优化的经典案例

2.1 LIMIT分页优化

常见错误写法：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10

优化方案：

SELECT * FROM orders 
WHERE create_time < (SELECT create_time FROM orders ORDER BY create_time DESC LIMIT 1000000,1)
ORDER BY create_time DESC 
LIMIT 10

执行时间从2.3秒 → 0.02秒！

2.2 JOIN连接的三大陷阱

驱动表选择：小表驱动大表
关联字段类型不一致：隐式转换导致索引失效
子查询滥用：能用JOIN就不用子查询

三、EXPLAIN执行计划详解

3.1 关键指标解读表

字段	重点关注点	优化方向
type	ALL/index/range等	避免全表扫描
rows	估算扫描行数	检查索引有效性
Extra	Using filesort/Using temporary	优化排序和临时表使用

3.2 实战分析示例

EXPLAIN SELECT * FROM products 
WHERE category='电子' AND price>1000 
ORDER BY sales DESC LIMIT 10;

输出关键信息：

type: range
key: idx_category_price
Extra: Using where; Using filesort

优化建议：建立（category, price, sales）复合索引

四、慢查询日志分析四步法

4.1 配置秘籍

# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

4.2 分析工具对比

工具	优点	适用场景
mysqldumpslow	内置工具简单易用	快速定位TOP SQL
pt-query-digest	分析维度全面	深度性能分析
阿里云DAS	可视化界面	云环境使用方便

五、高频面试题精讲

5.1 经典问题集锦

为什么索引能提高查询速度？
- 类比图书馆目录检索（B+树结构）
- 数据页存储 vs 索引页存储
如何避免索引失效？
- 函数操作陷阱：WHERE DATE(create_time)='2023-01-01'
- 隐式类型转换：字符串字段用数字查询
- 最左前缀缺失：复合索引使用不当
深分页问题如何解决？
- 滚动查询（适合连续翻页）
- 游标分页（适合随机跳页）
- 业务限制最大页数（简单粗暴有效）

5.2 场景分析题

题目：订单表每天新增50万数据，现有查询SELECT * FROM orders WHERE status=1 AND create_time>='2023-01-01'越来越慢，怎么优化？

参考答案：

建立(status, create_time)复合索引
历史数据归档（把status=2的订单移到历史表）
分区表按月份分区
引入Elasticsearch做查询分离

六、参数调优黄金法则

6.1 内存配置公式

可用内存 = 
innodb_buffer_pool_size + 
key_buffer_size + 
max_connections*(sort_buffer_size+read_buffer_size+join_buffer_size+...) 
+ 系统预留2GB

6.2 关键参数推荐值

参数	建议值	说明
innodb_buffer_pool_size	物理内存的70%-80%	最重要的缓存设置
max_connections	500-1000	根据实际连接数调整
thread_cache_size	max_connections的10%	减少线程创建开销