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_size 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_size desc;
+--------------------+-----------+------------+
| TABLE_SCHEMA       | data_size | index_size |
+--------------------+-----------+------------+
| db0                | 36.57 MB  | 0.00MB     |
| mysql              | 2.22 MB   | 0.22MB     |
| information_schema | 0.15 MB   | 0.00MB     |
| sys                | 0.01 MB   | 0.00MB     |
| performance_schema | 0.00 MB   | 0.00MB     |
+--------------------+-----------+------------+
5 rows in set (0.29 sec)

mysql> 

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

执行语句如下

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 = '数据库名称' order by data_length desc;

以上语句测试有效，注意替换为自己需要查询的数据库名

执行结果如下，截取部分

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 = 'db0' order by data_length desc;
+------------+-----------+------------+
| TABLE_NAME | data_size | index_size |
+------------+-----------+------------+
| x190826    | 9.51 MB   | 0.00 MB    |
| x1500      | 7.51 MB   | 0.00 MB    |
| x190829    | 7.51 MB   | 0.00 MB    |
| x190827    | 6.51 MB   | 0.00 MB    |
| x190828    | 5.51 MB   | 0.00 MB    |
+------------+-----------+------------+
5 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