mysql使用关键字in子查询不走索引的优化

问题

mysql查询当使用in关键字时, 条件里面为固定值, 则走索引, 若是子查询则不走索引

测试表

create Table test (
		`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
		`col` varchar(20) default null COMMENT 'test',
		PRIMARY KEY (`id`)
		)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test';

复现

1. 全量查询
   此处不走索引, 全表扫描

   mysql> explain select * from test;
   +----+-------------+-------+------+---------------+------+---------+------+------+-------+
   | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
   +----+-------------+-------+------+---------------+------+---------+------+------+-------+
   |  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
   +----+-------------+-------+------+---------------+------+---------+------+------+-------+
   

2. 加in关键字

   mysql> explain select * from test where id in (1,2,3);
   +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
   | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
   +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
   |  1 | SIMPLE      | test  | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 | Using where |
   +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
   

3. 将固定值换为子查询
   可以看出这个时候已经是全表扫描了

   mysql> explain select * from test where id in (select id from test);
   +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
   | id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | Extra       |
   +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
   |  1 | SIMPLE      | test  | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    1 | NULL        |
   |  1 | SIMPLE      | test  | eq_ref | PRIMARY       | PRIMARY | 4       | test.test.id |    1 | Using index |
   +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
   

定位

网上搜索出来的结果为在Mysql在处理子查询的时候，会将子查询改写，Mysql将会扫描外查询中的所有数据，每条数据都将会传到子查询中进行关联，子查询不能首先被执行，如果外表很大的话，那么性能上将会出现问题。

解决

使用join, 用小表驱动大表即可

mysql> explain select b.* from test a inner join test b on a.id=b.id;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
|  1 | SIMPLE      | a     | index  | PRIMARY       | PRIMARY | 4       | NULL      |    1 | Using index |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+