java - select 中不区分大小写的 JPA 性能

Question

我有一个 JPA 选择，您可以在其中接收参数，然后我们可以使用一些属性(用户名、电子邮件、标识符)进行搜索。用户只有一个文本字段来写入条件文本。

问题是性能，数据库中我们有大约900万用户注册，搜索太慢，使用JPA，

Form: - Value (Input text)

用户发送表单中的值(他没有说明他是否使用用户名、电子邮件或标识符)

User (Table) Fields: - Identifier - Name - Email

JPA 查询:

select u from UserEntity u where u.alias LIKE lower(:query) OR u.email LIKE lower(:query) OR lower(u.identifier) LIKE lower(:query) ORDER BY u.alias

我不知道提高搜索速度的最佳方法是什么，(我们在表中这些字段中有一些索引)，如果我们删除 u.identifier 字段中较低的速度，速度会提高很多(几乎是即时的)。但我们可以通过多种方式获得标识符(迁移、注册、手动客户端插入......)

Answer 1

We have about 9 Millions of users registered, and the search is too slow, using JPA

请注意，无论执行查询所涉及的技术如何，搜索都会太慢，因为 SQL 查询本身太慢。话虽如此，问题不在于JPA，而在于如何改进SQL查询。

将 LOWER() 函数与 LIKE 运算符组合会增加大量开销，因为 RDBMS 必须在分析 LIKE 匹配之前将文本函数应用于 3 个字段。

恕我直言，一个好的方法是使用 VIEW 来利用 3 个字段上的 LOWER() 部分，然后对此 View 执行查询(顺便说一句，使用 JPA 映射 View 就像映射表一样简单):

CRAETE VIEW user_view AS SELECT id, lower(identifier) AS identifier, lower(alias) AS alias, lower(email) AS email FROM user;

然后创建一个用于搜索的实体:

@Entity
@Table("user_view")
public class UserView {

    @Basic private Long id;
    @Basic private String identifier;
    @Basic private String alias;
    @Basic private String email;

    // getters and setters as required
}

最后是 JPQL 查询:

String jqpl = "SELECT u FROM UserView u WHERE u.alias LIKE :query OR u.email LIKE :query OR u.identifier LIKE :query";

请注意，您可以直接以小写形式传递 query 参数作为查询参数，并且可以在查询执行后对结果列表进行排序。两者都会减少 RDBMS 的工作量，从而提高整体响应时间。