一、为什么我们需要自动化SQL调优SQL优化建议器能够根据数据库内部状态向数据库管理员DBA提供调优建议。在应用这些优化建议后经过调整的SQL语句性能将得到显著提升。对于基于单条SQL语句的调优工作系统可以直接生成并呈现具体的优化建议内容无需额外进行任务管理或结果查询视图等操作即可一次性获得全部调优结果。系统的调优任务采用阻塞方式运行以确保执行的稳定性和可靠性。二、SQL优化建议器到底是个啥统计信息更新建议:在执行SQL语句的过程中可能会引用一个或多个关系表。如果这些表的统计信息未能及时更新或者缺乏多列统计信息可能导致查询优化器生成非最优的执行计划。这一问题在表数据出现显著倾斜即数据分布极不均衡时尤为突出。针对输入的SQL语句SQL调优建议器将分析其引用的所有关系表并对每个表进行统计信息检查。当检测到表中修改的数据量超过autovacuum的计算结果时系统会自动生成收集统计信息的建议。索引建议:对单条SQL语句进行分析时系统会检查其涉及的表在过滤或连接条件中是否被引用。如果发现相关表缺少必要的索引则自动应用虚拟索引技术进行处理。通过对比执行计划前后的性能数据若性能提升幅度达到5%以上则将给出则给出索引建议、建议索引类型、创建索引的DDL语句、预期收益率等优化方案。SQL改写建议:通过对预设改写规则进行配置系统能够识别符合条件的SQL语句并生成相应的优化建议三、调优接口说明报告通过以下接口通过SQL语句、QUERY_ID指定语句生成。3.1 调优报告接口 :接口名称说明PERF.QUICK_TUNE_BY_SQL(SQL_TEXT)输入一条SQL语句生成调优建议报告PERF.QUICK_TUNE_BY_QUERYID(QUERY_ID)输入一条SQL语句的查询ID生成调优建议报告PERF.QUICK_TUNE_BY_SQL_TO_FILE(SQL_TEXT, FORMAT, FILE_PATH)输入一条SQL语句生成调优建议报告到指定文件PERF.QUICK_TUNE_BY_QUERYID_TO_FILE(QUERY_ID, FORMAT, FILE_PATH)输入查询ID生成调优建议报告到指定文件3.2 接口参数说明参数名称说明SQL_TEXT指定输入的SQL语句QUERY_ID指定SQL语句的唯一查询标识符来源于sys_stat_statements系统视图FORMAT报告输出格式当前仅支持TEXT文本格式FILE_PATH报告文件的存储路径和名称四、调优报告生成的基本配置别急着跑先配好环境为提升用户体验SQL优化功能提供了专门针对数据库管理员DBA设计的调优建议报告接口。该报告内容涵盖以下几个方面GENERAL INFORMATION一般信息包括任务名称、任务所有者、状态、执行开始和完成时间、SQL ID和SQL语句。FINGDING调优发现包括优化建议的动作索引建议和收集统计信息会给出具体SQL改写建议不会给出改写后的SQL。预期收益仅索引建议有 收集统计信息和SQL改写没有。基本原理索引建议和收集统计信息有 改写建议没有。EXPLAIN PLANS执行计划优化前后执行计划对比仅索引建议有 收集统计信息和SQL改写没有。4.1 开启自动调优功能金仓默认可能没有开启SQL调优相关的扩展需要手动加载-- 检查是否已安装SELECT*FROMsys_extensionWHEREextnamesys_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中设置Autovacuumoff;-- 临时关闭统计信息自动更新重启数据库创建表、插入数据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_id9527ANDstatuscompletedORDERBYorder_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 降低至 2346.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_idu.user_idWHEREo.statuscompletedANDu.level5ANDo.order_date2024-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_id9527;虽然user_id是INT类型但传入字符串数据库会做隐式类型转换导致索引失效。建议器会提示SQL改写建议 ----------- 发现隐式类型转换可能影响性能。 建议改写为 SELECT * FROM sys_orders WHERE user_id 9527;7.2 优化IN子查询IN子查询数据量大时性能很差建议器会建议改成EXISTS或JOIN-- 原始SQLSELECT*FROMsys_ordersWHEREuser_idIN(SELECTuser_idFROMsys_usersWHERElevel9);建议器可能建议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.trackall-- 查询最耗时的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_id123456789012345678,task_nameprod_slow_query_001,time_limit600)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_idu.user_idWHEREo.create_timeCURRENT_DATE-INTERVAL30 daysAND(o.statuspendingORo.statusprocessing);用建议器分析后它给出的建议统计信息sys_orders的create_time列统计信息缺失索引建议创建(create_time, status, user_id)的复合索引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)::INT1,(random()*100)::INT1,(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)::SMALLINT1,random()0.8FROMgenerate_series(1,100000);INSERTINTOsys_categorySELECTgenerate_series(1,100),category_||generate_series(1,100),CASEWHENgenerate_series10THEN(random()*10)::INT1ELSE0ENDFROMgenerate_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_idu.user_idJOINsys_category cONo.category_idc.category_idWHEREo.create_timeCURRENT_DATE-INTERVAL30 daysANDo.order_status3ANDu.is_vipTRUEGROUPBYc.category_nameORDERBYtotal_amountDESC;9.4 执行调优分析-- 创建任务SELECTsys_sqltune.create_tuning_task(sql_textSELECT 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_namebusiness_report_tune,time_limit300)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_timeCURRENT_DATE-INTERVAL7 days;10.3 复杂SQL分段调优对于超复杂的嵌套查询建议拆分成多个子查询分别调优再整合建议。一次性分析太复杂的SQL建议器可能超时或给出的建议不够细致。十一、结语使用SQL优化建议的方法可直接针对SQL语句生成调优建议报告或通过输入的SQL语句的QUERYID参数指定具体查询并自动生成优化建议结果将存储于目标文件夹中。当然工具始终是辅助最终的数据库优化决策还得靠咱们DBA对业务的理解。建议器告诉你该建索引但建什么类型的索引、要不要做覆盖索引这些还是需要人工判断。如果你也在用金仓数据库强烈建议把SQL优化建议器用起来。特别是面对历史遗留系统面对成千上万条不知道从哪里下手的慢SQL时它能帮你快速找到优化突破口。