MySQL 如何找出占用CPU较高的SQL

在5.7版本中，MySQL通过扩展PERFORMANCE_SCHEMA实现了自己的实现。在THREADS表中添加了一个名为THREAD_OS_ID的新列，这是Percona Server for MySQL所采用的代替它自己的列，因为它通常保持尽可能接近上游。

The command is pidstat -t -p <mysqld_pid> 1

shell>  pidstat -t -p 31258 1
03:31:06 PM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
[...]
03:31:07 PM 10014         -     32039    5.00    1.00    0.00    6.00    22  |__mysqld
03:31:07 PM 10014         -     32040    5.00    1.00    0.00    6.00    23  |__mysqld
03:31:07 PM 10014         -     32042    6.00    1.00    0.00    7.00     8  |__mysqld
03:31:07 PM 10014         -     32047    5.00    1.00    0.00    6.00     6  |__mysqld
03:31:07 PM 10014         -     32048    5.00    1.00    0.00    6.00    15  |__mysqld
03:31:07 PM 10014         -     32049    5.00    1.00    0.00    6.00    14  |__mysqld
03:31:07 PM 10014         -     32052    5.00    1.00    0.00    6.00    14  |__mysqld
03:31:07 PM 10014         -     32053   94.00    0.00    0.00   94.00     9  |__mysqld
03:31:07 PM 10014         -     32055    4.00    1.00    0.00    5.00    10  |__mysqld
03:31:07 PM 10014         -      4275    5.00    1.00    0.00    6.00    10  |__mysqld
03:31:07 PM 10014         -      4276    5.00    1.00    0.00    6.00     7  |__mysqld
03:31:07 PM 10014         -      4277    6.00    1.00    0.00    7.00    15  |__mysqld
03:31:07 PM 10014         -      4278    5.00    1.00    0.00    6.00    18  |__mysqld
03:31:07 PM 10014         -      4279    5.00    1.00    0.00    6.00    10  |__mysqld
03:31:07 PM 10014         -      4280    5.00    1.00    0.00    6.00    12  |__mysqld
03:31:07 PM 10014         -      4281    5.00    1.00    0.00    6.00    11  |__mysqld
03:31:07 PM 10014         -      4282    4.00    1.00    0.00    5.00     2  |__mysqld
03:31:07 PM 10014         -     35261    0.00    0.00    0.00    0.00     4  |__mysqld
03:31:07 PM 10014         -     36153    0.00    0.00    0.00    0.00     5  |__mysqld

查看performance_schema.threads表

mysql > select * from performance_schema.threads where THREAD_OS_ID = 32053 \G
*************************** 1. row ***************************
          THREAD_ID: 686
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 590
   PROCESSLIST_USER: msandbox
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select * from test.joinit where b = 'a a eveniet ut.'
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 32053
1 row in set (0.00 sec)

通过explain查看执行计划并优化

mysql > explain select * from test.joinit where b = 'a a eveniet ut.' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: joinit
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7170836
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

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