mysql看数据库大小m_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