Acelerando a contagem de linhas no MySQL

Suponha que, para fins ilustrativos, você esteja executando uma biblioteca usando uma tabela simples de “livros” do MySQL com três colunas:

(id, título, status)

  • id é a chave primária
  • título é o título do livro
  • status pode ser um enum descrevendo o estado atual do livro (por exemplo, DISPONÍVEL, CHECKEDOUT, PROCESSANDO, AUSENTE)

Uma consulta simples para informar quantos livros se enquadram em cada estado é:

SELECT status, COUNT(*) FROM books GROUP BY status 

ou para encontrar especificamente quantos livros estão disponíveis:

 SELECT COUNT(*) FROM books WHERE status = "AVAILABLE" 

No entanto, quando a tabela aumenta para milhões de linhas, essas consultas levam vários segundos para serem concluídas. Adicionar um índice à coluna “status” não parece fazer diferença na minha experiência.

Além de periodicamente armazenar em cache os resultados ou atualizar explicitamente as informações de resumo em uma tabela separada a cada vez que um livro muda de estado (via acionadores ou algum outro mecanismo), há alguma técnica para acelerar esses tipos de consultas? Parece que as COUNT consultas acabam olhando para todas as linhas, e (sem saber mais detalhes) fico um pouco surpreso que essa informação não possa de alguma forma ser determinada a partir do índice.

ATUALIZAR

Usando a tabela de amostra (com uma coluna de “status” indexada) com 2 milhões de linhas, fiz uma avaliação comparativa da consulta GROUP BY. Usando o mecanismo de armazenamento InnoDB, a consulta leva de 3,0 a 3,2 segundos na minha máquina. Usando MyISAM, a consulta leva 0,9-1,1 segundos. Não houve diferença significativa entre contagem (*), contagem (status) ou contagem (1) em nenhum dos casos.

O MyISAM é reconhecidamente um pouco mais rápido, mas fiquei curioso para ver se havia uma maneira de fazer uma consulta equivalente rodar muito mais rápido (por exemplo, 10-50 ms – rápido o suficiente para ser chamado em cada solicitação de página para um site de baixo tráfego) sem a sobrecarga mental de cache e triggersdores. Parece que a resposta é “não há maneira de executar a consulta direta rapidamente”, que é o que eu esperava – eu só queria ter certeza de que não estava faltando uma alternativa fácil.

Então a questão é

Existem algumas técnicas para acelerar esses tipos de consultas?

Bem, na verdade não. Um mecanismo de armazenamento baseado em coluna provavelmente seria mais rápido com essas consultas SELECT COUNT (*), mas teria menos desempenho para praticamente qualquer outra consulta.

Sua melhor aposta é manter uma tabela de resumo via gatilhos. Não tem muita sobrecarga e a parte SELECT será instantânea, independentemente do tamanho da tabela. Aqui está um código clichê:

 DELIMITER // CREATE TRIGGER ai_books AFTER INSERT ON books FOR EACH ROW UPDATE books_cnt SET total = total + 1 WHERE status = NEW.status // CREATE TRIGGER ad_books AFTER DELETE ON books FOR EACH ROW UPDATE books_cnt SET total = total - 1 WHERE status = OLD.status; // CREATE TRIGGER au_books AFTER UPDATE ON books FOR EACH ROW BEGIN IF (OLD.status <> NEW.status) THEN UPDATE books_cnt SET total = total + IF(status = NEW.status, 1, -1) WHERE status IN (OLD.status, NEW.status); END IF; END // 

MyISAM é realmente muito rápido com count (*) a desvantagem é que o armazenamento MyISAM não é tão confiável e é melhor evitar onde a integridade dos dados é crítica.

O InnoDB pode ser muito lento para executar consultas do tipo count (*), pois ele é projetado para permitir várias visualizações simultâneas dos mesmos dados. Então, a qualquer momento, não é suficiente ir ao índice para obter a contagem.

De: http://www.mail-archive.com/mysql@lists.mysql.com/msg120320.html

O database começa com 1000 registros nele Eu inicio uma transação Você inicia uma transação Eu excluo 50 registros Você adiciona 50 registros Eu faço um COUNT ( ) e vejo 950 registros. Você faz um COUNT ( ) e vê 1050 registros. Eu comprometo minha transação – database agora tem 950 registros para todos, mas você. Você confirma sua transação – o database tem 1000 registros novamente.

Como o InnoDB monitora quais registros são “visíveis” ou “modificáveis” em relação a qualquer transação é através de bloqueio em nível de linha, níveis de isolamento de transação e multi-versionamento. http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/4.1/en/innodb-multi-versioning.html

É isso que faz com que a contagem de quantos registros cada pessoa possa ver não seja tão direta.

Então, a linha de fundo é que você precisa olhar para o cache das contagens de alguma forma ao invés de ir para a mesa, se você precisa obter essas informações com freqüência e rapidez.

de: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

O InnoDB não mantém uma contagem interna de linhas em uma tabela. (Na prática, isso seria um pouco complicado devido ao multi-versioning.) Para processar uma instrução SELECT COUNT (*) FROM, o InnoDB deve varrer um índice da tabela, o que leva algum tempo se o índice não estiver inteiramente no buffer. piscina.

A solução sugerida é:

Para obter uma contagem rápida, você precisa usar uma tabela de contagem criada por você mesmo e permitir que seu aplicativo a atualize de acordo com as inserções e exclusões feitas. SHOW TABLE STATUS também pode ser usado se uma contagem de linhas aproximada for suficiente.

Em suma: count (*) (no innoDB) levará muito tempo para tabelas contendo um grande número de linhas. Isso é por design e não pode ser ajudado.

Escreva sua própria solução.

Muitas respostas aqui disseram que um índice não ajudaria, mas no meu caso ele fez …

Minha tabela usava MyISAM e tinha apenas cerca de 100k linhas. A pergunta:

 select count(*) from mytable where foreign_key_id=n 

demorou 7-8 segundos para completar.

Eu adicionei um índice no foreign_key_id :

 create index myindex on mytable (foreign_key_id) using btree; 

Depois de criar o índice, a instrução select relatou um tempo de execução de 0,00 segundos.

Não houve diferença significativa entre contagem (*), contagem (status) ou contagem (1)

count (coluna) retorna o número de linhas onde a coluna é NOT NULL. Como 1 é NOT NULL e o status também é, presumivelmente, NOT NULL, o database otimizará o teste e converterá todos para count (*). Que, ironicamente, não significa “contar linhas onde todas as colunas não são nulas” (ou qualquer outra combinação), significa apenas “contar linhas” …

Agora, voltando à sua pergunta, você não pode ter seu bolo e comê-lo …

  • Se você quer que uma contagem “exata” esteja sempre disponível, então você tem que incrementar e decrementar em tempo real, via gatilhos, o que atrasa suas gravações.

  • Ou você pode usar count (*), mas isso será lento

  • Ou você pode optar por uma estimativa aproximada ou um valor desatualizado e usar o cache ou outras abordagens probabilísticas.

Geralmente, em valores acima de “alguns”, NO-ONE está interessado em uma contagem exata em tempo real. É um arenque vermelho de qualquer maneira, como pelo tempo que você lê, o valor provavelmente terá mudado.