MySQL优化庞大的查询

巫师

我有很大的SQL查询:

SELECT
    partner_id,
    the_date,
    SUM(clicks) as clicks,
    SUM(total_count) as total_count,
    SUM(count) as count,
    SUM(total_sum) as total_sum,
    SUM(received_sum) as received_sum,
    SUM(partner_fee) as partner_fee
FROM (
    SELECT
        clicks.partner_id,
        clicks.click_date as the_date,
        clicks,
        orders.total_count,
        orders.count,
        orders.total_sum,
        orders.received_sum,
        orders.partner_fee
    FROM
        (SELECT
            partner_id, click_date, sum(clicks) as clicks
        FROM
            daily_metric WHERE DATE(click_date) BETWEEN '2013-04-01' AND '2013-04-30'
        GROUP BY partner_id , click_date) as clicks
        LEFT JOIN
        (SELECT
            partner_id,
                DATE(order_date) as order_dates,
                SUM(order_sum) as total_sum,
                SUM(customer_paid_sum) as received_sum,
                SUM(partner_fee) as partner_fee,
                count(*) as total_count,
                count(CASE
                    WHEN status = 1 THEN 1
                    ELSE NULL
                END) as count
        FROM
            transaction WHERE DATE(order_date) BETWEEN '2013-04-01' AND '2013-04-30'
        GROUP BY DATE(order_date) , partner_id) as orders ON orders.partner_id = clicks.partner_id AND clicks.click_date = orders.order_dates
    UNION ALL SELECT
        orders.partner_id,
        orders.order_dates as the_date,
        clicks,
        orders.total_count,
        orders.count,
        orders.total_sum,
        orders.received_sum,
        orders.partner_fee
    FROM
        (SELECT
            partner_id, click_date, sum(clicks) as clicks
        FROM
            daily_metric  WHERE DATE(click_date) BETWEEN '2013-04-01' AND '2013-04-30'
        GROUP BY partner_id , click_date) as clicks
            RIGHT JOIN
        (SELECT
            partner_id,
                DATE(order_date) as order_dates,
                SUM(order_sum) as total_sum,
                SUM(customer_paid_sum) as received_sum,
                SUM(partner_fee) as partner_fee,
                count(*) as total_count,
                count(CASE
                    WHEN status = 1 THEN 1
                    ELSE NULL
                END) as count
        FROM
            transaction  WHERE DATE(order_date) BETWEEN '2013-04-01' AND '2013-04-30'
        GROUP BY DATE(order_date) , partner_id) as orders ON orders.partner_id = clicks.partner_id AND clicks.click_date = orders.order_dates
    WHERE
        clicks.partner_id is NULL
    ORDER BY the_date DESC
    ) as t
    GROUP BY the_date ORDER BY the_date DESC LIMIT 50 OFFSET 0

这是我的查询的解释:

+----+--------------+--------------+--------+---------------+---------+---------+------+---------+----------------------------------------------+
| id | select_type  | table        | type   | possible_keys | key     | key_len | ref  | rows    | Extra                                        |
+----+--------------+--------------+--------+---------------+---------+---------+------+---------+----------------------------------------------+
|  1 | PRIMARY      | <derived2>   | ALL    | NULL          | NULL    | NULL    | NULL |     162 | Using temporary; Using filesort              |
|  2 | DERIVED      | NULL         | NULL   | NULL          | NULL    | NULL    | NULL |    NULL | no matching row in const table               |
|  4 | DERIVED      | transaction  | ALL    | NULL          | NULL    | NULL    | NULL |  280118 | Using where; Using temporary; Using filesort |
|  3 | DERIVED      | daily_metric | index  | NULL          | PRIMARY | 1541    | NULL | 9370157 | Using where                                  |
|  5 | UNION        | <derived6>   | system | NULL          | NULL    | NULL    | NULL |       0 | const row not found                          |
|  5 | UNION        | <derived7>   | ALL    | NULL          | NULL    | NULL    | NULL |     162 |                                              |
|  7 | DERIVED      | transaction  | ALL    | NULL          | NULL    | NULL    | NULL |  280118 | Using where; Using temporary; Using filesort |
|  6 | DERIVED      | daily_metric | index  | NULL          | PRIMARY | 1541    | NULL | 9370157 | Using where                                  |
| NULL | UNION RESULT | <union2,5>   | ALL    | NULL          | NULL    | NULL    | NULL |    NULL | Using filesort                               |
+----+--------------+--------------+--------+---------------+---------+---------+------+---------+----------------------------------------------+
9 rows in set (12.92 sec)

我需要任何命题如何将该查询优化为> 5s

表索引:

mysql> show index from transaction
    -> ;
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| transaction |          0 | PRIMARY    |            1 | id          | A         |      279478 |     NULL | NULL   |      | BTREE      |         |               |
| transaction |          1 | partner_id |            1 | partner_id  | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| transaction |          1 | updated_at |            1 | updated_at  | A         |         495 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> show index from daily_metric;
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| daily_metric |          0 | PRIMARY           |            1 | partner_id  | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          0 | PRIMARY           |            2 | click_date  | A         |       10776 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          0 | PRIMARY           |            3 | utm_content | A         |      700476 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          0 | PRIMARY           |            4 | utm_term    | A         |     9806670 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          1 | partner_id_index  |            1 | partner_id  | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          1 | utm_content_index |            1 | utm_content | A         |      891515 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          1 | utm_term_index    |            1 | utm_term    | A         |     9806670 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.03 sec)

daily_metric有约1000万记录。

GolezTrol

您通过(where子句)进行过滤transaction.order_date我没有看到该字段的索引。添加它应该已经有所作为。

另外,click_date用于过滤,但与处于单独的索引(PK)中partner_id由于您不按进行过滤partner_id,因此您也可以受益于单独的索引click_date

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章