Encontrar índices do MySQL sem cardinalidade

Question

Recentemente, encontrei algumas consultas no meu log de consultas lentas que deveriam estar usando índices. Ao investigar os índices no phpmyadmin, mostra uma cardinalidade nula ou vazia. Não sei ao certo o que causa isso, mas preciso encontrar uma maneira de identificar esse problema sem verificar manualmente mais de 200 tabelas.

Existe um script ou uma consulta que eu possa usar para encontrar esses índices "corruptos"? Se assim for, posso forçar uma reconstrução do índice?

    

Answer 1

Você pode usar INFORMATION_SCHEMA.STATISTICS para encontrar os índices ofensivos:

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');

Você pode usar essa consulta para criar o script para executar ANALYZE TABLE nessas tabelas:

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;

Veja como usar a consulta para criar e executar a atualização de estatísticas de índice:

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

CAVEAT

Tenha em mente que nem todos os níveis de um índice podem ter cardinalidade. Note que eu só escolhi SEQ_IN_INDEX = 1 significa que eu olhei apenas para índices cuja primeira coluna indexada não possui cardinalidade. Isso pode se aplicar às colunas PRIMARY KEY em algumas instâncias.

    

Answer 2

Com base no que Rolando postou acima. Isso corta todas as tabelas vazias da lista de cardinalidade de índice 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';