ruby-on-rails - 唯一性区分大小写 false 导致查询慢

Question

我有以下验证:

validates :username, uniqueness: { case_sensitive: false }

这会导致以下查询运行缓慢:

5,510 ms
SELECT ? AS one FROM "users" WHERE (LOWER("users"."username") = LOWER(?) AND "users"."id" != ?) LIMIT ?
Explain plan
1 Query plan    Limit (cost=0.03..4.03 rows=1 width=0)
2 Query plan    -> Index Scan using idx_users_lower_username on users (cost=0.03..4.03 rows=1 width=0)
3 Query plan    Index Cond: ?
4 Query plan    Filter: ?

索引是在我的 structure.sql 中创建的，使用 CREATE INDEX idx_users_lower_username ON users USING btree (lower((username)::text)); 查看我的问题 How to create index on LOWER("users"."username") in Rails (using postgres)有关更多信息。

这是使用我设置的索引，仍然需要超过 5 秒？这里有什么问题吗？

Answer 1

这里有几个不同的、相互关联的事情。具体如何执行更改取决于您如何管理对数据库结构的更改。最常见的方法是使用 Rails 迁移，但您的链接问题表明您没有这样做。因此，我将主要使用 SQL 进行演讲，您可以根据自己的方法进行调整。

使用可搜索的 WHERE 子句

您的 WHERE 子句不可搜索。这意味着它是以一种防止 dbms 使用索引的方式编写的。 PostgreSQL 可以在这里使用创建索引。 . .

create index on "users" (lower("username") varchar_pattern_ops);

现在对小写用户名的查询可以使用该索引。

explain analyze
select * 
from users
where lower(username) = lower('9LCDgRHk7kIXehk6LESDqHBJCt9wmA');

它可能看起来好像 PostgreSQL 必须小写表中的每个用户名，但它的查询计划器足够聪明，可以看到表达式 lower(username) 本身已被索引。 PostgreSQL 使用索引扫描。

"Index Scan using users_lower_idx on users  (cost=0.43..8.45 rows=1 width=35) (actual time=0.034..0.035 rows=1 loops=1)""  Index Cond: (lower((username)::text) = 'b0sa9malg7yt1shssajrynqhiddm5d'::text)""Total runtime: 0.058 ms"

This table has a million rows of random-ish data; the query returns very, very quickly. It's just about equally fast with the additional condition on "id", but the LIMIT clause slows it down a lot. "Slows it down a lot" doesn't mean it's slow; it still returns in less than 0.1 ms.

Also, here the varchar_pattern_ops lets queries that use the LIKE operator use the index.

explain analyze
select *
from users
where lower(username) like 'b%'


"Bitmap Heap Scan on users  (cost=1075.12..9875.78 rows=30303 width=35) (actual time=10.217..91.030 rows=31785 loops=1)"
"  Filter: (lower((username)::text) ~~ 'b%'::text)"
"  ->  Bitmap Index Scan on users_lower_idx  (cost=0.00..1067.54 rows=31111 width=0) (actual time=8.648..8.648 rows=31785 loops=1)"
"        Index Cond: ((lower((username)::text) ~>=~ 'b'::text) AND (lower((username)::text) ~<~ 'c'::text))"
"Total runtime: 93.541 ms"

只需 94 毫秒即可从一百万行中选择并返回 3 万行。

即使有可用的索引，对非常小的表的查询也可能使用顺序扫描。如果我是你，我就不会担心。

在数据库中强制唯一性

如果您预计会有任何流量爆发，您应该在数据库中强制执行唯一性。无论对流量有何期望(猜测)，我一直都这样做。

RailsGuides Active Record Validations包括这段关于“唯一性”助手的略有误导性或令人困惑的段落。

This helper validates that the attribute's value is unique right before the object gets saved. It does not create a uniqueness constraint in the database, so it may happen that two different database connections create two records with the same value for a column that you intend to be unique. To avoid that, you must create a unique index on both columns in your database. See the MySQL manual for more details about multiple column indexes.

它清楚地表明，事实上，它不保证唯一性。误导性的部分是关于在“两列”上创建唯一索引。如果希望“用户名”是唯一的，则需要对“用户名”列声明一个唯一约束。

alter table "users"
add constraint constraint_name unique (username);

区分大小写

在 SQL 数据库中，区分大小写由排序规则决定。排序规则是 SQL 标准的一部分。

在PostgreSQL中，您可以在数据库级别、列级别、索引级别和查询级别设置排序规则。值来自操作系统在您使用 initdb 创建新数据库集群时公开的语言环境。

在 Linux 系统上，您可能没有不区分大小写的排序规则。这就是我们必须比那些以 SQL Server 和 Oracle 为目标的人经历更多困难的原因之一。