查看mysql数据库表状态_MySQL库表状态查询

一. 查看库的各链接状态

对于一个mysql连接或者一个线程，任何时刻都有一个状态，表示其当前正在做什么。一般使用show full processlist查看。

+---------+-------------+-------------------+------+---------+------+-------+-----------------------+

| Id | User | Host | db | Command | Time | State | Info |

+---------+-------------+-------------------+------+---------+------+-------+-----------------------+

| 2547225 | operator@RR | 10.3.18.205:60253 | fb | Query | 0 | NULL | show full processlist |

| 2548100 | operator@RR | 10.4.19.83:51754 | fb | Sleep | 1 | | NULL |

| 2548101 | operator@RR | 10.4.19.83:53661 | fb | Sleep | 1 | | NULL |

| 2548102 | operator@RR | 10.4.19.83:45808 | fb | Sleep | 1 | | NULL |

| 2548103 | operator@RR | 10.4.19.83:58881 | fb | Sleep | 1 | | NULL |

| 2548104 | operator@RR | 10.4.19.83:33521 | fb | Sleep | 1 | | NULL |

+---------+-------------+-------------------+------+---------+------+-------+-----------------------+

show full processlist

二. 查看存储引擎

2.1 查看当前库支持的存储引擎

保存在information_schema.ENGINES表里。

SHOW ENGINES

+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

结果

2.2 查看某存储引擎的状态

SHOW ENGINE INNODB STATUS

=====================================

2016-01-15 10:12:44 7f4a4c4b6700 INNODB MONITOR OUTPUT

================