*为与*动云做的云计算框架的sql优化

kelvin19840813 2019-03-03 原文

原语句:
SELECT
    ifnull(system_name, "ALL") AS system_name,
    ifnull(DISK, 0) AS disk
FROM
    (
        SELECT
            ROUND(SUM(disk_per) / COUNT(*), 4) AS DISK,
            s1.SYSTEM_ID
        FROM
            vm_info_tab p1
        LEFT JOIN (
            SELECT
                ROUND(
                    (
                        ifnull(SUM(disk_total), disk_free) - SUM(disk_free)
                    ) / SUM(disk_total),
                    4
                ) * 100 AS disk_per,
                per_hid
            FROM
                x86.vm_performance_tab f1
            WHERE
                f1.Per_Time > TIMESTAMPADD(WEEK, - 1, NOW())
            GROUP BY
                per_hid
        ) AS perf_value ON p1.ip = perf_value.per_hid
        LEFT JOIN system_device_relation_tab s1 ON p1.vm_id = s1.DEVICE_ID
        GROUP BY s1.`SYSTEM_ID`
    ) p1
LEFT JOIN system_info_tab p2 ON p1.system_id = p2.`system_id`;

+————-+——–+
| system_name | disk |
+————-+——–+
| ALL | 0.0000 |
+————-+——–+
1 row in set (6.30 sec)　　　　　　<——— 0条记录使用了 6.30秒

1. 最大问题在于 join 后进行 group by , 两表 join时候会有很多数据进行匹配 , group 有去重功能 , 可以先对数据进行去重 , 然后再进行join , 这样减少join数据时候比对量 , 并且用上了索引了 , 原语句只用了join 字段时候的索引 , 最后group by就要从表返回数据,这就是慢的原因之一 , 第3个操作就是用了 using where 过滤元组和是否读取数据文件或索引文件没有关系 , mysql官档:

Using where
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

2. 分析后主要有2个表进行group by , f1 和 s1 表

修改后:
SELECT
    ifnull( system_name, "ALL" ) AS system_name,
    ifnull( DISK, 0 ) AS disk 
FROM
    (
    SELECT
        ROUND( SUM( disk_per ) / COUNT( * ), 4 ) AS DISK,
        s1.SYSTEM_ID 
    FROM
        vm_info_tab p1
        LEFT JOIN (
        SELECT
            ROUND( ( ifnull( SUM( disk_total ), disk_free ) - SUM( disk_free ) ) / SUM( disk_total ), 4 ) * 100 AS disk_per,
            per_hid 
        FROM
            (
            SELECT
                disk_total,
                disk_free,
                per_hid 
            FROM
                x86.vm_performance_tab f1
                INNER JOIN ( SELECT id FROM x86.vm_performance_tab f1 GROUP BY per_hid ) a ON f1.id = a.id 
            WHERE
                f1.Per_Time > TIMESTAMPADD (
                    WEEK, - 1,
                NOW ()) 
            ) f1 
        GROUP BY
            f1.per_hid 
        ) AS perf_value ON p1.ip = perf_value.per_hid
        LEFT JOIN system_device_relation_tab s1 ON p1.vm_id = s1.DEVICE_ID 
    GROUP BY
        s1.` SYSTEM_ID ` 
    ) p1
    LEFT JOIN system_info_tab p2 ON p1.system_id = p2.` system_id `;