mysql查看表位置_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 | 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换成其它的数据库名称即可。