PostgreSQL专栏 ：索引原理与应用深度解析

PostgreSQL专栏 Day4：索引原理与应用深度解析

🎯 今日目标

• 深入理解PostgreSQL各种索引类型的原理
• 掌握不同索引的适用场景和性能特征
• 学会设计高效的复合索引和部分索引
• 熟练应用索引优化技巧
• 掌握索引相关的面试高频问题

📊 PostgreSQL索引全景图

#mermaid-svg-PbE4ieaZT8wCWAit {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-PbE4ieaZT8wCWAit .error-icon{fill:#552222;}#mermaid-svg-PbE4ieaZT8wCWAit .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-PbE4ieaZT8wCWAit .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-PbE4ieaZT8wCWAit .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-PbE4ieaZT8wCWAit .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-PbE4ieaZT8wCWAit .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-PbE4ieaZT8wCWAit .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-PbE4ieaZT8wCWAit .marker{fill:#333333;stroke:#333333;}#mermaid-svg-PbE4ieaZT8wCWAit .marker.cross{stroke:#333333;}#mermaid-svg-PbE4ieaZT8wCWAit svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-PbE4ieaZT8wCWAit .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-PbE4ieaZT8wCWAit .cluster-label text{fill:#333;}#mermaid-svg-PbE4ieaZT8wCWAit .cluster-label span{color:#333;}#mermaid-svg-PbE4ieaZT8wCWAit .label text,#mermaid-svg-PbE4ieaZT8wCWAit span{fill:#333;color:#333;}#mermaid-svg-PbE4ieaZT8wCWAit .node rect,#mermaid-svg-PbE4ieaZT8wCWAit .node circle,#mermaid-svg-PbE4ieaZT8wCWAit .node ellipse,#mermaid-svg-PbE4ieaZT8wCWAit .node polygon,#mermaid-svg-PbE4ieaZT8wCWAit .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-PbE4ieaZT8wCWAit .node .label{text-align:center;}#mermaid-svg-PbE4ieaZT8wCWAit .node.clickable{cursor:pointer;}#mermaid-svg-PbE4ieaZT8wCWAit .arrowheadPath{fill:#333333;}#mermaid-svg-PbE4ieaZT8wCWAit .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-PbE4ieaZT8wCWAit .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-PbE4ieaZT8wCWAit .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-PbE4ieaZT8wCWAit .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-PbE4ieaZT8wCWAit .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-PbE4ieaZT8wCWAit .cluster text{fill:#333;}#mermaid-svg-PbE4ieaZT8wCWAit .cluster span{color:#333;}#mermaid-svg-PbE4ieaZT8wCWAit div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-PbE4ieaZT8wCWAit :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}PostgreSQL索引体系按结构分类按应用分类按范围分类B-tree索引Hash索引GiST索引SP-GiST索引GIN索引BRIN索引单列索引复合索引唯一索引部分索引表达式索引覆盖索引聚簇索引非聚簇索引主键索引外键索引默认类型支持排序范围查询等值查询不支持范围几何数据全文搜索非平衡树前缀匹配反向索引数组JSON搜索块级索引大表优化

🌳 B-tree索引深度解析

B-tree索引结构原理

#mermaid-svg-zGjWE3pkrIYH6emu {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-zGjWE3pkrIYH6emu .error-icon{fill:#552222;}#mermaid-svg-zGjWE3pkrIYH6emu .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-zGjWE3pkrIYH6emu .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-zGjWE3pkrIYH6emu .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-zGjWE3pkrIYH6emu .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-zGjWE3pkrIYH6emu .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-zGjWE3pkrIYH6emu .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-zGjWE3pkrIYH6emu .marker{fill:#333333;stroke:#333333;}#mermaid-svg-zGjWE3pkrIYH6emu .marker.cross{stroke:#333333;}#mermaid-svg-zGjWE3pkrIYH6emu svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-zGjWE3pkrIYH6emu .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-zGjWE3pkrIYH6emu .cluster-label text{fill:#333;}#mermaid-svg-zGjWE3pkrIYH6emu .cluster-label span{color:#333;}#mermaid-svg-zGjWE3pkrIYH6emu .label text,#mermaid-svg-zGjWE3pkrIYH6emu span{fill:#333;color:#333;}#mermaid-svg-zGjWE3pkrIYH6emu .node rect,#mermaid-svg-zGjWE3pkrIYH6emu .node circle,#mermaid-svg-zGjWE3pkrIYH6emu .node ellipse,#mermaid-svg-zGjWE3pkrIYH6emu .node polygon,#mermaid-svg-zGjWE3pkrIYH6emu .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-zGjWE3pkrIYH6emu .node .label{text-align:center;}#mermaid-svg-zGjWE3pkrIYH6emu .node.clickable{cursor:pointer;}#mermaid-svg-zGjWE3pkrIYH6emu .arrowheadPath{fill:#333333;}#mermaid-svg-zGjWE3pkrIYH6emu .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-zGjWE3pkrIYH6emu .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-zGjWE3pkrIYH6emu .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-zGjWE3pkrIYH6emu .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-zGjWE3pkrIYH6emu .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-zGjWE3pkrIYH6emu .cluster text{fill:#333;}#mermaid-svg-zGjWE3pkrIYH6emu .cluster span{color:#333;}#mermaid-svg-zGjWE3pkrIYH6emu div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-zGjWE3pkrIYH6emu :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}B-tree索引结构根节点 Root内部节点 Internal内部节点 Internal叶子节点 Leaf叶子节点 Leaf叶子节点 Leaf叶子节点 Leaf键值1: TID指针键值2: TID指针键值3: TID指针键值4: TID指针键值5: TID指针键值6: TID指针键值7: TID指针键值8: TID指针叶子节点链表B-tree特性平衡树结构所有叶子节点同层有序存储支持范围查询高扇出度减少树高度叶子节点链表高效范围扫描

B-tree索引查询过程

B-tree索引适用场景

#mermaid-svg-RLO7dJWT6mmy5fLS {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-RLO7dJWT6mmy5fLS .error-icon{fill:#552222;}#mermaid-svg-RLO7dJWT6mmy5fLS .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-RLO7dJWT6mmy5fLS .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-RLO7dJWT6mmy5fLS .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-RLO7dJWT6mmy5fLS .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-RLO7dJWT6mmy5fLS .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-RLO7dJWT6mmy5fLS .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-RLO7dJWT6mmy5fLS .marker{fill:#333333;stroke:#333333;}#mermaid-svg-RLO7dJWT6mmy5fLS .marker.cross{stroke:#333333;}#mermaid-svg-RLO7dJWT6mmy5fLS svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-RLO7dJWT6mmy5fLS .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-RLO7dJWT6mmy5fLS .cluster-label text{fill:#333;}#mermaid-svg-RLO7dJWT6mmy5fLS .cluster-label span{color:#333;}#mermaid-svg-RLO7dJWT6mmy5fLS .label text,#mermaid-svg-RLO7dJWT6mmy5fLS span{fill:#333;color:#333;}#mermaid-svg-RLO7dJWT6mmy5fLS .node rect,#mermaid-svg-RLO7dJWT6mmy5fLS .node circle,#mermaid-svg-RLO7dJWT6mmy5fLS .node ellipse,#mermaid-svg-RLO7dJWT6mmy5fLS .node polygon,#mermaid-svg-RLO7dJWT6mmy5fLS .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-RLO7dJWT6mmy5fLS .node .label{text-align:center;}#mermaid-svg-RLO7dJWT6mmy5fLS .node.clickable{cursor:pointer;}#mermaid-svg-RLO7dJWT6mmy5fLS .arrowheadPath{fill:#333333;}#mermaid-svg-RLO7dJWT6mmy5fLS .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-RLO7dJWT6mmy5fLS .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-RLO7dJWT6mmy5fLS .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-RLO7dJWT6mmy5fLS .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-RLO7dJWT6mmy5fLS .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-RLO7dJWT6mmy5fLS .cluster text{fill:#333;}#mermaid-svg-RLO7dJWT6mmy5fLS .cluster span{color:#333;}#mermaid-svg-RLO7dJWT6mmy5fLS div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-RLO7dJWT6mmy5fLS :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}B-tree索引应用场景查询模式数据特征性能要求等值查询WHERE id = 100范围查询WHERE age BETWEEN 25 AND 35排序查询ORDER BY create_time前缀匹配WHERE name LIKE 'John%'高选择性重复值少有序需求需要排序范围查询区间查找查询频繁读多写少响应时间敏感低延迟要求数据一致性唯一性约束不适用场景低选择性重复值多频繁更新维护成本高大量插入页面分裂频繁

🔍 Hash索引特性分析

Hash索引结构与原理

Hash索引使用决策

#mermaid-svg-NXPUbMQXNNrqWK1m {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-NXPUbMQXNNrqWK1m .error-icon{fill:#552222;}#mermaid-svg-NXPUbMQXNNrqWK1m .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-NXPUbMQXNNrqWK1m .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-NXPUbMQXNNrqWK1m .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-NXPUbMQXNNrqWK1m .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-NXPUbMQXNNrqWK1m .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-NXPUbMQXNNrqWK1m .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-NXPUbMQXNNrqWK1m .marker{fill:#333333;stroke:#333333;}#mermaid-svg-NXPUbMQXNNrqWK1m .marker.cross{stroke:#333333;}#mermaid-svg-NXPUbMQXNNrqWK1m svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-NXPUbMQXNNrqWK1m .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-NXPUbMQXNNrqWK1m .cluster-label text{fill:#333;}#mermaid-svg-NXPUbMQXNNrqWK1m .cluster-label span{color:#333;}#mermaid-svg-NXPUbMQXNNrqWK1m .label text,#mermaid-svg-NXPUbMQXNNrqWK1m span{fill:#333;color:#333;}#mermaid-svg-NXPUbMQXNNrqWK1m .node rect,#mermaid-svg-NXPUbMQXNNrqWK1m .node circle,#mermaid-svg-NXPUbMQXNNrqWK1m .node ellipse,#mermaid-svg-NXPUbMQXNNrqWK1m .node polygon,#mermaid-svg-NXPUbMQXNNrqWK1m .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-NXPUbMQXNNrqWK1m .node .label{text-align:center;}#mermaid-svg-NXPUbMQXNNrqWK1m .node.clickable{cursor:pointer;}#mermaid-svg-NXPUbMQXNNrqWK1m .arrowheadPath{fill:#333333;}#mermaid-svg-NXPUbMQXNNrqWK1m .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-NXPUbMQXNNrqWK1m .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-NXPUbMQXNNrqWK1m .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-NXPUbMQXNNrqWK1m .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-NXPUbMQXNNrqWK1m .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-NXPUbMQXNNrqWK1m .cluster text{fill:#333;}#mermaid-svg-NXPUbMQXNNrqWK1m .cluster span{color:#333;}#mermaid-svg-NXPUbMQXNNrqWK1m div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-NXPUbMQXNNrqWK1m :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}仅等值查询需要范围查询需要排序分布均匀分布倾斜读多写少写操作频繁选择Hash索引?查询模式数据分布使用B-tree更新频率考虑B-treeHash索引适合评估维护成本数据倾斜可能导致冲突增加频繁更新可能导致桶分裂

🔄 GIN索引深度应用

GIN索引架构原理

GIN索引查询优化流程

🎨 GiST索引几何应用

GiST索引特性架构

几何查询类型

📦 BRIN索引块级优化

BRIN索引原理结构

#mermaid-svg-TfRJ9NjjpkvRRLqg {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-TfRJ9NjjpkvRRLqg .error-icon{fill:#552222;}#mermaid-svg-TfRJ9NjjpkvRRLqg .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-TfRJ9NjjpkvRRLqg .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-TfRJ9NjjpkvRRLqg .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-TfRJ9NjjpkvRRLqg .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-TfRJ9NjjpkvRRLqg .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-TfRJ9NjjpkvRRLqg .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-TfRJ9NjjpkvRRLqg .marker{fill:#333333;stroke:#333333;}#mermaid-svg-TfRJ9NjjpkvRRLqg .marker.cross{stroke:#333333;}#mermaid-svg-TfRJ9NjjpkvRRLqg svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-TfRJ9NjjpkvRRLqg .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-TfRJ9NjjpkvRRLqg .cluster-label text{fill:#333;}#mermaid-svg-TfRJ9NjjpkvRRLqg .cluster-label span{color:#333;}#mermaid-svg-TfRJ9NjjpkvRRLqg .label text,#mermaid-svg-TfRJ9NjjpkvRRLqg span{fill:#333;color:#333;}#mermaid-svg-TfRJ9NjjpkvRRLqg .node rect,#mermaid-svg-TfRJ9NjjpkvRRLqg .node circle,#mermaid-svg-TfRJ9NjjpkvRRLqg .node ellipse,#mermaid-svg-TfRJ9NjjpkvRRLqg .node polygon,#mermaid-svg-TfRJ9NjjpkvRRLqg .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-TfRJ9NjjpkvRRLqg .node .label{text-align:center;}#mermaid-svg-TfRJ9NjjpkvRRLqg .node.clickable{cursor:pointer;}#mermaid-svg-TfRJ9NjjpkvRRLqg .arrowheadPath{fill:#333333;}#mermaid-svg-TfRJ9NjjpkvRRLqg .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-TfRJ9NjjpkvRRLqg .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-TfRJ9NjjpkvRRLqg .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-TfRJ9NjjpkvRRLqg .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-TfRJ9NjjpkvRRLqg .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-TfRJ9NjjpkvRRLqg .cluster text{fill:#333;}#mermaid-svg-TfRJ9NjjpkvRRLqg .cluster span{color:#333;}#mermaid-svg-TfRJ9NjjpkvRRLqg div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-TfRJ9NjjpkvRRLqg :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}BRIN索引结构块范围索引摘要信息稀疏索引按物理页面分组默认128页面一组存储每组范围最小值和最大值大表友好索引大小很小Min/Max值范围边界信息包含信息是否包含NULL值位图信息特定值存在性不存储具体位置只记录范围信息查询时需要扫描整个页面范围适合大表数据有序性好的场景BRIN vs B-treeBRIN优势BRIN局限索引体积极小维护成本低大表查询加速快速排除无关页面需要数据有序或聚集存储查询精度较低可能扫描多余数据

BRIN索引适用场景

#mermaid-svg-KzvfOymw5Um5f06p {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-KzvfOymw5Um5f06p .error-icon{fill:#552222;}#mermaid-svg-KzvfOymw5Um5f06p .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-KzvfOymw5Um5f06p .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-KzvfOymw5Um5f06p .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-KzvfOymw5Um5f06p .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-KzvfOymw5Um5f06p .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-KzvfOymw5Um5f06p .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-KzvfOymw5Um5f06p .marker{fill:#333333;stroke:#333333;}#mermaid-svg-KzvfOymw5Um5f06p .marker.cross{stroke:#333333;}#mermaid-svg-KzvfOymw5Um5f06p svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-KzvfOymw5Um5f06p .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-KzvfOymw5Um5f06p .cluster-label text{fill:#333;}#mermaid-svg-KzvfOymw5Um5f06p .cluster-label span{color:#333;}#mermaid-svg-KzvfOymw5Um5f06p .label text,#mermaid-svg-KzvfOymw5Um5f06p span{fill:#333;color:#333;}#mermaid-svg-KzvfOymw5Um5f06p .node rect,#mermaid-svg-KzvfOymw5Um5f06p .node circle,#mermaid-svg-KzvfOymw5Um5f06p .node ellipse,#mermaid-svg-KzvfOymw5Um5f06p .node polygon,#mermaid-svg-KzvfOymw5Um5f06p .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-KzvfOymw5Um5f06p .node .label{text-align:center;}#mermaid-svg-KzvfOymw5Um5f06p .node.clickable{cursor:pointer;}#mermaid-svg-KzvfOymw5Um5f06p .arrowheadPath{fill:#333333;}#mermaid-svg-KzvfOymw5Um5f06p .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-KzvfOymw5Um5f06p .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-KzvfOymw5Um5f06p .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-KzvfOymw5Um5f06p .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-KzvfOymw5Um5f06p .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-KzvfOymw5Um5f06p .cluster text{fill:#333;}#mermaid-svg-KzvfOymw5Um5f06p .cluster span{color:#333;}#mermaid-svg-KzvfOymw5Um5f06p div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-KzvfOymw5Um5f06p :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}时间序列数据随机分布数据地理数据按区域BRIN索引适用性评估数据特征高度适合不适合较适合日志表按时间递增插入传感器数据时间戳有序交易记录日期字段查询城市数据按地区聚集销售数据按区域分布评估指标数据相关性物理存储与逻辑值关联查询模式范围查询为主表大小大表获益明显更新频率追加多更新少

🔧 复合索引设计策略

复合索引列顺序优化

索引优化决策树

#mermaid-svg-2G11aihRWYPcklZE {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-2G11aihRWYPcklZE .error-icon{fill:#552222;}#mermaid-svg-2G11aihRWYPcklZE .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-2G11aihRWYPcklZE .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-2G11aihRWYPcklZE .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-2G11aihRWYPcklZE .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-2G11aihRWYPcklZE .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-2G11aihRWYPcklZE .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-2G11aihRWYPcklZE .marker{fill:#333333;stroke:#333333;}#mermaid-svg-2G11aihRWYPcklZE .marker.cross{stroke:#333333;}#mermaid-svg-2G11aihRWYPcklZE svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-2G11aihRWYPcklZE .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-2G11aihRWYPcklZE .cluster-label text{fill:#333;}#mermaid-svg-2G11aihRWYPcklZE .cluster-label span{color:#333;}#mermaid-svg-2G11aihRWYPcklZE .label text,#mermaid-svg-2G11aihRWYPcklZE span{fill:#333;color:#333;}#mermaid-svg-2G11aihRWYPcklZE .node rect,#mermaid-svg-2G11aihRWYPcklZE .node circle,#mermaid-svg-2G11aihRWYPcklZE .node ellipse,#mermaid-svg-2G11aihRWYPcklZE .node polygon,#mermaid-svg-2G11aihRWYPcklZE .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-2G11aihRWYPcklZE .node .label{text-align:center;}#mermaid-svg-2G11aihRWYPcklZE .node.clickable{cursor:pointer;}#mermaid-svg-2G11aihRWYPcklZE .arrowheadPath{fill:#333333;}#mermaid-svg-2G11aihRWYPcklZE .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-2G11aihRWYPcklZE .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-2G11aihRWYPcklZE .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-2G11aihRWYPcklZE .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-2G11aihRWYPcklZE .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-2G11aihRWYPcklZE .cluster text{fill:#333;}#mermaid-svg-2G11aihRWYPcklZE .cluster span{color:#333;}#mermaid-svg-2G11aihRWYPcklZE div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-2G11aihRWYPcklZE :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}单条件查询多条件AND多条件OR模糊查询简单等值包含范围包含排序索引优化决策查询类型单列索引复合索引多个单列索引特殊索引选择高选择性字段重复值少条件复杂度按选择性排序范围条件放最后考虑ORDER BY字段位图索引扫描PostgreSQL自动合并前缀匹配B-tree索引包含匹配GIN索引 + trigram正则匹配GiST索引特殊考虑部分索引WHERE条件过滤表达式索引函数计算结果覆盖索引INCLUDE额外列

🎯 索引优化实战技巧

部分索引与表达式索引

— 部分索引：只为活跃用户建索引
CREATE INDEX idx_active_users_email
ON users (email)
WHERE status = 'active';

— 表达式索引：为函数结果建索引
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));

— 覆盖索引：避免回表查询
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (order_date, total_amount);

索引维护策略

#mermaid-svg-zxt6iIqRtWCPFmVI {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-zxt6iIqRtWCPFmVI .error-icon{fill:#552222;}#mermaid-svg-zxt6iIqRtWCPFmVI .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-zxt6iIqRtWCPFmVI .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-zxt6iIqRtWCPFmVI .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-zxt6iIqRtWCPFmVI .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-zxt6iIqRtWCPFmVI .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-zxt6iIqRtWCPFmVI .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-zxt6iIqRtWCPFmVI .marker{fill:#333333;stroke:#333333;}#mermaid-svg-zxt6iIqRtWCPFmVI .marker.cross{stroke:#333333;}#mermaid-svg-zxt6iIqRtWCPFmVI svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-zxt6iIqRtWCPFmVI .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-zxt6iIqRtWCPFmVI .cluster-label text{fill:#333;}#mermaid-svg-zxt6iIqRtWCPFmVI .cluster-label span{color:#333;}#mermaid-svg-zxt6iIqRtWCPFmVI .label text,#mermaid-svg-zxt6iIqRtWCPFmVI span{fill:#333;color:#333;}#mermaid-svg-zxt6iIqRtWCPFmVI .node rect,#mermaid-svg-zxt6iIqRtWCPFmVI .node circle,#mermaid-svg-zxt6iIqRtWCPFmVI .node ellipse,#mermaid-svg-zxt6iIqRtWCPFmVI .node polygon,#mermaid-svg-zxt6iIqRtWCPFmVI .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-zxt6iIqRtWCPFmVI .node .label{text-align:center;}#mermaid-svg-zxt6iIqRtWCPFmVI .node.clickable{cursor:pointer;}#mermaid-svg-zxt6iIqRtWCPFmVI .arrowheadPath{fill:#333333;}#mermaid-svg-zxt6iIqRtWCPFmVI .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-zxt6iIqRtWCPFmVI .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-zxt6iIqRtWCPFmVI .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-zxt6iIqRtWCPFmVI .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-zxt6iIqRtWCPFmVI .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-zxt6iIqRtWCPFmVI .cluster text{fill:#333;}#mermaid-svg-zxt6iIqRtWCPFmVI .cluster span{color:#333;}#mermaid-svg-zxt6iIqRtWCPFmVI div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-zxt6iIqRtWCPFmVI :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}索引维护策略监控指标维护操作优化时机索引使用情况pg_stat_user_indexes索引膨胀程度pgstattuple扩展查询性能执行时间监控维护成本更新操作影响REINDEX重建消除碎片VACUUM维护回收空间ANALYZE更新统计信息删除无用索引减少维护开销性能下降时查询变慢索引膨胀严重空间浪费大统计信息过期执行计划不准定期维护预防性操作维护流程1. 性能基线测量2. 识别问题索引3. 制定维护计划4. 执行维护操作5. 验证效果

索引监控SQL

— 查看索引使用情况
SELECT schemaname, tablename, indexname,
idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

— 查找无用索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

— 查看索引大小
SELECT schemaname, tablename, indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

🎯 高频面试题解析

面试题1：什么情况下索引不生效？

面试题2：复合索引的最左前缀原则

面试题3：部分索引和表达式索引的应用场景

面试题4：如何选择合适的索引类型？

🔍 索引性能分析实战

索引效果评估体系

索引优化案例分析

— 案例1：订单查询优化
— 原始查询（慢）
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 12345
AND order_date >= '2024-01-01'
AND status = 'completed'
ORDER BY order_date DESC
LIMIT 10;

— 优化方案：创建复合索引
CREATE INDEX idx_orders_customer_date_status
ON orders (customer_id, order_date DESC, status)
WHERE status IN ('completed', 'shipped');

— 案例2：JSON查询优化
— 原始查询（慢）
SELECT * FROM products
WHERE attributes–>>'category' = 'electronics'
AND (attributes–>>'price')::numeric < 1000;

— 优化方案：GIN索引 + 表达式索引
CREATE INDEX idx_products_gin ON products USING GIN (attributes);
CREATE INDEX idx_products_category ON products ((attributes–>>'category'));
CREATE INDEX idx_products_price ON products (((attributes–>>'price')::numeric));

🚀 PostgreSQL特有索引特性

索引并发构建

#mermaid-svg-7WNqgZURmDeDjeI0 {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-7WNqgZURmDeDjeI0 .error-icon{fill:#552222;}#mermaid-svg-7WNqgZURmDeDjeI0 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-7WNqgZURmDeDjeI0 .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-7WNqgZURmDeDjeI0 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-7WNqgZURmDeDjeI0 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-7WNqgZURmDeDjeI0 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-7WNqgZURmDeDjeI0 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-7WNqgZURmDeDjeI0 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-7WNqgZURmDeDjeI0 .marker.cross{stroke:#333333;}#mermaid-svg-7WNqgZURmDeDjeI0 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-7WNqgZURmDeDjeI0 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-7WNqgZURmDeDjeI0 .cluster-label text{fill:#333;}#mermaid-svg-7WNqgZURmDeDjeI0 .cluster-label span{color:#333;}#mermaid-svg-7WNqgZURmDeDjeI0 .label text,#mermaid-svg-7WNqgZURmDeDjeI0 span{fill:#333;color:#333;}#mermaid-svg-7WNqgZURmDeDjeI0 .node rect,#mermaid-svg-7WNqgZURmDeDjeI0 .node circle,#mermaid-svg-7WNqgZURmDeDjeI0 .node ellipse,#mermaid-svg-7WNqgZURmDeDjeI0 .node polygon,#mermaid-svg-7WNqgZURmDeDjeI0 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-7WNqgZURmDeDjeI0 .node .label{text-align:center;}#mermaid-svg-7WNqgZURmDeDjeI0 .node.clickable{cursor:pointer;}#mermaid-svg-7WNqgZURmDeDjeI0 .arrowheadPath{fill:#333333;}#mermaid-svg-7WNqgZURmDeDjeI0 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-7WNqgZURmDeDjeI0 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-7WNqgZURmDeDjeI0 .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-7WNqgZURmDeDjeI0 .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-7WNqgZURmDeDjeI0 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-7WNqgZURmDeDjeI0 .cluster text{fill:#333;}#mermaid-svg-7WNqgZURmDeDjeI0 .cluster span{color:#333;}#mermaid-svg-7WNqgZURmDeDjeI0 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-7WNqgZURmDeDjeI0 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}索引并发操作CREATE INDEX CONCURRENTLYDROP INDEX CONCURRENTLYREINDEX CONCURRENTLY优势特点使用限制注意事项不阻塞写操作允许并发DML生产环境友好减少服务中断渐进式构建分阶段完成不能在事务中执行autocommit模式可能构建失败需要处理invalid状态时间较长需要多次扫描表监控构建进度pg_stat_progress_create_index处理失败索引手动删除invalid索引避免高峰期选择合适时间窗口安全删除索引不影响查询两阶段删除先标记后删除在线重建PostgreSQL 12+减少锁定时间提高可用性

索引高级特性

#mermaid-svg-gxrWdiTzWH6NDb5W {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-gxrWdiTzWH6NDb5W .error-icon{fill:#552222;}#mermaid-svg-gxrWdiTzWH6NDb5W .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-gxrWdiTzWH6NDb5W .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-gxrWdiTzWH6NDb5W .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-gxrWdiTzWH6NDb5W .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-gxrWdiTzWH6NDb5W .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-gxrWdiTzWH6NDb5W .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-gxrWdiTzWH6NDb5W .marker{fill:#333333;stroke:#333333;}#mermaid-svg-gxrWdiTzWH6NDb5W .marker.cross{stroke:#333333;}#mermaid-svg-gxrWdiTzWH6NDb5W svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-gxrWdiTzWH6NDb5W .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-gxrWdiTzWH6NDb5W .cluster-label text{fill:#333;}#mermaid-svg-gxrWdiTzWH6NDb5W .cluster-label span{color:#333;}#mermaid-svg-gxrWdiTzWH6NDb5W .label text,#mermaid-svg-gxrWdiTzWH6NDb5W span{fill:#333;color:#333;}#mermaid-svg-gxrWdiTzWH6NDb5W .node rect,#mermaid-svg-gxrWdiTzWH6NDb5W .node circle,#mermaid-svg-gxrWdiTzWH6NDb5W .node ellipse,#mermaid-svg-gxrWdiTzWH6NDb5W .node polygon,#mermaid-svg-gxrWdiTzWH6NDb5W .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-gxrWdiTzWH6NDb5W .node .label{text-align:center;}#mermaid-svg-gxrWdiTzWH6NDb5W .node.clickable{cursor:pointer;}#mermaid-svg-gxrWdiTzWH6NDb5W .arrowheadPath{fill:#333333;}#mermaid-svg-gxrWdiTzWH6NDb5W .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-gxrWdiTzWH6NDb5W .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-gxrWdiTzWH6NDb5W .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-gxrWdiTzWH6NDb5W .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-gxrWdiTzWH6NDb5W .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-gxrWdiTzWH6NDb5W .cluster text{fill:#333;}#mermaid-svg-gxrWdiTzWH6NDb5W .cluster span{color:#333;}#mermaid-svg-gxrWdiTzWH6NDb5W div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-gxrWdiTzWH6NDb5W :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}PostgreSQL索引高级特性条件索引包含列索引索引扫描优化WHERE子句过滤只索引满足条件的行大幅减少索引大小提高维护效率精确匹配查询查询条件必须匹配INCLUDE语法PostgreSQL 11+非键列包含避免回表操作覆盖查询优化所有列都在索引中索引跳跃扫描Skip Scan优化位图索引扫描多索引合并索引条件下推Index Condition Pushdown实际应用电商查询优化商品分类+价格范围日志分析时间范围+状态过滤用户系统活跃用户邮箱查询

📊 索引监控与诊断

索引健康状况检查

#mermaid-svg-ncbeC9CvEEP0JtP1 {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-ncbeC9CvEEP0JtP1 .error-icon{fill:#552222;}#mermaid-svg-ncbeC9CvEEP0JtP1 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-ncbeC9CvEEP0JtP1 .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-ncbeC9CvEEP0JtP1 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-ncbeC9CvEEP0JtP1 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-ncbeC9CvEEP0JtP1 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-ncbeC9CvEEP0JtP1 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-ncbeC9CvEEP0JtP1 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-ncbeC9CvEEP0JtP1 .marker.cross{stroke:#333333;}#mermaid-svg-ncbeC9CvEEP0JtP1 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-ncbeC9CvEEP0JtP1 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-ncbeC9CvEEP0JtP1 .cluster-label text{fill:#333;}#mermaid-svg-ncbeC9CvEEP0JtP1 .cluster-label span{color:#333;}#mermaid-svg-ncbeC9CvEEP0JtP1 .label text,#mermaid-svg-ncbeC9CvEEP0JtP1 span{fill:#333;color:#333;}#mermaid-svg-ncbeC9CvEEP0JtP1 .node rect,#mermaid-svg-ncbeC9CvEEP0JtP1 .node circle,#mermaid-svg-ncbeC9CvEEP0JtP1 .node ellipse,#mermaid-svg-ncbeC9CvEEP0JtP1 .node polygon,#mermaid-svg-ncbeC9CvEEP0JtP1 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-ncbeC9CvEEP0JtP1 .node .label{text-align:center;}#mermaid-svg-ncbeC9CvEEP0JtP1 .node.clickable{cursor:pointer;}#mermaid-svg-ncbeC9CvEEP0JtP1 .arrowheadPath{fill:#333333;}#mermaid-svg-ncbeC9CvEEP0JtP1 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-ncbeC9CvEEP0JtP1 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-ncbeC9CvEEP0JtP1 .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-ncbeC9CvEEP0JtP1 .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-ncbeC9CvEEP0JtP1 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-ncbeC9CvEEP0JtP1 .cluster text{fill:#333;}#mermaid-svg-ncbeC9CvEEP0JtP1 .cluster span{color:#333;}#mermaid-svg-ncbeC9CvEEP0JtP1 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-ncbeC9CvEEP0JtP1 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}索引健康检查流程1. 使用情况分析2. 性能影响评估3. 存储开销分析4. 维护成本评估5. 优化建议生成查询pg_stat_user_indexesidx_scan, idx_tup_read识别未使用索引idx_scan = 0分析访问模式读写比例执行计划分析Index Scan vs Seq Scan查询时间对比有无索引差异并发性能测试高负载下表现索引大小统计pg_relation_size表索引比例索引总大小/表大小膨胀程度检查pgstattuple扩展更新频率影响DML操作成本维护窗口需求REINDEX时间空间回收需求VACUUM效果删除无用索引减少维护开销合并相似索引优化存储调整索引参数填充因子等

索引诊断SQL工具箱

— 索引使用情况排行
SELECT
schemaname, tablename, indexname,
idx_scan, idx_tup_read, idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

— 找出重复和冗余索引
WITH index_cols AS (
SELECT schemaname, tablename, indexname,
array_agg(attname ORDER BY attnum) as cols
FROM pg_indexes i
JOIN pg_attribute a ON a.attrelid = (schemaname||'.'||tablename)::regclass
WHERE attnum > 0 AND NOT attisdropped
GROUP BY schemaname, tablename, indexname
)
SELECT i1.schemaname, i1.tablename,
i1.indexname as index1, i2.indexname as index2
FROM index_cols i1
JOIN index_cols i2 ON i1.schemaname = i2.schemaname
AND i1.tablename = i2.tablename
AND i1.indexname < i2.indexname
WHERE i1.cols <@ i2.cols OR i2.cols <@ i1.cols;

— 索引膨胀检查
SELECT schemaname, tablename, indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as size,
round(100 * (1 – (avg_leaf_density/100.0)), 2) as bloat_pct
FROM pg_stat_user_indexes si
JOIN pgstattuple_approx(indexrelid) pst ON true
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

📈 索引性能调优最佳实践

索引设计原则总结

索引优化检查清单

📝 实战练习

练习1：电商系统索引设计

— 场景：电商订单表，设计合适的索引
CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TIMESTAMPTZ NOT NULL,
status VARCHAR(20) NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
shipping_address JSONB,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

— 常见查询模式：
— 1. 查询客户的所有订单：WHERE customer_id = ?
— 2. 查询特定状态的订单：WHERE status = ?
— 3. 查询时间范围内的订单：WHERE order_date BETWEEN ? AND ?
— 4. 客户最近订单：WHERE customer_id = ? ORDER BY order_date DESC LIMIT 10

— 推荐索引设计
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
CREATE INDEX idx_orders_status ON orders (status) WHERE status IN ('pending', 'processing');
CREATE INDEX idx_orders_date ON orders (order_date);
CREATE INDEX idx_orders_shipping_gin ON orders USING GIN (shipping_address);

练习2：日志表BRIN索引应用

— 场景：系统日志表，时间序列数据
CREATE TABLE system_logs (
log_id BIGSERIAL PRIMARY KEY,
log_time TIMESTAMPTZ NOT NULL,
level VARCHAR(10) NOT NULL,
message TEXT,
source_ip INET,
user_id INTEGER
);

— 数据特征：按时间顺序插入，查询多为时间范围查询
— 推荐使用BRIN索引
CREATE INDEX idx_logs_time_brin ON system_logs USING BRIN (log_time);
CREATE INDEX idx_logs_level_btree ON system_logs (level);

— 查询示例
SELECT COUNT(*) FROM system_logs
WHERE log_time >= '2024-01-01'
AND log_time < '2024-02-01'
AND level = 'ERROR';

📋 今日总结

✅ 核心掌握点

🎯 面试重点强化

#mermaid-svg-M6h7bSfsJRQlUaoB {font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-M6h7bSfsJRQlUaoB .error-icon{fill:#552222;}#mermaid-svg-M6h7bSfsJRQlUaoB .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-M6h7bSfsJRQlUaoB .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-M6h7bSfsJRQlUaoB .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-M6h7bSfsJRQlUaoB .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-M6h7bSfsJRQlUaoB .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-M6h7bSfsJRQlUaoB .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-M6h7bSfsJRQlUaoB .marker{fill:#333333;stroke:#333333;}#mermaid-svg-M6h7bSfsJRQlUaoB .marker.cross{stroke:#333333;}#mermaid-svg-M6h7bSfsJRQlUaoB svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-M6h7bSfsJRQlUaoB .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-M6h7bSfsJRQlUaoB .cluster-label text{fill:#333;}#mermaid-svg-M6h7bSfsJRQlUaoB .cluster-label span{color:#333;}#mermaid-svg-M6h7bSfsJRQlUaoB .label text,#mermaid-svg-M6h7bSfsJRQlUaoB span{fill:#333;color:#333;}#mermaid-svg-M6h7bSfsJRQlUaoB .node rect,#mermaid-svg-M6h7bSfsJRQlUaoB .node circle,#mermaid-svg-M6h7bSfsJRQlUaoB .node ellipse,#mermaid-svg-M6h7bSfsJRQlUaoB .node polygon,#mermaid-svg-M6h7bSfsJRQlUaoB .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-M6h7bSfsJRQlUaoB .node .label{text-align:center;}#mermaid-svg-M6h7bSfsJRQlUaoB .node.clickable{cursor:pointer;}#mermaid-svg-M6h7bSfsJRQlUaoB .arrowheadPath{fill:#333333;}#mermaid-svg-M6h7bSfsJRQlUaoB .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-M6h7bSfsJRQlUaoB .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-M6h7bSfsJRQlUaoB .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-M6h7bSfsJRQlUaoB .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-M6h7bSfsJRQlUaoB .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-M6h7bSfsJRQlUaoB .cluster text{fill:#333;}#mermaid-svg-M6h7bSfsJRQlUaoB .cluster span{color:#333;}#mermaid-svg-M6h7bSfsJRQlUaoB div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-M6h7bSfsJRQlUaoB :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}索引面试核心考点索引失效场景复合索引原理索引类型选择性能优化方法函数包装字段类型不匹配前导通配符最左前缀原则列顺序设计查询模式匹配数据类型特征查询模式分析性能要求评估监控指标分析执行计划解读系统化优化流程

💡 实战建议

🔧 索引工具箱

• pg_stat_user_indexes：索引使用统计
• pgstattuple：索引膨胀分析
• pg_stat_statements：查询性能统计
• EXPLAIN (ANALYZE, BUFFERS)：执行计划分析
• pg_stat_progress_create_index：索引构建进度