李桃峰的技术博客

# 如何定位并优化慢查询SQL

  • 根据慢日志定位慢查询sql
  • 使用explain等工具分析sql
  • 修改sql或者尽量让sql走索引

# 慢日志查询步骤

慢查询日志的作用

  • 慢查询日志会把查询耗时超过规定时间的SQL语句记录下来
  • 利用慢查询日志，定位分析性能瓶颈
  • slow_query_log 可以设置慢查询日志的开闭状态
  • slow_query_log_file 慢日志存放的地址
  • long_query_time 可以规定查询超时的时间，单位是秒,默认为 10秒，一般设置为1秒
-- 查询慢查询日志是否开启 以及 日志存放的路径
show variables like 'slow_query%'
1
2

查询慢查询sql的数量

-- 查询慢查询sql的数量
show status like '%slow_queries%'
1
2

打开慢日志命令

set global slow_query_log =on;
1

设置慢查询sql时间，如果sql查询超过1秒，则记录,注意：设置完后，需要重新连接，才能看到是否生效

set global long_query_time=1;
1

以上set参数也可以在my.cnf（linux） 或 my.ini（windows）这个配置文件里进行设置，如果是直接命令方式，当重启数据库服务的时候，就有复原了

注意：打开以上的设置，只是监听记录 本窗口的慢查询

# 利用explain关键字分析慢查询sql

将explain关键字放在select语句前面，用于描述mysql是如何执行查询操作的 并返回 查询sql返回的行数，explain 可以帮助我们分析select语句让我们知道查询效率低下的原因，从而改进我们的查询，让查询优化器能够更好的工作
例如：

EXPLAIN select * from act_hi_varinst ORDER BY NAME_ desc;
1

# explain关键字段

  • type
  • extar

# type

type表示的是mysql找到需要数据行的方法，方式，性能重最优到最差如下：
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range> index>all
index和all表示走的是全表扫描,但看到type是这两个值中的任何一个，证明语句是需要有优化的

# extra

可以从extar获取到一些更为详细的信息，辅助我们了解sql语句执行的运行方式，extra中出现以下2项意味着mysql根本不能 使用索引，效率会受到重大影响，应尽可能的对此进行优化。

extra项 说明
Using filesort 表示mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。mysql中无法利用索引完成的排序操作称为“文件排序”
Using temporary 表示mysql在对查询结果排序时使用了临时表。常见于排序order by和分组查询group by

使用force index 是强制使用某一类索引，可以用来判断走哪个索引是最优的，例如:

explains select count(id) froun tableName force index(primary)
1

# mysql技巧

技巧1 比较运算符能用 “=”就不用“<>”

“=”增加了索引的使用几率。

技巧2 明知只有一条查询结果，那请使用 “LIMIT 1”

“LIMIT 1”可以避免全表扫描，找到对应结果就不会再继续扫描了。

技巧3 为列选择合适的数据类型

能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT，道理你懂的，磁盘和内存消耗越小越好嘛。

技巧4 将大的DELETE，UPDATE or INSERT 查询变成多个小查询

能写一个几十行、几百行的SQL语句是不是显得逼格很高？然而，为了达到更好的性能以及更好的数据控制，你可以将他们变成多个小查询。

1613841473606

技巧5 使用UNION ALL 代替 UNION，如果结果集允许重复的话

因为 UNION ALL 不去重，效率高于 UNION。

技巧6 为获得相同结果集的多次执行，请保持SQL语句前后一致

这样做的目的是为了充分利用查询缓冲。

比如根据地域和产品id查询产品价格，第一次使用了：

SELECT price from order where id=123 and regin='shanghai'
1

那么第二次同样的查询，请保持以上语句的一致性，比如不要将where语句里面的id和region位置调换顺序。

技巧7 尽量避免使用 “SELECT *”

如果不查询表中所有的列，尽量避免使用 SELECT *，因为它会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。

技巧8 WHERE 子句里面的列尽量被索引

只是“尽量”哦，并不是说所有的列。因地制宜，根据实际情况进行调整，因为有时索引太多也会降低性能。

技巧9 JOIN 子句里面的列尽量被索引

同样只是“尽量”哦，并不是说所有的列。

技巧10 ORDER BY 的列尽量被索引

ORDER BY的列如果被索引，性能也会更好。

技巧11 使用 LIMIT 实现分页逻辑

不仅提高了性能，同时减少了不必要的数据库和应用间的网络传输。

技巧12 使用 EXPLAIN 关键字去查看执行计划

EXPLAIN 可以检查索引使用情况以及扫描的行。

其他

SQL调优方法有很多种，同样的查询结果可以有很多种不同的查询方式。其实最好的方法就是在开发环境中用最贴近真实的数据集和硬件环境进行测试，然后再发布到生产环境中。

# 本站导航

MySql慢查询日志
mySql联合索引最左匹配原则
mySql锁
mySql优化
回到主导航页

# 支持我-微信扫一扫-加入微信公众号

Aseven公众号

# 赞赏作者

赞赏作者