Mysql查询优化-非常慢

Question

我需要优化运行速度非常慢的查询，但不知道该怎么做。它包含一个子查询，这使得它非常慢。如果我删除内联查询，那么它运行得很好。

查询是:

EXPLAIN
SELECT  t.service_date, 
        t.service_time, 
        (SELECT js.modified_date FROM rej_job_status js WHERE js.booking_id=b.booking_id ORDER BY id DESC LIMIT 1) `cancel_datetime`, 
        b.booking_id, 
        b.ref_booking_id, 
        b.phone, b.city, 
        b.booking_time, 
        CONCAT(rc.firstname," ",rc.lastname) customer_name, 
        rc.phone_no, 
        rs.service_id,
        rs.service_name,
        rct.city_name 
FROM rej_job_details t 
JOIN rej_booking b ON t.booking_id = b.booking_id 
JOIN rej_customer rc ON rc.customer_id = b.customer 
JOIN rej_service rs ON t.service_id = rs.service_id 
JOIN rej_city rct ON rct.city_id=b.city 
WHERE t.act_status = 0 AND DATE(b.booking_time) >= '2016-06-01' 
        AND DATE(b.booking_time) <= '2016-06-14' 
ORDER BY b.booking_time DESC 
LIMIT 0 , 50 

解释计划显示了这一点:

id  select_type         table   type    possible_keys       key         key_len ref                         rows    Extra   
1   PRIMARY             b       ALL     PRIMARY             NULL        NULL    NULL                        32357   Using where; Using filesort
1   PRIMARY             rct     eq_ref  PRIMARY             PRIMARY     4       crmdb.b.city        1       NULL
1   PRIMARY             t       ref     booking_id          booking_id  4       crmdb.b.booking_id  1       Using where
1   PRIMARY             rs      eq_ref  PRIMARY,service_id  PRIMARY     4       crmdb.t.service_id  1       NULL
1   PRIMARY             rc      eq_ref  PRIMARY             PRIMARY     4       crmdb.b.customer    1       Using where 
2   DEPENDENT SUBQUERY  js      index   NULL                PRIMARY     4       NULL                        1       Using where

a) 如何阅读此解释计划并了解其含义？

b) 如何优化此查询？

Answer 1

booking_time 隐藏在函数内，因此无法使用 INDEX(booking_time)。这会导致代价高昂的表扫描。

AND DATE(b.booking_time) >= '2016-06-01' 
AND DATE(b.booking_time) <= '2016-06-14' 

-->

AND b.booking_time >= '2016-06-01' 
AND b.booking_time <  '2016-06-15' -- note 3 differences in this line

或者，这可能更简单(通过避免第二次日期计算):

AND b.booking_time >= '2016-06-01' 
AND b.booking_time <  '2016-06-01' + INTREVAL 2 WEEK

在EXPLAIN中，我希望“ALL”变为“range”，并且“Filesort”消失。