MySQL查看数据库表数据量大小和索引数据大小以及行数

前言

作为开发人员，需要对mysql数据表的数据的大小，索引大小，行数等信息有一定的了解，特别是数据量大的时候，才能心里有把握。

方法一

打开MySQL的 information_schema 数据库，在该库中有一个 TABLES 表，这个表里面记录了所有表的详细信息。

mysql> SELECT * FROM `information_schema`.`TABLES` LIMIT 5;
+---------------+--------------------+---------------------------------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| 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 |
+---------------+--------------------+---------------------------------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | information_schema | CHARACTER_SETS                        | SYSTEM VIEW | MEMORY |      10 | Fixed      |       NULL |            384 |           0 |        16434816 |            0 |          0 |           NULL | 2020-02-05 14:08:39 | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=43690 |               |
| def           | information_schema | COLLATIONS                            | SYSTEM VIEW | MEMORY |      10 | Fixed      |       NULL |            231 |           0 |        16704765 |            0 |          0 |           NULL | 2020-02-05 14:08:39 | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=72628 |               |
| def           | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY |      10 | Fixed      |       NULL |            195 |           0 |        16357770 |            0 |          0 |           NULL | 2020-02-05 14:08:39 | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=86037 |               |
| def           | information_schema | COLUMNS                               | SYSTEM VIEW | InnoDB |      10 | Dynamic    |       NULL |              0 |       16384 |               0 |            0 | 1663041536 |           NULL | NULL                | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=2789  |               |
| def           | information_schema | COLUMN_PRIVILEGES                     | SYSTEM VIEW | MEMORY |      10 | Fixed      |       NULL |           2565 |           0 |        16757145 |            0 |          0 |           NULL | 2020-02-05 14:08:39 | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=6540  |               |
+---------------+--------------------+---------------------------------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
5 rows in set, 16 warnings (20.12 sec)

主要有以下字段需要关注的：

TABLE_SCHEMA : 数据库名
TABLE_NAME：   表名
ENGINE：       所使用的存储引擎
TABLES_ROWS：  记录数
DATA_LENGTH：  数据大小(KB)
INDEX_LENGTH： 索引大小(KB)

查询某一个表的详细信息

SELECT * FROM `information_schema`.`TABLES` where table_name=''
很简单。在table_name字段加上需要查询的表名即可。

mysql> SELECT * FROM `information_schema`.`TABLES` where table_name='ads_cusprofile_et_info'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: vi_db
     TABLE_NAME: ads_cusprofile_et_info
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 38462550
 AVG_ROW_LENGTH: 89
    DATA_LENGTH: 3443523584
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 1648345088
      DATA_FREE: 1665138688
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2020-02-05 12:58:46
    UPDATE_TIME: 2020-02-04 22:41:14
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: ads et信息表
1 rows in set (0.01 sec)

方法二

show table status where name='表名'\G;

mysql> show table status where name='ads_cusprofile_et_info'\G;
*************************** 1. row ***************************
           Name: ads_cusprofile_et_info
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 38462550
 Avg_row_length: 89
    Data_length: 3443523584
Max_data_length: 0
   Index_length: 1648345088
      Data_free: 1665138688
 Auto_increment: NULL
    Create_time: 2020-02-05 12:58:46
    Update_time: 2020-02-04 22:41:14
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment: ads et信息表
1 row in set (0.00 sec)

ERROR:
No query specified

一样有Rows， Data_length， Index_length 字段，字段含义同上。