MySQL教程100-索引在什么情况下不会被使用?
索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况。
1. 查询语句中使用LIKE关键字
在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。
例 1
为了便于理解，我们先查询 tb_student 表中的数据，SQL 语句和运行结果如下：
mysql> select * from tb_student_info;
+----+--------+--------+------+------+--------+------------+
| id | name | deptId | age | sex | height | login_date |
+----+--------+--------+------+------+--------+------------+
| 1 | Dany | 1 | 25 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 66 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
| 12 | jijiji | 1 | 667 | 1 | 176 | 2020-08-24 |
+----+--------+--------+------+------+--------+------------+
11 rows in set (0.00 sec)
下面在查询语句中使用 LIKE 关键字，且匹配的字符串中含有“%”符号，使用 EXPLAIN 分析查询情况，SQL 语句和运行结果如下：
mysql> explain select * from tb_student_info where name like '%y'; +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_student_info | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 11.11 | Using where | +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> alter table tb_student_info add index index_name (name); Query OK, 0 rows affected (0.51 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from tb_student_info where name like '%y'; +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_student_info | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 11.11 | Using where | +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from tb_student_info where name like 'y%'; +----+-------------+-----------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | tb_student_info | NULL | range | index_name | index_name | 77 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.07 sec)
第一个查询语句执行后，rows 参数的值为 12，表示这次查询过程中查询了 12 条记录；创建索引之后第二个查询语句执行后，rows 参数的值为 12，表示这次查询过程只查询 12 条记录。第三次查询同样是使用 name 字段进行查询，因为第一个和第二个查询语句的 LIKE 关键字后的字符串是以“%”开头的，所以第二个查询语句没有使用索引(第一个查询的时候还没建立索引)，而第三个查询语句使用了索引 index_name。
注意: 关于explain的使用, 可以查看这里
2. 查询语句中使用多列索引
多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。
例 2
在 name 和 age 两个字段上创建多列索引，并验证多列索引的使用情况，SQL 语句和运行结果如下：
mysql> alter table tb_student_info add index index_name_age (name, age); Query OK, 0 rows affected (0.56 sec) Records: 0 Duplicates: 0 Warnings: 0
删除之前name列上的索引'index_name'
mysql> drop index index_name on tb_student_info; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0
查看现在表tb_student_info上的索引
mysql> show index from tb_student_info\G; *************************** 1. row *************************** Table: tb_student_info Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 13 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: tb_student_info Non_unique: 1 Key_name: index_name_age Seq_in_index: 1 Column_name: name Collation: A Cardinality: 11 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 3. row *************************** Table: tb_student_info Non_unique: 1 Key_name: index_name_age Seq_in_index: 2 Column_name: age Collation: A Cardinality: 11 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 3 rows in set (0.00 sec)
如上, 一个主键, 还有一个是组合索引, 组合字段是name和age...
执行分别查询name和age的sql, 如下:
mysql> explain select * from tb_student_info where name like 'y%'; +----+-------------+-----------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | tb_student_info | NULL | range | index_name_age | index_name_age | 77 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from tb_student_info where age = 12; +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_student_info | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 10.00 | Using where | +----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
第一条查询语句的查询条件使用了 name 字段，分析结果显示 rows 参数的值为 1，且查询过程中使用了 index_name_age 索引。第二条查询语句的查询条件使用了 age 字段，结果显示 rows 参数的值为 12，且 key 参数的值为 NULL，这说明第二个查询语句没有使用索引。
因为 name 字段是多列索引的第一个字段，所以只有查询条件中使用了 name 字段才会使 index_name_age 索引起作用。
mysql> explain select * from tb_student_info where age=23 and name = 'Green'; +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | tb_student_info | NULL | ref | index_name_age | index_name_age | 82 | const,const | 1 | 100.00 | NULL | +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from tb_student_info where name = 'Green' and age = 23; +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | tb_student_info | NULL | ref | index_name_age | index_name_age | 82 | const,const | 1 | 100.00 | NULL | +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
如上, 查询中的where条件中用到了name和age两列, 可以看出, 不管where条件中先写age还是name都对使用索引index_name_age没有影响, 因为两个sql中的where条件中都有name列, 根据索引index_name_age的第一个字段就是name, 所以索引被使用
mysql> explain select * from tb_student_info where name = 'Green' and sex = 'F'; +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | tb_student_info | NULL | ref | index_name_age | index_name_age | 77 | const | 1 | 10.00 | Using where | +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
where条件中使用了name和sex, 依然使用了索引, 因为where条件中的name得以使用索引...
3. 查询语句中使用OR关键字
查询语句只有 OR 关键字时，如果 OR 前后的两个条件的列都是索引，那么查询中将使用索引。如果 OR 前后有一个条件的列不是索引，那么查询中将不使用索引。
例 3
下面演示 OR 关键字的使用。
mysql> explain select * from tb_student_info where name='Green' or age>1; +----+-------------+-----------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_student_info | NULL | ALL | index_name_age | NULL | NULL | NULL | 12 | 40.00 | Using where | +----+-------------+-----------------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from tb_student_info where name='Green' or (name='Green' and age=23); +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tb_student_info | NULL | ref | index_name_age | index_name_age | 77 | const | 1 | 100.00 | NULL | +----+-------------+-----------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from tb_student_info where name='Green' or id=1; +----+-------------+-----------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------+ | 1 | SIMPLE | tb_student_info | NULL | index_merge | PRIMARY,index_name_age | index_name_age,PRIMARY | 77,4 | NULL | 2 | 100.00 | Using sort_union(index_name_age,PRIMARY); Using where | +----+-------------+-----------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
由于 单独的age字段没有索引，所以第一条查询语句没有使用索引, 因为or后面的age单独使用, 没有涉及到索引index_name_age第一个字段name的使用；第二个查询or前面的name和or后面的都设计到了索引index_name_age, 所以使用了此索引; 而最后一个查询name 字段和 id 字段都有索引, 使用了 index_name_age 和 PRIMARY 索引
其实综上一定要记住, 组合索引的使用, 一定要遵循最左匹配原则!
总结
使用索引查询记录时，一定要注意索引的使用情况。例如，LIKE 关键字配置的字符串不能以“%”开头；使用多列索引时，查询条件必须要使用这个索引的第一个字段；使用 OR 关键字时，OR 关键字连接的所有条件都必须使用索引。
分类:
MySQL教程
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· 没有源码，如何修改代码逻辑？
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）