MySQL left join优化方法一例

SELECT a.id,b.series_id,COUNT(b.id) cnt FROM 
(SELECT id FROM lib_brand_series WHERE 2>1 AND brand_id=1 AND category_id=5 AND STATUS=1) a 
LEFT JOIN 
(SELECT id,series_id FROM lib_product_index a WHERE 2>1 AND small_category_id = 252 AND brand_id = 1) b 
ON a.id = b.series_id GROUP BY a.id

执行计划：

优化后sql有一点点修改：

SELECT a.id,COUNT(b.id)
FROM lib_brand_series a LEFT JOIN 
(SELECT * FROM lib_product_index WHERE small_category_id = 252 AND brand_id = 1)b
ON a.id = b.series_id
WHERE a.brand_id=1 
    AND a.category_id=5 
    AND a.STATUS=1 
GROUP BY a.id

执行计划：