表结构
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的选择就比较“随性”了。能力有限,短时间内还不能解释这种差异到底是什么,感兴趣的朋友可以留言一起探讨或指导