前言写 SQL 的人都建过索引。但你回忆一下上次建索引是不是create index一敲就完事了十有八九是。默认给的就是 Btree 嘛用着也没出过啥大毛病。可索引这玩意儿其实分好多种——建索引语句里using后面跟的那个词行话叫索引访问方法金仓数据库一口气提供了好几种。Btree 只是其中最常见的一个不是唯一的一个。这事儿的差别能有多大举个场景你就明白了。一张按时间不停追加的流水表几个亿行给它上 Btree 索引磁盘先被吃掉一大块每次写入还得维护这么个大块头换 BRIN几十 KB 的事查询照样提速。而全文检索那种查询Btree 压根帮不上忙得 GIN 出马。所以这篇就把六种主力索引挨个过一遍。每种什么路数、吃哪碗饭、什么时候掉链子例子都给全了复制过去自己就能跑。一、先混个脸熟六位选手一张表索引类型一句话人设主场Btree有序树等值范围排序通吃默认首选大部分场景Hash哈希一次定位只认等值高基数列的纯等值查询GIN倒排一个键挂一串位置全文检索、数组、JSONBGiST通用搜索树框架啥都能装几何、多维、范围类型SP-GiST空间分区树切互不重叠的区域四叉树、k-D 树、基数树类数据BRIN只记块区间的取值范围按时间追加的流水、日志讲的顺序就按表格来的。Btree 打头毕竟它是默认选项中间 Hash 和 GIN 算是各有绝活的GiST、SP-GiST、BRIN 这仨平时存在感低可真碰上对口的场景比什么都香。二、Btree闭着眼选不会错但得知道它凭啥强从 Btree 说起。它是金仓数据库的默认索引B 树实现。这树有个特点每一层都是排好序的凭这个特点等值和范围条件它都能接——、、、、、IN来者不拒。排序类的活它也接ORDER BY、MIN、MAX、归并连接优化器碰上这些第一个想起的就是它。空口无凭跑个例子。10 万行的表查id 10createtablet1(idint,infotext);insertintot1values(generate_series(1,100000),md5(random()::text));analyze;-- 没索引explainanalyzeselect*fromt1whereid10;QUERYPLAN---------------------------------------------------------------------Seq ScanonT1(cost0.00..2084.00rows10width37)(actualtime0.024..20.771rows9loops1)Filter:(ID10)RowsRemovedbyFilter:99991Executiontime:20.804ms为了 9 行结果顺带扫了 99991 行无关的20 毫秒就耗在这上面。建上索引再看createindexi_btreeont1usingbtree(id);explainanalyzeselect*fromt1whereid10;QUERYPLAN---------------------------------------------------------------------IndexScanusingI_BTREEonT1(cost0.29..8.45rows9width37)(actualtime0.006..0.011rows9loops1)IndexCond:(ID10)Executiontime:0.036ms577 倍的差距。接着看个更隐蔽的好处聚合函数也能蹭索引的排序explainanalyzeselectmin(id)fromt1;QUERYPLAN---------------------------------------------------------------------Result(cost0.33..0.34rows1width4)InitPlan1(returns$0)-Limit(...)-IndexOnly ScanusingI_BTREEonT1(...)IndexCond:(IDISNOTNULL)Heap Fetches:1求个最小值压根不用碰表索引第一格拿完就收工。这就是数据排好序的福利。当然 Btree 也有吃瘪的时候。条件筛完还剩半张表的那种查询走索引纯属绕远优化器扭头就全表扫了。选择率这个话题之前讲调优方法论那篇里掰扯过不重复了只留一句结论命中行数在表的 1% 到 20% 之间索引的收益最大。三、Hash等值查询的快枪手可惜路子太窄Hash 索引的脑回路简单粗暴管你什么顺序把键值哈希一把直接算出位置。理想状况下一次检索完事。对比一下 Btree从根节点一层一层往下比比到叶子才算完路上的 IO 和 CPU 都是多花的。所以纯等值查询、列的取值又足够杂订单号、唯一编号这种Hash 有机会比 Btree 还利索。createtablet2(idint,infotext);insertintot2values(generate_series(1,100000),md5(random()::text));analyze;createindexi_hashont2usinghash(id);explainanalyzeselect*fromt2whereid10;-- 等值走 Hash 索引QUERYPLAN---------------------------------------------------------------------IndexScanusingI_HASHonT2(cost0.00..8.02rows1width37)(actualtime0.013..0.014rows1loops1)IndexCond:(ID10)Executiontime:0.033ms快是真快可惜就会这一招。范围条件它碰都不碰explainanalyzeselect*fromt2whereid10;-- 非等值索引不搭理QUERYPLAN---------------------------------------------------------------------Seq ScanonT2(cost0.00..2084.00rows10width37)(actualtime0.015..26.222rows9loops1)Filter:(ID10)RowsRemovedbyFilter:99991同一个索引等值走索引范围走全表26 毫秒跟没建一样。另外重复值是 Hash 的命门。一列翻来覆去就那几个值哈希出来全挤在一个桶里冲突多了性能哗哗往下掉。状态位、类型号这种两三个取值的列就别为难它了——讲句实话这种列建啥索引前都得多想想。四、GIN倒排思路专治里面包含什么Btree 和 Hash 回答的问题都是某个值出现在哪一行。GIN 把问题反过来了它存的是键加一串位置‘Tom’ 这个键下面挂着 TID5、TID10、TID100意思是这个值在三行里出现过。查 ‘Tom’直接跳到这三行完事。什么时候用得上这种反向操作条件本身带包含意味的时候。数组里有没有某个元素、一段文本里出没出现某个词问法都是里面装没装。所以 GIN 的主场写得很清楚全文检索、数组、JSONB一个字段塞一堆值的那种类型。全文检索走一个createtablet3(idint,infotext);insertintot3values(generate_series(1,10000),md5(random()::text));createindexi_t3_ginont3usinggin(to_tsvector(english,info));analyze;explainanalyzeselect*fromt3whereto_tsvector(english,info) plainto_tsquery(hello);QUERYPLAN---------------------------------------------------------------------Bitmap Heap ScanonT3(cost12.25..16.77rows1width37)(actualtime0.029..0.029rows0loops1)Recheck Cond:(TO_TSVECTOR(ENGLISH::REGCONFIG,INFO) PLAINTO_TSQUERY(hello::TEXT))-BitmapIndexScanonI_T3_GIN(cost0.00..12.25rows1width0)IndexCond:(TO_TSVECTOR(ENGLISH::REGCONFIG,INFO) PLAINTO_TSQUERY(hello::TEXT))这种匹配Btree 想帮忙都插不上手。这就是 GIN 的地盘。五、GiST不是一棵树是一套造树的框架GiST 名字里带个通用Generalized Search Tree确实通用得可以——它压根不是某种具体的树是个搭树的框架各种索引模式都能往上面装。装什么最合适那些没有天然大小顺序的数据。点、多边形、范围类型、多维数据比大小比不出来但能问谁包含谁“谁跟谁相交”“谁在谁左边”这类空间关系正好是 GiST 的菜。建张带坐标的表试一把createtablet1(idint,pos1point);insertintot1selectgenerate_series(1,100000),point(round((random()*1000)::numeric,2),round((random()*1000)::numeric,2));createindexidx_t1_gistont1usinggist(pos1);analyzet1;-- 找落在圆心(100,100)半径10的圆里的所有点explainselect*fromt1wherecircle((100,100) 10)pos1;QUERYPLAN---------------------------------------------------------------------Bitmap Heap Scanont1(cost5.06..272.12rows100width20)Recheck Cond:((100,100),10::circle pos1)-BitmapIndexScanonidx_t1_gist(cost0.00..5.03rows100width0)IndexCond:(pos1(100,100),10::circle)圆里有哪些点这种问题拿给 Btree它只能干瞪眼拿给 GiST正常发挥。做位置服务、地图圈选、范围重叠检测的记住这条路。六、SP-GiSTGiST 的空间分区特化版SP-GiSTSP 是 space-partitioned空间分区。它跟 GiST 同为框架差别就一条但这条很关键GiST 划分出来的区域允许你压着我、我压着你SP-GiST 不行它把空间递归地切成分毫不让的几块谁也不许搭界。四叉树、k-D 树、基数树都是按这个规矩长的。规矩死板换来的是搜索简单。区域互不重叠往下找的时候就一条道不用像 GiST 那样把可能沾边的分支挨个都探一遍。数据要是天生切得干净SP-GiST 一般更快也更省地方。金仓数据库给一批类型现成做好了 SP-GiST 操作类常用的几个quad_point_ops和kd_point_ops管点数据range_ops管范围类型text_ops管文本。来个点的例子createtablet1(ppoint);createindexidx_t1ont1usingspgist(p);analyzet1;-- 找所有在点(10,10)左边的点explainselect*fromt1whereppoint(10,10);QUERYPLAN-----------------------------------------------------------------------Bitmap Heap Scanont1(cost5.57..17.87rows184width16)Recheck Cond:(p(10,10)::point)-BitmapIndexScanonidx_t1(cost0.00..5.52rows184width0)IndexCond:(p(10,10)::point)那它跟 GiST 二选一的时候怎么挑给条粗规则数据能切得干干净净、互不打扰的点云、IP 前缀、规整网格先试 SP-GiST切出来免不了犬牙交错的还是 GiST 稳当。七、BRIN体积小到可以忽略的摘要索引BRIN全称 Block Range Index块范围索引。这位的思路跟前头几位都不一路每一行的值落在哪它根本不关心它只记个大概——每连续一段数据块里这列的值最小到多少、最大到多少一段记一条。你可以把它理解成目录页上那行小字摘要正文一概不抄。摘要自然有摘要的代价但先看甜头。同一张 10 万行的表BRIN 和 Btree 各建一个比比个头kingbase# \diListofrelationsSchema|Name|Type|Owner|Table|Size-----------------------------------------------PUBLIC|I5_BRIN|index|nimo|T5|48kBPUBLIC|I5_BTREE|index|nimo|T5|2208kB48kB 摆在那2208kB 也摆在那46 倍。表越大这个差距越吓人写入端的维护开销也是同样悬殊。几亿行的流水表要是把 Btree 换成 BRIN省的不光是磁盘每写一笔的索引维护账单也薄了一摞。代价呢看查询就明白了。它只能把范围划到块这一级块里哪些行真满足条件还得回头挨个核createtablet5(idint,nametext);insertintot5values(generate_series(1,100000),md5(random()::text));analyze;createindexi5_brinont5usingbrin(id);explainanalyzeselect*fromt5whereid10;QUERYPLAN--------------------------------------------------------------------Bitmap Heap ScanonT5(cost12.08..48.92rows10width37)(actualtime0.039..2.498rows9loops1)Recheck Cond:(ID10)RowsRemovedbyIndexRecheck:15351Heap Blocks: lossy128-BitmapIndexScanonI5_BRIN(cost0.00..12.07rows10width0)(actualtime0.028..0.028rows1280loops1)计划里俩数字把它的工作方式全交代了。Heap Blocks: lossy128索引圈了 128 个块Rows Removed by Index Recheck: 15351圈进来的这批里有一万五千行其实是误伤回表一核又给扔了。糙是糙了点可架不住快啊2.5ms要啥自行车。什么时候用它最对味数据按时间一天天追加进去的日志表、流水表。这种表的时间列跟物理块天然对齐——早写的块里全靠前的日期晚写的块里全靠后的日期每段摘要又窄又准。反过来列值在物理上乱序分布的拿 md5 列建 BRIN 就这种效果每段块的摘要都得写成从最小到最大等于啥也没过滤白搭一遍索引扫描还不如痛痛快快全表扫。用 BRIN 翻车的十个里九个翻在这。八、标题之外还有一位值得认识的Bitmap标题点名的六位之外金仓数据库其实还有个 Bitmap 索引不交代一下总觉得少一块。它的对口客户是低基数列。一列拢共没几个取值性别、状态位这种每个取值给一张压缩位图查状态1等于直接抽出那张图来看做 OLAP 分析的会喜欢。但有两条红线取值个数最好在 1 到 10000 之间更新别太勤位图这东西改一下就得解压、重压来回折腾交易型的表别凑这个热闹。顺便防个混淆。执行计划里常见的 Bitmap Index Scan、Bitmap Heap Scan跟 Bitmap 索引是两码事——那是执行器把多个普通索引的结果在内存里拼成位图再做 AND/OR属于扫描手段不是索引类型。名字撞了车心里得分清楚。九、真到选的时候看这张表六种索引全过完了。收拢一下按你要干什么活来对号你的场景优先考虑要点提醒等值 范围混着来还要排序Btree闭眼选它基本不亏通用性最好纯等值列基数很高Hash比 Btree 少几跳但范围条件彻底无缘全文检索、数组、JSONB 包含查询GINBtree 干不了的活得它上几何、空间、范围类型的交叠包含GiST区域可重叠适应性强点云、前缀这类能切不重叠分区的数据SP-GiST每层单路搜索分区切得干净时更快超大表 按时间追加的流水日志BRIN体积忽略不计但要求数据物理有序否则翻车低基数列 OLAP 少更新Bitmap更新代价高交易系统慎用表之外再啰嗦几句手感层面的东西。拿到一个建索引的需求我一般是这么盘的。查询条件长啥样等值还是范围还是包含类的等值 Hash、Btree 随便挑范围只有 Btree包含类的就得 GIN 或者 GiST 这边。接着盘数据这列基数多高、重复多不多、物理顺序跟列值有没有呼应BRIN 吃的就是这碗饭。最后盘表本身天天被 update 狂轰滥炸的表索引个个都是拖油瓶Bitmap 更是提都别提。维护上还有俩坑一并说了。头一个索引多了 insert 会跟着遭罪隔三差五查一把sys_stat_user_indexesidx_scan一直是 0 的那种直接删不用心疼。再一个delete 清不掉索引里的旧数据Btree 在这点上尤其轴整页空了才肯回收半空的页就那么耗着日积月累索引虚胖得厉害。所以该reindex就 reindex弄完补一句 analyze齐活。十、最后通篇下来选型这事的窍门就一句索引结构得跟数据长得合拍。Btree 是那个不会错的保守答案。可保守答案外头还有一片天地呢——Hash 就一个绝招但那招是真快GIN 专治包含类的查询Btree 插不上手的那种GiST 管着没有大小顺序的空间数据BRIN 拿精度换体积大流水表的救星。还有个坑这儿故意没展开类型明明选对了查询就是不走索引。函数把列包住了、联合索引前导列没对上、局部索引的谓词跟 where 条件对不齐、LIKE 用错了操作符类——全是写法层面的毛病随便单拎一个出来都够写一篇后面慢慢安排。先把类型选对再操心走不走这个顺序别弄反了。