点击参与 GreatSQL 8.0.32-26 文档捉虫~~~

GreatSQL社区

搜索

[已解决] mysql 8.0 left join时主表每次循环相关联子查询，每次都循环3次

589 4 2024-5-10 16:40
本帖最后由 leoxu8703 于 2024-5-10 17:00 编辑

最近5.7遇到A left join B on a.id=(子查询)，发现性能低效。通过慢日志里面row_examined指标发现扫描行数异常。为了验证具体扫描次数与行数，把数据导入到8.0数据库中，通过explain analyze模式.A与B关系是1:N关系，只要能关联到记录，子查询都被循环三次。所以导致性能下降。

[size=15.008px]for example:
  1. CREATE TABLE a (
  2. id int NOT NULL DEFAULT ‘0’,
  3. claim_status int NOT NULL,
  4. claim_time datetime DEFAULT NULL,
  5. type_id int DEFAULT NULL ,
  6. create_time datetime NOT NULL,
  7. PRIMARY KEY (id)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  10. CREATE TABLE b (
  11. id varchar(40) CHARACTER SET utf8 NOT NULL,
  12. headless_package_id int DEFAULT NULL,
  13. claim_time datetime DEFAULT NULL,
  14. confirm_time datetime DEFAULT NULL,
  15. PRIMARY KEY (id),
  16. KEY index01 (headless_package_id)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
复制代码

  1. root@localhost : test 03:32:02> select * from A;
  2. +----------+--------------+---------------------+---------+---------------------+
  3. | id       | claim_status | claim_time          | type_id | create_time         |
  4. +----------+--------------+---------------------+---------+---------------------+
  5. | 99358404 |            2 | 2022-01-08 10:05:43 |       0 | 2022-01-01 00:10:33 |
  6. | 99358412 |            2 | 2022-01-10 14:56:02 |       0 | 2022-01-01 00:13:26 |
  7. | 99358422 |            2 | 2022-01-19 10:50:43 |       0 | 2022-01-01 00:19:03 |
  8. | 99358442 |            2 | 2022-01-06 19:27:27 |       0 | 2022-01-01 00:30:23 |
  9. | 99358446 |            2 | 2022-01-07 18:22:02 |       0 | 2022-01-01 00:32:31 |
  10. | 99358448 |            2 | 2022-01-07 10:51:45 |       0 | 2022-01-01 00:33:07 |
  11. | 99358450 |            2 | 2022-01-04 11:03:58 |       0 | 2022-01-01 00:34:20 |
  12. | 99358452 |            2 | 2022-01-07 09:43:12 |       0 | 2022-01-01 00:36:49 |
  13. +----------+--------------+---------------------+---------+---------------------+
  14. 8 rows in set (0.00 sec)

  16. root@localhost : test 03:32:42> select * from B;
  17. +----------------------------------+---------------------+---------------------+---------------------+
  18. | id                               | headless_package_id | claim_time          | confirm_time        |
  19. +----------------------------------+---------------------+---------------------+---------------------+
  20. | 443ec574f6c14539a549a5729be3e7f7 |            99358412 | 2022-01-10 14:56:02 | 2022-01-10 15:23:02 |
  21. | 442f842b75e94d8bb62251900b3296e3 |            99358442 | 2022-01-06 19:27:28 | 2022-01-07 10:20:10 |
  22. | 44f0ba8c7cdb4e2fa728bd2b7eb18244 |            99358448 | 2022-01-07 10:51:46 | 2022-01-07 11:11:06 |
  23. | 44415ed44cd1480fb03cf3da68f87b68 |            99358450 | 2022-01-03 16:02:41 | 2022-01-03 17:47:38 |
  24. | 44b867e336554c139bf30346c141f98d |            99358446 | 2022-01-07 18:22:03 | 2022-01-08 09:01:49 |
  25. | 444d00e4cf324e42a3ef6e9f34c0599f |            99358404 | 2022-01-08 10:05:43 | 2022-01-08 13:36:53 |
  26. | 4492fb67f3774c97a359fdbe11aa2bb2 |            99358452 | 2022-01-07 09:43:13 | 2022-01-07 10:36:26 |
  27. | 44838565c02c4daf98cf9b3d333d2d2d |            99358452 | 2022-01-06 12:13:25 | 2022-01-06 13:24:58 |
  28. | 4434a8c0141243adbff26406b5b484d6 |            99358422 | 2022-01-19 10:50:43 | 2022-01-19 11:01:39 |
  29. | 44f57bc8ac4c4fbe9ec4289bb0d6c99e |            99358452 | 2022-01-03 09:52:19 | 2022-01-03 10:37:11 |
  30. | 445f647b1075462f8ef7189ab75ea6cc |            99358450 | 2022-01-04 11:03:58 | 2022-01-04 18:13:29 |
  31. +----------------------------------+---------------------+---------------------+---------------------+
  32. 11 rows in set (0.00 sec)
复制代码




  1. <div class="blockcode"><blockquote>example A : A has 8 records,B loop 24.
  2. explain analyze select h.id as hid
  3. from A h
  4. LEFT JOIN B claim
  5. ON claim.id =
  6. (SELECT c_in.id
  7. FROM B c_in
  8. WHERE c_in.headless_package_id = h.id
  9. ORDER BY c_in.claim_time DESC LIMIT 1);
  10. | EXPLAIN |
  11. | → Nested loop left join (cost=3.85 rows=8) (actual time=0.313…0.800 rows=8 loops=1)
  12. → Index scan on h using PRIMARY (cost=1.05 rows=8) (actual time=0.053…0.061 rows=8 loops=1)
  13. → Filter: (claim.id = (select [size=15.008px]#2[size=15.008px])) (cost=0.26 rows=1) (actual time=0.091…0.091 rows=1 loops=8)
  14. → Single-row index lookup on claim using PRIMARY (id=(select [size=15.008px]#2[size=15.008px])) (cost=0.26 rows=1) (actual time=0.074…0.074 rows=1 loops=8)
  15. → Select [size=15.008px]#2[size=15.008px] (subquery in condition; dependent)
  16. → Limit: 1 row(s) (cost=0.48 rows=1) (actual time=0.023…0.023 rows=1 loops=24)
  17. → Sort: c_in.claim_time DESC, limit input to 1 row(s) per chunk (cost=0.48 rows=1) (actual time=0.023…0.023 rows=1 loops=24)
  18. → Index lookup on c_in using index01 (headless_package_id=h.id) (actual time=0.015…0.017 rows=1 loops=24)


  21. 例子二:当主表只有一条满足时，B循环三次(可能需要构造记录多点)
  22. root@localhost : test 04:37:54> explain analyze SELECT COUNT(1)
  23.     -> FROM (select h.id as hid,claim.id,claim.headless_package_id, claim.claim_time
  24.     -> from A h
  25.     -> LEFT JOIN B claim
  26.     ->  ON claim.id =
  27.     ->  (SELECT c_in.id
  28.     ->  FROM B c_in
  29.     -> WHERE c_in.headless_package_id = h.id
  30.     ->  ORDER BY c_in.claim_time DESC LIMIT 1)
  31.     -> where h.id in (
  32.     -> 99358412
  33.     -> ))
  34.     -> TOTAL;
  35. | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | -> Aggregate: count(1)  (actual time=0.547..0.547 rows=1 loops=1)
  36.     -> Nested loop left join  (cost=39126.25 rows=10) (actual time=0.537..0.540 rows=1 loops=1)
  37.         -> Index lookup on h using <auto_key0> (id=99358412 [size=15.008px])  (actual time=0.010..0.012 rows=1 loops=1)
  38.             -> Materialize  (cost=39125.00..39125.00 rows=10) (actual time=0.252..0.254 rows=1 loops=1)
  39.                 -> Limit: 10 row(s)  (cost=39124.00 rows=10) (actual time=0.177..0.186 rows=10 loops=1)
  40.                     -> Table scan on hn  (cost=39124.00 rows=388360) (actual time=0.176..0.182 rows=10 loops=1)
  41.         -> Filter: (claim.id = (select #4))  (cost=0.35 rows=1) (actual time=0.281..0.282 rows=1 loops=1)
  42.             -> Single-row index lookup on claim using PRIMARY (id=(select #4))  (cost=0.35 rows=1) (actual time=0.258..0.258 rows=1 loops=1)
  43.             -> Select #4 (subquery in condition; dependent)
  44.                 -> Limit: 1 row(s)  (cost=0.37 rows=1) (actual time=0.077..0.077 rows=1 loops=3)
  45.                     -> Sort: c_in.claim_time DESC, limit input to 1 row(s) per chunk  (cost=0.37 rows=1) (actual time=0.076..0.076 rows=1 loops=3)
  46.                         -> Index lookup on c_in using idex_headless_id (headless_package_id=h.id)  (actual time=0.065..0.067 rows=1 loops=3)
复制代码



全部回复(4)
只看楼主
yejr 2024-5-10 16:47:15
麻烦重新整理下文中的代码段以提高可读性，手动用 "\[code\]代码段\[/code\]" （实际使用时去掉 "\"，我这里演示不这么加会被转义）这种方式包含起来，并且删掉里面的一些非数据库代码，例如 "[size=15.008px]" 这种
回复 采纳

举报

leoxu8703 2024-5-10 17:01:11
yejr 发表于 2024-5-10 16:47
麻烦重新整理下文中的代码段以提高可读性，手动用 "\[code\]代码段\[/code\]" （实际使用时去掉 "\"，我这 ...

已处理
回复 采纳

举报

yejr 2024-5-11 08:22:37
leoxu8703 发表于 2024-5-10 17:01
已处理

b表的id列是varchar，而a表的id列是int，先把二者统一下再看看呢

请补充提供a\b表用于INSERT的SQL吧，方便复现测例

另外，你用的8.0具体什么版本？
回复 采纳

举报

leoxu8703 2024-5-11 09:16:06
yejr 发表于 2024-5-11 08:22
b表的id列是varchar，而a表的id列是int，先把二者统一下再看看呢

请补充提供a\b表用于INSERT的SQL吧，方 ...

2个表不是通过id关联的。
回复 采纳

举报

leoxu8703

1

主题

0

博客

4

贡献

新手上路

Rank: 1

积分
7
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-12-4 01:22 , Processed in 0.020545 second(s), 13 queries , Redis On.
快速回复 返回顶部 返回列表