Início / dba / Perguntas / 267540
Accepted
Peter VARGA
Asked: 2020-05-21 07:38:49 +0800 CST

MyISAM cerca de 5x mais lento que o InnoDB

  • 772

Eu pensei, MyISAMé para tabelas simples e selects devido a sua arquitetura mais rápida que InnoDB. Portanto, alterei o mecanismo para esta tabela de InnoDBpara MyISAM:

CREATE TABLE `table1` (
  `DateTime` datetime NOT NULL,
  `BidHigh` decimal(11,5) NOT NULL,
  `BidLow` decimal(11,5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='1 minute rates';

ALTER TABLE `table1` ADD PRIMARY KEY (`DateTime`);

Estas condições se aplicam:

  1. Estou testando em uma VM SLES 15.1 com 5 GB de RAM e 8 núcleos de CPU em um host que é meu e não precisa atender outra VM [apenas uma informação de que não existe outra VM que possa influenciar no resultado].
  2. Um script PHP está executando milhares das instruções SELECT abaixo.
  3. Existem 24 das tabelas acima no banco de dados que são acessadas no script PHP.
  4. Cada tabela tem cerca de 800 mil linhas.
  5. Entre cada teste eu reinicio o servidor para garantir que haja sempre as mesmas condições. Devido ao fato de a diferença ser tão grande, não estou executando vários testes para obter uma média ...

O tempo de execução:

  • Com InnoDBengine o script roda 199 segundos.
  • Com MyISAMengine o script roda 1'026 segundos. Mais de 5 vezes mais.

Estou executando estas instruções SELECT:

SELECT `DateTime` FROM table1
     WHERE `DateTime` BETWEEN '2018-12-27 07:50:00' AND '2199-12-31 23:59:00'
        AND BidHigh > 0.96604
     ORDER BY `DateTime` LIMIT 1;

-- e --

SELECT MIN( BidLow ) FROM table1
    WHERE `DateTime` BETWEEN '2018-12-27 07:45:00' AND '2199-12-31 23:59:00';

OK, eu descobri que é um problema de índice. Adicionando esses dois índices

ALTER TABLE `table1` ADD UNIQUE `BidHigh` (`DateTime`, `BidHigh`);

-- e --

ALTER TABLE `table1` ADD UNIQUE `BidLow` (`DateTime`, `BidLow`);

corrige o problema de desempenho e agora o script precisa de 245 segundos, ainda mais lento do que InnoDB- isso não é realmente o que eu esperava ...

Adicionar esses índices à InnoDBversão não melhora o desempenho.

Minhas perguntas:

  1. Por que InnoDBnão precisa desses índices e ainda é mais rápido?
  2. Existe uma solução melhor?
  3. E, o que eu aparentemente entendi totalmente errado como a mudança MyISAMcausou um desempenho tão horrível.
mysql performance

3 respostas

  1. Best Answer

    1) O InnoDB também será mais rápido com índices.

    2) InnoDB com indexação apropriada é a melhor solução.

    3) O MyISAM tem sido mais lento que o InnoDB para a maioria das cargas de trabalho por mais de uma década. Há uma diferença fundamental em como a memória e o cache funcionam entre os dois.

    Neste caso, o InnoDB estava escolhendo a primeira linha por chave primária. Como as tabelas do InnoDB são agrupadas por chave primária, isso foi muito rápido e o PK provavelmente já estava na memória quando você criou a tabela.

    Crie um índice no BidHigh e será ainda mais rápido.

    A menos que você tenha uma razão esmagadoramente boa para usar o MyISAM - você não deveria. E se você acha que tem uma razão esmagadoramente boa, deve examiná-la novamente, porque elas são muito poucas em 2020.

    • 2

  2. "MyISAM é melhor ..." é um velho "conto de esposa" que está muito desatualizado. Use InnoDB.

    Os dois Engines usam índices de forma bastante diferente.

    PRIMARY KEY ( DateTime) -- Espero que você não tente armazenar dois registros com o mesmo segundo. PKs são únicos.

    Consulta 1

    SELECT `DateTime` FROM table1
     WHERE `DateTime` BETWEEN '2018-12-27 07:50:00' AND '2199-12-31 23:59:00'
        AND BidHigh > 0.96604
     ORDER BY `DateTime` LIMIT 1;

    Como isso envolve dois intervalos, é essencialmente impossível construir um bom índice para MyISAM ou InnoDB. O Optimizer usará um índice que começa com DateTimee testará todas as linhas da outra coluna. Vamos estudar os índices possíveis:

    PRIMARY KEY(DateTime)

    Para MyISAM, existe um BTree baseado em DateTime, além de um ponteiro para a linha de dados. Ele examinará a linha de dados para obter a BidHighverificação de seu valor.

    Para InnoDB, os dados são ordenados por Data e hora. Assim, não há o extra para obter o BidHigh. Vencedor: InnoDB.

    Em qualquer mecanismo, o Optimizer pode ser inteligente o suficiente para evitar a classificação e chegar ao arquivo LIMIT. Mas isso é arriscado porque depende de quantas linhas precisam ser testadas. Devido a essa variação nos dados, você pode ver facilmente 5x (ou até 500x) lentidão devido ao plano de consulta escolhido . ÍNDICE(DateTime, BidHigh)

    Isso resolve a ineficiência do MyISAM tornando-o um índice de "cobertura". Para o InnoDB, é principalmente um desperdício; o PK é essencialmente um INDEX(DateTime, BidHigh, BidLow), que é apenas um pouco pior do que o índice de 2 colunas.

    INDEX(BidHigh, DateTime)

    Isso provavelmente é mais rápido se houver muito menos linhas correspondendo ao teste de intervalo em BidHign do que o teste de intervalo em DateTime. Mas haveria um tipo antes de chegar ao LIMIT.

    Use EXPLAIN SELECT ...para ver o que ele fez.

    Talvez ESPACIAL

    A primeira consulta precisa de um índice 2D, que não é o que INDEXfornece. Discuto 5 opções para tal, expressas em termos de "latitude/longitude": http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

    O uso SPATIALpode ser viável para a primeira consulta, mas provavelmente não para a segunda.

    Consulta 2

    SELECT MIN( BidLow ) FROM table1
    WHERE `DateTime` BETWEEN '2018-12-27 07:45:00' AND '2199-12-31 23:59:00';

    Para InnoDB: PRIMARY KEY(DateTime)leva a uma varredura de cerca de um ano de dados.

    Para o MyISAM, suspeito que ele usará um índice, a menos que seja exatamente INDEX(DateTime, BidLow), que está "cobrindo".

    23:59:00

    Você está assumindo que não há lances no último minuto do dia?

    Considere usar

    WHERE DateTime >= '...
  AND DateTime  < '...-01-01'
    • 2

  3. Estou adicionando meus comentários como uma resposta porque o comprimento dos campos de comentário não é longo o suficiente.

    Aceitei a resposta de Gordan Bobic , pois ele é um novo membro e acho que a reputação que ele ganha por aceitar sua resposta o está pressionando .

    Como algumas de suas declarações foram confirmadas na resposta de Rick James , isso me mostra que Gordan Bobic entende o que está acontecendo.

    A declaração de Gordan:

    A menos que você tenha uma razão esmagadoramente boa para usar o MyISAM - você não deveria. E se você acha que tem uma razão esmagadoramente boa, deve examiná-la novamente, porque elas são muito poucas em 2020.

    foi um gatilho muito importante para mim, pois percebi que meu entendimento estava errado e tive que me redefinir em relação aos motores usados.

    Eu não mencionei toda a questão, pois não caberia em uma pergunta. Depois de ler as duas respostas, percebi que tenho que reconstruir os bancos de dados. Então, por exemplo, eu tinha um JSONcampo [comprimento máximo de ~6k bytes e um comprimento médio de ~2k bytes] em algumas InnoDBtabelas.

    Devido à alta contagem de linhas, o tamanho dessas InnoDBtabelas também era alto e crescia diariamente. Esta foi a razão pela qual comecei a converter essas InnoDBtabelas em MyISAM. Mas, como mencionado, isso diminuiu consideravelmente o desempenho e, portanto, fiz essa pergunta.

    Extraí esses JSONcampos em uma tabela muito simples MyISAMque possui apenas dois campos [a chave primária e o JSONcampo]. Isso reduziu o tamanho [~30%] e não afeta o desempenho.

    Talvez eu pareça agora um pouco confuso, mas em todo o contexto me ajudou muito e resolveu o problema!

    • 1

relate perguntas