mysql查询到底用了组合索引的哪些字段

工作中，我们常常使用到mysql的组合索引，但怎样判断mysql查询时，使用了组合索引的那些字段呢？
最近从我司DBA中学了一招，就是结合explain的key_len(索引长度字节数)和组合索引索引的字段长度来判断。

首先，我定义一个任务表

create table `my_task`(
`id` bigint unsigned not null auto_increment comment '任务id',
`task_type` varchar(32) not null comment '任务类型 1-待执行，2-执行中 3-执行成功 4-执行失败',
`server_id` char(16) comment '处理节点ip',
`process_status` int unsigned not null comment '处理状态',
`process_time` timestamp not null comment '处理时间',
`create_time`  timestamp default current_timestamp comment '创建时间',
`update_time`  timestamp default current_timestamp on update current_timestamp  comment '更新时间',
primary key (`id`),
key `key_task_type` (`task_type`) ,
key `key_server_id` (`server_id`),
key `key_process_status`(`process_status`),
key `key_process_time` (`process_time`),
key `key_multiple` (`task_type`,`server_id`, `process_status`, `process_time`)
) engine=innodb default charset=utf8;

我定义了很多索引，方便后面实例分析。

1. 找出所有的下载任务：

mysql> explain select * from my_task where task_type = 'download';
+----+-------------+---------+------------+------+----------------------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys              | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | my_task | NULL       | ref  | key_task_type,key_multiple | key_task_type | 98      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

使用了key_task_type索引，该索引建立在task_type varchar(32) 字段上。
varchar(32)表示32个字符，utf8编码下一个字符有3个字节，另外varchar还需要两个字节作为长度标示，所以explain的key_len为32*3 + 2 = 98。

2. 找出本地执行的任务：

mysql> explain select * from my_task where server_id = '127.0.0.1';
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | my_task | NULL       | ref  | key_server_id | key_server_id | 49      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

使用了key_server_id 索引，对应于server_id char(16)字段，另外，server_id允许为null，所以还需要一个字节表示是否为null，所以explain的key_len为16*3+1=49

3. 找出待执行的任务：

mysql> explain select * from my_task  where process_status = 1;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | my_task | NULL       | ref  | key_process_status | key_process_status | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

比较简单，使用了key_process_status索引，对应的process_status int 为4个字节。

4. 找出2018-08-09后执行的任务：

mysql> explain select * from my_task where process_time >= '2018-08-09';
+----+-------------+---------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | my_task | NULL       | range | key_process_time | key_process_time | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

简单，使用了key_process_time 索引，对应process_time timestamp字段也是4个字节。

5. 找出2018-08-09后本机执行的下载任务：

mysql> explain select * from my_task force  index(key_multiple) where task_type = 'download' and server_id = '127.0.0.1' and process_time >= '2018-08-09';
+----+-------------+---------+------------+------+---------------+--------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | my_task | NULL       | ref  | key_multiple  | key_multiple | 147     | const,const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

为了方便分析，这里使用了force index强制使用key_multiple(task_type,server_id, process_status, process_time)索引。
key_len为147，等于(32*3+2) + (16*3+1)，而32*3+2为task_type字段长度，16*3+1为server_id字段长度，可以看出，查询条件process_time 无法使用索引，这也是符合最左匹配原则的。

6. 找出2018-08-09后本机执行成功或者失败的下载任务：

mysql> explain select * from my_task force  index(key_multiple) where task_type = 'download' and server_id = '127.0.0.1' and process_status in (3,4) and  process_time >= '2018-08-09';
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | my_task | NULL       | range | key_multiple  | key_multiple | 155     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

key_len为155，等于(32*3+2) + (16*3+1) + 4 + 4，可以看到该查询使用了key_multiple的所有字段。
看来索引中间字段(process_status)使用了in条件，也不妨碍后面字段(process_time)使用索引。

7. 找出2018-08-09后被本机执行的下载任务：

mysql> explain select * from my_task force  index(key_multiple) where task_type = 'download' and server_id = '127.0.0.1' and process_status > 1 and  process_time >= '2018-08-09';
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | my_task | NULL       | range | key_multiple  | key_multiple | 151     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

key_len为151，等于(32*3+2) + (16*3+1) + 4，这里process_status > 1使用了范围查询，所以后面的字段process_time不能使用索引了。但process_status是可以使用索引的。

8. 找出2018-08-09后处理节点不为null的待处理下载任务（这种是异常任务）：

mysql> explain select * from my_task force  index(key_multiple) where task_type = 'download' and server_id is null and process_status = 1 and  process_time >= '2018-08-09';
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | my_task | NULL       | range | key_multiple  | key_multiple | 155     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

key_len为155，就是key_multiple所有字段都使用了索引，看来，查询为null的server_id字段不会影响后面字段使用索引，只是索引需要使用一个额外字节标示字段是否为null。

很实用的技巧，感谢我司DBA。

参考：
B+树实现范围查询
MYSQL EXPLAIN 中的KEY_LEN的说明
8.8.2 EXPLAIN Output Format
11.4.1 The CHAR and VARCHAR Types