MySQL-5.7在多列索引 in条件查询的优化

场景

多列索引where in SQL如下：

SELECT * FROM `entry_device` WHERE (`entry_no`, `device_udid`) IN (('464580908742086656', 'ffffffffdeaea5fcffffffff96fddfcf')), ('464109860674342912', 'acbf32266351de103bd80bc230882b4c783cb79d'));

线上版本为5.6.23
查询计划如下：

mysql> explain extended SELECT * FROM `entry_device` WHERE (`entry_no`, `device_udid`) IN (('464580908742086656', 'ffffffffdeaea5fcffffffff96fddfcf')), ('464109860674342912', 'acbf32266351de103bd80bc230882b4c783cb79d'));
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | entry_device | ALL  | NULL          | NULL | NULL    | NULL | 9763 |   100.00 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+

可以看到是个全表扫描，由于线上数据了比较大，一条sql需要60秒的时间。

对比5.7

5.7给了不一样的查询计划，并且结果秒出，这本不是个复杂的查询，简单的不能再简单，基于联合索引的in条件查询。

5.7的查询计划如下：

mysql> explain SELECT * FROM `entry_device` WHERE (`entry_no`, `device_udid`) IN (('464580908742086656', 'ffffffffdeaea5fcffffffff96fddfcf'), ('464109860674342912', 'acbf32266351de103b0882b4c783cb79d'));
+----+-------------+--------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys              | key                        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | entry_device | NULL       | range | index_entry_no_device_udid | index_entry_no_device_udid | 262     | NULL |    2 |   100.00 | Using where |
+----+-------------+--------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use ref access on index 'index_entry_no_device_udid' due to type or collation conversion on field 'entry_no'
*************************** 2. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use ref access on index 'index_entry_no_device_udid' due to type or collation conversion on field 'entry_no'
*************************** 3. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `JDB`.`entry_device`.`id` AS `id`,`JDB`.`entry_device`.`entry_device_no` AS `entry_device_no`,`JDB`.`entry_device`.`entry_no` AS `entry_no`,`JDB`.`entry_device`.`status` AS `status`,`JDB`.`entry_device`.`device_udid` AS `device_udid`,`JDB`.`entry_device`.`device_type` AS `device_type`,`JDB`.`entry_device`.`platform` AS `platform`,`JDB`.`entry_device`.`sys_version` AS `sys_version`,`JDB`.`entry_device`.`app_channel` AS `app_channel`,`JDB`.`entry_device`.`client_version` AS `client_version`,`JDB`.`entry_device`.`create_time` AS `create_time`,`JDB`.`entry_device`.`last_time` AS `last_time` from `JDB`.`entry_device` where ((`JDB`.`entry_device`.`entry_no`,`JDB`.`entry_device`.`device_udid`) in (<cache>(('464580908742086656','ffffffffdeaea5fcffffffff96fddfcf')),<cache>(('464109860674342912','acbf32266351de103bd80bc230882b4c783cb79d'))))

分析第一阶段：
5.7采取了更好的优化器选择，于是关闭了5.7所有与5.6不同的optimizer_switch,如下：
mysql> select @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=off,firstmatch=on,duplicateweedout=off,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=off,derived_merge=off 1 row in set (0.00 sec)
然，再次查询执行计划时，依然可以用到联合索引。

第二阶段，从5.7的warning信息入手，翻阅文档

第三阶段，5.6加hint，强制索引
结果证明是无效的，5.6依然不会使用这个索引。

第四阶段
trace，这是除了去翻阅代码最后我能做的事情了，5.6的trace结果显示，在联合索引in多个条件时，根本就没有考虑任何索引的cost值，直接选择全表扫描。5.7在做了分析判断之后，选择了代价较小的联合索引。

总之5.7对于这种场景做出了自己的优化，但是到目前为止，还不清楚是如何进行优化的。
但是在5.6环境下，依然可以通过改写SQL，来利用索引加速查询。比如写成这样

mysql> select * from t1 where (id_1=1 and id_2=1) or (id_1 = 2 and id_2 =2);
+----+------+------+------+
| id | id_1 | id_2 | info |
+----+------+------+------+
|  1 |    1 |    1 | aaa  |
|  2 |    2 |    2 | aaa  |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql>  select * from t1 where (id_1,id_2) in((1,1),(2,2));
+----+------+------+------+
| id | id_1 | id_2 | info |
+----+------+------+------+
|  1 |    1 |    1 | aaa  |
|  2 |    2 |    2 | aaa  |
+----+------+------+------+
2 rows in set (0.00 sec)