问请帮我优化MYSQL查询

EN

SELECT  

    ( 
        SELECT SUM(ile.gross)                   
        FROM item_ledger_entry AS ile 
        WHERE clr.docNr=ile.docNr 
        AND clr.serialNo=ile.serialNo 
        AND clr.productNr=ile.productNr 
        AND (ile.cargoLine=clr.id OR ile.orgLine=clr.id) 
        AND ile.id NOT IN (
                            SELECT MIN(zz.id) 
                            FROM item_ledger_entry AS zz 
                            WHERE zz.docNr=ile.docNr 
                            AND zz.serialNo=ile.serialNo 
                            AND zz.productNr=ile.productNr
                          ) 
        AND Date_format(ile.activityDate, '%Y-%m-%d') <= '2019-08-05' 
        AND ile.status='40' 
    ) AS gross_end_out

FROM cargo_line_receive AS clr 

LEFT JOIN cargo_header_receive AS chr 
ON clr.docNr=chr.docNr  

LEFT JOIN n_items AS ni 
ON clr.productNr=ni.code 

LEFT JOIN item_ledger_entry AS i 
ON clr.docNr=i.docNr AND i.status=40 
AND (i.id=clr.id OR i.orgLine=clr.id)   


WHERE ((clr.status='20' OR clr.status='30') && clr.status!='40')  

AND Date_format(clr.activityDate, '%Y-%m-%d') <= '2019-08-05' 

GROUP BY clr.id, clr.docNr, clr.batchNo, clr.serialNo, clr.productNr
ORDER BY clr.activityDate DESC, clr.id DESC

CREATE TABLE `cargo_header_receive` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `docNr` VARCHAR(50) NULL DEFAULT '0',

    PRIMARY KEY (`id`),
    INDEX `docNr` (`docNr`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

CREATE TABLE `cargo_line_receive` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `docNr` VARCHAR(250) NOT NULL DEFAULT '',
    `batchNo` VARCHAR(250) NULL DEFAULT '',
    `serialNo` VARCHAR(250) NULL DEFAULT '',
    `productNr` VARCHAR(250) NOT NULL DEFAULT '',   
    `activityDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    `status` INT(11) NOT NULL DEFAULT '0',
    `orgLine` INT(11) NOT NULL DEFAULT '0',

    PRIMARY KEY (`id`),
    INDEX `docNr` (`docNr`),
    INDEX `serialNo` (`serialNo`),
    INDEX `productNr` (`productNr`),
    INDEX `status` (`status`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

CREATE TABLE `item_ledger_entry` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `docNr` VARCHAR(250) NOT NULL DEFAULT '0',
    `cargoLine` INT(11) NULL DEFAULT NULL,
    `activityDate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    `productNr` VARCHAR(250) NOT NULL,
    `orgLine` INT(11) NULL DEFAULT '0',
    `status` INT(11) NOT NULL DEFAULT '0',  
    `serialNo` VARCHAR(250) NULL DEFAULT '0',   
    `gross` DECIMAL(20,10) NOT NULL DEFAULT '0.0000000000',

    PRIMARY KEY (`id`),
    INDEX `cargoLine` (`cargoLine`),
    INDEX `orgLine` (`orgLine`),
    INDEX `docNr` (`docNr`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

CREATE TABLE `n_items` (
    `code` VARCHAR(250) NOT NULL,

    PRIMARY KEY (`code`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

id | select_type        | table | type | possible_keys           | key   | key_len | ref                | rows | Extra
---------------------------------------------------------------------------------------------------------------------------------------------------------
1  | PRIMARY            | clr   | ALL  | status                  |       |         |                    | 3043 | Using where; Using temporary; Using filesort
1  | PRIMARY            | chr   | ref  | docNr                   | docNr | 153     | database.clr.docNr | 1    | Using where; Using index
1  | PRIMARY            | i     | ref  | PRIMARY,orgLine,docNr   | docNr | 752     | database.clr.docNr | 5    | Using where
2  | DEPENDENT SUBQUERY | ile   | ref  | cargoLine,orgLine,docNr | docNr | 752     | func               | 5    | Using where
3  | DEPENDENT SUBQUERY | zz    | ref  | docNr                   | docNr | 752     | database.ile.docNr | 5    | Using where