가로 방향 파생으로 인해 쿼리 속도가 느려집니다.

가로 방향 파생으로 인해 쿼리 속도가 느려집니다.

MariaDB 10.6.11

쿼리에 문제가 있습니다. 동작은 선택 항목에 따라 다릅니다. 첫 번째 경우 쿼리를 완료하는 데 1분 30초, 두 번째 경우 완료하는 데 3초밖에 걸리지 않습니다.

이 동작 없이 MySql에서 테스트한 쿼리를 완료하는 데 3초가 걸립니다.

이러한 요청 간의 유일한 차이점은 하나의 필드를 선택하는 것입니다.

절이 있는 my_opened_tickets의 첫 번째 경우에 test_town.id 필드를 선택하면 설명 계획에 RATAL DERIED가 사용되며 쿼리를 완료하는 데 1분 30초가 걸립니다.

절이 있는 my_opened_tickets의 두 번째 경우에 test_opened_tickets.test_town_id 필드를 선택하면 설명 계획에 DERIVE가 사용되며 쿼리를 완료하는 데 3초가 걸립니다.

다음을 사용하여 횡방향 파생을 비활성화할 수 있습니다.

set optimizer_switch='split_materialized=off'

하지만 저는 이것이 이 문제를 제거하는 좋은 방법이라고 생각하지 않습니다.그냥 이게 정상적인 행동인지 버그인지 알고 싶어요, 제 요청이 좋지 않은 것일지도 몰라요.

test_country 테이블에 300개의 항목이 있습니다.

test_town 테이블에 20,000개의 항목이 있습니다.

test_ticket 테이블에는 30개의 857690개 항목이 있습니다.

test_opened_ticket 테이블에는 6171538개의 항목이 있습니다.

다음은 첫 번째 쿼리 1분 30초입니다.

with my_opened_tickets as(
    select
        test_town.id  as id,
        test_opened_ticket.nb2,
        test_opened_ticket.nb1
    from
        test_town ,
        test_ticket ,
        test_opened_ticket
    where
        test_opened_ticket.id = test_ticket.id
        and test_opened_ticket.test_country_id = 186
        and test_opened_ticket.test_town_id = test_town.id
),
max_nb2 as(
    select
        id,
        max(nb2) nb2
    from
        my_opened_tickets
    group by id
),
max_by_id_nb2 as (
    select
        max_nb2.id,
        max_nb2.nb2,
        max(my_opened_tickets.nb1)
    from
        my_opened_tickets ,
        max_nb2
    where
        my_opened_tickets.id = max_nb2.id
        and max_nb2.nb2 = my_opened_tickets.nb2
    group by max_nb2.id,max_nb2.nb2
)
select * from max_by_id_nb2;

다음은 설명 계획입니다.

id|select_type    |table             |type  |possible_keys                                                                                             |key                                |key_len|ref                                     |rows |Extra                                                    |
--+---------------+------------------+------+----------------------------------------------------------------------------------------------------------+-----------------------------------+-------+----------------------------------------+-----+---------------------------------------------------------+
 1|PRIMARY        |<derived5>        |ALL   |                                                                                                          |                                   |       |                                        |40802|                                                         |
 5|DERIVED        |test_town         |index |PRIMARY,test_town_id_IDX                                                                                  |PRIMARY                            |4      |                                        |20401|Using index; Using temporary; Using filesort             |
 5|DERIVED        |test_opened_ticket|ref   |PRIMARY,fk_test_opened_ticket_town1,fk_test_opened_ticket_country1_idx,test_opened_ticket_test_town_id_IDX|test_opened_ticket_test_town_id_IDX|10     |test_mlp.test_town.id,const             |1    |                                                         |
 5|DERIVED        |test_ticket       |eq_ref|PRIMARY,test_ticket_id_IDX                                                                                |PRIMARY                            |4      |test_mlp.test_opened_ticket.id          |1    |Using index                                              |
 5|DERIVED        |<derived4>        |ref   |key0                                                                                                      |key0                               |4      |test_mlp.test_town.id                   |2    |Using where                                              |
 4|LATERAL DERIVED|test_town         |eq_ref|PRIMARY,test_town_id_IDX                                                                                  |PRIMARY                            |4      |test_mlp.test_opened_ticket.test_town_id|1    |Using where; Using index; Using temporary; Using filesort|
 4|LATERAL DERIVED|test_opened_ticket|ref   |PRIMARY,fk_test_opened_ticket_town1,fk_test_opened_ticket_country1_idx,test_opened_ticket_test_town_id_IDX|test_opened_ticket_test_town_id_IDX|10     |test_mlp.test_town.id,const             |1    |                                                         |
 4|LATERAL DERIVED|test_ticket       |eq_ref|PRIMARY,test_ticket_id_IDX                                                                                |PRIMARY                            |4      |test_mlp.test_opened_ticket.id          |1    |Using index                                              |

다음은 3/4s의 두 번째 쿼리입니다.

with my_opened_tickets as(
    select
        test_opened_ticket.test_town_id  as id,
        test_opened_ticket.nb2,
        test_opened_ticket.nb1
    from
        test_town ,
        test_ticket ,
        test_opened_ticket
    where
        test_opened_ticket.id = test_ticket.id
        and test_opened_ticket.test_country_id = 186
        and test_opened_ticket.test_town_id = test_town.id
),
max_nb2 as(
    select
        id,
        max(nb2) nb2
    from
        my_opened_tickets
    group by id
),
max_by_id_nb2 as (
    select
        max_nb2.id,
        max_nb2.nb2,
        max(my_opened_tickets.nb1)
    from
        my_opened_tickets ,
        max_nb2
    where
        my_opened_tickets.id = max_nb2.id
        and max_nb2.nb2 = my_opened_tickets.nb2
    group by max_nb2.id,max_nb2.nb2
)
select * from max_by_id_nb2;

그리고 실행 계획은 다음과 같습니다.

id|select_type|table             |type  |possible_keys                                                                                             |key                                |key_len|ref                           |rows |Extra                                       |
--+-----------+------------------+------+----------------------------------------------------------------------------------------------------------+-----------------------------------+-------+------------------------------+-----+--------------------------------------------+
 1|PRIMARY    |<derived5>        |ALL   |                                                                                                          |                                   |       |                              |20401|                                            |
 5|DERIVED    |<derived4>        |ALL   |                                                                                                          |                                   |       |                              |20401|Using where; Using temporary; Using filesort|
 5|DERIVED    |test_town         |eq_ref|PRIMARY,test_town_id_IDX                                                                                  |PRIMARY                            |4      |max_nb2.id                    |1    |Using index                                 |
 5|DERIVED    |test_opened_ticket|ref   |PRIMARY,fk_test_opened_ticket_town1,fk_test_opened_ticket_country1_idx,test_opened_ticket_test_town_id_IDX|test_opened_ticket_test_town_id_IDX|10     |max_nb2.id,const              |1    |Using where                                 |
 5|DERIVED    |test_ticket       |eq_ref|PRIMARY,test_ticket_id_IDX                                                                                |PRIMARY                            |4      |test_mlp.test_opened_ticket.id|1    |Using index                                 |
 4|DERIVED    |test_town         |index |PRIMARY,test_town_id_IDX                                                                                  |PRIMARY                            |4      |                              |20401|Using index                                 |
 4|DERIVED    |test_opened_ticket|ref   |PRIMARY,fk_test_opened_ticket_town1,fk_test_opened_ticket_country1_idx,test_opened_ticket_test_town_id_IDX|test_opened_ticket_test_town_id_IDX|10     |test_mlp.test_town.id,const   |1    |                                            |
 4|DERIVED    |test_ticket       |eq_ref|PRIMARY,test_ticket_id_IDX                                                                                |PRIMARY                            |4      |test_mlp.test_opened_ticket.id|1    |Using index                                 |

다음은 재현할 테이블 인덱스와 채우기 테이블을 만드는 스크립트입니다.

https://filetransfer.io/data-package/9kWfCYo6#link

아니면 여기 대본이 있습니다.

DROP TABLE IF EXISTS test_opened_ticket;
DROP TABLE IF EXISTS test_ticket;
DROP TABLE IF EXISTS test_town;
DROP TABLE IF EXISTS test_country;


CREATE TABLE `test_country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

CREATE TABLE `test_town` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);


CREATE TABLE `test_ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_town_id` int(11) DEFAULT NULL,
  `test_country_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test_ticket_town1` (`test_town_id`),
  KEY `fk_test_ticket_country1_idx` (`test_country_id`),
  CONSTRAINT `fk_test_ticket_country1_idx` FOREIGN KEY     (`test_country_id`) REFERENCES `test_country` (`id`),
  CONSTRAINT `fk_test_ticket_town1` FOREIGN KEY (`test_town_id`) REFERENCES `test_town` (`id`) ON DELETE CASCADE ON     UPDATE CASCADE
);

CREATE TABLE `test_opened_ticket` (
  `id` int(11) NOT NULL,
  `test_town_id` int(11) DEFAULT NULL,
  `test_country_id` int(11) DEFAULT NULL,
  `nb1` int(11) DEFAULT NULL,
  `nb2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test_opened_ticket_town1` (`test_town_id`),
  KEY `fk_test_opened_ticket_country1_idx` (`test_country_id`),
  CONSTRAINT `fk_test_opened_ticket_ticket1_idx` FOREIGN KEY (`id`) REFERENCES `test_ticket` (`id`),
  CONSTRAINT `fk_test_opened_ticket_country1_idx` FOREIGN KEY (`test_country_id`) REFERENCES `test_country` (`id`),
  CONSTRAINT `fk_test_opened_ticket_town1` FOREIGN KEY     (`test_town_id`) REFERENCES `test_town` (`id`) ON DELETE CASCADE ON     UPDATE CASCADE
);


insert into test_country(id)
SELECT seq FROM seq_1_to_300;

insert into test_town(id)
SELECT seq FROM seq_1_to_20000;

insert into test_ticket(id,test_country_id,test_town_id)
SELECT seq,RAND()*258+1,RAND()*19999+1 FROM seq_1_to_27000000;

insert into test_ticket(test_country_id,test_town_id)
SELECT 186,RAND()*19999+1 FROM seq_1_to_3857690;

insert into test_opened_ticket()
select id, test_town_id,test_country_id ,RAND()*300,RAND()*300     from test_ticket where id % 5 = 0 and test_country_id != 186;


insert into test_opened_ticket()
select id, test_town_id,test_country_id ,RAND()*300,RAND()*300     from test_ticket where id % 5 = 0 and test_country_id = 186;

CREATE INDEX test_opened_ticket_test_town_id_IDX USING BTREE ON     test_opened_ticket (test_town_id,test_country_id);
CREATE INDEX test_ticket_id_IDX USING BTREE ON test_ticket (id);
CREATE INDEX test_town_id_IDX USING BTREE ON test_town (id);
CREATE INDEX test_country_id_IDX USING BTREE ON test_country (id);

#slower
with my_opened_tickets as(
select
    test_town.id  as id,
    test_opened_ticket.nb2,
    test_opened_ticket.nb1
from
    test_town ,
    test_ticket ,
    test_opened_ticket
where
    test_opened_ticket.id = test_ticket.id
    and test_opened_ticket.test_country_id = 186
    and test_opened_ticket.test_town_id = test_town.id
    ),
max_nb2 as(
select
    id,
    max(nb2) nb2
from
    my_opened_tickets
group by
    id ),
     max_by_id_nb2 as (
select
    max_nb2.id,
    max_nb2.nb2,
    max(my_opened_tickets.nb1)
from
    my_opened_tickets ,
    max_nb2
where
    my_opened_tickets.id = max_nb2.id
    and max_nb2.nb2 = my_opened_tickets.nb2
group by
    max_nb2.id,max_nb2.nb2)
    select * from max_by_id_nb2;



#faster
with my_opened_tickets as(
select
    test_opened_ticket.test_town_id  as id,
    test_opened_ticket.nb2,
    test_opened_ticket.nb1
from
    test_town ,
    test_ticket ,
    test_opened_ticket
where
    test_opened_ticket.id = test_ticket.id
    and test_opened_ticket.test_country_id = 186
    and test_opened_ticket.test_town_id = test_town.id
    ),
max_nb2 as(
select
    id,
    max(nb2) nb2
from
    my_opened_tickets
group by
    id ),
     max_by_id_nb2 as (
select
    max_nb2.id,
    max_nb2.nb2,
    max(my_opened_tickets.nb1)
from
    my_opened_tickets ,
    max_nb2
where
    my_opened_tickets.id = max_nb2.id
    and max_nb2.nb2 = my_opened_tickets.nb2
group by
    max_nb2.id,max_nb2.nb2)
    select * from max_by_id_nb2;

이걸 읽는 사람이 있다면요

https://mariadb.org/mariadb-30x-faster/

ANALYZE TABLE tbl PERSISTENT FOR ALL;

나의 문제를 해결했습니다.

언급URL : https://stackoverflow.com/questions/75155852/lateral-derived-make-the-query-slower