mysql - 从 information_schema 表中选择非常慢

Question

从 information_schema.tables 查询 * 非常慢。Innodb_stats_on_metadata关闭，select table_name from table很快，只是选择更多字段非常慢(12分钟!)

mysql> select * from tables limit 1;
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| 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 |
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | information_schema | CHARACTER_SETS | SYSTEM VIEW | MEMORY |      10 | Fixed      |       NULL |            384 |           0 |        32869632 |            0 |         0 |           NULL | 2016-12-19 23:55:46 | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=87381 |               |
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (**12 min 27.02 sec**)

其他信息:

mysql> select count(*) from tables;
+----------+ 
| count(*) |
+----------+
|   194196 |
+----------+
1 row in set (0.57 sec)
mysql> show  global variables like '%innodb%metada%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)

Answer 1

选择更多列意味着服务器必须做更多工作 - 询问所有模式中所有表的存储引擎以获得您请求的内容。

information_schema 中的表不是真正的表。它们是服务器内部结构，通过 SQL 接口(interface)公开，在某些情况下允许您查询服务器不存储但必须根据您的要求计算或收集的信息。服务器代码知道您请求哪些列，并且只收集该信息。

LIMIT 1 没有帮助，因为 information_schema 不会像您期望的那样处理 LIMIT ——在返回第一行之前，整个表都会在内存中呈现其余的被丢弃。