如何判断一条 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

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)