Início / dba / Perguntas / 172303
Accepted
kriegu
Asked: 2017-04-29 15:38:06 +0800 CST

Consulta usando índice com ordem por

  • 772

Atualmente estou verificando o slow_query_log com índices log_queries_not_using habilitados de uma instância mysql. Há uma consulta que aparece com muita frequência, não é uma consulta lenta, mas uma consulta que não usa índices.

A consulta tem a seguinte estrutura:

select ...
from `Tb1`
left join `Tb2` on `Tb1`.`id` = `Tb2`.`id_tb1`
left join `Tb3` on `Tb2`.`id_common` = `Tb3`.`id_common`
left join `Tb4` on `Tb2`.`id` = `Tb4`.`id_tb2`
left join `Tb5` on `Tb1`.`id_tb5` = `Tb5`.`id`;

A explicação para esta consulta é a seguinte:

+----+-------------+--------+------------+--------+-----------------+------------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table  | partitions | type   | possible_keys   | key              | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+--------+------------+--------+-----------------+------------------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | Tb1    | NULL       | ALL    | NULL            | NULL             | NULL    | NULL                  |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Tb2    | NULL       | ref    | fk_tb2_tb1_idx  | fk_tb2_tb1_idx   | 4       | schema.tb1.id         |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Tb3    | NULL       | ref    | fk_tb2_tb3_idx  | fk_tb2_tb3_idx   | 4       | schema.Tb2.id_common  |    1 |   100.00 | Using index |
|  1 | SIMPLE      | Tb4    | NULL       | ref    | fk_tb4_tb2_idx  | fk_tb4_tb2_idx   | 4       | schema.Tb2.id         |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Tb5    | NULL       | eq_ref | PRIMARY         | PRIMARY          | 4       | schema.tb1.id_tb5     |    1 |   100.00 | NULL        |
+----+-------------+--------+------------+--------+-----------------+------------------+---------+-----------------------+------+----------+-------------+

Como podemos ver na saída da explicação, a consulta não usa nenhum índice em Tb1, embora tenha índices nas colunas nas quais são unidas a outras tabelas.

QUESTÃO 1:

Por que essa consulta não usa o índice na tabela Tb1?

Então, tentei usar um "order by" usando o id de Tb1 assim:

select ...
from `Tb1`
left join `Tb2` on `Tb1`.`id` = `Tb2`.`id_tb1`
left join `Tb3` on `Tb2`.`id_common` = `Tb3`.`id_common`
left join `Tb4` on `Tb2`.`id` = `Tb4`.`id_tb2`
left join `Tb5` on `Tb1`.`id_tb5` = `Tb5`.`id`
order by Tb1.id;

Obtendo o seguinte resultado na explicação:

    +----+-------------+--------+------------+--------+-----------------+-------------------+---------+----------------------+------+----------+-------------+
    | id | select_type | table  | partitions | type   | possible_keys   | key               | key_len | ref                  | rows | filtered | Extra       |
    +----+-------------+--------+------------+--------+-----------------+-------------------+---------+----------------------+------+----------+-------------+
    |  1 | SIMPLE      | Tb1    | NULL       | index  | NULL            | PRIMARY           | 4       | NULL                 |    1 |   100.00 | NULL        |
    |  1 | SIMPLE      | Tb2    | NULL       | ref    | fk_tb2_tb1_idx  | fk_tb2_tb1_idx    | 4       | schema.tb1.id        |    1 |   100.00 | NULL        |
    |  1 | SIMPLE      | Tb3    | NULL       | ref    | fk_tb2_tb3_idx  | fk_tb2_tb3_idx    | 4       | schema.Tb2.id_common |    1 |   100.00 | Using index |
    |  1 | SIMPLE      | Tb4    | NULL       | ref    | fk_tb4_tb2_idx  | fk_tb4_tb2_idx    | 4       | schema.Tb2.id        |    1 |   100.00 | NULL        |
    |  1 | SIMPLE      | Tb5    | NULL       | eq_ref | PRIMARY         | PRIMARY           | 4       | schema.tb1.id_tb5    |    1 |   100.00 | NULL        |
    +----+-------------+--------+------------+--------+-----------------+-------------------+---------+----------------------+------+----------+-------------+

Como podemos ver a partir da saída de explicação no. 2, ele agora usa a chave primária como índice.

QUESTÃO 2:

Usando esta ordem: a) Vai melhorar o desempenho da consulta? b) O desempenho da consulta seria o mesmo. c) Vai degradar o desempenho da consulta?

mysql performance

2 respostas

  1. a lógica do LEFT JOIN - pegue todas as linhas da tabela LEFT

    porque você não tem nenhuma condição WHERE - o MySQL não encontrou nenhum motivo para usar o índice para tb1 - com índice ou sem índice ele ainda lerá todas as linhas.

    então, resposta geral para sua pergunta - não aumentará a velocidade e possivelmente será mais lento (dependendo do tamanho dos dados), quando as operações de classificação exigirem o uso de tabelas temporárias.

    Você pode fazer alguns testes - por exemplo, alterar LEFT JOIN para INNER, não está correto para o teste de lógica, mas mostrará um plano diferente. Você também pode adicionar a condição WHERE para tb1 e ver - o que acontece.

    • 1
  2. Best Answer

    RESPOSTA 1:

    Internamente, no caso do InnoDB, a varredura completa da tabela é o mesmo que usar a chave primária porque o InnoDB agrupa os dados por chave primária. E não usa nenhum outro índice porque não há condição WHERE na primeira tabela.

    RESPOSTA 2:

    Na realidade, adicionar order bynão fará diferença porque o plano de execução permanecerá essencialmente o mesmo (e os resultados retornados também serão os mesmos). Isso se deve novamente ao fato de que os dados de cluster do InnoDB por chave primária, ou seja, as linhas são sempre classificadas internamente por chave primária.

    Portanto, se você se sentir melhor por não ver essas consultas no log de consultas lentas, adicione a ordem por.

    Finalmente, eu sei que você não perguntou isso, mas - eu realmente recomendaria contra o uso log-queries-not-using-indexes, pois isso faz com que você se concentre nas métricas erradas. Aqui está a minha maneira recomendada de analisar a consulta: Tutorial em vídeo de registro de consulta lenta do MySQL avançado .

    Boa sorte!

    • 1

relate perguntas