MySQL's Information_schema

When emptying a larger table (more than 400 trillion in the Qing dynasty), it is found that the table is recorded as 0 but the space is not released, use the following way to see the occupancy situation

--querying each database for disk usageSelectTable_schema, Concat (truncate(sum(data_length)/1024x768/1024x768,2),'MB') asdata_size, concat (truncate(sum(index_length)/1024x768/1024x768,2),'MB') asindex_size fromInformation_schema.tablesGroup  byTable_schemaOrder  byData_lengthdesc; --querying disk usage for tables under specified databaseSelectTable_name,table_rows,concat (truncate(Data_length/1024x768/1024x768,2),'MB') asdata_size, concat (truncate(Index_length/1024x768/1024x768,2),'MB') asindex_size fromInformation_schema.tableswhereTable_schema= ' my_db'  Group  bytable_nameOrder  byData_lengthdesc;

This part of the space can be freed by optimize operation, as follows:

TABLE my_db. My_table

When you view the disk usage above, it is released.

On the Internet, the usage and description of Guan Yu optimize were queried:

[LOCAL | No_write_to_binlog]TABLE[, Tbl_name] ...

If you have deleted a large part of the table, or if you have made many changes to a table with variable-length rows (tables with Varchar,blob or text columns), you should use optimize table.

Deleted records are not saved in the linked list, and subsequent insert operations re-use the old record location. You can use the Optimize table to reuse unused space and defragment the data files.

In most settings, you do not need to run optimize TABLE at all. Even if you have a large number of updates to a variable-length row, you do not need to run it frequently, once a week or once a month, only for specific tables.

OPTIMIZE table works only on MyISAM, BDB, and InnoDB tables.

Note: MySQL locks the table during optimize table operation.

There is no concept of information_schema, incidentally, to understand information_schema related information.

The INFORMATION_SCHEMA database is a MySQL-band that provides a way to access database metadata. Metadata is data about the data, such as the database name or table name, the data type of the column, or access permissions. There are many tables in information, as follows:

schemata Table: Provides information for all databases in the current MySQL instance. This table is the result of show databases. Tables Table: Provides information about the tables in the database, including views. Describes in detail which schema, table type, table engine, creation time, and so on, a table belongs to. It's show tables . fromThe results of the schemaname are taken from this table. Columns table: Provides the column information in the table. Describes in detail all the columns of a table and the information for each column. It's show columns . fromThe results of the schemaname.tablename are taken from this table. Statistics Table: Provides information about the index of a table. It's show .Index  fromThe results of the schemaname.tablename are taken from this table. User_privileges (User Rights) Table: gives information about the full range of permissions. This information is derived from the Mysql.user authorization form. Non-standard tables. Schema_privileges (Program permissions) Table: Provides information about schema (database) permissions. This information is from the Mysql.db authorization form. Non-standard tables. Table_privileges (table permissions) Table: Gives information about table permissions. This information is derived from the Mysql.tables_priv authorization form. Non-standard tables. Column_privileges (column permissions) Table: Gives information about column permissions. This information is derived from the Mysql.columns_priv authorization form. Non-standard tables. Character_sets (character Set) Table: Provides information about the available character sets for MySQL instances. It's show .CHARACTERThe set result set is taken from this table. Collations table: Provides cross-reference information about each character set. Collation_character_set_applicability table: Indicates the character set that can be used for proofreading. These columns are equivalent to the first two display fields of show collation. Table_constraints table: A table describing the existence of constraints. And the constraint type of the table. Key_column_usage table: Describes the key columns that have constraints. Routines Table: Provides information about storing subroutines (stored programs and functions). At this point, the routines table does not contain a custom function (UDF). Named "MySQL."procthe column "name" indicates the corresponding INFORMATION_SCHEMA. The Mysql.proc table column for the routines table. Views Table: Gives information about the view in the database. You need to have show views permission, otherwise you cannot view the view information. Triggers table: Provides information about the triggering program. You must have super permissions to view the table. 

information.tables Field Information

Information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+-----------------+---------------------+------+-----+---------+-------+|Table_catalog| varchar( +)|NO|     |         |       ||Table_schema| varchar( -)|NO|     |         |       |Database name|table_name| varchar( -)|NO|     |         |       |Table name|Table_type| varchar( -)|NO|     |         |       |engine|ENGINE| varchar( -)|YES|     | NULL    |       ||VERSION| bigint( +) unsigned|YES|     | NULL    |       |whether to compress|Row_format| varchar(Ten)|YES|     | NULL    |       ||Table_rows| bigint( +) unsigned|YES|     | NULL    |       ||Avg_row_length| bigint( +) unsigned|YES|     | NULL    |       ||Data_length| bigint( +) unsigned|YES|     | NULL    |       |Data Space Size|Max_data_length| bigint( +) unsigned|YES|     | NULL    |       ||Index_length| bigint( +) unsigned|YES|     | NULL    |       |Data Index size|Data_free| bigint( +) unsigned|YES|     | NULL    |       ||Auto_increment| bigint( +) unsigned|YES|     | NULL    |       ||Create_time| datetime            |YES|     | NULL    |       ||Update_time| datetime            |YES|     | NULL    |       ||Check_time| datetime            |YES|     | NULL    |       ||Table_collation| varchar( +)|YES|     | NULL    |       ||CHECKSUM| bigint( +) unsigned|YES|     | NULL    |       ||Create_options| varchar(255)|YES|     | NULL    |       ||Table_comment| varchar(2048)|NO|     |         |       |+-----------------+---------------------+------+-----+---------+-------+

Select *  from Information_schema.tables

MySQL's Information_schema