Início / dba / Perguntas / 54251
Accepted
dotancohen
Asked: 2013-12-03 04:58:58 +0800 CST

Em quais campos indexar? Campos consultados (WHERE) ou campos retornados (SELECT)

  • 772

Eu tenho uma grande tabela de banco de dados MySQL (~ 1 milhão de linhas e crescendo) em uma instância AWS RDS Medium :

mysql> describe clients;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| name            | varchar(500)  | YES  |     | NULL    |                |
| address         | varchar(500)  | YES  |     | NULL    |                |
| city            | varchar(200)  | YES  |     | NULL    |                |
| state           | varchar(100)  | YES  |     | NULL    |                |
| zip             | varchar(50)   | YES  |     | NULL    |                |
| country         | varchar(50)   | YES  |     | NULL    |                |
| phone           | varchar(20)   | YES  | UNI | NULL    |                |
| source          | varchar(20)   | YES  | MUL | NULL    |                |
| campaign        | varchar(200)  | YES  |     | NULL    |                |
| search_term     | varchar(200)  | YES  |     | NULL    |                |
| search_location | varchar(200)  | YES  |     | NULL    |                |
| added           | datetime      | YES  |     | NULL    |                |
| email           | varchar(150)  | YES  |     | NULL    |                |
| website         | varchar(150)  | YES  |     | NULL    |                |
| full_output     | varchar(5000) | YES  |     | NULL    |                |
| client          | varchar(50)   | YES  |     | NULL    |                |
| is_deleted      | int(2)        | YES  |     | 0       |                |
| is_valid        | int(2)        | YES  |     | 1       |                |
+-----------------+---------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

Frequentemente preciso executar uma variante da seguinte consulta:

SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;

E o relevante EXPLAIN:

mysql> EXPLAIN SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
|  1 | SIMPLE      | clients    | range | source        | source | 63      | NULL | 1168144 | Using where |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
1 row in set (0.03 sec)

Quais otimizações devo fazer? Devo adicionar índices nos campos namee zipou nos campos addede source?

mysql optimization

1 respostas

  1. Best Answer

    A resposta aceita ignora o conceito de índices de cobertura e também não menciona a importância de índices em várias colunas, juntos em um índice.

    Um único índice em ambas as colunas na WHEREcláusula:

    ALTER TABLE clients ADD KEY(source,added) -- adding this 
ALTER TABLE clients ADD KEY(added,source) -- or this

    ...geralmente irá ajudá-lo mais do que um índice individual em cada coluna, porque o otimizador pode selecionar apenas para usar uma das duas colunas. Qual dos índices acima ajudará mais depende da distribuição de valores em "fonte" e "adicionado". O índice selecionado usado para a consulta aparecerá em "chave" na EXPLAINsaída. "Usar where" geralmente significa que, dentre as linhas que o plano de consulta escolhido resultará na busca, o servidor percebe que algumas delas ainda não atenderão aos critérios de seleção e terão que ser posteriormente filtradas pelo servidor (como no exemplo, onde potencialmente um grande número teria que ser filtrado, já que nenhum índice foi usado).

    Um índice de cobertura também pode ser particularmente valioso porque, ao contrário da afirmação de que "os campos dentro de uma linha são rápidos e fáceis para o mecanismo obter", eles são apenas mais rápidos e fáceis do que encontrar as linhas examinando a tabela inteira - eles ainda leva tempo e consome recursos.

    É aqui que entram os índices de cobertura. Adicionar um índice com (source,added,zip,name) provavelmente melhoraria seu desempenho substancialmente, porque uma vez que o servidor encontrou as linhas relevantes usando o índice, ele não precisa procurar o resto dos dados porque os dados estão realmente dentro do índice. Quando um índice de cobertura está sendo usado, a coluna "chave" de Explain conterá o nome do índice que está sendo usado, e a coluna "Extra" incluirá "Using Index" (ou seja, usar o índice para realmente recuperar os dados, não apenas encontre.)

    Portanto, embora seja verdade que você indexa com base em seus critérios de seleção, essa não é toda a história.

    Observe também que não importa o que seja indexado, um índice será usado apenas para pesquisa real , começando pela coluna mais à esquerda no índice, até que seja encontrada uma coluna que não esteja na WHEREcláusula.

    Assim, um índice em (source,added) pode otimizar a localização de linhas para uma consulta com "source" e "added" na WHEREcláusula... ou apenas com "source" na WHEREcláusula, mas esse índice não será usado para pesquisas com apenas "adicionado" na cláusula where, porque há uma coluna à esquerda dela que não está sendo usada. Da mesma forma, um único índice em (source,added,zip,name) pode otimizar a pesquisa para consultas com WHEREcláusulas que mencionem source ... ou source e adicionado ... ou source e adicionado e zip ... ou source e adicionado e zip e nome ... mas não apenas "zip" ... não apenas "nome" ... não "adicionado" e "nome" e "zip" ... você entendeu. Um índice é irrelevante começando em e à direita deWHERE

    Observe que a ordem em que você lista as coisas na cláusula where não faz diferença, desde que todas as condições sejam AND. Isso é um equívoco que você encontrará online. Qualquer expressão equivalente é entendida como equivalente pelo otimizador.

    Além disso, a menos que você o tenha desabilitado explicitamente, IN('Foo','foo') é redundante porque a seleção não diferenciaria maiúsculas de minúsculas graças a collations , portanto, 'foo' deve ser suficiente para encontrar qualquer permutação de letras maiúsculas.

    • 5

relate perguntas