1.通过information_schema.TABLES来查看每一个表的相信信息,比如:table_rows, avg_row_length, data_length, man_data_length, date_free等数据
字段 | 含义 |
Table_catalog | 数据表登记目录 |
Table_schema | 数据表所属的数据库名 |
Table_name | 表名称 |
Table_type | 表类型[system view|base table] |
Engine | 使用的数据库引擎[MyISAM|CSV|InnoDB] |
Version | 版本,默认值10 |
Row_format | 行格式[Compact|Dynamic|Fixed] |
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 | 表的注释、备注 |
查看该数据库实例下所有库大小,得到的结果是以MB为单位
- select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 \
- as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables;
- +--------------------+---------------+--------------+---------------+
- | table_schema | data_length | index_length | sum |
- +--------------------+---------------+--------------+---------------+
- | information_schema | 2734.92757511 | 86.27539063 | 2821.20296574 |
- +--------------------+---------------+--------------+---------------+
查看该实例下各个库大小
- 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 |
- +--------------------+---------------+---------------+-------------+--------+------------+
- | data_1234567890 | 2820.59610939 | 2734.39689064 | 86.19921875 | 65 | 2015-11-02 |
- | mysql | 0.60579967 | 0.53744030 | 0.06835938 | 14 | 2015-11-02 |
- | information_schema | 0.00781250 | 0.00000000 | 0.00781250 | 35 | 2015-11-02 | 查看单个表状态 show table status from data_1234567890 where name = ‘data_1234567890_ss‘ \G