查找表的所有外键最有效的方法是什么

Question

如何最有效地获取外键信息(包括更新/删除规则(？

最明显的方法是使用如下查询：

SELECT 
`RC` . `CONSTRAINT_CATALOG` AS `fkCatalog` , 
`RC` . `CONSTRAINT_SCHEMA` AS `fkSchema` , 
`RC` . `CONSTRAINT_NAME` AS `fkName` , 
`RC` . `UPDATE_RULE` AS `onUpdate` ,
`RC` . `DELETE_RULE` AS `onDelete` , 
`RC` . `TABLE_NAME` AS `fkTable` , 
`RC` . `REFERENCED_TABLE_NAME` AS `refTable` , 
`KCU` . `COLUMN_NAME` AS `fkColumn` , 
`KCU` . `REFERENCED_COLUMN_NAME` AS `refColumn` , 
`KCU` . `ORDINAL_POSITION` AS `fkOrdinal` 
FROM 
`INFORMATION_SCHEMA` . `REFERENTIAL_CONSTRAINTS` AS `RC` 
INNER JOIN `INFORMATION_SCHEMA` . `KEY_COLUMN_USAGE` AS `KCU` 
ON 
`KCU` . `CONSTRAINT_SCHEMA` = `RC` . `CONSTRAINT_SCHEMA` AND 
`KCU` . `CONSTRAINT_NAME` = `RC` . `CONSTRAINT_NAME` 
WHERE 
`RC` . `CONSTRAINT_SCHEMA` = ? AND 
`RC` . `TABLE_NAME` = ? 
ORDER BY 
`RC` . `REFERENCED_TABLE_NAME` , `KCU` . `ORDINAL_POSITION`

例如，请参阅中的答案：

如何查找所有具有引用特定table.column的外键并具有这些外键值的表？

https://dba.stackexchange.com/questions/102371/how-to-check-foreign-keys-related-to-a-table

如何查找所有具有引用特定table.column的外键并具有这些外键值的表？

但在一个中等大小的数据库中(总大小约为20GB，20个数据库之间有1000-10000个表(，这可能需要几秒钟的时间(在我的系统上，每次查询几乎需要3秒钟(。

有没有更有效的方法来取它们？

注意：我使用的是MySQL 5.7。

Answer 1

一个解决方案最终是显式引用联接两侧的表和模式。我知道information_schema表(尤其是在MySQL<8.0中(不是典型意义上的表，所以我想它们有时不能有效地进行联接。

我首先尝试颠倒连接顺序，但没有帮助，或者将where放在key_column_usage表上，也没有帮助。不过，在联接的两侧显式地引用模式和表确实起到了作用。

在任何情况下，以下查询平均耗时1-2ms，而上述查询平均耗时不到3秒(从performance_schema的1000个左右查询中得出的数字(。

SELECT 
`RC` . `CONSTRAINT_CATALOG` AS `fkCatalog` , 
`RC` . `CONSTRAINT_SCHEMA` AS `fkSchema` , 
`RC` . `CONSTRAINT_NAME` AS `fkName` , 
`RC` . `UPDATE_RULE` AS `onUpdate` , 
`RC` . `DELETE_RULE` AS `onDelete` , 
`RC` . `TABLE_NAME` AS `fkTable` , 
`RC` . `REFERENCED_TABLE_NAME` AS `refTable` , 
`KCU` . `COLUMN_NAME` AS `fkColumn` , 
`KCU` . `REFERENCED_COLUMN_NAME` AS `refColumn` , 
`KCU` . `ORDINAL_POSITION` AS `fkOrdinal` 
FROM 
`INFORMATION_SCHEMA` . `KEY_COLUMN_USAGE` AS `KCU` 
INNER JOIN 
`INFORMATION_SCHEMA` . `REFERENTIAL_CONSTRAINTS` AS `RC` 
ON 
`KCU` . `CONSTRAINT_SCHEMA` = `RC` . `CONSTRAINT_SCHEMA` AND 
`KCU` . `CONSTRAINT_NAME` = `RC` . `CONSTRAINT_NAME` 
WHERE 
`KCU` . `TABLE_SCHEMA` = ? AND -- The addition of this and the next row are the only real difference.
`KCU` . `TABLE_NAME` = ? AND 
`RC` . `CONSTRAINT_SCHEMA` = ? AND 
`RC` . `TABLE_NAME` = ? 
ORDER BY 
`KCU` . `REFERENCED_TABLE_NAME` , `KCU` . `ORDINAL_POSITION`

Answer 2

这将为您提供有关外键的所有信息

还有更多的信息，leke-referenced列和refencd表

SELECT 
TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;

此外，在mysql5.7中还有这个选项，但只适用于innodb表

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN

这给你一个类型

TYPE位标志的集合，包含有关外键列的信息，一起进行OR运算。1=在删除级联上，2=在更新集NULL上，4=在更新级联上，8=在更新集合NULL上，16=在删除无操作上，32=在更新无操作上。

http://mysql.babo.ist/#/en/innodb-sys-foreign-table.html