查看mysql数据库 数据量大小和索引数据大小以及行数的方法
查看一个数据中所有表的相关信息:
(1)可以在命令下使用show table status \G命令查看:
(2)如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话,可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表,这个表主要字段分别是:
TABLE_SCHEMA : 数据库名
TABLE_NAME:表名
ENGINE:所使用的存储引擎
TABLES_ROWS:记录数
DATA_LENGTH:数据大小
INDEX_LENGTH:索引大小
其他字段请参考MySQL的手册,我们只需要了解这几个就足够了。所以要知道一个表占用空间的大小,那就相当于是 数据大小 + 索引大小 即可。
SQL:
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM information_schema.TABLES a WHERE a.TABLE_SCHEMA='test_yyz' AND a.TABLE_NAME='tk_question_ls';
一、查看特定数据库特定表的数据部分大小,索引部分大小和总占用磁盘大小
SELECT
a.table_schema ,
a.table_name ,
concat(round(sum(DATA_LENGTH / 1024 / 1024) + sum(INDEX_LENGTH / 1024 / 1024) ,2) ,'MB') total_size ,
concat(round(sum(DATA_LENGTH / 1024 / 1024) , 2) ,'MB') AS data_size ,
concat(round(sum(INDEX_LENGTH / 1024 / 1024) , 2) ,'MB') AS index_size
FROM
information_schema. TABLES a
WHERE
a.table_schema = 'test_yyz'
AND a.table_name = 'tk_question_ls';+--------------+----------------+------------+-----------+------------+
| table_schema | table_name | total_size | data_size | index_size |
+--------------+----------------+------------+-----------+------------+
| test_yyz | tk_question_ls | 4.47MB | 3.52MB | 0.95MB |
+--------------+----------------+------------+-----------+------------+
1 row in set (0.00 sec)
二、查看一个库中的情况
SELECT
concat_ws('.',a.table_schema ,a.table_name) ,CONCAT(a.table_schema,'.',a.table_name) ,
CONCAT(ROUND(table_rows/1000,4),'KB') AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024),4),',') AS 'data_size',
CONCAT(ROUND(index_length/(1024*1024),4),'M') AS 'index_size',
CONCAT(ROUND((data_length+index_length)/(1024*1024),4),'M') AS'Total'
FROM
information_schema. TABLES a
WHERE
a.table_schema = 'test_yyz'
三、查询一个数据库中每个表的总行数,数据大小,索引大小和总大小
mysql> SELECT
-> concat_ws('.',a.table_schema ,a.table_name) ,CONCAT(a.table_schema,'.',a.table_name) ,
-> CONCAT(ROUND(table_rows/1000,4),'KB') AS 'Number of Rows',
-> CONCAT(ROUND(data_length/(1024*1024),4),',') AS 'data_size',
-> CONCAT(ROUND(index_length/(1024*1024),4),'M') AS 'index_size',
-> CONCAT(ROUND((data_length+index_length)/(1024*1024),4),'M') AS'Total'
-> FROM
-> information_schema. TABLES a
-> WHERE
-> a.table_schema = 'test_yyz';
四、查看从第20行到30行之间的数据:
mysql> SELECT
-> concat_ws('.',a.table_schema ,a.table_name) ,CONCAT(a.table_schema,'.',a.table_name) ,
-> CONCAT(ROUND(table_rows/1000,4),'KB') AS 'Number of Rows',
-> CONCAT(ROUND(data_length/(1024*1024),4),',') AS 'data_size',
-> CONCAT(ROUND(index_length/(1024*1024),4),'M') AS 'index_size',
-> CONCAT(ROUND((data_length+index_length)/(1024*1024),4),'M') AS'Total'
-> FROM
-> information_schema. TABLES a
-> WHERE
-> a.table_schema = 'test_yyz'
-> LIMIT 20,30;
五、使用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: