lightdb默认采用分布式、集中式一体化架构，单实例仍然可以启用分布式架构。
环境配置
假设已经安装了lightdb，默认情况下，安装分布式的时候会自动为create database创建canopy插件，也就是分布式版。可通过show %lib%确认，如下：
[zjh@hs-10-20-30-193 ~]$ ltsql -p23456 ltsql (13.8-22.3) Type "help" for help. zjh@lt_test=# show %lib%; name | setting | description ---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+-- ----------------------------------------------------------------- dynamic_library_path | $libdir | S ets the path for dynamically loadable modules. local_preload_libraries | | L ists unprivileged shared libraries to preload into each backend. session_preload_libraries | lt_cheat_funcs | L ists shared libraries to preload into each backend. shared_preload_libraries | canopy,lt_stat_statements,lt_stat_activity,autoinc,auto_explain,lt_prewarm,lt_cron,ltaudit,lt_hint_plan,lt_show_plans,pg_stat_kcache,lt_standby_forward,lt_ope | L ists shared libraries to preload into server. ssl_library | OpenSSL | N ame of the SSL library. (5 rows) zjh@lt_test=# create extension canopy; CREATE EXTENSION zjh@lt_test=# select * from pg_dist_node; nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards --------+---------+----------+----------+----------+-------------+----------+----------+-------------+----------------+------------------ (0 rows)
创建分布式表
zjh@lt_test=# create table canopy_table(id int primary key,v text); CREATE TABLE zjh@lt_test=# select * from pg_dist_shard; logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue --------------+---------+--------------+---------------+--------------- (0 rows) zjh@lt_test=# select create_distributed_table('canopy_table','id'); create_distributed_table -------------------------- (1 row) zjh@lt_test=# select * from pg_dist_shard; logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue --------------+---------+--------------+---------------+--------------- canopy_table | 102008 | t | -2147483648 | -2013265921 canopy_table | 102009 | t | -2013265920 | -1879048193 canopy_table | 102010 | t | -1879048192 | -1744830465 canopy_table | 102011 | t | -1744830464 | -1610612737 canopy_table | 102012 | t | -1610612736 | -1476395009 canopy_table | 102013 | t | -1476395008 | -1342177281 canopy_table | 102014 | t | -1342177280 | -1207959553 canopy_table | 102015 | t | -1207959552 | -1073741825 canopy_table | 102016 | t | -1073741824 | -939524097 canopy_table | 102017 | t | -939524096 | -805306369 canopy_table | 102018 | t | -805306368 | -671088641 canopy_table | 102019 | t | -671088640 | -536870913 canopy_table | 102020 | t | -536870912 | -402653185 canopy_table | 102021 | t | -402653184 | -268435457 canopy_table | 102022 | t | -268435456 | -134217729 canopy_table | 102023 | t | -134217728 | -1 canopy_table | 102024 | t | 0 | 134217727 canopy_table | 102025 | t | 134217728 | 268435455 canopy_table | 102026 | t | 268435456 | 402653183 canopy_table | 102027 | t | 402653184 | 536870911 canopy_table | 102028 | t | 536870912 | 671088639 canopy_table | 102029 | t | 671088640 | 805306367 canopy_table | 102030 | t | 805306368 | 939524095 canopy_table | 102031 | t | 939524096 | 1073741823 canopy_table | 102032 | t | 1073741824 | 1207959551 canopy_table | 102033 | t | 1207959552 | 1342177279 canopy_table | 102034 | t | 1342177280 | 1476395007 canopy_table | 102035 | t | 1476395008 | 1610612735 canopy_table | 102036 | t | 1610612736 | 1744830463 canopy_table | 102037 | t | 1744830464 | 1879048191 canopy_table | 102038 | t | 1879048192 | 2013265919 canopy_table | 102039 | t | 2013265920 | 2147483647 (32 rows) zjh@lt_test=# select * from pg_dist_node; nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards --------+---------+-----------+----------+----------+-------------+----------+----------+-------------+----------------+------------------ 1 | 0 | localhost | 23456 | default | t | t | primary | default | t | t (1 row) zjh@lt_test=# create table canopy_table_detail(id int primary key,v text,branch_id varchar(100)); CREATE TABLE zjh@lt_test=# create table canopy_table_branch(v text,branch_id varchar(100)); CREATE TABLE zjh@lt_test=# select create_distributed_table('canopy_table_detail','id'); create_distributed_table -------------------------- (1 row) zjh@lt_test=# select create_reference_table('canopy_table_branch'); create_reference_table ------------------------ (1 row) --------------插入数据 zjh@lt_test=# insert into canopy_table select id, uuid() from generate_series(1,10000000) id; INSERT 0 10000000 zjh@lt_test=# SELECT update_distributed_table_colocation('canopy_table_detail', colocate_with => 'canopy_table'); update_distributed_table_colocation ------------------------------------- (1 row) zjh@lt_test=# insert into canopy_table_detail select id, uuid(),id % 1000 from generate_series(1,1000000) id; INSERT 0 1000000 zjh@lt_test=# select * from canopy_table_branch ; v | branch_id ---+----------- (0 rows) zjh@lt_test=# insert into canopy_table_branch select uuid(),id from generate_series(1,1000) id; INSERT 0 1000
注：lightdb也支持distributed by (col)语法，如：create table canopy_table_native(id int primary key,v text) distributed by (id); create table canopy_table_native(id int primary key,v text) distributed REPLICATED;
用户可以自行选择使用哪种语法。从lightdb 23c开始，如果在非分布式环境（canopy插件未启用或参数lightdb_arch_mode=classic）下指定了distributed by (id)子句，只是会被忽略，而不会报错，集中式、分布式更加一体化。
从22.4开始，lightdb支持不带distributed by子句的原生分布式表（不过主要用于POC目的），启用了canopy插件且参数lightdb_arch_mode=dist，默认会创建分布式表，会先取主键、没有主键取非唯一索引，否则报错。如果在分布式架构下希望创建本地表或参照表，则需要指定local子句，即create local table。
一般来说，生产推荐lightdb_arch_mode=off，通过distributed by子句创建分布式表、通过DISTRIBUTED REPLICATED子句创建复制表、不带子句创建本地表，开发学习可以启用该参数、更加开箱即用。
查看分布式的执行效果
zjh@lt_test=# explain analyze select b.branch_id,max(a.id),count(1),max(a.v) from canopy_table a,canopy_table_detail b,canopy_table_branch c where a.id=b.id and b.branch_id = c.branch_id group by b.branch_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1000.00..1003.50 rows=200 width=262) (actual time=193.039..193.216 rows=999 loops=1) Group Key: remote_scan.branch_id Batches: 1 Memory Usage: 593kB -> Custom Scan (Canopy Adaptive) (cost=0.00..0.00 rows=100000 width=262) (actual time=183.568..185.866 rows=31968 loops=1) Task Count: 32 Tuple data received from nodes: 1464 kB Tasks Shown: One of 32 -> Task Tuple data received from node: 46 kB Node: host=localhost port=23456 dbname=lt_test -> HashAggregate (cost=8334.15..8336.15 rows=200 width=262) (actual time=106.176..106.386 rows=999 loops=1) Group Key: b.branch_id Batches: 1 Memory Usage: 337kB -> Hash Join (cost=16.95..8215.19 rows=11896 width=254) (actual time=0.381..91.031 rows=31230 loops=1) Hash Cond: ((b.branch_id)::text = (c.branch_id)::text) -> Nested Loop (cost=0.42..7761.80 rows=8204 width=254) (actual time=0.059..80.260 rows=31253 loops=1) -> Seq Scan on canopy_table_detail_102056 b (cost=0.00..375.04 rows=8204 width=222) (actual time=0.024..6.619 rows=31253 loops=1) -> Index Scan using canopy_table_pkey_102024 on canopy_table_102024 a (cost=0.42..0.90 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=31253) Index Cond: (id = b.id) -> Hash (cost=12.90..12.90 rows=290 width=218) (actual time=0.307..0.308 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 44kB -> Seq Scan on canopy_table_branch_102072 c (cost=0.00..12.90 rows=290 width=218) (actual time=0.014..0.157 rows=1000 loops=1) Planning Time: 0.652 ms Execution Time: 106.715 ms Planning Time: 1.104 ms Execution Time: 193.623 ms (26 rows) zjh@lt_test=# select b.branch_id,max(a.id),count(1),max(a.v) from canopy_table a,canopy_table_detail b,canopy_table_branch c where a.id=b.id and b.branch_id = c.branch_id group by b.branch_id order by b.branch_id limit 10; branch_id | max | count | max -----------+--------+-------+-------------------------------------- 1 | 999001 | 1000 | ffe56d6d-b0a3-45f8-8142-68dca1641c2f 10 | 999010 | 1000 | fff9702e-1aa6-4b8b-a6b7-00d085ad00b3 100 | 999100 | 1000 | fff65b94-d2cf-4b6b-95cb-4e7128d59835 101 | 999101 | 1000 | ffb431bf-73d9-4a4b-b4bf-71380f8377be 102 | 999102 | 1000 | ffff5f74-b8ed-4582-85c2-0e7866c14ff7 103 | 999103 | 1000 | ffe86f77-1031-4b5f-8939-e37dffe74e7b 104 | 999104 | 1000 | fff5f9d4-eb29-4c91-9fab-a0c6f75067be 105 | 999105 | 1000 | ff706172-8281-4458-bd14-6b1d83941a72 106 | 999106 | 1000 | ffbdff1e-5f42-42ce-936d-8e56d402ebfe 107 | 999107 | 1000 | ffdd02c9-ab40-4a1e-96df-4bb0fd085cba (10 rows) Time: 101.436 ms
创建对应的非分布式表，然后对比性能
zjh@lt_test=# create table canopy_table_detail_classic(id int primary key,v text,branch_id varchar(100)); CREATE TABLE zjh@lt_test=# create table canopy_table_branch_classic(v text,branch_id varchar(100)); CREATE TABLE zjh@lt_test=# create table canopy_table_classic(id int primary key,v text); CREATE TABLE zjh@lt_test=# insert into canopy_table_branch_classic select uuid(),id from generate_series(1,1000) id; INSERT 0 1000 zjh@lt_test=# insert into canopy_table_detail_classic select id, uuid(),id % 1000 from generate_series(1,1000000) id; INSERT 0 1000000 zjh@lt_test=# insert into canopy_table_classic select id, uuid() from generate_series(1,10000000) id; INSERT 0 10000000
zjh@lt_test=# explain analyze select b.branch_id,max(a.id),count(1),max(a.v) from canopy_table_classic a,canopy_table_detail_classic b,canopy_table_branch_classic c where a.id=b.id and b.branch_id = c.branch_id group by b.branch_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- HashAggregate (cost=391963.76..391973.76 rows=1000 width=47) (actual time=746.889..746.998 rows=999 loops=1) Group Key: b.branch_id Batches: 1 Memory Usage: 321kB -> Hash Join (cost=33.36..381963.76 rows=1000000 width=39) (actual time=0.204..540.833 rows=999000 loops=1) Hash Cond: ((b.branch_id)::text = (c.branch_id)::text) -> Merge Join (cost=0.86..368181.26 rows=1000000 width=39) (actual time=0.030..392.142 rows=1000000 loops=1) Merge Cond: (a.id = b.id) -> Index Scan using canopy_table_classic_pkey on canopy_table_classic a (cost=0.43..298916.92 rows=11869166 width=36) (actual time=0.013..125.229 rows=1000001 loops=1) -> Index Scan using canopy_table_detail_classic_pkey on canopy_table_detail_classic b (cost=0.42..27091.42 rows=1000000 width=7) (actual time=0.011..105.792 rows=1000000 loop s=1) -> Hash (cost=20.00..20.00 rows=1000 width=3) (actual time=0.169..0.169 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 44kB -> Seq Scan on canopy_table_branch_classic c (cost=0.00..20.00 rows=1000 width=3) (actual time=0.005..0.093 rows=1000 loops=1) Planning Time: 0.306 ms Execution Time: 747.084 ms (14 rows)
增加avg函数，如下：
zjh@lt_test=# select b.branch_id,avg(a.id),count(1),max(a.v) from canopy_table_classic a,canopy_table_detail_classic b,canopy_table_branch_classic c where a.id=b.id and b.branch_id = c.branch_id group by b.branch_id order by b.branch_id limit 10; branch_id | avg | count | max -----------+---------------------+-------+-------------------------------------- 1 | 499501.000000000000 | 1000 | ffe3c50f-1c10-441a-b972-464f63f86e65 10 | 499510.000000000000 | 1000 | ffde5346-62aa-4b1b-ae66-2af452716a87 100 | 499600.000000000000 | 1000 | ffb84ead-bfe4-418b-8b7f-1a4d0159a9be 101 | 499601.000000000000 | 1000 | ffe774ab-522f-4652-9994-376cee2dc12c 102 | 499602.000000000000 | 1000 | ff924467-32c8-4d34-b1ad-4bab49a435cb 103 | 499603.000000000000 | 1000 | ffd1c3e4-9c15-47f1-85ee-7baafc8352a7 104 | 499604.000000000000 | 1000 | ffe80998-0a34-44db-95f9-a17417a0f954 105 | 499605.000000000000 | 1000 | ffdb53fc-f684-4d55-98a2-af12725767ae 106 | 499606.000000000000 | 1000 | ffcdde21-1a7c-49f6-bc86-116c96b80af9 107 | 499607.000000000000 | 1000 | ff0fa26f-0b36-40d3-9dec-63ae1a0655cc (10 rows) Time: 687.505 ms zjh@lt_test=# select b.branch_id,avg(a.id),count(1),max(a.v) from canopy_table a,canopy_table_detail b,canopy_table_branch c where a.id=b.id and b.branch_id = c.branch_id group by b.branch_id order by b.branch_id limit 10; branch_id | avg | count | max -----------+---------------------+-------+-------------------------------------- 1 | 499501.000000000000 | 1000 | ffe56d6d-b0a3-45f8-8142-68dca1641c2f 10 | 499510.000000000000 | 1000 | fff9702e-1aa6-4b8b-a6b7-00d085ad00b3 100 | 499600.000000000000 | 1000 | fff65b94-d2cf-4b6b-95cb-4e7128d59835 101 | 499601.000000000000 | 1000 | ffb431bf-73d9-4a4b-b4bf-71380f8377be 102 | 499602.000000000000 | 1000 | ffff5f74-b8ed-4582-85c2-0e7866c14ff7 103 | 499603.000000000000 | 1000 | ffe86f77-1031-4b5f-8939-e37dffe74e7b 104 | 499604.000000000000 | 1000 | fff5f9d4-eb29-4c91-9fab-a0c6f75067be 105 | 499605.000000000000 | 1000 | ff706172-8281-4458-bd14-6b1d83941a72 106 | 499606.000000000000 | 1000 | ffbdff1e-5f42-42ce-936d-8e56d402ebfe 107 | 499607.000000000000 | 1000 | ffdd02c9-ab40-4a1e-96df-4bb0fd085cba (10 rows) Time: 100.434 ms
从上可知，对于复杂SQL，分布式版的Lightdb性能远高于集中式版。
运行oracle或mysql模式
如果希望运行在oracle或mysql模式，可以按照lightdb开启mysql兼容模式和lightdb开启oracle兼容模式。