author 曹扬进
createTime 2022-05-16
2022-05-19: 培训结束
# MYSQL 索引
# 简介
Mysql 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
# 索引类型
MySQL索引种类 :
主键索引 索引列中的值必须是唯一的，不允许有空值。
普通索引 MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
唯一索引 索引列中的值必须是唯一的，但是允许为空值。
全文索引 只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。
空间索引 MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
前缀索引 在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
其他（按照索引列数量分类）
- 单列索引
- 组合索引
组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。
# 索引的优势和劣势
优势：
可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
劣势：
索引会占据磁盘空间
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
# SQL优化
# explain
explain这个命令来查看一个这些SQL语句的执行计划，就是为了分析耗时，是否走索引
查看SQL是否使用索引，前面加上explain即可
创建一张学生表不添加索引
create table student(
id int(11) not null auto_increment,
stuno varchar(20) not null ,
name varchar(20) default null,
age int(3) default null,
classID int(11) default null,
primary key (id)
)engine=innodb auto_increment=1 default charset=utf8;
查看表结构
mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| stuno | varchar(20) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| classID | int | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
插入一些测试数据后 用explain 查看语句的执行情况
mysql> EXPLAIN SELECT * FROM student WHERE stuno = 10000;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13617371 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
explain返回了10列
id:选择标识符select_type:表示查询的类型。table:输出结果集的表partitions:匹配的分区type:表示表的连接类型possible_keys:表示查询时，可能使用的索引key:表示实际使用的索引key_len:索引字段的长度ref:列与索引的比较rows:扫描出的行数(估算的行数)filtered:按表条件过滤的行百分比Extra:执行情况的描述和说明
type表示表的连接类型，由上至下，效率越来越高
ALL: 全表扫描index: 索引全扫描range: 索引范围扫描，常用语<,<=,>=,between,in等操作ref使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中eq_ref: 类似ref，区别在于使用的是唯一索引，使用主键的关联查询const/system: 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询null:MySQL不访问任何表或索引，直接返回结果
Extra:执行情况的描述和说明
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化Using index：表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
# show profile
show profile展示sql语句各个阶段的执行时间从而清楚sql慢的原因
先开启
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
执行sql
mysql> SELECT * FROM student WHERE stuno = 10000;
+---------+-------+--------+------+---------+
| id | stuno | name | age | classID |
+---------+-------+--------+------+---------+
| 9999 | 10000 | JhQEBU | 88 | 644 |
| 4009999 | 10000 | OVlsfA | 26 | 378 |
+---------+-------+--------+------+---------+
2 rows in set (4.98 sec)
执行 show profile
mysql> show PROFILES ;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 4.97527475 | SELECT * FROM student WHERE stuno = 10000 |
+----------+------------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
执行 show profile for query #{id}
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000046 |
| Executing hook on transaction | 0.000003 |
| starting | 0.000005 |
| checking permissions | 0.000004 |
| Opening tables | 0.000026 |
| init | 0.000004 |
| System lock | 0.000006 |
| optimizing | 0.000007 |
| statistics | 0.000014 |
| preparing | 0.000013 |
| executing | 4.975004 |
| end | 0.000013 |
| query end | 0.000003 |
| waiting for handler commit | 0.000007 |
| closing tables | 0.000007 |
| freeing items | 0.000075 |
| logging slow query | 0.000029 |
| cleaning up | 0.000011 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.00 sec)
# 索引失效
即使我们给数据表添加了索引，查询时也可能出现索引不生效的情况。
# 准备工作
给student表添加联合索引 和普通索引
mysql> ALTER TABLE student ADD INDEX idx_stuno_name_age(`stuno`,`name`,`age`);
Query OK, 0 rows affected (1 min 32.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE student ADD INDEX idx_class(`classID`);
Query OK, 0 rows affected (1 min 13.81 sec)
Records: 0 Duplicates: 0 Warnings: 0
现在student 表中有三个索引 主键 id,联合索引 idx_stuno_name_age, 普通索引 idx_class。
目前数据表中含有13617371条数据。
# 联合索引
联合索引的最左匹配原则：使用联合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。
可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。
# 联合索引生效的情况
mysql> EXPLAIN SELECT * from student WHERE stuno = 5955539 and name = 'gsvaRf' and age = 15;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_stuno_name_age | idx_stuno_name_age | 72 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * from student WHERE stuno = 5955539 and name = 'gsvaRf';
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_stuno_name_age | idx_stuno_name_age | 67 | const,const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * from student WHERE stuno = 5955539;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_stuno_name_age | idx_stuno_name_age | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
上面查询条件的顺序 stuno name age均符合上面最左匹配原则,看explain返回的type和key可以上述查询都走了索引。
mysql> EXPLAIN SELECT * from student WHERE stuno = 5955539 and age = 15;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | ref | idx_stuno_name_age | idx_stuno_name_age | 4 | const | 1 | 10.00 | Using index condition |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
上面语句 查询条件顺序为 stuno age ,name字段缺失了，可以看到还是走了索引，说明只有有联合索引第一个（最左边）的字段，sql就可以走索引。
# 联合索引失效的情况
mysql> EXPLAIN SELECT * from student WHERE name = 'gsvaRf' and age = 15;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13617371 | 1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * from student WHERE age = 15;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13617371 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * from student WHERE name = 'gsvaRf';
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13617371 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
上面查询条件的顺序 stuno name age均不符合上面最左匹配原则,看explain返回的type和key可以上述查询都没走索引。
# select *
在《阿里巴巴开发手册》中明确说过，查询sql中禁止使用select *
mysql> EXPLAIN SELECT * from student WHERE name = 'gsvaRf';
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13617371 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
看explain返回的type和key可以上述查询走了全表查询没有用到任何索引，查询效率低下。
如果查询的时候只查需要的列
mysql> EXPLAIN SELECT stuno,age from student WHERE name = 'gsvaRf';
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+----------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+----------+----------+--------------------------+
| 1 | SIMPLE | student | NULL | index | idx_stuno_name_age | idx_stuno_name_age | 72 | NULL | 13617371 | 10.00 | Using where; Using index |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+----------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到走了索引全扫描，比全表扫描效率要高。
其实这里用到了：覆盖索引
如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。
而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。
# 索引列上有计算
mysql> EXPLAIN SELECT * from student WHERE id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)
上面查询用到主键索引
mysql> EXPLAIN SELECT * from student WHERE id + 1 = 2;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13617371 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
在索引列加了计算以后主键索引失效了。
# 索引列用到函数
mysql> EXPLAIN SELECT * from student WHERE classId = 2;
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_class | idx_class | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
上述语句用到了普通索引
mysql> EXPLAIN SELECT * from student WHERE abs(classId) = 2;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13617371 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
在索引列添加函数后就变成走全表扫描，索引失效。
# like左边包含%
模糊查询的时候会经常用到like实现，在使用不当时将使得索引失效，导致查询缓慢。
mysql> EXPLAIN SELECT * from student WHERE stuno like '100%';
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+-------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+-------+----------+----------------------------------+
| 1 | SIMPLE | student | NULL | range | idx_stuno_name_age | idx_stuno_name_age | 62 | NULL | 41440 | 100.00 | Using index condition; Using MRR |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.04 sec)
mysql> EXPLAIN SELECT * from student WHERE stuno like '%100';
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13644476 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * from student WHERE stuno like '%100%';
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13644476 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到 like '100%' 查询才能走索引，like '%100' 和 like '%100%'均变成全表扫描
当like语句中的%，出现在查询条件的左边时，索引会失效。
# 列对比
mysql> EXPLAIN SELECT * from student WHERE id = classId;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 13644476 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
将两个单独建了索引的列，用来做对比时索引会失效。
# 使用or关键字
mysql> EXPLAIN SELECT * from student WHERE id = 1 or classId = 123;
+----+-------------+---------+------------+-------------+-------------------+-------------------+---------+------+-------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+-------------------+-------------------+---------+------+-------+----------+---------------------------------------------+
| 1 | SIMPLE | student | NULL | index_merge | PRIMARY,idx_class | PRIMARY,idx_class | 4,5 | NULL | 25267 | 100.00 | Using union(PRIMARY,idx_class); Using where |
+----+-------------+---------+------------+-------------+-------------------+-------------------+---------+------+-------+----------+---------------------------------------------+
1 row in set, 1 warning (0.07 sec)
上面的查询中 or 连接的两个条件均为索引字段，结果走了索引
mysql> EXPLAIN SELECT * from student WHERE classId = 123 or age = 11;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_class | NULL | NULL | NULL | 13644476 | 10.01 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
上面的查询中classId 是索引列 但 age 不是，结果没有走索引
如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效
# not in 和 not exists
# in
mysql> EXPLAIN SELECT * from student WHERE classid in (1,2,3,4);
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_class | idx_class | 5 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
classId 为普通索引 上面 in 查询走了索引
# not in
mysql> EXPLAIN SELECT * from student WHERE classid not in (1,2,3,4);
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_class | NULL | NULL | NULL | 13644476 | 50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.12 sec)
classId 为普通索引 上面 not in 查询没走索引
mysql> EXPLAIN SELECT * from student WHERE id not in (1,2,3,4);
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | student | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 6822242 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.06 sec)
id 为主键 上面 not in 查询还是走了索引
主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。
# exists
mysql> EXPLAIN SELECT * from student t1 WHERE exists (select 1 from student t2 where t2.classId = 3 and t1.id = t2.id );
+----+-------------+-------+------------+--------+-------------------+-----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------+-----------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | ref | PRIMARY,idx_class | idx_class | 5 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+-------------------+-----------+---------+------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
t1,t2均走了索引
# not exists
mysql> EXPLAIN SELECT * from student t1 WHERE not exists (select 1 from student t2 where t2.classId = 3 and t1.id = t2.id );
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+----------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+----------+----------+-------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 13644476 | 100.00 | NULL |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | test.t1.id | 1 | 100.00 | Using where; Not exists |
| 2 | MATERIALIZED | t2 | NULL | ref | PRIMARY,idx_class | idx_class | 5 | const | 1050 | 100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+----------+----------+-------------------------+
3 rows in set, 2 warnings (0.00 sec)
可以看到sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引