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

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

语句如下:

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;

执行结果如下:

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语句：

执行语句如下

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为数据库名

执行结果如下，截取部分

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查看锁表情况

show status like 'Table%';

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

show OPEN TABLES where In_use > 0;　　

3.查询进程

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

4.查看未提交事务:

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

5.查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

6.查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

　　

参考文档：

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