Início / dba / Perguntas / 45679
Accepted
user658182
Asked: 2013-07-04 12:23:05 +0800 CST

Como posso otimizar melhor minhas tabelas e uma consulta LEFT JOIN para encontrar itens que ainda não existem na tabela certa?

  • 772

Eu tenho duas tabelas que representam uma lista de urls e seus índices de palavras relacionadas. Aqui estão as definições de tabela para referência.

desc urllist;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| url   | text                | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

e

desc wordlocation;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| urlid    | bigint(20) unsigned | NO   |     | NULL    |       |
| wordid   | bigint(20) unsigned | NO   |     | NULL    |       |
| location | int(10) unsigned    | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+

O aplicativo de software é um web spider. Ele rastreia uma lista de URLs, extrai esses URLs e os insere na urllisttabela. Em seguida, um indexador verifica quais urls ainda não foram indexados e, em seguida, procede à indexação dos referidos urls.

Aqui está a consulta que estou usando para encontrar itens na tabela da esquerda ( urllist) que ainda não foram indexados na tabela da direita ( wordlocation). Esta consulta é sugerida no site mysql.com :

select * from urllist ul 
left join wordlocation wl on ul.id = wl.urlid 
where wl.urlid IS NULL;

No momento em que escrevo, meu banco de dados de teste tem apenas 600 URLs indexados e a tabela de localização de palavras tem 1,3 milhão de linhas. No entanto, minha CPU está em 100% e o máximo que esperei para ver se a consulta seria concluída é meia hora (o que, aliás, nunca aconteceu).

Para ser completo, aqui está a explicação da consulta:

explain select * from urllist ul left join wordlocation wl on ul.id = wl.urlid where wl.urlid IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                   |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
|  1 | SIMPLE      | ul    | ALL  | NULL          | NULL | NULL    | NULL |   50364 |                         |
|  1 | SIMPLE      | wl    | ALL  | NULL          | NULL | NULL    | NULL | 1351371 | Using where; Not exists |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+

Preciso que essa consulta seja concluída em segundos, não em minutos. Além disso, estou preocupado com a escalabilidade. Eu tenho 40.000 urls únicos esperando para serem adicionados ao índice, então como posso levar isso em consideração com minha tabela e design de consulta? 400.000 URLs?

Apenas algumas notas sobre minhas decisões sobre a estrutura atual da mesa.

Não tenho intenção de parar em 400.000 urls, mas talvez bigint(20) seja um pouco zeloso demais?

Url como texto é por razões mais práticas. Eu indexo muitos domínios asiáticos e de outros idiomas estrangeiros que não aparecem como seus equivalentes Kanji ou outros caracteres no banco de dados e frequentemente ocupam mais de 255 caracteres.

Estou usando MySQL. Estou definitivamente aberto a sugestões para melhorar o design de tabelas e consultas. Por favor, deixe-me saber se eu posso fornecer mais informações.

mysql database-design

2 respostas

  1. Primeiro, sua consulta está correta. Você não precisa das wordlocationcolunas (elas serão todas de NULLqualquer maneira), então eu mudaria select *para select ul.*:

    SELECT ul.* FROM urllist AS ul 
LEFT JOIN wordlocation AS wl ON ul.id = wl.urlid 
WHERE wl.urlid IS NULL;

    Existem mais duas maneiras pelas quais esse tipo de consulta (anti-junção ou anti-semijoin) geralmente é escrita. Usar NOT INwhich não é recomendado se as 2 colunas de junção não forem ambas não anuláveis. Suas urlidcolunas não são anuláveis, então isso também funcionaria:

    SELECT ul.* FROM urllist AS ul 
WHERE ul.id NOT IN 
      ( SELECT wl.urlid
        FROM wordlocation AS wl 
      ) ;

    Ou usando NOT EXISTS:

    SELECT ul.* FROM urllist AS ul 
WHERE NOT EXISTS
      ( SELECT 1
        FROM wordlocation AS wl
        WHERE ul.id = wl.urlid 
      ) ;

    Todas as três consultas resultam em planos de execução e eficiência muito semelhantes no MySQL. Portanto, você deve testar sua distribuição de dados (e tamanhos de tabela ) e a versão do MySQL que está usando, para decidir qual consulta usar.

    A consulta é obviamente lenta agora por causa do índice óbvio ausente em wordlocation (urlid). A eficiência melhorará (dramaticamente) quando você adicionar esse índice, mas não é tudo o que está faltando nesta tabela.

    Você não tem chave primária definida. Verifique e pense sobre sua modelagem e descubra quais colunas identificam exclusivamente as linhas nesta tabela. Meu palpite é que você está processando páginas da web, extraindo palavras delas e armazenando todas (ou algumas das) palavras e suas localizações nas páginas da web. Se estiver correto, você pode usar o (urlid, location)como a chave primária:

    ALTER TABLE wordlocation
  ADD PRIMARY KEY (urlid, location) ;

    E se você fizer isso, não precisará de um índice separado em (urlid). Você provavelmente terá outras consultas, essa busca por palavras (acho que você também tem uma wordlisttabela), então você também precisará de um índice on (wordid)- ou on (wordid, location)ou algumas outras combinações. Os índices necessários dependem de quais consultas você planeja executar nessas tabelas.

    Para o urllist.urltipo de dados, prefiro usar VARCHAR(x)em vez de TEXT. Qual é o URL mais longo que você poderia ter? O comprimento xpode ser de até 65535 e, se você quiser indexá-lo, o índice pode ter até os primeiros 255 caracteres. (Presumo que você esteja armazenando os URLs lá e não as páginas da Web reais. Se você armazenar páginas da Web, desconsidere este parágrafo.)

    • 3
  2. Best Answer

    Para ajudar com seu problema imediato, adicionar um índice em wordlocation.urlid ajudará muito sua consulta.

    Para escalabilidade, parece que sua melhor rota pode ser adicionar um campo à urllist que possa ser facilmente referenciado para ver quais urls foram indexados. Por exemplo, uma coluna tinyint chamada indexedcom um padrão de zero (portanto, novas entradas são sempre zero). Então, quando você indexar um URL, atualize esta coluna para um.

    • 1

relate perguntas