MariaDB 10.6.11中LATERAL DERIVED致查询性能骤降问题咨询

同一逻辑的两个查询仅因SELECT字段不同，执行耗时差异巨大：第一种场景耗时1分30秒，第二种仅需3秒。在MySQL中测试时无此问题，两种场景均耗时3秒。

差异仅在于CTE my_opened_tickets 中选取的字段：

• 选取test_town.id作为id时，执行计划中出现LATERAL DERIVED，查询耗时1分30秒；
• 选取test_opened_ticket.test_town_id作为id时，执行计划中为DERIVED，查询仅需3秒。

可通过以下语句禁用LATERAL DERIVED：

set optimizer_switch='split_materialized=off'

但这并非根治方法，现需确认此现象是正常行为、Bug，还是查询写法存在问题。

各表数据量 #

• test_country：300条
• test_town：20000条
• test_ticket：30857690条
• 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-4秒的查询 #

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                                 |

复现脚本 #

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;

内容的提问来源于stack exchange，提问作者Alays