Início / dba / Perguntas / 339426
Accepted
Trevor D
Asked: 2024-05-12 11:15:04 +0800 CST

minhas expectativas para o mysql são irrealistas?

  • 772

Acabei de criar meu primeiro banco de dados e tabela MySQL, mas fiquei surpreso com o desempenho lento de uma simples instrução select. Minha tabela tem 400 milhões de linhas e minha instrução select retorna cerca de 100.000 linhas, mas demorou 14 minutos! Não tenho certeza se minha configuração está errada ou se minhas expectativas em relação ao mysql eram muito altas. Qual seria o tempo esperado para uma tabela bem projetada retornar 100.000 linhas de uma tabela de 400 milhões de linhas? Esta é a minha configuração:

CREATE TABLE CALLS (
quote_date DATE,
quote_time TIME,
expiration DATE,
delta decimal(4,3),
mid decimal(8,4)
);

CREATE INDEX idx_quote_date ON CALLS (quote_date);
CREATE INDEX idx_quote_time ON CALLS (quote_time);
CREATE INDEX idx_expiration ON CALLS (expiration);
CREATE INDEX idx_delta on CALLS (delta);
CREATE INDEX covering_index ON CALLS (quote_date, quote_time, expiration, delta);

Minha tabela serve apenas para ler dados com instruções select, então faço os índices depois de carregar todos os dados.

Minha seleção é:

select * from CALLS where DELTA BETWEEN 0.4 and 0.6;

mysql

4 respostas

  1. Best Answer

    P: O que o plano de consulta mostra?

    R: Use EXPLAIN ANALYZEpara ver.

    Meu palpite é que ele está verificando uma grande parte, senão toda a tabela, para localizar seus dados. Isso ocorre devido aos seguintes problemas:

    • Sua consulta está mal escrita porque usa SELECT *which é um antipadrão.
    • Seus índices não estão definidos corretamente para a consulta que você está testando.
    • 400 milhões de linhas não são triviais, nem verificar 100.000 delas.

    Maneiras de melhorar seu caso de teste:

    • Não use SELECT *e, em vez disso, liste explicitamente as colunas que deseja selecionar.
    • Defina um índice composto que inclua todas essas colunas, por exemplo, CREATE INDEX idx_delta_expiration_mid on CALLS (delta, expiration, mid);se sua consulta for SELECT delta, expiration, mid FROM CALLS where DELTA BETWEEN 0.4 and 0.6;, por exemplo. Isso tornará o índice aplicável para uso, de preferência com a busca de localizar com eficiência os dados de seu interesse, em vez de verificar a tabela.
    • 2

  2. Essa consulta provavelmente usará INDEX(delta)o que você possui. Será realizado da seguinte forma (pseudocódigo):

    1. Reach into the B+Tree of INDEX(delta) to find 0.4 (fast)
2. Scan forward until 0.6.
2a. For each index entry, reach over into the main BTree
       using the PRIMARY KEY to find `*` (as in `SELECT *`)
2b. Send all 5 columns to the user.

    As idas e vindas em "2a" são bastante caras, especialmente se a mesa for maior que innodb_buffer_pool_size.

    É perverso não especificar um PRIMARY KEY; um foi fornecido para você. (Este detalhe não afeta o desempenho.)

    Seu "covering_index" não está cobrindo (para esta consulta) porque midestá faltando.

    A ordem das colunas em um índice composto é importante. (Mas não neste exemplo.)

    Se você não precisar de todas as colunas, seria mais rápido ter um índice composto começando com delta (para que fosse usado) e contendo todas as colunas necessárias (cobertura). (Como JD aponta.)

    Mais sobre indexação: Index Cookbook

    • 1

  3. Observações gerais:

    Onde está seu PRIMARY KEY?

    Parece-me que esses dados são imutáveis? Com isso quero dizer que um dado callé uma questão de registro histórico, em vez de estar sujeito a múltiplas atualizações.

    A partir daqui , obtemos (advertência - não tenho certeza se é com isso que estamos lidando):

    Uma opção de compra é um contrato entre um comprador e um vendedor para comprar uma determinada ação a um determinado preço até uma data de vencimento definida. O comprador de uma opção de compra tem o direito, e não a obrigação, de exercer a opção de compra e comprar as ações. Por outro lado, o vendedor da opção de compra tem a obrigação e não o direito de entregar o estoque se for cedido pelo comprador.

    Você não tem nenhum identificador de estoque - isso me intriga - certamente você deveria ter um campo como identifier CHAR(4)- ou qualquer que seja o comprimento dos identificadores do seu sistema - se for variável, use VARCHAR(n)- storage = n + 1 byte.

    Você pode armazenar DATEe TIMEjuntos como um TIMESTAMP, que pode conter valores entre '1970-01-01 00:00:01' (UTC) e '2038-01-19 03:14:07' (UTC). Se você não precisar de mais de 1s de precisão, isso economizará espaço - 4 bytes versus 6.

    Além disso, quanto ao seu PK, a partir daqui , você não precisa de um substituto PRIMARY KEY(normalmente INTEGER) se seus dados não estiverem mudando muito, o que, como supus acima, deveria ser o caso do que é, essencialmente, uma tabela de registro.

    1ª possibilidade:

    Então, eu projetaria assim (todo o código abaixo está disponível no violino aqui ):

    CREATE TABLE calls 
(
  stock_id    CHAR(4)      NOT NULL,
  quote_ts    TIMESTAMP    NOT NULL,
  expiry_date DATE         NOT NULL, 
  delta       DECIMAL(4,3) NOT NULL,
  mid         DECIMAL(8,4) NOT NULL,

  PRIMARY KEY (stock_id, quote_ts, expiry_date, delta, mid)
);

    Você pode ter um índice deltada seguinte maneira:

    CREATE INDEX delta_ix ON calls (delta);

    Observe que se você fizer isso, você receberá um INDEX SCANerro ao executar sua consulta (usando EXPLAIN ANALYZE):

    EXPLAIN
-> Filter: ((calls.delta >= 4.000) and (calls.delta <= 6.000))  (cost=0.55 rows=3) (actual time=0.0802..0.0847 rows=3 loops=1)
    -> Covering index scan on calls using delta_ix  (cost=0.55 rows=3) (actual time=0.0758..0.0794 rows=3 loops=1)

    2ª possibilidade:

    Agora, se a expiração for determinada pelo número de dias a partir do quote_ts, você poderia armazená-lo como SMALLINT(ou mesmo UNSIGNED TINYINTse nunca for > 255 dias) e usar a DATE_ADD()função e ter expiry_datecomo campo armazenado ( VIRTUAL) - sem espaço, o cálculo é feito no voar. Veja o violino - teste com seu próprio sistema.

    CREATE TABLE calls_bis
(
  stock_id    CHAR(4)      NOT NULL,
  quote_ts    TIMESTAMP    NOT NULL,
  expiry_days SMALLINT     NOT NULL,
  delta       DECIMAL(4,3) NOT NULL,
  mid         DECIMAL(8,4) NOT NULL,

  expiry_date DATE AS (DATE_ADD(DATE(quote_ts), INTERVAL expiry_days DAY)) VIRTUAL,


  PRIMARY KEY (stock_id, quote_ts, expiry_days, delta, mid)
  
);

    Em seguida, crie o mesmo índice deltae execute novamente a consulta - INDEX SCANnovamente. Experimente algumas consultas diferentes - verifique os tamanhos das tabelas (+/- INDEXes.

    Há uma certa sobrecarga associada a cada registro - os registros são armazenados em páginas (novamente, sobrecarga), assim como INDEXes - mais sobrecarga. Portanto, para controlar seu uso/registro real de dados, você teria que carregar 1 milhão de registros e realizar as consultas na parte inferior do violino.

    Alguns pontos a serem observados:

    • Não sei por que você está recuperando 400 mil registros de uma tabela - você está agregando alguma coisa?

    • Não sei como os campos delta e intermediário são produzidos/calculados/derivados. Se puderem GENERATED, há potencial para reduzir o tamanho da sua tabela - e, portanto, o tempo necessário para verificá-la?

    • É melhor ter NOT NULLtantos campos quanto possível - quanto mais informações você fornecer ao otimizador, maiores serão as chances de ele fazer um bom trabalho. Talvez isso não seja possível para o midcampo que não pode ser inserido até a callmetade do seu prazo? (não sei muito sobre negociação).

    • Seus dois índices ( CREATE INDEX covering_index ON CALLS (quote_date, quote_time, expiration, delta);e CREATE INDEX covering_index ON CALLS (quote_date)podem ser substituídos pelo PRIMARY KEY- o primeiro campo de a PKnão precisa de um extra, INDEXpois é o campo inicial de qualquer maneira.

    • Em vez do BETWEENoperador, é melhor usar os matemáticos ( <, <=, >=, >) - estes são inequívocos - são BETWEENinclusivos e exclusivos? A maioria dos servidores simplesmente transformará esse operador nesses outros operadores - veja aqui para confusão!

    • Com o índice delta_ix, ele é usado em uma consulta com registros de solicitações baseadas em valores de delta- veja EXPLAIN ANALYZEno violino.

    • A ordem dos campos deve PKdepender da sua consulta ou consultas mais frequentes. Teste para isso.

    Se você quiser explicar melhor como os campos são produzidos, talvez tenhamos mais oportunidades de reduzir o tamanho do registro e agilizar suas consultas. ps. bem-vindo ao dba.se!

    • 1

  4. Supondo que a coluna "delta" seja distribuída aleatoriamente, sua consulta está selecionando 100 mil linhas aleatórias da tabela de 400 milhões de linhas.

    Existe um índice no delta, mas ele ainda terá que ler 100 mil linhas distribuídas aleatoriamente da tabela.

    14min/100k = 8,4 milissegundos por linha, o que é muito próximo do tempo de acesso aleatório de um disco rígido de 7200rpm... hmm...

    Portanto, acho que você está executando isso em um disco rígido de 7200 rpm. Vai demorar um pouco, não há como evitar. A cabeça da unidade precisa se mover para alcançar os dados.

    As únicas soluções são

    • Use um SSD NVME rápido com alto IOPS aleatório ou coloque RAM suficiente na caixa para manter toda a tabela em cache.

    • Ou use um índice de cobertura em (delta, as demais colunas), que transformará o acesso aleatório em sequencial que é muito mais rápido. Mas vai ocupar muito espaço e demorar um pouco para ser construído.

    Outra solução é utilizar um banco de dados especializado nesse tipo de coisa:

    select sum(value) from mqtt_float where value between 1.095 and 1.1;

┌─────────sum(value)─┐
│ 1830854.5649999972 │
└────────────────────┘

1 row in set. Elapsed: 2.669 sec. Processed 2.13 billion rows, 17.04 GB (798.31 million rows/s., 6.39 GB/s.)
Peak memory usage: 2.80 MiB.

    Não há índice na coluna "valor", porque são dados MQTT, então o índice está ativado (mqtt_topic, timestamp), que também é a ordem da tabela e a chave de particionamento. Assim, ele lê a tabela inteira. Clickhouse compacta essa tabela de 2,1 bilhões de linhas (38 GB) por um fator de cerca de 11, portanto, usa apenas 3,4 GB, que o SSD NVME lê em cerca de 1 segundo, e a consulta é concluída em 2,7s em um PC desktop barato. Isso não inclui o tempo para transportar o conjunto de resultados para o cliente; nesse caso, o conjunto de resultados tem 1,6 milhão de linhas, o que seria cerca de 50 MB, meio segundo em Ethernet gigabit.

    • 1

relate perguntas