mysql 系统查询语句

前言：
information_schema 库的 TABLES 表，字段分别是
CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `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) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |

查询所有数据库总大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB') as data_size from information_schema.tables;
+-----------+
| data_size |
+-----------+
| 1177.00MB |
+-----------+
1 row in set (19.26 sec)


查询某个数据库大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB') as data_size from information_schema.tables where TABLE_SCHEMA='mysql';
+-----------+
| data_size |
+-----------+
| 0.55MB    |
+-----------+
1 row in set (0.25 sec)


1、查询数据库中的表占用空间大小
例如，查询mysql数据库下表
mysql> select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA='mysql' group by TABLE_NAME order by data_length desc;
+---------------------------+-----------+------------+
| TABLE_NAME                | data_size | index_size |
+---------------------------+-----------+------------+
| help_topic                | 0.45 MB   | 0.02 MB    |
| help_keyword              | 0.08 MB   | 0.01 MB    |
| help_relation             | 0.00 MB   | 0.01 MB    |
| proxies_priv              | 0.00 MB   | 0.00 MB    |
| help_category             | 0.00 MB   | 0.00 MB    |
| user                      | 0.00 MB   | 0.00 MB    |
| proc                      | 0.00 MB   | 0.00 MB    |
| db                        | 0.00 MB   | 0.00 MB    |
| ndb_binlog_index          | 0.00 MB   | 0.00 MB    |
| time_zone_transition      | 0.00 MB   | 0.00 MB    |
| general_log               | 0.00 MB   | 0.00 MB    |
| time_zone_leap_second     | 0.00 MB   | 0.00 MB    |
| event                     | 0.00 MB   | 0.00 MB    |
| tables_priv               | 0.00 MB   | 0.00 MB    |
| columns_priv              | 0.00 MB   | 0.00 MB    |
| servers                   | 0.00 MB   | 0.00 MB    |
| procs_priv                | 0.00 MB   | 0.00 MB    |
| plugin                    | 0.00 MB   | 0.00 MB    |
| host                      | 0.00 MB   | 0.00 MB    |
| time_zone_transition_type | 0.00 MB   | 0.00 MB    |
| time_zone_name            | 0.00 MB   | 0.00 MB    |
| func                      | 0.00 MB   | 0.00 MB    |
| time_zone                 | 0.00 MB   | 0.00 MB    |
| slow_log                  | 0.00 MB   | 0.00 MB    |
+---------------------------+-----------+------------+
24 rows in set (0.86 sec)

2、查询数据库中某个表占用空间的大小
例如，查询mysql数据库下的help_topic表
mysql> select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA='mysql' and TABLE_NAME='help_topic' group by TABLE_NAME order by data_length desc;
+------------+-----------+------------+
| TABLE_NAME | data_size | index_size |
+------------+-----------+------------+
| help_topic | 0.45 MB   | 0.02 MB    |
+------------+-----------+------------+
1 row in set (0.05 sec)


3、查看数据库中表的最后修改时间
例如，查询mysql数据库下的表
mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables where TABLE_SCHEMA='mysql';
+---------------------------+---------------------+
| TABLE_NAME                | UPDATE_TIME         |
+---------------------------+---------------------+
| columns_priv              | 2014-12-02 14:38:56 |
| db                        | 2014-12-02 14:56:52 |
| event                     | 2014-12-02 14:39:05 |
| func                      | 2014-12-02 14:38:59 |
| general_log               | NULL                |
| help_category             | 2014-12-02 14:38:34 |
| help_keyword              | 2014-12-02 14:38:34 |
| help_relation             | 2014-12-02 14:38:34 |
| help_topic                | 2014-12-02 14:38:34 |
| host                      | 2014-12-02 14:39:05 |
| ndb_binlog_index          | 2014-12-02 14:38:32 |
| plugin                    | 2014-12-02 14:39:00 |
| proc                      | 2014-12-02 14:39:05 |
| procs_priv                | 2014-12-02 14:39:03 |
| proxies_priv              | 2014-12-02 14:38:34 |
| servers                   | 2014-12-02 14:38:30 |
| slow_log                  | NULL                |
| tables_priv               | 2014-12-02 14:38:56 |
| time_zone                 | 2014-12-02 14:38:31 |
| time_zone_leap_second     | 2014-12-02 14:38:31 |
| time_zone_name            | 2014-12-02 14:38:30 |
| time_zone_transition      | 2014-12-02 14:38:31 |
| time_zone_transition_type | 2014-12-02 14:38:31 |
| user                      | 2015-06-26 18:14:29 |
+---------------------------+---------------------+
24 rows in set (0.01 sec)

同理，查询某个表，加上TABLE_NAME条件。