Início / dba / Perguntas / 1792
Accepted
Patrick
Asked: 2011-03-18 12:56:41 +0800 CST

No MySQL, a ordem das colunas em uma cláusula WHERE afeta o desempenho da consulta?

  • 772

Estou tendo problemas de desempenho em determinadas consultas de banco de dados que têm grandes conjuntos de resultados possíveis.

A consulta em questão, tenho três ANDs na cláusula WHERE

A ordem das cláusulas importa?

Como se eu colocasse a cláusula ASI_EVENT_TIME primeiro (já que isso removeria a maioria dos resultados de qualquer uma das cláusulas.

Isso melhorará o tempo de execução da consulta?

CONSULTA:

SELECT DISTINCT  activity_seismo_info.* 
FROM `activity_seismo_info` 
WHERE 
    activity_seismo_info.ASI_ACTIVITY_ID IS NOT NULL  AND 
    activity_seismo_info.ASI_SEISMO_ID IN (43,44,...,259) AND 
    (
        activity_seismo_info.ASI_EVENT_TIME>='2011-03-10 00:00:00' AND 
        activity_seismo_info.ASI_EVENT_TIME<='2011-03-17 23:59:59'
    ) 

ORDER BY activity_seismo_info.ASI_EVENT_TIME DESC

EXPLIQUE da consulta:

+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
| id | select_type | table   | type  | possible_keys             | key          | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
|  1 | SIMPLE      | act...o | range | act...o_FI_1,act...o_FI_2 | act...o_FI_1 | 5       | NULL | 65412 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+

Usando:

PHP 5.2

MySQL 5.0.51a-3ubuntu5.4

Propulsão 1.3

Symfony 1.2.5

mysql performance

6 respostas

  1. Best Answer

    Eu não penso assim. O otimizador de consulta deve ser inteligente o suficiente.

    Você pode tentar reorganizar as cláusulas WHERE e ver que EXPLAINS informa o mesmo em cada caso.

    Sobre o que pode ser feito para otimizar esta consulta: Existe um índice em ASI_EVENT_TIME ? (este é o mais crucial que eu acho para esta consulta, pois você também classifica os resultados usando-o).

    Existem índices nos outros dois campos (ASI_SEISMO_ID e ASI_ACTIVITY_ID)?

    Seria útil se você postasse a estrutura da tabela.

    • 28

  2. Da documentação :

    Se a tabela tiver um índice de várias colunas, qualquer prefixo mais à esquerda do índice poderá ser usado pelo otimizador para localizar linhas. Por exemplo, se você tiver um índice de três colunas em (col1, col2, col3), terá recursos de pesquisa indexados em (col1), (col1, col2) e (col1, col2, col3).

    O MySQL não pode usar um índice se as colunas não formarem um prefixo mais à esquerda do índice.

    Então sim, deve ser a mesma ordem das colunas em um índice composto .

    • 19

  3. Não, não importa.

    O otimizador faz várias transformações simples logo após analisar o SQL - essa é uma delas.

    • 10

  4. ONDE foo E bar

    otimiza o mesmo que

    ONDE bar E foo

    No entanto,

    ONDE não igual#1 E não igual#2

    Não é possível otimizar ambas as partes. Por exemplo,

    ONDE a ENTRE 1 e 3 E b > 17

    não pode fazer bom uso de INDEX(a,b) ou INDEX(b,a)

    Para expressar de forma diferente, quaisquer testes '=' AND'd juntos na cláusula WHERE são usados ​​primeiro, então um não-'=' (IN, BETWEEN, >, etc) pode ser tratado. Não mais do que um pode ser efetivamente otimizado.

    Sua consulta tem 3 dessas cláusulas.

    Como se vê, INDEX(EVENT_TIME) é provavelmente o mais útil -- ajudará com um dos ANDs e pode ser usado para evitar "classificação de arquivos" para ORDER BY.

    Se não houver linhas duplicadas (por que diabos haveria?), então livre-se de DISTINCT. Isso causa ainda mais esforço.

    Forneça SHOW CREATE TABLE e SHOW TABLE STATUS ao fazer perguntas de desempenho.

    Atualizar... Versões mais recentes (por exemplo, MySQL 5.7) podem, em algumas situações, tratar IN( list of constants )quase como arquivos =. Para jogar pelo seguro, fique com esta ordem (cada parte sendo opcional):

    1. Qualquer número de =.
    2. Alguns INs.
    3. No máximo um intervalo.
    • 8

  5. MySQL onde o documento de otimização diz:

    Você pode ficar tentado a reescrever suas consultas para tornar as operações aritméticas mais rápidas, enquanto sacrifica a legibilidade. Como o MySQL faz otimizações semelhantes automaticamente , muitas vezes você pode evitar esse trabalho e deixar a consulta em uma forma mais compreensível e sustentável. Algumas das otimizações realizadas pelo MySQL seguem:

    • ...

    • Para cada tabela em uma junção, um WHERE mais simples é construído para obter uma avaliação WHERE rápida para a tabela e também para pular linhas o mais rápido possível .

    • Cada índice de tabela é consultado e o melhor índice é usado, a menos que o otimizador acredite que é mais eficiente usar uma varredura de tabela . Ao mesmo tempo, uma varredura era usada com base no fato de o melhor índice abranger mais de 30% da tabela, mas uma porcentagem fixa não determina mais a escolha entre usar um índice ou uma varredura. O otimizador agora é mais complexo e baseia sua estimativa em fatores adicionais, como tamanho da tabela, número de linhas e tamanho do bloco de E/S.

    Dessa forma, é racional para o otimizador de consulta omitir a ordem COMO usamos as colunas na consulta (não apenas o MySQL, mas o SQL é uma linguagem declarativa e deve fazer o que queremos, não como queremos).

    No entanto, ainda gosto de ter o mesmo tipo para as colunas de uma chave composta na consulta, mas às vezes é inevitável, por exemplo, quando usamos ORM ou ActiveRecord, em alguns frameworks como o yii2, a personalização dos critérios de relação será anexada ao final do uma condição "on", mas ainda precisamos dos recursos de QueryBuilders em diferentes partes de um aplicativo.

    • 1

  6. QUALQUER campo que é usado em suas cláusulas WHERE/HAVING e tem alta seletividade (o número de valores únicos / o número total de registros > 10%~20%) DEVE ser indexado.

    Portanto, se sua ASI_EVENT_TIMEcoluna tiver muitos valores possíveis, primeiro indexe todos eles. Então, como @ypercube disse, tente reorganizá-los e veja o que EXPLAIN lhe diz. Deve ser tudo em torno do mesmo.

    Além disso, quero que você dê uma olhada em Indexando SQL LIKE Filters . Embora não seja para isso que você precisa de uma resposta, você ainda aprenderá sobre como a indexação funciona nos bastidores.

    * Editar: consulte os links fornecidos abaixo nos comentários para saber mais sobre indexação.

    • -2

relate perguntas