Mysql常用SQL合集

in 笔记 with 0 comment

查看数据库占用磁盘大小

语句如下

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 |
+--------------------+------------+------------+
| shdx               | 1042.38 MB | 258.97MB   |
| mysql              | 0.52 MB    | 0.08MB     |
| information_schema | 0.00 MB    | 0.00MB     |
+--------------------+------------+------------+
3 rows in set
 
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;

执行结果如下,截取部分

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 = 'shdx'
group by TABLE_NAME
order by data_length desc;
+--------------------------------+-----------+------------+
| TABLE_NAME                     | data_size | index_size |
+--------------------------------+-----------+------------+
| tab_automatch_log              | 499.08 MB | 95.03 MB   |
| tab_fundlog201505              | 101.96 MB | 19.13 MB   |
| tab_online                     | 63.49 MB  | 14.32 MB   |
| tab_game_record201505          | 56.97 MB  | 30.93 MB   |
| tab_user                       | 52.17 MB  | 12.31 MB   |
| dc_user_action_active          | 39.94 MB  | 0.00 MB    |
| tab_gametime_day               | 37.22 MB  | 21.50 MB   |
| tab_fundlog                    | 35.12 MB  | 6.45 MB    |
| tab_game_record                | 19.85 MB  | 10.56 MB   |
| tab_feeaccount                 | 19.17 MB  | 10.75 MB   |
| tab_userinfo                   | 18.78 MB  | 6.33 MB    |

设置Mysql允许远程访问

B13F5CEAEF08400F8B57068E4D217291.png