并发查询，速度变慢问题

Viewed 74
2.1

image.png

图上是并发查询的时候，花了200ms左右，但是同样这条sql拉出来执行，只需要80ms左右，
服务器配置是8核32G
并发的时候，在Manager上看监控的情况如下图，感觉也不算很高的并发吧
image.png

image.png

wywCn5
2 Answers

查询慢的profile
发不了太长，只发了summary

Summary:
      -  Profile  ID:  4854f7e8efb04e4b-ba1fbc657670eb0a
      -  Task  Type:  QUERY
      -  Start  Time:  2024-09-05  17:01:22
      -  End  Time:  2024-09-05  17:01:23
      -  Total:  646ms
      -  Task  State:  EOF
      -  User:  root
      -  Default  Db:  test
      -  Sql  Statement:  SELECT  SUM(t.`15.22.1.2_0`)  AS  `门诊人数-同期`  FROM  (SELECT  t2023.complex_range_1_code  AS  "990101_code",t2023.complex_range_1_value  AS  "990101",t2023.complex_range_4_code  AS  "279980cb005740d2834784a17e2f0d41_code",t2023.complex_range_4_value  AS  "279980cb005740d2834784a17e2f0d41",t2023.complex_range_3_code  AS  "e4c6a1cbf48a400db8ba806a9b8d4aeb_code",t2023.complex_range_3_value  AS  "e4c6a1cbf48a400db8ba806a9b8d4aeb",t2023.complex_range_2_code  AS  "990103_code",t2023.complex_range_2_value  AS  "990103",t2023.the_year  as  "the_year",t2023.the_month  as  "the_month",t2023.the_day  as  "the_day",t2023.the_date  as  "the_date",t2023.hbdwbh  as  "hbdwbh",t2023.hbdwbh_name  as  "hbdwbh_name",t2023.the_data  as  "15.22.1.2_0"  FROM  hrp_index_data2023_5  t2023  WHERE  index_guid  =  'd32e560b6e944ee886c54e423e252223')  t  WHERE  (DATE_FORMAT(t.`the_date`,'%Y-%m')  >=  '2023-01'  AND  DATE_FORMAT(t.`the_date`,'%Y-%m')  <=  '2023-07')  AND  (DATE_FORMAT(t.`the_date`,'%Y-%m')  >=  '2023-01'  AND  DATE_FORMAT(t.`the_date`,'%Y-%m')  <=  '2023-07'  AND  t.`hbdwbh`  =  '35042602')  
Execution  Summary:
      -  Parse  SQL  Time:  1ms
      -  Nereids  Analysis  Time:  1ms
      -  Nereids  Rewrite  Time:  2ms
      -  Nereids  Optimize  Time:  1ms
      -  Nereids  Translate  Time:  0ms
      -  Workload  Group:  normal
      -  Analysis  Time:  1ms
      -  Plan  Time:  3ms
          -  JoinReorder  Time:  N/A
          -  CreateSingleNode  Time:  N/A
          -  QueryDistributed  Time:  N/A
          -  Init  Scan  Node  Time:  N/A
          -  Finalize  Scan  Node  Time:  N/A
              -  Get  Splits  Time:  N/A
                  -  Get  Partitions  Time:  N/A
                  -  Get  Partition  Files  Time:  N/A
              -  Create  Scan  Range  Time:  N/A
      -  Schedule  Time:  28ms
          -  Fragment  Assign  Time:  0ms
          -  Fragment  Serialize  Time:  1ms
          -  Fragment  RPC  Phase1  Time:  27ms
          -  Fragment  RPC  Phase2  Time:  0ms
          -  Fragment  Compressed  Size:  5.24  KB
          -  Fragment  RPC  Count:  2
      -  Wait  and  Fetch  Result  Time:  614ms
          -  Fetch  Result  Time:  613ms
          -  Write  Result  Time:  0ms
      -  Doris  Version:  doris-2.1.5-rc02-d5a02e095d
      -  Is  Nereids:  Yes
      -  Is  Pipeline:  Yes
      -  Is  Cached:  No
      -  Total  Instances  Num:  5
      -  Instances  Num  Per  BE:  10.0.0.11:8060:5
      -  Parallel  Fragment  Exec  Instance  Num:  4
      -  Trace  ID:  
      -  Transaction  Commit  Time:  N/A

查询快的profile

Summary:
      -  Profile  ID:  fca4d7b7524d4898-a246765d5fc5f1f3
      -  Task  Type:  QUERY
      -  Start  Time:  2024-09-05  17:03:11
      -  End  Time:  2024-09-05  17:03:11
      -  Total:  34ms
      -  Task  State:  EOF
      -  User:  root
      -  Default  Db:  test
      -  Sql  Statement:  /*  ApplicationName=DBeaver  Ultimate  22.3.0  -  SQLEditor  <doris测试sql.sql>  */  SELECT

	SUM(t.`15.22.1.2_0`)  AS  `门诊人数-同期`

FROM

	(

	SELECT

		t2023.complex_range_1_code  AS  "990101_code",

		t2023.complex_range_1_value  AS  "990101",

		t2023.complex_range_4_code  AS  "279980cb005740d2834784a17e2f0d41_code",

		t2023.complex_range_4_value  AS  "279980cb005740d2834784a17e2f0d41",

		t2023.complex_range_3_code  AS  "e4c6a1cbf48a400db8ba806a9b8d4aeb_code",

		t2023.complex_range_3_value  AS  "e4c6a1cbf48a400db8ba806a9b8d4aeb",

		t2023.complex_range_2_code  AS  "990103_code",

		t2023.complex_range_2_value  AS  "990103",

		t2023.the_year  as  "the_year",

		t2023.the_month  as  "the_month",

		t2023.the_day  as  "the_day",

		t2023.the_date  as  "the_date",

		t2023.hbdwbh  as  "hbdwbh",

		t2023.hbdwbh_name  as  "hbdwbh_name",

		t2023.the_data  as  "15.22.1.2_0"

	FROM

		hrp_index_data2023_5  t2023

	WHERE

		index_guid  =  'd32e560b6e944ee886c54e423e252223')  t

WHERE

	(DATE_FORMAT(t.`the_date`,  '%Y-%m')  >=  '2023-01'

		AND  DATE_FORMAT(t.`the_date`,  '%Y-%m')  <=  '2023-07')

	AND  (DATE_FORMAT(t.`the_date`,  '%Y-%m')  >=  '2023-01'

		AND  DATE_FORMAT(t.`the_date`,  '%Y-%m')  <=  '2023-07'

			AND  t.`hbdwbh`  =  '35042602')
LIMIT  0,  200
Execution  Summary:
      -  Parse  SQL  Time:  0ms
      -  Nereids  Analysis  Time:  0ms
      -  Nereids  Rewrite  Time:  3ms
      -  Nereids  Optimize  Time:  1ms
      -  Nereids  Translate  Time:  0ms
      -  Workload  Group:  normal
      -  Analysis  Time:  0ms
      -  Plan  Time:  4ms
          -  JoinReorder  Time:  N/A
          -  CreateSingleNode  Time:  N/A
          -  QueryDistributed  Time:  N/A
          -  Init  Scan  Node  Time:  N/A
          -  Finalize  Scan  Node  Time:  N/A
              -  Get  Splits  Time:  N/A
                  -  Get  Partitions  Time:  N/A
                  -  Get  Partition  Files  Time:  N/A
              -  Create  Scan  Range  Time:  N/A
      -  Schedule  Time:  4ms
          -  Fragment  Assign  Time:  0ms
          -  Fragment  Serialize  Time:  1ms
          -  Fragment  RPC  Phase1  Time:  3ms
          -  Fragment  RPC  Phase2  Time:  0ms
          -  Fragment  Compressed  Size:  5.23  KB
          -  Fragment  RPC  Count:  2
      -  Wait  and  Fetch  Result  Time:  26ms
          -  Fetch  Result  Time:  25ms
          -  Write  Result  Time:  0ms
      -  Doris  Version:  doris-2.1.5-rc02-d5a02e095d
      -  Is  Nereids:  Yes
      -  Is  Pipeline:  Yes
      -  Is  Cached:  No
      -  Total  Instances  Num:  5
      -  Instances  Num  Per  BE:  10.0.0.11:8060:5
      -  Parallel  Fragment  Exec  Instance  Num:  4
      -  Trace  ID:  
      -  Transaction  Commit  Time:  N/A
wywCn5