企业官网建设流程全解析

广州 seo的网站,软件技术和软件工程的区别,太原网站建设最好,西安旅游网站开发KingbaseES SQL调优实战笔记#xff1a;从踩坑到精通的真实经验总结 现在业务系统里的数据库应用越来越复杂#xff0c;SQL写得好不好、性能优不优#xff0c;直接影响系统响应速度和并发扛量能力。我用KingbaseES V9版本做调优也有段时间了#xff0c;踩过不少坑#xf…KingbaseES SQL调优实战笔记从踩坑到精通的真实经验总结现在业务系统里的数据库应用越来越复杂SQL写得好不好、性能优不优直接影响系统响应速度和并发扛量能力。我用KingbaseES V9版本做调优也有段时间了踩过不少坑也总结了些实用的技巧。这篇笔记就从基础理论、核心工具到实操方法把我实际工作中用到的调优技术整理出来再配上真实的代码例子希望能帮到和我一样做数据库管理或开发的同学少走点弯路快速解决性能瓶颈。KingbaseES SQL调优实战笔记从踩坑到精通的真实经验总结KingbaseES SQL调优实战笔记从踩坑到精通的真实经验总结一、先搞懂SQL调优到底在调什么二、执行计划深度解析调优的核心工具2.1 三种查看执行计划的方式按需选择2.2 执行计划核心要素看懂这4点就够了2.3 常见执行计划问题这4个坑最容易踩三、核心SQL优化手段从基础到高级3.1 索引优化最常用也最有效的手段3.1.1 常用索引类型选对比选多更重要3.1.2 索引使用技巧这些细节能提升效率3.1.3 索引优化实战Like模式匹配3.2 HINT优化3.2.1 常用HINT示例3.2.2 HINT混合使用实战3.3 参数调整优化3.3.1 成本参数3.3.2 内存参数3.3.3 并行参数3.3.4 参数调整实战解决排序内存不足3.4 其他优化手段四、SQL监控与调优建议4.1 SQL监控工具4.2 自动化调优建议五、总结一、先搞懂SQL调优到底在调什么刚接触调优的时候我总觉得把SQL写得越复杂越厉害后来才发现完全是误区。调优根本不是炫技核心就两个实在目标一是让用户等得短点也就是减少接口响应时间二是让数据库能扛住更多请求提升吞吐量。而且调优不能上来就瞎改得遵循“先诊断后优化”的流程先靠工具找到问题SQL再收集表结构、索引、统计信息这些关键数据接着找准根因——是缺索引还是执行计划选错了方式然后针对性制定方案最后一定要测试验证效果确认有提升再上线不然容易改出新问题。二、执行计划深度解析调优的核心工具执行计划绝对是SQL性能分析的核心工具相当于SQL的“执行说明书”。能不能看懂它直接决定了能不能找到调优关键点。我刚开始学的时候对着执行计划看半天都看不懂后来多实操、多分析慢慢就摸清了规律。2.1 三种查看执行计划的方式按需选择KingbaseES提供了三种查看方式不同场景用不同的我平时工作中这么搭配用EXPLAIN命令最常用也最灵活支持多种选项组合。只想看预估的执行计划用基础的EXPLAIN就行想知道实际执行时间和数据量就用EXPLAIN ANALYZE要分析I/O问题就加上BUFFERS选项看缓冲区使用情况。这里必须提醒一句EXPLAIN ANALYZE会实际执行SQL如果是更新、删除这类写操作一定要先在测试环境验证或者用事务包裹执行完回滚千万别直接在生产环境跑容易影响业务数据。-- 基础执行计划预估不实际执行SQLEXPLAINSELECT*FROMuser_infoWHEREage30;-- 实际执行计划会执行SQL含耗时、实际行数统计EXPLAINANALYZESELECT*FROMuser_infoWHEREage30;-- 带缓冲区统计的执行计划分析I/O问题常用EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMuser_infoWHEREagegt;30;图形化工具适合新手入门。KingbaseES的对象管理工具有个“解析执行计划”功能能把执行流程做成树状图比看文字版的直观多了刚开始学的时候用这个工具能快速理解执行顺序。日志记录生产环境必备。生产环境不能随便执行EXPLAIN ANALYZE这时候就用auto_explain插件配置好后能自动记录执行时间超过阈值的SQL的执行计划方便后续分析。-- auto_explain插件配置步骤生产环境常用-- 1. 修改数据库配置文件kingbase.conf需要重启生效shared_preload_librariesauto_explain-- 加载插件auto_explain.log_min_duration500-- 记录执行时间超过500ms的SQL可根据业务调整auto_explain.log_analyzeon-- 记录实际执行信息比如耗时、行数auto_explain.log_bufferson-- 记录缓冲区使用情况方便分析I/O问题-- 2. 重启数据库使配置生效生产环境重启要提前规划-- systemctl restart kingbase-- 3. 执行慢查询后直接查看数据库日志就能拿到执行计划详情我之前用这个插件监控到一条关联查询执行时间1200ms从日志的执行计划里能看到两个表都是全表扫描缓冲区还有read操作说明有磁盘I/O后来给关联字段建了索引问题就解决了。2.2 执行计划核心要素看懂这4点就够了执行计划是树状结构从上到下执行其实不用逐字逐句看抓住4个核心要素就能快速分析出问题执行动作比如Index Scan、Hash Join、Sort这些一眼就能知道数据库在做什么操作是全表扫还是索引扫是哪种连接方式。代价信息包括预估代价启动成本总体成本和实际执行时间。如果实际时间远大于预估时间大概率是统计信息不准确优化器判断错了。数据量信息预估行数和实际行数。要是两者偏差很大比如预估100行实际10000行说明统计信息有问题优化器的决策肯定不准这时候就得更新统计信息。条件信息Index Cond是用索引过滤的条件Filter是扫描后再进一步过滤的条件。如果Filter过滤掉了大量数据说明索引建得不好没把关键过滤条件包含进去得调整索引。给大家举个实际的分析例子跟着这个思路走很快就能上手-- 执行查询并查看执行计划EXPLAINANALYZESELECTname,ageFROMuser_infoWHEREageBETWEEN25AND40ORDERBYageDESC;-- 简化后的执行计划输出/* Sort (cost100.00..105.00 rows200 width36) (actual time8.00..8.50 rows180 loops1) Sort Key: age DESC Sort Method: quicksort Memory: 32kB - Bitmap Heap Scan on user_info (cost10.00..80.00 rows200 width36) (actual time2.00..5.00 rows180 loops1) Recheck Cond: ((age 25) AND (age 40)) - Bitmap Index Scan on idx_user_info_age (cost0.00..9.50 rows200 width0) (actual time1.50..1.50 rows180 loops1) Index Cond: ((age 25) AND (age 40)) Execution Time: 9.00 ms */我的分析思路很简单首先看最内层用到了idx_user_info_age索引索引条件正好是查询的age范围说明索引生效了再看数据量预估200行实际180行偏差很小统计信息没问题排序用的是quicksort内存排序没用到临时文件排序效率高最后看整体执行时间9ms性能很好这个查询不用优化。如果这里出现问题比如用了Seq Scan全表扫描就是没建索引或索引没生效排序用了external merge外部排序就是work_mem太小了预估和实际行数偏差大就执行ANALYZE更新统计信息。2.3 常见执行计划问题这4个坑最容易踩分析执行计划多了我发现有4个问题最常见遇到了直接针对性解决就行统计信息不准确表现就是预估行数和实际行数偏差大解决方法很简单执行ANALYZE更新统计信息批量操作数据后一定要记得做。缺少索引大表出现Seq Scan全表扫描耗时很长解决方法是创建合适的索引。这里提醒下不是索引越多越好太多索引会影响插入、更新的性能够用就行。连接方式不当小表和大表连接用了Hash Join效率低这时候用HINT强制指定Nested Loop连接就好。内存不足排序操作出现external merge外部排序用到了临时文件调大work_mem参数就能解决。其中缺少索引是我遇到最多的问题给大家看个实战案例-- 问题查询根据手机号查用户信息执行很慢EXPLAINANALYZESELECT*FROMuser_infoWHEREphone13800138000;-- 执行计划显示是Seq Scan全表扫描虽然只返回1行但耗时15ms-- 解决方案给phone字段建索引CREATEINDEXidx_user_info_phoneONuser_info(phone);-- 优化后再查执行计划显示Index Scan索引扫描耗时0.1msEXPLAINANALYZESELECT*FROMuser_infoWHEREphone13800138000;性能直接提升了150倍效果特别明显。如果查询条件是多列建议建联合索引比单独建多个单列索引效率高。三、核心SQL优化手段从基础到高级3.1 索引优化最常用也最有效的手段我每次调优最先考虑的就是索引优化只要选对索引、用对方法大部分性能问题都能解决。KingbaseES支持多种索引类型不同场景选对了能少走很多弯路。下面都是我实战中验证过的常用索引类型和使用技巧分享给大家。3.1.1 常用索引类型选对比选多更重要Btree索引这是默认的索引类型也是最常用的。支持范围查询、排序不管是等值查询比如where id100还是范围查询比如where age30 and age50都适用平时大部分场景用它准没错。-- 创建Btree索引默认类型可省略USING btreeCREATEINDEXidx_user_info_ageONuser_info(age);-- 范围查询能高效命中索引EXPLAINANALYZESELECT*FROMuser_infoWHEREage30ANDage50;Hash索引仅支持等值查询查询效率比Btree还高但不支持范围查询。适合那些只做等值匹配的场景比如根据用户ID查询。我之前在一个用户详情查询接口里用了Hash索引等值查询速度确实比Btree快一点但后来加了个范围查询的需求索引就失效了又换成了Btree所以用之前一定要确认查询场景。-- 创建Hash索引CREATEINDEXidx_user_info_id_hashONuser_infoUSINGhash(id);-- 等值查询效率高EXPLAINANALYZESELECT*FROMuser_infoWHEREid100;GIN索引主要用于数组、全文检索这些多值查询场景。比如文章标签是数组类型要查询包含某个标签的文章用GIN索引就很合适。我之前做过一个文章检索功能标签是text[]类型建了GIN索引后查询包含指定标签的文章速度提升了不少。-- 创建数组类型列并建立GIN索引CREATETABLEarticle(idint,tagstext[]);-- tags存文章标签数组类型CREATEINDEXidx_article_tagsONarticleUSINGgin(tags);-- 查询包含“数据库”和“SQL”标签的文章EXPLAINANALYZESELECT*FROMarticleWHEREtags ARRAY[数据库,SQL];BRIN索引适合时序数据比如日志表、监控数据。这类数据的时间字段是有序的用BRIN索引维护成本低占用空间小查询效率也不错。我之前给一个每天产生几百万条数据的日志表建了BRIN索引按时间范围查询日志速度比全表扫快太多而且索引占用的空间比Btree小很多。不过它的查询精度不如Btree适合对查询精度要求不高的时序场景。-- 创建时序表并建立BRIN索引CREATETABLElogs(log_timetimestamp,contenttext);-- log_time是时序字段CREATEINDEXidx_logs_timeONlogsUSINGbrin(log_time);-- 时序范围查询查询某段时间内的日志EXPLAINANALYZESELECT*FROMlogsWHERElog_timeBETWEEN2024-01-01AND2024-01-02;3.1.2 索引使用技巧这些细节能提升效率除了选对索引类型还有几个小技巧能进一步提升索引效率都是我踩坑总结出来的表达式索引针对查询中用到函数计算的场景。比如查询时要做大小写无关匹配直接用name字段的索引不会生效这时候就需要建表达式索引。我之前遇到过一个用户查询功能要忽略姓名大小写刚开始没建表达式索引查询很慢建了upper(name)的表达式索引后速度立马上来了。-- 创建表达式索引CREATEINDEXidx_user_info_name_upperONuser_info(upper(name));-- 大小写无关查询能命中索引EXPLAINANALYZESELECT*FROMuser_infoWHEREupper(name)ZHANGSAN;局部索引只对表的子集创建索引能减少维护成本。比如一张用户表大部分查询都是针对成年人的就可以只给age18的记录建索引这样索引文件更小插入、更新数据时维护索引的成本也低。-- 创建局部索引仅索引年龄大于18的记录CREATEINDEXidx_user_info_adultONuser_info(age)WHEREage18;-- 查询成年人数据能命中索引EXPLAINANALYZESELECT*FROMuser_infoWHEREage20;联合索引把高频查询列放在前面支持前缀匹配。建联合索引时查询频率高的列要放在前面比如查询时经常用name和age组合条件就把name放在前面。而且联合索引支持前缀匹配比如建了(name, age, phone)的索引只查name、查name和age都能命中索引。-- 创建联合索引查询频率name age phone按频率排序CREATEINDEXidx_user_info_name_age_phoneONuser_info(name,age,phone);-- 以下查询都能命中索引EXPLAINANALYZESELECT*FROMuser_infoWHEREname张三;EXPLAINANALYZESELECT*FROMuser_infoWHEREname张三ANDage25;EXPLAINANALYZESELECT*FROMuser_infoWHEREname张三ANDage25ANDphone13800138000;定期维护索引用久了性能会下降要定期维护。可以通过VACUUM清理无效数据更新统计信息如果索引碎片多就用REINDEX重建索引。另外还要定期查看索引使用情况把那些从来没被使用过的索引删掉减少维护成本。-- 清理表并更新统计信息VACUUMANALYZEuser_info;-- 重建索引提升索引效率REINDEXINDEXidx_user_info_name_age_phone;-- 查看索引使用情况找出无用索引SELECTindexrelname,idx_scan,idx_tup_read,idx_tup_fetchFROMsys_stat_user_indexesWHERErelnameuser_info;3.1.3 索引优化实战Like模式匹配Like模式匹配是很常见的场景不同匹配方式对应的索引方案不一样我整理了三种常见情况的解决方案都是实战中能用的-- 1. 前匹配查询比如张%用Btree索引指定text_pattern_opsCREATEINDEXidx_user_info_name_prefixONuser_info(name text_pattern_ops);EXPLAINANALYZESELECT*FROMuser_infoWHEREnameLIKE张%;-- 2. 后匹配查询比如%三用reverse表达式索引CREATEINDEXidx_user_info_name_suffixONuser_info(reverse(name)collateC);EXPLAINANALYZESELECT*FROMuser_infoWHEREreverse(name)LIKEreverse(%三);-- 3. 中间匹配查询比如%三%用TRGM索引需要先创建sys_trgm扩展CREATEEXTENSION sys_trgm;CREATEINDEXidx_user_info_name_trgmONuser_infoUSINGgin(name gin_trgm_ops);EXPLAINANALYZESELECT*FROMuser_infoWHEREnameLIKE%三%;3.2 HINT优化有时候优化器会因为统计信息不准或者场景特殊生成次优的执行计划这时候就可以用HINT强制指定执行策略。不过用之前要先开启enable_hint配置不然HINT不生效。-- 开启HINT功能SETenable_hinton;3.2.1 常用HINT示例我整理了几个工作中最常用的HINT覆盖扫描方式、连接方式、连接顺序、并行执行这些核心场景指定扫描方式有时候优化器没选择索引扫描这时候可以强制指定。-- 强制使用idx_user_info_age索引扫描EXPLAINANALYZESELECT/*IndexScan(user_info idx_user_info_age)*/*FROMuser_infoWHEREage30;指定连接方式小表和大表连接优化器选错成Hash Join时强制指定Nested Loop。-- 强制user_info和order_detail用嵌套循环连接EXPLAINANALYZESELECT/*NestLoop(user_info order_detail)*/*FROMuser_info uJOINorder_detail oONu.ido.order_id;调整连接顺序连接表比较多时优化器可能选不对连接顺序手动指定能提升效率。-- 指定连接顺序为order_detail先连user_infoEXPLAINANALYZESELECT/*leading(order_detail user_info)*/*FROMuser_info uJOINorder_detail oONu.ido.order_id;并行执行对于大表的统计查询指定并行worker数量能提升速度。-- 指定2个并行worker进程提升count(*)查询速度EXPLAINANALYZESELECT/*Parallel(user_info 2)*/count(*)FROMuser_info;3.2.2 HINT混合使用实战实际调优中经常需要混合使用多个HINT。比如下面这个关联查询我同时指定了连接顺序、连接方式、扫描方式和并行执行性能提升很明显-- 混合使用HINT优化关联查询EXPLAINANALYZESELECT/* leading(order_detail user_info) -- 指定连接顺序order_detail → user_info NestLoop(order_detail user_info) -- 强制嵌套循环连接 IndexScan(user_info idx_user_info_id) -- user_info用id索引扫描 Parallel(order_detail 2) -- order_detail用2个并行worker */u.name,o.order_id,o.product_idFROMuser_info uJOINorder_detail oONu.ido.order_idWHEREo.create_time2024-01-01;提醒下HINT虽然好用但不要过度依赖优先通过更新统计信息、调整索引让优化器自动选最优计划只有优化器确实选错了才用HINT。3.3 参数调整优化除了索引和HINT调整数据库参数也是常用的优化手段。核心参数主要分三类根据业务场景调整能进一步提升性能。3.3.1 成本参数这类参数影响优化器对执行方式的选择主要有两个seq_page_cost全表扫描单页成本默认1.0和random_page_cost索引扫描单页成本默认4.0。如果数据库用的是SSD存储I/O性能强就可以把random_page_cost调低到1.1左右让优化器更倾向于选择索引扫描。-- SSD存储场景调整成本参数SETrandom_page_cost1.1;SETseq_page_cost0.5;3.3.2 内存参数内存参数对排序、哈希操作和维护操作影响很大work_mem排序和哈希操作的工作内存默认1MB。如果查询有复杂排序或哈希连接经常出现外部排序就把work_mem调大我之前遇到过一个排序慢的查询把work_mem调到64MB后排序方式从外部排序变成内存排序执行时间从500ms降到50ms。-- 临时调整当前会话有效SETwork_mem64MB;-- 永久调整修改kingbase.conf需要重启-- work_mem 64MBmaintenance_work_mem索引创建、VACUUM等维护操作的内存默认16MB。批量创建索引或大表VACUUM时调大这个参数能提升维护效率。-- 调整维护操作内存SETmaintenance_work_mem256MB;3.3.3 并行参数这类参数控制并行查询的资源主要有max_parallel_workers_per_gather单个查询最大并行worker数默认8和min_parallel_table_scan_size触发并行扫描的最小表大小默认8MB。根据服务器CPU核心数调整比如CPU核心少就把max_parallel_workers_per_gather调小避免并行抢占资源导致整体性能下降。-- 调整并行参数适配CPU核心数较少的服务器SETmax_parallel_workers_per_gather4;SETmin_parallel_table_scan_size16MB;3.3.4 参数调整实战解决排序内存不足给大家看个实际案例排序内存不足是很常见的问题解决起来很简单-- 问题查询排序用到了临时文件执行很慢EXPLAINANALYZESELECT*FROMuser_infoORDERBYname;-- 执行计划显示Sort Method: external merge Disk: 1024kB-- 解决方案调大work_memSETwork_mem32MB;-- 优化后查询排序用内存速度大幅提升EXPLAINANALYZESELECT*FROMuser_infoORDERBYname;-- 执行计划显示Sort Method: quicksort Memory: 64kB-- 执行时间从500ms降至50ms3.4 其他优化手段除了上面的核心手段还有几个实用的优化方法在特定场景下能发挥很大作用Query Mapping预先定义SQL映射关系自动替换低效SQL。比如很多开发习惯用UNION其实在没有重复数据的场景下用UNION ALL效率更高就可以创建Query Mapping规则把对应的UNION查询自动替换成UNION ALL。-- 创建Query Mapping规则UNION替换为UNION ALLSELECTcreate_query_rule(qm_union_to_unionall,select id from user_info where age $1 union select id from order_detail where amount $2,select id from user_info where age $1 union all select id from order_detail where amount $2,true,text);-- 执行查询时会自动替换不用改业务代码SELECTidfromuser_infowhereage30unionselectidfromorder_detailwhereamount1000;物化视图适合基表更新不频繁的复杂查询场景。比如一些业务报表查询需要关联多张表、做复杂聚合基表数据一天才更新一次这时候就可以创建物化视图预先存储查询结果查询时直接查物化视图速度比实时关联快很多。基表数据更新后刷新一下物化视图就行。-- 创建物化视图存储用户订单统计结果CREATEMATERIALIZEDVIEWmv_user_orderASSELECTu.id,u.name,count(o.order_id)ASorder_countFROMuser_info uLEFTJOINorder_detail oONu.ido.order_idGROUPBYu.id,u.name;-- 基表数据更新后刷新物化视图REFRESH MATERIALIZEDVIEWmv_user_order;-- 查询物化视图性能远超实时关联SELECT*FROMmv_user_orderWHEREorder_count5;SQL改写很多时候简单改写SQL就能提升性能不用复杂操作。我整理了几个常用的改写技巧-- 1. UNION改UNION ALL无重复数据时避免去重操作提升效率-- 优化前SELECTidFROMuser_infoWHEREage30UNIONSELECTidFROMorder_detailWHEREamount1000;-- 优化后SELECTidFROMuser_infoWHEREage30UNIONALLSELECTidFROMorder_detailWHEREamount1000;-- 2. DELETE全表改TRUNCATETRUNCATE不记录日志速度比DELETE快很多-- 优化前DELETEFROMtemp_table;-- 优化后TRUNCATETABLEtemp_table;-- 3. 子查询改写为JOIN很多时候JOIN比IN子查询效率高-- 优化前SELECT*FROMuser_infoWHEREidIN(SELECTuser_idFROMorder_detailWHEREamount1000);-- 优化后SELECTu.*FROMuser_info uJOINorder_detail oONu.ido.user_idWHEREo.amount1000;四、SQL监控与调优建议4.1 SQL监控工具调优不是一劳永逸的需要定期监控SQL性能及时发现问题。KingbaseES提供了SQL监控功能能自动跟踪消耗CPU或I/O超过5秒的语句配置也很简单-- 配置参数修改kingbase.confshared_preload_librariesplsql, sys_stat_statements, sys_sqltunesql_monitor.trackall-- 创建sys_sqltune插件CREATEEXTENSION sys_sqltune;配置好后通过视图就能查看监控结果还能生成详细的监控报告-- 查看最近10条消耗资源多的SQLSELECT*FROMV$SQL_MONITORORDERBYlast_tsDESCLIMIT10;-- 查看某条SQL的执行计划监控SELECTplan_index,plan_type,plan_object_name,total_timeFROMV$SQL_PLAN_MONITORWHEREsql_id具体SQL_ID;-- 生成TEXT版监控报告SELECTDBMS_SQL_MONITOR.REPORT_SQL_MONITOR(typeTEXT);-- 生成HTML版报告并保存到文件方便查看SELECTPERF.REPORT_SQL_MONITOR_TO_FILE(/tmp/sql_monitor.html,typeHTML);4.2 自动化调优建议如果觉得手动分析麻烦还可以用sys_sqltune插件的自动调优功能它能给出针对性的调优建议比如统计信息收集建议、索引建议、SQL改写建议等新手也能用上。-- 生成单条SQL的调优报告SELECTPERF.QUICK_TUNE_BY_SQL(SELECT * FROM user_info u JOIN order_detail o ON u.id o.user_id WHERE o.amount 1000;);-- 生成调优报告并保存到文件SELECTPERF.QUICK_TUNE_BY_SQL_TO_FILE(SELECT * FROM user_info u JOIN order_detail o ON u.id o.user_id WHERE o.amount 1000;,TEXT,/tmp/sql_tune_report.txt);我平时会用这个功能快速排查简单的性能问题比如它提示缺少某个索引我就会去验证确实能节省不少时间。但对于复杂场景还是需要结合手动分析。五、总结随着业务数据量增长SQL性能会慢慢退化定期的SQL审计和调优很重要。建议大家建立常态化的调优机制结合监控工具及时发现性能退化的语句持续优化系统响应速度和资源利用率这样才能保障业务稳定运行。这篇笔记里的方法都是我实际工作中验证过的希望能帮到大家少踩坑快速提升调优能力。