Mysql: criar índice em 1,4 bilhões de registros

Question

Eu tenho uma tabela com 1,4 bilhão de registros. A estrutura da tabela é a seguinte:

CREATE TABLE text_page (
    text VARCHAR(255),
    page_id INT UNSIGNED
) ENGINE=MYISAM DEFAULT CHARSET=ascii

O requisito é criar um índice sobre a coluna text .

O tamanho da tabela é de cerca de 34G.

Eu tentei criar o índice pela seguinte declaração:

ALTER TABLE text_page ADD KEY ix_text (text)

Após 10 horas de espera, eu finalmente desisto dessa abordagem.

Existe alguma solução viável sobre este problema?

UPDATE : é improvável que a tabela seja atualizada, inserida ou excluída. A razão pela qual criar índice na coluna text é porque esse tipo de consulta sql seria executada com frequência:

SELECT page_id FROM text_page WHERE text = ?

UPDATE : Eu resolvi o problema particionando a tabela.

A tabela é particionada em 40 partes na coluna text . Em seguida, criar o índice na tabela leva cerca de 1 hora para ser concluído.

Parece que a criação do índice MySQL se torna muito lenta quando o tamanho da tabela se torna muito grande. E o particionamento reduz a tabela em troncos menores.

    

Answer 1

Poderia ser seu sistema apenas não está à altura da tarefa? Eu não uso o MySQL (SQL Server aqui), mas eu sei a dificuldade de indexar uma tabela de entrada de 800 milhões. Basicamente .... você precisa do hardware certo para isso (como em: muitos discos rápidos). Agora eu uso quase uma dúzia de Velociraptors e a performance é ótima;)

SQL Servers (não como MS SQL Server, mas como servidores de banco de dados usando SQL), vivem e morrem com acesso ao disco, e os discos normais simplesmente não estão à altura da tarefa de operações maiores.

    

Answer 2

Você pode querer criar um índice em primeiro (por exemplo, 10) caracteres do campo de texto.

Do Documentos:

Índices podem ser criados usando apenas a parte principal dos valores da coluna, usando a sintaxe col_name (length) para especificar um tamanho de prefixo de índice:

CREATE INDEX ix_text ON text_page (text(10))

    

Answer 3

Defina o sort_buffer_size para 4 GB (ou o quanto você puder dependendo da quantidade de memória que tiver).

Neste exato momento, o índice de criação está fazendo uma espécie, mas como você tem um sort_buffer_size de 32 MB, basicamente é necessário espancar o disco rígido desnecessariamente.

    

Answer 4

Se você não precisar fazer consultas como:

SELECT page_id FROM text_page WHERE text LIKE '?%';

Sugiro criar uma nova coluna hash e indexar a tabela pela coluna. O tamanho total da tabela + índice pode ser muito menor.

UPD : Aliás, 1,4 bilhão de inteiros de chaves primárias ocupam cerca de 6 GB, ou seja, o comprimento médio da string é menor que 30 caracteres, ou seja, a indexação em um prefixo pode ser mais preferível.

Você também deve dar uma olhada no mecanismo de armazenamento MERGE .

    

Answer 5

Eu resolvi o problema particionando a tabela.

A tabela é particionada em 40 partes na coluna text . Em seguida, criar o índice na tabela leva cerca de 1 hora para ser concluído.

Parece que a criação do índice MySQL se torna muito lenta quando o tamanho da tabela se torna muito grande. E o particionamento reduz a tabela em troncos menores.

    

Answer 6

Uma maneira de fazer isso é criar uma nova tabela com o conjunto de índices e copiar os dados para a nova tabela.

Além disso, verifique se você tem espaço temporário suficiente.

    

Answer 7

Caso você ainda esteja se perguntando como fazer isso da melhor maneira, sugiro que use uma ferramenta de alteração de tabela on-line.

Existem muitos deles na internet, um dos mais famosos são:

• Alteração do esquema on-line do Percona (eu uso este)
  link
• Facebook OSC, não consegui encontrar uma URL em que você pudesse fazer o download

Temos os mesmos problemas com tabelas grandes (mais de 500 mil registros) e a alteração é perfeita. Cria uma nova tabela tmp, adiciona trigger na tabela original (para os novos registros update / delete / insert) e no meio tempo copia todos os registros para a nova tabela (com a nova estrutura)

Boa sorte!