MySQL索引失效案例分析
本文记录并分析一个MySQL索引失效的案例。
表结构
以下为ns_delivery_shop表的结构定义：
CREATE TABLE `ns_delivery_shop` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `goods_id` int(10) NOT NULL COMMENT '商品id', `sku_id` int(10) NOT NULL COMMENT '商品sku', `shop_id` int(10) NOT NULL COMMENT '代理商id', `stock` int(10) NOT NULL COMMENT '库存', `sales` int(10) NOT NULL COMMENT '销量', `create_time` int(10) NOT NULL COMMENT '时间', PRIMARY KEY (`id`), KEY `idx_shop_id` (`shop_id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='代理商打货表';
测试数据
表中插入以下测试数据：
INSERT INTO `ns_delivery_shop`(`id`, `goods_id`, `sku_id`, `shop_id`, `stock`, `sales`, `create_time`) VALUES (1, 86, 99, 1, 10, 5, 1613960196), (2, 86, 100, 1, 15, 10, 1613960196), (3, 86, 101, 1, 25, 10, 1613960196), (4, 101, 119, 9, 30, 5, 1613960196), (5, 101, 119, 11, 30, 5, 1613960196), (6, 101, 119, 12, 30, 5, 1613960196);
索引失效SQL
观察到以下SQL语句在特定条件下索引失效：当shop_id = 1的记录数量小于等于2时，索引生效；大于2时，索引失效。
EXPLAIN SELECT stock FROM `ns_delivery_shop` WHERE shop_id = 1;
失效原因分析
MySQL优化器在判断是否使用索引时，会根据数据量和查询条件进行成本评估。当满足shop_id = 1条件的记录数量占总数据量的比例超过一定阈值时，MySQL认为全表扫描的效率更高，从而导致索引失效。 这并非索引本身的问题，而是优化器选择的策略。 在数据量较小的情况下，索引的查找开销可能大于全表扫描的开销。
