MySQL按优先级排序

Question

你好，我正在构建一个Java程序，需要管理一些项目。为了做到这一点，我需要过滤这些项目。我通过构建SQL查询并执行它来过滤项目。我需要以下查询首先进行排序 (LEFT JOIN (SELECT * FROM grupe ORDER BY grupe.pavadinimas DESC)grupe ON) 然后才添加所有其他数据并执行WHERE子句。

SELECT  * 
FROM    (
      (
        SELECT  preke.* 
        FROM    preke_tiekejas
                , preke 
        WHERE   preke_tiekejas.tiek_id IN (18610,13604) 
                AND preke.pr_id=preke_tiekejas.pr_id 
        GROUP BY 
                preke_tiekejas.pr_id
      ) preke
      , preke_kaina
      , (
        SELECT  * 
        FROM    preke_info 
        WHERE   preke_info.pavadinimas LIKE '%kait%'
      ) preke_info
    ) LEFT JOIN (
      SELECT  * 
      FROM    grupe 
      ORDER BY 
              grupe.pavadinimas DESC
    )grupe ON grupe.pgs_id=preke.pgs_id 
    LEFT JOIN gamintojas ON gamintojas.gam_id=preke.gam_id 
    LEFT JOIN grupe_darb ON grupe_darb.pgs_id=grupe.pgs_id 
WHERE   preke_kaina.pr_id=preke.pr_id 
    AND preke_info.pr_id=preke.pr_id 
    AND grupe_darb.darb_id = 20 
LIMIT 0, 500

如果我删除这两个子查询：

(SELECT * FROM preke_info WHERE preke_info.pavadinimas LIKE '%kait%')

(SELECT preke.* FROM preke_tiekejas, preke
WHERE preke_tiekejas.tiek_id IN (18610,13604)
AND preke.pr_id=preke_tiekejas.pr_id
GROUP BY preke_tiekejas.pr_id)

然后查询将按照我想要的方式工作。

附言：仅在查询结束时排序不是选项，因为有大量记录，这样会变得非常慢。

解释：

@Lieven:

查询结果不相同 - 不加限制应该有约90k行：

查询结果不相同 - 返回约50行：

- Minutis

Answer 1

我发现很难阅读你的查询，我会将其重新格式化为：

重新格式化

SELECT  * 
FROM    (
          (
            SELECT  preke.* 
            FROM    preke_tiekejas
                    , preke 
            WHERE   preke_tiekejas.tiek_id IN (18610,13604) 
                    AND preke.pr_id=preke_tiekejas.pr_id 
            GROUP BY 
                    preke_tiekejas.pr_id
          ) preke
          , preke_kaina
          , (
            SELECT  * 
            FROM    preke_info 
            WHERE   preke_info.pavadinimas LIKE '%kait%'
          ) preke_info
        ) LEFT JOIN (
          SELECT  * 
          FROM    grupe 
          ORDER BY 
                  grupe.pavadinimas DESC
        )grupe ON grupe.pgs_id=preke.pgs_id 
        LEFT JOIN gamintojas ON gamintojas.gam_id=preke.gam_id 
        LEFT JOIN grupe_darb ON grupe_darb.pgs_id=grupe.pgs_id 
WHERE   preke_kaina.pr_id=preke.pr_id 
        AND preke_info.pr_id=preke.pr_id 
        AND grupe_darb.darb_id = 20 
LIMIT 0, 500

重新格式化后，我会删除隐式的JOIN语法。为了可读性和可维护性，您应该始终尝试使用显式的JOIN。隐式的JOIN语法迟早会过时。

使用显式的JOIN

SELECT  * 
FROM    ( (
            SELECT  preke.* 
            FROM    preke_tiekejas
                    INNER JOIN preke ON preke.pr_id=preke_tiekejas.pr_id 
            WHERE   preke_tiekejas.tiek_id IN (18610,13604)                     
            GROUP BY 
                    preke_tiekejas.pr_id
          ) preke
          INNER JOIN preke_kaina preke_kaina.pr_id=preke.pr_id 
          INNER JOIN (
            SELECT  * 
            FROM    preke_info 
            WHERE   preke_info.pavadinimas LIKE '%kait%'
          ) preke_info ON preke_info.pr_id=preke.pr_id 
        ) LEFT JOIN (
          SELECT  * 
          FROM    grupe 
          ORDER BY 
                  grupe.pavadinimas DESC
        )grupe ON grupe.pgs_id=preke.pgs_id 
        LEFT JOIN gamintojas ON gamintojas.gam_id=preke.gam_id 
        LEFT JOIN grupe_darb ON grupe_darb.pgs_id=grupe.pgs_id 
WHERE   grupe_darb.darb_id = 20 
LIMIT   0, 500

现在冗余的子查询很明显了。我可能错了，但我相信您的语句可以进一步简化为以下内容

删除冗余的子查询

SELECT  * 
FROM    (
          SELECT  preke.* 
          FROM    preke_tiekejas
                  INNER JOIN preke ON preke.pr_id=preke_tiekejas.pr_id 
          WHERE   preke_tiekejas.tiek_id IN (18610,13604)                     
          GROUP BY 
                  preke_tiekejas.pr_id
        ) preke
        INNER JOIN preke_kaina preke_kaina.pr_id=preke.pr_id 
        INNER JOIN preke_info ON preke_info.pr_id=preke.pr_id 
        LEFT OUTER JOIN grupe ON grupe.pgs_id=preke.pgs_id 
        LEFT OUTER JOIN gamintojas ON gamintojas.gam_id=preke.gam_id 
        LEFT OUTER JOIN grupe_darb ON grupe_darb.pgs_id=grupe.pgs_id 
WHERE   grupe_darb.darb_id = 20 
        AND preke_info.pavadinimas LIKE '%kait%'
ORDER BY 
        grupe.pavadinimas DESC
LIMIT   0, 500

对我来说，这是一个我可以处理的查询。不幸的是，我没有看到任何明显的性能问题，但适当的索引可以解决。

你能展示一下你的查询执行计划吗？

- Lieven Keersmaekers

---

如果我理解正确，这就是你想要的：http://i44.tinypic.com/ibl64i.jpg - Minutis

@Minutis - 对于阅读MySQL Explain计划，我是个门外汉，但看起来你在grupe_darb上缺少一个索引。 - Lieven Keersmaekers

好的，你第三个查询的结果是一样的，而且速度是我的查询的两倍。但是，如果我删除前两个搜索子查询（AND preke_info.pavadinimas LIKE '%kait%' 和 ( SELECT preke.* FROM preke_tiekejas INNER JOIN preke ON preke.pr_id=preke_tiekejas.pr_id WHERE preke_tiekejas.tiek_id IN (18610,13604) GROUP BY preke_tiekejas.pr_id )），你的查询需要69秒才能执行，而我的只需要0.3秒。 - Minutis

是的，没有索引，我应该添加一个复合主键吗？ - Minutis

@Minutis - 这个问题不会吸引太多新人。我建议你另外提一个关于优化最终查询的问题，如果需要一些背景知识，可以链接到这个问题上。 - Lieven Keersmaekers