选择information_schema查询太慢了

Question

SELECT * 
FROM information_schema.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA = DATABASE() 
  AND CONSTRAINT_NAME   = 'FK_FOLDER_FILTER_CONTRACT_CATEGORY_CONTRACT_CATEGORY' 
  AND CONSTRAINT_TYPE   = 'FOREIGN KEY';

上面的查询大约需要2分钟才能执行，还有其他的替代查询吗?或者如何优化这个查询？

Answer 1

对于grins，我在5.7上EXPLAINed您的查询：

+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | TABLE_CONSTRAINTS | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned all databases |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+

8.0：

+----+--------------+--------------+------------+--------+--------------------+-------------+---------+---------------------+------+----------+----------------------------------------------------+
| id | select_type  | table        | partitions | type   | possible_keys      | key         | key_len | ref                 | rows | filtered | Extra                                              |
+----+--------------+--------------+------------+--------+--------------------+-------------+---------+---------------------+------+----------+----------------------------------------------------+
|  1 | PRIMARY      | <derived2>   | NULL       | ref    | <auto_key0>        | <auto_key0> | 424     | const,const,const   |   10 |   100.00 | NULL                                               |
|  2 | DERIVED      | cat          | NULL       | index  | PRIMARY            | name        | 194     | NULL                |    1 |   100.00 | Using index                                        |
|  2 | DERIVED      | idx          | NULL       | ALL    | table_id           | NULL        | NULL    | NULL                |  244 |    40.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED      | tbl          | NULL       | eq_ref | PRIMARY,schema_id  | PRIMARY     | 8       | mysql.idx.table_id  |    1 |   100.00 | Using where                                        |
|  2 | DERIVED      | sch          | NULL       | eq_ref | PRIMARY,catalog_id | PRIMARY     | 8       | mysql.tbl.schema_id |    1 |   100.00 | Using where                                        |
|  3 | UNION        | cat          | NULL       | index  | PRIMARY            | name        | 194     | NULL                |    1 |   100.00 | Using index                                        |
|  3 | UNION        | fk           | NULL       | index  | table_id           | table_id    | 202     | NULL                |   63 |   100.00 | Using index; Using join buffer (Block Nested Loop) |
|  3 | UNION        | tbl          | NULL       | eq_ref | PRIMARY,schema_id  | PRIMARY     | 8       | mysql.fk.table_id   |    1 |   100.00 | Using where                                        |
|  3 | UNION        | sch          | NULL       | eq_ref | PRIMARY,catalog_id | PRIMARY     | 8       | mysql.tbl.schema_id |    1 |   100.00 | Using where                                        |
|  4 | UNION        | cc           | NULL       | ALL    | table_id           | NULL        | NULL    | NULL                |    1 |   100.00 | NULL                                               |
|  4 | UNION        | cat          | NULL       | index  | PRIMARY            | name        | 194     | NULL                |    1 |   100.00 | Using index; Using join buffer (Block Nested Loop) |
|  4 | UNION        | tbl          | NULL       | eq_ref | PRIMARY,schema_id  | PRIMARY     | 8       | mysql.cc.table_id   |    1 |   100.00 | Using where                                        |
|  4 | UNION        | sch          | NULL       | eq_ref | PRIMARY,catalog_id | PRIMARY     | 8       | mysql.tbl.schema_id |    1 |   100.00 | Using where                                        |
| NULL | UNION RESULT | <union2,3,4> | NULL       | ALL    | NULL               | NULL        | NULL    | NULL                | NULL |     NULL | Using temporary                                    |
+----+--------------+--------------+------------+--------+--------------------+-------------+---------+---------------------+------+----------+----------------------------------------------------+
14 rows in set, 1 warning (0.01 sec)

旧的代码需要打开所有的表，所以如果有很多表，就会很慢。

8.0代码从InnoDB表中获取，但要经历很多循环。

表的数目不一样，所以我无法进行有效的比较，也不知道您有多少表。

Answer 2

information_schema不是真正的数据库。若要预置此查询，需要打开表。

information_schema的内部代码没有经过优化，它将经常为请求的整个表生成数据，而不考虑查询。因此，无论当前表如何，您的查询都可以打开每个表。

Answer 3

通过在INFORMATION_SCHEMA 5.6上将AND TABLE_SCHEMA='DB_NAME'添加到查询中，我能够将针对MySQL的SELECT查询从12s减少到0.001s。

在(12S)之前：

SELECT
  AVG_ROW_LENGTH
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_NAME = 'myTableName';

在(0.001 s)之后：

SELECT
  AVG_ROW_LENGTH
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_NAME = 'myTableName'
  AND TABLE_SCHEMA = 'myDbname';