Início / dba / Perguntas / 151769
Accepted
Programster
Asked: 2016-10-08 23:53:39 +0800 CST

MySQL - Diferença entre usar count(*) e information_schema.tables para contar linhas

  • 772

Eu quero uma maneira rápida de contar o número de linhas na minha tabela que tem vários milhões de linhas. Encontrei o post " MySQL: Fastest way to count number of rows " no Stack Overflow, que parecia resolver meu problema. Bayuah forneceu esta resposta:

SELECT
    table_rows "Rows Count"
FROM
    information_schema.tables
WHERE
    table_name="Table_Name"
AND
    table_schema="Database_Name";

O que eu gostei porque parece uma pesquisa em vez de uma varredura, então deve ser rápido, mas decidi testá-lo contra

SELECT COUNT(*) FROM table

para ver quanta diferença de desempenho havia.

Infelizmente, estou recebendo respostas diferentes, conforme mostrado abaixo:

insira a descrição da imagem aqui

Pergunta

Por que as respostas são diferentes em aproximadamente 2 milhões de linhas? Estou supondo que a consulta que executa uma verificação completa da tabela é o número mais preciso, mas existe uma maneira de obter o número correto sem ter que executar essa consulta lenta?

Corri ANALYZE TABLE data_302, que completou em 0,05 segundos. Quando executei a consulta novamente, agora obtive um resultado muito mais próximo de 34384599 linhas, mas ainda não é o mesmo número select count(*)de 34906061 linhas. A tabela de análise retorna imediatamente e processa em segundo plano? Acho que vale a pena mencionar que este é um banco de dados de teste e não está sendo gravado no momento.

Ninguém vai se importar se é apenas um caso de dizer a alguém o tamanho de uma tabela, mas eu queria passar a contagem de linhas para um pouco de código que usaria essa figura para criar consultas assíncronas de "tamanho igual" para consultar o banco de dados em paralelo, semelhante ao método mostrado em Aumentando o desempenho de consultas lentas com a execução de consultas paralelas por Alexander Rubin. Do jeito que está, vou pegar o id mais alto SELECT id from table_name order by id DESC limit 1e espero que minhas tabelas não fiquem muito fragmentadas.

mysql mysql-5.6

2 respostas

  1. Best Answer

    Existem várias maneiras de "contar" linhas em uma tabela. O que é melhor depende dos requisitos (precisão da contagem, com que frequência é realizada, se precisamos contar a tabela inteira ou com variáveis where​​e group bycláusulas, etc.)

    • a) da maneira normal. Basta contá -los. 

      select count(*) as table_rows from table_name ;

      Precisão : contagem 100% precisa no momento da execução da consulta.
      Eficiência : Não é bom para mesas grandes. (para tabelas MyISAM é espetacularmente rápido, mas ninguém está usando MyISAM hoje em dia, pois tem tantas desvantagens em relação ao InnoDB. O "espetacularmente rápido" também se aplica apenas ao contar as linhas de uma tabela MyISAM inteira - se a consulta tiver uma WHEREcondição, ela ainda precisa varrer a tabela ou um índice.)
      Para tabelas InnoDB, depende do tamanho da tabela, pois o mecanismo precisa varrer toda a tabela ou um índice inteiro para obter a contagem precisa. Quanto maior a mesa, mais lento fica.

    • b) usando SQL_CALC_FOUND_ROWSe FOUND_ROWS(). Pode ser usado em vez da maneira anterior, se também quisermos um pequeno número de linhas (alterando o LIMIT). Eu vi isso usado para paginação (para obter algumas linhas e ao mesmo tempo saber quantas são int total e calcular o número de pgegs). 

      select sql_calc_found_rows * from table_name limit 0 ; 
select found_rows() as table_rows ;

      Precisão : o mesmo que o anterior.
      Eficiência : o mesmo que o anterior.

    • c) usando as information_schematabelas, como a questão vinculada:

      select  table_rows
from    information_schema.tables
where   table_schema = 'database_name'
  and   table_name = 'table_name' ;

      Precisão : Apenas uma aproximação. Se a tabela for o destino de inserções e exclusões frequentes, o resultado pode estar muito longe da contagem real. Isso pode ser melhorado executando com ANALYZE TABLEmais frequência.
      Eficiência : Muito bom, não toca a mesa.

    • d) armazenar a contagem no banco de dados (em outra tabela "counter" ) e atualizar esse valor toda vez que a tabela tiver uma inserção, exclusão ou truncar (isso pode ser feito com gatilhos ou modificando os procedimentos de inserção e exclusão) .
      Isso, é claro, colocará uma carga adicional em cada inserção e exclusão, mas fornecerá uma contagem precisa.

      Precisão : 100% de contagem precisa.
      Eficiência : Muito bom, precisa ler apenas uma única linha de outra tabela.
      No entanto, ele coloca carga adicional no banco de dados.

    • e) armazenar ( caching ) a contagem na camada de aplicação - e usar o 1º método (ou uma combinação dos métodos anteriores). Exemplo: execute a consulta de contagem exata a cada 10 minutos. No tempo médio entre duas contagens, use o valor armazenado em cache.

      Precisão : aproximação, mas não muito ruim em circunstâncias normais (a menos que milhares de linhas sejam adicionadas ou excluídas).
      Eficiência : Muito bom, o valor está sempre disponível.

    • 38

  2. Para INNODBvocê desejar information_schema.INNODB_SYS_TABLESTATS.NUM_ROWSdados precisos de contagem de linhas da tabela, em vez de information_schema.TABLES.TABLE_ROWS.

    Eu postei mais detalhes aqui: https://stackoverflow.com/questions/33383877/why-does-information-schema-tables-give-such-an-unstable-answer-for-number-of-ro/49184843#49184843

    • 1

relate perguntas