MySQL，查看数据库的信息，大小，最后修改时间等

最新推荐文章于 2022-12-16 15:57:22 发布

原创最新推荐文章于 2022-12-16 15:57:22 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

mySQL/pgSQL 专栏收录该内容

26 篇文章

订阅专栏

本文详细介绍了如何使用SQL查询语句查看数据库的基本信息、大小和修改时间，提供了数据库性能优化的方法，包括查询数据库大小、获取修改时间等关键操作。

1、查看数据库的基本信息

mysql> SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db' [AND TABLE_NAME = 'table'];
+---------------+--------------+------------+------------+--------+---------+------------+------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS |
+---------------+--------------+------------+------------+--------+---------+------------+------------+
| def           | db           | table      | BASE TABLE | InnoDB |      10 | Compact    |          0 |
+---------------+--------------+------------+------------+--------+---------+------------+------------+
+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
| AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         |
+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
|              0 |       16384 |               0 |            0 |         0 |              1 | 2013-12-25 16:17:29 |
+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
+-------------+------------+-----------------+----------+----------------+---------------+   
| UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |   
+-------------+------------+-----------------+----------+----------------+---------------+   
| NULL        | NULL       | utf8_general_ci |     NULL |                |               |              
+-------------+------------+-----------------+----------+----------------+---------------+   
1 row in set (0.07 sec)

2、查看数据库的大小

mysql> SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS size FROM information_schema.TABLES WHERE TABLE_SCHEMA='push';
+---------+
| size    |
+---------+
| 8830976 |
+---------+
1 row in set (0.00 sec)

说明：得到的结果是以字节为单位，除以1024为K，除以1024*1024(=1048576)为M。

3、查看数据库的修改时间

SELECT UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' ORDER BY UPDATE_TIME DESC LIMIT 1; 
SELECT UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' and TABLE_NAME = 'table';

说明：MyISAM类型表才支持，其他类型（如InnoDB）查出来的修改时间为null