商品列表查询优化咨询：现有查询耗时0.4534秒

嘿，我来帮你梳理下这个商品列表查询的优化思路～先把你的查询（看起来后半段没写完，不过核心逻辑我get到了）贴出来：

SELECT SQL_CALC_FOUND_ROWS DISTINCT 
    tp.prod_id, tp.prod_name, tp.prod_shop, tp.prod_retail_price, 
    tp.prod_sale_price, tp.prod_initial_price, tp.prod_stock, 
    ts.shop_id, ts.shop_name, ts.shop_logo, ts.shop_description, 
    ts.shop_title, tu.user_profile_image, 
    ( SELECT pdiscount_price FROM tbl_product_discounts tpd WHERE tpd.pdiscount_product_id = tp.prod_id ) AS discount_price
-- 推测这里还应该有tp与ts、tu的JOIN条件，以及可能的WHERE/LIMIT子句

下面是几个针对性的优化方向，亲测能大幅降低查询耗时：

1. 换掉SQL_CALC_FOUND_ROWS，改用两次查询 #

SQL_CALC_FOUND_ROWS会强制MySQL扫描所有符合条件的行来计算总条数，数据量越大越慢。如果需要分页的总记录数，建议拆分两步：

• 第一步：执行SELECT COUNT(DISTINCT tp.prod_id) FROM ...（带上你的WHERE条件）获取总数
• 第二步：执行带LIMIT的查询获取当前页的列表数据
  虽然是两次查询，但实际执行效率比一次用SQL_CALC_FOUND_ROWS高很多。

2. 把标量子查询改成LEFT JOIN #

你现在用的子查询会逐行执行（相当于每查一个商品就跑一次子查询），商品多了性能直接崩盘。改成JOIN写法，让MySQL用更高效的连接算法处理：

SELECT DISTINCT 
    tp.prod_id, tp.prod_name, tp.prod_shop, tp.prod_retail_price, 
    tp.prod_sale_price, tp.prod_initial_price, tp.prod_stock, 
    ts.shop_id, ts.shop_name, ts.shop_logo, ts.shop_description, 
    ts.shop_title, tu.user_profile_image, 
    tpd.pdiscount_price AS discount_price
FROM tbl_products tp
-- 关联折扣表，用LEFT JOIN保证无折扣的商品也能显示
LEFT JOIN tbl_product_discounts tpd ON tpd.pdiscount_product_id = tp.prod_id
-- 假设商品和店铺的关联条件是prod_shop对应shop_id
LEFT JOIN tbl_shops ts ON tp.prod_shop = ts.shop_id
-- 假设店铺和用户的关联条件是店铺拥有者对应user_id
LEFT JOIN tbl_users tu ON ts.shop_owner_id = tu.user_id
-- 这里加上你的WHERE、ORDER BY、LIMIT等条件

3. 给查询加合适的索引 #

索引是性能提升的核心，针对这个查询，建议添加这些索引：

• tbl_products(prod_shop, prod_id)：复合索引，帮助快速定位指定店铺的商品，同时覆盖prod_id字段
• tbl_product_discounts(pdiscount_product_id, pdiscount_price)：覆盖索引，关联时直接从索引取数据，不用回表查原表
• tbl_shops(shop_id)：主键索引应该已经存在，如果没有必须补上；如果有店铺状态过滤，可加tbl_shops(shop_id, shop_status)复合索引
• tbl_users(user_id, user_profile_image)：覆盖索引，获取用户头像时直接从索引读取，避免回表

4. 检查是否真的需要DISTINCT #

DISTINCT会让MySQL对结果集去重，需要额外的排序和内存开销。如果你的JOIN逻辑不会产生重复行（比如一个商品对应最多一个折扣、一个店铺对应一个用户），直接去掉DISTINCT能省不少时间。

5. 分页查询避免大OFFSET #

如果是分页场景，别用LIMIT 10000, 20这种写法——MySQL会先扫描前10020行再丢弃前10000行，非常低效。改用基于主键的分页：

-- 假设上一页最后一个商品的prod_id是10000
SELECT ... 
FROM tbl_products tp
WHERE tp.prod_id > 10000
ORDER BY tp.prod_id
LIMIT 20;

内容的提问来源于stack exchange，提问作者Mohit