Mysql查看数据库和表占用空间

待我称王封你为后i 2022-05-19 13:23 916阅读 0赞

**Mysql****查看数据库和表占用空间**

**1****、查看数据库的大小**

**select sum(DATA\_LENGTH)+sum(INDEX\_LENGTH) from information\_schema.TABLES where TABLE\_SCHEMA='****数据库名';**

结果是以字节为单位，除(1024\*1024)为M。

mysql> select sum(DATA\_LENGTH)+sum(INDEX\_LENGTH) as 'hellodb\_use\_Total(bytes)' from information\_schema.TABLES where TABLE\_SCHEMA='hellodb';

\+--------------------------+

| hellodb\_use\_Total(bytes) |

\+--------------------------+

|                   262144 |

\+--------------------------+

1 row in set (0.00 sec

**2****、****concat()****函数：联合字段**

**mysql> select concat(10,' Mb') as Lenge;**

\+-------+

| Lenge |

\+-------+

| 10 Mb |

\+-------+

1 row in set (0.00 sec)

**3****、****ROUND()****函数**

ROUND函数用于把数值字段舍入为指定的小数位数。语法如下：

**SELECT ROUND(column\_name,decimals) FROM table\_name**

**column\_name       ** **必需，要舍入的字段**

**decimals              ****必需，要返回的小数位数**

mysql> select round(10.1234,2) as '10.231';

\+--------+

| 10.231 |

\+--------+

|  10.12 |

\+--------+

1 row in set (0.00 sec)

mysql> select round(10.1234,0) as '10.231';

\+--------+

| 10.231 |

\+--------+

|     10 |

\+--------+

1 row in set (0.00 sec)

**4****、****truncate(X,D)****函数：截取字段函数**

**X****算数操作；D****截取字段，D****小于0****，将小数点前|D|****位置零；D****大于0****，截取小数点后D****位；**

**truncate()****函数用法示例**

**mysql> SELECT TRUNCATE(1.223343\*100,2);**

\+--------------------------+

| TRUNCATE(1.223343\*100,2) |

\+--------------------------+

|                   122.33 |

\+--------------------------+

1 row in set (0.00 sec)

**mysql> SELECT TRUNCATE(1.223343\*100,0);**

\+--------------------------+

| TRUNCATE(1.223343\*100,0) |

\+--------------------------+

|                      122 |

\+--------------------------+

1 row in set (0.00 sec)

**mysql> SELECT TRUNCATE(1.223343\*100,-1);**

\+---------------------------+

| TRUNCATE(1.223343\*100,-1) |

\+---------------------------+

|                       120 |

\+---------------------------+

1 row in set (0.03 sec)

**mysql> SELECT TRUNCATE(1.223343\*100,-2);**

\+---------------------------+

| TRUNCATE(1.223343\*100,-2) |

\+---------------------------+

|                       100 |

\+---------------------------+

1 row in set (0.00 sec)

**示例：查询数据库占用多少G**

**select concat(truncate((sum(DATA\_LENGTH)+sum(INDEX\_LENGTH) )/1024/1024/1024,2) ,' G') from information\_schema.TABLES where TABLE\_SCHEMA=’****数据库名’;**

**5****、****information\_schema** **数据库中的****TABLES** **表参数说明**

mysql> use information\_schema

Database changed

information\_schema库中的tables表字段如下：

mysql> select \* from TABLES limit 71,3;

\+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------------+

| NULL          | mysql        | user           | BASE TABLE | MyISAM |      10 | Dynamic    |          7 |             81 |         572 | 281474976710655 |         2048 |         0 |           NULL | 2017-11-26 17:19:46 | 2018-06-08 21:45:38 | NULL       | utf8\_bin          |     NULL |                | Users and global privileges |

| NULL          | smp11        | mcn\_user\_major | BASE TABLE | InnoDB |      10 | Compact    |          0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2018-06-10 20:40:45 | NULL                | NULL       | latin1\_swedish\_ci |     NULL |                |                             |

| NULL          | testslave    | id             | BASE TABLE | InnoDB |      10 | Compact    |          2 |           8192 |       16384 |               0 |            0 |         0 |           NULL | 2017-11-26 17:37:17 | NULL                | NULL       | latin1\_swedish\_ci |     NULL |                |                             |

3 rows in set (0.05 sec)

MySQL的 information\_schema 数据库中的TABLES 表记录了MySQL数据库中每个表占用的空间、表记录的行数，更新时间，说明等，这个表主要字段如下：

**TABLE\_SCHEMA :** **数据库名**

**TABLE\_NAME****：表名**

**ENGINE****：所使用的存储引擎**

**TABLES\_ROWS****：记录数，即表的行数**

**DATA\_LENGTH****：数据大小**

**INDEX\_LENGTH****：索引大小**

**CREATE\_TIME****：创建时间**

**UPDATE\_TIME****：最近更新时间**

**DATA\_FREE****：如果是共享表空间，该字段表示共享表空间的大小而非数据的大小。只有使用独占表空间时，该字段才表示该表的剩余空间；**

说明：该参数与mysql碎片有关，当MySQL从列表中删除一行内容，该段空间就会被留空。在一段时间内执行大量删除操作后，往往会使留空的空间变得比存储列表内容所使用的空间更大。

通俗的讲：Data\_free字段即为多占的物理空间，通过‘show table status’可以查看指定表的Data\_free字段，对应的值就是多占用的物理空间，当drop表重建或重新导入可以释放这部分空间。

此时可以使用optimize整理表的碎片：

注意：该操作执行的时候会把该表格先写入一个tmp临时表，所以磁盘剩余空间必须大于 表空间，否则会执行失败。mysql> optimize table classes,students;

5.6.X以前的版本会提示该表不支持optimize，5.6.X的版本已经支持Innodb了。

**6****、查询一个表或库占用总的空间的大小**

**使用sum(****数据大小)+sum(****索引大小)** **即可，SQL****如下：**

**1****）、查询库**

**select TABLE\_SCHEMA,sum(DATA\_LENGTH)+sum(INDEX\_LENGTH) as use\_total ,TABLE\_ROWS FROM information\_schema.TABLES where TABLE\_SCHEMA='****数据库名'**

mysql> select TABLE\_SCHEMA,sum(DATA\_LENGTH)+sum(INDEX\_LENGTH) FROM information\_schema.TABLES where TABLE\_SCHEMA='hellodb';

\+--------------+------------------------------------+

| TABLE\_SCHEMA | sum(DATA\_LENGTH)+sum(INDEX\_LENGTH) |

\+--------------+------------------------------------+

| hellodb      |                             262144 |

\+--------------+------------------------------------+

1 row in set (0.02 sec)

**2****）、查询表**

**select TABLE\_NAME,DATA\_LENGTH+INDEX\_LENGTH,TABLE\_ROWS FROM information\_schema.TABLES where TABLE\_SCHEMA='****数据库名' and TABLE\_NAME='****表名'**

mysql> select TABLE\_NAME,DATA\_LENGTH+INDEX\_LENGTH,TABLE\_ROWS FROM information\_schema.TABLES where TABLE\_SCHEMA='hellodb' and TABLE\_NAME='classes';

\+------------+--------------------------+------------+

| TABLE\_NAME | DATA\_LENGTH+INDEX\_LENGTH | TABLE\_ROWS |

\+------------+--------------------------+------------+

| classes    |                    16384 |          8 |

\+------------+--------------------------+------------+

1 row in set (0.00 sec)

**7****、查看表的最后更新时间**

select TABLE\_NAME,UPDATE\_TIME from information\_schema.TABLES where TABLE\_SCHEMA='数据库名';

可以通过查看数据库中表的更新时间，确定mysql数据库是使用情况。

**8****、案例：修改指定数据库名即可查看表格占用空间**

**案例1****：**

数据库所占磁盘空间，查询所有数据库占用磁盘空间大小的SQL语句如下：

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 |

\+--------------------+-----------+------------+

| smp11              | 0.01 MB   | 0.01MB     |

| mydb               | 0.06 MB   | 0.00MB     |

| ibdatax            | 0.03 MB   | 0.00MB     |

| hellodb            | 0.18 MB   | 0.06MB     |

| db1                | 43.62 MB  | 13.04MB    |

| testslave          | 0.01 MB   | 0.00MB     |

| mysql              | 0.53 MB   | 0.08MB     |

| information\_schema | 0.00 MB   | 0.00MB     |

\+--------------------+-----------+------------+

8 rows in set (0.27 sec)

**案例****2****：各个表所占的磁盘空间大小**

查询单个数据库里面各个表所占磁盘空间大小包括其索引的大小，SQL语句如下：

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 = 'hellodb'

-> group by TABLE\_NAME

-> order by data\_length desc;

\+------------+-----------+------------+

| TABLE\_NAME | data\_size | index\_size |

\+------------+-----------+------------+

| classes    | 0.01 MB   | 0.00 MB    |

| classes2   | 0.01 MB   | 0.00 MB    |

| classes3   | 0.01 MB   | 0.00 MB    |

| classes4   | 0.01 MB   | 0.00 MB    |

| coc        | 0.01 MB   | 0.00 MB    |

| courses    | 0.01 MB   | 0.00 MB    |

| paixi      | 0.01 MB   | 0.00 MB    |

| scores     | 0.01 MB   | 0.00 MB    |

| students   | 0.01 MB   | 0.03 MB    |

| students1  | 0.01 MB   | 0.03 MB    |

| teachers   | 0.01 MB   | 0.00 MB    |

| toc        | 0.01 MB   | 0.00 MB    |

| test       | NULL      | NULL       |

\+------------+-----------+------------+

13 rows in set (0.00 sec)

此处只需将SQL语句中的hellodb换成其它的数据库名称即可。

Mysql查看数据库和表占用空间

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle查看具体表占用空间 & oracle查看表属于哪个用户

相关 Mysql----查看数据库,表占用磁盘大小

相关 mysql查看数据库和表使用空间

相关 MySQL查看表占用空间大小

相关 Hive下查看表占用空间大小的方法

相关查看mysql数据库和表所占用空间

相关 Mysql查看数据库和表占用空间

相关 SQL Server查看表空间占用情况

相关查看数据库及表的大小和所占用空间大小

相关 MySQL查看表占用空间大小(转)