MySQL NOT IN走索引吗|极客笔记

MySQL NOT IN走索引吗

在使用MySQL进行查询操作时，我们常常会用到NOT IN语句来排除某些特定的值。然而，有一些开发者会对NOT IN语句是否会走索引产生疑问。本文将详细解释MySQL中NOT IN语句的走索引情况，并给出示例代码及运行结果加以说明。

NOT IN语句简介

NOT IN语句可用于排除查询结果中包含特定值的行。它的基本语法如下：

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

其中，column_name表示要查询的列名，table_name表示要查询的表名，value1、value2等表示要排除的特定值。

NOT IN语句是否走索引

常见的索引类型有B-Tree索引、Hash索引、Full-text索引等。在MySQL中，NOT IN语句通常不会走索引，主要有以下原因：

查询优化器的判断：MySQL的查询优化器会根据索引的选择性和查询条件的唯一性来判断是否使用索引。而NOT IN语句的查询条件通常是多个不同的值，索引的效果非常有限。
索引范围扫描：当使用NOT IN语句时，MySQL会进行索引范围扫描，遍历索引中的每一条记录来判断是否满足查询条件。这种方式比较耗时，尤其在数据量较大的情况下，性能会受到严重影响。

示例代码及运行结果

下面给出5个示例代码，用于演示NOT IN语句的走索引情况。

示例1

-- 创建测试表
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- 插入测试数据
INSERT INTO students VALUES (1, 'Alice');
INSERT INTO students VALUES (2, 'Bob');
INSERT INTO students VALUES (3, 'Charlie');
INSERT INTO students VALUES (4, 'David');

-- 查询数据
EXPLAIN SELECT * FROM students WHERE id NOT IN (1, 3);

运行结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	students	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where

从结果中可以看出，查询优化器判断使用了全表扫描（ALL）而不是索引。

示例2

-- 创建测试表
CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_no INT,
  status VARCHAR(20)
);

-- 插入测试数据
INSERT INTO orders VALUES (1, 1001, 'Pending');
INSERT INTO orders VALUES (2, 1002, 'Completed');
INSERT INTO orders VALUES (3, 1003, 'Canceled');
INSERT INTO orders VALUES (4, 1004, 'Pending');

-- 查询数据
EXPLAIN SELECT * FROM orders WHERE status NOT IN ('Canceled');

运行结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	NULL	ALL	NULL	NULL	NULL	NULL	4	75.00	Using where

同样地，查询优化器选择了全表扫描（ALL）而不是索引。

示例3

-- 创建测试表
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10, 2)
);

-- 插入测试数据
INSERT INTO products VALUES (1, 'Apple', 1.99);
INSERT INTO products VALUES (2, 'Orange', 2.99);
INSERT INTO products VALUES (3, 'Banana', 0.99);
INSERT INTO products VALUES (4, 'Grape', 4.99);

-- 查询数据
EXPLAIN SELECT * FROM products WHERE price NOT IN (1.99, 2.99);

运行结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	products	NULL	ALL	NULL	NULL	NULL	NULL	4	50.00	Using where

查询优化器仍然选择了全表扫描（ALL）。

示例4

-- 创建测试表
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50)
);

-- 插入测试数据
INSERT INTO employees VALUES (1, 'Alice', 'HR');
INSERT INTO employees VALUES (2, 'Bob', 'Finance');
INSERT INTO employees VALUES (3, 'Charlie', 'IT');
INSERT INTO employees VALUES (4, 'David', 'Sales');

-- 查询数据
EXPLAIN SELECT * FROM employees WHERE department NOT IN ('HR', 'IT');

运行结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	ALL	NULL	NULL	NULL	NULL	4	50.00	Using where

同样地，全表扫描（ALL）是查询优化器的选择。

示例5

-- 创建测试表
CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(50)
);

-- 插入测试数据
INSERT INTO books VALUES (1, 'Book A', 'Author X');
INSERT INTO books VALUES (2, 'Book B', 'Author Y');
INSERT INTO books VALUES (3, 'Book C', 'Author Z');
INSERT INTO books VALUES (4, 'Book D', 'Author X');

-- 创建索引
CREATE INDEX idx_author ON books (author);

-- 查询数据
EXPLAIN SELECT * FROM books WHERE author NOT IN ('Author X', 'Author Z');

运行结果：