目录
一、什么是慢查询？
慢查询是指执行时间超过阈值的SQL语句，会导致数据库性能下降、用户体验差、系统资源被长期占用。
二、如何定位？
一般我们会慢查询日志来记录超过阈值的，而开启慢查询日志也有两种方式。
（一）通过临时开启
-- 开启慢查询日志SET GLOBAL slow_query_log = 'ON';-- 设置慢查询阈值（单位：秒，如1秒）SET GLOBAL long_query_time = 1;-- 指定日志文件路径（可选）SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';-- 记录未使用索引的查询（可选）SET GLOBAL log_queries_not_using_indexes = 'ON';这种方法只适用于当前进程，重启后会失效。
（二）通过永久开启
1.修改my.cnf或my.ini
[mysqld] slow_query_log = ON # 开启慢查询日志 slow_query_log_file = /var/log/mysql/slow.log # 日志路径 long_query_time = 1 # 阈值设为1秒 log_queries_not_using_indexes = ON # 记录未使用索引的SQL2.创建日志文件并授权
sudo touch /var/log/mysql/slow.log sudo chown mysql:mysql /var/log/mysql/slow.log sudo chmod 640 /var/log/mysql/slow.log3.重启服务
sudo systemctl restart mysql(三）测试日志是否正常工作
1.查询慢查询日志工作状态
SHOW VARIABLES LIKE 'slow_query_log'; -- 应为 ONSHOW VARIABLES LIKE 'slow_query_log_file'; -- 确认路径正确SHOW VARIABLES LIKE 'long_query_time'; -- 确认阈值2.触发慢查询
select SLEEP(1.5);3.检查日志内容
vim /var/log/mysql/slow.log # 查看日志尾部内容 # User@Host: user[root] @ localhost [] # Query_time: 1.500487 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1720526400; SELECT SLEEP(1.5);如若出现以上内容，即代表测试成功。
（四）分析日志
使用原生工具mysqldumpslow来快速查询慢sql：
# 按执行时间排序，显示前10条慢SQLmysqldumpslow -s t -t 10 /var/log/mysql/slow.log三、慢查询常见原因
- 未使用索引或索引设置问题
- SQL语句编写问题
- 查询数据量级问题
- 表结构设置问题
- 业务逻辑问题
- 系统参数设置问题
四、优化思路
推荐按照如下顺序依次排查。
（一）索引
关于索引问题，我们需要检查以下几个方面：
1. 原因其一：未设置索引
若慢查询的条件字段未建立索引，将被迫执行全表扫描，这是查询性能低下的常见原因。需检查 WHERE、、ORDER BY 等关键子句中的字段是否已合理索引。
2. 原因其二：索引失效
检查当前慢SQL语句是否违反了最左前缀原则，是否使用了函数运算或者左模糊，亦或是数据类型不一致导致出现了隐式类型转换或者有序谓词条件未完全覆盖。
这方面详细的规避方法请见此博客中的（三）索引失效的场景以及规避方法 带你轻松学习MySQL的索引、锁、事务以及MVCC-CSDN博客https://blog.csdn.net/2401_88959292/article/details/149219146?spm=1001.2014.3001.5501
3. 原因其三： 索引覆盖不全
如果你使用explain执行计划发现该SQL语句明明使用到了索引，但查询速度还是慢，这时候你就要检查是否所有的查询条件字段都有索引，比如说以下SQL语句：
id,username,phone from user where username = ‘Yilena’ and phone =‘10000’;
但你只对username设置了二级索引，虽然id和username可以通过二级索引一次性查询到，但是phone就得回表查询，拖慢了性能，所以此处我们应该使用建立其username和phone的联合索引，让索引覆盖查询字段以及条件字段。
（二）SQL语句
1. 原因其一： 返回结果存在冗余字段
检查返回结果与实际结果是否一致，如果当前接口只需要select id,username，但是实际的SQL语句却是select *，这使得花费了不必要的时间在查询冗余字段上。
2. 原因其二： 子查询
子查询会构建一个临建表供父查询使用，这可能造成额外的时间开销，优先改用** **JOIN替代子查询，减少临时表开销；或者使用where限制临时表数据量级。
3. 原因其三： 多表JOIN过多
当使用多表查询但漏加连接条件时，会造成产生笛卡尔积。
比方说A表有100条数据，B表有200条，那么每查询A表数据一条就要查询整个B表，如此一来查询的总结过就有100*200=20000条，如果再多连接几个表的话那数据量级是不堪设想的，即使有where条件进行过滤，那也还是会先查询出所有结果再过滤，依旧会浪费大量磁盘IO。
然后尽量把JOIN的层级控制在3层以内，否则维护成本大，对性能也存在一定影响。
或者先使用where对结果集过滤再使用JOIN连接也是可以的。
4. 原因其四： 避免排序
若无索引支持，会触发昂贵的文件排序（Using filesort），这个时间开销时非常大的；如果业务允许的情况下最好查询的都是原表中有序的字段，若非要使用order by进行排序时，则确保排序字段覆盖了索引，避免全表扫描。
5. 原因其五： 避免使用NOT IN 和 !=
NOT IN 和 !=通常无法有效使用引擎，导致全表扫描，无论关联字段是否加了索引，建议改写为 OR 或范围查询。
（三）数据量级
| 数据量级 | 推荐方案 | 说明 |
|---|---|---|
| < 1万行 | 直接全量查询 | 数据完全载入内存无压力 |
| 1万 ~ 50万行 | 分页查询优化： • 索引分页 （WHERE id > ? LIMIT n） • 延迟关联（子查询覆盖索引） | 避免 LIMIT offset, N 深分页导致的性能塌陷 |
| > 50万行 | 游标查询 | 流式处理数据，避免内存溢出 |
| > 1000万行 | 分库分表 + 游标查询 | 通过数据分片降低单表压力（如按时间/业务ID拆分） |
什么是延迟关联？ 通过减少回表操作和无效数据扫描来提升性能。 其核心思路是分两步获取数据： 先通过覆盖索引快速定位目标数据的主键，再通过主键关联回原表获取完整数据，避免大规模回表。 传统分页是全表扫描到目标行，然后抛弃目标行以前的全部数据；延迟关联是通过索引快速定位目标行位置，然后通过B+树叶子节点的双向链表快速范围回表查询行数据。
以上数据量级只是理论，实际根据硬件情况与做调整即可。
如果业务逻辑使用了多线程分批并行处理的话，注意可以把每批要处理的数据都放进一个集合里最后一次性写入磁盘里或者抵达一定阈值写入磁盘一次，一定要避免每批都进行SQL操作，这样会使得大量的时间都浪费在网络请求上。
（四）表结构
1. 原因其一：未设置主键
检查一下当前表结构是否设置了主键，因为MySQL会给每个表加一个隐藏主键作为替代。
- 隐藏主键无业务意义，导致二级索引需回表查询。
- 自增主键缺失时，数据写入可能产生页分裂。
什么是页分裂？ 当向一个已满（默认16KB）的数据页插入新数据时，若空间不足，则触发页分裂。 顺序插入：若数据按主键递增插入（如自增ID），新数据通常追加到最后一页；最后一页满时直接创建新页，分裂开销较小。 非顺序插入：若新数据需插入到页的中间位置（例如使用UUID或乱序主键），需分裂原页以腾出空间，代价更高。
自增主键可保证顺序写入，减少随机I/O。
2. 原因其二： 反范式过度
为了避免多表查询的时间开销，有些时候我们会把一些其他表的热点字段也写到另一个业务的表里作为冗余字段，这样查询效率会加快，但如果加入的冗余字段过多会使得表结构变得很长，反而影响了查询性能。
3. 原因其三： 未使用小字段替换大字段
比如说：
- 用INT代替VARCHAR存状态（如status：0=未支付，1=已支付）。
- 用DATETIME代替TIMESTAMP存时间（TIMESTAMP有范围限制：1970-2038）。
- 用VARCHAR代替TEXT存短文本（TEXT会存到溢出页，影响性能）。
4. 原因其四： 未分库分表
当一个表的字段过多的时候，我们可以进行垂直分表，将热带字段保留在一个表里，其他字段放到另一个表里；当一个表的数据行过多时，我们可以进行水平分表，将表赋值好几份，然后可以使用取模分片或者范围分片；
垂直分库的话是按业务进行区分，将同一个业务的表放入一个库里；水平分库则与水平分表原理一致。
5. 原因其五： 大字段位于热点字段表中
像TEXT或者BLOB这样的大字段，需避免出现在热点字段表里，拖累轻量字段查询；应该将其放入与主表分离存储。
（五）业务逻辑问题
1. 原因其一：未使用批量SQL
检查当前业务是否频繁地使用SQL，而不是一次性发起批量请求；频繁的SQL请求会有大量时间开销，请尽量减少次数。
2. 原因其二： 未使用多线程并行处理
检查当前业务是否时单线程串行处理量级庞大的数据，针对数据量大的数据，我们需要使用线程池进行并行处理，在并行处理的过程中也别忘了不要频繁发起SQL请求，而是使用批量请求。
（六）参数设置问题
| 参数名 | 作用描述 | 建议值 | 调优依据 |
|---|---|---|---|
| innodb_buffer_pool_size | InnoDB缓存池大小 | 物理内存的 70%~80% | 缓存索引+数据页，减少磁盘I/O |
| innodb_log_file_size | Redo日志文件大小 | 4GB | 减少日志切换频率，提升写入吞吐 |
| join_buffer_size | JOIN操作缓冲区 | 4MB | 加速无索引JOIN操作 |
| sort_buffer_size | 排序操作缓冲区 | 4MB | 改善 ORDER BY 性能 |
| max_connections | 最大并发连接数 | 300~500 | 防连接风暴 |
| read_rnd_buffer_size | 随机读缓冲区 | 2MB | 优化全表扫描性能 |
当所有优化手段用尽仍存在性能瓶颈，则需要考虑硬件问题了。
码文不易，留个赞再走吧
原文链接: 有关慢查询SQL优化的思路 作者: Yilena
如果这篇文章对你有帮助，欢迎分享给更多人！
部分信息可能已经过时