Mysql索引跳跃扫描

在前面的复合索引文章里讲到过，复合索引要满足最左前缀匹配原则，即不能跳过索引的前导列而单独使用索引的后面的列。

如果有查询复合索引(a,b)，查询为select * from t where b=xx，此查询是否一定不能走索引呢？

表结构如下：

mysql> desc emps;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| emp_no     | int           | NO   |     | NULL    |                |
| birth_date | date          | NO   |     | NULL    |                |
| first_name | varchar(14)   | NO   | MUL | NULL    |                |
| last_name  | varchar(16)   | NO   | MUL | NULL    |                |
| gender     | enum('m','f') | NO   |     | NULL    |                |
| hire_date  | date          | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

在(gender,first_name)建组合索引

mysql> create index gf_ck on emps(gender,first_name);
Query OK, 0 rows affected (2.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from emps;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emps  |          0 | PRIMARY  |            1 | id          | A         |      299150 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emps  |          1 | gf_ck    |            1 | gender      | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emps  |          1 | gf_ck    |            2 | first_name  | A         |        2476 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

查看select gender,first_name from emps where first_name=‘Anneke’;的执行计划

mysql> desc select gender,first_name from emps where first_name='Anneke';
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+----------------------------------------+
|  1 | SIMPLE      | emps  | NULL       | range | gf_ck         | gf_ck | 59      | NULL | 29915 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

看到查询走了复合索引。这个查询违反了上面所说的最左前缀原则确走了索引，是不是最左前缀原则是错的呢？答案是否定的。

MySQL中有一种叫做索引跳跃扫描的优化，当查询条件中跳过了前导列，使用了后面的列时，如果跳过的前导列的唯一值不多时，查询是可以走复合索引的。如上面的示例。

索引跳跃扫描原理:

1. 将查询按照前导列拆分成多个select组，让后将每个组的where条件中加上跳过的前导列=值条件，上面的示例会拆成如下

select gender,first_name from emps where gender='F' and first_name='Anneke';
union all
select gender,first_name from emps where gender='M'  first_name='Anneke';

2. 使用union all将所有拆分的select组的结果集联合起来，最后将整个联合的结果集作为最终的结果集返回给客户端。

总结：

索引跳跃扫描并没有违反复合索引的最左前缀原则，只是优化器使用了一种巧妙的方式让用户误以为跳过了前导列。能使用索引跳跃扫描的前提是跳过的前导列唯一值不多。如上面的示例查询gender列只有’F’和’M’两个值。