MySQL语句in中包含1个值和多个值时索引选择的不同（索引建立时间先后会影响MySQL优化器）

表结构

CREATE TABLE `ag_dealer_goods` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `item_id` BIGINT(20) NOT NULL COMMENT '商品ID',
    `status` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '报价状态： -1-已下架，0-待上架,1-已上架',
    `is_deleted` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '删除状态： 0-未删除,1-已删除',
    `gmt_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_itemid_status_gmtmodified` (`item_id` , `status` , `gmt_modified`),
    KEY `idx_itemId` (`item_id`)
)  ENGINE=INNODB AUTO_INCREMENT=1051 DEFAULT CHARSET=UTF8;

索引

问题

MySQL中建立了一个重复的索引idx_itemId（因为索引idx_itemid_status_gmtmodified已经包含了item_id这一列），然后发现SQL语句中in中包含1个值和多个值时选择的索引不同。

实验1

EXPLAIN SELECT *
  FROM db_agreement.ag_dealer_goods
 WHERE 1= 1
   AND item_id IN(58245134,65316292)
   AND status= 1
   AND is_deleted= 0 
 ORDER BY gmt_modified desc;

小结

按照我们主观判断以及MySQL存在的“索引下推”优化，这时应该选择索引idx_itemid_status_gmtmodified，但MySQL却“误”选择了idx_itemId索引；通过多次试验，我们发现当in中只有一个数值时，MySQL才能“正确”地选择我们想要的索引，看试验2

实验2

EXPLAIN SELECT *
  FROM db_agreement.ag_dealer_goods
 WHERE 1= 1
   AND item_id IN(58245134)
   AND status= 1
   AND is_deleted= 0 
 ORDER BY gmt_modified desc;

小结

我们发现当in中只有一个数值时，MySQL能“正确”地选择我们想要的索引

实验3（删除索引idx_itemId）

这一次，我们删除了索引idx_itemId，MySQL作出了与我们预期一致的判断，同时使用了“索引下推”，详细可见Json格式的计划

{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_filesort": true,
      "table": {
        "table_name": "ag_dealer_goods",
        "access_type": "range",
        "possible_keys": [
          "idx_itemid_status_gmtmodified"
        ],
        "key": "idx_itemid_status_gmtmodified",
        "used_key_parts": [
          "item_id",
          "status"
        ],
        "key_length": "9",
        "rows": 2,
        "filtered": 100,
        "index_condition": "((`db_agreement`.`ag_dealer_goods`.`status` = 1) and (`db_agreement`.`ag_dealer_goods`.`item_id` in (58245134,65316292)))",
        "attached_condition": "(`db_agreement`.`ag_dealer_goods`.`is_deleted` = 0)"
      }
    }
  }
}

试验4（索引建立时间顺序）

小结

我们发现索引的顺序会影响MySQL优化器决策

结论

1、索引的顺序会影响MySQL优化器决策；
2、避免建立容易混淆MySQL优化器的重复索引。

思考

in中只有一个值（ref）和多个值（range）时，查询是有差异的，当只有一个值时，MySQL总能帮我们选择到我们想要的那个索引；但是有多个值时，MySQL的选择就比较“随性”了。能力有限，短时间内还不能解释这种差异到底是什么，感兴趣的朋友可以留言一起探讨或指导