情况是这样的,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,不知道有什么隐患没。
1
TZ 2020-04-01 20:30:24 +08:00
你单查 a 表还能检索 b.time ? a.bid 、a.time 联合索引一下试试?
|
2
lurenw 2020-04-01 20:42:09 +08:00
当 a 表中的 bid 在 b 表里不存在的时候, 不就查不够 100 个了么
|
3
InternetExplorer 2020-04-01 20:42:16 +08:00
应该没啥隐患,就是以后把这两个表放到两个数据库都不用改 SQL
|
4
stevenkang OP @TZ 打错了,应该是用 a.time 进行时间范围筛选,a.bid 、a.time 有单独的索引,聚合索引我试试。之前的 sql 通过 explain 查看索引用上了,可查询出来就是慢。。。
|
5
fortunezhang 2020-04-01 20:53:27 +08:00
同意二楼的说法。同时分页也是一个问题。
|
6
ElmerZhang 2020-04-01 20:58:38 +08:00
如果你前后 SQL 里的 b.time 都是打错了,应该是 a.time 的话,那么拆开写完全没问题。甚至很多大厂的规范中会要求必须拆开写,理由如 #3 所讲,即使未来 a 表和 b 表拆到两个库中,SQL 都不用改。
|
7
ElmerZhang 2020-04-01 21:00:33 +08:00
@lurenw @fortunezhang 楼主原 SQL 中就是 left join,是允许 b 表里不存在的。
|
8
TZ 2020-04-01 21:00:51 +08:00
@fortunezhang 想多了,没啥问题,left join 有关联行就塞,没关联行就不塞,又不是 inner join 。分 2 次写更好,联合索引都不用建,只是多一次网络请求,业务逻辑层做关联,减少关联查询 mysql 负担
|
9
kawowa 2020-04-01 21:01:19 +08:00 via Android
|
11
fortunezhang 2020-04-01 21:12:46 +08:00
@TZ 学到了
|
12
fortunezhang 2020-04-01 21:12:59 +08:00
@ElmerZhang 疏忽了。尴尬
|
13
areless 2020-04-01 21:53:27 +08:00 via Android
EXISTS 试试
|
14
cgh 2020-04-01 21:59:42 +08:00 via Android
可以做子查询先查出符合条件的 100 条 a. id 和 b. id 再连接 a,b 两个表查询,减少回表次数。
|
15
leon0903 2020-04-01 22:01:39 +08:00
其实我心中一直有一个疑问, 就是原来的复合 sql 拆分为单表之后,分页是要怎么做? 很多人懂不懂就说不要复杂查询 要分单表 但是这其中的分页怎么处理却从来没提过。 难道都是不分页的么。。。。
|
16
djoiwhud 2020-04-01 22:24:21 +08:00
Navicat 分析看看执行过程呢。不一定是索引执行的,有可能全表扫描了,你不知道而已。
|
17
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 再遍历 |
18
weizhen199 2020-04-01 23:42:36 +08:00 via Android
mysql 用的不多,但是最好贴一个执行计划看看,还有 in 子查询 mysql 不走索引?
|
19
stabc 2020-04-01 23:50:04 +08:00
最近用 nosql 用多了,习惯设计表是就灵活一些,不怕重复。你这个案例,我会在 a 表新加一个 b_time 字段方便查询优化。
|
20
codelover2016 2020-04-02 00:10:34 +08:00
@leon0903 看情况,拆表的情况下,分页是很蛋疼的。我这边的方案是,做一个逻辑视图来解决分页问题,它甚至可能是个内存分页,查到数据 Id 之后再去查数据。
不过实际应用中,我这里做了是分区表或者做统计表解决。 |
21
sagaxu 2020-04-02 00:59:38 +08:00 via Android
@weizhen199 in subquery 情况比较复杂,跟 mysql 版本还有关系
|
22
JamesR 2020-04-02 05:53:23 +08:00
数据库尽量别用 left join,慢。
|
23
horkooo 2020-04-02 08:13:21 +08:00 via Android
类似这样查询慢,我一般分开不用 join,中间引用 redis 缓存
|
24
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 |
25
raysonlu 2020-04-02 09:23:04 +08:00
存在隐患,查 b 表的时候,in 部分拼接过长,会导致 sql 语句过长,不过也得看实际情况是否会绝对不超出。
|
26
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 |
27
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 |
28
stevenkang OP |
29
zivyou 2020-04-02 09:43:21 +08:00
|
30
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 这个过程很慢。
|
31
gavinjou818 2020-04-02 10:02:38 +08:00
@yufpga 我个人觉得不一定,>,<走不走索引还是得看执行计划,比较赞成是因为 join 问题。我记得好像 sql 真实执行是 from..on..join 开始,感觉这两个表太大,导致的太慢。
|
32
asd123456cxz 2020-04-02 10:17:56 +08:00
@yufpga #30 <>是可以走索引的,叶子节点双链表,in 或者 between 的话要看优化器的想法。确实很有可能是 bufferpool 的问题
|
33
m1ch3ng 2020-04-02 10:21:04 +08:00
a 表 x,bid 和 time 用覆盖索引,可以达到 index 级别
![image.png]( ) |
37
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 么......... |
38
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, 这个无端的增加复杂度。以后看起来很麻烦。 |
40
TZ 2020-04-02 12:15:30 +08:00
嗯,本机试了下,联合索引没用,除非 where 语句加上 a.bid=""才能用上。关键还是这个 a.time 的范围查询数据量太大导致的,关联查询不会给你想 limit 100 行数据后再进行 left join b 表,而是先范围查询的全部数据然后去关联查询 b 表数据最后再进行 limit 。https://imgur.com/cuahrVu
|
41
m1ch3ng 2020-04-02 12:20:04 +08:00
单独查 a 是 index 级别,单独查 b 是 range 级别。个人感觉 left join 好一点,因为连接查询是 eq_ref 级别,比单独查 b 的 range 级别好,而且少了一次请求
|
42
reus 2020-04-02 15:25:15 +08:00
恭喜你,你发明了 hash join 。
msyql 8 有 hash join 了,不要用 5 了。 |
43
l00t 2020-04-02 15:30:37 +08:00
你这语句就是错的……
还是贴执行计划吧 |
44
krixaar 2020-04-02 15:47:08 +08:00
如果需求是 A 表取 100 条,B 表如果有就带上,没有就没有,那么第一条语句 where b.time <= xxx 限定 b.time 必须有值,left join 就没有 left 的效果了吧?
|
45
stevenkang OP |
46
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
|
47
kim01 2020-04-02 16:44:57 +08:00
对于这种有数据量的表,单表读取不香吗,尽量单表操作然后再组合数据,就是以后拆分也简单快捷不好吗。。
|
48
themostlazyman 2020-04-02 17:43:24 +08:00
@themostlazyman
再加个 WHERE a.time >= xxx |
49
TZ 2020-04-02 18:28:16 +08:00
你的执行计划太诡异了,你 ref 是 func,难道你还用了函数?
|
50
TZ 2020-04-02 18:37:41 +08:00
我就算模拟了你这种 func,我 a 表 800 万,b 表 400 万,查询也才 100ms 左右。
https://i.loli.net/2020/04/02/ATPIOFkZlWip6gs.png |
54
TZ 2020-04-02 19:39:52 +08:00
@RipL 嗯,不一定。哈哈哈哈,因为我这个数据里面的时间是跟 id 单调递增的,反而关了 mrr 性能更好。索引覆盖?我的 a.x,b.x 怎么被覆盖的。
https://i.loli.net/2020/04/02/S7OLH231A6tQGNs.png |
56
stevenkang OP |
57
zwj2885 2020-04-03 10:01:40 +08:00
搞大数据搞的习惯,如果业务场景是实时查询的,那就把表做分区,或者像你这样,把计算过程放到 java 中。如果是实时要求高,就放到 kafka 这类里面进行计算。如果离线分析就好办了。
|