Назад | Перейти на главную страницу

Найдите индексы MySQL без количества элементов

Недавно я обнаружил в моем журнале медленных запросов несколько запросов, которые должны были использовать индексы. При исследовании индексов в phpmyadmin он показывает нулевое или пустое количество элементов. Я не уверен, что вызывает это, но мне нужно найти способ определить эту проблему, не проверяя вручную более 200 таблиц.

Есть ли сценарий или запрос, которые я могу использовать для поиска этих «поврежденных» индексов? Если да, могу ли я принудительно перестроить индекс?

mysql indexes

Ты можешь использовать 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';