经常被问,NOT IN和<>操作就无法走索引?
真想只有一个:具体问题具体分析,没有前提的问题都是耍流氓。
准备测试数据:
## 删除测试表
DROP TABLE IF EXISTS tb2001;
## 创建测试表
CREATE TABLE `tb2001` (
`id`int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`c1`int(11) DEFAULT NULL,
`c2`int(11) DEFAULT NULL,
INDEX idx_c1(c1)
) ENGINE= InnoDB CHARSET =utf8;
## 插入测试数据(10万左右)
## 如果information_schema.columns数据较少,可以重复多次
INSERT INTO tb2001 (c1,c2)
SELECT1,1from information_schema.columns;
INSERT INTO tb2001 (c1,c2)
SELECT2,2from information_schema.columns
limit10;
INSERT INTO tb2001 (c1,c2)
SELECT 3,3from information_schema.columns
limit 10;
表中tb2001上C1列上有索引,全表数据10万条,但c1<>1的数据仅为20条。
查询c1不为1的10条数据,NOT IN 方式执行计划为:
desc select * from tb2001 where c1 not in(1) limit 10\G*************************** 1. row ***************************id:1select_type: SIMPLE
table: tb2001
partitions: NULL
type: range
possible_keys: idx_c1
key: idx_c1
key_len:5ref: NULL
rows:11filtered:100.00Extra: Using index condition
查询c1不为1的10条数据,<>方式执行计划为:
desc select * from tb2001 where c1 <> 1 limit 10\G*************************** 1. row ***************************id:1select_type: SIMPLE
table: tb2001
partitions: NULL
type: range
possible_keys: idx_c1
key: idx_c1
key_len:5ref: NULL
rows:11filtered:100.00Extra: Using index condition
从上面两个例子可以看出,NOT INT和<>操作都可以走索引,且执行性能极佳。
俗话说苍蝇不叮无缝的丹,因为在很多场景下,NOT IN或<>两类操作使用二级索引的成本远超于全表扫描的成本,查询优化器按照成本选择"最优执行计划",导致查询不走二级索引。但不能因此就彻底判断NOT IN或<>两类操作不能走索引。