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

时间：2024-05-05 21:33:44

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

语句如下:

select TABLE_SCHEMA, concat(truncate(sum(data_length)//,),' MB') as data_size, concat(truncate(sum(index_length)//,),'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)//,),' MB') as data_size, concat(truncate(sum(index_length)//,),'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  | .00MB     |

| mysql              | 2.22 MB   | .22MB     |

| information_schema | 0.15 MB   | .00MB     |

| sys                | 0.01 MB   | .00MB     |

| performance_schema | 0.00 MB   | .00MB     |

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

 rows in set (0.29 sec)

mysql>

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

执行语句如下

mysql> select TABLE_NAME, concat(truncate(data_length//,),' MB') as data_size, concat(truncate(index_length//,),' MB') as index_size from information_schema.tables where TABLE_SCHEMA = '数据库名称' order by data_length desc;

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

执行结果如下，截取部分

mysql> select TABLE_NAME, concat(truncate(data_length//,),' MB') as data_size, concat(truncate(index_length//,),' 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    |

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

 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　　

相关文章