江苏龙网

  1. 首页 > 人文 > >

「数据库系列」Postgres性能调优——Index

Postgres支持在表上使用各种索引 ， 以加快查询速度 。
多列索引多列B树索引可以与涉及索引的列的任意子集的查询条件下使用 。 当（最左边）列有约束时 ， 此索引最有效 。 确切的规则是 ， 前导列上的相等约束 ， 再加上第一列上没有相等约束的任何不相等约束 ， 都将用于限制扫描的索引部分 。
Cover索引包含查询所需的所有列的索引 ， 该索引位于select语句中 。
唯一索引唯一索引是用于强制列值的唯一性或一个以上列的组合值的唯一性的索引 。
【「数据库系列」Postgres性能调优——Index】关于索引的最被误解的概念之一是了解在哪里使用主键 ， 唯一约束或唯一索引 。 让我们使用一个问题来理解这一点：
问题陈述我们要求没有重复数据的最高性能 。 哪种方法更好？主键 ， 唯一约束或唯一索引？
当表中定义了主键和唯一约束时 ， Postgres会在表中自动创建唯一索引 。 这样 ， 创建唯一约束将是多余的 ， 并且不必要地创建索引会降低Postgres的性能 。 根据Postgres产品团队的建议 ， 在表上创建唯一约束 ， 然后就无需在这些列上创建唯一索引 。 Postgres为定义的主键本身创建一个索引 。 当我们创建唯一约束时 ， Postgres会在后台自动创建索引 。但是 ， 在某些情况下 ， 甚至索引也无法提高性能 。 一种这样的情况是当我们进行不区分大小写的搜索时 。 让我们了解的情况下 ， 查询成本之间的差额区分大小写和不区分大小写的搜索我们的计划表 。 鉴于我们在该列上有一个索引scheme_name 。
EXPLAINANALYSESELECT*FROMschemeswherescheme_name='weekend_scheme'
查询计划|在方案上使用idx_scheme_name进行索引扫描（成本=0.28..8.29行=1宽度=384）计划时间：0.155ms执行时间：0.063ms
EXPLAINANALYSESELECT*FROMschemeswherelower(scheme_name)='weekend_scheme'
查询计划|对方案进行Seq扫描（成本=0.00..69.00行=5宽度=384）过滤器：（lower（（scheme_name）::text）='weekend_scheme'::text）被过滤器删除的行：999规划时间：0.119ms执行时间：0.721ms
即使我们在处创建了索引scheme_name ， 该函数lower也会降低性能 ， 因为它会付出额外的努力将所有的值转换scheme_table为小写 。
不使用索引（尽管已定义）的情况 。
LIKE‘%scheme’永远不会使用索引 ， 但LIKE‘scheme%’可能会使用索引 。 where子句中使用的大写/小写函数 。因此 ， 无论何时我们想在where子句中使用函数 ， 我们都可以通过以下方式创建索引来优化查询 。 CREATEINDEXidx_scheme_nameONschemes(lower(scheme_name))
EXPLAINANALYSESELECT*FROMschemeswherelower(scheme_name)='weekend_scheme'
查询计划|方案上的位图堆扫描（（cost=4.32..19.83行=5宽度=384））重新检查条件：（较低（（scheme_name）::text）='weekend_scheme'::text）对块：精确=1位图扫描在方案上（（cost=0.00..4.32行=5宽度=0））索引条件：（较低（（scheme_name）::text）='weekend_scheme'::text）计划时间：1.784ms执行时间：0.079ms
部分指数Postgres支持在表的行子集上建立索引（称为部分索引） 。 如果我们要重复分析与给定WHERE子句匹配的行 ， 这通常是索引数据的最佳方法 。 让我们看看如何使用部分索引来增强Postgres的性能 。
问题陈述我们要返回所有应该在上午11:00之前运行的方案 。 EXPLAINANALYSESELECT*FROMschemesWHEREstart_time<'10:00:00'
查询计划|对方案进行Seq扫描（成本=0.00..66.50行=9宽度=23）过滤器：（start_time过滤器删除的行：991规划时间：0.082ms执行时间：0.226ms
我们可以在start_time列上创建索引 ， 但是假设我们有一个庞大的数据库 ， 这对于插入 ， 更新和删除可能不是最佳选择 。 因此 ， 我们创建一个带有条件的索引 。 当我们从选择查询中知道需要什么时 ， 将使用这种索引 。 假设我们对所有在10:00:00之前启动的方案进行了大量阅读 ， 而在以后启动时则阅读不多 。 CREATEINDEXidx_scheme_nameONschemesstart_timeWHEREstart_time<'11:00:00'
EXPLAINANALYSESELECT*FROMschemesWHEREstart_time<'10:00:00'
查询计划|方案上的位图堆扫描（（cost=4.21..29.30行=9宽度=23））重新检查条件：（start_time对块：精确=8方案上的位图索引扫描（（cost=0.00..4.21行=9宽度=0））索引条件：（start_time计划时间：1.729ms执行时间：0.075ms
这样可以将执行时间从减少0.226到0.075 。
让我们确认我们没有start_time为上午11:00之后的所有方案建立索引 。 EXPLAINANALYSESELECT*FROMschemesWHEREstart_time>'12:00:00'
查询计划|对方案进行Seq扫描（成本=0.00..66.50行=6宽度=23）筛选器：（start_time筛选器删除的行：993规划时间：0.101ms执行时间：0.228ms
这证明方案表中的部分数据已编制索引 ， 其余数据未编制索引 。 我们的索引大小非常小 ， 易于维护 ， 有助于维护重新索引的任务 。
联接查询计划优化器需要选择正确的连接时 ， 有在SELECT语句要加入多个表的算法 。 Postgres根据我们使用的联接类型使用3种不同的联接算法 。
嵌套循环：在这里 ， 计划者可以对第一个表中的每个元素使用顺序扫描或索引扫描 。 当第二个表较小时 ， 计划程序将使用顺序扫描 。 在顺序扫描和索引扫描之间进行选择的基本逻辑也适用于此 。 哈希联接：在此算法中 ， 计划程序在联接键上创建较小表的哈希表 。 然后扫描较大的表 ， 在哈希表中搜索满足连接条件的行 。 首先 ， 这需要大量内存才能存储哈希表 。 合并联接：这类似于合并排序算法 。 计划者在这里对两个要联接的表进行排序 。 然后并行扫描这些表以找到匹配的值 。
EXPLAINSELECTschemes.rulesFROMscheme_rulesJOINschemesON(scheme_rules.scheme_id=schemes.id)wherescheme_name='weekend_scheme';
生产环境中索引的缺点查找未使用的索引在大型生产环境中 ， 建议使用未使用的索引 ， 因为索引会占用内存 。 PostgresWiki页面详细介绍了如何找到索引摘要 ， 重复索引和索引大小 。
CREATE/DROP索引与CREATE/DROP索引并发在大型数据库中创建和删除索引可能要花费数小时甚至数天 ， 并且该CREATEINDEX命令会阻止对表的所有写操作（它不会阻止读操作 ， 但这仍然不理想） 。
但是 ， 与并发创建的索引CREATEINDEXCONCURRENT不会获得针对写入的锁定 。 在同时创建索引时 ， Postgres首先扫描表以建立索引 ， 然后再次运行索引以查找自第一遍以来要添加的内容 。
同时创建索引也有一个缺点 。 如果在此过程中出现问题 ， 它不会回滚 ， 并留下无效的索引 。 可以使用以下查询找到无效的索引 。
SELECT*FROMpg_class ， pg_indexWHEREpg_index.indisvalid=falseANDpg_index.indexrelid=pg_class.oid;重建索引REINDEX使用存储在索引表中的数据重建索引 ， 从而替换索引的旧副本 。 如果我们怀疑表上的索引损坏 ， 则可以使用REINDEXINDEX或来重建该索引或表上的所有索引 。 REINDEXTABLE
REINDEX与删除索引和重新创建索引相似 ， 因为索引内容是从头开始重建的 。 但是 ， 锁定注意事项却大不相同 。 REINDEX锁定对索引的父表的写入但不对其进行读取 。 它还对正在处理的特定索引采取排他锁 ， 这将阻止尝试使用该索引的读取 。
另一个选择是同时删除索引并再次创建 。
结论这篇文章旨在概述Postgres如何查询数据库 。 通过更好地理解查询计划并仔细采取措施（主要是通过索引） ， 我们可以从Postgres数据库中获得最佳性能 。
还有其他提高查询性能的方法 ， 但我们会将其保存在以后的文章中 。


    推荐阅读


    上一篇：口罩|想要安全呼吸，不止有口罩，它也能帮你！

    下一篇：营养|它是被“误解”最多的鱼！其实营养不比甲鱼差