Mysql 优化之查询截取分析

最新推荐文章于 2024-06-18 15:51:21 发布

白耘

最新推荐文章于 2024-06-18 15:51:21 发布

阅读量98

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 mysql优化

本文链接：https://blog.csdn.net/weixin_43558515/article/details/117225025

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

查询优化

1. 小表驱动大表

MySQL 只支持循环嵌套算法，因此驱动结果集越大，所需要的循环就越多，那么被驱动表的访问次数也就越多，而每次访问表，都会产生 IO 访存，因此循环次数越多，IO 访存也越多，并且每次循环都要消耗 CPU。因此我们应该尽量选择更小的驱动表，也即小表驱动大表。

现有表A（员工表），B（部门表），id 为部门 id。

因此 A 中有很多列，为大表，B 中的列较少，为小表。

表 A 与表 B 的 id 字段建立了索引。

#-------1.in---------- 
select * from A where id in (select id from B)
#等价于
for select id from B    #表B有n列，对表B访存n次
    for select * from A where A.id = B.id    #对表A访存n次，无论A有多少列

#------2.exists-------
select * from A where exists (select * from B where B.id = A.id)
#等价于
for select * from A    #表A有n列，对表A访存n次
    for select * from B where B.id = A.id    #对表B访存n次，无论B有多少列

驱动表越小，IO 访存越少

需要注意的是，我们不能只看表的大小，应该看经 where 过滤之后表的大小，如：

select * from A where name = "bob" and id in (select id from B)

此时驱动表应该为表 A 中姓名为 bob 的列，有可能会比表 B 小。

2. ORDER BY 与 GROUP BY 关键字优化

ORDER BY 尽量使用索引进行排序，以避免产生FileSort；GROUP BY 实际上是先排序后分组，可类比 ORDER BY。

慢查询日志

1. 慢查询日志是什么

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。
具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为10，意思是运行10秒以上的语句。
由他来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 sql 执行超过5秒钟，我们就算慢 SQL，希望能收集超过5秒的 sql，结合之前 explain 进行全面分析。

2. 如何启动慢查询日志

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

SQL	描述
show variables like ‘%slow_query_log%’;	查看慢查询日志是否开启
set global slow_query_log=1;	开启慢查询日志
show variables like ‘long_query_time%’;	查看慢查询设定阈值（单位：秒）
set long_query_time=1	设定慢查询阈值（单位：秒）
show global status like ‘%slow_queries%’;	查询超过阈值的 SQL 语句数量

3. 查看慢查询日志

SHOW VARIABLES LIKE '%slow_query_log%';可以查看慢查询日志是否开启，以及慢查询日志保存的位置：

第二列可以看出日志保存在 /var/lib/mysql/localhost-slow.log 文件中。

查看慢查询日志

通过 select sleep 制造高耗时的 SQL 语句：

通过 cat 指令获取文件内容：

上述信息有：

时间： Time: 210523 9:15:06，表示这是21年05月23日9:15:06产生的 SQL
用户： User@Host: root[root]，表示执行该 SQL 语句的用户是 root
执行时间： Query_time: 2.009751
使用的数据库： use db1;
时间戳： SET timestamp=1621775706;
超过阈值的 SQL：select sleep(2);

Show Profile

1. Show Profile 的作用

    Show Profile 是一个查询剖析工具，默认是禁止的。开启 Show Profile 后，在服务器上执行的所有 SQL 语句，都会测量其耗费的时间和其他一些查询执行状态变更的相关数据。

    当一条查询提交给服务器时，此工具会记录剖析信息到一张临时表上，并给查询赋予一个从 1 开始的整数标识符。

2. 开启 Show Profile

Show Profile 默认是禁止的，因此需要先开启 Show Profile。

Set profiling=1;开启 Show Profile

Show Variables Like '%profiling%'查看 Show Proflie 状态

Variable_name	Value	备注
have_profiling	YES	是否具备 Show Profile 功能
profiling	ON	Show Profile 是否开启
profiling_history_size	15	Show Profile 缓存大小

3. 如何使用 Show Profile

通过Show Profiles可以查询最近几次的 SQL 执行时间，注意 Profiles 是复数。

profiling_history_size = 15，Show Profile 便记录下了最近的15条 SQL 语句，以及他们所消耗的时间。

通过show profile for query N可以查询第 N 条 SQL 具体执行时间：
在这里插入图片描述

通过show profile cpu,block io for query N可以查询第 N 条 SQL 的 CPU 及 IO 占用：
在这里插入图片描述

剖析报告给出了查询执行的每个步骤及其花费时间，可以针对花费时间较多的步骤进行优化。

全局查询日志

1. 全局查询日志是什么

    开启全局查询日志将会记录所有执行的 SQL 语句，会对系统性能产生负担，默认不开启，一般在测试环境下开启。

2. 开启全局查询日志

修改配置文件 my.cnf 开启

开启：general_log = 1

全局查询日志保存路径：general_log_file = /path/logfile

输出格式：log_output = FILE
使用编码开启

set global general_log = 1

set global log_output = 'TABLE'

此后，所有执行的 SQL 语句都会记录到 mysql 库中的 general_log 表中;

通过select * from mysql.general_log语句查看 general_log 表：

白耘

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql 优化之查询截取分析

查询优化1. 小表驱动大表MySQL 只支持循环嵌套算法，因此驱动结果集越大，所需要的循环就越多，那么被驱动表的访问次数也就越多，而每次访问表，都会产生 IO 访存，因此循环次数越多，IO 访存也越多，并且每次循环都要消耗 CPU。因此我们应该尽量选择更小的驱动表，也即小表驱动大表。现有表A（员工表），B（部门表），id 为部门 id。因此 A 中有很多列，为大表，B 中的列较少，为小表。表 A 与表 B 的 id 字段建立了索引。#-------1.in---------- select *
复制链接

扫一扫