《Kingbase护城河》——猎捕慢查询：执行计划的微观解析与索引调优实战

“为什么这条 SQL 跑得这么慢”——猎捕慢查询执行计划的微观解析与索引调优实战在过去的专栏里面我们把数据库的安全问题搞了搞像僵尸进程、行锁死锁还有磁盘满的这些情况都给处理了。那么数据库不经常挂了也不报空间不够了其实这只是说明它算是在生产环境里面稳下来了而已。不过呢运维的日子没那么好过。系统稳了没几天业务那边的人又开始叫了。“服务器也没挂啊磁盘也还有空间但是我点一下前端页面的查询按钮那个圈圈要转个 10 秒钟才能出结果。这是为啥啊”这个时候你登到服务器上去看就会发现一个很奇怪的情况。数据库连接数看起来挺正常的死锁也没有。但是CPU 使用率却一直下不来长时间都在 90% 以上。这个其实就是企业层级里的数据库运维里面碰到最多、也是最看技术的情况了那就是慢查询Slow Query把性能搞崩了。一条写得烂的 SQL它不光是自己跑得慢。它还会把整台服务器的 CPU 和内存都给吃光。那么其他的正常业务也就跟着跑不动了。作为搞数据库调优的人我们肯定不能靠猜的去猜哪里慢是不行的。这篇文章的话就带你看看数据库查询优化器Optimizer它是怎么去算的。接着手把手教你用EXPLAIN ANALYZE这个很好用的诊断工具。我们会弄点真实的数据压测一下看看执行计划到底长啥样。也就是看看全表扫描和索引扫描到底有啥不一样。最后我们再来聊聊那些明明建了索引却不走索引的情况到底该怎么避开这些坑。文章目录“为什么这条 SQL 跑得这么慢”——猎捕慢查询执行计划的微观解析与索引调优实战第一阶段案发现场布置 —— 制造千万级数据的“性能怪兽”1. 一键生成百万级测试表第二阶段猎物显形 —— EXPLAIN ANALYZE 执行计划的硬核解剖1. 裸奔的代价全表扫描Seq Scan2. 读懂“天书”执行计划关键指标解析第三阶段动手干预 —— 索引Index是怎么起作用的还有性能的变化1. 建立 B-Tree 索引2. 看看效果索引扫描Index Scan第四阶段高阶排雷 —— 为什么我建了索引SQL 依然在“裸奔”禁忌一在索引列上面搞数学运算或者套函数肯定出问题禁忌二隐式类型转换有意思的地方优化器变聪明了禁忌三优化器其实比你聪明——要拿的数据太多的情况第一阶段案发现场布置 —— 制造千万级数据的“性能怪兽”在性能调优的世界里数据量就是一切。一条全表扫描的 SQL在 1 万条数据的开发环境里可能只需要 1 毫秒但在 1000 万条数据的生产环境里可能会跑上几十分钟。为了让今天的实战具有真实的生产级压迫感我们首先要在你的测试库中利用ksql瞬间制造一张拥有海量数据的测试表。1. 一键生成百万级测试表请打开你的 Win11ksql客户端连入数据库执行以下极速造数脚本-- 1. 创建一张模拟的用户交易流水表CREATETABLEopt_test_log(log_idINT,user_idINT,action_typeVARCHAR(50),trade_amountNUMERIC(10,2),create_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP);-- 2. 疯狂注入 200 万条测试数据执行可能需要十几秒到半分钟请耐心等待INSERTINTOopt_test_log(log_id,user_id,action_type,trade_amount)SELECTi,(random()*100000)::INT,-- 随机生成 10万以内的用户IDPAYMENT,(random()*500)::NUMERIC(10,2)FROMgenerate_series(1,2000000)ASi;此时你已经拥有了一张体积庞大、毫无优化的“原生态”千万级数据表。接下来好戏开场。第二阶段猎物显形 ——EXPLAIN ANALYZE执行计划的硬核解剖假设业务同学写了这样一条 SQL他想精确查询log_id 1500000第 150 万号的这条交易流水。如果直接执行SELECT * FROM opt_test_log WHERE log_id 1500000;你只会看到结果和漫长的等待时间却不知道底层经历了什么。我们需要给这条 SQL 套上“X光机”——也就是EXPLAIN ANALYZE命令。1. 裸奔的代价全表扫描Seq Scan在查询语句的最前面加上EXPLAIN ANALYZEEXPLAINANALYZESELECT*FROMopt_test_logWHERElog_id1500000;2. 读懂“天书”执行计划关键指标解析屏幕上给你返回这么几行英文新手一看可能觉得像天书一样。那么一个老手 DBA 跟新手的区别在哪儿呢其实就在于能不能从这些信息里快速地拎出来真正有用的东西。下面这三个指标得盯紧了。执行节点Node TypeParallel Seq Scan on opt_test_log这个是最要命的信号。你看截图里虽然电科金仓的优化器挺聪明的它自己就开了 Parallel —— 也就是并行多线程还分了 2 个 Worker 去跑想着能快一点。但是它的根儿没变还是 Seq Scan也就是顺序全表扫描。这啥意思呢就为了查那么 1 条数据数据库动了好几个 CPU 核心把 200 万行数据从头到尾给翻了一遍。你想啊并发一上来服务器 CPU 瞬间飙到 100%那背后不就是这玩意儿在搞鬼嘛。代价预估Costcost0.00…25231.67这个是优化器给出的一个执行成本估算。前边那个 0.00是说返回第一行的启动成本。后边的 25231.67 呢是把这个节点整个扫完的总成本。这个数字啊它没有单位就是个相对的值。也就是说它越大就代表越吃资源。真实耗时Execution TimeExecution Time: 88.380 ms经常有那种初级开发一看到这个数心里就想“哎呀才 88 毫秒连 0.1 秒都没到这不挺快的嘛。”我跟你说这个想法是要出问题的。为啥呢你想想对于一个那种在单列上做精确等值匹配的 OLTP 业务也就是在线交易类的正常的响应时间应该是 0.1 毫秒这个级别也就是亚毫秒级。那么88 毫秒意味着啥它比正常情况慢了差不多 1000 倍你试着想一个场景比如双十一的时候每秒有上千个请求打过来每个请求都要霸着 3 个 CPU 核心在那跑 88 毫秒那系统不得瞬间排起长队然后直接崩掉啊。第三阶段动手干预 —— 索引Index是怎么起作用的还有性能的变化既然全表扫描的代价这么高那我们就得用上数据库里面很常用的一个东西了也就是B-Tree 索引B树索引。索引是个啥原理呢其实就像是你给那 200 万条数据做了一个排序一样的目录找数据的时候先查目录速度自然就快了。1. 建立 B-Tree 索引接着我们在ksql里面给log_id这个字段建一个单列的索引操作如下-- 在 log_id 字段上创建 B-Tree 索引CREATEINDEXidx_opt_log_idONopt_test_log(log_id);(注建索引的时候它会把全表扫一遍你等它提示 CREATE INDEX 完成就行。)2. 看看效果索引扫描Index Scan目录搞好了以后我们把刚才那句用来诊断的 SQL 原封不动再跑一遍EXPLAINANALYZESELECT*FROMopt_test_logWHERElog_id1500000;性能前后的对比情况你结合上面这张截图来看其实执行计划的变化是非常大的节点变了之前那个很吃 CPU 的Parallel Seq Scan没了变成了Index Scan using idx_opt_log_id。也就是说数据库直接去查那个索引树了。顺着树根就走了几个分支数据就找到了再也不用去搞多线程扫描了。成本降了很多那个执行代价Cost的话之前还是两万多25231.67呢接着就掉到了0.43..8.45这么小的一个数。响应快了真实耗时Execution Time一开始是 88.380 ms现在的话变成了0.032 ms。连 1 毫秒都不到了。性能差不多提了有2700 倍通常来说这就是我们做 DBA 的在生产环境调索引的时候觉得最爽的时候了。第四阶段高阶排雷 —— 为什么我建了索引SQL 依然在“裸奔”掌握了如何看Index Scan只是入门。在真实的企业级架构中最让开发同学崩溃的灵魂拷问是“DBA 大佬我明明在这个字段上建了索引为什么执行计划一看它还是在走全表扫描”这种现象被称为“索引失效”。数据库的优化器极其聪明但如果你写的 SQL 触碰了底层的架构禁忌优化器就会果断抛弃你的索引。我们在ksql中用实战来逐一演示这三个经典的翻车或反转现场。结合上方这张压测全记录我们来逐一复盘禁忌一在索引列上面搞数学运算或者套函数肯定出问题你仔细看看截图里面第一段 SQL跑出来的结果。我们其实就是为了查log_id 1500000的数据但是的话故意在左边写成了log_id 1 1500001诊断结果它马上就变成了Parallel Seq Scan也就是说并行全表扫描了耗时一下子就飙到了88.760 ms。底层真相索引树里面存的其实往往是原来那个log_id的值。你给这个字段加了个 1或者说套了个TO_CHAR这样的函数数据库就没办法直接去匹配树的节点了。那怎么办呢它必须把那 200 万条数据全都拿出来一条一条算一遍接着才能去比对等号右边那个值。架构解法也就是说索引列你得让它干干净净的。你改成WHERE log_id 1500000 - 1;这样写的话索引马上就能用上了。禁忌二隐式类型转换有意思的地方优化器变聪明了再看看截图里面的第二段 SQL。log_id这个字段它是整数型INT的但是的话等号右边你传进去的却是一个字符串也就是1500000。很多以前的旧教程都会跟你说“隐式类型转换的话那肯定会让索引失效的”诊断结果你看看真实的反馈它居然走的是Index Scan耗时也就0.042 ms底层真相这个其实就是电科金仓现在这个优化器厉害的地方了。当它发现等号右边是个常量字符串的情况在真正执行之前它会很聪明地把这个字符串先转成数字。接着再去跟左边的 INT 型索引树去匹配这样的话索引就保住了避坑警告但是的话你千万别因为这个就觉得随便传参也行如果反过来了你的字段是VARCHAR但是你传进去的参数是INT。优化器为了怕精度丢掉往往就会去对左边的字段做隐式的函数强转。这就相当于你踩到了禁忌一那个坑里面了此时索引肯定是没法用的。所以啊传参的类型和表字段的类型一定要严格对齐这个规矩还是得死死守住的。禁忌三优化器其实比你聪明——要拿的数据太多的情况接着看看截图里面的第三段 SQL。我们在user_id上面建了很好的索引然后去查user_id 0的数据。因为测试数据里面的 ID 基本上都是大于 0 的也就是说我们其实是要把全表差不多 100% 的数据都给拿出来。诊断结果优化器想都没想就把你刚建好的索引给扔了它强行走了Seq Scan也就是全表扫描耗时跑到了270.604 ms。底层真相这个其实是基于代价优化器CBO聪明的地方。优化器在后台算了一笔账如果走索引扫描的话它不光是要去遍历那个很大的索引树接着还要根据树上的指针不停地去“回表”。也就是说要去磁盘上东找西找把真实的行数据给拼出来。这种海量的随机 I/O代价太高了。那还不如干脆直接从头到尾扫一遍磁盘也就是顺序 I/O这样往往来得更快架构启示也就是说索引这东西它也不是啥情况都能搞定的。如果你是那种要大批量导出数据的报表类 SQL你就别光想着建索引了。通常来说合理去用用时间分区表Partition或者说换个列存引擎这才是靠谱的做法。结语这篇文章我们做了什么先是造了大概 200 万条数据那个量级跑起来压力就上来了。然后用EXPLAIN ANALYZE这个命令往深里一看好嘛发现它走的是全表扫描性能的根儿就在这儿。接着我们建了个 B-Tree 索引这一下查询速度提升了好几万倍真的。再往后又仔细拆解了一下索引会失效的三种情况那都是架构层面的坑。这么一圈折腾下来其实就算是把数据库性能调优这事儿从怎么看一个具体的执行细节再到怎么从整体上去优化都给跑通了。一个真正合格的 DBA 或者架构师他平时不是说天天在那儿杀进程、重启数据库到处救火。更多的时候是通过看执行计划在开发那边 SQL 还没写得特别烂的时候就把问题给摁住。对就是把问题掐死在摇篮里的感觉。写到这儿咱们这个金仓数据库的《Kingbase护城河》系列专栏从网络怎么连调、死锁怎么排查、空间怎么管理到性能怎么调这四个难啃的部分算是都跟着走了一遍。我挺希望这套在 Win 和 Linux 两头实际动手折腾出来的操作方法能真的帮你把国产的金仓数据库用顺手。毕竟现在这些国产数据库在越来越多的关键系统里都已经用上了。把底层的那些运行道理整明白把排错的手艺练扎实后面再碰到什么新数据库、新挑战心里也就有个底了。行那咱们后面新的技术内容再见吧。