生活_傻大方

  1. 首页 > 人文 > >

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


光一样的少年|「数据库系列」Postgres性能调优——Index在本文中 ， 我们将探讨如何通过使用Explain和Analyze来分析慢查询 ， 以及使用索引来修改和增强查询时间来解决慢查询 。
Postgres支持在表上使用各种索引 ， 以加快查询速度 。
多列索引多列B树索引可以与涉及索引的列的任意子集的查询条件下使用 。 当（最左边）列有约束时 ， 此索引最有效 。 确切的规则是 ， 前导列上的相等约束 ， 再加上第一列上没有相等约束的任何不相等约束 ， 都将用于限制扫描的索引部分 。
Cover索引包含查询所需的所有列的索引 ， 该索引位于select语句中 。
唯一索引唯一索引是用于强制列值的唯一性或一个以上列的组合值的唯一性的索引 。
关于索引的最被误解的概念之一是了解在哪里使用主键 ， 唯一约束或唯一索引 。 让我们使用一个问题来理解这一点：
问题陈述我们要求没有重复数据的最高性能 。 哪种方法更好？主键 ， 唯一约束或唯一索引？
光一样的少年|「数据库系列」Postgres性能调优——Index解决方案注意：多个空值不相等 ， 因此它们不被视为重复记录 。

  • 当表中定义了主键和唯一约束时 ， Postgres会在表中自动创建唯一索引 。 这样 ， 创建唯一约束将是多余的 ， 并且不必要地创建索引会降低Postgres的性能 。 根据Postgres产品团队的建议 ， 在表上创建唯一约束 ， 然后就无需在这些列上创建唯一索引 。
  • Postgres为定义的主键本身创建一个索引 。
  • 当我们创建唯一约束时 ， Postgres会在后台自动创建索引 。
但是 ， 在某些情况下 ， 甚至索引也无法提高性能 。 一种这样的情况是当我们进行不区分大小写的搜索时 。 让我们了解的情况下 ， 查询成本之间的差额区分大小写和不区分大小写的搜索 我们的计划表 。 鉴于我们在该列上有一个索引scheme_name 。
EXPLAIN ANALYSE SELECT * FROM schemes where scheme_name = 'weekend_scheme'
查询计划| 在方案上使用idx_scheme_name进行索引扫描（成本= 0.28..8.29行= 1宽度= 384）计划时间：0.155 ms执行时间：0.063ms
EXPLAIN ANALYSE SELECT * FROM schemes where lower(scheme_name) = 'weekend_scheme'
查询计划| 对方案进行Seq扫描（成本= 0.00..69.00行= 5宽度= 384）过滤器：（lower（（scheme_name）:: text）='weekend_scheme':: text）被过滤器删除的行：999规划时间：0.119 ms执行时间：0.721ms
即使我们在处创建了索引scheme_name ， 该函数lower也会降低性能 ， 因为它会付出额外的努力将所有的值转换scheme_table为小写 。
不使用索引（尽管已定义）的情况 。
  • LIKE ‘%scheme’永远不会使用索引 ， 但LIKE ‘scheme%’可能会使用索引 。
  • where子句中使用的大写/小写函数 。
因此 ， 无论何时我们想在where子句中使用函数 ， 我们都可以通过以下方式创建索引来优化查询 。 CREATE INDEX idx_scheme_name ON schemes (lower(scheme_name))
EXPLAIN ANALYSE SELECT * FROM schemes where lower(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.784 ms执行时间：0.079 ms



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

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