如何判断一条 SQL 是否走索引？
表结构和数据
表结构：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+树全树扫描一遍