企业官网建设流程全解析

一、为什么我们需要自动化SQL调优？

SQL优化建议器能够根据数据库内部状态，向数据库管理员（DBA）提供调优建议。在应用这些优化建议后，经过调整的SQL语句性能将得到显著提升。

对于基于单条SQL语句的调优工作，系统可以直接生成并呈现具体的优化建议内容，无需额外进行任务管理或结果查询视图等操作，即可一次性获得全部调优结果。系统的调优任务采用阻塞方式运行以确保执行的稳定性和可靠性。

二、SQL优化建议器到底是个啥？

统计信息更新建议:

在执行SQL语句的过程中，可能会引用一个或多个关系表。如果这些表的统计信息未能及时更新，或者缺乏多列统计信息，可能导致查询优化器生成非最优的执行计划。

这一问题在表数据出现显著倾斜（即数据分布极不均衡）时尤为突出。针对输入的SQL语句，SQL调优建议器将分析其引用的所有关系表，并对每个表进行统计信息检查。

当检测到表中修改的数据量超过autovacuum的计算结果时，系统会自动生成收集统计信息的建议。

索引建议:

对单条SQL语句进行分析时，系统会检查其涉及的表在过滤或连接条件中是否被引用。如果发现相关表缺少必要的索引，则自动应用虚拟索引技术进行处理。通过对比执行计划前后的性能数据，若性能提升幅度达到5%以上，则将给出则给出索引建议、建议索引类型、创建索引的DDL语句、预期收益率等优化方案。

SQL改写建议:

通过对预设改写规则进行配置，系统能够识别符合条件的SQL语句并生成相应的优化建议

三、调优接口说明

报告通过以下接口，通过SQL语句、QUERY_ID指定语句生成。

3.1 调优报告接口 :

3.2 接口参数说明

四、调优报告生成的基本配置：别急着跑，先配好环境

为提升用户体验，SQL优化功能提供了专门针对数据库管理员（DBA）设计的调优建议报告接口。该报告内容涵盖以下几个方面：

1. GENERAL INFORMATION：一般信息

包括任务名称、任务所有者、状态、执行开始和完成时间、SQL ID和SQL语句。

2. FINGDING：调优发现

包括优化建议的动作：索引建议和收集统计信息会给出具体SQL，改写建议不会给出改写后的SQL。

预期收益：仅索引建议有， 收集统计信息和SQL改写没有。

基本原理：索引建议和收集统计信息有， 改写建议没有。

3. EXPLAIN PLANS：执行计划

优化前后执行计划对比（仅索引建议有， 收集统计信息和SQL改写没有）。

4.1 开启自动调优功能

金仓默认可能没有开启SQL调优相关的扩展，需要手动加载：

-- 检查是否已安装SELECT*FROMsys_extensionWHEREextname='sys_sqltune';-- 如果没有，创建扩展CREATEEXTENSIONIFNOTEXISTSsys_sqltune;

注意：这里要用sys_sqltune，不是其他名字。我第一次搞的时候，看网上有些资料写的名字不对，折腾了半天。

4.2 配置调优参数

在kingbase.conf配置文件中，有几个参数直接影响建议器的分析深度：

# 开启自动SQL调优 sys_sqltune.enabled = on # 设置调优任务的最大执行时间 秒 sys_sqltune.time_limit = 600 # 是否启用索引建议功能 sys_sqltune.index_advisor_enabled = on # 是否启用SQL改写建议 sys_sqltune.sql_rewrite_enabled = on # 设置建议器的详细程度，basic或comprehensive sys_sqltune.report_level = 'comprehensive'

改完配置记得重启数据库生效。我当时就是改完没重启，查了半天为什么配置不生效，真是低级错误。

4.3 创建调优用的辅助表

建议器需要一些工作表来存储中间结果，建议提前建好：

-- 创建调优任务记录表，如果不存在会自动创建，但手动建可以指定表空间CREATETABLEIFNOTEXISTSsys_sqltune_log(task_idSERIALPRIMARYKEY,task_nameVARCHAR(100),sql_textTEXT,create_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP,statusVARCHAR(20),report_contentTEXT)TABLESPACEsys_default;

表名一定要用sys_开头，比如sys_sqltune_log、sys_tuning_tasks，千万别用pg_前缀，那是违规的。

五、生成统计信息的更新建议：让优化器看清数据分布

配置文件kingbase.conf中设置

Autovacuum=off;-- 临时关闭统计信息自动更新

重启数据库

创建表、插入数据

CREATETABLEt1(id1INT);INSERTINTOt1SELECTgenerate_series(1,100000);

调用接口生成调优报告:

SELECTPERF.QUICK_TUNE_BY_SQL('SELECT * FROM t1');

六、生成索引的建议

索引建议是我用得最多的功能。金仓的建议器会分析SQL的WHERE条件、JOIN条件、ORDER BY子句，推荐最合适的索引。

6.1 单表查询的索引建议

继续用上面的sys_orders表，假设我们经常按user_id和status组合查询：

SELECTorder_id,amountFROMsys_ordersWHEREuser_id=9527ANDstatus='completed'ORDERBYorder_dateDESC;

跑一遍调优任务，报告里的索引建议部分可能是这样的：

索引建议 -------- 基于查询条件，建议在表 sys_orders 上创建复合索引： CREATE INDEX idx_sys_orders_user_status_date ON sys_orders(user_id, status, order_date); 理由： 1. 等值查询列 user_id 和 status 放在索引前导列 2. 排序列 order_date 放在最后，避免回表排序 3. 预计可将执行代价从 12345 降低至 234

6.2 多表关联的索引建议

实际业务中更常见的是多表关联。咱们再建个用户表：

CREATETABLEsys_users(user_idINTPRIMARYKEY,user_nameVARCHAR(50),register_dateTIMESTAMP,levelINT);-- 插入测试数据INSERTINTOsys_usersSELECTgenerate_series(1,10000),'user_'||generate_series(1,10000),CURRENT_TIMESTAMP-(random()*1000||' days')::INTERVAL,(random()*10)::INT;

现在执行关联查询：

SELECTo.order_id,o.amount,u.user_nameFROMsys_orders oJOINsys_users uONo.user_id=u.user_idWHEREo.status='completed'ANDu.level>5ANDo.order_date>'2024-01-01';

建议器可能会给出这样的建议：

索引建议 -------- 1. 表 sys_orders：创建索引 idx_sys_orders_status_date ON sys_orders(status, order_date, user_id) 2. 表 sys_users：在 level 列创建索引 idx_sys_users_level ON sys_users(level) 或考虑创建包含 user_name 的覆盖索引： ON sys_users(level, user_id, user_name)

我的经验：建议器推荐的索引不一定都要建，要结合业务实际。比如如果level列的区分度很低，只有0和1两个值，建索引反而可能更慢。建议器只是给建议，最终决策还得靠咱们DBA的判断。

七、生成SQL改写建议

有些SQL，即使加了索引、统计信息也最新，但就是因为写法问题导致性能差。金仓的建议器能识别出一些常见的低效写法并给出改写方案。

7.1 避免隐式类型转换

我见过太多人栽在这个坑上。比如：

-- 原始SQLSELECT*FROMsys_ordersWHEREuser_id='9527';

虽然user_id是INT类型，但传入字符串，数据库会做隐式类型转换，导致索引失效。建议器会提示：

SQL改写建议 ----------- 发现隐式类型转换可能影响性能。 建议改写为： SELECT * FROM sys_orders WHERE user_id = 9527;

7.2 优化IN子查询

IN子查询数据量大时性能很差，建议器会建议改成EXISTS或JOIN：

-- 原始SQLSELECT*FROMsys_ordersWHEREuser_idIN(SELECTuser_idFROMsys_usersWHERElevel=9);

建议器可能建议：

SQL改写建议 ----------- 当前IN子查询可能产生大量重复值扫描。 建议改写为： SELECT o.* FROM sys_orders o WHERE EXISTS ( SELECT 1 FROM sys_users u WHERE u.user_id = o.user_id AND u.level = 9 );

或者：

或者使用JOIN写法 SELECT DISTINCT o.* FROM sys_orders o JOIN sys_users u ON o.user_id = u.user_id WHERE u.level = 9;

7.3 优化LIMIT分页

深分页是性能杀手。比如：

-- 原始SQL，越往后越慢SELECT*FROMsys_ordersORDERBYorder_dateDESCLIMIT10OFFSET100000;

建议器会提示：

SQL改写建议 ----------- 深分页查询性能较差，建议使用键集分页或基于游标的分页。 改写方案，基于上次查询的最大值 SELECT * FROM sys_orders WHERE order_date < '上次最后一条的时间' ORDER BY order_date DESC LIMIT 10;

这些改写建议，说实话，有些我自己都没想到。特别是复杂的子查询优化，建议器给出的等价改写往往执行计划更优。

八、使用queryId生成建议

前面说的都是针对单条SQL的实时分析。但在生产环境，我们更常遇到的情况是：某个接口响应慢，但SQL是ORM生成的，咱们不知道具体SQL文本，只知道它在数据库里的queryId。

8.1 定位慢SQL的queryId

金仓的sys_stat_statements扩展会记录所有SQL的执行统计：

-- kingbase.conf开启统计收集sys_stat_statements.track=all-- 查询最耗时的SQLSELECTqueryid,query,calls,total_exec_time,mean_exec_time,rowsFROMsys_stat_statementsWHEREqueryNOTLIKE'%sys_stat_statements%'ORDERBYmean_exec_timeDESCLIMIT10;

假设咱们发现queryId为123456789012345678的SQL平均执行时间很长。

8.2 基于queryId创建调优任务

-- 创建任务SELECTsys_sqltune.create_tuning_task(query_id=>123456789012345678,task_name=>'prod_slow_query_001',time_limit=>600)FROMdual;-- 执行EXECsys_sqltune.execute_tuning_task('prod_slow_query_001');-- 获取详细报告SELECTsys_sqltune.report_tuning_task('prod_slow_query_001')INTOreport_content;

8.3 实际案例分享

上个月我就遇到个生产问题，有个报表接口超时。通过queryId定位到这条SQL：

-- 这是ORM生成的，实际执行计划很烂SELECTCOUNT(*)FROMsys_orders oLEFTJOINsys_users uONo.user_id=u.user_idWHEREo.create_time>CURRENT_DATE-INTERVAL'30 days'AND(o.status='pending'ORo.status='processing');

用建议器分析后，它给出的建议

1. 统计信息：sys_orders的create_time列统计信息缺失
2. 索引：建议创建(create_time, status, user_id)的复合索引
3. SQL改写：建议把OR条件改写成IN列表，并提示当前写法可能导致索引扫描范围过大

按照建议优化后，这条SQL从8秒降到了150毫秒。最爽的是，我根本不用去理解那条超复杂的ORM生成SQL的具体逻辑，建议器直接告诉我哪里有问题。

九、从建表到优化全流程

为了让大家更直观地理解整个流程，我模拟一个完整的业务场景。

9.1 场景设定

假设咱们有个电商系统，需要分析最近半年的订单数据，关联用户信息和商品类别。

9.2 建表和造数据

-- 订单表CREATETABLEsys_order_master(order_idBIGINTPRIMARYKEY,user_idINTNOTNULL,category_idINT,order_amountDECIMAL(12,2),order_statusSMALLINT,-- 0:待支付 1:已支付 2:已发货 3:已完成 4:已取消create_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP,pay_timeTIMESTAMP);-- 用户表CREATETABLEsys_user_info(user_idINTPRIMARYKEY,user_nameVARCHAR(50),register_timeTIMESTAMP,user_levelSMALLINTDEFAULT1,is_vipBOOLEANDEFAULTFALSE);-- 商品类别表CREATETABLEsys_category(category_idINTPRIMARYKEY,category_nameVARCHAR(50),parent_idINT);-- 插入测试数据INSERTINTOsys_order_masterSELECTgenerate_series(1,1000000),(random()*100000)::INT+1,(random()*100)::INT+1,(random()*10000)::DECIMAL(12,2),(random()*5)::SMALLINT,CURRENT_TIMESTAMP-(random()*180||' days')::INTERVAL,CASEWHENrandom()>0.3THENCURRENT_TIMESTAMP-(random()*180||' days')::INTERVALELSENULLENDFROMgenerate_series(1,1000000);INSERTINTOsys_user_infoSELECTgenerate_series(1,100000),'user_'||generate_series(1,100000),CURRENT_TIMESTAMP-(random()*365||' days')::INTERVAL,(random()*5)::SMALLINT+1,random()>0.8FROMgenerate_series(1,100000);INSERTINTOsys_categorySELECTgenerate_series(1,100),'category_'||generate_series(1,100),CASEWHENgenerate_series>10THEN(random()*10)::INT+1ELSE0ENDFROMgenerate_series(1,100);

9.3 模拟慢SQL

业务需要查询：最近30天，VIP用户的已完成订单，按类别汇总金额。

SELECTc.category_name,COUNT(*)asorder_count,SUM(o.order_amount)astotal_amount,AVG(o.order_amount)asavg_amountFROMsys_order_master oJOINsys_user_info uONo.user_id=u.user_idJOINsys_category cONo.category_id=c.category_idWHEREo.create_time>CURRENT_DATE-INTERVAL'30 days'ANDo.order_status=3ANDu.is_vip=TRUEGROUPBYc.category_nameORDERBYtotal_amountDESC;

9.4 执行调优分析

-- 创建任务SELECTsys_sqltune.create_tuning_task(sql_text=>'SELECT c.category_name, COUNT(*) as order_count, SUM(o.order_amount) as total_amount, AVG(o.order_amount) as avg_amount FROM sys_order_master o JOIN sys_user_info u ON o.user_id = u.user_id JOIN sys_category c ON o.category_id = c.category_id WHERE o.create_time > CURRENT_DATE - INTERVAL ''30 days'' AND o.order_status = 3 AND u.is_vip = TRUE GROUP BY c.category_name ORDER BY total_amount DESC',task_name=>'business_report_tune',time_limit=>300)FROMdual;-- 执行EXECsys_sqltune.execute_tuning_task('business_report_tune');-- 查看报告SELECTsys_sqltune.report_tuning_task('business_report_tune');

9.5 优化建议与实施

假设报告给出以下建议：

统计信息建议：

ANALYZEsys_order_master(create_time,order_status,category_id);ANALYZEsys_user_info(is_vip,user_id);

索引建议：

-- 针对订单表的复合索引CREATEINDEXidx_sys_order_master_time_statusONsys_order_master(create_time,order_status,category_id,user_id,order_amount);-- 针对用户表的索引CREATEINDEXidx_sys_user_info_vip_idONsys_user_info(is_vip,user_id);

SQL改写建议：
原SQL使用了CURRENT_DATE - INTERVAL '30 days'，建议器提示这种表达式在WHERE条件中可能导致函数索引无法使用，建议应用层传入具体日期值。

9.6 优化效果对比

优化前：执行时间4.2秒，全表扫描sys_order_master。

优化后：执行时间85毫秒，使用索引扫描，回表次数大幅减少。

十、最佳实践

用了这么久，我也总结出一些经验给大家列出来

10.1 不要全盘接受建议

建议器是死的，业务是活的。它建议的索引可能很多，但你要考虑：
索引维护成本，索引重复度 ，列的区分度

10.2 定期清理调优任务

调优任务和报告会占用空间，建议定期清理，

删除7天前的任务，这个是根据业务来定的

DELETEFROMsys_sqltune_logWHEREcreate_time<CURRENT_DATE-INTERVAL'7 days';

10.3 复杂SQL分段调优

对于超复杂的嵌套查询，建议拆分成多个子查询分别调优，再整合建议。一次性分析太复杂的SQL，建议器可能超时或给出的建议不够细致。

十一、结语

使用SQL优化建议的方法可直接针对SQL语句生成调优建议报告，或通过输入的SQL语句的QUERYID参数指定具体查询，并自动生成优化建议，结果将存储于目标文件夹中。

当然，工具始终是辅助，最终的数据库优化决策还得靠咱们DBA对业务的理解。建议器告诉你"该建索引"，但建什么类型的索引、要不要做覆盖索引，这些还是需要人工判断。

如果你也在用金仓数据库，强烈建议把SQL优化建议器用起来。特别是面对历史遗留系统，面对成千上万条不知道从哪里下手的慢SQL时，它能帮你快速找到优化突破口。