BUG #59182 技术研发中心-慢SQL审批提交工单 - 技术中心-需求池

重现步骤

反馈人：王凯
数据库：zhcxkj-center-product
查询时间：16.2004
扫描行数：48316.1
发送行数：998.818
指纹SQL: SELECT p . id , p . sku , p . productGroupSku , qlength , qwidth , qheight , p . isGroup FROM ( SELECT rp . id , rp . sku AS sku , rp . product_group_sku AS productGroupSku , rp . is_group as isGroup , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_length ELSE rp . length END AS qlength , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_width ELSE rp . width END AS qwidth , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_height ELSE rp . height END AS qheight , CASE WHEN rp . pack_weight > ? THEN rp . pack_weight ELSE rp . gross_weight END AS weight FROM ? rpa INNER JOIN rb_product rp ON rpa . customer_id = rp . customer_id AND rpa . sku = rp . sku AND rp . delete_status = ? AND rpa . check_status = ? WHERE rp . customer_id = ? AND rp . id > ? AND rpa . check_time >= ? AND rpa . check_time <= ? AND rp . product_state IN ( ? , ? , ? , ? , ? , ? ) AND rp . has_law_firm_agent IN ( ? , ? , ? ) AND rp . with_battery IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) p ORDER BY id asc LIMIT ? ;
真实SQL：SELECT p.id,p.sku,p.productGroupSku,qlength,qwidth,qheight,p.isGroup FROM ( SELECT rp.id, rp.sku AS sku, rp.product_group_sku AS productGroupSku,rp.is_group as isGroup, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_length ELSE rp.length END AS qlength, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_width ELSE rp.width END AS qwidth, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_height ELSE rp.height END AS qheight, CASE WHEN rp.pack_weight > 0 THEN rp.pack_weight ELSE rp.gross_weight END AS weight FROM rb_product_admin rpa INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku AND rp.delete_status=0 AND rpa.check_status = 1 WHERE rp.customer_id = 1 AND rp.id > 101576 AND rpa.check_time >= 1995-08-10 14:37:19.843 AND rpa.check_time <= 2025-08-10 14:37:19.843 AND rp.product_state IN ( 0 , 1 , 2 , 3 , 5 , 4 ) AND rp.has_law_firm_agent IN ( -1 , 0 , 1 ) AND rp.with_battery IN ( 6 , 7 , 8 , 11 , 15 , 26 , 30 , 41 , 46 , 384 , 392 , 393 , 394 , 395 , 396 , 397 , 400 , 401 , 429 , 448 , 471 , 474 , 477 , 480 , 481 ) ) p ORDER BY id asc LIMIT 1000;
无人工修改
AISQL修改建议：优化后的SQL语句：

SELECT rp.id,
rp.sku,
rp.product_group_sku AS productGroupSku,
CASE WHEN rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0 THEN rp.pack_length ELSE rp.length END AS qlength,
CASE WHEN rp.pack_width > 0 AND rp.pack_length > 0 AND rp.pack_height > 0 THEN rp.pack_width ELSE rp.width END AS qwidth,
CASE WHEN rp.pack_height > 0 AND rp.pack_length > 0 AND rp.pack_width > 0 THEN rp.pack_height ELSE rp.height END AS qheight,
rp.is_group AS isGroup
FROM rb_product_admin rpa
INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku
WHERE rp.customer_id = 1
AND rp.id > 101576
AND rp.delete_status = 0
AND rpa.check_status = 1
AND rpa.check_time BETWEEN 1995-08-10 14:37:19.843 AND 2025-08-10 14:37:19.843
AND rp.product_state IN (0,1,2,3,4,5)
AND rp.has_law_firm_agent IN (-1,0,1)
AND rp.with_battery IN (6,7,8,11,15,26,30,41,46,384,392,393,394,395,396,397,400,401,429,448,471,474,477,480,481)
ORDER BY rp.id ASC
LIMIT 1000;
explain表：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	rp	None	range	PRIMARY,uidx_rb_product,idx_deletestatus_updatetime,idx_withbattery_productstate,idx_has_law_firm_agent,idx_product_customer_id	PRIMARY	8	None	2177566	0.28	Using where
1	SIMPLE	rpa	None	ref	uxid_rb_product_admin,idx_check_status,idx_customer_id_sku_check_time,idx_check_time_check_status_sku_customer_id,idx_product_admin_customer_id,idx_check_status_sku_customer_id	uxid_rb_product_admin	402	zhcxkj-center-product.rp.sku	1	12.5	Using index condition; Using where

额外建议：
索引建议：
取出表【rb_product】 where条件字段【customer_id】 100000 条记录，重复的数据有：【95110】条，没有必要为该字段创建索引。

取出表【rb_product】 where条件字段【product_state】 100000 条记录，重复的数据有：【94500】条，没有必要为该字段创建索引。

取出表【rb_product】 where条件字段【has_law_firm_agent】 100000 条记录，重复的数据有：【95097】条，没有必要为该字段创建索引。

取出表【rb_product】 where条件字段【with_battery】 100000 条记录，重复的数据有：【94451】条，没有必要为该字段创建索引。

→ 【rb_product】表【id】字段，索引已经存在，无需添加任何索引。

【rb_product】表【id】字段，索引分析：
+--------------+--------------------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+==============+================================+===============+===============+
| rb_product | idx_product_customer_id | id | 4355132 |
+--------------+--------------------------------+---------------+---------------+
| rb_product | idx_property_state_agent_delet | id | 4355132 |
| | e | | |
+--------------+--------------------------------+---------------+---------------+
| rb_product | PRIMARY | id | 4355113 |
+--------------+--------------------------------+---------------+---------------+

取出表【rb_product_admin】 where条件字段【check_time】 100000 条记录，重复的数据有：【100000】条，没有必要为该字段创建索引。

→ 【rb_product_admin】表，无需添加任何索引。

历史记录

2025-08-25 16:55:46, 由 李乐同 创建。
2025-08-25 17:44:52, 由 任芳涛 解决，方案为 非BUG-纯属误判 。
修改了 解决方案，旧值为 ""，新值为 "feibug"。
修改了 解决版本，旧值为 ""，新值为 "trunk"。
修改了 解决日期，旧值为 ""，新值为 "2025-08-25 17:25:14"。
修改了 指派给 ，旧值为 "renfangtao"，新值为 ""。
修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
修改了 是否确认，旧值为 "0"，新值为 "1"。
修改了 解决者 ，旧值为 ""，新值为 "renfangtao"。

需求ID:M-29776
2025-08-25 18:12:35, 由 李乐同 指派给 刘瀚泽。
修改了 指派给，旧值为 ""，新值为 "liuhanze"。
2025-08-28 20:10:30, 由 陈莉珍 编辑。
修改了 所属模块 ，旧值为 "0"，新值为 "1868"。
修改了 业务部门 ，旧值为 "0"，新值为 "2"。
修改了 开发责任人，旧值为 "renfangtao"，新值为 "liuhanze"。
修改了 测试责任人，旧值为 ""，新值为 "liuhanze"。
修改了 解决时效 ，旧值为 "0"，新值为 "0.49"。
修改了 重现步骤 ，区别为：
001- ~~反馈人：王凯~~
001+ 反馈人：王凯数据库：zhcxkj-center-product 查询时间：16.2004 扫描行数：48316.1 发送行数：998.818 指纹SQL: SELECT p . id , p . sku , p . productGroupSku , qlength , qwidth , qheight , p . isGroup FROM ( SELECT rp . id , rp . sku AS sku , rp . product_group_sku AS productGroupSku , rp . is_group as isGroup , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_length ELSE rp . length END AS qlength , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_width ELSE rp . width END AS qwidth , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_height ELSE rp . height END AS qheight , CASE WHEN rp . pack_weight > ? THEN rp . pack_weight ELSE rp . gross_weight END AS weight FROM ? rpa INNER JOIN rb_product rp ON rpa . customer_id = rp . customer_id AND rpa . sku = rp . sku AND rp . delete_status = ? AND rpa . check_status = ? WHERE rp . customer_id = ? AND rp . id > ? AND rpa . check_time >= ? AND rpa . check_time <= ? AND rp . product_state IN ( ? , ? , ? , ? , ? , ? ) AND rp . has_law_firm_agent IN ( ? , ? , ? ) AND rp . with_battery IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) p ORDER BY id asc LIMIT ? ; 真实SQL：SELECT p.id,p.sku,p.productGroupSku,qlength,qwidth,qheight,p.isGroup FROM ( SELECT rp.id, rp.sku AS sku, rp.product_group_sku AS productGroupSku,rp.is_group as isGroup, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_length ELSE rp.length END AS qlength, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_width ELSE rp.width END AS qwidth, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_height ELSE rp.height END AS qheight, CASE WHEN rp.pack_weight > 0 THEN rp.pack_weight ELSE rp.gross_weight END AS weight FROM rb_product_admin rpa INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku AND rp.delete_status=0 AND rpa.check_status = 1 WHERE rp.customer_id = 1 AND rp.id > 101576 AND rpa.check_time >= 1995-08-10 14:37:19.843 AND rpa.check_time <= 2025-08-10 14:37:19.843 AND rp.product_state IN ( 0 , 1 , 2 , 3 , 5 , 4 ) AND rp.has_law_firm_agent IN ( -1 , 0 , 1 ) AND rp.with_battery IN ( 6 , 7 , 8 , 11 , 15 , 26 , 30 , 41 , 46 , 384 , 392 , 393 , 394 , 395 , 396 , 397 , 400 , 401 , 429 , 448 , 471 , 474 , 477 , 480 , 481 ) ) p ORDER BY id asc LIMIT 1000; 无人工修改 AISQL修改建议：优化后的SQL语句： SELECT rp.id, rp.sku, rp.product_group_sku AS productGroupSku, CASE WHEN rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0 THEN rp.pack_length ELSE rp.length END AS qlength, CASE WHEN rp.pack_width > 0 AND rp.pack_length > 0 AND rp.pack_height > 0 THEN rp.pack_width ELSE rp.width END AS qwidth, CASE WHEN rp.pack_height > 0 AND rp.pack_length > 0 AND rp.pack_width > 0 THEN rp.pack_height ELSE rp.height END AS qheight, rp.is_group AS isGroup FROM rb_product_admin rpa INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku WHERE rp.customer_id = 1 AND rp.id > 101576 AND rp.delete_status = 0 AND rpa.check_status = 1 AND rpa.check_time BETWEEN 1995-08-10 14:37:19.843 AND 2025-08-10 14:37:19.843 AND rp.product_state IN (0,1,2,3,4,5) AND rp.has_law_firm_agent IN (-1,0,1) AND rp.with_battery IN (6,7,8,11,15,26,30,41,46,384,392,393,394,395,396,397,400,401,429,448,471,474,477,480,481) ORDER BY rp.id ASC LIMIT 1000; explain表：id
002- ~~数据库：zhcxkj-center-product~~
002+ select_type
003- ~~查询时间：16.2004~~
003+ table
004- ~~扫描行数：48316.1~~
004+ partitions
005- ~~发送行数：998.818~~
005+ type
006- 指纹SQL: SELECT p . id , p . sku , p . productGroupSku , qlength , qwidth , qheight , p . isGroup FROM ( SELECT rp . id , rp . sku AS sku , rp . product_group_sku AS productGroupSku , rp . is_group as isGroup , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_length ELSE rp . length END AS qlength , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_width ELSE rp . width END AS qwidth , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_height ELSE rp . height END AS qheight , CASE WHEN rp . pack_weight > ? THEN rp . pack_weight ELSE rp . gross_weight END AS weight FROM ? rpa INNER JOIN rb_product rp ON rpa . customer_id = rp . customer_id AND rpa . sku = rp . sku AND rp . delete_status = ? AND rpa . check_status = ? WHERE rp . customer_id = ? AND rp . id > ? AND rpa . check_time >= ? AND rpa . check_time <= ? AND rp . product_state IN ( ? , ? , ? , ? , ? , ? ) AND rp . has_law_firm_agent IN ( ? , ? , ? ) AND rp . with_battery IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) p ORDER BY id asc LIMIT ? ;
006+ possible_keys
007- 真实SQL：SELECT p.id,p.sku,p.productGroupSku,qlength,qwidth,qheight,p.isGroup FROM ( SELECT rp.id, rp.sku AS sku, rp.product_group_sku AS productGroupSku,rp.is_group as isGroup, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_length ELSE rp.length END AS qlength, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_width ELSE rp.width END AS qwidth, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_height ELSE rp.height END AS qheight, CASE WHEN rp.pack_weight > 0 THEN rp.pack_weight ELSE rp.gross_weight END AS weight FROM rb_product_admin rpa INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku AND rp.delete_status=0 AND rpa.check_status = 1 WHERE rp.customer_id = 1 AND rp.id > 101576 AND rpa.check_time >= 1995-08-10 14:37:19.843 AND rpa.check_time <= 2025-08-10 14:37:19.843 AND rp.product_state IN ( 0 , 1 , 2 , 3 , 5 , 4 ) AND rp.has_law_firm_agent IN ( -1 , 0 , 1 ) AND rp.with_battery IN ( 6 , 7 , 8 , 11 , 15 , 26 , 30 , 41 , 46 , 384 , 392 , 393 , 394 , 395 , 396 , 397 , 400 , 401 , 429 , 448 , 471 , 474 , 477 , 480 , 481 ) ) p ORDER BY id asc LIMIT 1000;
007+ key
008- ~~无人工修改~~
008+ key_len
009- ~~AISQL修改建议：优化后的SQL语句：~~
009+ ref
010-
010+ rows
011- ~~SELECT rp.id,~~
011+ filtered
012- ~~rp.sku,~~
012+ Extra
013- ~~rp.product_group_sku AS productGroupSku,~~
013+ 1
014- ~~CASE WHEN rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0 THEN rp.pack_length ELSE rp.length END AS qlength,~~
014+ SIMPLE
015- ~~CASE WHEN rp.pack_width > 0 AND rp.pack_length > 0 AND rp.pack_height > 0 THEN rp.pack_width ELSE rp.width END AS qwidth,~~
015+ rp
016- ~~CASE WHEN rp.pack_height > 0 AND rp.pack_length > 0 AND rp.pack_width > 0 THEN rp.pack_height ELSE rp.height END AS qheight,~~
016+ None
017- ~~rp.is_group AS isGroup~~
017+ range
018- ~~FROM rb_product_admin rpa~~
018+ PRIMARY,uidx_rb_product,idx_deletestatus_updatetime,idx_withbattery_productstate,idx_has_law_firm_agent,idx_product_customer_id
019- ~~INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku~~
019+ PRIMARY
020- ~~WHERE rp.customer_id = 1~~
020+ 8
021- ~~AND rp.id > 101576~~
021+ None
022- ~~AND rp.delete_status = 0~~
022+ 2177566
023- ~~AND rpa.check_status = 1~~
023+ 0.28
024- ~~AND rpa.check_time BETWEEN 1995-08-10 14:37:19.843 AND 2025-08-10 14:37:19.843~~
024+ Using where
025- ~~AND rp.product_state IN (0,1,2,3,4,5)~~
025+ 1
026- ~~AND rp.has_law_firm_agent IN (-1,0,1)~~
026+ SIMPLE
027- ~~AND rp.with_battery IN (6,7,8,11,15,26,30,41,46,384,392,393,394,395,396,397,400,401,429,448,471,474,477,480,481)~~
027+ rpa
028- ~~ORDER BY rp.id ASC~~
028+ None
029- ~~LIMIT 1000;~~
029+ ref
030- ~~explain表：~~
030+ uxid_rb_product_admin,idx_check_status,idx_customer_id_sku_check_time,idx_check_time_check_status_sku_customer_id,idx_product_admin_customer_id,idx_check_status_sku_customer_id
031-
031+ uxid_rb_product_admin
032-
032+ 402
033-
033+ zhcxkj-center-product.rp.sku
034-
034+ 1
035-
035+ 12.5
036-
036+ Using index condition; Using where
037-
037+
038-
038+ 额外建议：索引建议：取出表【rb_product】 where条件字段【customer_id】 100000 条记录，重复的数据有：【95110】条，没有必要为该字段创建索引。取出表【rb_product】 where条件字段【product_state】 100000 条记录，重复的数据有：【94500】条，没有必要为该字段创建索引。取出表【rb_product】 where条件字段【has_law_firm_agent】 100000 条记录，重复的数据有：【95097】条，没有必要为该字段创建索引。取出表【rb_product】 where条件字段【with_battery】 100000 条记录，重复的数据有：【94451】条，没有必要为该字段创建索引。→ 【rb_product】表【id】字段，索引已经存在，无需添加任何索引。【rb_product】表【id】字段，索引分析： +--------------+--------------------------------+---------------+---------------+ | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +==============+================================+===============+===============+ | rb_product | idx_product_customer_id | id | 4355132 | +--------------+--------------------------------+---------------+---------------+ | rb_product | idx_property_state_agent_delet | id | 4355132 | | | e | | | +--------------+--------------------------------+---------------+---------------+ | rb_product | PRIMARY | id | 4355113 | +--------------+--------------------------------+---------------+---------------+ 取出表【rb_product_admin】 where条件字段【check_time】 100000 条记录，重复的数据有：【100000】条，没有必要为该字段创建索引。→ 【rb_product_admin】表，无需添加任何索引。
039-
040-
041-
042- id
043-
044-
045- ~~select_type~~
046-
047-
048- ~~table~~
049-
050-
051- ~~partitions~~
052-
053-
054- ~~type~~
055-
056-
057- ~~possible_keys~~
058-
059-
060- ~~key~~
061-
062-
063- ~~key_len~~
064-
065-
066- ~~ref~~
067-
068-
069- ~~rows~~
070-
071-
072- ~~filtered~~
073-
074-
075- ~~Extra~~
076-
077-
078-
079-
080-
081-
082- 1
083-
084-
085- ~~SIMPLE~~
086-
087-
088- rp
089-
090-
091- ~~None~~
092-
093-
094- ~~range~~
095-
096-
097- ~~PRIMARY,uidx_rb_product,idx_deletestatus_updatetime,idx_withbattery_productstate,idx_has_law_firm_agent,idx_product_customer_id~~
098-
099-
100- ~~PRIMARY~~
101-
102-
103- 8
104-
105-
106- ~~None~~
107-
108-
109- ~~2177566~~
110-
111-
112- ~~0.28~~
113-
114-
115- ~~Using where~~
116-
117-
118-
119-
120- 1
121-
122-
123- ~~SIMPLE~~
124-
125-
126- ~~rpa~~
127-
128-
129- ~~None~~
130-
131-
132- ~~ref~~
133-
134-
135- ~~uxid_rb_product_admin,idx_check_status,idx_customer_id_sku_check_time,idx_check_time_check_status_sku_customer_id,idx_product_admin_customer_id,idx_check_status_sku_customer_id~~
136-
137-
138- ~~uxid_rb_product_admin~~
139-
140-
141- ~~402~~
142-
143-
144- ~~zhcxkj-center-product.rp.sku~~
145-
146-
147- 1
148-
149-
150- ~~12.5~~
151-
152-
153- ~~Using index condition; Using where~~
154-
155-
156-
157-
158-
159- ~~额外建议：~~
160- ~~索引建议：~~
161- ~~取出表【rb_product】 where条件字段【customer_id】 100000 条记录，重复的数据有：【95110】条，没有必要为该字段创建索引。~~
162-
163- ~~取出表【rb_product】 where条件字段【product_state】 100000 条记录，重复的数据有：【94500】条，没有必要为该字段创建索引。~~
164-
165- ~~取出表【rb_product】 where条件字段【has_law_firm_agent】 100000 条记录，重复的数据有：【95097】条，没有必要为该字段创建索引。~~
166-
167- ~~取出表【rb_product】 where条件字段【with_battery】 100000 条记录，重复的数据有：【94451】条，没有必要为该字段创建索引。~~
168-
169- ~~→ 【rb_product】表【id】字段，索引已经存在，无需添加任何索引。~~
170-
171- ~~【rb_product】表【id】字段，索引分析：~~
172- ~~+--------------+--------------------------------+---------------+---------------+~~
173- ~~| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |~~
174- ~~+==============+================================+===============+===============+~~
175- ~~| rb_product | idx_product_customer_id | id | 4355132 |~~
176- ~~+--------------+--------------------------------+---------------+---------------+~~
177- ~~| rb_product | idx_property_state_agent_delet | id | 4355132 |~~
178- ~~| | e | | |~~
179- ~~+--------------+--------------------------------+---------------+---------------+~~
180- ~~| rb_product | PRIMARY | id | 4355113 |~~
181- ~~+--------------+--------------------------------+---------------+---------------+~~
182-
183- ~~取出表【rb_product_admin】 where条件字段【check_time】 100000 条记录，重复的数据有：【100000】条，没有必要为该字段创建索引。~~
184-
185- ~~→ 【rb_product_admin】表，无需添加任何索引。~~
186-
187-

001- ~~反馈人：王凯 ~~
001+ 反馈人：王凯 数据库：zhcxkj-center-product 查询时间：16.2004 扫描行数：48316.1 发送行数：998.818 指纹SQL: SELECT p . id , p . sku , p . productGroupSku , qlength , qwidth , qheight , p . isGroup FROM ( SELECT rp . id , rp . sku AS sku , rp . product_group_sku AS productGroupSku , rp . is_group as isGroup , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_length ELSE rp . length END AS qlength , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_width ELSE rp . width END AS qwidth , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_height ELSE rp . height END AS qheight , CASE WHEN rp . pack_weight > ? THEN rp . pack_weight ELSE rp . gross_weight END AS weight FROM ? rpa INNER JOIN rb_product rp ON rpa . customer_id = rp . customer_id AND rpa . sku = rp . sku AND rp . delete_status = ? AND rpa . check_status = ? WHERE rp . customer_id = ? AND rp . id > ? AND rpa . check_time >= ? AND rpa . check_time <= ? AND rp . product_state IN ( ? , ? , ? , ? , ? , ? ) AND rp . has_law_firm_agent IN ( ? , ? , ? ) AND rp . with_battery IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) p ORDER BY id asc LIMIT ? ; 真实SQL：SELECT p.id,p.sku,p.productGroupSku,qlength,qwidth,qheight,p.isGroup FROM ( SELECT rp.id, rp.sku AS sku, rp.product_group_sku AS productGroupSku,rp.is_group as isGroup, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_length ELSE rp.length END AS qlength, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_width ELSE rp.width END AS qwidth, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_height ELSE rp.height END AS qheight, CASE WHEN rp.pack_weight > 0 THEN rp.pack_weight ELSE rp.gross_weight END AS weight FROM rb_product_admin rpa INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku AND rp.delete_status=0 AND rpa.check_status = 1 WHERE rp.customer_id = 1 AND rp.id > 101576 AND rpa.check_time >= 1995-08-10 14:37:19.843 AND rpa.check_time <= 2025-08-10 14:37:19.843 AND rp.product_state IN ( 0 , 1 , 2 , 3 , 5 , 4 ) AND rp.has_law_firm_agent IN ( -1 , 0 , 1 ) AND rp.with_battery IN ( 6 , 7 , 8 , 11 , 15 , 26 , 30 , 41 , 46 , 384 , 392 , 393 , 394 , 395 , 396 , 397 , 400 , 401 , 429 , 448 , 471 , 474 , 477 , 480 , 481 ) ) p ORDER BY id asc LIMIT 1000; 无人工修改 AISQL修改建议：优化后的SQL语句： SELECT rp.id, rp.sku, rp.product_group_sku AS productGroupSku, CASE WHEN rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0 THEN rp.pack_length ELSE rp.length END AS qlength, CASE WHEN rp.pack_width > 0 AND rp.pack_length > 0 AND rp.pack_height > 0 THEN rp.pack_width ELSE rp.width END AS qwidth, CASE WHEN rp.pack_height > 0 AND rp.pack_length > 0 AND rp.pack_width > 0 THEN rp.pack_height ELSE rp.height END AS qheight, rp.is_group AS isGroup FROM rb_product_admin rpa INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku WHERE rp.customer_id = 1 AND rp.id > 101576 AND rp.delete_status = 0 AND rpa.check_status = 1 AND rpa.check_time BETWEEN 1995-08-10 14:37:19.843 AND 2025-08-10 14:37:19.843 AND rp.product_state IN (0,1,2,3,4,5) AND rp.has_law_firm_agent IN (-1,0,1) AND rp.with_battery IN (6,7,8,11,15,26,30,41,46,384,392,393,394,395,396,397,400,401,429,448,471,474,477,480,481) ORDER BY rp.id ASC LIMIT 1000; explain表： <table><tbody><tr><th>id</th>
002- ~~数据库：zhcxkj-center-product ~~
002+ <th>select_type</th>
003- ~~查询时间：16.2004 ~~
003+ <th>table</th>
004- ~~扫描行数：48316.1 ~~
004+ <th>partitions</th>
005- ~~发送行数：998.818 ~~
005+ <th>type</th>
006- 指纹SQL: SELECT p . id , p . sku , p . productGroupSku , qlength , qwidth , qheight , p . isGroup FROM ( SELECT rp . id , rp . sku AS sku , rp . product_group_sku AS productGroupSku , rp . is_group as isGroup , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_length ELSE rp . length END AS qlength , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_width ELSE rp . width END AS qwidth , CASE WHEN rp . pack_length > ? AND rp . pack_width > ? AND rp . pack_height > ? THEN rp . pack_height ELSE rp . height END AS qheight , CASE WHEN rp . pack_weight > ? THEN rp . pack_weight ELSE rp . gross_weight END AS weight FROM ? rpa INNER JOIN rb_product rp ON rpa . customer_id = rp . customer_id AND rpa . sku = rp . sku AND rp . delete_status = ? AND rpa . check_status = ? WHERE rp . customer_id = ? AND rp . id > ? AND rpa . check_time >= ? AND rpa . check_time <= ? AND rp . product_state IN ( ? , ? , ? , ? , ? , ? ) AND rp . has_law_firm_agent IN ( ? , ? , ? ) AND rp . with_battery IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) p ORDER BY id asc LIMIT ? ; 
006+ <th>possible_keys</th>
007- 真实SQL：SELECT p.id,p.sku,p.productGroupSku,qlength,qwidth,qheight,p.isGroup FROM ( SELECT rp.id, rp.sku AS sku, rp.product_group_sku AS productGroupSku,rp.is_group as isGroup, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_length ELSE rp.length END AS qlength, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_width ELSE rp.width END AS qwidth, CASE WHEN rp.pack_length >0 AND rp.pack_width >0 AND rp.pack_height >0 THEN rp.pack_height ELSE rp.height END AS qheight, CASE WHEN rp.pack_weight > 0 THEN rp.pack_weight ELSE rp.gross_weight END AS weight FROM rb_product_admin rpa INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku AND rp.delete_status=0 AND rpa.check_status = 1 WHERE rp.customer_id = 1 AND rp.id > 101576 AND rpa.check_time >= 1995-08-10 14:37:19.843 AND rpa.check_time <= 2025-08-10 14:37:19.843 AND rp.product_state IN ( 0 , 1 , 2 , 3 , 5 , 4 ) AND rp.has_law_firm_agent IN ( -1 , 0 , 1 ) AND rp.with_battery IN ( 6 , 7 , 8 , 11 , 15 , 26 , 30 , 41 , 46 , 384 , 392 , 393 , 394 , 395 , 396 , 397 , 400 , 401 , 429 , 448 , 471 , 474 , 477 , 480 , 481 ) ) p ORDER BY id asc LIMIT 1000; 
007+ <th>key</th>
008- ~~无人工修改 ~~
008+ <th>key_len</th>
009- ~~AISQL修改建议：优化后的SQL语句： ~~
009+ <th>ref</th>
010- ~~ ~~
010+ <th>rows</th>
011- ~~SELECT rp.id, ~~
011+ <th>filtered</th>
012- ~~rp.sku, ~~
012+ <th>Extra</th>
013- ~~rp.product_group_sku AS productGroupSku, ~~
013+ </tr></tbody><tbody><tr><td>1</td>
014- ~~CASE WHEN rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0 THEN rp.pack_length ELSE rp.length END AS qlength, ~~
014+ <td>SIMPLE</td>
015- ~~CASE WHEN rp.pack_width > 0 AND rp.pack_length > 0 AND rp.pack_height > 0 THEN rp.pack_width ELSE rp.width END AS qwidth, ~~
015+ <td>rp</td>
016- ~~CASE WHEN rp.pack_height > 0 AND rp.pack_length > 0 AND rp.pack_width > 0 THEN rp.pack_height ELSE rp.height END AS qheight, ~~
016+ <td>None</td>
017- ~~rp.is_group AS isGroup ~~
017+ <td>range</td>
018- ~~FROM rb_product_admin rpa ~~
018+ <td>PRIMARY,uidx_rb_product,idx_deletestatus_updatetime,idx_withbattery_productstate,idx_has_law_firm_agent,idx_product_customer_id</td>
019- ~~INNER JOIN rb_product rp ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku ~~
019+ <td>PRIMARY</td>
020- ~~WHERE rp.customer_id = 1 ~~
020+ <td>8</td>
021- ~~AND rp.id > 101576 ~~
021+ <td>None</td>
022- ~~AND rp.delete_status = 0 ~~
022+ <td>2177566</td>
023- ~~AND rpa.check_status = 1 ~~
023+ <td>0.28</td>
024- ~~AND rpa.check_time BETWEEN 1995-08-10 14:37:19.843 AND 2025-08-10 14:37:19.843 ~~
024+ <td>Using where</td>
025- ~~AND rp.product_state IN (0,1,2,3,4,5) ~~
025+ </tr><tr><td>1</td>
026- ~~AND rp.has_law_firm_agent IN (-1,0,1) ~~
026+ <td>SIMPLE</td>
027- ~~AND rp.with_battery IN (6,7,8,11,15,26,30,41,46,384,392,393,394,395,396,397,400,401,429,448,471,474,477,480,481) ~~
027+ <td>rpa</td>
028- ~~ORDER BY rp.id ASC ~~
028+ <td>None</td>
029- ~~LIMIT 1000; ~~
029+ <td>ref</td>
030- ~~explain表： ~~
030+ <td>uxid_rb_product_admin,idx_check_status,idx_customer_id_sku_check_time,idx_check_time_check_status_sku_customer_id,idx_product_admin_customer_id,idx_check_status_sku_customer_id</td>
031- ~~ ~~
031+ <td>uxid_rb_product_admin</td>
032- ~~ ~~
032+ <td>402</td>
033- ~~ ~~
033+ <td>zhcxkj-center-product.rp.sku</td>
034- ~~ ~~
034+ <td>1</td>
035- ~~ ~~
035+ <td>12.5</td>
036- ~~ ~~
036+ <td>Using index condition; Using where</td>
037- ~~ ~~
037+ </tr></tbody></table> 
038- ~~<table>~~
038+ 额外建议： 索引建议： 取出表【rb_product】 where条件字段【customer_id】 100000 条记录，重复的数据有：【95110】条，没有必要为该字段创建索引。 取出表【rb_product】 where条件字段【product_state】 100000 条记录，重复的数据有：【94500】条，没有必要为该字段创建索引。 取出表【rb_product】 where条件字段【has_law_firm_agent】 100000 条记录，重复的数据有：【95097】条，没有必要为该字段创建索引。 取出表【rb_product】 where条件字段【with_battery】 100000 条记录，重复的数据有：【94451】条，没有必要为该字段创建索引。 → 【rb_product】表【id】字段，索引已经存在，无需添加任何索引。 【rb_product】表【id】字段，索引分析： +--------------+--------------------------------+---------------+---------------+ | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +==============+================================+===============+===============+ | rb_product | idx_product_customer_id | id | 4355132 | +--------------+--------------------------------+---------------+---------------+ | rb_product | idx_property_state_agent_delet | id | 4355132 | | | e | | | +--------------+--------------------------------+---------------+---------------+ | rb_product | PRIMARY | id | 4355113 | +--------------+--------------------------------+---------------+---------------+ 取出表【rb_product_admin】 where条件字段【check_time】 100000 条记录，重复的数据有：【100000】条，没有必要为该字段创建索引。 → 【rb_product_admin】表，无需添加任何索引。 
039- ~~<tbody>~~
040- ~~<tr>~~
041- ~~<th>~~
042- id
043- ~~</th>~~
044- ~~<th>~~
045- ~~select_type~~
046- ~~</th>~~
047- ~~<th>~~
048- ~~table~~
049- ~~</th>~~
050- ~~<th>~~
051- ~~partitions~~
052- ~~</th>~~
053- ~~<th>~~
054- ~~type~~
055- ~~</th>~~
056- ~~<th>~~
057- ~~possible_keys~~
058- ~~</th>~~
059- ~~<th>~~
060- ~~key~~
061- ~~</th>~~
062- ~~<th>~~
063- ~~key_len~~
064- ~~</th>~~
065- ~~<th>~~
066- ~~ref~~
067- ~~</th>~~
068- ~~<th>~~
069- ~~rows~~
070- ~~</th>~~
071- ~~<th>~~
072- ~~filtered~~
073- ~~</th>~~
074- ~~<th>~~
075- ~~Extra~~
076- ~~</th>~~
077- ~~</tr>~~
078- ~~</tbody>~~
079- ~~<tbody>~~
080- ~~<tr>~~
081- ~~<td>~~
082- 1
083- ~~</td>~~
084- ~~<td>~~
085- ~~SIMPLE~~
086- ~~</td>~~
087- ~~<td>~~
088- rp
089- ~~</td>~~
090- ~~<td>~~
091- ~~None~~
092- ~~</td>~~
093- ~~<td>~~
094- ~~range~~
095- ~~</td>~~
096- ~~<td>~~
097- ~~PRIMARY,uidx_rb_product,idx_deletestatus_updatetime,idx_withbattery_productstate,idx_has_law_firm_agent,idx_product_customer_id~~
098- ~~</td>~~
099- ~~<td>~~
100- ~~PRIMARY~~
101- ~~</td>~~
102- ~~<td>~~
103- 8
104- ~~</td>~~
105- ~~<td>~~
106- ~~None~~
107- ~~</td>~~
108- ~~<td>~~
109- ~~2177566~~
110- ~~</td>~~
111- ~~<td>~~
112- ~~0.28~~
113- ~~</td>~~
114- ~~<td>~~
115- ~~Using where~~
116- ~~</td>~~
117- ~~</tr>~~
118- ~~<tr>~~
119- ~~<td>~~
120- 1
121- ~~</td>~~
122- ~~<td>~~
123- ~~SIMPLE~~
124- ~~</td>~~
125- ~~<td>~~
126- ~~rpa~~
127- ~~</td>~~
128- ~~<td>~~
129- ~~None~~
130- ~~</td>~~
131- ~~<td>~~
132- ~~ref~~
133- ~~</td>~~
134- ~~<td>~~
135- ~~uxid_rb_product_admin,idx_check_status,idx_customer_id_sku_check_time,idx_check_time_check_status_sku_customer_id,idx_product_admin_customer_id,idx_check_status_sku_customer_id~~
136- ~~</td>~~
137- ~~<td>~~
138- ~~uxid_rb_product_admin~~
139- ~~</td>~~
140- ~~<td>~~
141- ~~402~~
142- ~~</td>~~
143- ~~<td>~~
144- ~~zhcxkj-center-product.rp.sku~~
145- ~~</td>~~
146- ~~<td>~~
147- 1
148- ~~</td>~~
149- ~~<td>~~
150- ~~12.5~~
151- ~~</td>~~
152- ~~<td>~~
153- ~~Using index condition; Using where~~
154- ~~</td>~~
155- ~~</tr>~~
156- ~~</tbody>~~
157- ~~</table>~~
158- ~~ ~~
159- ~~额外建议： ~~
160- ~~索引建议： ~~
161- ~~取出表【rb_product】 where条件字段【customer_id】 100000 条记录，重复的数据有：【95110】条，没有必要为该字段创建索引。 ~~
162- ~~ ~~
163- ~~取出表【rb_product】 where条件字段【product_state】 100000 条记录，重复的数据有：【94500】条，没有必要为该字段创建索引。 ~~
164- ~~ ~~
165- ~~取出表【rb_product】 where条件字段【has_law_firm_agent】 100000 条记录，重复的数据有：【95097】条，没有必要为该字段创建索引。 ~~
166- ~~ ~~
167- ~~取出表【rb_product】 where条件字段【with_battery】 100000 条记录，重复的数据有：【94451】条，没有必要为该字段创建索引。 ~~
168- ~~ ~~
169- ~~→ 【rb_product】表【id】字段，索引已经存在，无需添加任何索引。 ~~
170- ~~ ~~
171- ~~【rb_product】表【id】字段，索引分析： ~~
172- ~~+--------------+--------------------------------+---------------+---------------+ ~~
173- ~~| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | ~~
174- ~~+==============+================================+===============+===============+ ~~
175- ~~| rb_product | idx_product_customer_id | id | 4355132 | ~~
176- ~~+--------------+--------------------------------+---------------+---------------+ ~~
177- ~~| rb_product | idx_property_state_agent_delet | id | 4355132 | ~~
178- ~~| | e | | | ~~
179- ~~+--------------+--------------------------------+---------------+---------------+ ~~
180- ~~| rb_product | PRIMARY | id | 4355113 | ~~
181- ~~+--------------+--------------------------------+---------------+---------------+ ~~
182- ~~ ~~
183- ~~取出表【rb_product_admin】 where条件字段【check_time】 100000 条记录，重复的数据有：【100000】条，没有必要为该字段创建索引。 ~~
184- ~~ ~~
185- ~~→ 【rb_product_admin】表，无需添加任何索引。 ~~
186- ~~ ~~
187- ~~ ~~
2025-09-01 09:22:32, 由 刘瀚泽 关闭。
修改了 Bug状态 ，旧值为 "resolved"，新值为 "closed"。
修改了 指派给 ，旧值为 "liuhanze"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "liuhanze"。
修改了 关闭日期，旧值为 ""，新值为 "2025-09-01 09:22:32"。

切换回原逻辑，暂无问题，bug 先关闭

基本信息
迭代/需求/任务

所属项目	技术中心-需求池
所属模块	多平台 Aliexpress
所属计划
所属迭代
Bug类型
严重程度	一般
优先级	P2
Bug状态	已关闭
业务部门	总经办
影响功能
开发责任人	刘瀚泽
测试责任人	刘瀚泽
解决时效	0.49
激活次数	0
激活日期
是否确认	是
当前指派	Closed 于 2025-09-01 09:22:32
截止日期	0000-00-00
上线时间	2025-08-25 17:45:00
上线时效	0.82
操作系统
浏览器
关键词
抄送给

所属迭代
相关需求
相关任务

Bug的一生
其他相关

由谁创建	于 2025-08-25 16:55:46
影响环境	生产环境-业务反馈
由谁解决	任芳涛于 2025-08-25 17:25:14
解决版本	主干
解决方案	非BUG-纯属误判
由谁关闭	刘瀚泽于 2025-09-01 09:22:32
最后修改	刘瀚泽于 2025-09-01 09:22:32

相关Bug