mysql derived2、Using temporary 和 Using filesort 优化记录

-- 业务每周查询一份报表，每次执行sql磁盘直接由49%涨到%93，执行了几次发现是sql用到了磁盘临时表，对sql进行了改写
-- 原查询sql：

SELECT
    CODE,
    NAME,
    num,
    GROUP_CONCAT(product_id) AS product_ids,
    GROUP_CONCAT(product_name) AS product_names
FROM
    (SELECT
        COUNT(DISTINCT (psn.Psnid)) AS num,
            cus.CODE,
            cus.`name`,
            od.product_id,
            pr.NAME AS product_name
    FROM
        mid_psn_copy AS psn
    JOIN customer AS cus ON cus.CODE = psn.PkCorp
    JOIN product_order_rel AS od ON od.customer_code = cus.CODE
    JOIN product AS pr ON pr.id = od.product_id
    WHERE
        psn.`Status` = 1 AND od.`status` = 1
    GROUP BY od.customer_code , od.product_id) AS tt
GROUP BY CODE;
        
-- 69 rows in set, 1 warning (34.54 sec)

-- 原执行计划 

+----+-------------+------------+--------+---------------------------------------------------------------------------+------------------------------+---------+------------------------+--------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys                                                             | key                          | key_len | ref                    | rows   | Extra                                        |
+----+-------------+------------+--------+---------------------------------------------------------------------------+------------------------------+---------+------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                                      | NULL                         | NULL    | NULL                   | 325296 | Using filesort                               |
|  2 | DERIVED     | cus        | index  | idx_custom_code,idx_code_name                                             | idx_code_name                | 274     | NULL                   |    251 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | psn        | ref    | idx_psn_pkcorp,idx_psn_Status,idx_pkcrop_status                           | idx_pkcrop_status            | 157     | gmcloud.cus.code,const |     36 | Using index condition                        |
|  2 | DERIVED     | od         | ref    | idx_customer_code_pdid_status,idx_product_id,idx_customer_code_product_id | idx_customer_code_product_id | 123     | gmcloud.cus.code       |     36 | Using where; Using index                     |
|  2 | DERIVED     | pr         | eq_ref | PRIMARY                                                                   | PRIMARY                      | 8       | gmcloud.od.product_id  |      1 | NULL                                         |
+----+-------------+------------+--------+---------------------------------------------------------------------------+------------------------------+---------+------------------------+--------+----------------------------------------------+ 

 

-- 第一次优化，去掉外层嵌套，把外层条件融合到当前sql
-- 加联合索引
-- alter table customer add key idx_code_name (`code`,`name`)
-- alter table product_order_rel add key idx_customer_code_pdid_status (`customer_code`,`product_id`,`status`)

SELECT
        COUNT(DISTINCT (psn.Psnid)) AS num,
            cus.CODE,
            cus.`name`,
            GROUP_CONCAT(od.product_id) AS product_ids,
            GROUP_CONCAT(pr.NAME) AS product_names
    FROM
        mid_psn_copy AS psn
    JOIN customer AS cus ON cus.CODE = psn.PkCorp
    JOIN product_order_rel AS od ON od.customer_code = cus.CODE
    JOIN product AS pr ON pr.id = od.product_id
    WHERE
        psn.`Status` = 1 AND od.`status` = 1
    GROUP BY cus.CODE, od.customer_code , od.product_id;

-- 查看执行计划，虽然没有了DERIVED、derived2，但是看到还有Using temporary; Using filesort存在

+----+-------------+-------+--------+-------------------------------+-------------------------------+---------+-----------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                 | key                           | key_len | ref                   | rows | Extra                                        |
+----+-------------+-------+--------+-------------------------------+-------------------------------+---------+-----------------------+------+----------------------------------------------+
|  1 | SIMPLE      | cus   | index  | idx_custom_code,idx_code_name | idx_code_name                 | 274     | NULL                  |  251 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | psn   | ref    | idx_psn_pkcorp,idx_psn_Status | idx_psn_pkcorp                | 152     | gmcloud.cus.code      |   69 | Using index condition; Using where           |
|  1 | SIMPLE      | od    | ref    | idx_customer_code_pdid_status | idx_customer_code_pdid_status | 123     | gmcloud.cus.code      |   36 | Using where; Using index                     |
|  1 | SIMPLE      | pr    | eq_ref | PRIMARY                       | PRIMARY                       | 8       | gmcloud.od.product_id |    1 | NULL                                         |
+----+-------------+-------+--------+-------------------------------+-------------------------------+---------+-----------------------+------+----------------------------------------------+ 

-- 第二次优化，把group by 后边的od.customer_code , od.product_id去掉，在前边group_concat加上distinct    
-- 在where条件和group by条件都加了联合索引
-- alter table customer add key idx_code_name (`code`,`name`)
-- alter table mid_psn_copy add key idx_pkcrop_status` (`PkCorp`,`Status`)
-- alter table product_order_rel add key idx_customer_code_product_id (`customer_code`,`product_id`,`status`)
-- 优化完的sql：

SELECT
        COUNT(DISTINCT (psn.Psnid)) AS num,
        cus.CODE,
        cus.`name`,
        GROUP_CONCAT(distinct od.product_id) AS product_ids,
        GROUP_CONCAT(distinct pr.NAME) AS product_names
    FROM
        customer AS cus
    JOIN product_order_rel AS od ON od.customer_code = cus.CODE
    JOIN mid_psn_copy AS psn ON  psn.PkCorp = cus.CODE
    JOIN product AS pr ON pr.id = od.product_id
    WHERE
        psn.`Status` = 1 AND od.`status` = 1
    GROUP BY cus.CODE;

-- 再次查看执行计划

+----+-------------+-------+--------+-------------------------------------------------+------------------------------+---------+------------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys                                   | key                          | key_len | ref                    | rows | Extra                    |
+----+-------------+-------+--------+-------------------------------------------------+------------------------------+---------+------------------------+------+--------------------------+
|  1 | SIMPLE      | cus   | index  | idx_code_name,idx_code                          | idx_code_name                | 274     | NULL                   |  251 | Using index              |
|  1 | SIMPLE      | psn   | ref    | idx_psn_pkcorp,idx_psn_Status,idx_pkcrop_status | idx_pkcrop_status            | 157     | gmcloud.cus.code,const |   36 | Using index condition    |
|  1 | SIMPLE      | od    | ref    | idx_product_id,idx_customer_code_product_id     | idx_customer_code_product_id | 123     | gmcloud.cus.code       |   36 | Using where; Using index |
|  1 | SIMPLE      | pr    | eq_ref | PRIMARY                                         | PRIMARY                      | 8       | gmcloud.od.product_id  |    1 | NULL                     |
+----+-------------+-------+--------+-------------------------------------------------+------------------------------+---------+------------------------+------+--------------------------+

-- 调整下表头顺序

SELECT
    cus.CODE,
    cus.`name`,
    COUNT(DISTINCT (psn.Psnid)) AS num,
    GROUP_CONCAT(DISTINCT od.product_id) AS product_ids,
    GROUP_CONCAT(DISTINCT pr.NAME) AS product_names
FROM
    customer AS cus
        JOIN
    product_order_rel AS od ON od.customer_code = cus.CODE
        JOIN
    mid_psn_copy AS psn ON psn.PkCorp = cus.CODE
        JOIN
    product AS pr ON pr.id = od.product_id
WHERE
    psn.`Status` = 1 AND od.`status` = 1
GROUP BY cus.CODE;
-- 69 rows in set (6.95 sec)

-- 执行sql后，查看临时表状态没有增加了
mysql> show status like '%tmp%';

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 619   |
| Created_tmp_tables      | 3     |
+-------------------------+-------+

    
优化待续。。。