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

2018-12-23 17:17:44

工作中,我们常常使用到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