MySQL 5.5 Community Server MySQL 5.6 Community Server Percona Configuration Wizard XtraBackup 搭建主从复制 Great Sites on MySQL Percona MySQL Performance Blog Severalnines 推荐管理工具 Sequel Pro phpMyAdmin 推荐书目 MySQL Cookbook
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html 
这是一个创建于 2019 天前的主题，其中的信息可能已经有所发展或是发生改变。
情况是这样的，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 条附言 2020-04-02 16:06:53 +08:00
抱歉各位，之前笔误 a.time 打成了 b.time，需求是按照 a.time 进行时间范围筛选（范围不超过一天）
执行计划如图：
https://i.loli.net/2020/04/02/NzGIJ5w4HLv9dlM.png
idx_con_ext 为聚合索引，b.id + 另外一个字段，b.id 索引在前，单独查 b.id 的时候索引有效
执行计划如图：
https://i.loli.net/2020/04/02/NzGIJ5w4HLv9dlM.png
idx_con_ext 为聚合索引，b.id + 另外一个字段，b.id 索引在前，单独查 b.id 的时候索引有效
 | 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 |
1 | 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 级别  ) |
 | 37 F281M6Dh8DXpD1g2 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 这类里面进行计算。如果离线分析就好办了。 |
Select Language