送大家以下 java 学习材料
概述
如果是 Oracle 数据库咱们能够很容易通过 sql 来定位到以后数据库中哪些耗费 CPU 高的语句，而 mysql 数据库能够怎么定位呢？这里用一个简略例子阐明下 …
次要是理解如何定位的思路，具体看官网介绍..
参考：https://www.percona.com/blog/…
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`
通过该命令咱们能够定位到 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``
依据操作系统 id 能够到 processlist 表找到对应的会话，如下：
3、查看问题 sql 执行打算
这里对应看一下执行打算根本就能够判断以后数据库 CPU 为什么耗费这么高了 …
至于优化的点只须要在 dock 建一个索引即可，这里就不介绍了。