# Day16 MySQL 深度巡检
# information_schema
- 查看数据元数据视图
# 重点关注
information_schema.tables
information_schema.processlist
# processlist
-- 两种调用方式
mysql> show processlist;
mysql> select * from information_schema.processlist;
+----+-----------------+-----------+--------------------+---------+-------+------------------------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+-----------------+-----------+--------------------+---------+-------+------------------------+----------------------------------------------+
| 52 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.processlist |
| 5 | event_scheduler | localhost | NULL | Daemon | 88245 | Waiting on empty queue | NULL |
+----+-----------------+-----------+--------------------+---------+-------+------------------------+----------------------------------------------+
- 连接线程ID 关联信息
1. mysql> desc performance_schema.threads;
+---------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+-------+
| THREAD_ID | bigint unsigned | NO | PRI | NULL | |
| PROCESSLIST_ID | bigint unsigned | YES | MUL | NULL | |
| THREAD_OS_ID | bigint unsigned | YES | MUL | NULL | |
+---------------------+------------------+------+-----+---------+-------+
2. mysql> select * from performance_schema.events_statements_history where thread_id=53;
3. mysql> select * from performance_schema.events_statements_current where thread_id=53;
4. 灵活 kill 11;
- USER
1. event_scheduler
2. root
3. 主从复制用户
- DB 当前执行语句对应的数据库，use db，否则为NULL
- COMMAND 显示正在做的事情
Query：该线程正在执行一个语句
Sleep：线程正在等待客户端向其发送新命令
- TIME 连接时间
- STATE **
Sending data 结合 TIME，如果时间很长，如大表查询
Waiting xxx 相关，如有锁
- INFO 包含执行语句的SQL前100字符，全看可查show full processlist;