如何优化含HAVING子句的MySQL查询，避免表b全表扫描？

执行以下查询时，product_variants表（表b）会触发全表扫描，已尝试为表b的product_id和product_category_variants_values_id创建索引但问题未解决，以下是具体分析和优化方案：

查询语句 #

SELECT
    a.product_id, a.product_name, a.product_qty, a.product_stock_status, a.product_type,
    a.product_mode, a.slug, a.product_price, a.product_sales_price 
FROM `product_meta` a 
JOIN `product_variants` b ON a.product_id = b.product_id 
WHERE a.product_category = 1 AND 
    CASE
        WHEN a.product_sales_price>0 THEN a.product_sales_price BETWEEN 0 AND 100000
        ELSE a.product_price BETWEEN 0 AND 100000
    END 
GROUP BY a.product_id 
HAVING SUM(b.product_category_variants_values_id = 24)
AND SUM(b.product_category_variants_values_id = 9);

表结构 #

product_meta（表a） #

CREATE TABLE `product_meta` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(500) DEFAULT NULL,
  `product_price` int(50) NOT NULL DEFAULT 0,
  `product_sales_price` int(50) DEFAULT NULL,
  `product_qty` int(10) NOT NULL DEFAULT 0,
  `product_stock_status` int(10) NOT NULL DEFAULT 1,
  `product_category` int(10) NOT NULL DEFAULT 0,
  `product_tag` int(10) NOT NULL DEFAULT 0,
  `product_type` varchar(20) NOT NULL DEFAULT '0',
  `product_mode` varchar(30) NOT NULL DEFAULT '0',
  `slug` varchar(500) NOT NULL,
  PRIMARY KEY (`product_id`),
  KEY `product_category_search` (`product_category`),
  KEY `product_tag_search` (`product_tag`),
  KEY `indx_slug` (`slug`)
);

product_variants（表b） #

CREATE TABLE `product_variants` (
  `product_variants_id` int(11) NOT NULL AUTO_INCREMENT,
  `created_date` date NOT NULL DEFAULT current_timestamp(),
  `updated_date` date NOT NULL DEFAULT current_timestamp(),
  `product_id` varchar(50) NOT NULL,
  `product_category_variants_id` varchar(50) NOT NULL,
  `product_category_variants_values_id` varchar(50) NOT NULL,
  PRIMARY KEY (`product_variants_id`),
  KEY `indx_product_id_product_category_variants_values_id` (`product_category_variants_values_id`,`product_id`)
);

查询执行计划 #

优化方案 #

1. 修正数据类型不匹配问题 #

表a的product_id是int类型，表b的product_id是varchar类型，关联时会触发隐式类型转换，导致MySQL无法有效使用索引，进而引发全表扫描。

执行以下语句修改表b的product_id类型：

ALTER TABLE `product_variants` MODIFY COLUMN `product_id` int(11) NOT NULL;

2. 调整复合索引顺序 #

现有索引indx_product_id_product_category_variants_values_id的列顺序是(product_category_variants_values_id, product_id)，但查询中是先通过product_id关联表a，再筛选特定的product_category_variants_values_id。

创建以product_id为前缀的复合索引，让关联时能快速定位到对应产品的变体记录：

CREATE INDEX idx_product_id_values_id ON product_variants(product_id, product_category_variants_values_id);

创建完成后可以删除原有无效索引：

DROP INDEX indx_product_id_product_category_variants_values_id ON product_variants;

3. 重构查询逻辑，避免分组开销 #

原查询使用GROUP BY和SUM来判断产品是否同时存在两种变体，这种方式会生成临时表并触发文件排序，效率较低。改用EXISTS子查询，可以利用索引快速验证条件，避免不必要的分组操作：

优化后的查询语句：

SELECT
    a.product_id, a.product_name, a.product_qty, a.product_stock_status, a.product_type,
    a.product_mode, a.slug, a.product_price, a.product_sales_price 
FROM `product_meta` a 
WHERE a.product_category = 1 
    AND (
        (a.product_sales_price > 0 AND a.product_sales_price BETWEEN 0 AND 100000)
        OR (a.product_sales_price IS NULL OR a.product_sales_price <= 0) AND a.product_price BETWEEN 0 AND 100000
    )
    AND EXISTS (
        SELECT 1 
        FROM product_variants b1 
        WHERE b1.product_id = a.product_id 
          AND b1.product_category_variants_values_id = '24'
    )
    AND EXISTS (
        SELECT 1 
        FROM product_variants b2 
        WHERE b2.product_id = a.product_id 
          AND b2.product_category_variants_values_id = '9'
    );

4. 为表a添加价格相关索引 #

表a的WHERE条件包含价格判断，可以创建覆盖product_category和价格字段的复合索引，进一步加快表a的筛选速度：

CREATE INDEX idx_category_price ON product_meta(product_category, product_sales_price, product_price);

内容的提问来源于stack exchange，提问作者MUHSIN MOHAMED PC