江苏龙网

  1. 首页 > 人文 > >

光一样的少年|「数据库系列」Postgres性能调优——Index( 二 )


部分指数Postgres支持在表的行子集上建立索引（称为部分索引） 。如果我们要重复分析与给定WHERE子句匹配的行 ， 这通常是索引数据的最佳方法 。 让我们看看如何使用部分索引来增强Postgres的性能 。
问题陈述我们要返回所有应该在上午11:00之前运行的方案 。 EXPLAIN ANALYSE SELECT * FROM schemes WHERE start_time < '10:00:00'
查询计划| 对方案进行Seq扫描（成本= 0.00..66.50行= 9宽度= 23）过滤器：（start_time <'10：00：00'）过滤器删除的行：991规划时间：0.082 ms执行时间：0.226ms
我们可以在start_time列上创建索引 ， 但是假设我们有一个庞大的数据库 ， 这对于插入 ， 更新和删除可能不是最佳选择 。 因此 ， 我们创建一个带有条件的索引 。 当我们从选择查询中知道需要什么时 ， 将使用这种索引 。 假设我们对所有在10:00:00之前启动的方案进行了大量阅读 ， 而在以后启动时则阅读不多 。 CREATE INDEX idx_scheme_name ON schemes start_time WHERE start_time < '11:00:00'
EXPLAIN ANALYSE SELECT * FROM schemes WHERE start_time < '10:00:00'
【光一样的少年|「数据库系列」Postgres性能调优——Index】查询计划| 方案上的位图堆扫描（（cost = 4.21..29.30行= 9宽度= 23））重新检查条件：（start_time <'10：00：00'）对块：精确= 8方案上的位图索引扫描（（cost = 0.00..4.21行= 9宽度= 0））索引条件：（start_time <'10：00：00'）计划时间：1.729 ms执行时间：0.075 ms
这样可以将执行时间从减少0.226到0.075 。
让我们确认我们没有start_time为上午11:00之后的所有方案建立索引 。 EXPLAIN ANALYSE SELECT * FROM schemes WHERE start_time >'12:00:00'
查询计划| 对方案进行Seq扫描（成本= 0.00..66.50行= 6宽度= 23）筛选器：（start_time <'12：00：00'）筛选器删除的行：993规划时间：0.101 ms执行时间：0.228ms
这证明方案表中的部分数据已编制索引 ， 其余数据未编制索引 。 我们的索引大小非常小 ， 易于维护 ， 有助于维护重新索引的任务 。
联接查询计划优化器需要选择正确的连接时 ， 有在SELECT语句要加入多个表的算法 。 Postgres根据我们使用的联接类型使用3种不同的联接算法 。
嵌套循环：在这里 ， 计划者可以对第一个表中的每个元素使用顺序扫描或索引扫描 。 当第二个表较小时 ， 计划程序将使用顺序扫描 。 在顺序扫描和索引扫描之间进行选择的基本逻辑也适用于此 。 哈希联接：在此算法中 ， 计划程序在联接键上创建较小表的哈希表 。 然后扫描较大的表 ， 在哈希表中搜索满足连接条件的行 。 首先 ， 这需要大量内存才能存储哈希表 。 合并联接：这类似于合并排序算法 。 计划者在这里对两个要联接的表进行排序 。 然后并行扫描这些表以找到匹配的值 。
EXPLAIN SELECT schemes.rules FROM scheme_rules JOIN schemes ON (scheme_rules.scheme_id = schemes.id ) where scheme_name = 'weekend_scheme';
生产环境中索引的缺点查找未使用的索引在大型生产环境中 ， 建议使用未使用的索引 ， 因为索引会占用内存 。 Postgres Wiki页面详细介绍了如何找到索引摘要 ， 重复索引和索引大小 。
CREATE / DROP索引与CREATE / DROP索引并发在大型数据库中创建和删除索引可能要花费数小时甚至数天 ， 并且该CREATE INDEX命令会阻止对表的所有写操作（它不会阻止读操作 ， 但这仍然不理想） 。
但是 ， 与并发创建的索引CREATE INDEX CONCURRENT不会获得针对写入的锁定 。 在同时创建索引时 ， Postgres首先扫描表以建立索引 ， 然后再次运行索引以查找自第一遍以来要添加的内容 。
同时创建索引也有一个缺点 。 如果在此过程中出现问题 ， 它不会回滚 ， 并留下无效的索引 。 可以使用以下查询找到无效的索引 。


推荐阅读


上一篇：像梦一样奔驰|docker 镜像的使用

下一篇：阿狸先森|刚刚，马斯克公布“脑机接口”，猪脑和AI融合，潘多拉魔盒开了