最近在看《高性能的 Mysql》一书，下面是关于如何学习统计 Mysql 服务器状态的学习总结，主要是学习使用 SHOW STATUS，SHOW ENGINE INNODB STATUS，SHOW PROCESSLIST，SHOW PROFILE 四个命令。
命令一：SHOW STATUS
- show status 命令用于查询 Mysql 状态变量相关统计信息
- 通过 show status like 查看部分变量
- 这些状态变量对用户来说是是只读的，Mysql 自身在运行过程中会自动更新这些状态信息
- show status 混杂了全局和会话变量，其中很多变量既有全局也有会话级别的
- 如果会话级别和全局重名，show status 只显示会话级别的，可以通过 show global status 查看全局状态
- show status 查询的结果可以在 information_schema.global_status 和 information_schema.session_status 查到
Aborted_* 相关变量
mysql> show status like “aborted%”;
±-----------------±--------+
| Variable_name | Value |
±-----------------±--------+
| Aborted_clients | 1439 |
| Aborted_connects | 5025336 |
±-----------------±--------+
2 行于数据集 (0.08 秒)
Aborted Connect 表示尝试连接到 MySQL 服务器失败的次数，引起这个状态变量激增的原因如下：
- 客户端没有权限但是尝试访问 MySQL 数据库
- 客户端输入的密码有误
- A connection packet does not contain the right information（连接发送包数据不正确），这种情况下一般都是监控系统检查服务器 3306 端口是否存活报错导致的
- 超过连接时间限制，连接超时时间由系统变量 connect_timeout 控制（mysql 默认是 10s）
Aborted Clients 意味着有客户端成功建立连接，但是由于某些原因断开连接或者被终止了，这种情况一般发生在网络不稳定的环境中。主要的可能性有：
- 客户端程序在退出之前未调用 mysql_close（）正确关闭 MySQL 连接
- 客户端休眠的时间超过了系统变量 wait_timeout 和 interactive_timeout 的值，导致连接被 MySQL 进程终止
- 客户端程序在数据传输过程中突然结束
connection* 相关变量
mysql> show status like ‘%connections%’;
±----------------------------------±------+
| Variable_name | Value |
±----------------------------------±------+
| Connection_errors_max_connections | 0 |
| Connections | 197 |
| Max_used_connections | 2 |
±----------------------------------±------+
- connections 表示MySQL从启动至今，成功建立连接的连接数，这个值是不断累加的
- Max_used_connections 表示 MySQL 从启动至今，同一时刻并发的连接数的最大值。如果这个值大于 max_connections 参数配置则表明系统经常处于高并发的状态，应该考虑调大最大并发连接数
- Connection_errors_max_connections 当 MySQL 的最大并发数大于系统变量 max_connections 的最大并发数，因此而被拒绝的次数，将会记录在这个变量里。如果 Connection_error_max_connections 值比较大，则说明当前系统并发比较高，要考虑调大 max_connections 的值。
这边顺便了解一下关于连接信息的系统变量配置：
mysql> show variables like ‘%connect%’;
±----------------------------------------------±----------------+
| Variable_name | Value |
±----------------------------------------------±----------------+
| character_set_connection | utf8 |
| connect_timeout | 10 |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
±----------------------------------------------±----------------+
- max_connections：
是指 MySQL 服务实例能够同时接受的的最大并发连接数。MySQL 实际上支持最大连接数加一的算法，保障当连接数用完的时候，超级管理员依然可以和服务端建立连接，进行管理。
- max_user_connections：
每个账号的最大并发连接数。
- max_connect_errors：
当某台非法主机恶意连接 MySQL 服务端，遭到的错误达到设置值后，MySQL 会拒绝来自该主机的所有连接。但执行 flush hosts 后会清零。
- connect_timeout
在获取连接阶段（authenticate）起作用，获取 MySQL 连接是多次握手的结果，除了用户名和密码的匹配校验外，还有 IP->HOST->DNS->IP 验证，任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上，超过 connect_timeout 的连接请求将会被拒绝，默认值 10 秒。
Thread_* 相关变量
mysql> show status like ‘Thread%’;
±------------------------±------+
| Variable_name | Value |
±------------------------±------+
| Threads_cached | 29 |
| Threads_connected | 94 |
| Threads_created | 417 |
| Threads_running | 2 |
±------------------------±------+
6 行于数据集 (0.02 秒)
- Thread_cached 在缓存中的线程数，该数字受 thread_cache_size 参数大小的影响
- Thread_connected 当前的连接数
- Thread_created: 从启动到现在，创建过的线程数，如果该值很大，建议调大 thread_cache_size 参数
- Thred_running：当前活跃的线程数
Com_* 开头的统计变量
mysql> show global status like ‘Com%’;
±----------------------------±------+
| Variable_name | Value |
±----------------------------±------+
| Com_change_db | 4444 |
| Com_select | 5117790905 |
| Com_alter_db | 0 |
…
±----------------------------±------+
Com_* 开头的统计信息主要用于记录每种类型的 SQL 发起过的次数，例如 Com_delete 和 Com_insert 用于统计 Delete 和 Insert 操作的次数，但是如果一次查询命中缓存，该数字将不会被记录。注意的是 show global status like ‘Com%’ 和 show status like ‘Com%’ 的区别，后者可能只显示当前会话的统计值。
Created_tmp* 临时文件和临时表统计
mysql> show global status like ‘Created_tmp%’;
±------------------------±---------+
| Variable_name | Value |
±------------------------±---------+
| Created_tmp_disk_tables | 8856206 | 临时表在磁盘的创建量
| Created_tmp_files | 381 | 临时文件的创建量
| Created_tmp_tables | 26450958 | 临时表的总的创建量
±------------------------±---------+
3 行于数据集 (0.14 秒)
查询缓存的统计
通过变量 query_cache_type 来设置是否开启缓存，通过变量 query_cache_limit 来设置缓存结果集的上限，如果某次查询超过该上限制则不进行缓存
mysql> show global status like ‘Qcache%’;
±------------------------±---------+
| Variable_name | Value |
±------------------------±---------+
| Qcache_free_blocks | 1 | 这个表示目前处于空闲状态的 query cache 中内存 block 的数目
| Qcache_free_memory | 1031832 | 缓存中空闲内存总量
| Qcache_hits | 0 | 缓存命中次数
| Qcache_inserts | 0 | 缓存失效次数
| Qcache_lowmem_prunes | 0 | 缓存出现内存不足并且必须要进行清理以便为 Qcache_inserts 动作腾出空间的次数
| Qcache_not_cached | 17841427 | 没有进行缓存的查询的数量
| Qcache_queries_in_cache | 0 | 当前在 query_cache 中‘注册’的 select 语句条数
| Qcache_total_blocks | 1 | 缓存中块的总量
±------------------------±---------+
8 行于数据集 (0.12 秒)
Select 查询类型统计
Select.* 统计特定类型 Select 查询的计数器，它可以帮我们了解各种查询计划
mysql> show global status like ‘Select%’;
±-----------------------±---------+
| Variable_name | Value |
±-----------------------±---------+
| Select_full_join | 7224 |
| Select_full_range_join | 0 |
| Select_range | 72966 |
| Select_range_check | 0 |
| Select_scan | 22053174 |
±-----------------------±---------+
5 行于数据集 (0.17 秒)
- Select_scan：表示查询时第一张表整表扫描的次数，如果你并不想要第一张表的所有行但是又没用索引，此时查询性能很差
- Select_range：在第一张表上扫描索引区间的查询数目
- Select_range_check：这个比 Select_full_join 要好一点，和 Select_range 差不多。区别是 MySQL 不能确定它能否使用一个范围来做关联查询。如果可以那么会使用范围，如果不行仍会使用全表扫描
- Select_full_join：和 Select_scan 差不多，区别是 Select_full_join 代表的是第二张及之后的表
- Select_full_range_join：和 Select_range_check 类似，不过 MySQL 可以肯定它能够使用范围查找。这时 explain 中的类型是 range
Sort.* 类型统计
当 mysql 不能使用一个索引来进行预先排序的时，必须使用文件排序，这就会增加 Sort* 状态变量的值，
mysql> show global status like “Sort%”;
±------------------±--------+
| Variable_name | Value |
±------------------±--------+
| Sort_merge_passes | 256 |
| Sort_range | 31603 |
| Sort_rows | 9718897 |
| Sort_scan | 1727948 |
±------------------±--------+
4 行于数据集 (0.02 秒)
- Sort_range：当 Mysql 从文排序结果中读取已经排序好的行时，如果是 Select_range 增加，那么 Sort_range 也会增加
- Sort_scan：当 Mysql 从文排序结果中读取已经排序好的行时，如果是 Select_scan 增加，那么 Sort_scan 也会增加
- Sort_rows：被排序记录的总数
- Sort_merge_passes： Mysql 在排序时首先尝试在内存中做排序，使用内存的大小由 Sort_buffer_size 决定，如果超过该大小会将排序结果放在临时文件中，最后进行统一合并，此时会增加 Sort_merge_passes 的值
Table_locks.* 表锁相关统计
mysql> show global status like “Table_locks%”;
±----------------------±--------+
| Variable_name | Value |
±----------------------±--------+
| Table_locks_immediate | 4270707 |
| Table_locks_waited | 0 |
±----------------------±--------+
2 行于数据集 (0.28 秒)
- Table_locks_immediate：这个表示有多少锁当查询到来时立即被授权
- Table_locks_waited：这个表示有多少锁当查询到来时需要等待，如果该值较大说明锁争用比较严重，因为 InnoDb 支持行级锁，该值一般都很小
命令二：SHOW ENGINE INNODB STATUS\G
show engine innodb status 输出了关于 InnoDB 一些平均值的统计信息，对于数据的采样计算的时间间隔也不总是相同的，里面提供了大多数你要想要的统计信息。
该命令输出是一个单独的字符串，没有行和列，分为很多小段，所以加 \G 参看更为方便，每一段都对应了 InnoDB 存储引擎不同部分的信息，
头部信息
– 表示当前输出结果是对过去某个时间范围内 InnoDB 存储引擎的状态的统计
Per second averages calculated from the last 60 seconds
BACKGROUND THREAD
InnoDB 存储引擎的核心操作大部分都集中在 Mater Thread 后台线程中，该状态显示了后台线程状态信息，Master Thread 的主要工作：
- 主循环(loop)主要以每一秒和每十秒的频率执行刷新日志缓存，合并插入缓存，刷新脏页缓存，删除无用 undo 页等操作
- 如果当前没有用户活动，则进入后台循环流程(backgroud loop)，主要执行删除无用的 undo 页，合并插入缓存
- 如果没有什么事情可以做了，便进入了暂停循环(suspend loop)，等待事件循环唤起
-----------------
BACKGROUND THREADsrv_active 为每秒的循环次数，srv_idle 为每 10 秒的的循环次数，srv_shutdown 为停止的循环，通常为 0
如果每秒循环次数少，每 10 秒次数多，证明当前负载很低；如果每秒循环次数多，每 10 秒次数少，远大于10：1，证明当前负载很高
srv_master_thread loops: 2818842 srv_active, 0 srv_shutdown, 411 srv_idle
日志缓冲刷盘次数
srv_master_thread log flush and writes: 2819194
SEMAPHORES（信号量）
主要包含了两种数据：事件计数器以及可选的当前等待线程列表。首先了解一下 InnoDB 如何两步获得锁过程：
- 首先线程试图获得一个锁，如果此锁被它人占用。它就会执行所谓的 spin wait，轮询查看锁是否释放
- 如果在循环过程中，一直未得到锁释放的信息，则其转入 os wait，即线程进入挂起(suspended)状态
- 直到锁被释放后，通过信号(singal)唤醒线程
spin wait 的消耗远小于 os waits，spin wait 利用 cpu 的空闲时间，检查锁的状态，os Wait 从 CPU 内核中换出当前执行线程以供其它线程使用。所以应尽量减少 os waits，可以通过 innodb_sync_spin_loops 参数来平衡 spin wait 和 os wait。
----------
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 293642477 # 表示 InnoDB 产生了多少次操作系统的等待 OS WAIT ARRAY INFO: signal count 117881542 # 表示进入 OS WAIT 的线程被唤醒次数 RW-shared spins 0, rounds 318628772, OS waits 205885745 # 共享锁 RW-excl spins 0, rounds 2120303681, OS waits 66818882 # 排他锁 RW-sx spins 16623848, rounds 479205501, OS waits 15180111 # 共享排他锁 Spin rounds per wait: 318628772.00 RW-shared, 2120303681.00 RW-excl, 28.83 RW-sx
LATEST FOREIGN KEY ERROR
该错误一般不会出现，除非你服务器上外键错误，比如违反外键约束去更新，删除数据，或者去修改外键的类型导致类型不匹配，都会报这个错， show engine innodb status 在每次有新错误时，该信息都会被重写
LATEST DETECTED DEADLOCK
记录最近一次死锁信息，只有产生过死锁才会有记录。该信息对于查看到底是什么导致死锁非常有用
通过下面的信息我们可以知道死锁的发生时间，死锁里每个事务的状态和信息
------------------------
LATEST DETECTED DEADLOCK
190425 18:00:13
*** (1) TRANSACTION:
TRANSACTION 231E7C5DF, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 1346996, OS thread handle 0x7fd968454700, query id 760545285 10.10.x.x app_user updating
DELETE
FROM db_0.table_0
WHERE ORDER_ID IN ( 456787464 , 456787465 )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 6064 n bits 824 index orderId_index of table db_0.table_0 trx id 231E7C5DF lock_mode X waiting
Record lock, heap no 180 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 8; hex 80000015eb6a1041; asc j A;;
1: len 8; hex 800000002018fce2; asc ;;
*** (2) TRANSACTION:
TRANSACTION 231E7C5DD, ACTIVE 0 sec starting index read, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 4 row lock(s)
MySQL thread id 1348165, OS thread handle 0x7fd96669f700, query id 760545283 10.10.x.x app_user updating
DELETE
FROM db_0.table_0
WHERE ORDER_ID IN ( 456787464 , 456787465 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5 page no 6064 n bits 824 index orderId_index of table db_0.table_0 trx id 231E7C5DD lock_mode X locks rec but not gap
Record lock, heap no 180 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 8; hex 80000015eb6a1041; asc j A;;
1: len 8; hex 800000002018fce2; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 6064 n bits 824 index orderId_index of table db_0.table_0 trx id 231E7C5DD lock_mode X waiting
Record lock, heap no 180 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 8; hex 80000015eb6a1041; asc j A;;
1: len 8; hex 800000002018fce2; asc ;;
*** WE ROLL BACK TRANSACTION (1) # 这个表示那个事务被选中成为死锁的牺牲品
TRANSACTIONS
包含了 InnoDB 事务(transaction)的统计信息。
------------
TRANSACTIONS
当前的事务 ID，这是一个系统变量，每创建一个新事务会加 1
Trx id counter 319435931
Purge done for trx’s 这个表示清除旧的 MVCC 行时所用到的事务 ID，和当前事务 ID 相比较，可以知道有多少老版本数据未被清理。
undo n:o 表示正在使用的 undo 日志编号，如果是 0 的话，表明处于空闲状态
Purge done for trx’s n:o < 319435931 undo n:o < 0 state: running but idle
这个表示 InnoDB undo 日志文件页面的个数，如果事务执行了更新并提交，这个数字会增加，当清理进程移除旧版本数据时，该值会减少
History list length 1631
FILE I/O
在 InnoDB 中大量使用了 AIO（Async IO）来处理 IO 请求，IO Thread 主要是负责这些 IO 请求的回调处理，通过调用 fsync() 函数协调内存与磁盘之间的数据。
主要包括以下几种 IO 线程：
- insert buffer thread： 插入合并缓存线程
- log thread： 异步刷新事务日志线程
- read thread： 读线程，通过参数 innodb_read_io_threads 控制个数，默认是 4
- write thread： 写线程，主要用于刷新脏页缓存，通过参数 innodb_write_io_threads 控制个数，默认是 4
- purge thread： 回收已经使用并分配的 undo 页，从 Master Thread 线程中分离出来，提高 CPU 的利用率。innodb 1.2 版本以后，可以通过参数 innodb_purge_threads 来设置 Purge 线程的个数
- page cleaner thread： 负责脏页的刷新，innodb 1.2 版本以后从 Master Thread 线程中分离出来，提高 CPU 的利用率，减轻 Master Thread 的压力
FILE I/O 显示了辅助线程的状态以及性能计数器的状态
--------
FILE I/O
各个辅助线程的状态信息
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
下面三行显示的是每个辅助线程被挂起操作的数目，如果这些 I/O 大部分有挂起的操作，那么负载可能 I/O 受限
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o’s:, sync i/o’s:
Pending flushes (fsync) log: 0; buffer pool: 0
显示的是读，写，fsync 调用执行的次数
230490715 OS file reads, 200072148 OS file writes, 154690982 OS fsyncs
每个线程每秒的平均值
17.15 reads/s, 16384 avg bytes/read, 23.65 writes/s, 21.08 fsyncs/s
INSERT BUFFER AND ADAPTIVE HASH INDEX
- 插入缓存：
插入缓存主要用于非聚集索引的插入和更新操作，把多次插入和更新操作放在一个 Insert Buffer 对象里，再以一定的频率进合并更新，提高性能。
- 自适应哈希索引
InnoDB 会监控对表上各个索引页的查询，如果观察到通过哈希索引可以带来性能提升，则自动建立哈希索引，通过参数 innodb_adaptive_hash_index 来禁用或启动
INSERT BUFFER AND ADAPTIVE HASH INDEX 显示了插入缓存和自适应哈希索引的使用情况。
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf 表示已经合并页的数量，free list len 表示空闲列表长度，seg size 表示 Insert Buffer 的大小，merges 表示合并次数，
Ibuf: size 1, free list len 80, seg size 82, 2639224 merges
分别查看 Insert Buffer, Delete Buffer, Purge Buffer 的次数
merged operations:
insert 4178472, delete mark 151972, delete 60065
不需要合并的次数
discarded operations:
insert 0, delete mark 0, delete 0
自适应 hash 索引的大小
Hash table size 276671, node heap has 5 buffer(s)
Hash table size 276671, node heap has 21 buffer(s)
Hash table size 276671, node heap has 56 buffer(s)
Hash table size 276671, node heap has 210 buffer(s)
Hash table size 276671, node heap has 8 buffer(s)
Hash table size 276671, node heap has 111 buffer(s)
Hash table size 276671, node heap has 9 buffer(s)
Hash table size 276671, node heap has 33 buffer(s)
通过 hash 索引查询每秒个数，无法通过 hash 索引查询每秒的个数
4798.09 hash searches/s, 600.67 non-hash searches/s
LOG
这部分内容显示的是关于 InnoDB 重做日志的统计，以下三种情况重做日志都会被刷新到磁盘
- Master Thread 会每秒进行刷新
- 每个事务提交时会刷新
- 重做日志缓存空间小于 1/2 时会强制刷新
—
LOG
Log sequence number 197081867058 # 当前日志 LSN 序号
Log flushed up to 197081867058 # 日志刷新到磁盘的 LSN 序号
Pages flushed up to 196985565734 # 已经刷新到磁盘页的 LSN 序号
Last checkpoint at 196985468367 # 上一次 Checkpoint 的 LSN 序号
统计日志的总量及每秒日志的量
0 pending log flushes, 0 pending chkp writes 142876358 log i/o’s done, 18.32 log i/o’s/second
BUFFER POOL AND MEMORY
这部分信息显示了关于 InnoDB 缓存池及其如何使用内存的统计，为了提高数据库的性能，引入缓存池的概念，通过参数 innodb_buffer_pool_size 可以设置缓存池的大小，参数 innodb_buffer_pool_instances 可以设置缓存池的实例个数。缓存池主要用于存储以下内容：索引页，数据页，undo 页，插入缓存(insert buffer)，自适应哈希索引(adaptive hash index)，锁信息，数据字典信息等信息。
----------------------
BUFFER POOL AND MEMORY
Total large memory allocated 2198863872 # InnoDB 分配的总内存
Dictionary memory allocated 7884277 # InnoDB 数据字典分配的内存数，
Buffer pool size 131064 # innodb_buffer_pool 页的数量
Free buffers 1024 # LRU 列表中空闲页的数量
Database pages 129587 # LRU 列表中非空闲页的数量
Old database pages 47815 # LRU 列表中 old 页的数量，new 和 old 页的分界通过参数 innodb_old_blocks_pct 设置
Modified db pages 20916 # 脏页的数量
Pending reads 0 # 挂起读的数量
Pending writes: LRU 0, flush list 0, single page 0 # 挂起的写的数量
Pages made young 125019609, not young 11734595709
6.70 youngs/s, 109.36 non-youngs/s
read 是指从磁盘读到缓存池中，written 是从缓存池写入磁盘，created 是指在缓存池中分配但没有从数据文件中读取的页
Pages read 230491590, created 15808941, written 53911882
17.15 reads/s, 1.20 creates/s, 4.28 writes/s
缓存命中率
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 4 / 1000
ROW OPERATIONS
显示了 row 操作及其他一些统计信息
--------------
ROW OPERATIONS
queries，表示 innodb 内核中有多少个线程，队列中有多少个线程
0 queries inside InnoDB, 0 queries in queue
表示有多少个读视图被打开，一个读视图包含从事务开始数据库内容的 MVCC 快照
0 read views open inside InnoDB
内核主线程的状态，大部分情况下处于"sleeping"，主线程主要工作有 making checkpoint, flushing log, flushing buffer poll pages 等
Process ID=51, Main thread ID=140023262725888, state: sleeping
表示多少行被插入，更新和删除，读取及每秒读取信息，可用于监控
Number of rows inserted 637410366, updated 210696023, deleted 335066, read 311734809045
60.43 inserts/s, 9.95 updates/s, 0.00 deletes/s, 48640.59 reads/s
命令三：SHOW PROCESSLIST
显示了当前连接到 MYSQL 的连接或者线程的状态，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独为这个用户赋予了 PROCESS 权限。show processlist 的结果其实来自于 information_schema.processlist 表，所以我们通过查询 information_schema.processlist 表可以获取对于排查性能问题有用的信息，比如哪个客户端连接信息最多，哪些线程执行时间最长等信息。
SHOW PROCESSLIST\G;
*************************** 238. 行 ***************************
Id : 666748
User : youdata
Host : 10.255.0.2:56058
db : youdata
Command : Sleep
Time : 127
ACK_WAIT_TIME: 0
State :
Info :
*************************** 239. 行 ***************************
Id : 666761
User : youdata
Host : 10.255.0.2:60619
db : NULL
Command : Query
Time : 0
ACK_WAIT_TIME: 0
State : starting
Info : starting
239 行于数据集 (0.51 秒)
下面看一下每个字段的含义：
- Id： 这个是线程的唯一标志，当遇到问题时可以通过 kill 加上这个 Id 值将这个线程杀掉
- User： 表示启动线程的用户
- Host： 记录了发送请求的客户端的 IP 和端口
- DB： 执行的数据库
- Command： 正在执行的命令，比如 Query，Create DB，Quit，Sleep 等
- Time：该线程处于当前状态的时间
- State：线程的状态,有很多种情况，下面只列举了部分：
- Checking table: 正在检查数据表
- Closing tables：正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表
- Locked：被其它查询锁住了
- Sending data：正在处理 Select 查询的记录同时把数据发动到客户端
- Sorting for group：正在为 group by 操作做排序
- Updating： 正在寻找匹配的记录来修改他们
…
- Info：一般记录的是线程执行的语句。默认只显示前 100 个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist
命令四：SHOW PROFILE
- mysql 5.1 版本开始支持 SHOW PROFILE
- SHOW PROFILE 可以高精度的记录每个查询语句在运行过程中各个操作的执行时间
- 开启 SET profiling = ON，默认关闭
- SHOW PROFILES 可以列出最近 N 条 SQL 的执行时间，N 默认是 15，可以通过参数 profiling_history_size 进行控制
mysql> show profiles;
±---------±-----------±--------------±---------------±--------------------------------------------------------------------------+
| Query_ID | Duration | Logical_reads | Physical_reads | Query |
±---------±-----------±--------------±---------------±--------------------------------------------------------------------------+
| 1 | 0.00019325 | 0 | 0 | select @@version_comment limit 1 |
| 2 | 0.00043125 | 0 | 0 | SELECT DATABASE() |
| 3 | 0.00058600 | 0 | 0 | show databases |
| 4 | 0.00060725 | 0 | 0 | show tables |
| 5 | 0.00013125 | 0 | 0 | show global profiles |
| 6 | 0.00014375 | 0 | 0 | show global profiles |
| 7 | 0.00073550 | 0 | 0 | select * from bigviz_user where email like “%hzzhang%” order by nick desc |
| 8 | 0.01025300 | 0 | 0 | select count(*) from new_component |
±---------±-----------±--------------±---------------±--------------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)- SHOW PROFILE 可以列出最近一条语句的执行详细信息。如果指定 FOR QUERY n 子句时，可以列出最近 N 条中某条语句的执行详细信息, 其中 n 表示上面显示的 Query_ID
mysql> show for query 7;
±---------------------±---------+
| Status | Duration |
±---------------------±---------+
| starting | 0.000158 |
| checking permissions | 0.000013 |
| Opening tables | 0.000028 | 打开表
| init | 0.000073 |
| System lock | 0.000018 | 加锁
| optimizing | 0.000015 |
| statistics | 0.000026 |
| preparing | 0.000017 |
| Sorting result | 0.000010 | 排序
| executing | 0.000005 |
| Sending data | 0.000029 | 发送数据
| Creating sort index | 0.000223 |
| end | 0.000008 |
| query end | 0.000011 |
| closing tables | 0.000039 | 关闭表
| freeing items | 0.000039 |
| cleaning up | 0.000025 |
±---------------------±---------+
17 rows in set, 1 warning (0.00 sec)- SHOW PROFILE 只对当前会话生效，所以我们一般是在命令行工具里开启然后执行自己想要分析的SQL，再通过 show profile 查看性能。
参考文献
- mysql深入学习笔记及实战指南-连接优化
- MySQL的 select 和 sort 变量
- 一条命令解读InnoDB存储引擎—show engine innodb status
- InnoDB 存储引擎的关键特性学习
- mysql: show processlist 详解
- mysql 官方文档
