前言
作为开发人员,需要对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 字段,字段含义同上。