mysql - 如何加快 sql 查询速度?
问题描述
假设有两个表,例如:
表campaign
:
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| campaign_id | varchar(64) | YES | UNI | NULL | |
| account_id | varchar(64) | YES | MUL | NULL | |
| name | blob | YES | | NULL | |
| objective | varchar(64) | YES | | NULL | |
| can_use_spend_cap | tinyint(2) | YES | | NULL | |
| status | varchar(64) | YES | | NULL | |
| spend_cap | bigint(14) | YES | | 0 | |
| effective_status | char(128) | YES | | NULL | |
| promoted_object | text | YES | | NULL | |
| lifetime_budget | bigint(14) | YES | | NULL | |
| daily_budget | bigint(14) | YES | | NULL | |
| update_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| create_time | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
| task_status | tinyint(3) | YES | | NULL | |
| is_smb | tinyint(3) | YES | | NULL | |
| rule_created | tinyint(4) | YES | | 0 | |
| access_token | varchar(255) | YES | MUL | NULL | |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
和表campaign_statistic
+---------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| campaign_id | varchar(64) | YES | MUL | NULL | |
| account_id | varchar(64) | YES | | NULL | |
| impressions | int(11) | YES | | NULL | |
| clicks | int(11) | YES | | NULL | |
| spend | decimal(14,6) | YES | | NULL | |
| cpc | decimal(14,6) | YES | | NULL | |
| cpm | decimal(14,6) | YES | | NULL | |
| cpp | decimal(14,6) | YES | | NULL | |
| ctr | decimal(14,6) | YES | | NULL | |
| cost_per_purchase | decimal(14,6) | YES | | NULL | |
| cost_per_addtocart | decimal(14,6) | YES | | NULL | |
| fb_pixel_addtocart | int(11) | YES | | NULL | |
| fb_pixel_addtocart_values | decimal(14,6) | YES | | NULL | |
| fb_pixel_purchase | int(11) | YES | | NULL | |
| fb_pixel_purchase_values | decimal(14,6) | YES | | NULL | |
| roas | decimal(14,6) | YES | | NULL | |
| roi | decimal(14,6) | YES | | NULL | |
| dt | date | YES | | NULL | |
+---------------------------+---------------+------+-----+---------+----------------+
我想加入这两个表然后做一个order
,search
然后time_range
工作。
我已经尝试过这样的糟糕尝试:
EXPLAIN SELECT
campaign.id AS id,
campaign.campaign_id AS campaign_id,
campaign.account_id AS account_id,
campaign.daily_budget AS daily_budget,
campaign.lifetime_budget AS lifetime_budget,
campaign.name AS name,
campaign.effective_status AS effective_status,
campaign.daily_budget AS daily_budget,
statistic.spend AS spend,
statistic.clicks AS clicks,
statistic.impressions AS impressions,
statistic.spend AS spend,
statistic.fb_pixel_addtocart AS fb_pixel_addtocart,
statistic.fb_pixel_addtocart_values AS fb_pixel_addtocart_values,
statistic.fb_pixel_purchase AS fb_pixel_purchase,
statistic.fb_pixel_purchase_values AS fb_pixel_purchase_values,
statistic.cpc AS cpc,
statistic.ctr AS ctr,
statistic.cpm AS cpm,
statistic.cost_per_addtocart AS cost_per_addtocart,
statistic.cost_per_purchase AS cost_per_purchase,
statistic.roi AS roi,
statistic.roas AS roas,
campaign.rule_created AS rule_created,
campaign.is_smb AS is_smb
FROM `campaign` as campaign
LEFT JOIN
(SELECT
campaign_id,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
ROUND(SUM(spend),2) AS spend,
SUM(fb_pixel_addtocart) AS fb_pixel_addtocart,
ROUND(SUM(fb_pixel_addtocart_values),2) AS fb_pixel_addtocart_values,
ROUND(SUM(fb_pixel_purchase),2) AS fb_pixel_purchase,
ROUND(SUM(fb_pixel_purchase_values),2) AS fb_pixel_purchase_values,
ROUND(SUM(clicks)*100/SUM(impressions), 6) AS ctr,
ROUND(SUM(spend)/SUM(clicks), 6) AS cpc,
ROUND(SUM(spend)*1000/SUM(impressions), 6) AS cpm,
ROUND(SUM( spend )/SUM(fb_pixel_addtocart), 6) AS cost_per_addtocart,
ROUND(SUM( spend )/SUM(fb_pixel_purchase), 6) AS cost_per_purchase,
ROUND(SUM( fb_pixel_purchase )/SUM(spend), 6) AS roi,
ROUND(SUM( fb_pixel_purchase )/SUM(spend), 6) AS roas
FROM `campaign_statistic` WHERE dt BETWEEN 2019-07-08 AND 2019-07-08 GROUP BY campaign_id) statistic
ON statistic.campaign_id = campaign.campaign_id
WHERE campaign.account_id = 1000623940119431
AND (campaign.campaign_id LIKE "%%" OR campaign.name LIKE "%%") ORDER BY id ASC
它可以工作,但查询速度很慢,对不起,我是 mysql 新手。
如何优化 sql 并加快查询速度?
非常欢迎任何评论。太谢谢了。
更新问题:
使用时,我会使用SUM
,ROUND
或其他函数计算功。看起来
没有使用索引,查询速度更慢。为什么会这样?
1)
EXPLAIN
SELECT campaign_id
FROM `campaign_statistic`
WHERE dt >= '2019-03-01' AND dt <= '2019-03-31'
GROUP BY fb_campaign_id
+----+-------------+----------------------------+-------+--------------------+---------------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------+-------+--------------------+---------------+---------+------+--------+-----------------------------------------------------------+
| 1 | SIMPLE | campaign_statistic | range | campaign_date,date | campaign_date | 4 | NULL | 567748 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+----------------------------+-------+--------------------+---------------+---------+------+--------+-----------------------------------------------------------+
2)
EXPLAIN
SELECT campaign_id,
SUM(impressions) AS impressions
FROM `campaign_statistic`
WHERE dt >= '2019-03-01' AND dt <= '2019-03-31'
GROUP BY fb_campaign_id
+----+-------------+----------------------------+------+--------------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------+------+--------------------+------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | campaign_statistic | ALL | campaign_date,date | NULL | NULL | NULL | 1647182 | Using where; Using temporary; Using filesort |
+----+-------------+----------------------------+------+--------------------+------+---------+------+---------+----------------------------------------------+
解决方案
首先,您的查询有一些明显的缺陷。它应该看起来像:
SELECT . . .
FROM `campaign` as campaign LEFT JOIN
(SELECT campaign_id,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
ROUND(SUM(spend),2) AS spend,
SUM(fb_pixel_addtocart) AS fb_pixel_addtocart,
ROUND(SUM(fb_pixel_addtocart_values),2) AS fb_pixel_addtocart_values,
ROUND(SUM(fb_pixel_purchase),2) AS fb_pixel_purchase,
ROUND(SUM(fb_pixel_purchase_values),2) AS fb_pixel_purchase_values,
ROUND(SUM(clicks)*100/SUM(impressions), 6) AS ctr,
ROUND(SUM(spend)/SUM(clicks), 6) AS cpc,
ROUND(SUM(spend)*1000/SUM(impressions), 6) AS cpm,
ROUND(SUM( spend )/SUM(fb_pixel_addtocart), 6) AS cost_per_addtocart,
ROUND(SUM( spend )/SUM(fb_pixel_purchase), 6) AS cost_per_purchase,
ROUND(SUM( fb_pixel_purchase )/SUM(spend), 6) AS roi,
ROUND(SUM( fb_pixel_purchase )/SUM(spend), 6) AS roas
FROM `campaign_statistic`
WHERE dt BETWEEN '2019-07-08' AND '2019-07-08'
GROUP BY campaign_id
) statistic
ON statistic.campaign_id = campaign.campaign_id
WHERE campaign.account_id = 1000623940119431 AND
(campaign.campaign_id LIKE '%%' OR campaign.name LIKE '%%')
ORDER BY id ASC;
一个开始的地方是索引campaign(account_id, campaign_id, name)
。然后对于子查询,campaign_statistic(dt, campaign_id)
.
根据数据,您可能会发现“展开”聚合更好。也就是说,先做JOIN
然后聚合两个表。
推荐阅读
- java - Java XML api在自结束标记之前删除空格
- javascript - 滚动带有内容的小图像
- ios - OBJC 另一个崩溃日志问题
- css - 循环变量的 SCSS 不起作用
- ajax - AWS Lambda 和 Ajax 的 CORS 问题
- asp.net-mvc-5 - 如何在不允许使用 MVC5 的 HTML 的情况下解码字符串中的变音符号
- kotlin - 文档中的领域示例代码给出了“io.realm.exceptions.RealmException:Dog is not part of the schema for this Realm”
- c# - Unity 的自定义网络 (TCP) 中的 SocketException 错误
- tensorflow - 从依赖于张量流中随机变量的张量中采样
- vba - 类型不匹配错误 Excel