mysql - 使用连接缓冲区的连接速度极慢

Question

我在使用多个连接的复杂查询时遇到问题。运行解释时:

查询

explain

select ud.id from user_detail ud
cross join ticket t
cross join guest_list gl
cross join event e
cross join venue v

where t.guest_list = gl.id and gl.event = e.id and e.venue = v.id
and (ud.account = 10 or ud.venue = 10 or ud.event = 10 or ud.guest_list = 10 or t.reference_user = 10 and (ud.guest_list=t.guest_list or ud.event = gl.event or ud.venue = e.venue or ud.account = v.account) and (t.guest_list = 10))

我明白了:

id, select_type, table, type, rows, extra
1, SIMPLE, v, index, 2, "Using index"
1, SIMPLE, e, ref, 2, "Using where; using index"
1, SIMPLE, gl, ref, 1, "Using where; using index"
1, SIMPLE, t, ref, 418, "Using where"
1, SIMPLE, ud, ALL, 44028, "Using where; Using join buffer"

数据模型是这样的:

帐户 <1-> 地点 <1-> 事件 <1-> guest 列表 <1-> 门票UserDetail 有一个作为父级的帐户、地点、事件或 guest 列表。

我试图用这个查询做的是获取所有以特定帐户/ field /事件/ guest 列表之一为父级的 UserDetail，或者以 guest 列表为父级且拥有一张票证的 UserDetail将reference_user 字段设置为特定用户。

hibernate 标准

public List<UserDetail> listUserDetails(final Collection<UserDetailNode> anyOfNodes, final User orTicketReferenceUser, final Collection<GuestList> andAnyOfGuestlistsForTicketReferenceUser, final Collection<User> anyOfUsers, final Date fromLastModificationDate, final Date toLastModificationDate, final Boolean deletedNodes, final Boolean deletedUsers, final Boolean deletedUserDetails) {

    final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    final CriteriaQuery<UserDetail> cq = cb.createQuery(UserDetail.class);
    final Root<UserDetail> userDetail = cq.from(UserDetail.class);
    Predicate criteria = cb.conjunction();

    if (anyOfNodes != null || orTicketReferenceUser != null) {

        Predicate subCriteria = cb.disjunction();

        if (anyOfNodes != null) {

            Predicate anyOfNodesCriteria = cb.disjunction();

            Collection<Account> anyOfAccounts = null;
            Collection<Venue> anyOfVenues = null;
            Collection<Event> anyOfEvents = null;
            Collection<GuestList> anyOfGuestLists = null;

            final Set<UserDetailNode> anyOfNodesWithParents = new HashSet<UserDetailNode>();
            for (UserDetailNode node : anyOfNodes) {

                while (node != null) {

                    anyOfNodesWithParents.add(node);
                    node = node.getParentNode();
                }
            }

            for (final UserDetailNode node : anyOfNodesWithParents) {

                if (node instanceof Account) {

                    if (anyOfAccounts == null) anyOfAccounts = new ArrayList<Account>();
                    anyOfAccounts.add((Account)node);
                }
                else if (node instanceof Venue) {

                    if (anyOfVenues == null) anyOfVenues = new ArrayList<Venue>();
                    anyOfVenues.add((Venue)node);
                }
                else if (node instanceof Event) {

                    if (anyOfEvents == null) anyOfEvents = new ArrayList<Event>();
                    anyOfEvents.add((Event)node);
                }
                else if (node instanceof GuestList) {

                    if (anyOfGuestLists == null) anyOfGuestLists = new ArrayList<GuestList>();
                    anyOfGuestLists.add((GuestList)node);
                }
            }

            if (anyOfAccounts != null) anyOfNodesCriteria = cb.or(anyOfNodesCriteria, cb.or(userDetail.get("account").in(anyOfAccounts)));
            if (anyOfVenues != null) anyOfNodesCriteria = cb.or(anyOfNodesCriteria, cb.or(userDetail.get("venue").in(anyOfVenues)));
            if (anyOfEvents != null) anyOfNodesCriteria = cb.or(anyOfNodesCriteria, cb.or(userDetail.get("event").in(anyOfEvents)));
            if (anyOfGuestLists != null) anyOfNodesCriteria = cb.or(anyOfNodesCriteria, cb.or(userDetail.get("guestList").in(anyOfGuestLists)));

            subCriteria = cb.or(subCriteria, anyOfNodesCriteria);
        }

        if (orTicketReferenceUser != null && (andAnyOfGuestlistsForTicketReferenceUser == null || !andAnyOfGuestlistsForTicketReferenceUser.isEmpty())) {

            final Root<Ticket> ticket = cq.from(Ticket.class);
            Predicate ticketCriteria = cb.equal(ticket.get("referenceUser"), orTicketReferenceUser);
            ticketCriteria = cb.and(ticketCriteria, cb.or(cb.equal(userDetail.get("guestList"), ticket.get("guestList")), cb.equal(userDetail.get("event"), ticket.get("guestList").get("event")), cb.equal(userDetail.get("venue"), ticket.get("guestList").get("event").get("venue")), cb.equal(userDetail.get("account"), ticket.get("guestList").get("event").get("venue").get("account"))));

            if (andAnyOfGuestlistsForTicketReferenceUser != null) ticketCriteria = cb.and(ticketCriteria, ticket.get("guestList").in(andAnyOfGuestlistsForTicketReferenceUser));

            subCriteria = cb.or(subCriteria, ticketCriteria);
        }

        criteria = cb.and(criteria, subCriteria);
    }

    if (anyOfUsers != null) {

        if (anyOfUsers.isEmpty()) return new ArrayList<UserDetail>();
        criteria = cb.and(criteria, userDetail.get("user").in(anyOfUsers));
    }

    if (fromLastModificationDate != null) criteria = cb.and(criteria, cb.greaterThanOrEqualTo(userDetail.<Date>get("lastModificationDate"), fromLastModificationDate));
    if (toLastModificationDate != null) criteria = cb.and(criteria, cb.lessThanOrEqualTo(userDetail.<Date>get("lastModificationDate"), toLastModificationDate));

    cq.select(userDetail).distinct(true).where(criteria);

    return entityManager.createQuery(cq).getResultList();
}

据我所知，最后一行是问题所在，但我该如何解决这个问题？这个查询是由 hibernate 自动生成的，所以我不确定我可以改变它多少。

Answer 1

您过度使用交叉连接笛卡尔连接是没有意义的......您实际上在寻找什么。由于您的“OR”子句均基于此值 10，但随后通过 guest_list id 隐式连接到票证表 - 最后需要 t.guest_list = 10 ？

因为您的所有内部联接也会查看原始用户详细信息表，其值与联接结果相同。你的亮点是最后的“AND”专门寻找 guest_list = 10。我会立即以此为基础开始，或者其他...我可能会考虑以下内容:

select STRAIGHT_JOIN
      ud.id 
   from 
      ticket t
         JOIN user_detail ud
            ON t.guest_list = ud.guest_list
   where 
          t.guest_list = 10
      AND (   ud.account = 10 
           or ud.venue = 10 
           or ud.event = 10 )

您引用了“Reference_User = 10”，但该上下文是什么......就像一个用户详细信息有一位 guest 一样？该客人可以与相同的用户详细信息事件/地点/帐户关联吗？

通过提供一些细节示例，并澄清您希望得到的内容，将使您走得更远......