如何查詢mysql數據庫中哪些表的數（shù）據量最大？
數據（jù）庫中（zhōng）有幾十上百張表，那麽哪些表的（de）數據量比較大呢，總（zǒng）不能一個（gè）表（biǎo）一個表的去查詢吧，在mysql中也有類似於oracle的數據（jù）字典表，隻不過mysql沒有oracle記錄的（de）那麽多和（hé）詳細，但也足夠我們查詢這（zhè）些信息了。
在mysql的information_schema下有存儲數據庫基（jī）本（běn）信（xìn）息的數（shù）據字典表（biǎo），可以通（tōng）過查詢tables表來獲得所需（xū）要的表相（xiàng）關信息。
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)
那麽（me）我（wǒ）們查（chá）看一下talbes表結構（gòu）信息，看看存儲的具體信息
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)
主要存儲（chǔ）了表的（de）信（xìn）息如（rú）表使用的引擎，表的類型等信（xìn）息（xī）。我們可以通過查（chá）詢（xún）table_rows屬性（xìng）獲得哪些表（biǎo）數（shù）據量比較（jiào）大。
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 |
我們（men）繼續（xù）深入思考，這些（xiē）存儲的數據是否準確，是否（fǒu）真實的反應了表中數據量大小？
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 '',
&nb
關鍵詞：mysql
閱讀本文後您有什麽感想? 已有 人給出評價!
- 1
- 38
- 1
- 1
- 1
- 1
