问题
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';
复现
- 全量查询
此处不走索引, 全表扫描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 | +----+-------------+-------+------+---------------+------+---------+------+------+-------+
- 加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 | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
- 将固定值换为子查询
可以看出这个时候已经是全表扫描了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 |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+