數據庫中有幾十上百張表，那么哪些表的數據量比較大呢，總不能一個表一個表的去查詢吧，在mysql中也有類似于oracle的數據字典表，只不過mysql沒有oracle記錄的那么多和詳細，但也足夠我們查詢這些信息了。

在mysql的information_schema下有存儲數據庫基本信息的數據字典表，可以通過查詢tables表來獲得所需要的表相關信息。

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)

那么我們查看一下talbes表結構信息，看看存儲的具體信息

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)

主要存儲了表的信息如表使用的引擎，表的類型等信息。我們可以通過查詢table_rows屬性獲得哪些表數據量比較大。

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 |

我們繼續深入思考，這些存儲的數據是否準確，是否真實的反應了表中數據量大小？

mysql> show create table tables \G;

*************************** 1. row***************************

Table: TABLES

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,

`CREATE_OPTIONS` varchar(255) default NULL,

`TABLE_COMMENT` varchar(80) NOT NULL default ''

)ENGINE=MEMORYDEFAULTCHARSET=utf8

看到上面紅色的字體了吧，information_schema下的表tables是內存表，數據庫啟動的時候，會讀取分析各表中數據，然后填充tables表。如果某些表更新頻繁，而來不及更新tables表的時候，tables中存儲的數據就不一定準確了，這會影響到執行計劃的分析，索引在執行計劃的時候，可以analyze表，然后確保存儲的信息準確。

總結

以上是生活随笔為你收集整理的mysql查看比较大的数据表_mysql 如何查看哪些表数据量比较大的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。