8.2 show processlist 定位低效率执行SQL
慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用show processlist
命令查看当前MySQL正在进行的线程,包括线程的状态、是否锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。
show processlist;
只列出前100条,如果想全列出请使用 show full processlist;
🏷有时候一个快照可能看不出什么问题,那么可以频发的刷新执行试试
1) Id列,用户登录mysql时,系统分配的"connection_id 连接id",可以使用函数connection_id()查看
2) User列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
3) Host列,显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
4) db列,显示这个进程目前连接的是哪个数据库
5) Command列,显示当前连接的执行的命令,一般取值为休眠(sleep),查询(query),连接(connect)等
6) Time列,显示这个状态持续的时间,单位是秒
7) State列,显示使用当前连接的sql语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8) info列,显示这个sql语句,是判断问题语句的一个重要依据
这个命令中最关键的就是 State 列,mysql列出的状态主要有以下几种:
Checking table
正在检查数据表(这是自动的)。
Closing tables
正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
复制从服务器正在连接主服务器。
Copying to tmp table on disk
由于临时结果集大于 tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。
索引及现有结构无法涵盖查询条件,才会建立一个临时表来满足查询要求,产生巨大的恐怖的i/o压力。
很可怕的搜索语句会导致这样的情况,如果是数据分析,或者半夜的周期数据清理任务,偶尔出现,可以允许。频繁出现务必优化之。
Copy to tmp table通常与连表查询有关,建议逐渐习惯不使用连表查询。
实战范例: 某社区数据库阻塞,求救,经查,其服务器存在多个数据库应用和网站,其中一个不常用的小网站数据库产生了一个恐怖的copy to tmp table操作,导致整个硬盘i/o和cpu压力超载。Kill掉该操作一切恢复。
Creating tmp table
正在创建临时表以存放部分查询结果。
deleting from main table
服务器正在执行多表删除中的第一部分,刚删除第一个表。
deleting from reference tables
服务器正在执行多表删除中的第二部分,正在删除其他表的记录。
Flushing tables
正在执行 FLUSH TABLES,等待其他线程关闭数据表。
Killed
发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。
Locked
被其他查询锁住了。
有更新操作锁定
通常使用innodb(支持行锁定)可以很好的减少locked状态的产生,但是切记,更新操作要正确使用索引,即便是低频次更新操作也不能疏忽。
在myisam的时代,locked是很多高并发应用的噩梦。所以mysql官方也开始倾向于推荐innodb。
Sending data
正在处理 SELECT 查询的记录,同时正在把结果发送给客户端。
Sending data并不是发送数据,别被这个名字所欺骗,这是从物理磁盘获取数据的进程,如果你的影响结果集较多,那么就需要从不同的磁盘碎片去抽取数据
偶尔出现该状态连接无碍。
回到上面影响结果集的问题,一般而言,如果sending data连接过多,通常是某查询的影响结果集过大,也就是查询的索引项不够优化。
如果出现大量相似的SQL语句出现在show proesslist列表中,并且都处于sending data状态,优化查询索引,记住用影响结果集的思路去思考。
Sorting for group
正在为 GROUP BY 做排序。
Sorting for order
正在为 ORDER BY 做排序。
和Sending data类似,结果集过大,排序条件没有索引化,需要在内存里排序,甚至需要创建临时结构排序
Opening tables
这个过程应该会很快,除非受到其他因素的干扰。例如,在执
ALTER TABLE
或LOCK TABLE
语句行完以前,数据表无法被其他线程打开。 正尝试打开一个表。
Removing duplicates
正在执行一个
SELECT DISTINCT
方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。
Reopen table
获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。
Repair by sorting
修复指令正在排序以创建索引。
Repair with keycache
修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。
Searching rows for update
正在将符合条件的记录找出来以备更新。它必须在
UPDATE
要修改相关的记录之前就完成了。
Sleeping
正在等待客户端发送新请求
System lock
正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表,那么可以通过增加
--skip-external-locking
参数来禁止外部系统锁。
Upgrading lock
INSERT DELAYED
正在尝试取得一个锁表以插入新记录。
Updating
正在搜索匹配的记录,并且修改它们。
User Lock
正在等待 GET_LOCK()。
Waiting for tables
该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:
FLUSH TABLES tbl_name
、ALTER TABLE
、RENAME TABLE
、REPAIR TABLE
、ANALYZE TABLE
、或OPTIMIZE TABLE
。
waiting for handler insert
INSERT DELAYED
已经处理完了所有待处理的插入操作,正在等待新的请求。
Waiting for net,reading from net,writing to net
偶尔出现无妨
如大量出现,迅速检查数据库到前端的网络连接状态和流量
案例:因外挂程序,内网数据库大量读取,内网使用的百兆交换迅速爆满,导致大量连接阻塞在waiting for net,数据库连接过多崩溃
大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。还有其它的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。
show processlist 命令详解 原文地址:请优先查看!请优先查看!
问题排查
学会用 Mysql show processlist 排查问题 原文地址:请优先查看!请优先查看!
show full processlist
可以看到所有链接的情况,但是大多链接的 state 其实是 Sleep 的,这种的其实是空闲状态,没有太多查看价值
我们要观察的是有问题的,所以可以进行过滤:
-- 查询非 Sleep 状态的链接,按消耗时间倒序展示,自己加条件过滤 select id, db, user, host, command, time, state, info from information_schema.processlist where command != 'Sleep' order by time desc
这样就过滤出来哪些是正在干活的,然后按照消耗时间倒叙展示,排在最前面的,极大可能就是有问题的链接了,然后查看 info 一列,就能看到具体执行的什么 SQL 语句了,针对分析。
kill 使用
上面提到的 线程ID 是可以通过 kill 杀死的;所以上面基本上可以把有问题的执行语句找出来,然后就可以 kill 掉了,那么一个一个来 kill 么?
-- 查询执行时间超过2分钟的线程,然后拼接成 kill 语句 select concat('kill ', id, ';') from information_schema.processlist where command != 'Sleep' and time > 2*60 order by time desc
在下一步我就不用说了吧,把拼接 kill 的执行结果跑一遍就搞定了
这个有时候非常好用,谁用谁知道
常见问题
一些问题会导致连锁反应,而且不太好定位,有时候以为是慢查询,很可能是大多时间是在等在CPU、内存资源的释放,所以有时候同一个查询消耗的时间有时候差异很大
总结了一些常见问题:
- CPU报警:很可能是 SQL 里面有较多的计算导致的
- 连接数超高:很可能是有慢查询,然后导致很多的查询在排队,排查问题的时候可以看到”事发现场“类似的 SQL 语句一大片,那么有可能是没有索引或者索引不好使,可以用:
explain
分析一下 SQL 语句
本文来自博客园,作者:Lz_蚂蚱,转载请注明原文链接:https://www.cnblogs.com/leizia/p/16063212.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步