通过对生产环境一次慢查询导致CPU暴增案例分析，从具体业务到执行SQL，关联了案例分析需要准备的知识点，主要是执行计划，以及自己的一些思考和最终结论
下载地址: 【图解版】生产环境一次慢查询导致CPU暴增案例分析.xmind
1、慢查询
1.1、业务
- 门店联营销售报表
1.2、场景
-
1.CPU从5%飙升至100%
2.yjpl_shop库执行该SQL的时长为41秒
3.解析行数达到了1200W条 -
-
1.3、SQL
SELECT sale_date,
shop_id,
trading_amount,
trading_count,
return_amount,
return_count,
fixture_number,
fixture_weight,
gross_profit,
gross_profit_rate,
joint_venture_rate,
joint_venture_gross_profit
FROM (
SELECT tmp1.sale_date sale_date,
tmp1.shop_id,
ROUND(IFNULL(tmp1.trading_amount, 0), 2) trading_amount,
tmp1.trading_count,
ROUND(IFNULL(tmp3.return_amount, 0), 2) AS return_amount,
ROUND(
tmp1.trading_amount - tmp1.sala_cost_amount - ifnull(tmp3.return_amount, 0) +
ifnull(tmp3.refund_cost_amount, 0),
2
) AS gross_profit,
cast(
(tmp1.trading_amount - tmp1.sala_cost_amount - ifnull(tmp3.return_amount, 0) +
ifnull(tmp3.refund_cost_amount, 0)) /
(tmp1.trading_amount - ifnull(tmp3.return_amount, 0)) * 100 AS DECIMAL(18, 2)
) AS gross_profit_rate,
IFNULL(tmp3.return_count, 0) return_count,
tmp1.fixture_number - ifnull(tmp3.ref_fixture_number, 0) AS fixture_number,
tmp1.fixture_weight - ifnull(tmp3.ref_fixture_weight, 0) AS fixture_weight,
ROUND(ifnull(tmp1.joint_venture_gross_profit, 0) - ifnull(tmp3.joint_venture_return_gross_profit, 0),
2) AS joint_venture_gross_profit,
cast(
(
(ifnull(tmp1.joint_venture_gross_profit, 0) -
ifnull(tmp3.joint_venture_return_gross_profit, 0)) /
(tmp1.trading_amount - ifnull(tmp3.return_amount, 0))
) * 100 AS DECIMAL(18, 2)
) joint_venture_rate
FROM (
SELECT DATE_FORMAT(od.create_time, '%Y-%m-%d') sale_date,
od.shop_id,
count(DISTINCT od.id) AS trading_count,
SUM(ifnull(item.subtotal_amount, 0)) trading_amount,
SUM(item.sale_spec_quantity * item.quantity) fixture_number,
SUM(item.weight * item.sale_spec_quantity) fixture_weight,
SUM(IFNULL(item.cost_price, 0) * (item.quantity + item.weight) *
item.sale_spec_quantity) sala_cost_amount,
SUM(IFNULL(item.subtotal_amount, 0) *
ifnull(oiejv.joint_venture_rate, 0)) joint_venture_gross_profit
FROM yjpl_order.orders od
INNER JOIN yjpl_order.order_item item ON od.id = item.shop_order_id
INNER JOIN yjpl_shop.shop s ON s.id = od.shop_id
LEFT JOIN yjpl_order.order_item_ex_joint_venture oiejv ON oiejv.id = item.id
WHERE od.order_state NOT IN (0, 6)
AND item.product_sale_type = 7
AND od.create_time BETWEEN '2021-04-15 00:00:00'
AND '2021-04-16 23:59:59'
AND od.service_provider_code = 3
AND s.join_type IN (2)
GROUP BY sale_date,
od.shop_id
) tmp1
LEFT JOIN (
SELECT DATE_FORMAT(odref.create_time, '%Y-%m-%d') sale_date,
odref.shop_id,
count(DISTINCT odref.id) AS return_count,
SUM(ifnull(ori.refund_price, 0)) return_amount,
SUM(IFNULL(ori.cost_price, 0) * (ori.quantity + ori.weight) * ori.spec_quantity) refund_cost_amount,
SUM(
IFNULL(ori.refund_price, 0) * ifnull(oiejv.joint_venture_rate, 0)) joint_venture_return_gross_profit,
SUM(ori.spec_quantity * ori.quantity) AS ref_fixture_number,
SUM(ori.weight * ori.spec_quantity) AS ref_fixture_weight
FROM yjpl_order.order_refund odref
INNER JOIN yjpl_shop.shop s ON s.id = odref.shop_id
INNER JOIN yjpl_order.order_refund_item ori ON odref.id = ori.refund_id
LEFT JOIN yjpl_order.order_item_ex_joint_venture oiejv ON oiejv.id = ori.order_item_id
INNER JOIN yjpl_order.orders od ON odref.order_id = od.id
WHERE odref.refund_state = 0
AND ori.product_sale_type = 7
AND odref.create_time BETWEEN '2021-04-15 00:00:00'
AND '2021-04-16 23:59:59'
AND odref.service_provider_code = 3
AND s.join_type IN (2)
GROUP BY sale_date,
odref.shop_id
) tmp3 ON tmp1.sale_date = tmp3.sale_date
AND tmp1.shop_id = tmp3.shop_id
) a
2、几点思考
2.1、优化核心是什么
- 避免全表扫描
2.1、什么列必须创建索引(SQL优化核心思想)
-
概念
-
基数
- 某个列唯一键（Distinct_Keys）的数量叫作基数
- 比如性别列，该列只有男女之分，所以这一列基数是2
- 列基数越小，意味着列中有大量的重复值
-
选择性
- 基数与总行数的比值再乘以100%就是某个列的选择性
- 当一个列选择性大于20%，说明该列的数据分布就比较均衡了
-
-
结论
- 当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引，从而提升SQL查询性能
3、执行计划
3.1、命令
-
explain/desc
- explain/desc sql
-
3.2、输出参数
-
id
-
select 查询序列号
-
规则
- id相同、从上往下一次执行
- id不同、id越大优先级越高，约先被执行
-
-
select_type
-
查询语句类型
-
SIMPLE
- 简单SELECT,不使用UNION或子查询等
-
PRIMARY
- 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
-
UNION
- UNION中的第二个或后面的SELECT语句
-
DEPENDENT UNION
- UNION中的第二个或后面的SELECT语句，取决于外面的查询
-
UNION RESULT
- UNION的结果
-
SUBQUERY
- 子查询中的第一个SELECT
-
DEPENDENT SUBQUERY
- 子查询中的第一个SELECT，取决于外面的查询
-
-
table
- 查询涉及的表或衍生表
-
type
-
访问类型
-
SYSTEM
- 系统，表仅有一行(=系统表).这是const联接类型的一个特例
-
CONST
- 常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次
-
EQ_REF
- 搜索时使用primary key 或 unique类型
-
REF
- 根据索引查找一个或多个值
-
INDEX_MERGE
- 合并索引，使用多个单列索引搜索
-
RANGE
- 对索引列进行范围查找
-
index
- 全索引表扫描
-
ALL
- 全数据表扫描
-
-
possible_keys
- 指示MySQL可以从中选择查找此表中的行的索引
-
key
- MySQL查询实际使用到的索引
-
key_len
- 表示索引中使用的字节数（只计算利用索引作为index key的索引长度，不包括用于group by/order by的索引长度）
-
ref
- 显示该表的索引字段关联了哪张表的哪个字段
-
rows
- 根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
-
filtered
- 返回结果的行数占读取行数的百分比，值越大越好
-
extra
-
包含不适合在其他列中显示但十分重要的额外信息
-
use filesort
- MySQL会对数据使用非索引列进行排序，而不是按照索引顺序进行读取；若出现改值，应优化索引
-
use temporary
- 使用临时表保存中间结果，比如，MySQL在对查询结果排序时使用临时表，常见于order by和group by；若出现改值，应优化索引
-
use index
- 表示select操作使用了索引覆盖，避免回表访问数据行，效率不错
-
use where
- where子句用于限制哪一行
-
4、案例分析
4.1、分析执行计划的type可得，所有查询的字段均使用了索引，并未出现ALL全表扫描类型
4.2、分析fitered(count(shop_type)/count(1)),
-
加索引
-
不加索引
-
filetered从10变成13，效率并未有实质提升
4.3、分析extra，
-
不加索引
-
加索引
-
extral在加索引跟不加索引的情况下都是Using where
4.4、结论一：通过执行计划可知，索引对此次慢查询SQL执行计划并无实质性优化
4.5、直接查询此慢SQL前100条数据对比
-
-
-
加索引： 1 m 18 s 482 ms
不加索引：1 m 22 s 871 ms
效率上快了4秒，并无实质提升
4.6、结论二：通过查询结果可知，索引对此次慢查询SQL执行效率并无实质性优化
4.7、调整思路，该慢查询执行的数据库名都是yjpl_shop，尝试去掉shop的关联
- 对比去掉前后除shop相关的执行计划未发生任何变化
4.8、直接查询此慢SQL前500条数据对比
-
去掉shop
- SQL
SELECT sale_date,
shop_id,
trading_amount,
trading_count,
return_amount,
return_count,
fixture_number,
fixture_weight,
gross_profit,
gross_profit_rate,
joint_venture_rate,
joint_venture_gross_profit
FROM (
SELECT tmp1.sale_date sale_date,
tmp1.shop_id,
ROUND(IFNULL(tmp1.trading_amount, 0), 2) trading_amount,
tmp1.trading_count,
ROUND(IFNULL(tmp3.return_amount, 0), 2) AS return_amount,
ROUND(
tmp1.trading_amount - tmp1.sala_cost_amount - ifnull(tmp3.return_amount, 0) +
ifnull(tmp3.refund_cost_amount, 0),
2
) AS gross_profit,
cast(
(tmp1.trading_amount - tmp1.sala_cost_amount - ifnull(tmp3.return_amount, 0) +
ifnull(tmp3.refund_cost_amount, 0)) /
(tmp1.trading_amount - ifnull(tmp3.return_amount, 0)) * 100 AS DECIMAL(18, 2)
) AS gross_profit_rate,
IFNULL(tmp3.return_count, 0) return_count,
tmp1.fixture_number - ifnull(tmp3.ref_fixture_number, 0) AS fixture_number,
tmp1.fixture_weight - ifnull(tmp3.ref_fixture_weight, 0) AS fixture_weight,
ROUND(ifnull(tmp1.joint_venture_gross_profit, 0) - ifnull(tmp3.joint_venture_return_gross_profit, 0),
2) AS joint_venture_gross_profit,
cast(
(
(ifnull(tmp1.joint_venture_gross_profit, 0) -
ifnull(tmp3.joint_venture_return_gross_profit, 0)) /
(tmp1.trading_amount - ifnull(tmp3.return_amount, 0))
) * 100 AS DECIMAL(18, 2)
) joint_venture_rate
FROM (
SELECT DATE_FORMAT(od.create_time, '%Y-%m-%d') sale_date,
od.shop_id,
count(DISTINCT od.id) AS trading_count,
SUM(ifnull(item.subtotal_amount, 0)) trading_amount,
SUM(item.sale_spec_quantity * item.quantity) fixture_number,
SUM(item.weight * item.sale_spec_quantity) fixture_weight,
SUM(IFNULL(item.cost_price, 0) * (item.quantity + item.weight) *
item.sale_spec_quantity) sala_cost_amount,
SUM(IFNULL(item.subtotal_amount, 0) *
ifnull(oiejv.joint_venture_rate, 0)) joint_venture_gross_profit
FROM yjpl_order.orders od
INNER JOIN yjpl_order.order_item item ON od.id = item.shop_order_id
# INNER JOIN yjpl_shop.shop s ON s.id = od.shop_id AND s.join_type IN (2)
LEFT JOIN yjpl_order.order_item_ex_joint_venture oiejv ON oiejv.id = item.id
WHERE od.order_state NOT IN (0, 6)
AND od.create_time BETWEEN '2020-08-15 00:00:00'
AND '2021-01-01 23:59:59'
AND od.service_provider_code = 3
GROUP BY sale_date,
od.shop_id
) tmp1
LEFT JOIN (
SELECT DATE_FORMAT(odref.create_time, '%Y-%m-%d') sale_date,
odref.shop_id,
count(DISTINCT odref.id) AS return_count,
SUM(ifnull(ori.refund_price, 0)) return_amount,
SUM(IFNULL(ori.cost_price, 0) * (ori.quantity + ori.weight) * ori.spec_quantity) refund_cost_amount,
SUM(
IFNULL(ori.refund_price, 0) * ifnull(oiejv.joint_venture_rate, 0)) joint_venture_return_gross_profit,
SUM(ori.spec_quantity * ori.quantity) AS ref_fixture_number,
SUM(ori.weight * ori.spec_quantity) AS ref_fixture_weight
FROM yjpl_order.order_refund odref
# INNER JOIN yjpl_shop.shop s ON s.id = odref.shop_id AND s.join_type IN (2)
INNER JOIN yjpl_order.order_refund_item ori ON odref.id = ori.refund_id
LEFT JOIN yjpl_order.order_item_ex_joint_venture oiejv ON oiejv.id = ori.order_item_id
INNER JOIN yjpl_order.orders od ON odref.order_id = od.id
WHERE odref.refund_state = 0
AND odref.create_time BETWEEN '2020-08-15 00:00:00'
AND '2021-01-01 23:59:59'
AND odref.service_provider_code = 3
GROUP BY sale_date,
odref.shop_id
) tmp3 ON tmp1.sale_date = tmp3.sale_date
AND tmp1.shop_id = tmp3.shop_id
) a limit 500
- 42 s
-
去掉shop并且通过shop_id in查询
- SQL
SELECT sale_date,
shop_id,
trading_amount,
trading_count,
return_amount,
return_count,
fixture_number,
fixture_weight,
gross_profit,
gross_profit_rate,
joint_venture_rate,
joint_venture_gross_profit
FROM (
SELECT tmp1.sale_date sale_date,
tmp1.shop_id,
ROUND(IFNULL(tmp1.trading_amount, 0), 2) trading_amount,
tmp1.trading_count,
ROUND(IFNULL(tmp3.return_amount, 0), 2) AS return_amount,
ROUND(
tmp1.trading_amount - tmp1.sala_cost_amount - ifnull(tmp3.return_amount, 0) +
ifnull(tmp3.refund_cost_amount, 0),
2
) AS gross_profit,
cast(
(tmp1.trading_amount - tmp1.sala_cost_amount - ifnull(tmp3.return_amount, 0) +
ifnull(tmp3.refund_cost_amount, 0)) /
(tmp1.trading_amount - ifnull(tmp3.return_amount, 0)) * 100 AS DECIMAL(18, 2)
) AS gross_profit_rate,
IFNULL(tmp3.return_count, 0) return_count,
tmp1.fixture_number - ifnull(tmp3.ref_fixture_number, 0) AS fixture_number,
tmp1.fixture_weight - ifnull(tmp3.ref_fixture_weight, 0) AS fixture_weight,
ROUND(ifnull(tmp1.joint_venture_gross_profit, 0) - ifnull(tmp3.joint_venture_return_gross_profit, 0),
2) AS joint_venture_gross_profit,
cast(
(
(ifnull(tmp1.joint_venture_gross_profit, 0) -
ifnull(tmp3.joint_venture_return_gross_profit, 0)) /
(tmp1.trading_amount - ifnull(tmp3.return_amount, 0))
) * 100 AS DECIMAL(18, 2)
) joint_venture_rate
FROM (
SELECT DATE_FORMAT(od.create_time, '%Y-%m-%d') sale_date,
od.shop_id,
count(DISTINCT od.id) AS trading_count,
SUM(ifnull(item.subtotal_amount, 0)) trading_amount,
SUM(item.sale_spec_quantity * item.quantity) fixture_number,
SUM(item.weight * item.sale_spec_quantity) fixture_weight,
SUM(IFNULL(item.cost_price, 0) * (item.quantity + item.weight) *
item.sale_spec_quantity) sala_cost_amount,
SUM(IFNULL(item.subtotal_amount, 0) *
ifnull(oiejv.joint_venture_rate, 0)) joint_venture_gross_profit
FROM yjpl_order.orders od
INNER JOIN yjpl_order.order_item item ON od.id = item.shop_order_id
INNER JOIN yjpl_shop.shop s ON s.id = od.shop_id AND s.join_type IN (2)
LEFT JOIN yjpl_order.order_item_ex_joint_venture oiejv ON oiejv.id = item.id
WHERE od.order_state NOT IN (0, 6)
AND od.create_time BETWEEN '2020-08-15 00:00:00'
AND '2021-01-01 23:59:59'
AND od.service_provider_code = 3
GROUP BY sale_date,
od.shop_id
) tmp1
LEFT JOIN (
SELECT DATE_FORMAT(odref.create_time, '%Y-%m-%d') sale_date,
odref.shop_id,
count(DISTINCT odref.id) AS return_count,
SUM(ifnull(ori.refund_price, 0)) return_amount,
SUM(IFNULL(ori.cost_price, 0) * (ori.quantity + ori.weight) * ori.spec_quantity) refund_cost_amount,
SUM(
IFNULL(ori.refund_price, 0) * ifnull(oiejv.joint_venture_rate, 0)) joint_venture_return_gross_profit,
SUM(ori.spec_quantity * ori.quantity) AS ref_fixture_number,
SUM(ori.weight * ori.spec_quantity) AS ref_fixture_weight
FROM yjpl_order.order_refund odref
INNER JOIN yjpl_shop.shop s ON s.id = odref.shop_id AND s.join_type IN (2)
INNER JOIN yjpl_order.order_refund_item ori ON odref.id = ori.refund_id
LEFT JOIN yjpl_order.order_item_ex_joint_venture oiejv ON oiejv.id = ori.order_item_id
INNER JOIN yjpl_order.orders od ON odref.order_id = od.id
WHERE odref.refund_state = 0
AND odref.create_time BETWEEN '2020-08-15 00:00:00'
AND '2021-01-01 23:59:59'
AND odref.service_provider_code = 3
GROUP BY sale_date,
odref.shop_id
) tmp3 ON tmp1.sale_date = tmp3.sale_date
AND tmp1.shop_id = tmp3.shop_id
) a limit 500
- 36 s
-
不去掉shop
- SQL
SELECT sale_date,
shop_id,
trading_amount,
trading_count,
return_amount,
return_count,
fixture_number,
fixture_weight,
gross_profit,
gross_profit_rate,
joint_venture_rate,
joint_venture_gross_profit
FROM (
SELECT tmp1.sale_date sale_date,
tmp1.shop_id,
ROUND(IFNULL(tmp1.trading_amount, 0), 2) trading_amount,
tmp1.trading_count,
ROUND(IFNULL(tmp3.return_amount, 0), 2) AS return_amount,
ROUND(
tmp1.trading_amount - tmp1.sala_cost_amount - ifnull(tmp3.return_amount, 0) +
ifnull(tmp3.refund_cost_amount, 0),
2
) AS gross_profit,
cast(
(tmp1.trading_amount - tmp1.sala_cost_amount - ifnull(tmp3.return_amount, 0) +
ifnull(tmp3.refund_cost_amount, 0)) /
(tmp1.trading_amount - ifnull(tmp3.return_amount, 0)) * 100 AS DECIMAL(18, 2)
) AS gross_profit_rate,
IFNULL(tmp3.return_count, 0) return_count,
tmp1.fixture_number - ifnull(tmp3.ref_fixture_number, 0) AS fixture_number,
tmp1.fixture_weight - ifnull(tmp3.ref_fixture_weight, 0) AS fixture_weight,
ROUND(ifnull(tmp1.joint_venture_gross_profit, 0) - ifnull(tmp3.joint_venture_return_gross_profit, 0),
2) AS joint_venture_gross_profit,
cast(
(
(ifnull(tmp1.joint_venture_gross_profit, 0) -
ifnull(tmp3.joint_venture_return_gross_profit, 0)) /
(tmp1.trading_amount - ifnull(tmp3.return_amount, 0))
) * 100 AS DECIMAL(18, 2)
) joint_venture_rate
FROM (
SELECT DATE_FORMAT(od.create_time, '%Y-%m-%d') sale_date,
od.shop_id,
count(DISTINCT od.id) AS trading_count,
SUM(ifnull(item.subtotal_amount, 0)) trading_amount,
SUM(item.sale_spec_quantity * item.quantity) fixture_number,
SUM(item.weight * item.sale_spec_quantity) fixture_weight,
SUM(IFNULL(item.cost_price, 0) * (item.quantity + item.weight) *
item.sale_spec_quantity) sala_cost_amount,
SUM(IFNULL(item.subtotal_amount, 0) *
ifnull(oiejv.joint_venture_rate, 0)) joint_venture_gross_profit
FROM yjpl_order.orders od
INNER JOIN yjpl_order.order_item item ON od.id = item.shop_order_id
INNER JOIN yjpl_shop.shop s ON s.id = od.shop_id AND s.join_type IN (2)
LEFT JOIN yjpl_order.order_item_ex_joint_venture oiejv ON oiejv.id = item.id
WHERE od.order_state NOT IN (0, 6)
AND od.create_time BETWEEN '2020-08-15 00:00:00'
AND '2021-01-01 23:59:59'
AND od.service_provider_code = 3
AND od.shop_id in (682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
729,
730,
735,
736,
737,
738,
739,
742,
743,
744,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
958,
682,
684,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
729,
730,
735,
736,
737,
738,
739,
742,
743,
744,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
958,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
729,
730,
735,
736,
737,
738,
739,
742,
743,
744,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
958,
658,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
729,
730,
735,
736,
737,
738,
739,
742,
743,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
730,
735,
736,
737,
738,
739,
742,
743,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
955,
658,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
730,
735,
736,
737,
738,
739,
742,
743,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
955,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
730,
735,
736,
737,
738,
739,
742,
743,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936
)
GROUP BY sale_date,
od.shop_id
) tmp1
LEFT JOIN (
SELECT DATE_FORMAT(odref.create_time, '%Y-%m-%d') sale_date,
odref.shop_id,
count(DISTINCT odref.id) AS return_count,
SUM(ifnull(ori.refund_price, 0)) return_amount,
SUM(IFNULL(ori.cost_price, 0) * (ori.quantity + ori.weight) * ori.spec_quantity) refund_cost_amount,
SUM(
IFNULL(ori.refund_price, 0) * ifnull(oiejv.joint_venture_rate, 0)) joint_venture_return_gross_profit,
SUM(ori.spec_quantity * ori.quantity) AS ref_fixture_number,
SUM(ori.weight * ori.spec_quantity) AS ref_fixture_weight
FROM yjpl_order.order_refund odref
INNER JOIN yjpl_shop.shop s ON s.id = odref.shop_id AND s.join_type IN (2)
INNER JOIN yjpl_order.order_refund_item ori ON odref.id = ori.refund_id
LEFT JOIN yjpl_order.order_item_ex_joint_venture oiejv ON oiejv.id = ori.order_item_id
INNER JOIN yjpl_order.orders od ON odref.order_id = od.id
WHERE odref.refund_state = 0
AND odref.create_time BETWEEN '2020-08-15 00:00:00'
AND '2021-01-01 23:59:59'
AND odref.service_provider_code = 3
AND odref.shop_id in (682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
729,
730,
735,
736,
737,
738,
739,
742,
743,
744,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
958,
682,
684,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
729,
730,
735,
736,
737,
738,
739,
742,
743,
744,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
958,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
729,
730,
735,
736,
737,
738,
739,
742,
743,
744,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
958,
658,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
729,
730,
735,
736,
737,
738,
739,
742,
743,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
730,
735,
736,
737,
738,
739,
742,
743,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
955,
658,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
730,
735,
736,
737,
738,
739,
742,
743,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936,
940,
944,
948,
955,
682,
684,
685,
691,
692,
699,
701,
702,
707,
710,
711,
714,
716,
724,
725,
726,
727,
730,
735,
736,
737,
738,
739,
742,
743,
746,
747,
748,
749,
750,
751,
753,
755,
756,
759,
762,
774,
775,
777,
779,
804,
809,
812,
817,
818,
820,
824,
844,
852,
889,
892,
897,
905,
906,
908,
912,
914,
915,
918,
923,
926,
928,
929,
932,
933,
935,
936
)
GROUP BY sale_date,
odref.shop_id
) tmp3 ON tmp1.sale_date = tmp3.sale_date
AND tmp1.shop_id = tmp3.shop_id
) a limit 500
- 1 m 35 m
- 只去掉shop、对比去掉前后、查询前500条数据SQL效率提升了1分2秒，差不多1.6倍
- 去掉shop,并且通过create_time跟shop_id的联合索引查询SQL效率提升了1分12秒，差不多2倍
- 不去掉shop，但是通过create_time跟shop_id的联合索引查询SQL效率提升11秒，提升效率不明显
519
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
