查询所有数据库占用磁盘空间大小的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  

最新文章

  1. 4.6 .net core依赖注入的封装
  2. http 状态码含义
  3. Oracle VM Virtual
  4. 歐洲國家拓展其移動和IT服務業務
  5. git -- 如何解决冲突
  6. Python 输出文字带颜色
  7. POJ 1811 Prime Test(Miller-Rabin & Pollard-rho素数测试)
  8. android学习(2) 多线程的理解
  9. 面向对象程序设计-C++_课时13初始化列表
  10. Asp.Net MVC页面静态化功能实现一:利用IHttpModule,摒弃ResultFilter
  11. python第四天
  12. 51nod_1122:机器人走方格 V4 (矩阵快速幂)
  13. HDFS知识点总结
  14. C++ 函数模板&类模板
  15. 开源配置管理平台-Apollo
  16. 在MyEclipse中将Java Project转换成Web Project
  17. ubuntu下安装mysql及常用操作
  18. Java 注解 (Annotation)
  19. Git最常用的命令 总结
  20. Android 保存和恢复activity的状态数据

热门文章

  1. 【Windows】windows核心编程整理(下)
  2. sql随机查询数据order by newid()
  3. canvas的图片绘制案例
  4. 使用Python实现Map Reduce程序
  5. 记一次金士顿DT100 G3 32G修复
  6. 中南大学oj:1336: Interesting Calculator(广搜经典题目)
  7. 慢速HTTP拒接服务攻击(DoS)复现
  8. Spring boot 报错 Unable to start EmbeddedWebApplicationContext due to missing EmbeddedServletContainerFactory bean.
  9. Android Retrofit2 网路编程
  10. win7系统损坏无法进入系统(dsark.sys文件损坏)(未测试过)