查看mysql数据库数据量大小和索引数据大小以及行数的方法。
在mysql中有一个默认的数据表information_schema,information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。
查看一个数据中所有表的相关信息:
(1)可以在命令下使用 show table status \G命令查看:
(2)数据库中每个表占用的空间、表记录的行数的话,可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表,这个表主要字段分别是:
- TABLE_SCHEMA : 数据库名
- TABLE_NAME:表名
- ENGINE:所使用的存储引擎
- TABLES_ROWS:记录数
- DATA_LENGTH:聚集索引所占用的空间,单位是bytes
- INDEX_LENGTH:二级索引所占用的空间,单位是bytes
- data_free:已分配但是未使用的空间,单位是bytes
1.查看所有数据库容量大小
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.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
2.查看所有数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
3.查看指定数据库容量大小
例:查看mysql库容量大小
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.tables
where table_schema='mysql';
4.查看指定数据库各表容量大小
例:查看mysql库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;
五、使用show table status \G命令行下查看每个表的索引大小,数据大小和行数
mysql> show table status \G;
*************************** 1. row ***************************
Name: tk_question_ls
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1287
Avg_row_length: 2864
Data_length: 3686400
Max_data_length: 0
Index_length: 999424
Data_free: 0
Auto_increment: NULL
Create_time: 2017-09-05 11:02:40
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: 试题表(历史)
30 rows in set (0.00 sec)
*************************** 2. row ***************************
Name: test_person
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: 2018-07-12 16:44:21
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: