使用与创建索引

提升 PostgreSQL 查询性能

Amy McCarty

Instructor

索引概览

是什么

  • 通过创建已排序的列键来加速搜索
  • 类似书籍索引
  • 指向数据位置的引用

为什么

  • 查询更快

在哪里

  • 常用过滤列
  • 主键
提升 PostgreSQL 查询性能

索引示例

ingredient recipe
tomatoes spaghetti & meatballs
green onions fried rice
eggs fried rice
ground beef spaghetti & meatballs
pasta spaghetti & meatballs
rice fried rice
soy sauce fried rice 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
提升 PostgreSQL 查询性能

索引作为键与指针

两张表。索引表包含 recipe 列和一列指针，指针依次为 _12 到 _18。带索引的表与左侧相同但多一列指针。该指针列的值与索引表的指针列一致。箭头展示索引表中四条 fried rice 与带索引表中对应四条记录的关系。

提升 PostgreSQL 查询性能

查找现有索引

pg_tables
  • 类似 information_schema
    • Postgres 专用
  • 数据库元数据
提升 PostgreSQL 查询性能

查找现有索引

pg_tables
  • 类似 information_schema
    • Postgres 专用
  • 数据库元数据

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
提升 PostgreSQL 查询性能

创建索引

餐桌，包含 ingredient、recipe、serving size 列。tomatoes - spaghetti & meatballs - 4。green onions - fried rice - 2。eggs - fried rice - 2。ground beef - spaghetti & meatballs - 4。pasta - spaghetti & meatballs - 4。rice - fried rice - 2。soy sauce - fried rice - 2。

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
提升 PostgreSQL 查询性能

用还是不用

何时使用索引

  • 大表
  • 常用过滤条件
  • 主键

何时避免索引

  • 小表
  • 空值很多的列
  • 频繁更新的表
    • 索引会碎片化
    • 写入需两处
提升 PostgreSQL 查询性能

频繁更新的表

两张表。索引表包含 recipe 列和一列指针，指针依次为 _12 到 _19。带索引的表与左侧相同但多一列指针。该指针列的值与索引表中的指针列一致。箭头展示索引表中四条 spaghetti and meatballs 与带索引表中对应四条记录的关系。索引表中有三条排在顶部，另一条在底部，对应带索引表中新插入的 basil 记录。

提升 PostgreSQL 查询性能

索引查询评估

查询计划器

多位厨师围着一口大锅 

EXPLAIN
SELECT * 
FROM cookbook

 

查询计划

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • 成本（时间）估计
提升 PostgreSQL 查询性能

Passons à la pratique !

提升 PostgreSQL 查询性能

Preparing Video For Download...