MySQL 如何修复MySQL中INFORMATION_SCHEMA.key_column_usage的性能问题？

在MySQL数据库中，INFORMATION_SCHEMA.key_column_usage表是一个非常重要的系统表，它包含了所有数据库中的外键约束信息。对于那些使用外键约束的数据库来说，这个表是非常有用的。然而，当使用这个表时，可能会遇到一些性能问题，本文将介绍如何修复这些问题。

阅读更多：MySQL 教程

问题描述

当查询一个大型数据库的INFORMATION_SCHEMA.key_column_usage表时，可能遇到非常严重的性能问题，造成查询时间非常长。

问题解决方案

方案一：使用缓存查询

这是一个非常简单的解决方案，只需要将查询的结果缓存起来即可。这种解决方案在查询缓存不会导致内存问题的前提下，可以大大减少查询的时间。以下是一个使用缓存查询的示例代码：

<?php
// 缓存查询结果
 $cacheKey = 'key_column_usage_cache'; if (($ result = apc_fetch( $cacheKey)) === false) { // 如果缓存不存在则查询数据库$ result = mysqli_query( $con, 'SELECT * FROM INFORMATION_SCHEMA.key_column_usage'); // 将结果存储在缓存中 apc_store($ cacheKey, $result);
}

上面的示例代码中使用了 APC 缓存扩展，但是也可以使用其它缓存方案。

方案二：优化查询语句

这是一个更加复杂的解决方案，需要对查询语句进行调整，以减少查询时间。以下是一个优化查询语句的示例代码：

SELECT kc.TABLE_NAME, kc.COLUMN_NAME, kc.CONSTRAINT_NAME, c.UPDATE_RULE, c.DELETE_RULE
FROM   INFORMATION_SCHEMA.key_column_usage kc JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
ON     kc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
ORDER BY kc.COLUMN_NAME ASC;

上面的查询语句中添加了一个JOIN语句，以避免查询多个表时导致的性能问题。同时，还可以对查询结果进行排序，以提高数据读取效率。

方案三：使用视图查询

这是一个非常简单但十分有效的解决方案，只需要使用一个视图来查询INFORMATION_SCHEMA.key_column_usage表，就可以大大减少查询时间。以下是一个使用视图查询的示例代码：

CREATE VIEW v_key_column_usage AS
    SELECT kc.TABLE_NAME, kc.COLUMN_NAME, kc.CONSTRAINT_NAME, tc.UPDATE_RULE, tc.DELETE_RULE
    FROM   INFORMATION_SCHEMA.key_column_usage kc JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS tc
    ON     kc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME;

SELECT * FROM v_key_column_usage;

上述示例代码创建了一个名为v_key_column_usage的视图，它包含了INFORMATION_SCHEMA.key_column_usage和INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS两个表中的信息。查询视图时，将会自动执行JOIN和WHERE语句，以返回所有符合条件的数据。