How to check which tables have a large amount of data in mysql?
This article introduces how to view which tables in mysql have a large amount of data, the content is very detailed, interested friends can refer to it, I hope it can help you.
How do I check which tables have a larger amount of data?
There are dozens of hundreds of tables, so which tables have a relatively large amount of data, can not be a table to query it, there are also similar to oracle data dictionary tables, but mysql is not oracle records so much and detailed, but also enough for us to query this information.
Under information_schema of mysql, there is a data dictionary table that stores basic database information. You can query the tables table to obtain the required table-related information.
www.2cto.com
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
|report |
| report_result |
|test |
+--------------------+
5 rows in set (0.02 sec)
mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
|Tables_in_information_schema |
+---------------------------------------+
|CHARACTER_SETS |
|COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
|COLUMNS |
|COLUMN_PRIVILEGES |
|KEY_COLUMN_USAGE |
|PROFILING |
|ROUTINES |
|SCHEMATA |
|SCHEMA_PRIVILEGES |
|STATISTICS |
|TABLES |
|TABLE_CONSTRAINTS |
|TABLE_PRIVILEGES |
|TRIGGERS |
|USER_PRIVILEGES |
|VIEWS |
+---------------------------------------+
17 rows in set (0.00 sec)
So let's look at the talbes table structure information to see what's stored.
www.2cto.com
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)
It mainly stores information about tables, such as the engine used by tables and the type of tables. We can query the table_rows property to find out which tables have a large amount of data.
mysql> select table_name,table_rows from tables order by table_rows desc limi 10;
+---------------+------------+
www.2cto.com
| table_name |table_rows |
+---------------+------------+
| task6 | 1558845 |
| task | 1554399 |
| task5 | 1539009 |
| task3 | 1532169 |
| task1 | 1531143 |
| task2 | 1531143 |
| task4 | 1521225 |
| task7 | 980865 |
We continue to think deeply about whether these stored data are accurate and truly reflect the size of the data in the table.
mysql> show create table tables \G;
*************************** 1. row***************************
Table: TABLES
www.2cto.com
Create Table: CREATE TEMPORARY TABLE`TABLES` (
`TABLE_CATALOG` varchar(512) default NULL,
`TABLE_SCHEMA` 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) default 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,
www.2cto.com
`CREATE_OPTIONS` varchar(255) default NULL,
`TABLE_COMMENT` varchar(80) NOT NULL default ''
) ENGINE=MEMORY DEFAULTCHARSET=utf8
About mysql how to view which table data volume is relatively large to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.
