Início / user-237511

samu's questions

Martin Hope
samu
Asked: 2024-03-28 22:01:36 +0800 CST
  • 5

Encontrei um problema hoje e não tenho certeza se é um bug no MySQL 8 ou se estou fazendo algo errado.

Prefácio

No final da postagem anexei uma consulta para reproduzir o problema localmente.

Eu tenho três tabelas: clientse mais duas. Todas as tabelas têm aproximadamente 50 colunas. Estou fazendo uma consulta bem simples que une todas as tabelas à clientstabela por uma chave estrangeira e depois é só ordena-las.

A clientstabela tem 1 milhão de linhas. As outras duas tabelas estão vazias.

A consulta problemática é a seguinte:

select
    test_client.*,
    test_join1.*,
    test_join2.*
from
    test_client
    left join test_join1 on test_client.id = test_join1.client_id
    left join test_join2 on test_client.id = test_join2.client_id
order by test_client.id
limit 10;

Esta consulta leva de 4 a 5 segundos para ser concluída.

Quando eu removo ORDER BYele é executado em 50 ms. Se eu desabilitar junções de hash usando a /*+ NO_BNL() */dica do planejador, ela também será concluída em 50 ms.

Aqui estão os planos de consulta:

1. Consulta regular com ORDER_BY:

EXPLICAR ANÁLISE:

-> Limit: 10 row(s)  (actual time=4757..4757 rows=10 loops=1)
    -> Sort: test_client.id, limit input to 10 row(s) per chunk  (actual time=4757..4757 rows=10 loops=1)
        -> Stream results  (cost=194554 rows=994944) (actual time=0.59..4241 rows=1e+6 loops=1)
            -> Left hash join (test_join2.client_id = test_client.id)  (cost=194554 rows=994944) (actual time=0.574..1334 rows=1e+6 loops=1)
                -> Left hash join (test_join1.client_id = test_client.id)  (cost=147022 rows=994944) (actual time=0.444..1240 rows=1e+6 loops=1)
                    -> Table scan on test_client  (cost=100801 rows=994944) (actual time=0.331..1118 rows=1e+6 loops=1)
                    -> Hash
                        -> Table scan on test_join1  (cost=0.0478 rows=1) (actual time=0.0984..0.0984 rows=0 loops=1)
                -> Hash
                    -> Table scan on test_join2  (cost=0.0955 rows=1) (actual time=0.117..0.117 rows=0 loops=1)

Plano de execução para consulta com ordem por

EXPLICAR:

+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+
|id|select_type|table      |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra                                     |
+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+
|1 |SIMPLE     |test_client|null      |ALL |null         |null|null   |null|994944|100     |Using temporary; Using filesort           |
|1 |SIMPLE     |test_join1 |null      |ALL |client_id    |null|null   |null|1     |100     |Using where; Using join buffer (hash join)|
|1 |SIMPLE     |test_join2 |null      |ALL |client_id    |null|null   |null|1     |100     |Using where; Using join buffer (hash join)|
+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+

2. Sem ORDER BY:

EXPLICAR ANÁLISE:

-> Limit: 10 row(s)  (cost=194554 rows=10) (actual time=0.0813..0.096 rows=10 loops=1)
    -> Left hash join (test_join2.client_id = test_client.id)  (cost=194554 rows=994944) (actual time=0.0806..0.0946 rows=10 loops=1)
        -> Left hash join (test_join1.client_id = test_client.id)  (cost=147022 rows=994944) (actual time=0.0643..0.0776 rows=10 loops=1)
            -> Table scan on test_client  (cost=100801 rows=994944) (actual time=0.0482..0.0607 rows=10 loops=1)
            -> Hash
                -> Table scan on test_join1  (cost=0.0478 rows=1) (actual time=0.00996..0.00996 rows=0 loops=1)
        -> Hash
            -> Table scan on test_join2  (cost=0.0955 rows=1) (actual time=0.0105..0.0105 rows=0 loops=1)

Plano de execução para consulta sem ordem por

EXPLICAR:

+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+
|id|select_type|table      |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra                                     |
+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+
|1 |SIMPLE     |test_client|null      |ALL |null         |null|null   |null|994944|100     |null                                      |
|1 |SIMPLE     |test_join1 |null      |ALL |client_id    |null|null   |null|1     |100     |Using where; Using join buffer (hash join)|
|1 |SIMPLE     |test_join2 |null      |ALL |client_id    |null|null   |null|1     |100     |Using where; Using join buffer (hash join)|
+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+

3. Com /*+ NO_BNL() */dica: EXPLIQUE ANALISE:

-> Limit: 10 row(s)  (cost=497474 rows=10) (actual time=0.179..0.3 rows=10 loops=1)
    -> Nested loop left join  (cost=497474 rows=10) (actual time=0.178..0.296 rows=10 loops=1)
        -> Nested loop left join  (cost=248737 rows=10) (actual time=0.144..0.239 rows=10 loops=1)
            -> Index scan on test_client using PRIMARY  (cost=0.0131 rows=10) (actual time=0.0782..0.148 rows=10 loops=1)
            -> Index lookup on test_join1 using client_id (client_id=test_client.id)  (cost=0.25 rows=1) (actual time=0.00824..0.00824 rows=0 loops=10)
        -> Index lookup on test_join2 using client_id (client_id=test_client.id)  (cost=0.25 rows=1) (actual time=0.0048..0.0048 rows=0 loops=10)

Explique o plano com junções de hash desativadas

EXPLICAR:

+--+-----------+-----------+----------+-----+-------------+---------+-------+----------------------------+----+--------+-----+
|id|select_type|table      |partitions|type |possible_keys|key      |key_len|ref                         |rows|filtered|Extra|
+--+-----------+-----------+----------+-----+-------------+---------+-------+----------------------------+----+--------+-----+
|1 |SIMPLE     |test_client|null      |index|null         |PRIMARY  |4      |null                        |10  |100     |null |
|1 |SIMPLE     |test_join1 |null      |ref  |client_id    |client_id|5      |hashjoinissue.test_client.id|1   |100     |null |
|1 |SIMPLE     |test_join2 |null      |ref  |client_id    |client_id|5      |hashjoinissue.test_client.id|1   |100     |null |
+--+-----------+-----------+----------+-----+-------------+---------+-------+----------------------------+----+--------+-----+

Isso é um bug no mysql 8 ou estou fazendo algo errado?

EDITAR 1

Só para esclarecer: a tabela real não se parece exatamente com esta aqui, mas possui uma quantidade semelhante de colunas (em média).

O conjunto de dados real também contém alguns dados test_join1e test_join2tabelas - acabei de perceber que a quantidade de dados não parece importar muito.

Adicionei muitas colunas para ilustrar o problema. O que notei, entretanto, é que se a consulta não solicitar todas as colunas, mas, por exemplo, solicitar apenas ids:

select
    test_client.id,
    test_join1.id,
    test_join2.id
from
    test_client
    left join test_join1 on test_client.id = test_join1.client_id
    left join test_join2 on test_client.id = test_join2.client_id
order by test_client.id limit 10;

Então a consulta é quase instantânea. Observe que nenhuma das outras colunas é usada em qualquer filtragem, união ou ordenação adicional, mas quando o planejador espera que os dados resultantes sejam grandes o suficiente - ele passa a usar uma junção hash, que não é a solução ideal.

EXPLICAR:

+--+-----------+-----------+----------+-----+-------------+--------------+-------+----------------------------+----+--------+-----------+
|id|select_type|table      |partitions|type |possible_keys|key           |key_len|ref                         |rows|filtered|Extra      |
+--+-----------+-----------+----------+-----+-------------+--------------+-------+----------------------------+----+--------+-----------+
|1 |SIMPLE     |test_client|null      |index|null         |test_client_id|4      |null                        |10  |100     |Using index|
|1 |SIMPLE     |test_join1 |null      |ref  |client_id    |client_id     |5      |hashjoinissue.test_client.id|1   |100     |Using index|
|1 |SIMPLE     |test_join2 |null      |ref  |client_id    |client_id     |5      |hashjoinissue.test_client.id|1   |100     |Using index|
+--+-----------+-----------+----------+-----+-------------+--------------+-------+----------------------------+----+--------+-----------+

EXPLICAR ANÁLISE:

-> Limit: 10 row(s)  (cost=497474 rows=10) (actual time=0.796..0.835 rows=10 loops=1)
    -> Nested loop left join  (cost=497474 rows=10) (actual time=0.794..0.833 rows=10 loops=1)
        -> Nested loop left join  (cost=248737 rows=10) (actual time=0.721..0.757 rows=10 loops=1)
            -> Covering index scan on test_client using test_client_id  (cost=0.0131 rows=10) (actual time=0.543..0.545 rows=10 loops=1)
            -> Covering index lookup on test_join1 using client_id (client_id=test_client.id)  (cost=0.25 rows=1) (actual time=0.0198..0.0198 rows=0 loops=10)
        -> Covering index lookup on test_join2 using client_id (client_id=test_client.id)  (cost=0.25 rows=1) (actual time=0.00645..0.00645 rows=0 loops=10)

Explique a análise ao solicitar apenas IDs

Consulta para reproduzir dados

A seguinte consulta pode ser usada para reproduzir os dados localmente:

drop table if exists test_join1;
drop table if exists test_join2;
drop table if exists test_client cascade;

create table if not exists test_client (
    id int auto_increment primary key,
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    col4 varchar(255),
    col5 varchar(255),
    col6 varchar(255),
    col7 varchar(255),
    col8 varchar(255),
    col9 varchar(255),
    col10 varchar(255),
    col11 varchar(255),
    col12 varchar(255),
    col13 varchar(255),
    col14 varchar(255),
    col15 varchar(255),
    col16 varchar(255),
    col17 varchar(255),
    col18 varchar(255),
    col19 varchar(255),
    col20 varchar(255),
    col21 varchar(255),
    col22 varchar(255),
    col23 varchar(255),
    col24 varchar(255),
    col25 varchar(255),
    col26 varchar(255),
    col27 varchar(255),
    col28 varchar(255),
    col29 varchar(255),
    col30 varchar(255),
    col31 varchar(255),
    col32 varchar(255),
    col33 varchar(255),
    col34 varchar(255),
    col35 varchar(255),
    col36 varchar(255),
    col37 varchar(255),
    col38 varchar(255),
    col39 varchar(255),
    col40 varchar(255),
    col41 varchar(255),
    col42 varchar(255),
    col43 varchar(255),
    col44 varchar(255),
    col45 varchar(255),
    col46 varchar(255),
    col47 varchar(255),
    col48 varchar(255),
    col49 varchar(255)
);
create table if not exists test_join1 (
    id int auto_increment primary key,
    client_id int,
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    col4 varchar(255),
    col5 varchar(255),
    col6 varchar(255),
    col7 varchar(255),
    col8 varchar(255),
    col9 varchar(255),
    col10 varchar(255),
    col11 varchar(255),
    col12 varchar(255),
    col13 varchar(255),
    col14 varchar(255),
    col15 varchar(255),
    col16 varchar(255),
    col17 varchar(255),
    col18 varchar(255),
    col19 varchar(255),
    col20 varchar(255),
    col21 varchar(255),
    col22 varchar(255),
    col23 varchar(255),
    col24 varchar(255),
    col25 varchar(255),
    col26 varchar(255),
    col27 varchar(255),
    col28 varchar(255),
    col29 varchar(255),
    col30 varchar(255),
    col31 varchar(255),
    col32 varchar(255),
    col33 varchar(255),
    col34 varchar(255),
    col35 varchar(255),
    col36 varchar(255),
    col37 varchar(255),
    col38 varchar(255),
    col39 varchar(255),
    col40 varchar(255),
    col41 varchar(255),
    col42 varchar(255),
    col43 varchar(255),
    col44 varchar(255),
    col45 varchar(255),
    col46 varchar(255),
    col47 varchar(255),
    col48 varchar(255),
    col49 varchar(255),
    foreign key (client_id) references test_client(id)
);
create table if not exists test_join2 (
    id int auto_increment primary key,
    client_id int,
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    col4 varchar(255),
    col5 varchar(255),
    col6 varchar(255),
    col7 varchar(255),
    col8 varchar(255),
    col9 varchar(255),
    col10 varchar(255),
    col11 varchar(255),
    col12 varchar(255),
    col13 varchar(255),
    col14 varchar(255),
    col15 varchar(255),
    col16 varchar(255),
    col17 varchar(255),
    col18 varchar(255),
    col19 varchar(255),
    col20 varchar(255),
    col21 varchar(255),
    col22 varchar(255),
    col23 varchar(255),
    col24 varchar(255),
    col25 varchar(255),
    col26 varchar(255),
    col27 varchar(255),
    col28 varchar(255),
    col29 varchar(255),
    col30 varchar(255),
    col31 varchar(255),
    col32 varchar(255),
    col33 varchar(255),
    col34 varchar(255),
    col35 varchar(255),
    col36 varchar(255),
    col37 varchar(255),
    col38 varchar(255),
    col39 varchar(255),
    col40 varchar(255),
    col41 varchar(255),
    col42 varchar(255),
    col43 varchar(255),
    col44 varchar(255),
    col45 varchar(255),
    col46 varchar(255),
    col47 varchar(255),
    col48 varchar(255),
    col49 varchar(255),
    foreign key (client_id) references test_client(id)
);

SET @@cte_max_recursion_depth  = 1000001;

insert into test_client
WITH RECURSIVE nrows(id) AS (
SELECT 1 as id UNION ALL
SELECT id + 1 from nrows WHERE  id<=1000000
)
SELECT id, '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '' FROM nrows;

create index test_client_id on test_client (id);
mysql