KES数据库索引机制与执行计划分析：从慢查询到可解释优化

KES数据库索引机制与执行计划分析从慢查询到可解释优化本文是本系列第 8 篇。上一篇通过“下单扣库存”讲解了 KingbaseES 的事务一致性和并发控制本文开始进入性能方向围绕索引和执行计划分析查询为什么慢、如何优化。引言上一篇咱们聊的是“多步骤写入怎么保证一致性”的事儿。其实事务这东西它能把订单、明细还有库存捏合在一块要么一起提交要么一起回滚。但是呢业务数据慢慢变多了之后有个新情况就冒出来了。什么情况呢查询开始变慢了。搞数据库的性能优化通常来说你真的不能光凭感觉走。也就是说你不能随便甩一句“加个索引就快了”也不能一看到查询慢就瞎建一堆索引。那更靠谱一点的搞法是什么呢其实得先搞清楚你的查询场景是啥。接着去看看执行计划然后再去判断到底要不要加索引或者说你加的索引它到底有没有真正被用上。那么这篇呢咱们就围着kb_shop里的客户表、商品表还有订单表来搞重点其实就是弄明白下面这几件事搞懂索引到底起啥作用还有它要付出啥代价。学会用EXPLAIN去看看 SQL 的执行计划长啥样。搞明白主键、唯一约束跟自动索引它们仨到底是啥关系。像客户 ID、订单明细外键这种经常要用来做关联的字段咱们给它把索引建上。聊聊为啥索引这东西不是建得越多就越好。文章目录KES数据库索引机制与执行计划分析从慢查询到可解释优化引言索引为什么能提升查询效率一、连接 kb_shop 并准备观察环境二、先看一个订单号查询三、使用 EXPLAIN 查看执行计划四、先查看已有索引五、为外键列创建索引六、为状态和时间查询创建组合索引七、组合索引的顺序很重要八、更新统计信息九、常见问题排查问题 1为什么还没手工创建订单号索引就已经走了 Index Scan问题 2创建索引后执行计划仍然是 Seq Scan问题 3索引越建越多写入变慢问题 4不知道某张表有哪些索引十、本文小结索引为什么能提升查询效率索引这东西你其实可以把它当成是数据库给某些字段额外弄出来的一个查找目录。要是没索引的话数据库往往仅仅只能把整张表从头扫到尾。那如果有了合适的索引呢数据库就能顺着这个目录非常快地找到你要的那行数据在哪。拿订单表来举个例子吧sales.customer_order ├─ order_id ├─ order_no ├─ customer_id ├─ order_status └─ created_at如果你平时经常要根据order_no来查订单那么给order_no建个索引其实就很有必要了。为啥呢因为订单号这东西通常来说它都是唯一的你查询的条件也是非常明确的。不过这里面有个点得注意一下。咱们在前面建sales.customer_order这张表的时候其实已经给order_no加上唯一约束了CONSTRAINTuk_customer_order_noUNIQUE(order_no)在 KingbaseES 这个数据库里头主键和唯一约束通常来说它会自己偷偷把对应的唯一索引给建上干啥用呢其实就是用来保证数据不会重复。也就是说拿订单号来查询它其实并不是一个“完全没索引”的情况。那拿它来说事其实更想表达的是什么呢约束这东西它不光是约束了数据同时它也可能给优化器提供了一条能走通的访问路径。但是搞索引它也不是白搞的它起码有三种成本你得心里有数成本说明存储成本索引自己也是要占磁盘空间的写入成本你在插入、更新或者删数据的时候索引也得跟着一起同步去维护选择成本索引一多优化器去评估走哪条路径的时候它就更容易选花眼复杂度就上去了所以啊设计索引的原则它往往仅仅只是看你的高频查询条件、连接条件还有排序条件来搞的。千万不要搞那种“所有字段都上索引”的操作。一、连接 kb_shop 并准备观察环境进入工具目录cd /d D:\Tools\Kingbase\ES\Server\bin连接业务库ksql -U system -d kb_shop -h localhost -p 54321开启执行耗时显示\timing这个命令不会改变 SQL 的执行逻辑只是让ksql在每条语句执行后显示耗时。数据量小的时候差异可能不明显但在性能文章里保留这个习惯很重要。确认核心表存在\dt sales.*\dt inventory.*如果客户表、订单表、订单明细表、商品表都存在就可以继续。二、先看一个订单号查询订单号查询是非常典型的业务场景。用户在页面输入订单号系统需要快速定位一条订单。SELECTorder_id,order_no,customer_id,order_status,total_amount,created_atFROMsales.customer_orderWHEREorder_noSO202605270001;这条 SQL 的过滤条件非常明确WHEREorder_noSO202605270001如果订单表只有几条数据即使没有索引也很快但真实系统中订单可能有几十万、几百万行这时查询路径就很关键。三、使用 EXPLAIN 查看执行计划执行计划这东西其实就是数据库跑这条 SQL 之前给你交个底。也就是说它准备怎么去执行这条语句。EXPLAINSELECTorder_id,order_no,customer_id,order_status,total_amount,created_atFROMsales.customer_orderWHEREorder_noSO202605270001;因为咱们前面给order_no加了唯一约束的情况那么实际跑出来的执行计划你多半会看到下面这样的结果你看这里出现的uk_customer_order_no它其实就是那个唯一约束自动弄出来的索引。也就是说这条查询它没有去从头到尾扫表而是直接顺着唯一索引就把订单给找出来了。那么如果你在自己环境里也看到了Index Scan using uk_customer_order_no这种字眼的话别慌这绝对不是出问题了。这反而说明咱们前面建表时加的那个唯一约束起作用了。执行计划这东西它不光能让你看出来“到底有没有走索引”其实还能帮你确认“它走的是哪个索引”。如果你想把这条 SQL 真正跑一遍顺便看看实际运行的情况那么可以用这个命令EXPLAINANALYZESELECTorder_id,order_no,customer_id,order_status,total_amount,created_atFROMsales.customer_orderWHEREorder_noSO202605270001;这俩有啥区别呢EXPLAIN仅仅是给你看看计划而已但是EXPLAIN ANALYZE它是真的会去执行这条 SQL 的并且还会把实际花的时间给你返回来。所以要注意了在生产环境里面如果你要对那种大 SQL 用EXPLAIN ANALYZE那你可得谨慎点。为什么呢因为它确实会真的去跑这条查询的。四、先查看已有索引既然执行计划里面都已经出现了uk_customer_order_no那么接下来千万别急着去建新索引。你要做的其实是先去看看这张表上目前已经有哪些索引了。通常来说你可以去查一下系统视图就像这样SELECTschemaname,tablename,indexname,indexdefFROMpg_indexesWHEREschemanamesalesANDtablenamecustomer_orderORDERBYindexname;跑完你多半会看到类似这样的索引这里面的情况是这样的索引来源作用customer_order_pkeyorder_id主键保证订单主键唯一uk_customer_order_noorder_no唯一约束保证订单号唯一并支持订单号精确查询这一步其实非常关键。做性能优化你绝对不能一看到查询条件就立刻手痒去建索引。你得先确认一下现有的那些约束啊、主键啊还有索引啥的它们能不能把当前的查询给覆盖掉。如果现有的索引已经能满足访问路径了你还去重复建同类索引的话那往往仅仅只是增加了维护的成本而已。五、为外键列创建索引跟主键还有唯一约束不一样的情况是外键列这东西你往往更需要主动去检查一下。你看订单表里面有个customer_id然后订单明细表里面又有个order_id和product_id。这些字段通常来说都是经常拿来做关联查询的SELECTc.customer_name,o.order_no,o.total_amountFROMsales.customer cJOINsales.customer_order oONc.customer_ido.customer_idWHEREc.customer_id1;这里要注意了sales.customer.customer_id它是客户表的主键通常来说它自己就已经带主键索引了。但是sales.customer_order.customer_id呢它只是订单表里面的一个外键列你可千万别想当然地觉得它也已经带索引了。那如果订单数据量很大的话而且你的查询又经常是按客户去查订单这时候就可以给订单表的外键列建个索引了CREATEINDEXIFNOTEXISTSidx_customer_order_customerONsales.customer_order(customer_id);订单明细表其实也是一样的道理CREATEINDEXIFNOTEXISTSidx_order_item_orderONsales.order_item(order_id);CREATEINDEXIFNOTEXISTSidx_order_item_productONsales.order_item(product_id);建完之后呢接着你可以用EXPLAIN再去看看那条关联查询的访问路径有没有发生改变EXPLAINSELECTc.customer_name,o.order_no,o.total_amountFROMsales.customer cJOINsales.customer_order oONc.customer_ido.customer_idWHEREc.customer_id1;这时候你要重点盯一下订单表sales.customer_order的访问方式它到底变没变。不过有个情况得说明一下在本系列现在的这个小样例数据里面哪怕你已经把idx_customer_order_customer给建好了执行计划里面你仍然可能看到的是这种遇到这情况别以为是你索引创建失败了其实这是优化器它自己做的成本选择。因为当前订单表里面就那么几条数据顺序扫描整张表的代价非常低。那相比之下呢走索引的话你得先去访问索引接着还得回表去读数据优化器一算觉得走索引反而更麻烦它可能就判断没必要用这个索引了。那你要是不放心的话可以用下面这个查询去确认一下索引到底是不是真的存在了SELECTschemaname,tablename,indexname,indexdefFROMpg_indexesWHEREschemanamesalesANDtablenamecustomer_orderORDERBYindexname;如果你能看到idx_customer_order_customer这条记录那说明索引确实创建成功了。至于说执行计划它到底用不用这个索引这个就得由优化器结合表数据量啊、统计信息啊、查询条件选择性还有访问成本这些东西来综合决定了。那为什么外键列往往需要建索引呢其实原因很简单关联查询通常来说都是通过这些字段去把两张表连起来的。如果sales.customer_order.customer_id上面没有合适的索引那当订单数据量很大的时候数据库往往仅仅只能去扫描大批的订单数据然后才能把某个客户的订单给找出来。六、为状态和时间查询创建组合索引如果某个字段没有唯一约束但经常作为查询条件就可以手工创建索引。例如订单状态和创建时间经常组合查询CREATEINDEXIFNOTEXISTSidx_customer_order_status_createdONsales.customer_order(order_status,created_at);这个索引适合这样的查询SELECTorder_no,order_status,total_amount,created_atFROMsales.customer_orderWHEREorder_statuspaidORDERBYcreated_atDESC;创建索引后再执行EXPLAINSELECTorder_no,order_status,total_amount,created_atFROMsales.customer_orderWHEREorder_statuspaidORDERBYcreated_atDESC;观察执行计划是否发生变化。和前面的外键索引一样小数据量下即使创建了组合索引也不一定马上看到索引扫描。这里的重点不是强行让执行计划变成Index Scan而是建立一个判断方法先确认查询场景再确认索引是否存在最后用EXPLAIN看优化器实际选择了什么访问路径。七、组合索引的顺序很重要组合索引不是把几个字段随便放一起。字段顺序会影响适用场景。继续看前面这个组合索引CREATEINDEXIFNOTEXISTSidx_customer_order_status_createdONsales.customer_order(order_status,created_at);更适合WHEREorder_statuspaidORDERBYcreated_atDESC如果查询只按created_at过滤而不使用order_status这个索引未必是最佳选择。因此设计组合索引时要先看业务查询查询场景推荐索引思路按订单号精确查询order_no唯一索引按客户查订单customer_id索引按状态查最近订单(order_status, created_at)组合索引按商品统计销量order_item.product_id索引八、更新统计信息优化器要去选执行计划的话其实它是得看统计信息的。那么如果说你的表里面数据变化挺多了的情况咱们就可以自己主动去收一下这个统计信息像这样ANALYZEsales.customer_order;ANALYZEsales.order_item;ANALYZEinventory.product;为什么要搞这一步呢其实就是为了让优化器能更清楚你这个表里的数据分布是个啥情况。比如说现在有多少行啦某个字段的选择性怎么样啦这些。要是统计信息不准的情况优化器往往就会把数据量给看走眼要么往少了估要么往多了估。数据量估错了那它选出来的执行计划往往仅仅只是看着好看实际跑起来根本就不行。九、常见问题排查问题 1为什么还没手工创建订单号索引就已经走了 Index Scan出现这个情况原因其实就是order_no上面我们加了一个唯一约束CONSTRAINTuk_customer_order_noUNIQUE(order_no)通常来说主键还有唯一约束这俩东西它会自己偷偷建一个对应的唯一索引出来。那么这个索引的话一方面它能保证你的数据不重复另一方面优化器拿它来做查询访问也是没问题的。也就是说你用订单号去查的时候走了uk_customer_order_no这个索引那是再正常不过的事情了。问题 2创建索引后执行计划仍然是 Seq Scan碰到这个先别慌这不一定是哪里搞错了。常见的几个原因我给你列一下原因说明表数据太少全表顺序扫描的成本反而更低的情况查询条件选择性低比如状态字段也就那么寥寥几个值统计信息不新需要去执行一下ANALYZE索引字段顺序不匹配组合索引它没覆盖到你现在的过滤方式我们这个系列用的kb_shop样例库里面啊数据量其实特别小。那么这种情况下哪怕你把外键列索引还有状态时间组合索引都建好了执行计划它还是去选Seq Scan这其实是非常正常的。得等到你真实的业务表里面数据到了成千上万甚至更多行的时候索引到底有没有用往往才会在执行计划和耗时上给你体现出来。问题 3索引越建越多写入变慢建索引确实能让一部分查询跑得飞快但是你得知道它也会把写入的维护成本给拉上来。咱们做订单系统的话订单表和明细表那都属于高写入的表所以在索引设计上一定要克制一点。我的建议是你得围绕真实的查询去建索引千万别看到个字段就手痒去建一个。问题 4不知道某张表有哪些索引不知道也没关系你可以去查一下像这样SELECTschemaname,tablename,indexname,indexdefFROMpg_indexesWHEREschemanameIN(sales,inventory)ORDERBYschemaname,tablename,indexname;十、本文小结这篇文章接着第七篇的事务实践咱们开始聊性能分析这块了。其实我们并没有上来就直接跟你说“加索引肯定就快”而是通过订单查询还有执行计划和索引定义这些个东西去理出来一条更靠谱的优化路子明确查询场景 查看执行计划 判断访问路径 创建合适索引 更新统计信息 再次观察计划这篇文章咱们重点得搞明白这些个东西EXPLAIN EXPLAIN ANALYZE 主键和唯一约束对应索引 CREATE INDEX pg_indexes ANALYZE 组合索引 外键列索引到了下一篇的话我们还会接着这些业务查询来讲。到时候说说怎么用视图把那些复杂的 SQL 给封装成稳定的报表接口。这样一搞的话查询结果再去复用就简单多了而且权限控制这块也能搞得更明白一点。