Mysql 对 where 子句中带有 IN() 子句的 select 查询的优化（解释给定的输出）

Question

我有这样的查询：-

SELECT SUM(DISTINCT( ttagrels.id_tag IN ( 1816, 2642, 1906, 1398,
                                          2436, 2940, 1973, 2791, 1389 ) )) AS
       key_1_total_matches,
       IF(( od.id_od > 0 ), COUNT(DISTINCT( od.id_od )), 0)                 AS
       tutor_popularity,
       td.*,
       u.*
FROM   tutor_details AS td
       JOIN users AS u
         ON u.id_user = td.id_user
       JOIN all_tag_relations AS ttagrels
         ON td.id_tutor = ttagrels.id_tutor
       LEFT JOIN learning_packs AS lp
         ON ttagrels.id_lp = lp.id_lp
       LEFT JOIN learning_packs_categories AS lpc
         ON lpc.id_lp_cat = lp.id_lp_cat
       LEFT JOIN learning_packs_categories AS lpcp
         ON lpcp.id_lp_cat = lpc.id_parent
       LEFT JOIN learning_pack_content AS lpct
         ON ( lp.id_lp = lpct.id_lp )
       LEFT JOIN webclasses AS wc
         ON ttagrels.id_wc = wc.id_wc
       LEFT JOIN learning_packs_categories AS wcc
         ON wcc.id_lp_cat = wc.id_wp_cat
       LEFT JOIN learning_packs_categories AS wccp
         ON wccp.id_lp_cat = wcc.id_parent
       LEFT JOIN order_details AS od
         ON td.id_tutor = od.id_author
       LEFT JOIN orders AS o
         ON od.id_order = o.id_order
WHERE  ( u.country = 'IE'
          OR u.country IN ( 'INT' ) )
       AND u.status = 1
       AND CASE
             WHEN ( lp.id_lp > 0 ) THEN lp.id_status = 1
                                        AND lp.published = 1
                                        AND lpcp.status = 1
                                        AND ( lpcp.country_code = 'IE'
                                               OR lpcp.country_code IN ( 'INT' )
                                            )
             ELSE 1
           END
       AND CASE
             WHEN ( wc.id_wc > 0 ) THEN wc.wc_api_status = 1
                                        AND wc.id_status = 1
                                        AND wc.wc_type = 0
                                        AND
             wc.class_date > '2010-06-16 11:44:40'
                                        AND wccp.status = 1
                                        AND ( wccp.country_code = 'IE'
                                               OR wccp.country_code IN ( 'INT' )
                                            )
             ELSE 1
           END
       AND CASE
             WHEN ( od.id_od > 0 ) THEN od.id_author = td.id_tutor
                                        AND o.order_status = 'paid'
                                        AND CASE
             WHEN ( od.id_wc > 0 ) THEN od.can_attend_class = 1
             ELSE 1
                                            END
             ELSE 1
           END
       AND ( ttagrels.id_tag IN ( 1816, 2642, 1906, 1398,
                                  2436, 2940, 1973, 2791, 1389 ) )
GROUP  BY td.id_tutor
HAVING key_1_total_matches = 1
ORDER  BY tutor_popularity DESC,
          u.surname ASC,
          u.name ASC
LIMIT  0, 20 

IN() 中的数字实际上是另一个名为 Tags 的表的 ID，它与用户输入的搜索关键字相匹配。在此示例中，用户搜索了“class”。

请在此处查看此查询的解释输出：- http://www.test.examvillage.com/Screenshot.png

所用时间此查询的执行时间为 0.0536 秒

，但是，如果 () 中 ttagrels.id_tag 中的值数量增加（随着用户输入更多搜索关键字），则执行时间将上升到大约 1-5 秒甚至更多。例如，如果用户搜索“每天向导师和学生提供 3 次课程”
执行时间为 4.2226 秒。该查询的解释查询输出包含 2513 行。

All_Tag_Relations 表中共有 6,152 条记录。是否可以进一步优化？

Answer 1

我不知道你的数据库，所以无法给出明确的答案。

我注意到在您的查询中，Distinct 关键字两次 SUM(DISTINCT() 和 COUNT(DISTINCT()

这意味着您的 select 语句返回多行，其中相同的 id_tag 或 id_od。这意味着分组之前的中间结果包含许多行。您必须缩小 where 子句以限制

查询中的行数，并观察行数。如果它比预期大，这就解释了你的问题。