“如何判断一条 SQL 是否走索引？”的版本间的差异

来自姬鸿昌的知识库
跳到导航 跳到搜索
（建立内容为“https://www.bilibili.com/video/BV1ya41157oE?spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=66ca2a18f4b76100f6e90bdb5950f9f6 *表结构…”的新页面）
 
 
（未显示同一用户的4个中间版本）
第1行： 第1行：
 
https://www.bilibili.com/video/BV1ya41157oE?spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=66ca2a18f4b76100f6e90bdb5950f9f6
 
https://www.bilibili.com/video/BV1ya41157oE?spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=66ca2a18f4b76100f6e90bdb5950f9f6
  
−
*表结构和数据
+
===表结构和数据===
 
表结构：id, a, b, c, d 5个字段
 
表结构：id, a, b, c, d 5个字段
  
−
索引：id，idx_a_b_c
+
索引：id，idx_a_b_c<syntaxhighlight lang="powershell">
 +
mysql> select * from t_demo;
 +
+------+------+------+------+------+
 +
| id  | a    | b    | c    | d    |
 +
+------+------+------+------+------+
 +
|    1 |    1 |    1 |    1 | NULL |
 +
|    5 |    5 |    5 |    5 | NULL |
 +
|  10 |  10 |  10 |  10 | NULL |
 +
+------+------+------+------+------+
 +
3 rows in set (0.00 sec)
 +
</syntaxhighlight>
 +
 
 +
 
 +
=== 等值匹配原则 ===
 +
<syntaxhighlight lang="sql">
 +
select * from t_demo
 +
where a = 1
 +
  and b = 1
 +
  and c = 1;
 +
</syntaxhighlight><syntaxhighlight lang="powershell">
 +
mysql> explain select * from t_demo where a = 1 and b = 1 and c = 1;
 +
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
 +
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref              | rows | filtered | Extra |
 +
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
 +
|  1 | SIMPLE      | t_demo | NULL      | ref  | idx_a_b_c    | idx_a_b_c | 15      | const,const,const |    1 |  100.00 | NULL  |
 +
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>a、b、c 同时存在的情况下，只要是等值判断，即便调换 where 后面的先后顺序，也仍然会命中索引。
 +
 
 +
 
 +
=== 最左侧匹配 ===
 +
<syntaxhighlight lang="sql">
 +
select * from t_demo where a = 1 and b = 1; /*可以命中索引*/
 +
select * from t_demo where b = 1; /*不能命中索引*/
 +
</syntaxhighlight><syntaxhighlight lang="powershell">
 +
mysql> explain select * from t_demo where a = 1 and b = 1;
 +
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
 +
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra |
 +
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
 +
|  1 | SIMPLE      | t_demo | NULL      | ref  | idx_a_b_c    | idx_a_b_c | 10      | const,const |    1 |  100.00 | NULL  |
 +
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
 +
1 row in set, 1 warning (0.00 sec)
 +
 
 +
mysql> explain select * from t_demo where b = 1;
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
|  1 | SIMPLE      | t_demo | NULL      | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>因为索引在B+树中是按照字段顺序创建的，索引 idx_a_b_c 顺序是 a、b、c，所以条件 "where a = 1 and b = 1" 可以命中索引，条件 "where b = 1" 不能命中索引
 +
 
 +
 
 +
=== 最左前缀匹配规则 ===
 +
<syntaxhighlight lang="sql">
 +
select * from t_demo where a like '1%'; /*可以命中索引*/
 +
select * from t_demo where a like '%1%'; /*不能命中索引*/
 +
</syntaxhighlight><syntaxhighlight lang="powershell">
 +
mysql> explain select * from t_demo where a like '1%';
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
|  1 | SIMPLE      | t_demo | NULL      | ALL  | idx_a_b_c    | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
 
 +
mysql> explain select * from t_demo where a like '%1%';
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
|  1 | SIMPLE      | t_demo | NULL      | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 +
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>
 +
 
 +
=== 范围查找规则 ===
 +
<syntaxhighlight lang="sql">
 +
select * from t_demo where a > 0 and a < 5; /*可以命中索引*/
 +
 
 +
select * from t_demo where a > 0 and a < 5 and b > 0 and b < 5; /*部分命中索引，a 可以命中索引，b 不能命中索引*/
 +
</syntaxhighlight><syntaxhighlight lang="powershell">
 +
mysql> explain select * from t_demo where a > 0 and a < 5;
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
 +
| id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
 +
|  1 | SIMPLE      | t_demo | NULL      | range | idx_a_b_c    | idx_a_b_c | 5      | NULL |    1 |  100.00 | Using index condition |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
 
 +
mysql> explain select * from t_demo where a > 0 and a < 5 and b > 0 and b < 5;
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
 +
| id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
 +
|  1 | SIMPLE      | t_demo | NULL      | range | idx_a_b_c    | idx_a_b_c | 5      | NULL |    1 |    33.33 | Using index condition |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>explain 结果在此例中仅供参考。
 +
 
 +
进行范围查找时，只有联合索引最左侧的字段才能命中索引。联合索引 idx_a_b_c 最左侧的字段是 a
 +
 
 +
 
 +
=== 等值查询+范围查询 ===
 +
<syntaxhighlight lang="sql">
 +
select * from t_demo where a > 0 and a < 5 and b > 0 and b < 5; /*部分命中索引，a 可以命中索引，b 不能命中索引*/
 +
 
 +
select * from t_demo where a = 1 and b > 0 and b < 5 and c > 0 and c < 5; /*a 可以， b 可以, c 不可以*/
 +
</syntaxhighlight>等值查询+范围查询时，联合索引中，进行范围查询找最左侧的字段才能命中索引
 +
 
 +
 
 +
=== 覆盖索引 ===
 +
<syntaxhighlight lang="sql">
 +
select a, b, c from t_demo where a = 1 and b = 1; /*等值查询，覆盖索引，不用回表*/
 +
 
 +
select a, b, c from t_demo where b = 1;
 +
 
 +
select a, b, c from t_demo where a like '%1%';
 +
 
 +
select a, b, c from t_demo where b like '%1%';
 +
</syntaxhighlight><syntaxhighlight lang="powershell">
 +
mysql> explain select a, b, c from t_demo where b = 1;
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
| id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
|  1 | SIMPLE      | t_demo | NULL      | index | NULL          | idx_a_b_c | 15      | NULL |    3 |    33.33 | Using where; Using index |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
 
 +
mysql> explain select a, b, c from t_demo where a like '%1%';
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
| id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
|  1 | SIMPLE      | t_demo | NULL      | index | NULL          | idx_a_b_c | 15      | NULL |    3 |    33.33 | Using where; Using index |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
 
 +
mysql> explain select a, b, c from t_demo where b like '%1%';
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
| id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
|  1 | SIMPLE      | t_demo | NULL      | index | NULL          | idx_a_b_c | 15      | NULL |    3 |    33.33 | Using where; Using index |
 +
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>第2~4条 SQL 都会命中索引，explain 中 type 为 index 代表全索引扫描，就是要把 idx_a_b_c 的B+树全树扫描一遍

2022年8月26日 (五) 00:20的最新版本

https://www.bilibili.com/video/BV1ya41157oE?spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=66ca2a18f4b76100f6e90bdb5950f9f6

表结构和数据

表结构：id, a, b, c, d 5个字段

索引：id，idx_a_b_c

mysql> select * from t_demo;
+------+------+------+------+------+
| id   | a    | b    | c    | d    |
+------+------+------+------+------+
|    1 |    1 |    1 |    1 | NULL |
|    5 |    5 |    5 |    5 | NULL |
|   10 |   10 |   10 |   10 | NULL |
+------+------+------+------+------+
3 rows in set (0.00 sec)


等值匹配原则

select * from t_demo 
where a = 1 
  and b = 1
  and c = 1;

mysql> explain select * from t_demo where a = 1 and b = 1 and c = 1;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 15      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

a、b、c 同时存在的情况下，只要是等值判断，即便调换 where 后面的先后顺序，也仍然会命中索引。


最左侧匹配

select * from t_demo where a = 1 and b = 1; /*可以命中索引*/
select * from t_demo where b = 1; /*不能命中索引*/

mysql> explain select * from t_demo where a = 1 and b = 1;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 10      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where b = 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

因为索引在B+树中是按照字段顺序创建的，索引 idx_a_b_c 顺序是 a、b、c，所以条件 "where a = 1 and b = 1" 可以命中索引，条件 "where b = 1" 不能命中索引


最左前缀匹配规则

select * from t_demo where a like '1%'; /*可以命中索引*/
select * from t_demo where a like '%1%'; /*不能命中索引*/

mysql> explain select * from t_demo where a like '1%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | idx_a_b_c     | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where a like '%1%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

范围查找规则

select * from t_demo where a > 0 and a < 5; /*可以命中索引*/

select * from t_demo where a > 0 and a < 5 and b > 0 and b < 5; /*部分命中索引，a 可以命中索引，b 不能命中索引*/

mysql> explain select * from t_demo where a > 0 and a < 5;
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_demo | NULL       | range | idx_a_b_c     | idx_a_b_c | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where a > 0 and a < 5 and b > 0 and b < 5;
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_demo | NULL       | range | idx_a_b_c     | idx_a_b_c | 5       | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

explain 结果在此例中仅供参考。

进行范围查找时，只有联合索引最左侧的字段才能命中索引。联合索引 idx_a_b_c 最左侧的字段是 a


等值查询+范围查询

select * from t_demo where a > 0 and a < 5 and b > 0 and b < 5; /*部分命中索引，a 可以命中索引，b 不能命中索引*/

select * from t_demo where a = 1 and b > 0 and b < 5 and c > 0 and c < 5; /*a 可以， b 可以, c 不可以*/

等值查询+范围查询时，联合索引中，进行范围查询找最左侧的字段才能命中索引


覆盖索引

select a, b, c from t_demo where a = 1 and b = 1; /*等值查询，覆盖索引，不用回表*/

select a, b, c from t_demo where b = 1; 

select a, b, c from t_demo where a like '%1%';

select a, b, c from t_demo where b like '%1%';

mysql> explain select a, b, c from t_demo where b = 1;
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_demo | NULL       | index | NULL          | idx_a_b_c | 15      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select a, b, c from t_demo where a like '%1%';
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_demo | NULL       | index | NULL          | idx_a_b_c | 15      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select a, b, c from t_demo where b like '%1%';
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_demo | NULL       | index | NULL          | idx_a_b_c | 15      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

第2~4条 SQL 都会命中索引，explain 中 type 为 index 代表全索引扫描，就是要把 idx_a_b_c 的B+树全树扫描一遍

取自“https://www.jihongchang.top/index.php?title=如何判断一条_SQL_是否走索引？&oldid=1124”