专栏首页技术面面观到底是哪条SQL拖慢了你的数据库?

到底是哪条SQL拖慢了你的数据库?

我们使用的是mariadb 10.1.20 版本、mysql也适用

开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

核心参数

以下就是我们要设置的核心参数

ps: 管理语句包括: ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE.

确认单点还是集群

第一步要做的就是确认单点还是集群 以下是集群查询结果,可以看到是3节点集群,单点的返回结果是空的

show global status like 'wsrep_cluster_size';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| wsrep_cluster_size | 3 |
+-----------------------+-------+
1 rows in set (0.00 sec)

根据不同的集群有三种情况:

  • 如果是主从模式集群,只需要在主节点上操作,同时打开log_slow_slave_statements开关就可以了。
  • 如果是多主,则每个主节点都要操作一次。
  • 如果是单点,只需要操作一次。

修改配置文件开启慢查询(要重启)

$ vim /etc/my.cnf.d/server.cnf

在mysqld节加上以下配置后重启即可

[mysqld]
slow_query_log = ON
slow_query_log_file = /data/mariadb/data/slow.log
long_query_time = 1
log_slow_slave_statements = ON
log_slow_admin_statements = ON

免重启方式开启慢查询(重启失效)

这种方式只需要登陆Mariadb输入以下命令即可 注:使用命令 set global 修改后,,需要重新连接或新开一个会话才能看到修改值。

set global log_slow_slave_statements=1;
set global log_slow_admin_statements=1;
set global slow_query_log=1;
set global slow_query_log_file='/data/mariadb/data/slow.log';
set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

查询是否修改成功

重启mariadb,然后输入命令查询,如果和以下结果一样,就是成功打开了

show variables like '%slow%';
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_admin_statements | ON |
| log_slow_slave_statements | ON |
| slow_query_log | ON |
| slow_query_log_file | /data/mariadb/data/slow.log |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

测试是否开启成功

执行查询语句,休眠4秒,超过我们定的1秒阀值

select sleep(4);
+----------+
| sleep(4) |
+----------+
| 0 |
+----------+
1 row in set (4.00 sec)

查看慢查询日志,可以看到我们刚刚执行的select sleep(4);

$ tail slow.log 
/usr/local/mariadb/bin/mysqld, Version: 10.1.20-MariaDB (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mariadb.sock
Time Id Command Argument
# Time: 190528 13:25:49
# User@Host: root[root] @ localhost []
# Thread_id: 59011860 Schema: QC_hit: No
# Query_time: 4.000110 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0
SET timestamp=1559021149;
select sleep(4);

分析慢查询语句

自带的日志分析工具mysqldumpslow: 比如: 得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow.log

另外建议在使用这些命令时结合 |more 或者直接 > file.log 重定向到新文件中 ,否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/slow.log | more

着重关注哪些sql?

  • 查询次数多且每次查询占用时间长的sql
  • IO大的SQL
  • 未命中索引的SQL

缺点:慢查询日志是在查询结束后才记录,故正在执行的慢SQL并不能被定位到,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等等,可以实时地查看SQL的执行情况。

本文分享自微信公众号 - 编程三分钟(coding3min)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 希尔排序

    希尔排序(ShellSort)是以它的发明者Donald Shell名字命名的,希尔排序是插入排序的改进版,实现简单,对于中等规模数据的性能表现还不错

    机智的程序员小熊
  • 漏提交与打tag- 每天三分钟玩转Git(9)

    恭喜你!!当你学到这一篇的时候,可以非常有底气的说自己是一个熟悉Git的使用和底层原理的Git高手了!开发协作也完全没有了问题。了解了各个步骤操作的后果再也不担...

    机智的程序员小熊
  • 直接插入排序

    插入排序是一种比较简单直观的排序算法,适用处理数据量比较少或者部分有序的数据,今天我们来聊聊插入排序。

    机智的程序员小熊
  • java面试之数据库篇

    为什么要使用索引? 全表扫描是低效的, 但是全表扫描在表里数据量非常少的时候效率挺好, 数据量大了就不行了

    北漂的我
  • PHP表单处理与文件保存

    在php中 能够通过$_FILE 获取上传的文件 * 浏览器端部分代码() * 假定浏览器在form表单中如下标签 * 注1form...

    csxiaoyao
  • redo log和binlog的一些好问题

    那么如果在两阶段提交的过程中,发生了数据库的崩溃,MySQL内部会做什么事情来保证数据的一致性呢?以上述的update操作为例:

    AsiaYe
  • 这个2000人团队做的业务不赚钱,却是李彦宏的战略部队

    这几天去三亚参加百度地图的采集训练营,详细了解了地图数据采集作业流程,感触颇深。地图给我们的生活带来很大的便捷,看上去很简单的App背后却有庞大的团队在支持。数...

    罗超频道
  • 基于wanAndroid-项目实战

    秦子帅
  • msvc:BAT脚本判断是否设置MSVC编译的环境变量(执行vcvarsall.bat)

    版权声明:本文为博主原创文章,转载请注明源地址。 https://blog.csdn.net/10...

    用户1148648
  • 云计算对于互联网基础设施意味着什么

    由于云计算在IT世界得到广泛应用,开发人员和组织需要考虑在负载平衡、集成、安全性等方面采用最新技术。 如今的商业环境是以移动性和云计算为中心,那些未能将业务迁移...

    静一

扫码关注云+社区

领取腾讯云代金券