mariadb LATERAL DERIVED使查询速度变慢

玛丽亚 db2 011年6月10日

我在查询时遇到了问题，它的行为取决于选择，在第一种情况下，查询需要1 min 30 s才能完成，在第二种情况下，它只需要3s才能完成。
在没有此行为的MySql上测试，查询需要3秒才能完成。
这些请求之间的唯一区别是选择一个字段。
如果在my_opened_ticketswith子句的第一个case中选择字段test_town.id，则会在解释计划中使用LATERAL DERIVED，并且查询需要1 min 30 s才能完成。
在my_opened_ticketswith子句的第二个case中选择字段test_opened_tickets.test_town_id，将在解释计划中使用DERIVED，并且查询需要3s才能完成。
我可以使用以下命令禁用Lateral Derived：

set optimizer_switch='split_materialized=off'

但我觉得这不是解决这个问题的好办法，我只是想知道这是正常行为还是bug，可能我的要求不好我也不知道。
表test_country有300个条目
表test_town有20，000个条目
表test_ticket具有30 857 690个条目
表test_opened_ticket具有6 171 538个条目
下面是第一个查询1 min 30 s：

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;

mariadb LATERAL DERIVED使查询速度变慢

1条答案

相关问题

热门标签

最新问答