PolarDB MySQL HTAP：实时数据分析加速
1. 选择实验资源
本实验支持实验资源体验、开通免费试用、个人账户资源三种实验资源方式。
在实验开始前，请您选择其中一种实验资源，单击确认开启实验。
如果您选择的是实验资源体验，资源创建过程需要3～5分钟（视资源不同开通时间有所差异，ACK等资源开通时间较长）。完成实验资源的创建后，在实验室页面左侧导航栏中，单击云产品资源列表，可查看本次实验资源相关信息（例如子用户名称、子用户密码、AK ID、AK Secret、资源中的项目名称等）。
说明：实验环境一旦开始创建则进入计时阶段，建议学员先基本了解实验具体的步骤、目的，真正开始做实验时再进行创建。
如果您选择的是开通免费试用，下方卡片会展示本实验支持的试用规格，可以选择你要试用的云产品资源进行开通。您在实验过程中，可以随时用右下角icon唤起试用卡片。
说明：试用云产品开通在您的个人账号下，并占用您的试用权益。如试用超出免费试用额度，可能会产生一定费用。
阿里云支持试用的产品列表、权益及具体规则说明请参考开发者试用中心。
2. 创建资源
本步骤指导您如何创建云服务器ECS和云数据库PolarDB。
如果您已创建云服务器ECS和云数据库PolarDB，请您选择个人账户资源，并跳过本小节，直接进行实验操作即可。
本步骤仅作为参考使用，您可以根据需求自行选择配置。
如果您选择的是开通免费试用，参考以下步骤云服务器ECS和云数据库PolarDB。
在本实验页面下方卡片会展示本实验支持的试用规格，依次选择一个试用规格，单击立即试用。
在云服务器ECS试用开通页面，根据页面引导进行开通云服务器ECS，其中操作系统选择CentOS 7.7 64位，地域需要与云数据库PolarDB在同一地域、VPC及交换机下，其他配置根据您的需求自行选择即可。
在云数据库PolarDB试用开通页面，根据页面引导进行开通云数据库PolarDB MySQL Serverless，其中数据库类型选择MySQL，计费类型为Serverless，数据库版本号选择8.0，地域需要与云服务器ECS在同一地域、VPC及交换机下，其他配置根据您的需求自行选择即可。
如果您选择的是个人账户资源，参考以下步骤云服务器ECS和云数据库PolarDB。
创建云服务器ECS。
前往云服务器ECS控制台，在概览页面的我的资源区域，单击创建实例。
在云服务器ECS购买页面，根据页面引导进行开通云服务器ECS，其中操作系统选择CentOS 7.7 64位，地域需要与云数据库PolarDB在同一地域、VPC及交换机下，其他配置根据您的需求自行选择即可。
创建云数据库PolarDB。
前往云数据库PolarDB创建页面。
在云数据库PolarDB创建页面，根据页面引导进行开通云数据库PolarDB MySQL Serverless，其中数据库类型选择MySQL，数据库版本号选择8.0，地域需要与云服务器ECS在同一地域、VPC及交换机下，其他配置根据您的需求自行选择即可。计费问题请参考官方文档。
3. 实验环境准备
安装MySQL
执行如下命令，更新YUM源。
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
执行如下命令，安装MySQL。
sudo yum -y install mysql-community-server --enablerepo=mysql80-community --nogpgcheck
执行如下命令，查看MySQL版本号。
mysql -V
返回结果如下，表示MySQL安装成功。
执行如下命令，启动MySQL服务。
systemctl start mysqld
修改配置文件。
vim /etc/my.cnf
按i进入输入模式，将如下代码复制进入。
[mysqld] local_infile=1 [client] loose-local-infile=1
输入完成后按:wq，保存并退出。
基础环境配置
下载附件。
cd / wget https://labfileapp.oss-cn-hangzhou.aliyuncs.com/benchtpch.zip wget https://labfileapp.oss-cn-hangzhou.aliyuncs.com/host.txt wget https://labfileapp.oss-cn-hangzhou.aliyuncs.com/userdata.txt
安装解压软件，并对文件进行解压。
yum -y install unzip unzip benchtpch.zip
进入PolarDB控制台，选择所创建的集群>账号管理>创建账号。
数据库账号：test_user
账号类型：普通账号
账号密码：Password123
进入polarDB基本信息页>数据库节点部分>增删节点>增加节点。
节点配置信息如下，点击增加一个节点>节点2【2核8G】，列存索引为开启。
4. 配置PolarDB MySQL集群
本步骤将指导您在PolarDB MySQL集群上创建用于压测的数据库和账号，并配置白名单实现ECS访问。
登录PolarDB控制台。
双击打开远程桌面的Chromium网页浏览器。
在RAM用户登录框中单击下一步，并复制粘贴页面左上角的子用户密码到用户密码输入框，单击登录。
复制下方地址，在Chromium网页浏览器打开新页签，粘贴并访问云数据库PolarDB控制台。
https://polardb.console.aliyun.com/
在集群列表页面顶部菜单栏，选择资源所在地域。例如下图中，地域切换为华东2（上海）。
说明 ：您可以在云产品资源列表中查看到PolarDB实例所在地域。
在集群列表页面，找到实验分配的PolarDB集群，单击集群ID，进入集群基本信息页。
说明 ：您可以在云产品资源列表中查看到PolarDB实例ID。
创建测试数据库。
在左侧导航栏中，单击配置与管理>数据库管理，可以创建数据库。
本实验已经预先创建好需要的测试数据库，您可以通过云产品资源列表信息以及控制台查看到数据库名称，无需手动创建测试数据库。数据库手工创建步骤如下。
创建数据库参数说明：
数据库（DB）名称：输入数据库名称，例如tpch1g。
支持字符集：默认设为utf8mb4。
授权账号：选择上一步创建的账号，例如test_user。
账号权限：选择要授予账号的权限为读写。
备注说明：非必填。用于备注该数据库的相关信息，便于后续数据库管理，最多支持256个字符。
创建账号及授权。
a. 在左侧导航栏中，选择配置与管理>账号管理，可以创建数据库帐号，并完成授权。
b. 本实验已经预先创建好需要的测试数据库账号，您可以通过云产品资源列表信息以及控制台查看到数据库账号、密码，无需手动创建测试数据库账号。数据库账号手工创建步骤如下。
创建数据库账号参数说明：
数据库账号：输入数据库账号名称，例如test_user。
账号类型：选择普通账号。
密码：输入账号密码，例如Password123。
确认密码：再次输入账号密码。
白名单授权。
为确保数据安全，云原生数据库PolarDB默认采用白名单策略，仅支持白名单内的客户端访问。您可以通过实验室云产品资源列表查看已经预创建用于访问测试的ECS实例内网IP地址信息，并将服务器IP添加至PolarDB白名单分组内。
说明 ：本实验已经自动将测试ECS服务器的内网IP添加至RDS Serverless实例白名单，您无序手动操作。
获取PolarDB集群访问地址。
本实验使用的ECS服务器通过PolarDB集群内网地址访问数据库，可以通过PolarDB集群的基本信息>数据库代理企业通用版>集群地址，查看PolarDB集群的内网地址。
说明：本实验需要使用的PolarDB实例内网连接地址信息，已经通过云产品资源列表提供，您可以快速复制。
5. 开启行/列存自动分流，并申请公网集群地址
本步骤将指导您为PolarDB MySQL集群开启行/列存自动分流。
在基本信息页面的数据库代理企业通用版区域，单击集群地址名称右侧的编辑配置。
在编辑地址配置面板中，设置读写模式为可读可写（自动读写分离），服务节点选择主节点、只读节点和只读列存节点，选择行存/列存自动引流为开启，然后单击确定。
说明 ：
在服务节点中，默认情况下，已选择主节点、只读节点和上一步添加的只读列存节点，请确认选择是否正确。
您可在基本信息页面的数据库节点区域，查看主节点、只读节点和只读列存节点的集群地址。
6. 创建测试表
本步骤将指导您通过DMS为PolarDB MySQL集群创建实验测试用的数据表。
由于本实验中涉及PolarDB MySQL数据库内操作以及ECS服务器内的命令，故下文引导客户开启两个会话：会话1和会话2，避免退出数据库这样的操作。
在实验室页面右侧，单击
图标，切换至Web Terminal，即ECS终端。 说明：Web Terminal打开后，已自动通过ECS的公网IP地址连接至ECS并开启一个会话1。
在ECS终端会话1中，执行以下命令连接到PolaDB MySQL 集群。
/usr/bin/mysql --host <连接地址> --port <端口> -u<用户名> -p<密码>
其中，命令中的如下参数要根据PolarDB MySQL集群的实际信息输入：
参数 |
说明 |
host |
PolarDB MySQL集群的私网连接地址。 |
port |
PolarDB MySQL集群的端口号，PolarDB MySQL默认为3306。 |
u |
数据库用户名。本实验预创建的测试用户为test_user。 |
-p |
用户名对应的密码。本实验预创建的用户密码为Password123。 |
执行以下命令切换到tpch1g数据库。
use tpch1g;
在tpch1g数据库中，执行下方的建表语句，创建8个列存测试表，表名称分别为region、nation、part、supplier、partsupp、customer、orders、lineitem。说明：建表语法与一般的MySQL语法一致，只需要在table comment中加入"COLUMNAR=1"字样，建出来的表即会包含列存索引。
CREATE TABLE region ( r_regionkey BIGINT NOT NULL, r_name CHAR(25) NOT NULL, r_comment VARCHAR(152), PRIMARY KEY (r_regionkey)) COMMENT 'COLUMNAR=1'; CREATE TABLE nation ( n_nationkey BIGINT NOT NULL, n_name CHAR(25) NOT NULL, n_regionkey BIGINT NOT NULL, n_comment VARCHAR(152), PRIMARY KEY (n_nationkey)) COMMENT 'COLUMNAR=1'; CREATE TABLE part ( p_partkey BIGINT NOT NULL, p_name VARCHAR(55) NOT NULL, p_mfgr CHAR(25) NOT NULL, p_brand CHAR(10) NOT NULL, p_type VARCHAR(25) NOT NULL, p_size BIGINT NOT NULL, p_container CHAR(10) NOT NULL, p_retailprice DECIMAL(15,2) NOT NULL, p_comment VARCHAR(23) NOT NULL, PRIMARY KEY (p_partkey)) COMMENT 'COLUMNAR=1'; CREATE TABLE supplier ( s_suppkey BIGINT NOT NULL, s_name CHAR(25) NOT NULL, s_address VARCHAR(40) NOT NULL, s_nationkey BIGINT NOT NULL, s_phone CHAR(15) NOT NULL, s_acctbal DECIMAL(15,2) NOT NULL, s_comment VARCHAR(101) NOT NULL, PRIMARY KEY (s_suppkey)) COMMENT 'COLUMNAR=1'; CREATE TABLE partsupp ( ps_partkey BIGINT NOT NULL, ps_suppkey BIGINT NOT NULL, ps_availqty BIGINT NOT NULL, ps_supplycost DECIMAL(15,2) NOT NULL, ps_comment VARCHAR(199) NOT NULL, PRIMARY KEY (ps_partkey, ps_suppkey)) COMMENT 'COLUMNAR=1'; CREATE TABLE customer ( c_custkey BIGINT NOT NULL, c_name VARCHAR(25) NOT NULL, c_address VARCHAR(40) NOT NULL, c_nationkey BIGINT NOT NULL, c_phone CHAR(15) NOT NULL, c_acctbal DECIMAL(15,2) NOT NULL, c_mktsegment CHAR(10) NOT NULL, c_comment VARCHAR(117) NOT NULL, PRIMARY KEY (c_custkey)) COMMENT 'COLUMNAR=1'; CREATE TABLE orders ( o_orderkey BIGINT NOT NULL, o_custkey BIGINT NOT NULL, o_orderstatus CHAR(1) NOT NULL, o_totalprice DECIMAL(15,2) NOT NULL, o_orderdate DATE NOT NULL, o_orderpriority CHAR(15) NOT NULL, o_clerk CHAR(15) NOT NULL, o_shippriority BIGINT NOT NULL, o_comment VARCHAR(79) NOT NULL, PRIMARY KEY (o_orderkey)) COMMENT 'COLUMNAR=1'; CREATE TABLE lineitem ( l_orderkey BIGINT NOT NULL, l_partkey BIGINT NOT NULL, l_suppkey BIGINT NOT NULL, l_linenumber BIGINT NOT NULL, l_quantity DECIMAL(15,2) NOT NULL, l_extendedprice DECIMAL(15,2) NOT NULL, l_discount DECIMAL(15,2) NOT NULL, l_tax DECIMAL(15,2) NOT NULL, l_returnflag CHAR(1) NOT NULL, l_linestatus CHAR(1) NOT NULL, l_shipdate DATE NOT NULL, l_commitdate DATE NOT NULL, l_receiptdate DATE NOT NULL, l_shipinstruct CHAR(25) NOT NULL, l_shipmode CHAR(10) NOT NULL, l_comment VARCHAR(44) NOT NULL, PRIMARY KEY (l_orderkey, l_linenumber)) COMMENT 'COLUMNAR=1';
以上八张表依次创建完毕后，执行以下命令确认表是否创建成功。
show tables from tpch1g;
7. 导入测试数据
本步骤指导您通过云服务器ECS将测试数据导入到PolarDB MySQL集群。
在当前ECS的Web Terminal页面右侧的导航按钮中，单击
内的加号，打开新的ECS会话作为会话2。新的会话自动通过公网地址连接至ECS 。
在ECS终端会话2中，参考说明修改如下命令中的参数，然后执行命令，将测试数据导入到PolarDB MySQL集群。
bash /root/benchtpch/tpch/data_kit.sh --parallel 2 --base /usr -s 1 -c 64 --data /root/benchtpch/tpchdata1g --database tpch1g --ddl /root/benchtpch/tpch/columnar.ddl --host <连接地址> --port <端口> -u <用户名> -p <密码> load
其中，命令中的部分参数需要根据PolarDB MySQL集群的实际信息输入：
参数 |
说明 |
--database |
数据库名称，本实验预创建的数据库tpch1g。 |
--host |
PolarDB MySQL集群的私网连接地址。 |
--port |
PolarDB MySQL集群的端口号，默认为3306。 |
-u |
数据库用户名。本实验预创建的用户为test_user。 |
-p |
用户名对应的密码。本实验预创建的密码为Password123。 |
如下即表示正在导入测试数据。
说明：导入数据大约需要5 ~ 10分钟，请耐心等待。
8. 行存/列存查询测试
本步骤将指导您如何在PolarDB MySQL集群中进行单表聚合查询、多表关联查询和点查，测试列存索引作用。
连接数据库。
执行如下命令，连接到PolarDB集群。
/usr/bin/mysql --host <连接地址> --port <端口> -u<用户名> -p<密码>
执行如下命令，使用数据库tpch1g。
use tpch1g;
单表聚合查询测试。
执行如下查询SQL语句。
select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year and l_discount between .06 - 0.01 and .06 + 0.01 and l_quantity < 24;
返回结果如下，您可以看到，该查询SQL语句的查询时间为0.64秒。
执行如下SQL语句，查询上一步SQL语句的执行计划。
explain select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year and l_discount between .06 - 0.01 and .06 + 0.01 and l_quantity < 24;
返回结果如下，您可以从“IMCI Execution Plan”字样可知，该查询SQL语句使用了列存索引加速。
执行如下SQL语句，关闭列存索引加速，即使用行存索引。
set use_imci_engine = off;
重新执行以上查询，并查询对应的执行计划，可发现，关闭列存索引加速后，该查询使用了原生MySQL的索引进行查询，即行存索引，且行存索引下的查询时间为22.55秒。
select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year and l_discount between .06 - 0.01 and .06 + 0.01 and l_quantity < 24; explain select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year and l_discount between .06 - 0.01 and .06 + 0.01 and l_quantity < 24;
返回结果如下，您可以看到，该查询SQL语句的查询时间为22.55秒。
多表关联查询测试。
执行如下SQL语句，开启列存索引。
set use_imci_engine = on;
执行如下查询SQL语句。
select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15' and l_shipdate > date '1995-03-15' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10;
返回结果如下，您可以看到，该查询SQL语句的查询时间为1.08秒。
执行如下命令，查询以上命令的执行计划。
explain select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15' and l_shipdate > date '1995-03-15' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10;
返回结果如下，您可以从“IMCI Execution Plan”字样可知，该查询SQL语句使用了列存索引加速。
执行如下SQL语句，关闭列存索引加速，即使用行存索引。
set use_imci_engine = off;
重新执行以上查询，并查询对应的执行计划，可发现，关闭列存索引加速后，该查询使用了原生MySQL的索引进行查询，即行存索引，且行存索引下的查询时间为20.96秒。
select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15' and l_shipdate > date '1995-03-15' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10; explain select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15' and l_shipdate > date '1995-03-15' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10;
返回结果如下，您可以看到，该查询SQL语句的查询时间为20.96秒。
进行点查测试。
执行如下SQL语句，开启列存索引。
set use_imci_engine = on;
执行如下查询SQL语句。
select * from lineitem where l_orderkey = 1;
返回结果如下，您可以看到，普通点查的查询时间很短。
执行如下SQL语句，查询上一步SQL语句的执行计划。
explain select * from lineitem where l_orderkey = 1;
返回如下结果，您可以看到结果中未带有“IMCI Execution Plan”字样，即该查询SQL语句使用了原生MySQL的索引（即行存索引）进行查询。
9. 实验总结
PolarDB MySQL支持行存和列存自动分流。
当执行的SQL请求的扫描行数超过一定数量时（例如步骤六中执行的单表聚合查询和多表关联查询测试），数据库代理可自动将该请求分流至只读列存节点处理；反之（例如步骤六中执行的点查测试）该请求则由行存节点或主节点来处理。
列存索引功能对查询性能提升明显。
列存索引功能对复杂的SQL查询操作（例如步骤六中执行的单表聚合查询和多表关联查询）有明显的加速作用，查询性能甚至可以提升百倍。
10. 释放资源
在完成实验之后，若您想保留实验相关的云产品资源，请随时关注账户扣费情况。若您不想保留实验相关的云产品资源，释放云产品资源即可。
实验链接：https://developer.aliyun.com/adc/scenario/3034d7cb2f3d4224b65e932078b2001b
