MySQL查看某库表大小及锁表情况

查询所有数据库占用磁盘空间大小的SQL语句：

语句如下:

1 2 3 4 5

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size, concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;

执行结果如下:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,     -> concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size     -> from information_schema.tables     -> group by TABLE_SCHEMA     -> order by data_length desc; +--------------------+-----------+------------+ | TABLE_SCHEMA       | data_size | index_size | +--------------------+-----------+------------+ | ems                | 11.82 MB  | 6.87MB     | | information_schema | 0.00 MB   | 0.00MB     | | performance_schema | 0.00 MB   | 0.00MB     | | mysql              | 2.57 MB   | 2.27MB     | +--------------------+-----------+------------+ 4 rows in set (0.01 sec)   mysql>

查询单个库中所有表磁盘占用大小的SQL语句：

执行语句如下

1 2 3 4 5

select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA = 'TestDB' group by TABLE_NAME order by data_length desc;

以上语句测试有效，注意替换以上的TestDB为数据库名

执行结果如下，截取部分

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

mysql> select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,     -> concat(truncate(index_length/1024/1024,2),' MB') as index_size     -> from information_schema.tables where TABLE_SCHEMA = 'TestDB'     -> group by TABLE_NAME     -> order by data_length desc; +----------------------+-----------+------------+ | TABLE_NAME           | data_size | index_size | +----------------------+-----------+------------+ | repay_plan           | 8.51 MB   | 5.03 MB    | | repay_case           | 1.51 MB   | 0.54 MB    | | repay_user           | 0.45 MB   | 0.26 MB    | | repay_order          | 0.43 MB   | 0.43 MB    | | repay_assign_info    | 0.43 MB   | 0.18 MB    | | repay_contact_person | 0.21 MB   | 0.20 MB    | | area                 | 0.09 MB   | 0.09 MB    | | aging                | 0.01 MB   | 0.00 MB    | | sys_user             | 0.01 MB   | 0.03 MB    | | sys_role             | 0.01 MB   | 0.01 MB    | | dictionary           | 0.01 MB   | 0.01 MB    | | allocation_log       | 0.01 MB   | 0.00 MB    | | track_attach         | 0.01 MB   | 0.00 MB    | | sys_role_auth        | 0.01 MB   | 0.04 MB    | | sys_auth             | 0.01 MB   | 0.00 MB    | | repay_track_info     | 0.01 MB   | 0.00 MB    | | repay_company        | 0.01 MB   | 0.00 MB    | +----------------------+-----------+------------+ 17 rows in set (0.00 sec)   mysql>

 

1.mysql查看锁表情况

1	show status like 'Table%';

2.查看正在被锁定的的表

1	show OPEN TABLES where In_use > 0;

3.查询进程

1 2	show processlist 查询到相对应的进程===然后 kill    id

4.查看未提交事务:

1	select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

5.查看正在锁的事务

1	SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

6.查看等待锁的事务

1	SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

　　

参考文档：

　　　　http://www.cnblogs.com/nmap/p/6714142.html?utm_source=itdadao&utm_medium=referral