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

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

本文是本系列第 8 篇。上一篇通过“下单扣库存”讲解了 KingbaseES 的事务一致性和并发控制，本文开始进入性能方向，围绕索引和执行计划分析查询为什么慢、如何优化。

引言

上一篇咱们聊的是“多步骤写入怎么保证一致性”的事儿。其实事务这东西，它能把订单、明细还有库存捏合在一块，要么一起提交，要么一起回滚。但是呢，业务数据慢慢变多了之后，有个新情况就冒出来了。什么情况呢？查询开始变慢了。

搞数据库的性能优化，通常来说你真的不能光凭感觉走。也就是说，你不能随便甩一句“加个索引就快了”，也不能一看到查询慢就瞎建一堆索引。那更靠谱一点的搞法是什么呢？其实得先搞清楚你的查询场景是啥。接着去看看执行计划，然后再去判断到底要不要加索引，或者说你加的索引它到底有没有真正被用上。

那么这篇呢，咱们就围着kb_shop里的客户表、商品表还有订单表来搞，重点其实就是弄明白下面这几件事：

1. 搞懂索引到底起啥作用，还有它要付出啥代价。
2. 学会用EXPLAIN去看看 SQL 的执行计划长啥样。
3. 搞明白主键、唯一约束跟自动索引，它们仨到底是啥关系。
4. 像客户 ID、订单明细外键这种经常要用来做关联的字段，咱们给它把索引建上。
5. 聊聊为啥索引这东西不是建得越多就越好。

索引为什么能提升查询效率

索引这东西，你其实可以把它当成是数据库给某些字段额外弄出来的一个查找目录。要是没索引的话，数据库往往仅仅只能把整张表从头扫到尾。那如果有了合适的索引呢？数据库就能顺着这个目录，非常快地找到你要的那行数据在哪。

拿订单表来举个例子吧：

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_no='SO202605270001';

这条 SQL 的过滤条件非常明确：

WHEREorder_no='SO202605270001'

如果订单表只有几条数据，即使没有索引也很快；但真实系统中订单可能有几十万、几百万行，这时查询路径就很关键。

三、使用 EXPLAIN 查看执行计划

执行计划这东西，其实就是数据库跑这条 SQL 之前给你交个底。也就是说，它准备怎么去执行这条语句。

EXPLAINSELECTorder_id,order_no,customer_id,order_status,total_amount,created_atFROMsales.customer_orderWHEREorder_no='SO202605270001';

因为咱们前面给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_no='SO202605270001';

这俩有啥区别呢？EXPLAIN仅仅是给你看看计划而已，但是EXPLAIN ANALYZE它是真的会去执行这条 SQL 的，并且还会把实际花的时间给你返回来。所以要注意了，在生产环境里面，如果你要对那种大 SQL 用EXPLAIN ANALYZE，那你可得谨慎点。为什么呢？因为它确实会真的去跑这条查询的。

四、先查看已有索引

既然执行计划里面都已经出现了uk_customer_order_no，那么接下来千万别急着去建新索引。你要做的，其实是先去看看这张表上目前已经有哪些索引了。

通常来说你可以去查一下系统视图，就像这样：

SELECTschemaname,tablename,indexname,indexdefFROMpg_indexesWHEREschemaname='sales'ANDtablename='customer_order'ORDERBYindexname;

跑完你多半会看到类似这样的索引：

这里面的情况是这样的：

这一步其实非常关键。做性能优化，你绝对不能一看到查询条件就立刻手痒去建索引。你得先确认一下，现有的那些约束啊、主键啊还有索引啥的，它们能不能把当前的查询给覆盖掉。如果现有的索引已经能满足访问路径了，你还去重复建同类索引的话，那往往仅仅只是增加了维护的成本而已。

五、为外键列创建索引

跟主键还有唯一约束不一样的情况是，外键列这东西，你往往更需要主动去检查一下。

你看订单表里面有个customer_id，然后订单明细表里面又有个order_id和product_id。这些字段通常来说都是经常拿来做关联查询的：

SELECTc.customer_name,o.order_no,o.total_amountFROMsales.customer cJOINsales.customer_order oONc.customer_id=o.customer_idWHEREc.customer_id=1;

这里要注意了，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_id=o.customer_idWHEREc.customer_id=1;

这时候你要重点盯一下，订单表sales.customer_order的访问方式它到底变没变。
不过有个情况得说明一下，在本系列现在的这个小样例数据里面，哪怕你已经把idx_customer_order_customer给建好了，执行计划里面你仍然可能看到的是这种：

遇到这情况别以为是你索引创建失败了，其实这是优化器它自己做的成本选择。因为当前订单表里面就那么几条数据，顺序扫描整张表的代价非常低。那相比之下呢，走索引的话你得先去访问索引，接着还得回表去读数据，优化器一算，觉得走索引反而更麻烦，它可能就判断没必要用这个索引了。

那你要是不放心的话，可以用下面这个查询去确认一下索引到底是不是真的存在了：

SELECTschemaname,tablename,indexname,indexdefFROMpg_indexesWHEREschemaname='sales'ANDtablename='customer_order'ORDERBYindexname;

如果你能看到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_status='paid'ORDERBYcreated_atDESC;

创建索引后，再执行：

EXPLAINSELECTorder_no,order_status,total_amount,created_atFROMsales.customer_orderWHEREorder_status='paid'ORDERBYcreated_atDESC;

观察执行计划是否发生变化。和前面的外键索引一样，小数据量下即使创建了组合索引，也不一定马上看到索引扫描。这里的重点不是强行让执行计划变成Index Scan，而是建立一个判断方法：先确认查询场景，再确认索引是否存在，最后用EXPLAIN看优化器实际选择了什么访问路径。

七、组合索引的顺序很重要

组合索引不是把几个字段随便放一起。字段顺序会影响适用场景。

继续看前面这个组合索引：

CREATEINDEXIFNOTEXISTSidx_customer_order_status_createdONsales.customer_order(order_status,created_at);

更适合：

WHEREorder_status='paid'ORDERBYcreated_atDESC

如果查询只按created_at过滤，而不使用order_status，这个索引未必是最佳选择。

因此设计组合索引时，要先看业务查询：

八、更新统计信息

优化器要去选执行计划的话，其实它是得看统计信息的。那么如果说你的表里面数据变化挺多了的情况，咱们就可以自己主动去收一下这个统计信息，像这样：

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

碰到这个先别慌，这不一定是哪里搞错了。常见的几个原因我给你列一下：

我们这个系列用的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 给封装成稳定的报表接口。这样一搞的话，查询结果再去复用就简单多了，而且权限控制这块也能搞得更明白一点。