Início / dba / Perguntas / 74693
Accepted
Gili
Asked: 2014-08-23 09:17:05 +0800 CST

Como dividir a mesa em duas sem perder performance?

  • 772

De acordo com https://stackoverflow.com/a/174047/14731 , separar as colunas pouco necessárias libera o cache, permitindo uma recuperação mais rápida das colunas mais usadas.

Eu tenho uma tabela cujas colunas são sempre recuperadas juntas, mas ainda gostaria de dividi-las por motivos de design (reduzir a duplicação em várias tabelas, melhorar a reutilização do código). Por exemplo, tenho tabelas diferentes que usam o mesmo esquema de permissão. Em vez de adicionar colunas de permissão a cada tabela, gostaria de usar uma chave estrangeira para fazer referência a uma tabela de esquema de permissão separada.

Preenchi o MySQL com 1 milhão de linhas, executei consultas em ambas as versões e descobri que a versão com JOIN é ~3x mais lenta (0,9 segundos versus 2,9 segundos).

Aqui estão minhas tabelas:

original
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    third BIGINT NOT NULL
);
part1
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    PRIMARY KEY(id)
);
part2
(
    link BIGINT NOT NULL,
    third BIGINT NOT NULL,
    FOREIGN KEY (link) REFERENCES part1(id)
);

Aqui estão minhas perguntas:

SELECT first, second, third FROM original;
SELECT part1.first, part1.second, part2.third FROM part1, part2 WHERE part2.link = part1.id;

Existe alguma maneira de reduzir a sobrecarga de desempenho do design dividido?

Se você quiser reproduzir esse teste do seu lado, pode usar o seguinte aplicativo Java para gerar o script SQL para preencher o banco de dados:

import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class Main
{
    public static void main(String[] args) throws FileNotFoundException
    {
        final int COUNT = 1_000_000;
        try (PrintWriter out = new PrintWriter("/import.sql"))
        {
            for (int i = 0; i < COUNT; ++i)
                out.println("INSERT INTO original VALUES (" + i + ", " + i + ", 0);");
            out.println("INSERT INTO original VALUES (" + (COUNT - 2) + ", " + (COUNT - 1) +
                ", 1);");
            out.println();
            for (int i = 0; i < COUNT; ++i)
            {
                out.println("INSERT INTO part1 (first, second) VALUES (" + i + ", " + i + ");");
                out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 0);");
            }
            out.println("INSERT INTO part1 (first, second) VALUES (" + (COUNT - 2) + ", " +
                (COUNT - 1) + ");");
            out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 1);");
            out.println();
        }
    }
}
mysql database-design

2 respostas

  1. OPÇÃO #1: Use INT UNSIGNED em vez de BIGINT

    Se os campos não excederem 4,294,967,295, altere-os paraINT UNSIGNED

    ALTER TABLE part1
    MODIFY COLUMN id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
    MODIFY COLUMN first  INT UNSIGNED NOT NULL,
    MODIFY COLUMN second INT UNSIGNED NOT NULL;
ALTER TABLE part2
    MODIFY COLUMN link  INT UNSIGNED NOT NULL,
    MODIFY COLUMN third INT UNSIGNED NOT NULL;

    Tipos de dados menores, especialmente para chaves JOIN, farão com que a mesma consulta seja mais rápida.

    Se os campos não excederem 16,777,215, use MEDIUMINT UNSIGNEDpara colunas ainda menores.

    OPÇÃO #2: Use um buffer de junção maior

    Adicionar isto amy.cnf

    [mysqld]
join_buffer_size = 16M

    Em seguida, faça login no MySQL e execute

    mysql> SET GLOBAL join_buffer_size = 1024 * 1024 * 16;

    A reinicialização do MySQL não é necessária.

    Consulte a documentação do MySQL sobre join_buffer_size

    OPÇÃO #3: Certifique-se de que linkestá indexado

    Como você tem uma FOREIGN KEYreferência, esse é um ponto bastante discutível. Se você não tiver o FOREIGN KEY, verifique se o link está indexado:

    ALTER TABLE part2 ADD UNIQUE KEY (link);

    De uma chance !!!

    ATUALIZAÇÃO 2014-08-22 17:13 EDT

    Eu criei minha própria versão dos dados de amostra usando isto:

    DROP DATABASE IF EXISTS GILI; CREATE DATABASE GILI;
USE GILI
create table original 
(
    id mediumint unsigned not null auto_increment,
    first mediumint unsigned not null,
    second mediumint unsigned not null,
    third mediumint unsigned not null,
    primary key (id)
);
create table part1 like original;
alter table part1 drop column third;
create table part2 select third from original;
alter table part2 add link mediumint unsigned not null first;
alter table part2 add primary key (link);
insert into original (first,second,third) values (1,2,3);
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into part1 (id,first,second) select id,first,second from original;
insert into part2 (link,third) select id,third from original;
select count(1) from (select * from original) A;
select count(1) from (select * from part1 inner join part2 on part1.id = part2.link) A;

    Os resultados são aproximadamente os mesmos que os seus: Cerca de 3x mais lentos. O, eu li seu link stackoverflow. Então comecei a pensar: olha a amostra que usamos. Basicamente, levamos o MySQL ao seu limite para juntar 1 milhão de linhas com 1 milhão de linhas. Este é o pior cenário de junção equitativa. Considerando tudo, o desempenho é muito bom.

    Lembro-me de meus dias de faculdade em que tive que criar uma matriz bidimensional usando listas encadeadas para construir uma matriz esparsa . Se um nó não existisse em uma determinada coordenada, o valor padrão para a matriz era definido como zero no app. Então, imagine isso. Criando uma matriz esparsa de 1000x1000 onde todas as 1000000 (1 milhão) de coordenadas tinham um valor diferente de zero representado. Agora, você tinha pelo menos 2,002 milhões de ponteiros mapeando todos os nós adjacentes. Isso é adicional aos 1 milhão de números inteiros de 4 bytes para os dados. A obtenção de um único valor exigia mais CPU para navegação do que a recuperação dos dados reais.

    Fazer um INNER JOIN de 1 milhão de linhas da parte 1 para a parte 2, onde a parte 2 tem absolutamente todas as chaves, requer mais recursos para navegação (criação de tabela temporária, comparação de chaves, preenchimento de valor). A desnormalização às vezes pode ser desmoralizante se o lado direito de um LEFT JOIN não for muito esparso ou o lado esquerdo do LEFT JOIN for enorme. No seu caso, separar o original em parte1 e parte2 não lhe traz nada se eles tiverem que ser frequentemente referenciados juntos e em massa. Em outras palavras, separar colunas que não formam grupos repetidos não é normalização verdadeira.

    As 3 opções que dei fariam muito bem para a parte 1 e buscar a parte 2 linha por linha.

    Pense nos seguintes casos:

    • você só precisa de algumas linhas da parte 1
      • você confia no MySQL Query Optimizer
      • você refatora a consulta para obter uma seção cruzada para part1 antes de ingressar em part2?
      • você recupera de part2 apenas uma linha por vez?
    • Usando um LEFT JOIN e aprendendo que não há part2 para uma determinada part1
      • definir valor padrão no aplicativo para part2?
      • preencher part2 para essa part1?

    Dar mais atenção a como você planeja recuperar dados, quantos dados você precisa em uma única consulta e como você estrutura a consulta se tornará o fator determinante à medida que você busca um bom desempenho.

    EPÍLOGO

    Você não obterá melhor desempenho por causa do custo para fazer o JOIN acontecer. É como tentar transformar chumbo em ouro (Teoricamente possível, prática e financeiramente impossível) .

    Sua melhor aposta é deixar a mesa em seu estado original.

    • 1
  2. Best Answer

    Esta é exatamente a razão para usar a normalização de forma limitada e após o teste de desempenho. A normalização tem o custo de junções (classificação). O principal objetivo do DWH no 5NF é armazenar dados com segurança, não recuperá-los rapidamente.

    Alternativa 1 Existe um conceito de Visualização Materializada: uma visualização que é salva no disco rígido. O MySQL não o fornece pronto para uso, mas este artigo - Visões materializadas com MySQL - explica como essa funcionalidade pode ser recriada com um SP atualizando/atualizando uma tabela.

    Uma Visualização Materializada (MV) é o resultado pré-calculado (materializado) de uma consulta. Ao contrário de uma VIEW simples, o resultado de uma Visualização Materializada é armazenado em algum lugar, geralmente em uma tabela. Visualizações materializadas são usadas quando uma resposta imediata é necessária e a consulta em que a visualização materializada se baseia levaria muito tempo para produzir um resultado. Visualizações materializadas precisam ser atualizadas de vez em quando. Depende dos requisitos com que frequência uma visualização materializada é atualizada e quão real é seu conteúdo. Basicamente, uma visão materializada pode ser atualizada imediatamente ou adiada, pode ser atualizada totalmente ou até um determinado ponto no tempo. O MySQL não fornece visualizações materializadas por si só.

    Alternativa 2 Você pode tentar alcançar seu projeto fazendo as coisas de outra maneira. Em vez de dividir a tabela principal, crie 2-3 visualizações ou tabelas provenientes da principal. Desta forma você terá tabelas normalizadas para o esquema em estrela com valores distintos e também manterá a tabela principal rápida.

    O ajuste de desempenho é sempre sobre a troca entre CPU (tempo), RAM e IO (taxa de transferência ou espaço). Neste caso, é entre CPU e IO.

    • 1

relate perguntas