Início / dba / Perguntas / 338029
Accepted
O. Jones
Asked: 2024-03-25 23:10:11 +0800 CST

COMPACT é um formato melhor para linhas de comprimento fixo do que DYNAMIC?

  • 772

No MySQL/MariaDB eu tenho esta tabela com linhas de comprimento fixo (sem VARCHAR, TEXT, etc)

CREATE TABLE trigram (
    id BIGINT(20) NOT NULL,
    trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (trigram, id) USING BTREE,
    INDEX id (id) USING BTREE
)
COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;

A tabela tem dezenas de megarows e obtém consultas de produção neste formato

SELECT id FROM trigram 
 WHERE trigram IN ('dba', 'ba.', 'a.s', '.st', 'sta', 'tac', 'ack')
 GROUP BY ID HAVING COUNT(*) = 7

bem como INSERTs e DELETE FROM trigram WHERE id = 12345consultas de manutenção. Os índices são apropriados para os padrões de consulta da tabela.

Esta tabela é um índice trigrama do homem pobre. (Este pobre homem não pode atualizar para o postgreSQL e usar seus índices trigramas integrados, suspiro.) A consulta de exemplo procura ids que contenham strings 'dba.stack'. É muito mais rápido do que content_column LIKE '%dba.stack%'quando a tabela de trigramas é construída.

Editar: O que quero dizer com "melhor"? Mais rápido, mais confiável, menos liberação do buffer pool na produção, menos carga de manutenção para usuários que não são DBA.

Pergunta: Devo definir esta tabela de linhas de comprimento fixo com ROW_FORMAT=COMPACT? Ou é necessário DYNAMIC? Percebi que ocupa um pouco menos espaço em disco com o COMPACT.

Pergunta: Alguma outra sugestão ou aspecto de desempenho com que se preocupar?

Meus usuários (usuários do software WordPress.org) estão principalmente no MariaDB 10.3+, mas alguns estão no MySQL 8 e alguns estão no MySQL 5.7-. Não preciso oferecer suporte a material legado do Antelope ou MyISAM.

Outra edição:

Minha IN()consulta faz uma varredura de intervalo em um conjunto de dados de teste com 180 mil linhas na tabela. A JOINtabela UNION sugerida em uma resposta faz um loop aninhado. A varredura de alcance leva menos tempo. Verdadeiro no MariaDB 10.11, MySQL 8 e MySQL 5.7. Pelo que vale a pena. Parece que a otimização do skip-scan funciona muito bem.

mysql

5 respostas

  1. Outra opção seria usar junções múltiplas, para evitar agrupamento e contagem e aproveitar o fato de que após as primeiras junções há cada vez menos linhas que satisfazem as condições:

    SELECT t1.id FROM trigram t1
JOIN trigram t2 ON (t1.id=t2.id)
JOIN trigram t3 ON (t1.id=t3.id)
JOIN trigram t4 ON (t1.id=t4.id)
JOIN trigram t5 ON (t1.id=t5.id)
JOIN trigram t6 ON (t1.id=t6.id)
JOIN trigram t7 ON (t1.id=t7.id)
WHERE t1.trigram = 'dba' and t2.trigram ='ba.' 
  and t3.trigram = 'a.s' and t4.trigram ='.st' 
  and t5.trigram = 'sta' and t6.trigram ='tac' 
  and t7.trigram = 'ack'
    • 3
  2. Best Answer

    Para a tabela que você mostra, não acho que o formato de linha COMPACT ou DYNAMIC faça alguma diferença significativa.

    No MySQL moderno, o formato de linha DYNAMIC é o padrão. É praticamente o mesmo que COMPACT, exceto que melhora o armazenamento de colunas TEXT e BLOB longas e permite comprimentos de prefixo de índice mais longos. Nenhuma dessas diferenças é relevante para a tabela que você mostra.

    Você disse que o formato de linha anterior resulta em uma tabela menor, mas isso pode ser apenas uma coincidência, já que você provavelmente conseguiu uma desfragmentação ao alterar a tabela de um formato de linha para outro.

    Se você OPTIMIZE TABLEestiver na mesa com o formato de linha DYNAMIC, espero que você a veja desfragmentar e ficar menor também.

    Não há nada no InnoDB que ofereça vantagem para linhas de comprimento fixo. As linhas são sempre localizadas usando ponteiros dentro de uma página.

    MyISAM tem alguma vantagem para linhas de comprimento fixo, porque as linhas podem ser localizadas multiplicando-se pelo comprimento da linha. Mas isso nunca é feito no InnoDB.

    • 3

  3. FIXEDvs DYNAMICfez uma pequena diferença no MyISAM. Para o InnoDB, essencialmente não existe tal coisa.

    COMPACTvs DYNAMICdiz o que acontece quando existe uma coluna "grande" em uma linha. CHAR(3)não se qualifica como "grande".

    "trigrama" soa como um tipo de dados de comprimento fixo, portanto CHAR(3)é apropriado, mas salva apenas o "comprimento" de 1 byte (em relação ao VARCHAR(3).

    O conjunto de caracteres utf8mb4, por outro lado, obriga até mesmo CHARa ter comprimento variável, já que cada caractere pode ter até 4 bytes de largura. Se você precisar apenas de letras em inglês, use Ascii.

    O seguinte reduziria o tamanho da tabela pela metade - se você realmente não precisar id:

    CREATE TABLE trigram (
    trigram CHAR(3) NOT NULL COLLATE 'ascii_general_ci',
    PRIMARY KEY (trigram)
) ENGINE=InnoDB;

    Se você realmente tiver mais colunas na tabela, diga-o!

    Ao escolher um agrupamento e conjunto de caracteres:

    _bin is faster than _general_ci is faster than the others
_ci is needed for case folding (or do you fold the incoming data?)
ascii and latin% are only one byte and faster than others

    O antílope é velho; Baraccuda é o preferido. Mas como o WP parece não conseguir atualizar seus clientes, você deve cuidar de qualquer um deles.

    "Skip-scan" provavelmente será a melhor otimização; parece ter sido adicionado no MySQL 8.0.13; Não sei se está no MariaDB.

    • 3

  4. Count é conhecido por ser lento.

    Mas um dos problemas de desempenho pode ser resolvido. Eliminando a cláusula IN, que precisaria fazer uma varredura completa da tabela, ehivh xou veria se você fizesse um EXPLAIN sua consulta

    SELECT 
    id
FROM
    trigram
        JOIN
    (SELECT 'dba' AS trigram UNION ALL SELECT 'ba.' UNION ALL SELECT 'a.s' UNION ALL SELECT '.st' UNION ALL SELECT 'sta' UNION ALL SELECT 'tac' UNION ALL SELECT 'ack') tr12 ON trigram.trigram = tr1.trigram
GROUP BY ID
HAVING COUNT(*) = 7

    CREATE TABLE trigram (
    id BIGINT(20) NOT NULL,
    trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (trigram, id) USING BTREE,
    INDEX id (id) USING BTREE
)
COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;

    EXPLAIN SELECT id FROM trigram 
 WHERE trigram IN ('dba', 'ba.', 'a.s', '.st', 'sta', 'tac', 'ack')
 GROUP BY ID HAVING COUNT(*) = 7
    eu ia selecione o tipo mesa partições tipo chaves_possíveis chave key_len referência linhas filtrado Extra
    1 SIMPLES trigrama nulo índice PRIMÁRIO, id eu ia 8 nulo 1 100,00 Usando onde; Usando índice 
    EXPLAIN
    SELECT 
        id
    FROM
        trigram
            JOIN
        (SELECT 'dba' AS trigram UNION ALL SELECT 'ba.' UNION ALL SELECT 'a.s' UNION ALL SELECT '.st' UNION ALL SELECT 'sta' UNION ALL SELECT 'tac' UNION ALL SELECT 'ack') tr1 ON trigram.trigram = tr1.trigram
    GROUP BY ID
    HAVING COUNT(*) = 7
    eu ia selecione o tipo mesa partições tipo chaves_possíveis chave key_len referência linhas filtrado Extra
    1 PRIMÁRIO trigrama nulo índice PRIMÁRIO, id eu ia 8 nulo 1 100,00 Usando índice; Usando temporário
    1 PRIMÁRIO <derivado2> nulo TODOS nulo nulo nulo nulo 7 14h29 Usando onde; Usando buffer de junção (hash join)
    2 DERIVADO nulo nulo nulo nulo nulo nulo nulo nulo nulo Nenhuma tabela usada
    3 UNIÃO nulo nulo nulo nulo nulo nulo nulo nulo nulo Nenhuma tabela usada
    4 UNIÃO nulo nulo nulo nulo nulo nulo nulo nulo nulo Nenhuma tabela usada
    5 UNIÃO nulo nulo nulo nulo nulo nulo nulo nulo nulo Nenhuma tabela usada
    6 UNIÃO nulo nulo nulo nulo nulo nulo nulo nulo nulo Nenhuma tabela usada
    7 UNIÃO nulo nulo nulo nulo nulo nulo nulo nulo nulo Nenhuma tabela usada
    8 UNIÃO nulo nulo nulo nulo nulo nulo nulo nulo nulo Nenhuma tabela usada

    violino

    • 2

  5. MySQL 5.7+ suporta indexação ngram para pesquisa de texto completo. As chances são de que isso seja mais rápido do que a implementação de nível superior do mesmo recurso. Defina ngram_token_size=3 e você terá pesquisa de trigrama.

    O encantamento que você precisa é:

    CREATE FULLTEXT INDEX ... WITH PARSER ngram;

    Em seguida, use a sintaxe padrão FTS MATCH/AGAINST para consultá-lo.

    • 1

relate perguntas