MySQL查询优化——distinct、order by和limit

Question

我正在尝试优化以下查询：

select distinct this_.id as y0_
from Rental this_
    left outer join RentalRequest rentalrequ1_ 
      on this_.id=rentalrequ1_.rental_id
    left outer join RentalSegment rentalsegm2_ 
      on rentalrequ1_.id=rentalsegm2_.rentalRequest_id
where
    this_.DTYPE='B'
    and this_.id<=1848978
    and this_.billingStatus=1
    and rentalsegm2_.endDate between 1273631699529 and 1274927699529
order by rentalsegm2_.id asc
limit 0, 100;

该查询连续多次执行，用于记录的分页处理（每次都有不同的限制）。它返回我在处理中需要的 id。我的问题是这个查询需要超过 3 秒。这三个表中的每一个都有大约 200 万行。

解释给出：

+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys                                       | key           | key_len | ref                                        | rows   | Extra                                        |
+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | rentalsegm2_ | range  | index_endDate,fk_rentalRequest_id_BikeRentalSegment | index_endDate | 9       | NULL                                       | 449904 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | rentalrequ1_ | eq_ref | PRIMARY,fk_rental_id_BikeRentalRequest              | PRIMARY       | 8       | solscsm_main.rentalsegm2_.rentalRequest_id |      1 | Using where                                  | 
|  1 | SIMPLE      | this_        | eq_ref | PRIMARY,index_billingStatus                         | PRIMARY       | 8       | solscsm_main.rentalrequ1_.rental_id        |      1 | Using where                                  | 
+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+----------------------------------------------+

我尝试删除不同的值，查询运行速度快了三倍。不带查询的解释给出：

+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+-----------------------------+
| id | select_type | table        | type   | possible_keys                                       | key           | key_len | ref                                        | rows   | Extra                       |
+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+-----------------------------+
|  1 | SIMPLE      | rentalsegm2_ | range  | index_endDate,fk_rentalRequest_id_BikeRentalSegment | index_endDate | 9       | NULL                                       | 451972 | Using where; Using filesort | 
|  1 | SIMPLE      | rentalrequ1_ | eq_ref | PRIMARY,fk_rental_id_BikeRentalRequest              | PRIMARY       | 8       | solscsm_main.rentalsegm2_.rentalRequest_id |      1 | Using where                 | 
|  1 | SIMPLE      | this_        | eq_ref | PRIMARY,index_billingStatus                         | PRIMARY       | 8       | solscsm_main.rentalrequ1_.rental_id        |      1 | Using where                 | 
+----+-------------+--------------+--------+-----------------------------------------------------+---------------+---------+--------------------------------------------+--------+-----------------------------+

如您所见，在使用不同时添加 Usingtemporary 。

我已经有一个关于 where 子句中使用的所有字段的索引。 我可以做些什么来优化这个查询吗？

非常感谢！

编辑：我尝试按照建议在 this_.id 上排序，但查询速度慢了 5 倍。以下是解释计划：

+----+-------------+--------------+------+-----------------------------------------------------+---------------------------------------+---------+------------------------------+--------+----------------------------------------------+
| id | select_type | table        | type | possible_keys                                       | key                                   | key_len | ref                          | rows   | Extra                                        |
+----+-------------+--------------+------+-----------------------------------------------------+---------------------------------------+---------+------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | this_        | ref  | PRIMARY,index_billingStatus                         | index_billingStatus                   | 5       | const                        | 782348 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | rentalrequ1_ | ref  | PRIMARY,fk_rental_id_BikeRentalRequest              | fk_rental_id_BikeRentalRequest        | 9       | solscsm_main.this_.id        |      1 | Using where; Using index; Distinct           | 
|  1 | SIMPLE      | rentalsegm2_ | ref  | index_endDate,fk_rentalRequest_id_BikeRentalSegment | fk_rentalRequest_id_BikeRentalSegment | 8       | solscsm_main.rentalrequ1_.id |      1 | Using where; Distinct                        | 
+----+-------------+--------------+------+-----------------------------------------------------+---------------------------------------+---------+------------------------------+--------+----------------------------------------------+

Answer 1

1. 从执行计划中我们看到优化器足够聪明，可以理解这里不需要 OUTER JOIN。无论如何，您最好明确指定这一点。
2. DISTINCT 修饰符意味着您想要对 SELECT 部分中的所有字段进行 GROUP BY，即对所有指定字段进行 ORDER BY，然后丢弃重复项。换句话说，order byrentsegm2_.id asc子句在这里没有任何意义。

下面的查询应返回等效结果：

select distinct this_.id as y0_
from Rental this_
    join RentalRequest rentalrequ1_ 
      on this_.id=rentalrequ1_.rental_id
    join RentalSegment rentalsegm2_ 
      on rentalrequ1_.id=rentalsegm2_.rentalRequest_id
where
    this_.DTYPE='B'
    and this_.id<=1848978
    and this_.billingStatus=1
    and rentalsegm2_.endDate between 1273631699529 and 1274927699529
limit 0, 100;

UPD

如果您希望执行计划以 RentalSegment 开头，则需要将以下索引添加到数据库中：

1. RentalSegment (endDate )
2. RentalRequest (id, Rental_id)
3. Rental (id, DTYPE, billingStatus) 或 (id, billingStatus, DTYPE)

然后可以将查询重写如下：

SELECT this_.id as y0_
FROM RentalSegment rs
    JOIN RentalRequest rr
    JOIN Rental this_
WHERE rs.endDate between 1273631699529 and 1274927699529
    AND rs.rentalRequest_id = rr.id
    AND rr.rental_id <= 1848978
    AND rr.rental_id = this_.id
    AND this_.DTYPE='D'
    AND this_.billingStatus = 1
GROUP BY this_.id
LIMIT 0, 100;

如果执行计划不会从 RentalSegment 开始，您可以使用 强制执行STRAIGHT_JOIN。

Answer 2

没有不同的查询运行得更快的原因是因为您有一个限制子句。如果没有 unique，服务器只需要查看前一百个匹配项。然而，其中一些行可能具有重复的字段，因此如果引入 unique 子句，服务器必须查看更多行才能找到没有重复值的行。

顺便说一句，你为什么使用 OUTER JOIN？

Answer 3

这里，对于“rentalsegm2_”表，优化器选择了“index_endDate”索引，并且该表的预期行数约为 45 万行。由于还存在其他where条件，您可以检查“this_”表索引。我的意思是您可以在“this_ table”中检查每个 where 条件影响的记录数量。

总之，您可以通过更改优化器使用的索引来尝试替代解决方案。
这可以通过“USE INDEX”、“FORCE INDEX”命令获得。

谢谢林森克

​
数据库管理员
www.qburst.com