MySQL含JSON字段的复合索引在范围/IN查询中未生效的咨询

已创建的索引 #

create index main_cp_index on catalogue_product(
  product_class_id, is_public,
  (cast(coalesce(data->>'$."need_tags"', 0) as unsigned)) ASC);

等值查询可正常使用索引 #

当对need_tags进行等值查询时，索引main_cp_index能被正常使用：

mysql> explain SELECT count(*) FROM `catalogue_product` WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) = 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: catalogue_product
   partitions: NULL
         type: ref
possible_keys: catalogue_product_product_class_id_0c6c5b54_fk_catalogue,catalogue_product_is_public_1cf798c5,main_cp_index
          key: main_cp_index
      key_len: 14
          ref: const,const,const
         rows: 2740
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

范围/IN/BETWEEN查询无法使用目标索引 #

IN查询的情况 #

mysql> explain SELECT count(*) FROM `catalogue_product` WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) in (0, 1))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: catalogue_product
   partitions: NULL
         type: ref
possible_keys: catalogue_product_product_class_id_0c6c5b54_fk_catalogue,catalogue_product_is_public_1cf798c5,main_cp_index
          key: catalogue_product_product_class_id_0c6c5b54_fk_catalogue
      key_len: 5
          ref: const
         rows: 3471
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

比较运算符查询的情况 #

mysql> explain SELECT count(*) FROM `catalogue_product` WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) < 2)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: catalogue_product
   partitions: NULL
         type: ref
possible_keys: catalogue_product_product_class_id_0c6c5b54_fk_catalogue,catalogue_product_is_public_1cf798c5,main_cp_index
          key: catalogue_product_product_class_id_0c6c5b54_fk_catalogue
      key_len: 5
          ref: const
         rows: 3471
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

BETWEEN查询的情况 #

mysql> explain SELECT count(*) FROM `catalogue_product` WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) between 0 and 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: catalogue_product
   partitions: NULL
         type: ref
possible_keys: catalogue_product_product_class_id_0c6c5b54_fk_catalogue,catalogue_product_is_public_1cf798c5,main_cp_index
          key: catalogue_product_product_class_id_0c6c5b54_fk_catalogue
      key_len: 5
          ref: const
         rows: 3471
     filtered: 11.11
        Extra: Using where

表结构信息 #

mysql> show create table catalogue_product\G
*************************** 1. row ***************************
       Table: catalogue_product
Create Table: CREATE TABLE `catalogue_product` (
  `id` int NOT NULL AUTO_INCREMENT,
  `structure` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `upc` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `rating` double DEFAULT NULL,
  `date_created` datetime(6) NOT NULL,
  `date_updated` datetime(6) NOT NULL,
  `is_discountable` tinyint(1) NOT NULL,
  `parent_id` int DEFAULT NULL,
  `product_class_id` int DEFAULT NULL,
  `is_public` tinyint(1) NOT NULL,
  `meta_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci,
  `meta_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  `data` json NOT NULL DEFAULT (_utf8mb4'{}'),
  `title_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  `title_sl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  `nutrition` json NOT NULL DEFAULT (_utf8mb4'{}'),
  `brand_id` int DEFAULT NULL,
  `contents` json NOT NULL DEFAULT (_utf8mb4'{}'),
  `allergens` json NOT NULL DEFAULT (_utf8mb4'[]'),
  `description_en` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci,
  `description_sl` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci,
  `country` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `priority` smallint NOT NULL,
  `code` varchar(255) COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `upc` (`upc`),
  UNIQUE KEY `code` (`code`),
  KEY `catalogue_product_parent_id_9bfd2382_fk_catalogue_product_id` (`parent_id`),
  KEY `catalogue_product_product_class_id_0c6c5b54_fk_catalogue` (`product_class_id`),
  KEY `catalogue_product_slug_c8e2e2b9` (`slug`),
  KEY `catalogue_product_date_updated_d3a1785d` (`date_updated`),
  KEY `catalogue_product_date_created_d66f485a` (`date_created`),
  KEY `catalogue_product_is_public_1cf798c5` (`is_public`),
  KEY `catalogue_product_brand_id_74599134_fk_products_brand_id` (`brand_id`),
  KEY `catalogue_product_priority_983a8f56` (`priority`),
  KEY `need_tags_index` ((cast(json_extract(`data`,_utf8mb4'$."need_tags"') as char(5) charset utf8mb4))),
  KEY `start_index` ((cast(json_extract(`data`,_utf8mb4'$."start"') as char(10) charset utf8mb4))),
  KEY `touristic_index` ((cast(json_extract(`data`,_utf8mb4'$."touristic"') as char(2) charset utf8mb4))),
  KEY `main_cp_index` (`product_class_id`,`is_public`,(cast(coalesce(json_unquote(json_extract(`data`,_utf8mb4'$."need_tags"')),0) as unsigned))),
  CONSTRAINT `catalogue_product_brand_id_74599134_fk_products_brand_id` FOREIGN KEY (`brand_id`) REFERENCES `products_brand` (`id`),
  CONSTRAINT `catalogue_product_parent_id_9bfd2382_fk_catalogue_product_id` FOREIGN KEY (`parent_id`) REFERENCES `catalogue_product` (`id`),
  CONSTRAINT `catalogue_product_product_class_id_0c6c5b54_fk_catalogue` FOREIGN KEY (`product_class_id`) REFERENCES `catalogue_productclass` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18229 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci

问题 #

是否有办法让这类IN/范围查询也能使用main_cp_index？该查询是更大查询的一部分，需保留need_tags相关条件以保证索引前缀顺序。

解决方案 #

1. 强制指定索引 #

直接在查询中通过FORCE INDEX指定使用目标索引，绕过优化器的自动选择逻辑：

SELECT count(*) FROM `catalogue_product` 
FORCE INDEX(main_cp_index)
WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) in (0, 1));

这种方式简单直接，适合确认目标索引性能更优的场景。

2. 保持查询与索引表达式完全一致 #

虽然data->>'$."need_tags"'和json_unquote(json_extract(data,_utf8mb4'$."need_tags"'))功能等价，但优化器可能对表达式的文本匹配更敏感。尝试在查询中完全复用索引定义中的表达式：

SELECT count(*) FROM `catalogue_product` 
WHERE (product_class_id = 3 AND is_public = True AND cast(coalesce(json_unquote(json_extract(data,_utf8mb4'$."need_tags"')),0) as unsigned) in (0, 1));

3. 更新表统计信息 #

如果表的数据分布发生变化，MySQL的统计信息可能过时，导致优化器做出错误的索引选择。执行以下命令更新统计信息：

ANALYZE TABLE catalogue_product;

更新后重新执行EXPLAIN检查索引使用情况。

4. 使用生成列重构索引 #

将JSON字段中的need_tags提取为物理存储的生成列，再基于生成列创建索引，这种方式对各类查询的兼容性最好：
首先添加生成列：

ALTER TABLE catalogue_product 
ADD COLUMN need_tags_unsigned INT UNSIGNED 
GENERATED ALWAYS AS (cast(coalesce(data->>'$."need_tags"', 0) as unsigned)) STORED;

然后创建新的复合索引：

CREATE INDEX main_cp_index_v2 ON catalogue_product(product_class_id, is_public, need_tags_unsigned);

之后查询直接使用生成列：

SELECT count(*) FROM `catalogue_product` 
WHERE product_class_id = 3 AND is_public = True AND need_tags_unsigned IN (0,1);

生成列是物理存储的，优化器能更清晰地识别索引的适用场景，包括范围、IN、BETWEEN等查询。

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