mysql数据库占用空间查询

最新推荐文章于 2024-03-20 09:37:27 发布
最新推荐文章于 2024-03-20 09:37:27 发布
阅读量313 收藏
点赞数
分类专栏： mysql 文章标签： mysql zabbix
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fyh360345192/article/details/84622586
版权

查看实例所有库占用空间

mysql> select table_schema, sum(data_length+index_length)/1024/1024 as total_mb,   
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb,   
count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;  
+--------------------+--------------+-------------+-------------+--------+------------+
| table_schema       | total_mb     | data_mb     | index_mb    | tables | today      |
+--------------------+--------------+-------------+-------------+--------+------------+
| zabbix             | 102.12500000 | 67.95312500 | 34.17187500 |    129 | 2018-11-29 |
| mysql              |   0.67597771 |  0.57539177 |  0.10058594 |     24 | 2018-11-29 |
| information_schema |   0.00878906 |  0.00000000 |  0.00878906 |     40 | 2018-11-29 |
| performance_schema |   0.00000000 |  0.00000000 |  0.00000000 |     17 | 2018-11-29 |
+--------------------+--------------+-------------+-------------+--------+------------+
4 rows in set (0.08 sec)

查看单个库占用空间，如下查询zabbix库占用

mysql> select concat(truncate(sum(data_length)/1024/1024,2),'mb') as data_size,  
concat(truncate(sum(max_data_length)/1024/1024,2),'mb') as max_data_size,   
concat(truncate(sum(data_free)/1024/1024,2),'mb') as data_free,  
concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size 
 from information_schema.tables where table_schema = 'zabbix';  
+-----------+---------------+------------+------------+
| data_size | max_data_size | data_free  | index_size |
+-----------+---------------+------------+------------+
| 68.95mb   | 0.00mb        | 21156.00mb | 34.17mb    |
+-----------+---------------+------------+------------+
1 row in set (0.06 sec)

查看zabbix库下所有表占用空间

mysql> select table_name, (data_length/1024/1024) as data_mb , (index_length/1024/1024)   
as index_mb, ((data_length+index_length)/1024/1024) as all_mb, table_rows   
from information_schema.tables where table_schema = 'zabbix';  
+----------------------------+-------------+-------------+-------------+------------+
| table_name                 | data_mb     | index_mb    | all_mb      | table_rows |
+----------------------------+-------------+-------------+-------------+------------+
| acknowledges               |  0.01562500 |  0.04687500 |  0.06250000 |         20 |
| act_dict                   |  0.01562500 |  0.00000000 |  0.01562500 |          8 |
| actions                    |  0.01562500 |  0.03125000 |  0.04687500 |          5 |
| alerts                     |  1.51562500 |  0.39062500 |  1.90625000 |       1486 |
| application_discovery      |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| application_prototype      |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| application_template       |  0.01562500 |  0.03125000 |  0.04687500 |         10 |
| applications               |  0.01562500 |  0.01562500 |  0.03125000 |        102 |
| auditlog                   |  0.28125000 |  0.17187500 |  0.45312500 |       2542 |
| auditlog_details           |  0.01562500 |  0.01562500 |  0.03125000 |          1 |
| autoreg_host               |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| conditions                 |  0.01562500 |  0.01562500 |  0.03125000 |          8 |
| config                     |  0.01562500 |  0.03125000 |  0.04687500 |          1 |
| corr_condition             |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| corr_condition_group       |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| corr_condition_tag         |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| corr_condition_tagpair     |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| corr_condition_tagvalue    |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| corr_operation             |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| correlation                |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| dbversion                  |  0.01562500 |  0.00000000 |  0.01562500 |          1 |
| dchecks                    |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| dhosts                     |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| drules                     |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| dservices                  |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| escalations                |  0.01562500 |  0.01562500 |  0.03125000 |          1 |
| event_recovery             |  0.20312500 |  0.23437500 |  0.43750000 |       4814 |
| event_tag                  |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| events                     |  1.51562500 |  0.65625000 |  2.17187500 |      10141 |
| expressions                |  0.01562500 |  0.01562500 |  0.03125000 |          6 |
| functions                  |  0.14062500 |  0.17187500 |  0.31250000 |       1435 |
| globalmacro                |  0.01562500 |  0.01562500 |  0.03125000 |          1 |
| globalvars                 |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| graph_discovery            |  0.01562500 |  0.01562500 |  0.03125000 |          6 |
| graph_theme                |  0.01562500 |  0.01562500 |  0.03125000 |          2 |
| graphs                     |  0.01562500 |  0.06250000 |  0.07812500 |         34 |
| graphs_items               |  0.01562500 |  0.03125000 |  0.04687500 |         67 |
| group_discovery            |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| group_prototype            |  0.01562500 |  0.04687500 |  0.06250000 |          0 |
| groups                     |  0.01562500 |  0.01562500 |  0.03125000 |         16 |
| history                    | 30.56250000 | 16.54687500 | 47.10937500 |     568598 |
| history_log                |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| history_str                |  2.51562500 |  1.51562500 |  4.03125000 |      21626 |
| history_text               |  3.51562500 |  0.21875000 |  3.73437500 |       6870 |
| history_uint               | 20.54687500 | 11.14062500 | 31.68750000 |     391835 |
| host_discovery             |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| host_inventory             |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| hostmacro                  |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| hosts                      |  0.01562500 |  0.09375000 |  0.10937500 |         22 |
| hosts_groups               |  0.01562500 |  0.03125000 |  0.04687500 |         23 |
| hosts_templates            |  0.01562500 |  0.03125000 |  0.04687500 |          2 |
| housekeeper                |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| httpstep                   |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| httpstepitem               |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| httptest                   |  0.01562500 |  0.06250000 |  0.07812500 |          0 |
| httptestitem               |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| icon_map                   |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| icon_mapping               |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| ids                        |  0.01562500 |  0.00000000 |  0.01562500 |         35 |
| images                     |  1.51562500 |  0.01562500 |  1.53125000 |        219 |
| interface                  |  0.01562500 |  0.03125000 |  0.04687500 |          1 |
| interface_discovery        |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| item_application_prototype |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| item_condition             |  0.01562500 |  0.01562500 |  0.03125000 |         14 |
| item_discovery             |  0.06250000 |  0.03125000 |  0.09375000 |        123 |
| items                      |  0.12500000 |  0.07812500 |  0.20312500 |        423 |
| items_applications         |  0.07812500 |  0.06250000 |  0.14062500 |        328 |
| maintenances               |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| maintenances_groups        |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| maintenances_hosts         |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| maintenances_windows       |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| mappings                   |  0.01562500 |  0.01562500 |  0.03125000 |        143 |
| media                      |  0.01562500 |  0.03125000 |  0.04687500 |          1 |
| media_type                 |  0.01562500 |  0.01562500 |  0.03125000 |          4 |
| opcommand                  |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| opcommand_grp              |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| opcommand_hst              |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| opconditions               |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| operations                 |  0.01562500 |  0.01562500 |  0.03125000 |          8 |
| opgroup                    |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| opinventory                |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| opmessage                  |  0.01562500 |  0.01562500 |  0.03125000 |          8 |
| opmessage_grp              |  0.01562500 |  0.03125000 |  0.04687500 |          3 |
| opmessage_usr              |  0.01562500 |  0.03125000 |  0.04687500 |          2 |
| optemplate                 |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| problem                    |  0.07812500 |  0.07812500 |  0.15625000 |        932 |
| problem_tag                |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| profiles                   |  0.04687500 |  0.03125000 |  0.07812500 |        215 |
| proxy_autoreg_host         |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| proxy_dhistory             |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| proxy_history              |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| regexps                    |  0.01562500 |  0.01562500 |  0.03125000 |          5 |
| res_dict                   |  0.01562500 |  0.00000000 |  0.01562500 |         32 |
| rights                     |  0.01562500 |  0.03125000 |  0.04687500 |          6 |
| screen_user                |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| screen_usrgrp              |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| screens                    |  0.01562500 |  0.03125000 |  0.04687500 |          1 |
| screens_items              |  0.01562500 |  0.01562500 |  0.03125000 |          1 |
| scripts                    |  0.01562500 |  0.04687500 |  0.06250000 |          3 |
| service_alarms             |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| services                   |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| services_links             |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| services_times             |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| sessions                   |  0.21875000 |  0.14062500 |  0.35937500 |       2048 |
| slides                     |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| slideshow_user             |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| slideshow_usrgrp           |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| slideshows                 |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| sysmap_element_url         |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| sysmap_url                 |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| sysmap_user                |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| sysmap_usrgrp              |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| sysmaps                    |  0.01562500 |  0.06250000 |  0.07812500 |          1 |
| sysmaps_elements           |  0.01562500 |  0.07812500 |  0.09375000 |          1 |
| sysmaps_link_triggers      |  0.01562500 |  0.03125000 |  0.04687500 |          0 |
| sysmaps_links              |  0.01562500 |  0.04687500 |  0.06250000 |          0 |
| task                       |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| task_close_problem         |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| timeperiods                |  0.01562500 |  0.00000000 |  0.01562500 |          0 |
| trends                     |  2.51562500 |  0.00000000 |  2.51562500 |      20892 |
| trends_uint                |  1.51562500 |  0.00000000 |  1.51562500 |       2951 |
| trigger_depends            |  0.01562500 |  0.03125000 |  0.04687500 |         10 |
| trigger_discovery          |  0.01562500 |  0.01562500 |  0.03125000 |         16 |
| trigger_tag                |  0.01562500 |  0.01562500 |  0.03125000 |          0 |
| triggers                   |  0.28125000 |  0.07812500 |  0.35937500 |        717 |
| users                      |  0.01562500 |  0.01562500 |  0.03125000 |          3 |
| users_groups               |  0.01562500 |  0.03125000 |  0.04687500 |          1 |
| usrgrp                     |  0.01562500 |  0.01562500 |  0.03125000 |          5 |
| valuemaps                  |  0.01562500 |  0.01562500 |  0.03125000 |         17 |
+----------------------------+-------------+-------------+-------------+------------+
129 rows in set (0.08 sec)

查询单个表状态，如查询zabbix库trends表

mysql> show table status from zabbix where name = 'trends' \G                                  
*************************** 1. row ***************************
           Name: trends
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 17558
 Avg_row_length: 150
    Data_length: 2637824
Max_data_length: 0
   Index_length: 0
      Data_free: 171966464
 Auto_increment: NULL
    Create_time: 2018-09-12 19:41:43
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

 

F_yinhai
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 查看大表,查看个表占用大小，以及查看一周内,三个月内,半年内,一年新增的数据
dayulxl的博客
11-26 1042
mysql 版本号5.7.9-log -- 查看 各库 占用 大小 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 dat
MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句
12-15
查询所有数据库占用磁盘空间大小的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_sizefrom information_schema.tablesgroup by TABLE_SCHEMAorder by data_length desc; 查询单个库中所有表磁盘占用大小的SQL语句： 代码如下:select TABL
排查--[MySQL8.X 所占内存越来越大] 思路
最新发布
Mr.ACO的专栏
03-20 836
MYSQL 内存性能问题 优化 思路
MySQL 查询 表 容量大小 索引大小
Good luck is another name for tenacity of purpose.
08-13 1000
## 所有数据库 select table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema....
如何查看mysql数据库中各个表的大小
热门推荐
伏虎游侠的博客
08-26 2万+
        由于数据太大了。所以MYSQL需要瘦身，那前提就是需要知道每个表占用的空间大小。 首先打开指定的数据库： use information_schema;   如果想看指定数据库中的数据表，可以用如下语句： select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES wh...
查看mysql 占用空间
微阳潋潋落寒汀的博客
05-16 578
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
linux 系统版本信息命令查询大全
哈尼的博客
03-09 620
linux 系统版本信息命令查询大全查看命令参考 查看命令 uname -a # 查看内核/操作系统/CPU信息 cat /proc/cpuinfo # 查看CPU信息 hostname # 查看计算机名 du -sh <目录名> # 查看指定目录的大小 fdisk -l （观察硬盘实体使用情况，也可对硬盘分区） sblk （查看磁盘信息 - 列出所有可用块设备的信息 查看逻辑C...
MySQL数据库查看数据表占用空间大小和记录数的方法
09-10
主要介绍了MySQL数据库查看数据表占用空间大小和记录数的方法,如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话,可以打开MySQL的information_schema 数据库查询,本文就讲解查询方法,需要的朋友可以参考下
mysql表占用空间大小脚本
01-02
此脚本可以查看mysql数据库中各个表占用空间大小，排序，以便清理数据之用。
mysql数据库高级，mysql优化.rar
07-28
一、规范化数据库： 规范化是一种将数据组织成逻辑上一致和最小化冗余的过程。通过规范化数据库结构，可以提高数据...使用最小化的数据类型：选择最小化的数据类型来存储数据，以减少存储空间的占用和提高查询效率。
MySQL如何查看数据库、表占用磁盘大小
Ddd_victory的博客
11-14 8413
MySQL如何查看数据库、表占用磁盘大小，简单搞定
mysql查询表的数据大小
n_meng的博客
01-10 1634
通过sql语句查询整个数据库的容量，或是单独查看表所占容量。 　　1、要查询表所占的容量，就是把表的数据和索引加起来就可以了 select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables where table_schema='数据库名称'; 上面获取的结果是以字节为单位的，可以通过%1024在%
MySQL 占用空间一键查询实践
qq_36663951的博客
07-13 237
经常会遇到DB满的情况 特别是大家共用DB的时候 这时候需要找到，哪个DB比较大，哪个表比较大，默认的方法你得一个一个的查询， 毕竟这里你没法使用 du -sh 这种文件系统级别的命令 但这肯定不是偷懒程序员爱干的事。 原理很简单： MySQL在系统库中记录了这些数据，只需要使用一条SQL命令组合起来就好 | TABLE_SCHEMA | varchar(64) | NO | | | |
mysql查看库、表占用存储空间大小
weixin_30426879的博客
08-07 376
http://blog.csdn.net/bzfys/article/details/55252962 1. 查看该数据库实例下所有库大小，得到的结果是以MB为单位 1 <span class="keylink">mysql</span>> select table_schema,sum(data_length)/1024/1024 as data_...
MySQL空间函数应用_计算mysql数据库占用空间(使用容量)的函数mysqlUse.fn.php
weixin_33729288的博客
02-28 162
说明：计算mysql中某数据库的占用空间。下载：暂不提供mysqlUse.fn.php的下载，请参照下面的代码自己创建。^_^代码：// 说明：数据库使用量// 作者：upall// 日期：10:22 2010年11月10日 星期三$db="upall_blog"; // 需要返回使用空间的数据库$host="localhost";$user="root";$pwd="db:root@blog.u...
mysql查询数据库储存数据的占用容量大小
qq_42900469的博客
06-05 894
mysql查询数据库占用容量大小
MySQL中查询磁盘空间
稻草人技术博客
01-19 4842
查询所有数据库占用磁盘空间大小的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.
mysql统计存储空间_MySQL状态（存储空间和数据量）查询命令
weixin_36324426的博客
01-18 1229
本文记录一些检测MySQL性能，配置的命令status命令status命令可以列出MySQL数据库的基本参数。比如编码信息，MySQL版本，查询次数等等。mysql> status--------------mysql Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64) using readline 5.1Connection i...
mysql空间查询方法_mysql空间查询
weixin_33998219的博客
02-28 1247
今天要做一个根据经纬度获取位置信息的功能(百度地图也提供了相应接口，由于请求频率限制，没有使用)，于是用到了mysql的空间查询：1.创建表：CREATE TABLE `xiaoqu` (`ID` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL,`code` varchar(255) DEFAULT NULL,`type` int(11) DEF...
如何查看MYSQL数据库占用的硬盘空间
09-18
你可以使用以下SQL查询语句来查看MYSQL数据库占用的硬盘空间： ``` SELECT table_schema "Database Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "Database Size in MB" FROM information_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值