在information_schema上選擇查詢太慢了

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 分鐘來執行是否有任何其他替代查詢或者我們如何優化這個查詢

information_schema 不是真正的數據庫。要執行此查詢，它需要打開表。

information_schema 的內部程式碼沒有優化，它會經常為請求的整個表生成數據，而不考慮查詢。因此，無論目前表如何，您的查詢都可能打開每個表。

對於笑容，我EXPLAINed對 5.7 的查詢：

+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| 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 表中獲取，但經過了很多麻煩。

桌子的數量不一樣，所以我無法進行有效的比較，我不知道你有多少張桌子。

引用自：https://dba.stackexchange.com/questions/260592