苦逼，下班了，遇到一个 SQL 查询慢的问题，和同事讨论很久，结果用代码解决了

[stevenkang]

情况是这样的，mysql 5.7.14-log，有两个表，A 表 2000w 条数据，B 表 1000w 条数据。

现有需求：select a.x, b.x from A a left join B b on a.bid = b.id where a.time >= xxx and b.time <= xxx limit 0, 100

a.bid 、a.time 有索引的情况下，查询一次 5000ms 左右，查询频繁了 10000ms+ 导致接口超时。

和同事讨论了很久，各种 sql 子查询什么的都很慢。最后想到用 in 的方式程序拼接 sql 语句。

于是用了下面的流程：

select a.x, a.bid from A a where a.time >= xxx and b.time <= xxx limit 0, 100

程序将上面查询的 a.bid 拼装为 sql： select b.id, b.x from B b where b.id in (xx1, xx2...xx100)

然后程序中用 b.id 作为 key，b.x 作为 value 建立映射

再次循环查询 A 的数据，将 b.x 通过程序设置进去，然后统一返回。

目前这种方案查询下来 50ms，不知道有什么隐患没。

[sagaxu]

@weizhen199 in subquery 情况比较复杂，跟 mysql 版本还有关系

[JamesR]

数据库尽量别用 left join，慢。

[horkooo]

类似这样查询慢，我一般分开不用 join，中间引用 redis 缓存

[xuanbg]

select a.x, a.bid from A a where a.time >= xxx and b.time <= xxx limit 0, 100 这个语句能执行？里面 b.time 哪里来？

如果 b.time 是 a.time 的笔误，那你这个结果能等价？除非你原先的 sql 里面 b.time 也是 a.time

[raysonlu]

存在隐患，查 b 表的时候，in 部分拼接过长，会导致 sql 语句过长，不过也得看实际情况是否会绝对不超出。

[calmzhu]

不确定这个版本 MySQL 引擎中 left join 跟 where 的执行数据。

推测执行过程可能是先对整表执行了 2000w left join 1000w right.的查询。然后才做的 where 过滤。自然是极慢而非必须的。

合理的流程其实你已经用代码实现了。先对左右表过滤得到两个临时表。然后用这个临时表去 left join 拿数据

这过程同样可以用 SQL 实现 https://blog.csdn.net/guochunyang/article/details/79236446

[raysonlu]

类似的问题，这个方法不知是否也有帮助：
select a.x, b.x from (select * from A where a.time >= xxx) a left join B b on a.bid = b.id and b.time <= xxx limit 0, 100

[stevenkang]

@kawowa 之前试过这种方式，但提示 mysql 版本不支持
@calmzhu 感谢，我研究一下这个 SQL 实现方式
@leon0903 分页主要是用 A 表的数据，B 表的数据没有也行，B 表不足 100 条也不影响业务需求

[zivyou]

学习了。
有个小问题要请教下各位大佬(和 lz 问题无关): select b.id, b.x from B b where b.id in (xx1, xx2...xx100) ，in 后面的列表如果很长有影响吗? 最长可以有多长?

[yufpga]

a.time 加了索引也没用，innodb 非主键的索引是非聚簇索引, 不支持范围查询的，用这种>, <的应该是不会走索引的, 还是得全表扫描，select a.x, a.bid from A a where a.time >= xxx and b.time <= xxx limit 0, 100 这条 sql 应该是全表扫描的，explain 一下看看就知道了。就单表来说，两张表都不算小，分开写比较合理。之所以原查询慢，是因为两张表的数据 join 在一起太多了，而 innodb 的 buffer pool 远远不够，频繁读取磁盘数据到 buffer pool 这个过程很慢。

[gavinjou818]

@yufpga 我个人觉得不一定，>,<走不走索引还是得看执行计划，比较赞成是因为 join 问题。我记得好像 sql 真实执行是 from..on..join 开始，感觉这两个表太大，导致的太慢。

[asd123456cxz]

@yufpga #30 <>是可以走索引的，叶子节点双链表，in 或者 between 的话要看优化器的想法。确实很有可能是 bufferpool 的问题

[m1ch3ng]

a 表 x,bid 和 time 用覆盖索引，可以达到 index 级别

[starcraft]

@m1ch3ng 这个确实算正确解法。但 lz 这个 x，可能代表的是若干个其他字段吧？

[TZ]

@m1ch3ng 终于回答了我 1 楼的问题了

[TZ]

@m1ch3ng 额，你这单表啊，能不能像楼主一样 left join 试下

[liprais]

select a.x, b.x from A a left join B b on a.bid = b.id where a.time >= xxx and b.time <= xxx limit 0, 100
你们讨论了半天都没发现这里的 left join 其实是 inner join 么.........

[js8510]

我觉得
（ 1 ）要考虑 Atomic 的问题吧。 你要把你的 sql 放到一个 atomic block 里面。 另外，如果有 ORM layer 的话，封装的好，我觉得这样做问题不大。
（ 2 ）另外你的 sql 最后会多长，sql 长度应该是有限制的。要看下 mysql 的限制是多少， 最好封装的时候有个 enforce

但是最好还是 分析下为什么。比如能不能看到 sql DB 的 log. 查下 sql server 的 iostate 看看慢在哪。如果作为 temporary solution, 我觉得可以。如果是大型服务 hot code path, 这个无端的增加复杂度。以后看起来很麻烦。

[m1ch3ng]

@TZ #36

[TZ]

嗯，本机试了下，联合索引没用，除非 where 语句加上 a.bid=""才能用上。关键还是这个 a.time 的范围查询数据量太大导致的，关联查询不会给你想 limit 100 行数据后再进行 left join b 表，而是先范围查询的全部数据然后去关联查询 b 表数据最后再进行 limit 。https://imgur.com/cuahrVu