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