苦逼，下班了，遇到一个 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，不知道有什么隐患没。

抱歉各位，之前笔误 a.time 打成了 b.time，需求是按照 a.time 进行时间范围筛选（范围不超过一天）

执行计划如图：
https://i.loli.net/2020/04/02/NzGIJ5w4HLv9dlM.png


idx_con_ext 为聚合索引，b.id + 另外一个字段，b.id 索引在前，单独查 b.id 的时候索引有效

[TZ]

你单查 a 表还能检索 b.time ？ a.bid 、a.time 联合索引一下试试？

[lurenw]

当 a 表中的 bid 在 b 表里不存在的时候, 不就查不够 100 个了么

[InternetExplorer]

应该没啥隐患，就是以后把这两个表放到两个数据库都不用改 SQL

[stevenkang]

@TZ 打错了，应该是用 a.time 进行时间范围筛选，a.bid 、a.time 有单独的索引，聚合索引我试试。之前的 sql 通过 explain 查看索引用上了，可查询出来就是慢。。。

[fortunezhang]

同意二楼的说法。同时分页也是一个问题。

[ElmerZhang]

如果你前后 SQL 里的 b.time 都是打错了，应该是 a.time 的话，那么拆开写完全没问题。甚至很多大厂的规范中会要求必须拆开写，理由如 #3 所讲，即使未来 a 表和 b 表拆到两个库中，SQL 都不用改。

[ElmerZhang]

@lurenw @fortunezhang 楼主原 SQL 中就是 left join，是允许 b 表里不存在的。

[TZ]

@fortunezhang 想多了，没啥问题，left join 有关联行就塞，没关联行就不塞，又不是 inner join 。分 2 次写更好，联合索引都不用建，只是多一次网络请求，业务逻辑层做关联，减少关联查询 mysql 负担

[kawowa]

select b.id, b.x from B b where b.id in (
select a.bid id from A a where a.time >= xxx and a.time <= xxx limit 0, 100
)
不知道 MySQL 能不能这样用？

[TZ]

@kawowa in 子查询不走索引

[fortunezhang]

@TZ 学到了

[fortunezhang]

@ElmerZhang 疏忽了。尴尬

[areless]

EXISTS 试试

[cgh]

可以做子查询先查出符合条件的 100 条 a. id 和 b. id 再连接 a,b 两个表查询，减少回表次数。

[leon0903]

其实我心中一直有一个疑问， 就是原来的复合 sql 拆分为单表之后，分页是要怎么做？ 很多人懂不懂就说不要复杂查询 要分单表 但是这其中的分页怎么处理却从来没提过。 难道都是不分页的么。。。。

[djoiwhud]

Navicat 分析看看执行过程呢。不一定是索引执行的，有可能全表扫描了，你不知道而已。

[ffeii]

我经常用的两种方式
1 、子查询：
select a.x, (select x from b where id=a.bid) from A where a.time >= xxx and a.time <= xxx limit 0, 100
2 、禁止表关联，禁止 for 循环中查询：
查 a 表，得到分页 list
遍历得到 a.bid 的 set
in b 表，得到 list，转 map
再遍历

[weizhen199]

mysql 用的不多，但是最好贴一个执行计划看看，还有 in 子查询 mysql 不走索引？

[stabc]

最近用 nosql 用多了，习惯设计表是就灵活一些，不怕重复。你这个案例，我会在 a 表新加一个 b_time 字段方便查询优化。

[codelover2016]

@leon0903 看情况，拆表的情况下，分页是很蛋疼的。我这边的方案是，做一个逻辑视图来解决分页问题，它甚至可能是个内存分页，查到数据 Id 之后再去查数据。
不过实际应用中，我这里做了是分区表或者做统计表解决。

[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

[m1ch3ng]

单独查 a 是 index 级别，单独查 b 是 range 级别。个人感觉 left join 好一点，因为连接查询是 eq_ref 级别，比单独查 b 的 range 级别好，而且少了一次请求

[reus]

恭喜你，你发明了 hash join 。

msyql 8 有 hash join 了，不要用 5 了。

[l00t]

你这语句就是错的……

还是贴执行计划吧

[krixaar]

如果需求是 A 表取 100 条，B 表如果有就带上，没有就没有，那么第一条语句 where b.time <= xxx 限定 b.time 必须有值，left join 就没有 left 的效果了吧？

[stevenkang]

@reus 老系统，一线码农，没有办法让用 mysql 8

@l00t 抱歉之前笔误 a.time 打成了 b.time，已经附加了执行计划

@m1ch3ng 之前也是用的 left join 直接查，索引也加上了，测试环境没问题，结果上了生产查询很慢
@TZ a.time 的范围不会超过一天，数据大概 7w 左右

[themostlazyman]

on 后面的连接条件可以把 where 的晒选提前：SELECT a.x, b.x FROM A a LEFT JOIN B b ON a.bid = b.id AND a.time >= xxx AND b.time <= xxx limit 0, 100

[kim01]

对于这种有数据量的表，单表读取不香吗，尽量单表操作然后再组合数据，就是以后拆分也简单快捷不好吗。。

[themostlazyman]

@themostlazyman
再加个 WHERE a.time >= xxx

[TZ]

你的执行计划太诡异了，你 ref 是 func，难道你还用了函数？

[TZ]

我就算模拟了你这种 func，我 a 表 800 万，b 表 400 万，查询也才 100ms 左右。
https://i.loli.net/2020/04/02/ATPIOFkZlWip6gs.png

[RipL]

@TZ in 不走索引这个不一定吧

[RipL]

@TZ 你这个用了索引覆盖 还用了 mrr

[RipL]

@raysonlu 这个可以试试

[TZ]

@RipL 嗯，不一定。哈哈哈哈，因为我这个数据里面的时间是跟 id 单调递增的，反而关了 mrr 性能更好。索引覆盖？我的 a.x，b.x 怎么被覆盖的。
https://i.loli.net/2020/04/02/S7OLH231A6tQGNs.png

[RipL]

@TZ 逃~ 看错了，是索引下推。看楼主的第二个索引比较长，200 多。

[stevenkang]

@TZ 查询的时候只是简单的 left join，没有用到函数，检查了一下字段，发现 a.bid 长度为 255，b.id 长度为 50，不知道是否因为这个原因导致的索引 ref 为 func

[zwj2885]

搞大数据搞的习惯，如果业务场景是实时查询的，那就把表做分区，或者像你这样，把计算过程放到 java 中。如果是实时要求高，就放到 kafka 这类里面进行计算。如果离线分析就好办了。