☰

警告

tag-icon • tag-icon mysql indexes
警告

我最近在慢查询日志中发现了一些应该使用索引的查询。在 phpmyadmin 中调查索引时，它显示为空或空基数。我不确定是什么原因造成的，但我需要找到一种方法来识别这个问题，而无需手动检查 200 多个表。

是否有脚本或查询可用于查找这些“损坏”的索引？如果有，我可以强制重建索引吗？

答案1

您可以使用INFORMATION_SCHEMA.STATISTICS找到有问题的索引：

SELECT table_schema,table_name,index_name FROM information_schema.statistics
WHERE CARDINALITY IS NULL AND SEQ_IN_INDEX = 1 AND INDEX_TYPE <> 'FULLTEXT'
AND table_schema NOT IN ('information_schema','mysql');

您可以使用此查询来创建要运行的脚本ANALYZE TABLE在这些桌子上：

SELECT CONCAT('ANALYZE TABLE ',db,'.',tb,';') FROM
(SELECT table_schema db, table_name tb FROM information_schema.statistics
WHERE CARDINALITY IS NULL AND SEQ_IN_INDEX = 1 AND INDEX_TYPE <> 'FULLTEXT'
AND table_schema NOT IN ('information_schema','mysql')) A;

以下是如何使用查询进行并执行索引统计更新：

SQLSTMT="SELECT CONCAT('ANALYZE TABLE ',db,'.',tb,';') FROM"
SQLSTMT="${SQLSTMT} (SELECT table_schema db, table_name tb FROM "
SQLSTMT="${SQLSTMT} information_schema.statistics"
SQLSTMT="${SQLSTMT} WHERE CARDINALITY IS NULL AND SEQ_IN_INDEX = 1"
SQLSTMT="${SQLSTMT} AND INDEX_TYPE <> 'FULLTEXT'"
SQLSTMT="${SQLSTMT} AND table_schema NOT IN ('information_schema','mysql')) A"
mysql -u... -p... -ANe"${SQLSTMT}" > AnalyzeTablesWithNoCardinalities.sql
mysql -u... -p... < AnalyzeTablesWithNoCardinalities

警告

请记住，索引中并非所有级别都具有基数。请注意，我仅选择所有SEQ_IN_INDEX = 1表示我只查看了第一个索引列没有基数的索引。在某些情况下，这可能适用于 PRIMARY KEY 列。

答案2

基于 Rolando 上面发布的内容。这将从 NULL 索引基数列表中删除所有空表。

SELECT s.table_schema, s.table_name, s.index_name
FROM information_schema.statistics AS s
INNER JOIN information_schema.tables AS t ON s.table_schema = t.table_schema AND s.table_name = t.table_name
WHERE t.TABLE_ROWS <> 0 AND s.CARDINALITY IS NULL AND s.SEQ_IN_INDEX = 1 AND s.INDEX_TYPE <> 'FULLTEXT'
AND s.table_schema NOT IN ('information_schema','mysql') AND t.ENGINE <> 'MEMORY';

相关内容