Mysql database View how the data table occupies the space and the number of records _mysql

You can open the MySQL information_schema database if you want to know the space occupied by each table in the MySQL database and the number of rows recorded in the table. There is a tables table in this library, the main fields of this table are:

Table_schema: Database name
TABLE_NAME: Table name
ENGINE: the storage engine used
Tables_rows: Number of records
Data_length: Data size
Index_length: Index size

Other fields please refer to the MySQL manual, which is most useful for us.
The size of a table occupies space, equivalent to the size of the data + index size,

Example:

1. To view all the table sizes for the HX library, you can use:

Copy Code code as follows:

SELECT table_name,data_length+index_length,table_rows from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' HX ';

+-------------------+--------------------------+------------+
| table_name | Data_length+index_length | Table_rows |
+-------------------+--------------------------+------------+
|                  Enjoy_data |      6979584 | 70113 |
|                Hx_record |     113410048 | 753279 |
|              Itlearner_record |  21835546624 | 104917777 |
|                    tmp_day_id |        17326 | 811 |
+-------------------+--------------------------+------------+

2. To view the size of the enjoy table for the HX library, you can use:

Copy Code code as follows:

SELECT data_length+index_length,table_rows from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' HX ' and table_name = ' enjoy_data ';

Return:

Copy Code code as follows:

+--------------------------+------------+
| Data_length+index_length | Table_rows |
+--------------------------+------------+
|          6979584 | 70113 |
+--------------------------+------------+