提高Solr和MySQL配对的性能（使用JPA连接“WHERE IN”）

Question

提高Solr和MySQL配对的性能（使用JPA连接“WHERE IN”）

mysqlsqljpasolr

3

我有一个MySQL数据库，已经被Solr索引。我使用Solr进行搜索（速度很快），并使用JPA从数据库中检索Solr搜索的每个结果。JPA在数据库上运行WHERE IN查询，这非常慢。

是否有方法可以使此过程更快，或重构设计以提高性能？

我刚刚将整个应用程序从使用MySQL的全文搜索转换为使用Solr，并且现在性能更差。

注意：我需要立即获取所有结果以进行计算，因此无法使用分页。

Java代码：

    SolrDocumentList documentList = response.getResults();
    Collection<String> listingIds = new ArrayList<>();
    for(SolrDocument doc : documentList) {
        String listingId = (String) doc.getFirstValue("ListingId");
        listingIds.add(listingId);
    }

    Query query = em.createNamedQuery("getAllListingsWithId");
    query.setParameter("listingIds", listingIds);
    List<ListedItemDetail> listings = query.getResultList();

命名查询：

<query>Select listing from ListingSet listing where listing.listingId in :listingIds</query>

附加信息:

SHOW CREATE TABLE ListingSet 生成以下内容 [已缩短]:

CREATE TABLE `listingset` (
  `LISTINGID` int(11) NOT NULL,
  `STARTDATE` datetime DEFAULT NULL,
  `STARTPRICE` decimal(10,2) DEFAULT NULL,
  `TITLE` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`LISTINGID`),
  KEY `FK_LISTINGSET_MEMBER_MEMBERID` (`MEMBER_MEMBERID`),
  CONSTRAINT `FK_LISTINGSET_MEMBER_MEMBERID` FOREIGN KEY (`MEMBER_MEMBERID`) REFERENCES `member` (`MEMBERID`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

研究生成的SQL语句

查看生成的SQL语句，JPA为单个JPA查询运行了许多SQL查询。ListingSet表与7个表相关联，并且为每个listingid（共有1,000-10,000个）运行单独的SELECT查询。因此，我的一个JPA查询被分解成了大约7,000个查询！

- Kevin

SQL语法如下：`Select listing from ListingSet listing where listing.listingId in (:listingIds)'；假设listingIds是一个逗号分隔的列表。 - wildplasser

JPA 处理这个。<query>..</query> 中的查询不是原始 SQL 查询，而是由 JPA 转换成您建议的形式。 - Kevin

我的问题不是对运行的查询有疑惑。我的问题是，连接Solr和MySQL的典型方式是什么，以使性能不那么差。我已经看到了JPA运行的查询，它是使用逗号分隔的id列表的标准WHERE IN查询。 - Kevin

你在ListingSet (listingId)上有索引吗（显示SHOW CREATE TABLE ListingSet;的输出）？能提供执行计划吗？（在Workbench或命令行中运行EXPLAIN SELECT ...并将输出粘贴到问题中）。 - ypercubeᵀᴹ

我已经回答了这个问题。你的评论非常有帮助，但本身并不是一个答案。然而，由于你的评论帮助我找到了答案，我不介意给你悬赏（假设这是可接受的礼仪）。 - Kevin

显示剩余8条评论

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Kevin · Answer 1

这个问题是由我使用JPA引起的。由于我的实体有许多关系，一个单一的查询会爆炸成1,000-10,000个查询。

解决方案是在JPA中使用批处理来防止ORM n + 1查询问题。批处理使JPA一次性请求所有相关表中的所有行，而不是每个实体一次。当查询返回许多结果，并且被查询的实体具有许多关系时，此解决方案是合适的。

确定JPA潜在问题的最简单方法是启用更精细的日志记录。对于EclipseLink，请向persistence.xml添加属性：

  <property name="eclipselink.logging.level" value="FINEST"/>

请注意，EclipseLink 默认设置下生成的日志仅显示查询的 JPQL 形式。

- whunmr · Answer 2

以下仅为个人调试问题的想法：

打开 mysql 查询日志并检查 JPA 是否在每个 listingId 的查询中都访问 MySQL。

mysql -uroot -pYOUR-PASSWORD -e "SET GLOBAL log_output = 'FILE'; Set GLOBAL general_log_file = '/tmp/mysql.log'; SET GLOBAL general_log = 'ON';" tail -f /tmp/mysql.log
检查性能是否由 MySQL 引起，运行相应的 SQL 在你的 MySQL 数据库中。

Select listing from ListingSet where listingId in (put your real listingId here);

确保 ListingId 列上有索引（可能非常好的机会是索引已经存在）
由于您只从 MySQL 读取行，因此可以为更多的 slave 设置复制，然后将您的 ListingIds 拆分到所有的 slave MySQL 中，并合并结果。 http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html