如何快速定位当前数据库消耗CPU最高的sql语句？

我会带着你远行 2022-10-18 12:49 43阅读 0赞

点击蓝色“java大数据修炼之道”关注我哟
    
    加个“星标”，每晚21:00，一起学技术

来源：https://www.toutiao.com/i6923526305795293707?wid=1623686217615

概述

如果是Oracle数据库我们可以很容易通过sql来定位到当前数据库中哪些消耗CPU高的语句，而mysql数据库可以怎么定位呢？这里用一个简单例子说明下...

主要是了解如何定位的思路，具体看官网介绍..

> 参考：https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/

> One of our customers recently asked whether it is possible to identify, from the MySQL side, the query that is causing high CPU usage on his system. The usage of simple OS tools to find the culprit has been a widely used technique for a long time by PostgreSQL and Oracle DBAs, but it didn’t work for MySQL as historically we’ve lacked the instrumentation to match an OS thread with an internal processlist thread – until recently.
> 
> Percona added support to map processlist ids to OS thread ids through column TID of the information\_schema.processlist table starting on Percona Server for MySQL 5.6.27. With the release of 5.7, MySQL followed with its own implementation by extending the PERFORMANCE\_SCHEMA.THREADS table and adding a new column named THREAD\_OS\_ID, which Percona Server for MySQL adopted in place of its own, as it usually does remain as close to upstream as possible.
> 
> The following approach is useful for cases where there is a query overloading one particular CPU while other cores are performing normally. For cases where it is a general CPU usage issue, different methods can be used, such as the one in this other blog post Reducing High CPU on MySQL: A Case Study.

主要意思是针对定位CPU的问题，Percona增加了对通过信息的TID列将processlist ID映射到OS线程ID的支持，而MySQL在5.7版本后在 PERFORMANCE\_SCHEMA.THREADS表加了一个THREAD\_OS\_ID新列来实现，以下方法适用于在其他内核正常运行时，某个特定CPU的查询过载的情况。

--------------------

## find out which session is using the most CPU resources in my database? ##

1、定位线程

pidstat -t -p <mysqld_pid> 1  5

![db2e92d4bc2d975f38a790cb0627ca7d.png][]

通过该命令我们可以定位到802、4445等线程消耗了大量的CPU，这里尽量确保在pidstat的多个样本中验证消耗是恒定的。根据这些信息，我们可以登录到数据库，并使用以下查询找出哪个MySQL线程是罪魁祸首。

--------------------

## 2、定位问题sql ##

select * from performance_schema.threads where thread_os_id = xx ;
    select * from information_schema.`PROCESSLIST` where  id=threads.processlist_id

![6af23de8a3ccdb8a1b937a5670b497a0.png][]

根据操作系统id可以到processlist表找到对应的会话，如下：

![4b682a8a00f1a27a5be93721e72f74b7.png][]

--------------------

## 3、查看问题sql执行计划 ##

这里对应看一下执行计划基本就可以判断当前数据库CPU为什么消耗这么高了...

至于优化的点只需要在dock建一个索引即可，这里就不介绍了。

![9a236c92957a7244261a2cef5ab0b3fa.png][]

**在微信公众号后台回复**关键字: **mysql** (为防止出错: 强烈建议复制关键字到后台回复

获取：mysql从入门到精通 完整版视频讲解资源 , 感谢支持 !)

PS：如果觉得我的分享不错，欢迎大家随手点赞、在看。

▽加我微信,交个朋友

![e2078563bb7eca072ee7f094fc3b5a83.png][]

长按/扫码添加↑↑↑

*  [1、MySQL 技术非懂不可][1_MySQL]
 *  [2、颠覆认知 - MySQL数字类型如何选择？][2_ - MySQL]
 *  [3、把我坑惨的一个MySQL双引号！][3_MySQL]
 *  [4、这六个 MySQL 死锁案例，能让你理解死锁的原因！][4_ MySQL]
 *  [5、MySQL老大难事务和锁，一次性讲清楚！][5_MySQL]
 *  [6、面试官问：缓存与数据库一致性如何解决？先操作数据库，还是缓存？][6]

**看完本文有收获？请转发分享给更多人**

请长按二维码，关注 Java大数据修炼之道.

**推荐程序员必备公众号 **

**▼**

> **Java大数据修炼之道**
> 
> 公众号：
> 
> gh\_9119f24d3793
> 
> **推荐理由：**
> 
> 在这里，我们分享程序员相关技术，职场生活，行业热点资讯。不定期还会分享IT趣文和趣图。这里属于我们程序员自己的生活，工作和娱乐空间。
> 
>  ▼长按下方↓↓↓二维码识别关注
> 
> ####  ####
> 
> 关注 java大数据修炼之道
> 
>     每天学习java技术你想学的Java知识这里都有**![e98ff878b4292f479b3c2a3ecba2ffde.png][]**
> 
> ![f97bf8be01913fe35885cb9cfed13b30.png][]

![3e295853737024db482b1e80d730f2d0.png][]

点「在看」的人都涨薪了哦

[db2e92d4bc2d975f38a790cb0627ca7d.png]: /images/20221005/2637d3a0f0dd4dd7a70c1b1b5c767e19.png
[6af23de8a3ccdb8a1b937a5670b497a0.png]: /images/20221005/558ccae4942a4eec9ca05c029b5bf82c.png
[4b682a8a00f1a27a5be93721e72f74b7.png]: /images/20221005/7ec5bc8745c84295a36e591963430a8c.png
[9a236c92957a7244261a2cef5ab0b3fa.png]: /images/20221005/feb544c09614444dbb71f82429d517ac.png
[e2078563bb7eca072ee7f094fc3b5a83.png]: /images/20221005/711207b3b094477fa5fbcde3131441d1.png
[1_MySQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc20ac3ccb583d527905703508d50be681d8bc660a3e361b248e906a855e6bad2109ce7cda1&idx=1&mid=2247500815&scene=21&sn=b2f01eafa86e728cb4973ce4a8b92646#wechat_redirect
[2_ - MySQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc20e44ccb587527df9aa06b820dbed095f6e679cb1f809003ed0063c40b23296f0b743829b&idx=1&mid=2247499912&scene=21&sn=cea925f1929533c2041ca82a05380622#wechat_redirect
[3_MySQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc20edaccb587cca8480250055d874c5b05a7f4873605d62d8b0e2b114a73eb61a4578353e6&idx=1&mid=2247499798&scene=21&sn=9d34000b38a90e0fab6a60226e5bc7ef#wechat_redirect
[4_ MySQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc231c5ccb5b8d3070070dc557e0012871f337483f51c77a5d35f620325ea83b4285ac6c7dd&idx=1&mid=2247499529&scene=21&sn=a890bbaf759fffc4c42b2a7a5364e696#wechat_redirect
[5_MySQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc236a1ccb5bfb79dd516a57ff9d6cdf6461070a07ba1aeed7d6c49f4ea1c00617b879bd0ed&idx=1&mid=2247497837&scene=21&sn=67a453371ca837259db26cedf5be3a6d#wechat_redirect
[6]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc23963ccb5b075befab75f5352e1bff4354348ff0980cdb1344a364c2cc4f98c300769e384&idx=1&mid=2247497647&scene=21&sn=dbc29bcc506341233d2257ea79366203#wechat_redirect
[e98ff878b4292f479b3c2a3ecba2ffde.png]: /images/20221005/4ddd9a2d7e2a49e588bf4cc2e5195c0f.png
[f97bf8be01913fe35885cb9cfed13b30.png]: /images/20221005/2eaaa4e2dcba41329963a9cde119bc94.png
[3e295853737024db482b1e80d730f2d0.png]: /images/20221005/599f2af1addc481dab92055e1939c6b7.png