求解一个语句优化。。

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表只有这个地方用上了的，确实不知道什么原因了。求大神帮忙解答下
...全文
336 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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行。
心怀啊 2019-07-08
  • 打赏
  • 举报
 回复
连接是必须需要查询第一个表的所有数据再到第二个表查询值相等的数据，建议先酱第一个表的数据通过条件减少数据再与第二个表连接
GAMS中文教程
在教程的开篇，会介绍GAMS的安装过程和界面布局，让读者对GAMS有一个初步的认识。接下来，教程会详细阐述GAMS的语言基础，包括变量、参数、集合、和符号的定义与使用。GAMS中的变量可以是决策变量，表示模型中的可变...
ANSYS高性能计算.pdf
本文档介绍了如何在MATLAB高性能调用ANSYS进行优化计算，内含调用语句，高性能求解方法，不同类型的问题选择什么样的求解器可以达到更高的求解速度
CPLEX_test.m
yalmip+cplex 联合编程优化，最基本的建模，变量声明以及求解语句，有利于初学者入门学习。
C语言100个算法经典例题
求解数学序列的和是一个常见的编程任务，涉及到循环和累积变量的使用，有助于理解数学序列和迭代过程。 #### 知识点十九：完数检测（程序19） 完数是指一个数恰好等于它的所有真因子（即除了自身以外的因子）之和。...
基于CPLEX求解综合能源系统优化问题附MATLAB代码
假设我们有一个综合能源系统，包括多个能源源（例如太阳能、风能、天然气）和能源转换设备（例如风力发电机、太阳能电池板、燃气发电机）。我们的目标是最小化系统的总能源成本，同时满足能源需求和其他约束条件。...
MySQL

56,937

社区成员

56,755

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章