sql查询慢怎么优化（SQL执行太慢如何优化）

sql查询慢怎么优化（SQL执行太慢如何优化）低效sql提高了数据库的响应时间，如返回过多数据列增大传输负载、where语句中进行计算、类型转换增加计算时间同时导致索引失效、大表中count(*)或不当sql分页查询靠后的数据(如直接limit 50w 100)、对非索引字段排序等。低效SQL语句对于大数据量的查询，没有建合适的索引或者SQL语句使用不当导致索引失效会引起慢SQL。锁等待 以常用的InnoDB存储引擎来说，其支持行锁和表锁，比如一个事务执行update或者select xx from xx for update时，会持有锁(行锁、间隙锁或者大量的行锁引起锁升级为表锁等)，造成其他事务等待锁资源。

MySQL由于开源免费、服务稳定、性能卓越、社区活跃，使其在互联网公司中成为了主流数据库之一。

应用服务与数据库的交互主要是通过SQL语句。随着业务的发展、数据量级的提升，会出现一些慢SQL，进而影响到接口耗时、服务吞吐、用户体验。作为一名业务rd，掌握常见的数据库优化方法是必不可少的，这也是面试的重点，本文做下梳理总结。

慢SQL是如何产生的

从数据库的角度来看，一般有几个方面的原因会导致慢SQL：

无索引或索引失效

对于大数据量的查询，没有建合适的索引或者SQL语句使用不当导致索引失效会引起慢SQL。

锁等待

以常用的InnoDB存储引擎来说，其支持行锁和表锁，比如一个事务执行update或者select xx from xx for update时，会持有锁(行锁、间隙锁或者大量的行锁引起锁升级为表锁等)，造成其他事务等待锁资源。

低效SQL语句

低效sql提高了数据库的响应时间，如返回过多数据列增大传输负载、where语句中进行计算、类型转换增加计算时间同时导致索引失效、大表中count(*)或不当sql分页查询靠后的数据(如直接limit 50w 100)、对非索引字段排序等。

刷脏页

当内存数据页和磁盘数据页上的内容不一致时，称这个内存页为脏页。在MySQL中，由Master Thread以每秒或每十秒的速度从缓冲池的脏页列表中异步刷新一定比例的页回磁盘。在刷脏页的过程中会使数据库产生抖动，导致当时的SQL执行慢。

资源瓶颈

MySQL所在实例IO、网络、CPU、内存、磁盘等使用达到上限。

如何定位慢SQL

MySQL提供了两种查询日志供分析使用：普通日志(General Log)和慢速日志(Slow Log)。

Geleral log记录了服务器接收到的每一个查询或是命令(包括语法错误的命令语句)。开启General log会产生不小的系统开销，默认是关闭的，可以通过命令查看设置。

show global variables like '%general_log%'; // 查看是否开启及文件目录

set global general_log = on; // 开启

Slow log只包含执行超过特定时长的已经执行过的语句，也就是慢sql。常见参数

show global variables like '%slow_query_log%'; // 查看是否开启及文件目录

show global variables like '%long_query_time%'; // 慢查询阈值 单位是秒

set global slow_query_log = on; //开启

log_queries_not_using_indexes // 开启后可以将没有使索引的SQL记录到慢查询日志

log_output //慢日志的格式

如何优化SQL语句

在优化之前可以通过EXPLAIN分析SQL 执行计划，找到慢的具体原因，再针对性的优化。

EXPLAIN的字段含义

Column	含义
id	查询序号
select_type	查询类型
table	表名
partitions	匹配的分区
type	join类型
prossible_keys	可能会选择的索引
key	实际选择的索引
key_len	索引的长度
ref	与索引作比较的列
rows	要检索的行数(估算值)
filtered	查询条件过滤的行数的百分比
Extra	额外信息

常见的优化方式：

（1）优化分页查询

通常会使用<limit M N>及order by实现分页查询，在没有索引的支持下会有文件排序操作（file sort）降低性能，在有索引的情况下，越往后翻页性能也会越差。

例如select * from tem_table order by tem_no limit 50w 10需要查询50010条数据，最后返回10条。这种方式会非常耗时，因为查询的字段不在辅助索引上（通常tem_no字段会有索引) 需要回表查询主键索引，会产生随机IO，同时前5w条数据不是需要的也回表了，会使耗时增加。

可以使用子查询优化，select * from tem_table where id> (select id from tem_table order by tem_no limit 50w 1) limit 10;这样子查询通过覆盖索引查到id，不需要回表，主查询通过主键索引只需返回10条数据，查询性能可以明显提升。

（2）优化 SELECT *

无用的字段会增加数据传输时间与网络开销、失去覆盖索引优化的可能。尽可能只查询需要的字段、尽可能使用索引覆盖减少回表操作。

（3）优化 SELECT COUNT(*)

在没有where条件时，MyISAM查询的速度要明显快于 InnoDB。因为MyISAM 存储引擎记录的是整个表的行数，在 COUNT(*) 查询操作时无需遍历表计算，直接获取该值即可。而在 InnoDB 存储引擎中就需要扫描表来统计具体的行数。在有where条件时两者差不多。

对一张大表做count操作扫描行数过多会产生慢sql，此时可以采用其他方式代替:

近似值：对于要求不是很精确的场景可以估算近似值。

统计表：对于需要精确统计的场景，可以新增一个统计表通过异步任务统计，或增加缓存字段来统计。

（4）其他优化

当单纯对sql语句的优化没有多大空间时，就要需要考虑从其他方面做优化了。如数据量过大时考虑分库分表，做垂直拆分或水平拆分，找DBA对数据库参数设置优化，通过增加辅助表进行优化、归档历史数据等。

比如使用辅助表做的一次优化：

有个code表，主要使用的有两个字段，这里简化为c_id和code，在使用时需要获取一个code。为了保证接口性能做了两次优化。第一次是将code异步加载到一个redis队列中，使用时直接获取一个。第二次是由于异步加载存在慢sql(索引区分度不好，同一个c_id可能有200w或者更多的code)，优化过程是加了一个index表，这个表有四个主要字段，这里简化为c_id、usingNum、upNum，给code表增加mark字段，mark是对c_id和编号(num)加密得来的，目的是为了增强索引的区分度，upNum记录上传的序号位置、usingNum记录使用的序号位置 mark字段建立了索引，每次加载时都是使用mark字段去查，走索引，且区分度较高。