数据库：zhcxkj_center_listing_comprehensive
查询时间：10.7752
扫描行数：2068.45
发送行数：653.7
指纹SQL: select tal . id , tal . customer_id customerId , tal . source_id sourceId , tal . source_name sourceName , tal . pf_item_id pfItemId , tal . status status , tal . pf_item_id pfListingId , tal . currency_code currency , tal . first_publish_time publishTime , tal . title title , tal . pf_category_id pfCategoryId , tal . pf_category_path pfCategoryPath , tal . country country , tal . currency_code currencyCode , tal . offline_time offlineTime , tal . has_variant hasVariant , tal . has_market_picture hasMarketPicture , tal . pop_type popType , tal . pop_choice_change_time popChoiceChangeTime , tal . delivery_time deliveryTime , tal . main_image_url mainImageUrl , tal . business_admin_id businessAdminId , tal . business_admin_name businessAdminName , tal . handle_status handleStatus , tal . pf_gmt_modified_time pfGmtModifiedTime , tal . source_admin_id sourceAdminId , tal . source_admin_name sourceAdminName , tal . currency_code , tals . id listingSkuId , tals . listing_id listingId , tals . pf_source_sku pfSourceSku , tals . sku , tals . sale_price price , tals . pf_sku_id pfSkuId , tals . original_price originalPrice , tals . sale_price salePrice , tals . property_id propertyId , tals . pf_gross_weight pfGrossWeight , tals . pf_package_height pfPackageHeight , tals . pf_package_length pfPackageLength , tals . pf_package_width pfPackageWidth FROM ? tal inner join t_aliexpress_listing_sku tals on tal . id = tals . listing_id WHERE tals . sku IN ( ? , ? , ? , ? , ? ) ORDER BY tals . id ASC limit ? ;
真实SQL：select tal.id, tal.customer_id customerId, tal.source_id sourceId, tal.source_name sourceName, tal.pf_item_id pfItemId, tal.status status, tal.pf_item_id pfListingId, tal.currency_code currency, tal.first_publish_time publishTime, tal.title title, tal.pf_category_id pfCategoryId, tal.pf_category_path pfCategoryPath, tal.country country, tal.currency_code currencyCode, tal.offline_time offlineTime, tal.has_variant hasVariant, tal.has_market_picture hasMarketPicture, tal.pop_type popType, tal.pop_choice_change_time popChoiceChangeTime, tal.delivery_time deliveryTime, tal.main_image_url mainImageUrl, tal.business_admin_id businessAdminId, tal.business_admin_name businessAdminName, tal.handle_status handleStatus, tal.pf_gmt_modified_time pfGmtModifiedTime, tal.source_admin_id sourceAdminId, tal.source_admin_name sourceAdminName, tal.currency_code, tals.id listingSkuId, tals.listing_id listingId, tals.pf_source_sku pfSourceSku, tals.sku, tals.sale_price price, tals.pf_sku_id pfSkuId, tals.original_price originalPrice, tals.sale_price salePrice, tals.property_id propertyId, tals.pf_gross_weight pfGrossWeight, tals.pf_package_height pfPackageHeight, tals.pf_package_length pfPackageLength, tals.pf_package_width pfPackageWidth from t_aliexpress_listing tal inner join t_aliexpress_listing_sku tals on tal.id = tals.listing_id WHERE tals.sku IN ( 17208004 , 17402179 , 17273469 , 17294796 , 1732164 ) ORDER BY tals.id ASC limit 1000;
无人工修改
AISQL修改建议： 优化后的SQL
SELECT tal.id, tal.customer_id customerId, tal.source_id sourceId, tal.source_name sourceName, tal.pf_item_id pfItemId, tal.status status, tal.pf_item_id pfListingId, tal.currency_code currency, tal.first_publish_time publishTime, tal.title title, tal.pf_category_id pfCategoryId, tal.pf_category_path pfCategoryPath, tal.country country, tal.currency_code currencyCode, tal.offline_time offlineTime, tal.has_variant hasVariant, tal.has_market_picture hasMarketPicture, tal.pop_type popType, tal.pop_choice_change_time popChoiceChangeTime, tal.delivery_time deliveryTime, tal.main_image_url mainImageUrl, tal.business_admin_id businessAdminId, tal.business_admin_name businessAdminName, tal.handle_status handleStatus, tal.pf_gmt_modified_time pfGmtModifiedTime, tal.source_admin_id sourceAdminId, tal.source_admin_name sourceAdminName, tal.currency_code, tals.id listingSkuId, tals.listing_id listingId, tals.pf_source_sku pfSourceSku, tals.sku, tals.sale_price price, tals.pf_sku_id pfSkuId, tals.original_price originalPrice, tals.sale_price salePrice, tals.property_id propertyId, tals.pf_gross_weight pfGrossWeight, tals.pf_package_height pfPackageHeight, tals.pf_package_length pfPackageLength, tals.pf_package_width pfPackageWidth
FROM t_aliexpress_listing_sku tals
STRAIGHT_JOIN t_aliexpress_listing tal ON tal.id = tals.listing_id
WHERE tals.sku IN (17208004,17402179,17273469,17294796,1732164)
ORDER BY tals.id ASC
LIMIT 1000;
explain表：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tals | None | range | idx_sku,idx_listing_id | idx_sku | 259 | None | 1209 | 100 | Using index condition; Using filesort |
| 1 | SIMPLE | tal | None | eq_ref | PRIMARY | PRIMARY | 8 | zhcxkj_center_listing_comprehensive.tals.listing_id | 1 | 100 | None |
额外建议：
索引建议：
建议添加索引：ALTER TABLE t_aliexpress_listing_sku ADD INDEX idx_sku_id(sku,id);
【t_aliexpress_listing_sku】表 【sku,id】字段，索引分析：
+--------------------------+---------------------+---------------+---------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+==========================+=====================+===============+===============+
| t_aliexpress_listing_sku | idx_listing_id | sku | 25297306 |
+--------------------------+---------------------+---------------+---------------+
| t_aliexpress_listing_sku | idx_sku | sku | 622476 |
+--------------------------+---------------------+---------------+---------------+
| t_aliexpress_listing_sku | idx_source_id_sku | sku | 14199740 |
+--------------------------+---------------------+---------------+---------------+
| t_aliexpress_listing_sku | idx_source_item_sku | sku | 24832022 |
+--------------------------+---------------------+---------------+---------------+
| t_aliexpress_listing_sku | PRIMARY | id | 25590718 |
+--------------------------+---------------------+---------------+---------------+
→ 【t_aliexpress_listing】 表，无需添加任何索引。
数据库：zhcxkj_center_datasync_comprehensive
查询时间：13.5868
扫描行数：27875.9
发送行数：1
指纹SQL: select count ( * ) FROM ? where customer_id = ? and source_id = ? and type = ? and status in ( ? , ? ) ;
真实SQL：select count(*) from t_shopee_listing_pull_queue where customer_id = 1 and source_id = 13738 and type = 0 and status in ( 0 , 1 );
无人工修改
AISQL修改建议：ALTER TABLE t_shopee_listing_pull_queue ADD INDEX idx_optimize (customer_id, source_id, type, status);
SELECT count(*)
FROM t_shopee_listing_pull_queue
WHERE customer_id = 1
AND source_id = 13738
AND type = 0
AND status IN (0,1);
explain表：
额外建议：
索引建议：