mysql - 按优化排序

Question

我正在两个大表上执行查询，但是当添加一列进行排序(ps.start_time asc)时，我的查询死机了，花费了大约 25 分钟，否则没有本专栏需要 1 秒。我分享一下我的sql语句:

SELECT  
    `ji`.`jobsId`         AS `jobsId`,
    `ji`.`fkCompanyId`    AS `fkCompanyId`,
    `ji`.`routeNumber`    AS `routeNumber`,
    `ji`.`fromStop`       AS `fromStop`,
    `ji`.`toStop`         AS `toStop`,
    `ji`.`schDeptTime`    AS `schDeptTime`,
    `ji`.`schArrTime`     AS `schArrTime`,
    `ji`.`run`            AS `run`,
    `ji`.`scheduleCode`   AS `scheduleCode`,
    `ji`.`driverDeptTime` AS `driverDeptTime`,
    `ji`.`driverArrTime`  AS `driverArrTime`,
    `ji`.`serviceType`    AS `serviceType`,
    `ji`.`days`           AS `days`,
    `ji`.`busNumber`      AS `busNumber`,
    `ji`.`hrNumber`       AS `hrNumber`,
    `ji`.`DriverName`     AS `DriverName`,
    `ps`.`start_time`     AS `start_time`,
    `ps`.`start_location` AS `start_location`,
    `ps`.`start_areas`    AS `start_areas`,
    `ps`.`passenger_scan` AS `passenger_scan`,
    `ji`.`fkCityId`       AS `fkCityId`,
    `ji`.`City`           AS `City`,
    `ji`.`date`           AS `date`
FROM (`passenger_scan` `ps`
   JOIN `jobs_import` `ji` USE INDEX(jobs_date)  
     ON ((`ji`.`busNumber` = `ps`.`busInfo`)))
WHERE DATE BETWEEN '2018-d03-01' AND '2018-03-31'
ORDER BY ji.`date` DESC, ji.`City` ASC ,  ji.`routeNumber` ASC  -- ,`ps`.`start_time` asc

Execution Plan without desired sorting column

Execution Plan with desired sorting column (its problem)

Indexes on both tables (all columns indexed in order by clause)

Answer 1

应为您的连接字段建立索引以优化订单。请检查 ps.start_time 是否已建立索引