我有很大的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万记录。
您通过(where子句)进行过滤transaction.order_date
。我没有看到该字段的索引。添加它应该已经有所作为。
另外,click_date
用于过滤,但与处于单独的索引(PK)中partner_id
。由于您不按进行过滤partner_id
,因此您也可以受益于单独的索引click_date
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句