sql - 使用 Rails/Postgresql 索引多列索引

Question

我对正确的索引有疑问。我用的是rails 3.2.13，后面用的是Posgresql。顺便说一句，可能更多的是关系数据库/索引问题......

我有这张表:

# Table name: exams
#
#  id             :integer          not null, primary key
#  cognomenome    :string(255)
#  matricola      :string(255)
#  corsolaurea    :string(255)
#  annoaccademico :string(255)
#  blablabla
#
# Indexes
#
#  index_exams_on_annoaccademico  (annoaccademico)
#  index_exams_on_cognomenome     (cognomenome)
#  index_exams_on_corsolaurea     (corsolaurea)
#  index_exams_on_matricola       (matricola)   

我想查询千条记录表(记录条数每年线性增加，比如每年增加500条，即十年5000-6000条)；

我必须进行这些查询:

SELECT "exams".* FROM "exams" WHERE (upper(cognomenome) like '%GIORGIO%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

或者那个:

SELECT "exams".* FROM "exams" WHERE (matricola like '%8327483274%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

或者那个:

SELECT "exams".* FROM "exams" WHERE (annoaccademico = '2013') AND (upper(cognomenome) like '%GIORGIO%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

或者那个:

SELECT "exams".* FROM "exams" WHERE (corsolaurea = 'Infermieristica') AND (upper(cognomenome) like
'%GIORGIO%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

或者那个:

SELECT "exams".* FROM "exams" WHERE (corsolaurea = 'Medicina-Anatomia I' and annoaccademico = '2013') AND (upper(cognomenome) like '%GIORGIO%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

简而言之，我使用列的一些 AND 组合查询表纪元学院Corsolaurea认知基因组母乳

总是我必须按列排序:学术纪事Corsolaurea认知基因组

我的问题:

1) 考虑到表的大小，您是否建议使用索引？2)如图所示，我已经在单列上设置了索引；那是对的吗？3) 可能我需要添加两个多列索引，例如:

add_index :exams, [:annoaccademico, :corsolaurea, :cognomenome]

add_index :exams, [:annoaccademico, :corsolaurea, :matricola]

是吗？

我不太清楚的是:除了选择条件，索引对 order by 子句也有用吗？

非常感谢您的耐心等待/我对 db/sql 的无知。乔治奥solyaris.altervista.org

Answer 1

我和您一样，不是数据库极客。所以当我遇到这种问题时，我就是这样做的:

• 我确定将执行这种繁重查询的页面，最终删除临时身份验证/静态设置 current_user，或任何其他允许直接访问此页面而无需通过登录过程的临时修复
• 我编写了一个小脚本，可以访问此页面 100 次(或更多，具体取决于页面加载时间以及您准备等待的时间)
• 我写下执行时间
• 我修改我的代码(在您的情况下:添加索引和迁移数据库，但它可以是您尝试优化的任何其他内容)
• 我手动运行该页面一次(因为 Rails 会缓存很多东西，我不希望在我的计算中产生这种开销)
• 我再次运行脚本并比较结果

当然，您需要完成代码并填充数据库

这是我正在使用的脚本(你只需要 curl)

#!/bin/bash

time (for ((i=0; i<100;i++)); do curl -s -o /dev/null http://127.0.0.1:3000/my_page; done)

所以我的答案是:测试一下，这种情况取决于你的应用程序和你的数据，所以唯一知道的方法就是测试它