Início / dba / Perguntas / 326493
Accepted
Elikill58
Asked: 2023-04-27 14:56:30 +0800 CST

Por que criar uma tabela temporária é mais rápido do que várias condições WHERE?

  • 772

Eu tenho uma tabela assim:

CREATE TABLE Sessions (
  SessionId int(11) NOT NULL AUTO_INCREMENT,
  ExternalId int(11) NOT NULL,
  Active bit(1) NOT NULL DEFAULT b'0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE Sessions
  ADD PRIMARY KEY (SessionId),
  ADD KEY ExternalId (ExternalId),
  ADD KEY Active (Active);

Esta tabela contém mais de 40 milhões de linhas, com apenas 2 mil linhas definidas como ativas e apenas 3 linhas por ExternalId.

Eu quero obter todas as sessões ativas para um arquivo ExternalId. Então, basicamente fiz:

SELECT * FROM Sessions WHERE Active = 1 AND ExternalId = myId;

Mas, leva muito tempo (mais de 3 segundos). Depois de alguma investigação, descobri que este é mais rápido:

SELECT * FROM (SELECT * FROM Sessions WHERE Active = 1) x WHERE x.ExternalId = myId;

Com isso, leva cerca de 0,005 segundos.

Gostaria de saber porque fazer uma tabela temporária é mais rápido que uma basic onde checar com 2 params?

performance

2 respostas

  1. Best Answer

    Na primeira consulta, o mariadb precisa decidir se usará o índice no Active ou no ExternaId. Como Active é um campo booleano, provavelmente assume que metade das linhas (20 milhões) satisfará a condição e usará o índice em ExternalId pensando que pode ser mais eficiente.

    Na segunda, você o força a selecionar primeiro as sessões Ativas, usando o índice Ativo. Se as sessões ativas são apenas uma pequena fração da tabela, isso resulta em algumas linhas, que devem ser verificadas para encontrar aquelas em que ExternalId corresponde àquela que você está selecionando.

    Usar EXPLAIN em ambas as consultas deve fornecer informações sobre qual índice o maridab está usando.

    • 3

  2. Seria muito mais rápido e simples usar um índice composto:

    INDEX(Active, ExternalId)

    A ordem dessas duas colunas não importa. Ao adicionar isso, descarte a corrente INDEX(Active)como desnecessária.

    Um detalhe sobre a terminologia: FROM ( SELECT ... )é uma "tabela derivada". Uma tabela temporária é CREATE TEMPORARY TABLE ...ou uma tabela temporária criada implicitamente. O Optimizer pode ou não usar uma tabela temporária implícita para implementar uma tabela derivada.

    Em sua consulta original, o que é "myid"? É um número? Um argumento para uma rotina armazenada? Uma linha? Se for um número literal simples, eu esperaria que o otimizador usasse INDEX(ExternalId), encontrasse 3 linhas e decidisse quais são Active. Isso deveria ter sido muito rápido. Então... parece que as estatísticas estão confusas. Corra ANALYZE TABLE Sessions;. (Esse tipo de problema é muito raro.)

    • 0

relate perguntas