使用Usingtemporary解释mysql性能中的计划；使用文件排序；使用索引条件

我在线阅读各种博客和文档，但只是想知道如何优化查询。我无法决定是否必须重写查询或添加索引才能优化。

还添加创建表结构

CREATE TABLE `dsr_table` (
  `DSR_VIA` CHAR(3) DEFAULT NULL,
  `DSR_PULLDATA_FLAG` CHAR(1) DEFAULT 'O',
  `DSR_BILLING_FLAG` CHAR(1) DEFAULT 'O',
  `WH_FLAG` CHAR(1) DEFAULT 'O',
  `ARCHIVE_FLAG` CHAR(1) NOT NULL DEFAULT 'O',
  `DSR_BOOKING_TYPE` INT(2) DEFAULT NULL,
  `DSR_BRANCH_CODE` CHAR(3) NOT NULL,
  `DSR_CNNO` CHAR(12) NOT NULL,
  `DSR_BOOKED_BY` CHAR(1) NOT NULL,
  `DSR_CUST_CODE` VARCHAR(7) NOT NULL,
  `DSR_CN_WEIGHT` DECIMAL(8,3) NOT NULL,
  `DSR_CN_TYPE` CHAR(3) NOT NULL,
  `DSR_DEST` CHAR(3) NOT NULL,
  `DSR_MODE` CHAR(2) NOT NULL,
  `DSR_NO_OF_PIECES` DECIMAL(3,0) NOT NULL,
  `DSR_DEST_PIN` DECIMAL(6,0) DEFAULT NULL,
  `DSR_BOOKING_DATE` DATE NOT NULL,
  `DSR_AMT` DECIMAL(10,2) DEFAULT NULL,
  `DSR_STATUS` CHAR(1) NOT NULL,
  `DSR_POD_RECD` CHAR(1) DEFAULT NULL,
  `DSR_TRANSMF_NO` VARCHAR(10) NOT NULL,
  `DSR_BOOKING_TIME` CHAR(8) NOT NULL,
  `DSR_DOX` CHAR(1) NOT NULL,
  `DSR_SERVICE_TAX` DECIMAL(4,2) DEFAULT NULL,
  `DSR_SPL_DISC` DECIMAL(10,2) DEFAULT NULL,
  `DSR_CONTENTS` VARCHAR(255) DEFAULT NULL,
  `DSR_REMARKS` VARCHAR(25) DEFAULT NULL,
  `DSR_VALUE` VARCHAR(20) DEFAULT NULL,
  `DSR_INVNO` VARCHAR(10) DEFAULT NULL,
  `DSR_INVDATE` DATE DEFAULT NULL,
  `MOD_DATE` DATE DEFAULT NULL,
  `OFFICE_TYPE` CHAR(2) DEFAULT NULL,
  `OFFICE_CODE` CHAR(3) DEFAULT NULL,
  `DSR_REFNO` VARCHAR(50) DEFAULT NULL,
  `MOD_TIME` DECIMAL(4,0) DEFAULT NULL,
  `NODEID` VARCHAR(20) DEFAULT NULL,
  `USERID` VARCHAR(7) DEFAULT NULL,
  `TRANS_STATUS` CHAR(1) DEFAULT NULL,
  `DSR_ACT_CUST_CODE` VARCHAR(11) DEFAULT NULL,
  `DSR_MOBILE` VARCHAR(25) DEFAULT NULL,
  `DSR_EMAIL` VARCHAR(50) DEFAULT NULL,
  `DSR_NDX_PAPER` CHAR(1) DEFAULT NULL,
  `DSR_PICKUP_TIME` DECIMAL(4,0) DEFAULT NULL,
  `DSR_VOL_WEIGHT` DECIMAL(8,3) DEFAULT NULL,
  `DSR_CAPTURED_WEIGHT` DECIMAL(8,3) DEFAULT NULL,
  `DSR_PRODUCT` CHAR(3) DEFAULT NULL,
  `DSR_TRANS_STATUS_XI` CHAR(1) DEFAULT 'A',
  `DSR_TRANS_ID` DECIMAL(10,0) DEFAULT NULL,
  `DSR_ID_TYPE` INT(2) DEFAULT NULL,
  `DSR_ID_NUM` VARCHAR(50) DEFAULT NULL,
  `DSR_WT_CAPTURED` INT(1) DEFAULT NULL,
  `DSR_PUP_FRCODE` VARCHAR(7) DEFAULT NULL,
  `DSR_TS_AMT` DECIMAL(10,2) DEFAULT NULL,
  `DSR_FSC_PER` DECIMAL(4,2) DEFAULT NULL,
  `DSR_RATE_ID` DECIMAL(8,0) DEFAULT NULL COMMENT 'Used to store RateID by which final rate is calculated',
  `RECORD_ENTRY_DATETIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Record_creation_date` DATETIME DEFAULT NULL,
  `Record_Arrival_date` DATETIME DEFAULT NULL,
  `DSR_CONSIGNEE_MOBILE` VARCHAR(11) DEFAULT NULL,
  `DSR_PREV_WEIGHT` DECIMAL(8,3) DEFAULT NULL,
  `DSR_PREV_AMT` DECIMAL(10,2) DEFAULT NULL,
  `DSR_GOR_REMARKS` VARCHAR(50) DEFAULT NULL,
  `DSR_GOR_VOL_WT` DECIMAL(8,3) DEFAULT NULL,
  PRIMARY KEY (`DSR_CNNO`),
  KEY `idx_bcd_cnno_bkdate` (`DSR_BRANCH_CODE`,`DSR_CNNO`,`DSR_BOOKING_DATE`),
  KEY `idx_dsr_bkdate` (`DSR_BOOKING_DATE`),
  KEY `idx_dsr_custcode` (`DSR_CUST_CODE`),
  KEY `idx_dsr_invoiceno` (`DSR_INVNO`),
  KEY `idx_dsr_mdate` (`MOD_DATE`),
  KEY `idx_dsr_bookdby_ccd_cnno` (`DSR_BOOKED_BY`,`DSR_CUST_CODE`,`DSR_CNNO`),
  KEY `idx_dsr_bkdby_ccd_bkd_stat` (`DSR_BOOKED_BY`,`DSR_CUST_CODE`,`DSR_BOOKING_DATE`,`DSR_STATUS`),
  KEY `idx_dsr_refno` (`DSR_REFNO`),
  KEY `idx_dsr_txi_stat_mdate` (`DSR_TRANS_STATUS_XI`,`DSR_STATUS`,`MOD_DATE`),
  KEY `idx_dsr_trans_id` (`DSR_TRANS_ID`),
  KEY `idx_dsr_inv_date` (`DSR_INVDATE`),
  KEY `IDX_Create_arr_date` (`Record_creation_date`,`Record_Arrival_date`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 

这是我正在尝试执行的查询。请帮我解决这个问题。

select ob.BRANCH_CITY orig_city,dstb.BRANCH_CITY dest_city,round(sum(CASE WHEN left(dsr_cnno, 1) IN ('V', 'E', 'X') THEN
IFNULL(value, 0) ELSE 0 END),2) Premium,
round(sum(CASE WHEN left(dsr_cnno, 1) NOT IN ('V', 'E', 'X') THEN  IFNULL(value, 0) ELSE 0 END),2) Non_Premium
from ( select DSR_BRANCH_CODE,dsr_cnno,DSR_AMT,
((dsr_amt) +((((dsr_amt)-ifnull((select max(ndsr_ins_amt) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0)-ifnull((select max(ndsr_serv_charge) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0) -ifnull((select sum(dr_extra_amt) from dsr_revenue where dr_cnno=dsr_cnno ),0))-(((dsr_amt)-ifnull((select max(ndsr_ins_amt) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0) -ifnull((select max(ndsr_serv_charge) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0)
-ifnull((select sum(dr_extra_amt) from dsr_revenue where dr_cnno=dsr_cnno ),0) )*ifnull(((select fr_discount from fr_mas where fr_Code=dsr_cust_code)),0)/100)) *ifnull(((select (case when dsr_invdate <'2017-05-01' then ifnull(fr_fsc_per,0) else 30 end) from fr_mas where fr_code=dsr_cust_code limit 1)),0)/100)) as value, dsr_cust_code
,dsr_dest_pin from dsr_table d where  dsr_booking_date BETWEEN '2017-05-01' AND '2017-06-30' AND LENGTH(dsr_cnno)=9 
AND DSR_BOOKED_BY ='F' AND dsr_status<>'R' AND dsr_cnno NOT LIKE 'J%' AND dsr_cnno NOT LIKE '@%'
AND dsr_cnno NOT LIKE '576%' AND dsr_cnno NOT LIKE 'I3%' AND dsr_cnno NOT LIKE '7%'
AND dsr_cnno NOT LIKE 'N%' and d.dsr_dest_pin>0) zz
inner join fr_mas f on f.FR_CODE=zz.dsr_cust_code
inner join branch_mas ob on ob.BRANCH_CODE=zz.dsr_branch_code and ob.BRANCH_LOC='L'
inner join serv_dest_mas dm on dm.SERV_PIN=zz.dsr_dest_pin 
inner join branch_mas dstb on dstb.BRANCH_CODE=dm.SERV_BRANCH and dstb.BRANCH_LOC='L'
where dstb.BRANCH_CITY in ('HYD','DEL','AMD','GGN','BLR','PNQ','MUM','CHE','CCU','NOD')
and ob.BRANCH_CITY in('HYD','DEL','AMD','GGN','BLR','PNQ','MUM','CHE','CCU','NOD')
group by orig_city,dest_city ; 

这是我的解释计划：

      id  select_type         table          type    possible_keys                                                       key                       key_len  ref                             rows  Extra                                                                
------  ------------------  -------------  ------  ------------------------------------------------------------------  ------------------------  -------  -------------------------  ---------  ---------------------------------------------------------------------
     1  PRIMARY             ob             range   PRIMARY,FK_BRM_CITYMAS                                              FK_BRM_CITYMAS            4        (NULL)                            10  Using index condition; Using where; Using temporary; Using filesort  
     1  PRIMARY             <derived2>     ref     <auto_key2>                                                         <auto_key2>               3        billingdb.ob.BRANCH_CODE      319051  Using where                                                          
     1  PRIMARY             f              eq_ref  PRIMARY                                                             PRIMARY                   9        zz.dsr_cust_code                   1  Using index                                                          
     1  PRIMARY             dm             ref     PRIMARY,IDX_SDM_SERVPIN_STATUS1                                     IDX_SDM_SERVPIN_STATUS1   3        zz.dsr_dest_pin                    2  Using index                                                          
     1  PRIMARY             dstb           eq_ref  PRIMARY,FK_BRM_CITYMAS                                              PRIMARY                   3        billingdb.dm.SERV_BRANCH           1  Using where                                                          
     2  DERIVED             d              ref     idx_dsr_bkdate,idx_dsr_bookdby_ccd_cnno,idx_dsr_bkdby_ccd_bkd_stat  idx_dsr_bookdby_ccd_cnno  1        const                      182365315  Using index condition; Using where                                   
    10  DEPENDENT SUBQUERY  fr_mas         eq_ref  PRIMARY                                                             PRIMARY                   9        billingdb.d.DSR_CUST_CODE          1  (NULL)                                                               
     9  DEPENDENT SUBQUERY  fr_mas         eq_ref  PRIMARY                                                             PRIMARY                   9        billingdb.d.DSR_CUST_CODE          1  (NULL)                                                               
     8  DEPENDENT SUBQUERY  dsr_revenue    ref     PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     7  DEPENDENT SUBQUERY  ndx_dsr_table  eq_ref  PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     6  DEPENDENT SUBQUERY  ndx_dsr_table  eq_ref  PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     5  DEPENDENT SUBQUERY  dsr_revenue    ref     PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     4  DEPENDENT SUBQUERY  ndx_dsr_table  eq_ref  PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     3  DEPENDENT SUBQUERY  ndx_dsr_table  eq_ref  PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               

Update

我尝试也在 INDEX(DSR_BOOKED_BY, dsr_booking_date, dsr_cnno、dsr_status、dsr_cnno、dsr_dist_pin、id）但没有运气。

服务器的 RAM 大小为 16GB，innodb 缓冲池大小为 12GB

这个查询花了将近8个小时但最终没有结果

UPDATE

适用于特定日期的查询：

SELECT  ob.BRANCH_CITY orig_city, 
    (
        SELECT  LEFT(branch_code,1)
            FROM  branch_mas c,serv_dest_mas b
            WHERE  c.branch_loc='L'
              AND  LEFT(c.branch_code,1) IN ('K','H','C','B','M','A',
                        'P','N','S','L'
                       )
              AND  c.branch_code=b.serv_branch
              AND  b.serv_pin=zz.dsr_dest_pin
        LIMIT  1
    ) dest_city,
        ROUND(SUM(CASE WHEN LEFT(dsr_cnno, 1) IN ('V', 'E', 'X')
                       THEN IFNULL(VALUE,
                        0) ELSE 0 END),2) Premium,
        ROUND(SUM(CASE WHEN LEFT(dsr_cnno,
       1) NOT IN ('V', 'E', 'X') THEN IFNULL(VALUE,
       0) ELSE 0 END),2
             ) Non_Premium FROM
    (
        SELECT  DSR_BRANCH_CODE,dsr_cnno,DSR_AMT,
                    ((dsr_amt) +((((dsr_amt) -IFNULL( (
               SELECT  MAX(ndsr_serv_charge)
                   FROM  ndx_dsr_table
                   WHERE  ndsr_cnno=dsr_cnno ),0) -IFNULL(
           (
               SELECT  SUM(dr_extra_amt)
                   FROM  dsr_revenue
                   WHERE  dr_cnno=dsr_cnno ),0))-(((dsr_amt) -IFNULL(
                   (
                       SELECT  MAX(ndsr_ins_amt)
                           FROM  ndx_dsr_table
                           WHERE  ndsr_cnno=dsr_cnno ),0
                          ) -IFNULL(
                   (
                       SELECT  MAX(ndsr_serv_charge)
                           FROM  ndx_dsr_table
                           WHERE  ndsr_cnno=dsr_cnno ),0
                          ) -IFNULL(
                   (
                       SELECT  SUM(dr_extra_amt)
                           FROM  dsr_revenue
                           WHERE  dr_cnno=dsr_cnno ),0) 
                          ) *IFNULL((
                   (
                       SELECT  fr_discount
                           FROM  fr_mas
                           WHERE  fr_Code=dsr_cust_code)),0)/100)) *
                           IFNULL(( (
       SELECT  (CASE WHEN dsr_invdate <'2017-05-01'
                     THEN IFNULL(fr_fsc_per,
                               0) ELSE 30 END )
           FROM  fr_mas
           WHERE  fr_code=dsr_cust_code
           LIMIT  1)),0)/100)
                    ) AS VALUE, dsr_cust_code, dsr_dest_pin
            FROM  dsr_table d
            WHERE  dsr_booking_date = '2017-04-30'
              AND  LENGTH(dsr_cnno)=9
              AND  DSR_BOOKED_BY ='F'
              AND  dsr_status<>'R'
              AND  d.dsr_dest_pin>0 
    ) zz
    INNER JOIN  fr_mas f  ON f.FR_CODE=zz.dsr_cust_code
    INNER JOIN  branch_mas ob  ON ob.BRANCH_CODE=zz.dsr_branch_code
      AND  ob.BRANCH_LOC='L'
    WHERE  ob.BRANCH_CITY IN('HYD','DEL','AMD','GGN','BLR','PNQ',
                'MUM','CHE','CCU','NOD'
                          )
      AND  d.dsr_cnno = f.dsr_cnno

如果我们指定一个月，上面的代码不适用于日期范围：

SELECT  ob.BRANCH_CITY orig_city, 
    (
        SELECT  LEFT(branch_code,1)
            FROM  branch_mas c,serv_dest_mas b
            WHERE  c.branch_loc='L'
              AND  LEFT(c.branch_code,1) IN ('K','H','C','B','M','A',
                        'P','N','S','L'
                       )
              AND  c.branch_code=b.serv_branch
              AND  b.serv_pin=zz.dsr_dest_pin
        LIMIT  1
    ) dest_city,
        ROUND(SUM(CASE WHEN LEFT(dsr_cnno, 1) IN ('V', 'E', 'X')
                       THEN IFNULL(VALUE,
                        0) ELSE 0 END),2) Premium,
        ROUND(SUM(CASE WHEN LEFT(dsr_cnno,
       1) NOT IN ('V', 'E', 'X') THEN IFNULL(VALUE,
       0) ELSE 0 END),2
             ) Non_Premium FROM
    (
        SELECT  DSR_BRANCH_CODE,dsr_cnno,DSR_AMT,
                    ((dsr_amt) +((((dsr_amt) -IFNULL( (
               SELECT  MAX(ndsr_serv_charge)
                   FROM  ndx_dsr_table
                   WHERE  ndsr_cnno=dsr_cnno ),0) -IFNULL(
           (
               SELECT  SUM(dr_extra_amt)
                   FROM  dsr_revenue
                   WHERE  dr_cnno=dsr_cnno ),0))-(((dsr_amt) -IFNULL(
                   (
                       SELECT  MAX(ndsr_ins_amt)
                           FROM  ndx_dsr_table
                           WHERE  ndsr_cnno=dsr_cnno ),0
                          ) -IFNULL(
                   (
                       SELECT  MAX(ndsr_serv_charge)
                           FROM  ndx_dsr_table
                           WHERE  ndsr_cnno=dsr_cnno ),0
                          ) -IFNULL(
                   (
                       SELECT  SUM(dr_extra_amt)
                           FROM  dsr_revenue
                           WHERE  dr_cnno=dsr_cnno ),0) 
                          ) *IFNULL((
                   (
                       SELECT  fr_discount
                           FROM  fr_mas
                           WHERE  fr_Code=dsr_cust_code)),0)/100)
                          ) *IFNULL((
   (
       SELECT  (CASE WHEN dsr_invdate <'2017-05-01'
                     THEN IFNULL(fr_fsc_per,
                               0) ELSE 30 END )
           FROM  fr_mas
           WHERE  fr_code=dsr_cust_code
           LIMIT  1)),0)/100)
                    ) AS VALUE, dsr_cust_code, dsr_dest_pin
            FROM  dsr_table d
            WHERE  dsr_booking_date = '2017-04-30'
              AND  '2017-05-30'
              AND  LENGTH(dsr_cnno)=9
              AND  DSR_BOOKED_BY ='F'
              AND  dsr_status<>'R'
              AND  d.dsr_dest_pin>0 
    ) zz
    INNER JOIN  fr_mas f  ON f.FR_CODE=zz.dsr_cust_code
    INNER JOIN  branch_mas ob  ON ob.BRANCH_CODE=zz.dsr_branch_code
      AND  ob.BRANCH_LOC='L'
    WHERE  ob.BRANCH_CITY IN('HYD','DEL','AMD','GGN','BLR','PNQ',
                'MUM','CHE','CCU','NOD'
                          )
      AND  d.dsr_cnno = f.dsr_cnno 

请提供SHOW CREATE TABLE.

主要过滤器似乎是

            where  dsr_booking_date BETWEEN '2017-05-01' AND '2017-06-30'
              AND  LENGTH(dsr_cnno)=9
              AND  DSR_BOOKED_BY ='F'
              AND  dsr_status<>'R'
              AND  dsr_cnno NOT LIKE 'J%'
              AND  dsr_cnno NOT LIKE '@%'
              AND  dsr_cnno NOT LIKE '576%'
              AND  dsr_cnno NOT LIKE 'I3%'
              AND  dsr_cnno NOT LIKE '7%'
              AND  dsr_cnno NOT LIKE 'N%'
              and  d.dsr_dest_pin>0

可能唯一有用的索引是，按以下顺序：

INDEX(DSR_BOOKED_BY, dsr_booking_date)

像

ifnull((select max(ndsr_ins_amt)     from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0)-
ifnull((select max(ndsr_serv_charge) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0) -

也许应该一起完成。考虑类似的事情

ifnull(mm.max_nia), 0) -
ifnull(mm.max_nsc), 0) .
...
LEFT JOIN ( SELECT max(ndsr_ins_amt)     AS max_nia,
                   max(ndsr_serv_charge) AS max_nsc
                from ndx_dsr_table
          ) AS mm  ON ndsr_cnno=dsr_cnno

或者，如果需要，使用该子查询构建一个临时表，然后 LEFT JOIN 到它。

（由于您尚未使用其所在的表来限定每一列，因此我无法更具体。）

您是否有适合各种情况的“综合”索引JOINs?

根据EXPLAIN，它正在扫描 182M 行dsr_table。因此，我上面的索引可能会有所帮助（如果您还没有类似的索引）。

我犹豫是否建议这么长的索引，但这可能会有所帮助：

INDEX(DSR_BOOKED_BY, dsr_booking_date,  -- these first, in this order
      dsr_cnno, dsr_status, dsr_cnno, dsr_dist_pin,  -- in any order
      id)   -- (whatever the PK of the table is); last

第二次查询中的坏问题

        WHERE  dsr_booking_date = '2017-04-30'
          AND  '2017-05-30'

也许你的意思是 31 天：

        WHERE  dsr_booking_date BETWEEN '2017-04-30'
                                   AND  '2017-05-30'

或者也许 2 天：

        WHERE  dsr_booking_date IN ('2017-04-30', '2017-05-30')

你拥有的是

        WHERE  dsr_booking_date = '2017-04-30'  -- test for one day
          AND  true  -- that's how '2017-05-30' is interpreted