使用行列分流实现OLAP的复杂查询

1. 登录管理控制台。
2. 单击管理控制台左上角的，选择区域和项目。
3. 在页面左上角单击，选择“数据库 > 云数据库 TaurusDB”。
4. 在“实例管理”页面的实例列表中，选择目标实例，单击实例名称进入实例概览页面。
5. 在左侧导航栏选择“HTAP实时分析”，单击“创建HTAP实例”，进入创建HTAP实例页面。
6. 配置HTAP标准版实例参数。
   • 基础配置
     图1 基础配置
     

     表3 基础配置

     参数	参数说明
     计费模式	支持“按需计费”及“包年/包月”。

   • 资源选配
     图2 资源选配
     

     表4 资源选配参数说明

     参数	说明
     实例系列	标准版：基于开源StarRocks进行深度二次开发的版本。
     HTAP实例类型	支持单机和集群。 单机：只有一个FE节点与一个BE节点。单机版仅适用于功能体验和测试，不承诺SLA。 集群：最少三个FE节点或BE节点，最多10个FE节点或BE节点。
     存储类型	支持极速型SSD和超高IO。 极速型SSD：极速型SSD云盘，结合25GE网络和RDMA技术，为您提供单盘最大吞吐量达1000 MB/s，并具有亚毫秒级低时延性能。 超高IO：采用多磁盘条带化技术，将IO负载均衡到多块磁盘上以提升读写带宽，最大吞吐量为1.7GB/s。
     可用区类型	目前支持单可用区类型。
     可用区	选择目标可用区。
     时区	由于世界各国家与地区经度不同，地方时也有所不同，因此会划分为不同的时区。时区可在创建实例时选择，后期不可修改。
     内网安全组	默认为TaurusDB内网安全组，建议与TaurusDB保持一致。

   • 实例选配
     图3 实例选配
     

     表5 实例选配参数说明

     参数	说明
     性能规格	支持通用增强型。
     BE节点规格	选择BE节点规格。 BE节点是标准版HTAP的后端节点，主要负责数据存储和SQL计算等工作。
     BE节点存储	设置BE节点能够使用的存储空间。默认存储空间为50GB，最大可扩容至32000GB。
     BE节点数量	单机版实例只有1个BE节点。 集群版实例可设置3~10个BE节点，单次最多可批量申请10个节点。
     FE节点规格	选择FE节点规格。 FE节点是标准版HTAP的前端节点，主要负责管理元数据、管理客户端连接、进行查询规划、查询调度等工作。
     FE节点存储	设置FE节点能够使用的存储空间。默认存储空间为50GB，最大可扩容至1000GB。
     FE节点数量	单机版实例只有1个FE节点。 集群版实例可设置3~10个FE节点，单次最多可批量申请10个节点。

   • 实例配置
     图4 实例配置
     

     表6 实例配置参数说明

     参数	说明
     实例名称	实例名称长度最小为4个字符，最大为64个字符，必须以字母开头，区分大小写，可以包含字母、数字、中划线、下划线或中文，不能包含其他特殊字符。
     管理员密码	所设置的密码长度为8~32个字符，至少包含大写字母、小写字母、数字、特殊字符三种字符的组合，其中允许输入~!@#%^*-_=+?,()&$|.特殊字符。请您输入高强度密码并定期修改，以提高安全性，防止出现密码被暴力破解等安全风险。
     确认密码	必须和管理员密码相同。

   • 购买时长与数量
     图5 购买时长与数量
     

     表7 购买时长与数量参数说明

     参数	说明
     购买时长（包年/包月）	选择所需的时长，系统会自动计算对应的配置费用，时间越长，折扣越大。
     自动续费（包年/包月）	默认不勾选，不进行自动续费。 勾选后实例自动续费，自动续费周期与原订单周期一致。
     购买数量	当前仅支持购买1个实例，不支持批量购买。

7. 参数配置完成后，单击右下角“立即创建”。
   • 对于按需计费的实例，执行8。
   • 对于包年/包月模式的实例，执行9。
8. 确认信息无误后，单击“提交”。

   如需重新修改实例信息，单击“上一步”。

9. 进行订单确认。
   • 如果需要重新修改实例信息，单击“上一步”。
   • 如果订单确认无误，单击“去支付”，在订单页面完成支付即可。
10. 实例创建成功后，用户可在“HTAP实时分析”页面对其进行查看和管理。

1. 在“实例管理”页面的实例列表中，选择目标TaurusDB实例，单击实例名称进入实例概览页面。
2. 在左侧导航栏选择“HTAP实时分析”。
3. 单击目标HTAP实例名称，进入实例的“基本信息”页面。
4. 在左侧导航栏选择“数据同步”，单击“创建数据同步”。
5. 在创建数据同步页面，进行参数设置。
   图6 创建数据同步
   

   表8 创建数据同步参数

   参数	说明
   从只读节点同步数据	是否从目标只读节点进行全量数据同步。全量同步过程中需保证只读节点可用，否则全量同步失败后需重新开始同步。 是：会从目标只读节点进行全量数据同步，以避免全量同步阶段对主节点产生查询压力。如果仅有一个只读节点，默认选择该节点为目标节点。 否：不进行同步。
   实例级同步	同步一个或多个数据库。 是：一个数据同步任务可以同步多个库或者全部库。 否：一个数据同步任务仅仅只同步一个数据库。
   库同步范围	仅在“实例级同步”选择“是”时显示。 所有库：默认同步所有数据库，不需要指定任何数据库名。 部分库：需要指定2个及以上的数据库名。
   同步任务名	同步任务名在3到128个字符之间，由字母、数字、下划线组成，不能包含其他特殊字符。
   目标库	目标库在3到128个字符之间，由字母、数字、下划线组成，不能包含其他特殊字符。 当“库同步范围”选择“所有库”时，“目标库”选项不展示。 开启行列自动分流时，源库名称和目标库名称需保持一致。
   库同步设置	单击下拉列表选择需要进行数据同步的目标库，可根据实际需求修改HTAP节点的数据库参数。当“库同步范围”选项选择“所有库”时，下拉列表将隐藏。
   同步范围	根据实际情况选择同步范围，支持对所有表或部分表进行同步。
   表名支持通配符	只有在“实例级同步”选择“是”，同步范围选择“部分表”时显示，库名/表名支持通配符。 实例级同步场景可以选择黑白名单中表名是否支持通配符，通配符只支持“*”和“?”字符。 “*”通配符表示匹配零个或多个任意字符，“?”通配符表示匹配一个任意字符。
   同步范围 : 白名单	当同步范围选择部分表时，需要设置黑白名单的同步范围。 白名单和黑名单设置可根据实际需要选择其一进行设置，无法同时配置。如配置白名单，则只同步白名单中选择的表。如配置黑名单，则不同步黑名单中选择的表。 需要同步的表，需要包含主键或非空unique key，否则无法同步到HTAP节点。 后台数据合并任务及查询过程中可能额外使用磁盘空间，建议保留50%的空闲磁盘空间供系统额外使用。 在设置表黑白名单时可以在搜索框中一次性输入多张表，表之间可以通过逗号(“,”)、空格(“ ”)、换行(“\n”)分隔。在多表输入之后，单击，与库中表相匹配的表会被默认勾选，出现在已选表中。
   表同步设置	开启或关闭表同步设置。 开启：左侧选择已经同步的表进行相应的列操作，支持order by, key columns, distributed by, partition by, data_model，buckets， replication_num，enable_persistent_index，如果同时设置多个操作，操作之间通过分号连接。 详细的语法可以参考表9。 关闭：不需要进行“表同步设置”。

   表9 操作语法

   操作类型	具体语法
   order by	order by(column1, column2)或者order by column1,column2
   key columns	key columns(column1, column2)或者key columns column1,column2
   distributed by	distributed by (column1, column2) buckets 3 说明： buckets可选，如果不设置采用默认值。
   partition by	分为表达式分区，List分区等。详见分区语法示例。
   data_model	用于指定表类型，取值范围可以是主键表（primary key），明细表（duplicate key）或者更新表（unique key）。 具体语法如下： data_model=primary key或者data_model=duplicate key或者data_model=unique key
   replication_num	replication_num=3 说明： 取值不能超过BE节点数，否则会校验失败。
   enable_persistent_index	表示是否持久化索引，具体语法如下： enable_persistent_index=true或者enable_persistent_index=false
   组合场景	data_model=duplicate key;key columns column1, column2;

   分区语法示例：

   表达式分区，仅需在建表时设置分区表达式（时间函数表达式或列表达式）。在数据导入时，HTAP会根据数据和分区表达式的定义规则自动创建分区。

   时间函数表达式分区：如果经常按照连续日期范围来查询和管理数据，则只需要在时间函数分区表达式中，指定一个日期类型（DATE或DATETIME）的分区列，以及指定分区粒度（年、月、日、小时），HTAP会根据导入的数据和分区表达式，自动创建分区并设置分区的起止时间。

   语法：

   PARTITION BY expression
   ...
   [ PROPERTIES( 'partition_live_number' = 'xxx' ) ]
   
   expression ::=
       { date_trunc ( <time_unit> , <partition_column> ) |
         time_slice ( <partition_column> , INTERVAL <N> <time_unit> [ , boundary ] ) }

   表10 参数说明

   参数	是否必填	说明
   expression	是	目前仅支持date_trunc和time_slice函数。并且如果您使用 time_slice函数，则可以不传入参数boundary，因为在该场景中该参数默认且仅支持为floor，不支持为ceil。
   time_unit	是	分区粒度，目前仅支持为hour、day、month或year，暂时不支持为week。如果分区粒度为hour，则仅支持分区列为DATETIME类型，不支持为DATE类型。
   partition_column	是	分区列。 仅支持为日期类型（DATE或DATETIME），不支持为其它类型。如果使用date_trunc函数，则分区列支持为DATE或DATETIME类型。如果使用time_slice函数，则分区列仅支持为DATETIME类型。分区列的值支持为NULL。 如果分区列是DATE类型，则范围支持为[0000-01-01 ~ 9999-12-31]。如果分区列是DATETIME类型，则范围支持为[0000-01-01 01:01:01 ~ 9999-12-31 23:59:59]。 目前仅支持指定一个分区列，不支持指定多个分区列。

   示例：假设您经常按天查询数据，则建表时可以使用分区表达式 date_trunc() ，并且设置分区列为 event_day ，分区粒度为 day，实现导入数据时自动按照数据所属日期划分分区。将同一天的数据存储在一个分区中，利用分区裁剪可以显著提高查询效率。

   CREATE TABLE site_access1 (
       event_day DATETIME NOT NULL,
       site_id INT DEFAULT '10',
       city_code VARCHAR(100),
       user_name VARCHAR(32) DEFAULT '',
       pv BIGINT DEFAULT '0'
   )
   DUPLICATE KEY(event_day, site_id, city_code, user_name)
   PARTITION BY date_trunc('day', event_day)
   DISTRIBUTED BY HASH(event_day, site_id);

   列表达式分区：如果您经常按照枚举值来查询和管理数据，则您只需要指定表示类型的列为分区列，HTAP会根据导入的数据的分区列值，来自动划分并创建分区。

   语法：

   PARTITION BY expression
   ...
   [ PROPERTIES( 'partition_live_number' = 'xxx' ) ]
   
   expression ::=
       ( <partition_columns> )
   
   partition_columns ::=
       <column>, [ <column> [,...] ]

   表11 参数说明

   参数	是否必填	说明
   partition_columns	是	分区列。 支持为字符串（不支持BINARY）、日期、整数和布尔值。不支持分区列的值为NULL。 导入后自动创建的一个分区中只能包含各分区列的一个值，如果需要包含各分区列的多值，请使用List分区。

   示例：假设经常按日期范围和特定城市查询机房收费明细，则建表时可以使用分区表达式指定分区列为日期 dt 和城市 city。这样属于相同日期和城市的数据分组到同一个分区中，利用分区裁剪可以显著提高查询效率。

   CREATE TABLE t_recharge_detail1 (
       id bigint,
       user_id bigint,
       recharge_money decimal(32,2), 
       city varchar(20) not null,
       dt varchar(20) not null
   )
   DUPLICATE KEY(id)
   PARTITION BY (dt,city)
   DISTRIBUTED BY HASH(`id`);

   List 分区

   数据按照您显式定义的枚举值列表进行分区，适用于按枚举值来查询和管理数据。您需要显式列出每个 List 分区所包含的枚举值列表，并且值不需要连续。

   List分区适用于存储具有少量枚举值列的数据、并且经常按列的枚举值来查询和管理数据的场景。例如表示地理位置、状态、类别的列。列的每个值都代表一种独立的类别。按照列的枚举值对数据进行分区，可以提高查询性能和方便数据管理。尤其适用于一个分区中需要包含各分区列的多个值的场景。例如表中存在 City 列表示个体所属的城市，并且您经常按照州和城市查询和管理数据，则建表时可以使用 City 列作为分区列进行 List 分区，指定同属一个州的多个城市的数据分在同一分区 PARTITION pCalifornia VALUES IN ("Los Angeles","San Francisco","San Diego")，可以加速查询和方便数据管理。

   

   必须在建表时就创建分区，导入时不支持自动创建分区，如果表中不存在数据对应的分区，会报错。

   语法：

   PARTITION BY LIST (partition_columns)（
       PARTITION <partition_name> VALUES IN (value_list)
       [, ...]
   )
   
   partition_columns::= 
       <column> [,<column> [, ...] ]
   
   value_list ::=
       value_item [, value_item [, ...] ]
   
   value_item ::=
       { <value> | ( <value> [, <value>, [, ...] ] ) }    

   表12 参数说明

   参数	是否必填	说明
   partition_columns	是	分区列。 分区列的值支持为字符串（除BINARY）、日期（DATE和DATETIME）、整数和布尔值。分区列的值不支持为NULL。
   partition_name	是	分区名称。 建议您按照业务场景设置合理的分区名称，便于区别不同分区包含的数据分类。
   value_list	是	分区中分区列的枚举值列表。

   示例一：假设经常按照州或城市查询机房收费明细，则建表时可以指定分区列为城市city ，并且指定每个分区所包含城市同属一个州，这样可以加速查询特定州或城市的数据，并且方便按照特定州或城市进行数据管理。

   CREATE TABLE t_recharge_detail2 (
       id bigint,
       user_id bigint,
       recharge_money decimal(32,2), 
       city varchar(20) not null,
       dt varchar(20) not null
   )
   DUPLICATE KEY(id)
   PARTITION BY LIST (city) (
      PARTITION pCalifornia VALUES IN ("Los Angeles","San Francisco","San Diego"), -- 这些城市同属一个州
      PARTITION pTexas VALUES IN ("Houston","Dallas","Austin")
   )
   DISTRIBUTED BY HASH(`id`);

   示例二：假设经常按日期范围和特定州或城市查询机房收费明细，则建表时可以指定分区列为日期dt和城市city。这样属于特定日期和特定州或城市的数据分组到同一个分区中，以加速查询和方便数据管理。

   CREATE TABLE t_recharge_detail4 (
       id bigint,
       user_id bigint,
       recharge_money decimal(32,2), 
       city varchar(20) not null,
       dt varchar(20) not null
   ) ENGINE=OLAP
   DUPLICATE KEY(id)
   PARTITION BY LIST (dt,city) (
      PARTITION p202204_California VALUES IN (
          ("2022-04-01", "Los Angeles"),
          ("2022-04-01", "San Francisco"),
          ("2022-04-02", "Los Angeles"),
          ("2022-04-02", "San Francisco")
       ),
      PARTITION p202204_Texas VALUES IN (
          ("2022-04-01", "Houston"),
          ("2022-04-01", "Dallas"),
          ("2022-04-02", "Houston"),
          ("2022-04-02", "Dallas")
      )
   )
   DISTRIBUTED BY HASH(`id`);

6. 配置完成后，单击“创建同步任务”。
7. 操作成功后，单击“返回同步列表”，返回数据同步页面。会生成一条待同步的数据同步任务。此时该任务的状态为“同步阶段：等待同步”。如需启动此任务，需单击操作列“同步”。

   当HTAP实例的状态显示为“同步阶段：增量同步中”，表示该实例数据同步状态完成。

   图7 查看任务状态
   
   

   在全量同步过程中，若部分表同步异常，实例会产生告警，同时跳过异常表，继续同步后续的表，同步完成后进入增量同步状态。可在增量同步阶段修复异常表。

   业务测试时，若想暂停数据同步可以通过暂停按钮暂停任务，但暂停的时间不能超过源TaurusDB主实例的Binlog保留时长。若超过，则同步任务不能继续同步，只能删除重建。生产环境禁止暂停同步任务，防止导致OLTP和OLAP数据不一致。

行列分流支持自动分流和手动分流两种方式。

方式一：自动分流

1. 在HTAP实例列表页面，单击目标实例的名称，进入实例的“基本信息”页面。
2. 在左侧导航栏中，单击“数据库代理”，进入数据库代理页面。
   图8 数据库代理页面
   
3. 单击“新增代理”。
4. 在“新增数据库代理服务”弹框中，设置相关参数。

   表13 参数说明

   参数名称	说明
   代理实例名称	代理实例名称长度最小为4个字符，最大为64个字符且不超过64个字节（一个中文字符占用3个字节），必须以字母或中文开头，区分大小写，可以包含字母、数字、中划线、下划线或中文，不能包含其他特殊字符。
   代理模式	仅支持读写模式。 读写模式：所有写请求只发往主节点，所有读请求按照读权重配比分发到已选节点。主节点的读权重值默认为100。
   一致性级别	仅支持最终一致性。 最终一致性：如果需要减轻主节点压力，让尽量多的读请求路由到只读节点，您可以选择最终一致性。对数据的读取仅保证最终的结果是一致的，不保证能立即读取更新后的数据。对主节点的压力最小，性能最好。只读模式由于无法进行写操作，仅支持最终一致性。
   路由模式	仅支持权重负载，根据您设置的读权重比例分发读请求。
   代理实例规格	根据实际需要选择代理实例规格。 鲲鹏通用计算增强型：2 vCPUs | 4 GB、4 vCPUs | 8GB、8 vCPUs | 16 GB 通用增强型：2 vCPUs | 4 GB、4 vCPUs | 8 GB、8 vCPUs | 16 GB
   子网	开通读写分离时支持指定子网。 如需开通开启读写分离指定子网的权限，请提交工单。 如果TaurusDB实例所在子网为扩展网段，则不支持跨子网开启读写分离，需要与TaurusDB实例在同一子网。
   代理实例节点数量	默认为2个节点。最小支持2个节点，最大支持16个节点。 推荐代理实例节点数量 =（主节点CPU核数+所有只读节点CPU核数总和）/（4*代理实例CPU核数），计算结果向上取整。
   新节点自动加入	开启新节点自动加入开关后，新增的只读节点会自动添加到该数据库代理实例中。
   新节点权重	当路由模式为权重负载时，需要设置新节点的读权重。节点的读权重默认为100，读权重越高，处理的读请求越多。
   行存/列存自动引流	打开列存/行存自动引流功能后，您需要设置SQL语句的执行代价阈值，数据库代理将此阈值作为判断依据。 若业务侧请求的SQL语句的预估执行代价大于该阈值，则将该请求引流至列存节点（HTAP）执行；若小于或等于该阈值，则将该请求引流至行存节点（只读节点）执行。 代理模式为读写模式、一致性级别为最终一致性、路由模式为权重负载且子网与TaurusDB实例一致时，才允许设置行存/列存自动引流。
   选择数据库节点	选择主节点和需要参与处理请求的只读节点和HTAP节点。 至少需要选择一个只读节点和一个HTAP节点。 示例： 已选择1个主节点、1个只读节点、2个HTAP实例，读权重配置为100、100、100、300。 开启行存/列存自动引流后，写请求将引流至主节点，当SQL小于或等于阈值（假设当前阈值为50000）时，读请求将按照1：1的比例引流到主节点和只读节点，即主节点和只读节点分别处理50%的读请求；当SQL大于阈值（假设当前阈值为50000）时，将按照1：3的比例分别引流到两个HTAP节点，即两个HTAP实例分别处理25%和75%的读请求。

5. 单击“确定”。
6. 设置自动引流阈值。

   开启了列存/行存自动引流功能后，您需要设置SQL语句的实际执行代价阈值。阈值配置完成后，数据库代理将此阈值作为判断依据。若业务侧请求的SQL语句的预估执行代价大于该阈值，则将该请求引流至列存节点（HTAP）执行；若小于或等于该阈值，则将该请求引流至行存节点（只读节点）执行。

   当前SQL语句的实际执行代价阈值由下表中的参数决定，您可以在数据库代理的参数修改页面，根据自身业务情况修改参数的值，从而调整自动分流效果。

   表14 参数说明

   参数名称	描述
   looseImciApThreshold	分发至列存节点的SQL语句的执行代价阈值，默认为50000。 说明： 开启列存/行存自动引流功能后，如果SQL语句的执行代价大于阈值，则路由至HTAP节点。

   您可以通过直连主节点执行如下命令，精确查询上一条SQL语句的执行代价，从而判断如何调整上表中的参数值。

   explain format=json SQL命令

   示例：

   explain format=json select sbtest1.*,sbtest2.* from sbtest1 inner join sbtest2 where sbtest1.id < 100 and sbtest2.k > 11;

   

   从以上查询结果可以看出，该SELECT语句的执行代价为1.97。

   此时若要实现将该SQL查询分流至列存节点的列存计划执行，则需要将“looseImciApThreshold”参数的值设置为“1”。

方式二：手动分流

如果使用行存和列存自动分流没有达到预期效果，您可以使用HINT语法强制执行行存或列存执行计划。

• HINT语法仅对指定的SQL语句生效，对其它连接或同一个连接下的其它SQL语句没有影响
• 如果在5.7.7版本之前的MySQL客户端执行HINT语法，则在连接数据库引擎时需要添加--comments选项。您可以使用mysql --version命令来查看MySQL客户端版本。

• 使用数据库代理进行行存和列存自动分流时，还可以通过HINT语法使得数据库代理将SQL语句强制分发至列存节点执行，而不受“looseImciApThreshold”的影响。

  具体方式为：在SQL关键字前添加/* FORCE_IMCI_NODES */。

  例如：

  /*FORCE_IMCI_NODES*/ SELECT COUNT(*) FROM t1;

• 使用数据库代理进行行存和列存自动分流时，还可以通过HINT语法使得数据库代理将SQL语句强制分发至行存节点执行，而不受“looseImciApThreshold”的影响。

  具体方式为：在SQL关键字前添加/*FORCE_SLAVE*/或/*FORCE_MASTER*/。

  示例：

  发往只读行存节点：

  /*FORCE_SLAVE*/ SELECT COUNT(*) FROM t1;

  发往主节点：

  /*FORCE_MASTER*/ SELECT COUNT(*) FROM t1;

通过数据库代理地址连接TaurusDB实例并进行OLAP的查询。具体请参见通过JDBC连接TaurusDB实例。