【详述】
1、建表语句：
CREATE TABLE staff_data_v3 (
staff_open_id varchar(1048576) NOT NULL COMMENT “员工ID”,
tenant_id varchar(1048576) NOT NULL COMMENT “租户ID”,
department_id_array ARRAY<varchar(1048576)> NULL COMMENT “部门ID数组”,
position_id_array ARRAY<varchar(1048576)> NULL COMMENT “岗位ID数组”,
etl_time datetime NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY(staff_open_id, tenant_id)
COMMENT “员工信息宽表V3”
DISTRIBUTED BY HASH(tenant_id) BUCKETS 6
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”,
“compression” = “LZ4”
);
2、数据分布： tenant_id为00ptbnd6680584221679734567057167的数据占表90%，以上ID值均为UUID值填充
【背景】
1、查询SQL：
SELECT
count(1)
from staff_data_v3
where tenant_id=‘00ptbnd6680584221679734567057167’
and arrays_overlap(department_id_array,[‘68d214bde5624e4f8a09cc3e1abe608b’,‘51ca93b24d5540919e1ae4ca86580310’,‘1a5c418b3e9743eea1afe5407be624dd’,‘9860a8aae86347ce8dfae4159242d6f1’,‘4439e0051744400ab68f41248012d4c0’,‘8b368f7195a74e0888713fa1b9d79615’,‘a4b03b782e2e4be0a0d437b18f8293ba’,‘c97d3df9edac40488c02a701ffc78188’,‘95e3ea140acb4093a817529cd3d8bd98’,‘0f02b5b159d54d3eb984a98c71803bd8’,‘66de118d87a141e995fd9aae9f8100a5’,‘57b4e196bd8041138ed59a801a3bd30a’,‘45f14867da6945d0a782ea6c868df642’,‘ed301ff0b0da4da58f3abb06e760e0e3’,‘bdcf1293bd25443492a61ba6bbf5a7c5’,‘9e665c74f8ee40c5af72459c9c86565c’,‘369fed16cc1c4f7a891840820dd2d5a0’,‘62b996ce1718448ba6f2b1be9993745d’,‘b5fb705cf68f4ee8a34e62be196c2829’,‘0cc55f1288c44a9a98d9b0aceaceb0c1’,‘d7e80f7f6b224aeca9ee1d099878a5ed’,‘b7b0d63da37d44a8acebea0e576a5944’,‘c343875d127c485f8547211f6e7a5664’,‘b4bcb7a255004ac79ef37c06b0e479a9’,‘a25957cd572f432887699eee984a3ce5’,‘99ac94e9d5c54fe7ba6c2f262ef662c2’,‘09c28d5e7019485496f2706d0dbc3e8e’,‘ab3911ba4e734885800b032df09cde38’,‘cc9da7fc07d64b02b9ba35235ee26285’,‘0b3272106f4f47aca0b0254cff4b708a’,‘15e655086e5f477a80a339a98b1bc930’,‘026ffd5c70d94ae497106dbdf6353d4e’,‘33ae24e175aa4e79bccbc9992096387e’,‘5064fb1d44654734807d0e65359ffdb8’,‘3c79fc225ed1443586a88faa9b9ebf60’,‘c1764c7734264642b533eb02add57189’,‘5dbd5d89c94a4cbdb3680226ff285920’,‘e0dc09e4233843e0be4f32660132f40e’,‘782935dddf10433991e29c1551c8a1b3’,‘a2d4add6b6d341bba6d056c74152c558’,‘c484387ee47545c4bc4a5c2ec748c68a’,‘02d3a1636bc1440b82ab599412475037’,‘00f35a007eca4249897a40f24c1726cb’,‘b27b740e4eeb4280a0213b322068141d’,‘58465aceee8648a08c256d669ae811dd’,‘0d94146877f449fbb69b35acdedc11fb’,‘b8aa69012e3447f6a558926f16c373a2’,‘2fc0b079e6724954a20c1df0b76ab5a5’,‘c395635dbc57490bbdc6aefd7764687d’,‘5b3d7f72a1ca421999301c38c0bfc751’,‘e78dee090b8143de9a808e43107a50d0’,‘69f3644cc05a47f1a4113c27077cdc12’,‘f6eda744d7fe4dd7ae57b38e21bbb79e’,‘a1bf20938fa34b8984e6780b0f0db5e0’,‘b48c17b996d44dbe9d030ed207d1f43c’,‘3fae7080ae2d4d9bb91574492c362f56’,‘9fa84bffa1cd4ccfb976a7075cab49c9’,‘a6ab964ad723482da8fab3310ece535b’,‘6297321b3058452db084923978744d54’,‘61c0bc2b7af4413aaf45d150a0cbb1ca’,‘50cbef86f8064c39bd683a0e7ccab25f’,‘41f3a314c84a47c6a293a2bc5ed0d893’,‘15898158ca5b4f09887b35ee551081f8’,‘6333aa4995504cc785d01ef69d70298e’,‘3f927438f25544c48e242c6ecd1c519d’,‘bc24cfd36f374b5ba98c3caceeceeacb’,‘2565f2ca8829474cab3c37262f9967c5’,‘06edd71a5dad45c7ba0ec68209cddbd0’,‘8122a1e961e54f32a277a85f8cd534fa’,‘08e47991d81b468aabcb33a925b244bb’,‘a2ed804d4a684309a46998dbbade74de’,‘5153e068faff4c70b98672d74548ab78’,‘e1ba2555d3b344eb8dceea92d40c1a3c’,‘2b6b354367a149459ed1ec5ac988da00’,‘8320c48d36f54241b9eef31ffc153b8e’,‘7d7b8f59cc52436b81751062a3353ffe’,‘f63a5a38fa2d40e19e9c3044a8b48724’,‘b482b284b37b4f438de9186d971963f4’,‘12a8d881b8794ab8b8da398dcd3df392’,‘74ccc182fb534e7fb5bcff06a1206e54’,‘d0f519507b714ca4afac5286fa4ea900’,‘d15f67e4c2b14de6ad71206ca1b03e59’,‘de9ee6ae418a4aa48c615089510219c5’,‘40cf2a48214b44bf9087df775d45c126’,‘caf9b815d3ee4ab993a483d6be1073f2’,‘69a9c353f24b4e63b3101f99aebb6bd0’,‘16f413ae95bd4c668507bcdc90d338ff’,‘327e011671954792998dba56046b4124’,‘f608a330cb7c48f3a8404ae39c7f192c’,‘e6f27c3443b0474aad82df25b00b7e4a’,‘8bb2010d65974b27b492c536c5825b29’,‘38a8c046d1194e0aa525cb92ea873313’,‘22d05d316e254bcf821229fe9d9c375a’,‘a375c5b5ab634949b3ea139772c86880’,‘b7a906171e124c49b03840cf1d03ea1b’,‘79703155644d491894feed596cf6c9b7’,‘0ed9457e9e5b4d92b0de58c3f1fcbca4’,‘1ae1766cb76d495894e594470401adc4’,‘a13c34d39a4b4e828da8acc1c843bc0e’,‘5c68ad0a2e7e42d6bfa2dc5b6d8435e8’,‘84aef4abb54341f78fa7eb0f05e43d08’,‘2b14d4629fa64376913101d48cafb5f0’,‘a5047bb317cd4b879104273a4277e934’,‘341214da065247d09a1470ba32592b98’,‘0d2879e312bc43d4b68828a19957d726’,‘bed2c355682a496ca1f1b2fadb24c3cc’,‘079bece8bf8e4d7d8e203cd2c858a3a1’,‘0c03d512192243c0a022d3f1e1636bf4’,‘0381384681bf474ba13d4de69c5ea1ea’,‘98c9c961540b4a6596634121500625b0’,‘08118acb6e724115a158138698fcb3a8’,‘647c940ebfe6422e8c2cdbcc5c956673’,‘7551692554c04b1987a7ec149accfa0e’,‘0140d8ceca70430b954ca0e62a004cf4’,‘8d666311ef9c46a789710d46c663b6c0’,‘643c43be68044a5fbf05c6300b57df2e’,‘af21995071864b44b9de99d7f4d36110’,‘d68049b7c3b047aaaf4bfee309ae1994’,‘31dbb7fbb250468a80f4699bb5cb9207’,‘d75481049b19443b8320845aca160149’,‘6c2343fa2d664c6d96ce7114b8158c1d’,‘9717f152a3af4044abb7103fcc519be5’,‘0f4d4c03ee924f2698347f6d3ea46acb’,‘4e61e326d0694eebbe99356a8c10e790’,‘f348fdeae4044c7887a307636fbfb3af’,‘e1ecf5d7687b48cf9d3256bd78575dfe’,‘309234d8954c4b21b4133bbab7f0f1df’,‘90c47cadd6734d9697e27671b3e0aab3’,‘4c2e950f81fd41edbb420268c224b318’,‘f2f0b665550b4cc489b58df0325b015c’,‘cbd35535ff7945c2ad1c2d3f0461fa27’,‘4554e32699d845089d486fdb5775282e’,‘f4a747f90ff7425c928c5973085009cc’,‘062678817bd34febb51767b6b3bdd693’,‘98631249cc7f4eceb1b22cabe69f7a1b’,‘19db7064a9334cd3af32414a952864ec’,‘f34906231124444caee7d1000f7a2e7e’,‘e2121f8609e74cfbaca294396675217e’,‘f8b778e65b7847699bd98e2aa2e015ad’,‘5dd7c7fe124845a087401fe57d169857’,‘00f99b0e4cb64fac9ff26fb98fe7b472’,‘ee0c356a686b4ac68b726ea8793606a0’,‘df2f25f8ac0341a18a257e3457a914c3’,‘784ba5e71b4447499852796366ba3cd3’,‘455da7803b6449e69320067b5b848e96’,‘775bea87b5684e85bb865210dbef33f0’,‘68be57a93e464077bc2e62ba8d12eb68’,‘6467355cd76d45bc9a06c52e15bad672’,‘25bc50148970418392715c1b6f993ec0’,‘cc883c94d7344312a2e88905436c09a9’,‘1610b8a653e148878f3af92923e3f201’,‘2444e485308c42a28af3ee7d551c54d0’,‘5c9bfb86b36044669987a9c90a546c56’,‘e2210bf18ff5414d9ecb788ba362c9ac’,‘94190a56a3fc4b2a895a067ea717bb0b’,‘1c5f006da58348d0a89825765054ca2d’,‘f94a32bd5262459d91fe4d12cb0572ef’,‘45147bf3c4da4d58aaff35a334ce2f5d’,‘0cf76bf0bb434aa8b2cc89d6f89d45f4’,‘6ce2833daa5e41d5a1ed794058cf7d47’,‘a6a4be750a484c8baf7f043319aeeee9’,‘d9961ec50472410c8cd9147d821889a0’,‘db88da645f5d444389080983143f1ce8’,‘7ffad0bcef3b42b994a7b47ed1f00c2d’,‘bddd96bf23304395b0408f021c69ba71’,‘8614cc3bb2a8463c8f3d6bd9af4eb0d8’/,‘df6c8888836d4cb6a73cd36186575166’,‘8aa401aa659345429ae6566c448ebb4a’,‘df1c1be7765f4d7e8065f063a086c13b’,‘33a165446e424b76ae08adff53b89dba’,‘5e1278ac564d4ee2bb578eb930bbca15’,‘121b8da955564577904964298d0a350e’,‘74eef73b8dff44b6b2964ba2ea0bce4d’,‘696e47de202c4572b9b93025aa508777’,‘219f68a0f44541dd82732e4751b868a8’,‘37d38719248d404c96580ed9f35fc61c’,‘d26ab6f9661744d7b3b172ba94a98c6f’,‘eb495d1530504ee584b98237c7e9eb7e’,‘87f6166eadb84f14abeaebc2c677da0f’,‘a5f3e42350b849adbbb9684c7aa23e68’,‘745bac69d7df485e9082dbb94d530bf7’,‘4113cb09b318408e8097f39576453ca1’,‘7f3b176e05134da3b611c343dee108f3’,‘0f4e372d6a2b43c298dfeab0bae14290’,‘87e8a6ea669947d58f91df21e3bed10c’,‘ed4af40a900d420abb8ff38297b69a61’,‘ba2344c50561474cbac2a36c949d0f10’,‘cb2f64ee91f54fc2982ac363f38b4446’,‘d39e0f9c9fc54a5b9880eb2ac02fde23’,‘08895976b8964dfebb65cfd17fc94ce8’,‘dc57229de40a40ca9a1fd43fa62c77a6’,‘f42f6fd611cc4e6082ebfeaabe0d65a0’,‘8f66fdfe0fd4480f91095c886c2ea69c’,‘5d39f520b77e481db5f80fbd82f1bc65’,‘8f71b742f8eb477ca36ae8e08e4d4986’,‘dbf1040f798d4503b390fae72ca0d0fa’,‘00e93b0e63bf4646a7074926884e7fb0’,‘ae8483769b634602a23b68a36402261e’/ ])
【业务影响】查询耗时长，不满足秒级实时要求
【StarRocks版本】2.5.4
【集群规模、机器信息】fe：8C16G3 ， be：8C32G3 ，BE/FE单独部署
【联系方式】StarRocks社区群8
【附件】
- Profile信息，如何获取profile
- 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;show variables like ‘%pipeline_dop%’;
|Variable_name|Value|
|—|---|
|parallel_fragment_exec_instance_num|1|
| Variable_name | Value |
|---|---|
| pipeline_dop | 0 |
-
pipeline是否开启：show variables like ‘%pipeline%’;
|Variable_name|Value|
|—|---|
|enable_pipeline_engine|true|
|enable_pipeline_query_statistic|true|
|pipeline_dop|0|
|pipeline_profile_level|1| -
执行计划：explain costs + sql
PLAN FRAGMENT 0
OUTPUT EXPRS:1: staff_open_id | 2: tenant_id | 3: department_id_array | 4: position_id_array | 5: etl_time
PARTITION: RANDOM
RESULT SINK
0:OlapScanNode
TABLE: dwd_hrm_saas_public_staff_data_v3
PREAGGREGATION: ON
PREDICATES: 2: tenant_id = ‘00ptbnd6680584221679734567057167’, arrays_overlap(3: department_id_array, ARRAY[‘68d214bde5624e4f8a09cc3e1abe608b’,‘51ca93b24d5540919e1ae4ca86580310’,‘1a5c418b3e9743eea1afe5407be624dd’,‘9860a8aae86347ce8dfae4159242d6f1’,‘4439e0051744400ab68f41248012d4c0’,‘8b368f7195a74e0888713fa1b9d79615’,‘a4b03b782e2e4be0a0d437b18f8293ba’,‘c97d3df9edac40488c02a701ffc78188’,‘95e3ea140acb4093a817529cd3d8bd98’,‘0f02b5b159d54d3eb984a98c71803bd8’,‘66de118d87a141e995fd9aae9f8100a5’,‘57b4e196bd8041138ed59a801a3bd30a’,‘45f14867da6945d0a782ea6c868df642’,‘ed301ff0b0da4da58f3abb06e760e0e3’,‘bdcf1293bd25443492a61ba6bbf5a7c5’,‘9e665c74f8ee40c5af72459c9c86565c’,‘369fed16cc1c4f7a891840820dd2d5a0’,‘62b996ce1718448ba6f2b1be9993745d’,‘b5fb705cf68f4ee8a34e62be196c2829’,‘0cc55f1288c44a9a98d9b0aceaceb0c1’,‘d7e80f7f6b224aeca9ee1d099878a5ed’,‘b7b0d63da37d44a8acebea0e576a5944’,‘c343875d127c485f8547211f6e7a5664’,‘b4bcb7a255004ac79ef37c06b0e479a9’,‘a25957cd572f432887699eee984a3ce5’,‘99ac94e9d5c54fe7ba6c2f262ef662c2’,‘09c28d5e7019485496f2706d0dbc3e8e’,‘ab3911ba4e734885800b032df09cde38’,‘cc9da7fc07d64b02b9ba35235ee26285’,‘0b3272106f4f47aca0b0254cff4b708a’,‘15e655086e5f477a80a339a98b1bc930’,‘026ffd5c70d94ae497106dbdf6353d4e’,‘33ae24e175aa4e79bccbc9992096387e’,‘5064fb1d44654734807d0e65359ffdb8’,‘3c79fc225ed1443586a88faa9b9ebf60’,‘c1764c7734264642b533eb02add57189’,‘5dbd5d89c94a4cbdb3680226ff285920’,‘e0dc09e4233843e0be4f32660132f40e’,‘782935dddf10433991e29c1551c8a1b3’,‘a2d4add6b6d341bba6d056c74152c558’,‘c484387ee47545c4bc4a5c2ec748c68a’,‘02d3a1636bc1440b82ab599412475037’,‘00f35a007eca4249897a40f24c1726cb’,‘b27b740e4eeb4280a0213b322068141d’,‘58465aceee8648a08c256d669ae811dd’,‘0d94146877f449fbb69b35acdedc11fb’,‘b8aa69012e3447f6a558926f16c373a2’,‘2fc0b079e6724954a20c1df0b76ab5a5’,‘c395635dbc57490bbdc6aefd7764687d’,‘5b3d7f72a1ca421999301c38c0bfc751’,‘e78dee090b8143de9a808e43107a50d0’,‘69f3644cc05a47f1a4113c27077cdc12’,‘f6eda744d7fe4dd7ae57b38e21bbb79e’,‘a1bf20938fa34b8984e6780b0f0db5e0’,‘b48c17b996d44dbe9d030ed207d1f43c’,‘3fae7080ae2d4d9bb91574492c362f56’,‘9fa84bffa1cd4ccfb976a7075cab49c9’,‘a6ab964ad723482da8fab3310ece535b’,‘6297321b3058452db084923978744d54’,‘61c0bc2b7af4413aaf45d150a0cbb1ca’,‘50cbef86f8064c39bd683a0e7ccab25f’,‘41f3a314c84a47c6a293a2bc5ed0d893’,‘15898158ca5b4f09887b35ee551081f8’,‘6333aa4995504cc785d01ef69d70298e’,‘3f927438f25544c48e242c6ecd1c519d’,‘bc24cfd36f374b5ba98c3caceeceeacb’,‘2565f2ca8829474cab3c37262f9967c5’,‘06edd71a5dad45c7ba0ec68209cddbd0’,‘8122a1e961e54f32a277a85f8cd534fa’,‘08e47991d81b468aabcb33a925b244bb’,‘a2ed804d4a684309a46998dbbade74de’,‘5153e068faff4c70b98672d74548ab78’,‘e1ba2555d3b344eb8dceea92d40c1a3c’,‘2b6b354367a149459ed1ec5ac988da00’,‘8320c48d36f54241b9eef31ffc153b8e’,‘7d7b8f59cc52436b81751062a3353ffe’,‘f63a5a38fa2d40e19e9c3044a8b48724’,‘b482b284b37b4f438de9186d971963f4’,‘12a8d881b8794ab8b8da398dcd3df392’,‘74ccc182fb534e7fb5bcff06a1206e54’,‘d0f519507b714ca4afac5286fa4ea900’,‘d15f67e4c2b14de6ad71206ca1b03e59’,‘de9ee6ae418a4aa48c615089510219c5’,‘40cf2a48214b44bf9087df775d45c126’,‘caf9b815d3ee4ab993a483d6be1073f2’,‘69a9c353f24b4e63b3101f99aebb6bd0’,‘16f413ae95bd4c668507bcdc90d338ff’,‘327e011671954792998dba56046b4124’,‘f608a330cb7c48f3a8404ae39c7f192c’,‘e6f27c3443b0474aad82df25b00b7e4a’,‘8bb2010d65974b27b492c536c5825b29’,‘38a8c046d1194e0aa525cb92ea873313’,‘22d05d316e254bcf821229fe9d9c375a’,‘a375c5b5ab634949b3ea139772c86880’,‘b7a906171e124c49b03840cf1d03ea1b’,‘79703155644d491894feed596cf6c9b7’,‘0ed9457e9e5b4d92b0de58c3f1fcbca4’,‘1ae1766cb76d495894e594470401adc4’,‘a13c34d39a4b4e828da8acc1c843bc0e’,‘5c68ad0a2e7e42d6bfa2dc5b6d8435e8’,‘84aef4abb54341f78fa7eb0f05e43d08’,‘2b14d4629fa64376913101d48cafb5f0’,‘a5047bb317cd4b879104273a4277e934’,‘341214da065247d09a1470ba32592b98’,‘0d2879e312bc43d4b68828a19957d726’,‘bed2c355682a496ca1f1b2fadb24c3cc’,‘079bece8bf8e4d7d8e203cd2c858a3a1’,‘0c03d512192243c0a022d3f1e1636bf4’,‘0381384681bf474ba13d4de69c5ea1ea’,‘98c9c961540b4a6596634121500625b0’,‘08118acb6e724115a158138698fcb3a8’,‘647c940ebfe6422e8c2cdbcc5c956673’,‘7551692554c04b1987a7ec149accfa0e’,‘0140d8ceca70430b954ca0e62a004cf4’,‘8d666311ef9c46a789710d46c663b6c0’,‘643c43be68044a5fbf05c6300b57df2e’,‘af21995071864b44b9de99d7f4d36110’,‘d68049b7c3b047aaaf4bfee309ae1994’,‘31dbb7fbb250468a80f4699bb5cb9207’,‘d75481049b19443b8320845aca160149’,‘6c2343fa2d664c6d96ce7114b8158c1d’,‘9717f152a3af4044abb7103fcc519be5’,‘0f4d4c03ee924f2698347f6d3ea46acb’,‘4e61e326d0694eebbe99356a8c10e790’,‘f348fdeae4044c7887a307636fbfb3af’,‘e1ecf5d7687b48cf9d3256bd78575dfe’,‘309234d8954c4b21b4133bbab7f0f1df’,‘90c47cadd6734d9697e27671b3e0aab3’,‘4c2e950f81fd41edbb420268c224b318’,‘f2f0b665550b4cc489b58df0325b015c’,‘cbd35535ff7945c2ad1c2d3f0461fa27’,‘4554e32699d845089d486fdb5775282e’,‘f4a747f90ff7425c928c5973085009cc’,‘062678817bd34febb51767b6b3bdd693’,‘98631249cc7f4eceb1b22cabe69f7a1b’,‘19db7064a9334cd3af32414a952864ec’,‘f34906231124444caee7d1000f7a2e7e’,‘e2121f8609e74cfbaca294396675217e’,‘f8b778e65b7847699bd98e2aa2e015ad’,‘5dd7c7fe124845a087401fe57d169857’,‘00f99b0e4cb64fac9ff26fb98fe7b472’,‘ee0c356a686b4ac68b726ea8793606a0’,‘df2f25f8ac0341a18a257e3457a914c3’,‘784ba5e71b4447499852796366ba3cd3’,‘455da7803b6449e69320067b5b848e96’,‘775bea87b5684e85bb865210dbef33f0’,‘68be57a93e464077bc2e62ba8d12eb68’,‘6467355cd76d45bc9a06c52e15bad672’,‘25bc50148970418392715c1b6f993ec0’,‘cc883c94d7344312a2e88905436c09a9’,‘1610b8a653e148878f3af92923e3f201’,‘2444e485308c42a28af3ee7d551c54d0’,‘5c9bfb86b36044669987a9c90a546c56’,‘e2210bf18ff5414d9ecb788ba362c9ac’,‘94190a56a3fc4b2a895a067ea717bb0b’,‘1c5f006da58348d0a89825765054ca2d’,‘f94a32bd5262459d91fe4d12cb0572ef’,‘45147bf3c4da4d58aaff35a334ce2f5d’,‘0cf76bf0bb434aa8b2cc89d6f89d45f4’,‘6ce2833daa5e41d5a1ed794058cf7d47’,‘a6a4be750a484c8baf7f043319aeeee9’,‘d9961ec50472410c8cd9147d821889a0’,‘db88da645f5d444389080983143f1ce8’,‘7ffad0bcef3b42b994a7b47ed1f00c2d’,‘bddd96bf23304395b0408f021c69ba71’,‘8614cc3bb2a8463c8f3d6bd9af4eb0d8’])
partitions=1/1
rollup: dwd_hrm_saas_public_staff_data_v3
tabletRatio=1/6
tabletList=34339761
cardinality=2208
avgRowSize=104.0
numNodes=0
- be节点cpu和内存使用率截图
