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

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

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

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

SQL

查询

Time

select

57 条回复 • 2020-04-03 10:01:40 +08:00

2020-04-01 20:30:24 +08:00

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

lurenw

2020-04-01 20:42:09 +08:00

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

InternetExplorer

2020-04-01 20:42:16 +08:00

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

stevenkang

2020-04-01 20:43:02 +08:00

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

fortunezhang

2020-04-01 20:53:27 +08:00

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

ElmerZhang

2020-04-01 20:58:38 +08:00

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

ElmerZhang

2020-04-01 21:00:33 +08:00

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

2020-04-01 21:00:51 +08:00

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

kawowa

2020-04-01 21:01:19 +08:00 via Android

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 能不能这样用？

2020-04-01 21:02:51 +08:00

@kawowa in 子查询不走索引

fortunezhang

2020-04-01 21:12:46 +08:00

@TZ 学到了

fortunezhang

2020-04-01 21:12:59 +08:00

@ElmerZhang 疏忽了。尴尬

areless

2020-04-01 21:53:27 +08:00 via Android

EXISTS 试试

cgh

2020-04-01 21:59:42 +08:00 via Android

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

leon0903

2020-04-01 22:01:39 +08:00

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

djoiwhud

2020-04-01 22:24:21 +08:00

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

ffeii

2020-04-01 23:23:18 +08:00 via iPhone

我经常用的两种方式
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

2020-04-01 23:42:36 +08:00 via Android

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

stabc

2020-04-01 23:50:04 +08:00

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

codelover2016

2020-04-02 00:10:34 +08:00

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

sagaxu

2020-04-02 00:59:38 +08:00 via Android

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

JamesR

2020-04-02 05:53:23 +08:00

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

horkooo

2020-04-02 08:13:21 +08:00 via Android

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

xuanbg

2020-04-02 08:29:21 +08:00

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

2020-04-02 09:23:04 +08:00

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

calmzhu

2020-04-02 09:25:39 +08:00 via Android

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

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

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

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

raysonlu

2020-04-02 09:30:55 +08:00

类似的问题，这个方法不知是否也有帮助：
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

2020-04-02 09:43:07 +08:00

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

zivyou

2020-04-02 09:43:21 +08:00

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

yufpga

2020-04-02 09:45:06 +08:00

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

2020-04-02 10:02:38 +08:00

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

asd123456cxz

2020-04-02 10:17:56 +08:00

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

m1ch3ng

2020-04-02 10:21:04 +08:00

a 表 x,bid 和 time 用覆盖索引，可以达到 index 级别
![image.png](

)

starcraft

2020-04-02 11:09:08 +08:00

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

2020-04-02 11:11:42 +08:00

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

2020-04-02 11:22:39 +08:00

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

liprais

2020-04-02 11:31:38 +08:00

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

2020-04-02 11:44:59 +08:00

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

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

m1ch3ng

2020-04-02 12:14:46 +08:00

@TZ #36

2020-04-02 12:15:30 +08:00

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

m1ch3ng

2020-04-02 12:20:04 +08:00

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

reus

2020-04-02 15:25:15 +08:00

恭喜你，你发明了 hash join 。

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

l00t

2020-04-02 15:30:37 +08:00

你这语句就是错的……

还是贴执行计划吧

krixaar

2020-04-02 15:47:08 +08:00

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

stevenkang

2020-04-02 16:11:12 +08:00

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

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

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

themostlazyman

2020-04-02 16:13:34 +08:00

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

2020-04-02 16:44:57 +08:00

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

themostlazyman

2020-04-02 17:43:24 +08:00

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

2020-04-02 18:28:16 +08:00

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

2020-04-02 18:37:41 +08:00

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

RipL

2020-04-02 19:14:12 +08:00

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

RipL

2020-04-02 19:20:37 +08:00

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

RipL

2020-04-02 19:21:25 +08:00

@raysonlu 这个可以试试

2020-04-02 19:39:52 +08:00

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

RipL

2020-04-02 20:46:44 +08:00

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

stevenkang

2020-04-03 09:51:52 +08:00

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

zwj2885

2020-04-03 10:01:40 +08:00

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