MySQL查看某库表大小及锁表情况
2024-10-30 11:57:20
查询所有数据库占用磁盘空间大小的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
最新文章
- 4.6 .net core依赖注入的封装
- http 状态码含义
- Oracle VM Virtual
- 歐洲國家拓展其移動和IT服務業務
- git -- 如何解决冲突
- Python 输出文字带颜色
- POJ 1811 Prime Test(Miller-Rabin &; Pollard-rho素数测试)
- android学习(2) 多线程的理解
- 面向对象程序设计-C++_课时13初始化列表
- Asp.Net MVC页面静态化功能实现一:利用IHttpModule,摒弃ResultFilter
- python第四天
- 51nod_1122:机器人走方格 V4 (矩阵快速幂)
- HDFS知识点总结
- C++ 函数模板&类模板
- 开源配置管理平台-Apollo
- 在MyEclipse中将Java Project转换成Web Project
- ubuntu下安装mysql及常用操作
- Java 注解 (Annotation)
- Git最常用的命令 总结
- Android 保存和恢复activity的状态数据
热门文章
- 【Windows】windows核心编程整理(下)
- sql随机查询数据order by newid()
- canvas的图片绘制案例
- 使用Python实现Map Reduce程序
- 记一次金士顿DT100 G3 32G修复
- 中南大学oj:1336: Interesting Calculator(广搜经典题目)
- 慢速HTTP拒接服务攻击(DoS)复现
- Spring boot 报错 Unable to start EmbeddedWebApplicationContext due to missing EmbeddedServletContainerFactory bean.
- Android Retrofit2 网路编程
- win7系统损坏无法进入系统(dsark.sys文件损坏)(未测试过)