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

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;

+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------------+

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |

+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+-----------------------------+

| NULL | mysql | user | BASE TABLE | MyISAM | 10 | Dynamic | 7 | 81 | 572 | 281474976710655 | 2048 | 0 | NULL | -11-26 17:19:46 | -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 | -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 | -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换成其它的数据库名称即可。