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

原创已于 2024-03-06 14:50:12 修改 · 599 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

于 2020-09-14 16:15:28 首次发布

mysql 专栏收录该内容

2 篇文章

订阅专栏

探讨MySQL中IN关键字在不同场景下对索引的影响，包括固定值与子查询的区别，以及如何通过JOIN优化查询性能。

问题

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        |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+