首发于后端开发笔记

切换模式

MySQL慢查询优化之IN查询优化

蓝天

日常工作，学习记录

在生产环境中使用了in查询，in查询内部嵌套了子查询，in所在的键上已经创建了索引，但是在生产环境中却没有命中索引，并且该表数据量不到一百万，查询居然耗时10s以上。笔者在开发环境创建了一些表和数据并复现了慢查询(表和数据与公司业务无关)，其中tb_book_main是书籍主体表，记录了书的作者，翻译，出版社等信息，其中isbn列建有索引，tb_book_base是书籍基本信息表。

explain SELECT
	`isbn`,
	`code`
	tb_book_main
WHERE
	`isbn` IN ( SELECT `isbn` FROM tb_book_base WHERE `publish_time` BETWEEN 20191003 AND 20191103 )
	AND `role` = 100
LIMIT 1000,10

使用explain查看索引命中情况，发现并没有使用isbn索引

笔者继续使用force index强制使用isbn索引，发现索引仍然没有命中

除了没有命中索引，更致命的是sql涉及到了dependent subquery，这意味着每扫描一行tb_book_main都要执行一次IN内部的子查询，这样的查询语句，即使命中了索引，速度也会相当慢。为了解决IN慢查询问题，笔者想到了如下两个方案

拆分sql语句

笔者怀疑生产环境5.5版本的mysql in查询就是无法命中索引，于是去除子查询进行验证，发现in在没有子查询的情况下是可以利用索引的，explain如下

于是笔者有了第一种方案，这种方案是拆分sql子查询，将一条sql拆为两条sql，将in内部的sql拆分出来，取出isbn集合列表，然后利用mybatis的sql拼接的功能，拼成完整的sql语句。Xml代码如下所示，第一步首先利用时间参数分页取出isbn列表

<select id="findIsbnBetweenTime" >
    SELECT `isbn` FROM `tb_book_base` WHERE publish_time BETWEEN #{fromTime} AND #{toTime} LIMIT #{offset},#{size}
</select>

第二步先在服务层中判断上一轮取出的isbn列表是否为空，如果不为空继续向下执行，如果isbn集合列表不为空，继续利用mybatis提供的xml动态标签功能，拼接出in查询条件

<select id="findBookMain" resultType="com.lantian.db.model.BookMain">
   SELECT * FROM `tb_book_main` WHERE `isbn` IN (
    <foreach collection="list" item="isbn" separator=",">
        #{isbn}
    </foreach>
</select>

联结代替子查询

这种方案原理还是避免子查询，将子查询语句改写为联结查询，改写后的sql语句如下所示

SELECT
	t1.`isbn`,
	`code`