Найти индексы MySQL без кардинальности
Недавно я нашел несколько запросов в моем журнале медленных запросов, которые должны были использовать индексы. При исследовании индексов в phpmyadmin он показывает нулевое или пустое количество элементов. Я не уверен, что вызывает это, но мне нужно найти способ идентифицировать эту проблему, не проверяя вручную более 200 таблиц.
Есть ли скрипт или запрос, который я могу использовать, чтобы найти эти "поврежденные" индексы? Если да, могу ли я перестроить индекс?
2 ответа
Ты можешь использовать 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 в некоторых случаях.
Опираясь на то, что Роландо разместил выше. Это исключает любые пустые таблицы из списка значений 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';