ruby-on-rails - 如何在 Rails 中的 LOWER ("users"."username"上创建索引(使用 postgres)

Question

我的 UsersController#create 操作中发生顺序扫描。

SELECT ? AS one FROM "users" WHERE (LOWER("users"."username") = LOWER(?) AND "users"."id" != ?) LIMIT ?

Explain plan
1 Query planLimit (cost=0.00..3.35 rows=1 width=0)
2 Query plan-> Seq Scan on users (cost=0.00..3.35 rows=1 width=0)
3 Query planFilter: ?

我相当肯定这是由以下模型验证引起的:

验证:用户名，唯一性:{ case_sensitive: false }

我应该为这个 express 创建一个索引吗？如果是这样，在 Rails 4 中执行此操作的正确方法是什么？

Answer 1

是的，验证会执行那种查询，而那种查询将执行表扫描。

你实际上在这里有几个问题:

• 验证受制于竞争条件，因为逻辑不在它所属的数据库中。无论通常的 Rails 意识形态如何，数据库都应负责所有数据完整性问题。
• 您的验证会触发表扫描，没有人喜欢表扫描。

您可以使用一个索引解决这两个问题。第一个问题是通过使用数据库内部的唯一索引来解决的。第二个问题是通过索引 lower(username) 而不是 username 的结果来解决的。

AFAIK Rails 仍然不理解表达式的索引，所以你必须做两件事:

1. 从 schema.rb 切换到 structure.sql 以防止 Rails 忘记您的索引。在您的 config/application.rb 中，您需要设置:

   config.active_record.schema_format = :sql
   

   您还必须开始使用 db:structure:* rake 任务而不是 db:schema:* 任务。切换到 structure.sql 后，您可以删除 db/schema.rb，因为它不会再更新或使用；您还需要开始在版本控制中跟踪 db/structure.sql。

2. 通过在迁移中编写一些 SQL 来手动创建索引。这很简单:

   def up
     connection.execute(%q{
       create index idx_users_lower_username on users(lower(username))
     })
   end
   
   def down
     connection.execute(%q{
       drop index idx_users_lower_username
     })
   end
   

当然，这会给您留下 PostgreSQL 特有的东西，但这没什么好担心的，因为 ActiveRecord 不会给您任何有用的可移植性。