MySQL 查看数据库大小、表大小和最后修改时间
2,177 views
0
1. 查看数据库表基本信息
select * from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA = '数据库名' and information_schema.TABLES.TABLE_NAME = '表名';
示例：
select * from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA = 'wordpress' and information_schema.TABLES.TABLE_NAME = 'wp_posts'; +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------------+--------------------+-----------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | MAX_INDEX_LENGTH | TEMPORARY | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------------+--------------------+-----------+ | def | wordpress | wp_posts | BASE TABLE | MyISAM | 10 | Dynamic | 17525 | 10849 | 190141752 | 281474976710655 | 1997824 | 0 | 39875 | 2018-08-14 12:08:35 | 2020-01-19 18:13:20 | 2019-11-22 16:42:23 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------------+--------------------+-----------+ 1 row in set (0.002 sec)
2. 查看mysql数据库大小
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';
得到的结果是以字节为单位，除1024为KB，除(1024*1024)为MB，除(1024*1024*1024)为GB
示例（字节）：
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='wordpress'; +------------------------------------+ | sum(DATA_LENGTH)+sum(INDEX_LENGTH) | +------------------------------------+ | 217191309 | +------------------------------------+
示例（MB）：
SELECT (sum(DATA_LENGTH)+sum(INDEX_LENGTH)) / (1024*1024) as "data+index MB" FROM information_schema.TABLES where TABLE_SCHEMA='wordpress'; +---------------+ | data+index MB | +---------------+ | 207.1298 | +---------------+ 1 row in set (0.001 sec)
3. 查看表的最后mysql修改时间
select TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, AUTO_INCREMENT, DATA_LENGTH, INDEX_LENGTH, MAX_DATA_LENGTH, MAX_INDEX_LENGTH, CREATE_TIME from information_schema.TABLES where TABLE_SCHEMA='数据库名' order by TABLE_ROWS desc limit 20;
示例：
select TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, AUTO_INCREMENT, DATA_LENGTH, INDEX_LENGTH, MAX_DATA_LENGTH, MAX_INDEX_LENGTH, CREATE_TIME from information_schema.TABLES where TABLE_SCHEMA='wordpress' order by TABLE_ROWS desc limit 20; +----------------------------+------------+----------------+-----------------+-------------+--------------+------------------+----------------------+---------------------+ | TABLE_NAME | TABLE_ROWS | AVG_ROW_LENGTH | AUTO_INCREMENT | DATA_LENGTH | INDEX_LENGTH | MAX_DATA_LENGTH | MAX_INDEX_LENGTH | CREATE_TIME | +----------------------------+------------+----------------+-----------------+-------------+--------------+------------------+----------------------+---------------------+ | wp_term_relationships | 56865 | 21 | NULL | 1194186 | 2330624 | 5910974510923775 | 1125899906841600 | 2018-08-14 12:08:40 | | wp_postmeta | 49506 | 181 | 71730 | 8973864 | 1813504 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:35 | | wp_usermeta | 49151 | 45 | 50458 | 2215380 | 1612800 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:41 | | wp_posts | 17526 | 10849 | 39876 | 190151460 | 1997824 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:35 | | wp_terms | 14729 | 66 | 14768 | 983004 | 1714176 | 281474976710655 | 18446744073709551615 | 2018-08-14 12:08:40 | | wp_term_taxonomy | 14729 | 39 | 14772 | 576508 | 509952 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:40 | | wp_options | 1072 | 1662 | 246673192642047 | 2527284 | 74752 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:34 | | wp_comments | 970 | 296 | 21489 | 287428 | 87040 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:34 | | wp_commentmeta | 333 | 61 | 5855 | 20540 | 20480 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:34 | | wp_gallery_slides | 61 | 141 | 62 | 8632 | 2048 | 281474976710655 | 1125899906841600 | 2018-08-14 12:08:34 | | wp_gallery_galleriesslides | 61 | 27 | 68 | 1647 | 2048 | 7599824371187711 | 1125899906841600 | 2018-08-14 12:08:34 | | wp_reward | 22 | 744 | NULL | 16384 | 0 | 0 | 0 | 2019-04-11 07:46:34 | | wp_mobilepress | 12 | 29 | 13 | 348 | 2048 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:34 | | wp_users | 6 | 139 | 3862 | 836 | 8192 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:41 | | wp_gallery_galleries | 4 | 44 | 5 | 176 | 2048 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:34 | | wp_links | 3 | 93 | 8 | 280 | 3072 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:34 | | sae_checker_92_2013112914 | 1 | 28 | NULL | 28 | 4096 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:34 | | wp_share | 0 | 0 | 1 | 0 | 1024 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:40 | | wp_newsletter_stats | 0 | 0 | 1 | 0 | 1024 | 281474976710655 | 1125899906841600 | 2018-08-14 12:08:34 | | wp_baidusubmit_sitemap | 0 | 0 | 1 | 0 | 1024 | 281474976710655 | 288230376151710720 | 2018-08-14 12:08:34 | +----------------------------+------------+----------------+-----------------+-------------+--------------+------------------+----------------------+---------------------+ 20 rows in set (0.001 sec)
select * from information_schema.TABLES where TABLE_SCHEMA='数据库名' and information_schema.TABLES.TABLE_NAME = '表名';
示例：
select * from information_schema.TABLES where TABLE_SCHEMA='wordpress' and information_schema.TABLES.TABLE_NAME = 'wp_posts'; +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------------+--------------------+-----------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | MAX_INDEX_LENGTH | TEMPORARY | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------------+--------------------+-----------+ | def | wordpress | wp_posts | BASE TABLE | MyISAM | 10 | Dynamic | 17526 | 10849 | 190151460 | 281474976710655 | 1997824 | 0 | 39876 | 2018-08-14 12:08:35 | 2020-01-19 18:25:43 | 2019-11-22 16:42:23 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------------+--------------------+-----------+ 1 row in set (0.001 sec)
要想知道每个数据库的大小的话，步骤如下：
0、显示全部数据库
show databases; +--------------------+ | Database | +--------------------+ | discuz | | fundmanager | | information_schema | | mediawiki | | mysql | | performance_schema | | wordpress | +--------------------+ 7 rows in set (0.001 sec)
1、进入information_schema 数据库（存放了其他的数据库的信息）
use information_schema;
2、查询所有数据的大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
3、查看指定数据库的大小：
例如：查看数据库 wordpress 的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='wordpress';
4、查看指定数据库的某个表的大小
例如：查看数据库 wordpress 中 wp_posts 表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='wordpress' and table_name='wp_posts';
1） 查看正在处理的进程：
show processlist;
示例：
MariaDB [wordpress]> show processlist; +--------+-------------+-----------------+-----------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+-------------+-----------------+-----------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 454153 | ithomer | localhost:40566 | wordpress | Query | 0 | Init | show processlist | 0.000 | +--------+-------------+-----------------+-----------+---------+------+--------------------------+------------------+----------+ 6 rows in set (0.095 sec)
2） 查看数据库下的数据表占空间大小：
show table status from db_name ;
示例：
MariaDB [wordpress]> show table status from wordpress; +----------------------------+--------+---------+------------+-------+----------------+-------------+-------------------+--------------+-----------+-----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------+----------------------+-----------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary | +----------------------------+--------+---------+------------+-------+----------------+-------------+-------------------+--------------+-----------+-----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------+----------------------+-----------+ | sae_checker_92_2013112914 | MyISAM | 10 | Dynamic | 1 | 28 | 28 | 281474976710655 | 4096 | 0 | NULL | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_unicode_ci | NULL | | | 288230376151710720 | N | | wp_baidusubmit_sitemap | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 288230376151710720 | N | | wp_baidusubmit_urlstat | MyISAM | 10 | Fixed | 0 | 0 | 0 | 5910974510923775 | 1024 | 0 | 1 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 1125899906841600 | N | | wp_commentmeta | MyISAM | 10 | Dynamic | 333 | 61 | 20540 | 281474976710655 | 20480 | 0 | 5855 | 2018-08-14 12:08:34 | 2019-12-03 18:49:48 | 2019-11-22 16:42:22 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | | wp_comments | MyISAM | 10 | Dynamic | 970 | 296 | 287428 | 281474976710655 | 87040 | 0 | 21489 | 2018-08-14 12:08:34 | 2020-01-02 14:39:14 | 2019-11-22 16:42:22 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | | wp_diggc_log | MyISAM | 10 | Fixed | 0 | 0 | 0 | 18577348462903295 | 1024 | 0 | 1 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 1125899906841600 | N | | wp_gallery_galleries | MyISAM | 10 | Dynamic | 4 | 44 | 176 | 281474976710655 | 2048 | 0 | 5 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 288230376151710720 | N | | wp_gallery_galleriesslides | MyISAM | 10 | Fixed | 61 | 27 | 1647 | 7599824371187711 | 2048 | 0 | 68 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 1125899906841600 | N | | wp_gallery_slides | MyISAM | 10 | Dynamic | 61 | 141 | 8632 | 281474976710655 | 2048 | 0 | 62 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 1125899906841600 | N | | wp_links | MyISAM | 10 | Dynamic | 3 | 93 | 280 | 281474976710655 | 3072 | 0 | 8 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | | wp_mobilepress | MyISAM | 10 | Dynamic | 12 | 29 | 348 | 281474976710655 | 2048 | 0 | 13 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_unicode_ci | NULL | | | 288230376151710720 | N | | wp_newsletter | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0 | 1 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 288230376151710720 | N | | wp_newsletter_emails | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 1125899906841600 | N | | wp_newsletter_stats | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2018-08-14 12:08:34 | 2018-08-14 12:08:34 | 2019-11-22 16:42:22 | utf8_general_ci | NULL | | | 1125899906841600 | N | | wp_options | MyISAM | 10 | Dynamic | 1072 | 1664 | 2527284 | 281474976710655 | 74752 | 742700 | 246673192642167 | 2018-08-14 12:08:34 | 2020-01-20 12:03:04 | 2019-11-22 16:42:22 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | | wp_postmeta | MyISAM | 10 | Dynamic | 49525 | 181 | 8974644 | 281474976710655 | 1816576 | 0 | 71749 | 2018-08-14 12:08:35 | 2020-01-20 12:06:36 | 2019-11-22 16:42:22 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | | wp_posts | MyISAM | 10 | Dynamic | 17551 | 10866 | 190719212 | 281474976710655 | 2004992 | 0 | 39901 | 2018-08-14 12:08:35 | 2020-01-20 12:01:26 | 2019-11-22 16:42:23 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | | wp_ratings | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2018-08-14 12:08:40 | 2018-08-14 12:08:40 | 2019-11-22 16:42:23 | utf8_general_ci | NULL | | | 288230376151710720 | N | | wp_reward | InnoDB | 10 | Dynamic | 22 | 744 | 16384 | 0 | 0 | 0 | NULL | 2019-04-11 07:46:34 | 2020-01-08 14:47:26 | NULL | utf8_general_ci | NULL | | | 0 | N | | wp_share | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2018-08-14 12:08:40 | 2018-08-14 12:08:40 | 2019-11-22 16:42:23 | utf8_general_ci | NULL | | | 288230376151710720 | N | | wp_term_relationships | MyISAM | 10 | Fixed | 56875 | 21 | 1194375 | 5910974510923775 | 2331648 | 0 | NULL | 2018-08-14 12:08:40 | 2020-01-20 11:26:03 | 2019-11-22 16:42:24 | utf8_general_ci | NULL | | | 1125899906841600 | N | | wp_term_taxonomy | MyISAM | 10 | Dynamic | 14733 | 39 | 576984 | 281474976710655 | 509952 | 0 | 14776 | 2018-08-14 12:08:40 | 2020-01-20 12:01:15 | 2019-11-22 16:42:24 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | | wp_termmeta | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2019-04-11 07:46:40 | NULL | NULL | utf8mb4_unicode_ci | NULL | | | 0 | N | | wp_terms | MyISAM | 10 | Dynamic | 14733 | 66 | 983344 | 281474976710655 | 1714176 | 0 | 14772 | 2018-08-14 12:08:40 | 2020-01-20 11:26:03 | 2019-11-22 16:42:24 | utf8mb4_unicode_ci | NULL | | | 18446744073709551615 | N | | wp_usermeta | MyISAM | 10 | Dynamic | 49151 | 45 | 2215380 | 281474976710655 | 1612800 | 0 | 50458 | 2018-08-14 12:08:41 | 2020-01-20 12:01:26 | 2019-11-22 16:42:24 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | | wp_users | MyISAM | 10 | Dynamic | 6 | 139 | 836 | 281474976710655 | 8192 | 0 | 3862 | 2018-08-14 12:08:41 | 2019-12-09 13:23:09 | 2019-11-22 16:42:24 | utf8mb4_unicode_ci | NULL | | | 288230376151710720 | N | +----------------------------+--------+---------+------------+-------+----------------+-------------+-------------------+--------------+-----------+-----------------+---------------------+---------------------+---------------------+--------------------+----------+----------------+---------+----------------------+-----------+ 26 rows in set (0.002 sec)
select table_name, data_length, index_length from information_schema.tables where table_name like 'wp_%';
例如：
MariaDB [wordpress]> select table_name, data_length, index_length from information_schema.tables where table_name like 'wp_%'; +----------------------------+-------------+--------------+ | table_name | data_length | index_length | +----------------------------+-------------+--------------+ | wp_share | 0 | 1024 | | wp_reward | 16384 | 0 | | wp_term_relationships | 1194375 | 2331648 | | wp_mobilepress | 348 | 2048 | | wp_usermeta | 2215380 | 1612800 | | wp_term_taxonomy | 576984 | 509952 | | wp_postmeta | 8974644 | 1816576 | | wp_gallery_slides | 8632 | 2048 | | wp_newsletter_emails | 0 | 1024 | | wp_commentmeta | 20540 | 20480 | | wp_ratings | 0 | 1024 | | wp_comments | 287428 | 87040 | | wp_posts | 190719212 | 2004992 | | wp_baidusubmit_sitemap | 0 | 1024 | | wp_terms | 983344 | 1714176 | | wp_newsletter_stats | 0 | 1024 | | wp_gallery_galleries | 176 | 2048 | | wp_newsletter | 0 | 2048 | | wp_gallery_galleriesslides | 1647 | 2048 | | wp_links | 280 | 3072 | | wp_baidusubmit_urlstat | 0 | 1024 | | wp_options | 2527284 | 74752 | | wp_users | 836 | 8192 | | wp_diggc_log | 0 | 1024 | | wp_termmeta | 16384 | 32768 | +----------------------------+-------------+--------------+ 25 rows in set (0.007 sec)
查看特定数据库大小
SELECT table_schema, sum( data_length + index_length ) / 1024 / 1024 "dbSize(MB)" FROM information_schema.TABLES where table_schema = "db_name";
查看所有数据库大小
SELECT table_schema, sum( data_length + index_length ) / 1024 / 1024 "dbSize(MB)" FROM information_schema.TABLES GROUP BY table_schema;
示例：
MariaDB [wordpress]> SELECT table_schema, sum( data_length + index_length ) / 1024 / 1024 "dbSize(MB)" FROM information_schema.TABLES where table_schema = "wordpress"; +--------------+--------------+ | table_schema | dbSize(MB) | +--------------+--------------+ | wordpress | 207.69296455 | +--------------+--------------+ 1 row in set (0.001 sec) MariaDB [wordpress]> SELECT table_schema, sum( data_length + index_length ) / 1024 / 1024 "dbSize(MB)" FROM information_schema.TABLES GROUP BY table_schema; +--------------------+--------------+ | table_schema | dbSize(MB) | +--------------------+--------------+ | discuz | 5.60425854 | | fundmanager | 0.51562500 | | information_schema | 0.17187500 | | mediawiki | 37.55473709 | | mysql | 0.87493134 | | performance_schema | 0.00000000 | | wordpress | 207.69296455 | +--------------------+--------------+ 7 rows in set (0.013 sec)
参考推荐：
MySQL 时间函数加减计算 （推荐）
MySQL 中 distinct 和 group by 性能比较
版权所有: 本文系米扑博客原创、转载、摘录，或修订后发表，最后更新于 2020-01-20 12:13:14
侵权处理: 本个人博客，不盈利，若侵犯了您的作品权，请联系博主删除，莫恶意，索钱财，感谢！