深圳智汇创想科技有限公司

001-
001+ 严重级别：一般 所属项目：aliexpress 问题原因：慢SQL、大表、多表JOIN：（SELECT tus.id, tus.customer_id, tus.strategy_name, tus.policy_id, tus.business_type, tus.sku_type, tus.template_name, tus.product_strategy_name, tus.strategy_type, tus.STATUS, tus.last_execute_time, tus.execute_status, tus.execute_type, tus.execute_cycle, tus.interval_days, tus.drive_table, tusif.import_vals sourceIds FROM t_strategy_aliexpress tus LEFT JOIN t_strategy_rule_aliexpress tusr ON tus.id = tusr.strategy_id AND tusr.rule_code = 'driver_group' AND tusr.is_deleted = 0 LEFT JOIN (SELECT import_id, group_concat(DISTINCT import_val) import_vals FROM t_strategy_import_filter_aliexpress WHERE import_type = 1 AND is_deleted = 0 AND rule_code = 'driver_group' GROUP BY import_id) tusif ON tus.id = tusif.import_id AND tusr.filter_express = '9' WHERE tus.is_deleted = 0 AND tus.STATUS = 1 AND tus.strategy_type = 6;） 解决方案：第一、把索引idx_strategy_id 调整为：(strategy_id, rule_code, is_deleted, filter_express) 第二、先查小表（建议增加分页查，避免数据增长后，出现性能问题） SELECT tus.id, tus.customer_id, tus.strategy_name, tus.policy_id, tus.business_type, tus.sku_type, tus.template_name, tus.product_strategy_name, tus.strategy_type, tus.STATUS, tus.last_execute_time, tus.execute_status, tus.execute_type, tus.execute_cycle, tus.interval_days, tus.drive_table FROM t_strategy_aliexpress tus LEFT JOIN t_strategy_rule_aliexpress tusr ON tus.id = tusr.strategy_id AND tusr.rule_code = 'driver_group' AND tusr.is_deleted = 0 -- LEFT JOIN (SELECT import_id, group_concat(DISTINCT import_val) import_vals FROM t_strategy_import_filter_aliexpress WHERE import_type = 1 AND is_deleted = 0 AND rule_code = 'driver_group' GROUP BY import_id) tusif ON tus.id = tusif.import_id -- AND tusr.filter_express = '9' WHERE tus.is_deleted = 0 AND tus.STATUS = 1 AND tus.strategy_type = 6 limit 50 第三、再通过第二中查询的数据，再批量查2000多万大表t_strategy_import_filter_aliexpress的数据（记得使用合适的索引）
002- 严重级别：一般
003- 所属项目：aliexpress
004- 问题原因：慢SQL、大表、多表JOIN：（SELECT
005- tus.id,
006- tus.customer_id,
007- tus.strategy_name,
008- tus.policy_id,
009- tus.business_type,
010- tus.sku_type,
011- tus.template_name,
012- tus.product_strategy_name,
013- tus.strategy_type,
014- tus.STATUS,
015- tus.last_execute_time,
016- tus.execute_status,
017- tus.execute_type,
018- tus.execute_cycle,
019- tus.interval_days,
020- tus.drive_table,
021- tusif.import_vals sourceIds
022- FROM
023- t_strategy_aliexpress tus
024- LEFT JOIN t_strategy_rule_aliexpress tusr ON tus.id = tusr.strategy_id
025- AND tusr.rule_code = 'driver_group'
026- AND tusr.is_deleted = 0
027- LEFT JOIN (SELECT import_id, group_concat(DISTINCT import_val) import_vals FROM t_strategy_import_filter_aliexpress WHERE import_type = 1 AND is_deleted = 0 AND rule_code = 'driver_group' GROUP BY import_id) tusif ON tus.id = tusif.import_id
028- AND tusr.filter_express = '9'
029- WHERE
030- tus.is_deleted = 0
031- AND tus.STATUS = 1
032- AND tus.strategy_type = 6;）
033- 解决方案：第一、把索引idx_strategy_id 调整为：(strategy_id, rule_code, is_deleted, filter_express)
034- 第二、先查小表（建议增加分页查，避免数据增长后，出现性能问题）
035- SELECT
036- tus.id,
037- tus.customer_id,
038- tus.strategy_name,
039- tus.policy_id,
040- tus.business_type,
041- tus.sku_type,
042- tus.template_name,
043- tus.product_strategy_name,
044- tus.strategy_type,
045- tus.STATUS,
046- tus.last_execute_time,
047- tus.execute_status,
048- tus.execute_type,
049- tus.execute_cycle,
050- tus.interval_days,
051- tus.drive_table
052- FROM
053- t_strategy_aliexpress tus
054- LEFT JOIN t_strategy_rule_aliexpress tusr ON tus.id = tusr.strategy_id
055- AND tusr.rule_code = 'driver_group'
056- AND tusr.is_deleted = 0
057- -- LEFT JOIN (SELECT import_id, group_concat(DISTINCT import_val) import_vals FROM t_strategy_import_filter_aliexpress WHERE import_type = 1 AND is_deleted = 0 AND rule_code = 'driver_group' GROUP BY import_id) tusif ON tus.id = tusif.import_id
058- -- AND tusr.filter_express = '9'
059- WHERE
060- tus.is_deleted = 0
061- AND tus.STATUS = 1
062- AND tus.strategy_type = 6 limit 50
063- 第三、再通过第二中查询的数据，再批量查2000多万大表t_strategy_import_filter_aliexpress的数据（记得使用合适的索引）

001- <br />
001+ <br /> 严重级别：一般<br /> 所属项目：aliexpress<br /> 问题原因：慢SQL、大表、多表JOIN：（SELECT<br /> tus.id,<br /> tus.customer_id,<br /> tus.strategy_name,<br /> tus.policy_id,<br /> tus.business_type,<br /> tus.sku_type,<br /> tus.template_name,<br /> tus.product_strategy_name,<br /> tus.strategy_type,<br /> tus.STATUS,<br /> tus.last_execute_time,<br /> tus.execute_status,<br /> tus.execute_type,<br /> tus.execute_cycle,<br /> tus.interval_days,<br /> tus.drive_table,<br /> tusif.import_vals sourceIds <br /> FROM<br /> t_strategy_aliexpress tus<br /> LEFT JOIN t_strategy_rule_aliexpress tusr ON tus.id = tusr.strategy_id <br /> AND tusr.rule_code = 'driver_group' <br /> AND tusr.is_deleted = 0<br /> LEFT JOIN (SELECT import_id, group_concat(DISTINCT import_val) import_vals FROM t_strategy_import_filter_aliexpress WHERE import_type = 1 AND is_deleted = 0 AND rule_code = 'driver_group' GROUP BY import_id) tusif ON tus.id = tusif.import_id <br /> AND tusr.filter_express = '9' <br /> WHERE<br /> tus.is_deleted = 0 <br /> AND tus.STATUS = 1 <br /> AND tus.strategy_type = 6;）<br /> 解决方案：第一、把索引idx_strategy_id 调整为：(strategy_id, rule_code, is_deleted, filter_express)<br /> 第二、先查小表（建议增加分页查，避免数据增长后，出现性能问题）<br /> SELECT<br /> tus.id,<br /> tus.customer_id,<br /> tus.strategy_name,<br /> tus.policy_id,<br /> tus.business_type,<br /> tus.sku_type,<br /> tus.template_name,<br /> tus.product_strategy_name,<br /> tus.strategy_type,<br /> tus.STATUS,<br /> tus.last_execute_time,<br /> tus.execute_status,<br /> tus.execute_type,<br /> tus.execute_cycle,<br /> tus.interval_days,<br /> tus.drive_table<br /> FROM<br /> t_strategy_aliexpress tus<br /> LEFT JOIN t_strategy_rule_aliexpress tusr ON tus.id = tusr.strategy_id <br /> AND tusr.rule_code = 'driver_group' <br /> AND tusr.is_deleted = 0<br /> -- LEFT JOIN (SELECT import_id, group_concat(DISTINCT import_val) import_vals FROM t_strategy_import_filter_aliexpress WHERE import_type = 1 AND is_deleted = 0 AND rule_code = 'driver_group' GROUP BY import_id) tusif ON tus.id = tusif.import_id <br /> -- AND tusr.filter_express = '9' <br /> WHERE<br /> tus.is_deleted = 0 <br /> AND tus.STATUS = 1 <br /> AND tus.strategy_type = 6 limit 50<br /> 第三、再通过第二中查询的数据，再批量查2000多万大表t_strategy_import_filter_aliexpress的数据（记得使用合适的索引）
002- 严重级别：一般<br />
003- 所属项目：aliexpress<br />
004- 问题原因：慢SQL、大表、多表JOIN：（SELECT<br />
005- tus.id,<br />
006- tus.customer_id,<br />
007- tus.strategy_name,<br />
008- tus.policy_id,<br />
009- tus.business_type,<br />
010- tus.sku_type,<br />
011- tus.template_name,<br />
012- tus.product_strategy_name,<br />
013- tus.strategy_type,<br />
014- tus.STATUS,<br />
015- tus.last_execute_time,<br />
016- tus.execute_status,<br />
017- tus.execute_type,<br />
018- tus.execute_cycle,<br />
019- tus.interval_days,<br />
020- tus.drive_table,<br />
021- tusif.import_vals sourceIds <br />
022- FROM<br />
023- t_strategy_aliexpress tus<br />
024- LEFT JOIN t_strategy_rule_aliexpress tusr ON tus.id = tusr.strategy_id <br />
025- AND tusr.rule_code = 'driver_group' <br />
026- AND tusr.is_deleted = 0<br />
027- LEFT JOIN (SELECT import_id, group_concat(DISTINCT import_val) import_vals FROM t_strategy_import_filter_aliexpress WHERE import_type = 1 AND is_deleted = 0 AND rule_code = 'driver_group' GROUP BY import_id) tusif ON tus.id = tusif.import_id <br />
028- AND tusr.filter_express = '9' <br />
029- WHERE<br />
030- tus.is_deleted = 0 <br />
031- AND tus.STATUS = 1 <br />
032- AND tus.strategy_type = 6;）<br />
033- 解决方案：第一、把索引idx_strategy_id 调整为：(strategy_id, rule_code, is_deleted, filter_express)<br />
034- 第二、先查小表（建议增加分页查，避免数据增长后，出现性能问题）<br />
035- SELECT<br />
036- tus.id,<br />
037- tus.customer_id,<br />
038- tus.strategy_name,<br />
039- tus.policy_id,<br />
040- tus.business_type,<br />
041- tus.sku_type,<br />
042- tus.template_name,<br />
043- tus.product_strategy_name,<br />
044- tus.strategy_type,<br />
045- tus.STATUS,<br />
046- tus.last_execute_time,<br />
047- tus.execute_status,<br />
048- tus.execute_type,<br />
049- tus.execute_cycle,<br />
050- tus.interval_days,<br />
051- tus.drive_table<br />
052- FROM<br />
053- t_strategy_aliexpress tus<br />
054- LEFT JOIN t_strategy_rule_aliexpress tusr ON tus.id = tusr.strategy_id <br />
055- AND tusr.rule_code = 'driver_group' <br />
056- AND tusr.is_deleted = 0<br />
057- -- LEFT JOIN (SELECT import_id, group_concat(DISTINCT import_val) import_vals FROM t_strategy_import_filter_aliexpress WHERE import_type = 1 AND is_deleted = 0 AND rule_code = 'driver_group' GROUP BY import_id) tusif ON tus.id = tusif.import_id <br />
058- -- AND tusr.filter_express = '9' <br />
059- WHERE<br />
060- tus.is_deleted = 0 <br />
061- AND tus.STATUS = 1 <br />
062- AND tus.strategy_type = 6 limit 50<br />
063- 第三、再通过第二中查询的数据，再批量查2000多万大表t_strategy_import_filter_aliexpress的数据（记得使用合适的索引）