How to View tables with large data volume in mysql _ MySQL-mysql tutorial

How does mysql check which tables have a large data volume bitsCN.com?
How does mysql check which tables have a large amount of data? if there are dozens or hundreds of tables in the database, which tables have a large amount of data, you cannot query one table or one table, mysql also has a data dictionary table similar to oracle, except that mysql does not have as many details as it records, but it is enough for us to query this information. In information_schema of mysql, there is a data dictionary table that stores the basic information of the database. you can query the tables Table to obtain the required table information. Mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | mysql | report | report_result | test | + ------------------ + 5 rows in set (0.02 sec) mysql> use information_schema; Database changedmysql> show tables; + tables + | Tables_in_information_schema | + --------------------------------------- + | CHARACTER_SETS | COLLATIONS | CO Partitions | COLUMNS | COLUMN_PRIVILEGES | partitions | PROFILING | ROUTINES | SCHEMATA | STATISTICS | TABLES | TABLE_CONSTRAINTS | TABLE_PRIVILEGES | TRIGGERS | limit | VIEWS | + --------------------------------------- + 17 rows in set (0.00 sec) let's take a look at the structure information of the talbes table and look at the specific information stored in mysql> desc tables; + ----------------- + ------------ + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ----------------- + ------------ + ------ + ----- + --------- + ------- + | TABLE_CATALOG | varchar (512) | YES | NULL | TABLE_SCHEMA | varchar (64) | NO | TABLE_NAME | varchar (64) | NO | TABLE_TYPE | varchar (64) | NO | ENGINE | varchar (64) | YES | NULL | VERSION | bigint (21) | YES | NULL | ROW_FORMAT | varchar (10) | YES | NULL | TABLE_ROWS | bigint (21) | YES | NULL | AVG_ROW_LENGTH | bigint (21) | YES | NULL | DATA_LENGTH | bigint (21) | YES | NULL | MAX_DATA_LENGTH | bigint (21) | YES | NULL | INDEX_LENGTH | bigint (21) | YES | NULL | DATA_FREE | bigint (21) | YES | NULL | AUTO_INCREMENT | bigint (21) | YES | NULL | CREATE_TIME | datetime | YES | NULL | UPDATE_TIME | Datetime | YES | NULL | CHECK_TIME | datetime | YES | NULL | TABLE_COLLATION | varchar (64) | YES | NULL | CHECKSUM | bigint (21) | YES | NULL | CREATE_OPTIONS | varchar (255) | YES | NULL | TABLE_COMMENT | varchar (80) | NO | + --------------- + -------------- + ------ + ----- + --------- + ------- + 21 rows in set (0.00 sec) mainly stores table information, such as the engine used by the table, table type and other information. You can query which tables have a large data volume by querying the table_rows attribute. Mysql> select table_name, table_rows from tables order by table_rows desc limi 10; + --------------- + ------------ + | table_name | table_rows | + --------------- + ------------ + | task6 | 1558845 | task | 1554399 | task5 | 1539009 | task3 | 1532169 | task1 | 1531143 | | task2 | 1531143 | task4 | 1521225 | task7 | 980865 | let's continue to think deeply, is the stored data accurate? does it actually reflect the data size in the table? Mysql> show create table tables/G; ***************************** 1. row *************************** Table: TABLES Create Table: create temporary table 'Tables '('Table _ CATALOG' varchar (512) default NULL, 'Table _ scheme' varchar (64) not null default '', 'Table _ name' varchar (64) not null default '', 'Table _ type' varchar (64) not null default'', 'engine' varchar (64) default NULL, 'version' bigint (21) de Fault NULL, 'row _ format' varchar (10) default NULL, 'Table _ ROWS 'bigint (21) default NULL, 'avg _ ROW_LENGTH' bigint (21) default NULL, 'data _ length' bigint (21) default NULL, 'max _ DATA_LENGTH 'bigint (21) default NULL, 'index _ length' bigint (21) default NULL, 'data _ free' bigint (21) default NULL, 'auto _ INCREMENT 'bigint (21) default NULL, 'create _ time' datetime default NULL, 'update _ time' datetime default NULL, 'Check _ time' datetime default NULL, 'Table _ COLLATION 'varchar (64) default NULL, 'checksum' bigint (21) default NULL, 'create _ options' varchar (255) default NULL, 'Table _ comment' varchar (80) not null default '') ENGINE = memory defaultcharset = utf8 see the red font above. The Table tables under information_schema is a MEMORY table. when the database starts, it reads and analyzes the data in each table, and then fills the tables table. If some tables are updated frequently, but the tables table cannot be updated, the data stored in the tables table is not necessarily accurate, which affects the analysis of the execution plan. when the index is executing the plan, you can analyze the table and ensure that the stored information is accurate. Author: aeolus_pubitsCN.com