查看“如何判断一条 SQL 是否走索引？”的源代码

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<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+树全树扫描一遍