查询所有数据库占用磁盘空间大小的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
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/6729.html