数据库的自动扩缩容和自动SQL优化-阿里云帮助中心

本方案介绍如何用数据库自治服务DAS实现RDS MySQL数据库的自动扩缩容和自动SQL优化。

一键部署

https://www.aliyun.com/solution/tech-solution/adsa_sql

方案概览

随着业务的快速迭代和发展，数据库实例规模扩大，数据分布特征动态地发生变化，数据库管理和调优面临诸多挑战。

若数据库实例规格过高，会造成资源浪费；若规格过小，计算性能不足会影响业务。即使选择了合适的实例规格，线上流量突增导致数据库性能问题的情况也时有发生。

此外，传统的SQL优化过程完全依赖人力驱动，导致异常发现或响应不及时，而且人工处理问题耗时过长，既影响用户体验，又增加故障风险。

针对以上数据库运维的问题，本方案通过数据库自治服务DAS实现以下能力：

自动扩缩容：基于数据库实例的实时性能数据，发现流量异常并提供合理的数据库规格建议和磁盘容量建议，使数据库服务具备自动扩展存储和计算资源的能力。
自动SQL优化：将基于人工的被动式优化转变为基于智能的主动式持续优化，最终实现SQL优化的无人值守。

方案架构

方案提供的默认设置完成部署后在阿里云上搭建的网站运行环境如下图所示。实际部署时您可以根据资源规划修改部分设置，但最终形成的运行环境与下图相似。

本方案的技术架构包括以下基础设施和云服务：

1个专有网络VPC：为云资源形成云上私有网络。
1台云服务器ECS：用于模拟应用产生的负载。
1个云数据库RDS MySQL高可用版：具备主备高可用能力。
数据库自治服务：为云数据库RDS MySQL提供异常检测、自动扩缩容、自动SQL优化等能力。

部署准备

开始部署前，请按以下指引完成账号申请、账号充值。

准备账号

如果您还没有阿里云账号，请访问阿里云账号注册页面，根据页面提示完成注册。阿里云账号是您使用云资源的付费实体，因此是部署方案的必要前提。

为阿里云账号充值。

为节省成本，本方案默认选择使用按量付费资源，使用按量付费资源需要确保账户余额不小于100元。

按照建议规格完成本方案的部署及体验，预计产生费用不超过3元（假设资源运行时间不超过1小时）。如果您调整了资源规格、使用时长，或执行了本方案以外的操作，可能会导致费用发生变化，请以控制台显示的实际价格和最终账单为准。以下为本方案中需付费资源在杭州地域的单价。

方案付费资源单价

产品	计费项	单价（仅供参考）
云服务器ECS	实例规格与系统盘	约1.86元/小时
云数据库RDS	实例规格与存储空间	0.48元/小时
数据库自治服务DAS	SQL洞察和审计	0.008元/（GB*小时）

阿里云账号拥有操作资源的最高权限，从云资源安全角度考虑，建议您创建一个RAM用户，使RAM用户仅能操作有限的资源，然后由RAM用户完成本方案。
1. 访问资源组页面，单击创建资源组，创建一个资源组，例如设置名称为测试资源组。
2. 访问RAM控制台-用户页面，单击创建用户，勾选控制台访问和OpenAPI 调用访问，填写其他信息，创建一个RAM用户。
3. 访问RAM控制台-权限策略页面，单击创建权限策略，单击脚本编辑，并复制粘贴以下内容，完成创建自定义权限策略。
  自定义权限策略
```
{
  "Version": "1",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "ecs:Describe*",
        "vpc:Describe*",
        "ros:*"
      ],
      "Resource": "*"
    }
  ]
}
```
访问RAM控制台-用户页面，授予权限。
1. 单击目标RAM用户操作列的添加权限，选择授权范围为指定资源组（例如上云资源组），然后选择系统策略AliyunECSFullAccess、AliyunRDSFullAccess、AliyunVPCFullAccess、AliyunHDMFullAccess，单击确定。
2. 再次单击目标RAM用户操作列的添加权限，选择授权范围为整个云账号，然后选择前面创建的自定义权限策略，单击确定。

一键部署

一键部署模板将自动创建以下资源并构造测试数据。

1个专有网络VPC
1个虚拟交换机VSwitch
1个云服务器ECS

1个云数据库RDS

本模板会自动将ECS实例内网IP添加至RDS实例白名单，使ECS实例能通过内网访问RDS实例。
RDS实例中有数据库账号dbuser和数据库das_test。

数据库das_test中有一个students表，表中有200万行数据。

模板使用的建表和插入数据的代码

CREATE TABLE `students` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sid` char(64) NOT NULL COMMENT 'student id',
  `name` varchar(64) NOT NULL COMMENT 'name',
  `birth_date` date NOT NULL COMMENT 'birth date',
  `gender` char(16) NOT NULL COMMENT 'gender',
  `extra_info` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sid` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO students(sid, name, birth_date, gender, extra_info) VALUES(UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 30)),2,0)), 'female', UUID());
INSERT INTO students(sid, name, birth_date, gender, extra_info) VALUES(UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 30)),2,0)), 'male', UUID());
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);
INSERT INTO students(sid, name, birth_date, gender, extra_info) (select UUID(), SUBSTRING(UUID(), FLOOR(1+(RAND() * 18)), FLOOR(2+(RAND() * 16))), CONCAT(FLOOR(1990 + (RAND() * 20)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)), gender, UUID() FROM students limit 1000000);

操作步骤

单击一键部署模板进入ROS控制台。
在顶部选择华东1（杭州）。
设置ECS实例参数：选择任一可用区，在筛选条件中选择4 vCPU，选中一个4 vCPU、16GiB的规格，然后填写ECS实例密码。
设置RDS实例参数：选择mysql.n2.small.2c规格，然后填写数据库密码，其他参数可使用默认值或按需修改。
查看页面右下角的资源价格，确认无误后单击创建。
等待资源栈创建，资源部署时间约10分钟。当资源栈状态显示为创建成功时，表示部署完成。
单击资源页签，可以查看已创建的资源。
单击输出页签，可以查看输出关键字列表。

开启自治能力

在资源栈列表，单击刚创建的资源栈。
单击资源页签。
找到RdsInstance资源，单击资源名称。
在RDS实例的基本信息页，单击左侧菜单里的自治服务 > SQL洞察与审计，然后单击一键开启。
单击左侧菜单里的自治服务 > 一键诊断，单击自治中心页签，然后单击右侧的自治功能开关。
选中SQL诊断并自动索引创建。
勾选自动性能扩展和自动回缩，并设置观测窗口为5分钟，然后单击确定。

执行压测

在资源栈列表，单击刚创建的资源栈。
单击资源页签。
找到EcsInstance资源，单击资源名称。
单击远程连接按钮，在弹出的窗口中，单击通过Workbench远程连接下方的立即登录。
在弹出的窗口中，填写ECS实例密码进行登录。

登录成功后，执行以下命令进行压测。

cd /user/local/src/das-testbed-simple
java -jar das-testbed-simple.jar

完成及清理

方案验证

在RDS实例的基本信息页，单击左侧的一键诊断，然后单击自治中心页签。
可以看到1个优化事件，名称为慢SQL诊断(诊断优化)，该优化事件将在运维时间段内自动执行。
等CPU负载持续5分钟超过70%后，可以看到弹性伸缩事件，实例开始自动升配。
等CPU负载回落持续15分钟后（缩容观测窗口=观测窗口+10分钟），可以看到另一个弹性伸缩事件，实例开始自动降配。

清理资源

释放资源栈下的资源，包括ECS实例、RDS实例等。

在资源栈列表，找到目标资源栈，单击删除。
在删除资源栈对话框，选择删除方式为释放资源，然后单击确定，根据提示完成资源释放。