求解一个语句优化。。

Dleno 2019-07-08 07:39:44

如图：t,p不管，数据原因。。
主要问题在gd表，gd.goods_id与gd.terminal_val建了联合唯一索引的。但row却是257行（这个表的总数据就是257），Extra里也成了：Using temporary; Using filesort。
gd表只有这个地方用上了的，确实不知道什么原因了。求大神帮忙解答下

...全文

338 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

遇星 2019-07-10

打赏
举报

如果要优化的话，我大概看了下SQL，目前来看是最优的，优化主要从各步骤的返回行数入手吧， union里的第一个SQL，p表的rows是109，我看这里是单列索引，按照p.parts_classify=t.parts_classify and p.is_deleted = 0 AND p.states = 1建立联合索引可能有效率提升； union里的第二个SQL，t表rows是21，如果t.parts_id>0区分比较高，也可以考虑建一个联合索引

遇星 2019-07-10

打赏
举报

引用 8 楼 Dleno 的回复:

业务相对复杂，这个语句要实现效果也就复杂了。。可以另外从其他角度做优化，但现在来不及。。上面是拆分之后的。。以前的语句如果不查P表的字段，和第一个字段，则速度也还将就400毫秒左右。。一旦加入p表字段，语句就要接近两秒了。。

你的第二个截图，我也仅针对截图来分析，为什么一个gd是1，一个gd是257：第一个gd的rows是1，是因为这里gd是被驱动表，驱动表是t，这里的访问逻辑其实是从t表中取出一行数据，然后通过gd的联合唯一索引去唯一定位一行数据，这个可以从type列的eq_ref看出；第二个gd的rows是257，是因为这里gd是驱动表，t是被驱动表，这里的访问逻辑是需要先访问gd表，然后根据gd表的每一行去跟t表匹配，这个可以从type列的index看出是索引全扫描，由于gd表的筛选条件是gd.goods_id=t.goods_id AND gd.terminal_val=1，当gd表作为驱动表时，gd.goods_id=t.goods_id这个条件是没办法用来筛选了，只能用gd.terminal_val=1来筛选，但是这个条件并不符合索引的最左原则，只能是通过索引下推的方式，所以gd表是需要走联合索引的全扫描。 union的执行计划，是可以通过拆分union各SQL来查看的，实际上MySQL优化器也是将union的各个SQL各自执行之后再把结果集合并，所以要跟踪union的执行计划问题，最好是细分到各个SQL去排查；没有细看SQL，太长了；这里gd的rows是257的情况，有可能是SQL不一样导致执行计划不同或者优化器BUG，如果是后者，可以通过straight_join强制指定t和gd的访问顺序。

Dleno 2019-07-09

打赏
举报

业务相对复杂，这个语句要实现效果也就复杂了。。
可以另外从其他角度做优化，但现在来不及。。

上面是拆分之后的。。
以前的语句如果不查P表的字段，和第一个字段，则速度也还将就400毫秒左右。。
一旦加入p表字段，语句就要接近两秒了。。

Dleno 2019-07-09

打赏
举报



SELECT t1.*, count(1) as partsSize FROM (

        SELECT

        t.goods_id,

        '19768' as model_id,

        t.parts_classify as parts_group,

        t.parts_default,

        t.parts_id AS partsId,

				if(

        t.parts_numbers>0,t.parts_numbers,

        if(cm1.parts_numbers>0,cm1.parts_numbers,if(cm2.parts_numbers>0,cm2.parts_numbers,if(cm3.parts_numbers>0,cm3.parts_numbers,cm4.parts_numbers)))

        ) AS parts_numbers,

        if(

        t.time_fee_total>-1,t.time_fee_total,

        if(cm1.time_fee_total>-1,cm1.time_fee_total,if(cm2.time_fee_total>-1,cm2.time_fee_total,if(cm3.time_fee_total>-1,cm3.time_fee_total,cm4.time_fee_total)))

        ) time_fee_total,

        if(cm1.predict_working_hours>=0,cm1.predict_working_hours,if(cm2.predict_working_hours>=0,cm2.predict_working_hours,

        if(cm3.predict_working_hours>=0,cm3.predict_working_hours,cm4.predict_working_hours)))

        as predict_working_hours,

        if(NOT ISNULL(cm1.parts_oe_code),cm1.parts_oe_code,if(NOT ISNULL(cm2.parts_oe_code),cm2.parts_oe_code,

        if(NOT ISNULL(cm3.parts_oe_code),cm3.parts_oe_code,cm4.parts_oe_code))) AS parts_oe_code,

				(

        (p.car_owner_sales_price*if(

        t.parts_numbers>0,t.parts_numbers,

        if(NOT ISNULL(cm1.parts_numbers),cm1.parts_numbers,if(NOT ISNULL(cm2.parts_numbers),cm2.parts_numbers,if(NOT ISNULL(cm3.parts_numbers),cm3.parts_numbers,cm4.parts_numbers)))

        ) + if(

        t.time_fee_total>-1,t.time_fee_total,

        if(NOT ISNULL(cm1.time_fee_total),cm1.time_fee_total,if(NOT ISNULL(cm2.time_fee_total),cm2.time_fee_total,if(NOT ISNULL(cm3.time_fee_total),cm3.time_fee_total,cm4.time_fee_total)))

        ))

        ) total_amount,

p.id,

        p.parts_name,

        p.parts_brand,

        p.parts_brand_code,

        p.parts_classify,

        p.car_owner_sales_price,

        p.cost_price,

        p.unit,

        p.goods_describe,

        p.states,

        p.skill_of_technician,

        p.technician_sales_price,

        p.parts_type,

        p.parts_spec,

        p.version_no,

        p.is_activity,

         imi.img_url



FROM goods_of_parts t



INNER JOIN goods_terminal_display  gd	ON gd.goods_id=t.goods_id AND gd.terminal_val=1

LEFT JOIN parts_config p		ON p.parts_classify=t.parts_classify and p.is_deleted = 0 AND p.states = 1

LEFT JOIN img_manager_info 				imi on imi.business_tab='parts_config' and imi.business_id=p.id

LEFT JOIN car_parts_of_model 			cm1 on cm1.car_brand_id=1929 and cm1.car_audi_id=1930 and cm1.car_model_id=19768 and cm1.car_parts_id=p.id

LEFT JOIN car_parts_of_model 			cm2 on cm2.car_brand_id=1929 and cm2.car_audi_id=1930 and cm2.car_model_id=0     and cm2.car_parts_id=p.id

LEFT JOIN car_parts_of_model 			cm3 on cm3.car_brand_id=1929 and cm3.car_audi_id=0    and cm3.car_model_id=0     and cm3.car_parts_id=p.id

LEFT JOIN car_parts_of_model 			cm4 on cm4.car_brand_id=0    and cm4.car_audi_id=0    and cm4.car_model_id=0     and cm4.car_parts_id=p.id



WHERE t.parts_id=0

and (cm1.id>0 or cm2.id>0 or cm3.id>0 or cm4.id>0)

        

UNION ALL



        SELECT

        t.goods_id,

        '19768' as model_id,

        t.parts_classify as parts_group,

        t.parts_default,

        t.parts_id AS partsId,

if(

        t.parts_numbers>0,t.parts_numbers,

        if(cm1.parts_numbers>0,cm1.parts_numbers,if(cm2.parts_numbers>0,cm2.parts_numbers,if(cm3.parts_numbers>0,cm3.parts_numbers,cm4.parts_numbers)))

        ) AS parts_numbers,

        if(

        t.time_fee_total>-1,t.time_fee_total,

        if(cm1.time_fee_total>-1,cm1.time_fee_total,if(cm2.time_fee_total>-1,cm2.time_fee_total,if(cm3.time_fee_total>-1,cm3.time_fee_total,cm4.time_fee_total)))

        ) time_fee_total,

        if(cm1.predict_working_hours>=0,cm1.predict_working_hours,if(cm2.predict_working_hours>=0,cm2.predict_working_hours,

        if(cm3.predict_working_hours>=0,cm3.predict_working_hours,cm4.predict_working_hours)))

        as predict_working_hours,

        if(NOT ISNULL(cm1.parts_oe_code),cm1.parts_oe_code,if(NOT ISNULL(cm2.parts_oe_code),cm2.parts_oe_code,

        if(NOT ISNULL(cm3.parts_oe_code),cm3.parts_oe_code,cm4.parts_oe_code))) AS parts_oe_code,

				(

        (p.car_owner_sales_price*if(

        t.parts_numbers>0,t.parts_numbers,

        if(NOT ISNULL(cm1.parts_numbers),cm1.parts_numbers,if(NOT ISNULL(cm2.parts_numbers),cm2.parts_numbers,if(NOT ISNULL(cm3.parts_numbers),cm3.parts_numbers,cm4.parts_numbers)))

        ) + if(

        t.time_fee_total>-1,t.time_fee_total,

        if(NOT ISNULL(cm1.time_fee_total),cm1.time_fee_total,if(NOT ISNULL(cm2.time_fee_total),cm2.time_fee_total,if(NOT ISNULL(cm3.time_fee_total),cm3.time_fee_total,cm4.time_fee_total)))

        ))

        ) total_amount,

p.id,

        p.parts_name,

        p.parts_brand,

        p.parts_brand_code,

        p.parts_classify,

        p.car_owner_sales_price,

        p.cost_price,

        p.unit,

        p.goods_describe,

        p.states,

        p.skill_of_technician,

        p.technician_sales_price,

        p.parts_type,

        p.parts_spec,

        p.version_no,

        p.is_activity,

         imi.img_url



FROM goods_of_parts t



INNER JOIN goods_terminal_display  gd	ON gd.goods_id=t.goods_id AND gd.terminal_val=1

LEFT JOIN parts_config p		ON p.id = t.parts_id

LEFT JOIN img_manager_info 				imi on imi.business_tab='parts_config' and imi.business_id=p.id

LEFT JOIN car_parts_of_model 			cm1 on cm1.car_brand_id=1929 and cm1.car_audi_id=1930 and cm1.car_model_id=19768 and cm1.car_parts_id=p.id

LEFT JOIN car_parts_of_model 			cm2 on cm2.car_brand_id=1929 and cm2.car_audi_id=1930 and cm2.car_model_id=0     and cm2.car_parts_id=p.id

LEFT JOIN car_parts_of_model 			cm3 on cm3.car_brand_id=1929 and cm3.car_audi_id=0    and cm3.car_model_id=0     and cm3.car_parts_id=p.id

LEFT JOIN car_parts_of_model 			cm4 on cm4.car_brand_id=0    and cm4.car_audi_id=0    and cm4.car_model_id=0     and cm4.car_parts_id=p.id



WHERE t.parts_id>0

and (cm1.id>0 or cm2.id>0 or cm3.id>0 or cm4.id>0)



ORDER BY goods_id,parts_classify,total_amount



) t1

GROUP BY goods_id,parts_group

ORDER BY goods_id,parts_group desc;

遇星 2019-07-09

打赏
举报

引用 4 楼 Dleno 的回复:

[quote=引用 2 楼受了伤风的星辰的回复:] 你这里gd是驱动表，也就是MySQL优化器第一个访问的表，所以对于联合唯一索引是不起到快速定位数据的作用的。因为第一个访问gd表，只能按照gd.terminal_val=1和gd.id>0这两个条件去筛选数据，而这两个都不符合索引最左原则，所以只能用到索引全扫描，就是执行计划里的using index，这就是为什么要访问257行。

gd.id>0这个可以忽略，这个是调试使用left join时加的，使用INNER JOIN就不需要它了。。联合唯一索引其实也是能快速定位到数据的。我现在遇到的情况： 1：如果只有两个表的时候，row就肯定是1，且能使用索引；现在多表的时候，分析出来，开始也是1，然后再分析一次就成257了。 2：我现在通过p表的两种条件，把语句分成了两条用UNION ALL；结果一个是1一个是257.同样的关联方式。。这个就感觉很奇葩了。。 [/quote] 我只能分析你截图里的现象，其他情况没有实际调试，不好猜测

Dleno 2019-07-09

打赏
举报

之前的语句执行要两秒左右。。
使用UNION ALL拆分优化后只需要34毫秒了。
就想搞清楚下前后到底是为什么。。。

Dleno 2019-07-09

打赏
举报

引用 2 楼受了伤风的星辰的回复:

你这里gd是驱动表，也就是MySQL优化器第一个访问的表，所以对于联合唯一索引是不起到快速定位数据的作用的。因为第一个访问gd表，只能按照gd.terminal_val=1和gd.id>0这两个条件去筛选数据，而这两个都不符合索引最左原则，所以只能用到索引全扫描，就是执行计划里的using index，这就是为什么要访问257行。

gd.id>0这个可以忽略，这个是调试使用left join时加的，使用INNER JOIN就不需要它了。。
联合唯一索引其实也是能快速定位到数据的。
我现在遇到的情况：
1：如果只有两个表的时候，row就肯定是1，且能使用索引；现在多表的时候，分析出来，开始也是1，然后再分析一次就成257了。
2：我现在通过p表的两种条件，把语句分成了两条用UNION ALL；结果一个是1一个是257.同样的关联方式。。
这个就感觉很奇葩了。。

Dleno 2019-07-09

打赏
举报

引用 1 楼心怀啊的回复:

连接是必须需要查询第一个表的所有数据再到第二个表查询值相等的数据，建议先酱第一个表的数据通过条件减少数据再与第二个表连接

第一个表数据无法减少了。现在只有几千条数据。业务需求上基本算是全表扫描了。。
而且第二个表建的是联合唯一索引，关联表ON里也是用上了的。

遇星 2019-07-09

打赏
举报

你这里gd是驱动表，也就是MySQL优化器第一个访问的表，所以对于联合唯一索引是不起到快速定位数据的作用的。因为第一个访问gd表，只能按照gd.terminal_val=1和gd.id>0这两个条件去筛选数据，而这两个都不符合索引最左原则，所以只能用到索引全扫描，就是执行计划里的using index，这就是为什么要访问257行。