主页 / dba / 问题 / 191559
Accepted
nimrodm
Asked: 2017-11-23 23:47:29 +0800 CST

MYSQL - 优化 MAX（CASE WHEN ...）

  • 772

我有一个类似于以下的表（更新：添加了一个type列**我猜太多简化了实际查询）

CREATE TABLE versions(
   type INT NOT NULL,
   version INT NOT NULL,
   important BOOLEAN NOT NULL,
   PRIMARY KEY (type, version)
)

运行SELECT type, MAX(version) FROM versions GROUP BY type非常高效，并且从数据库中检索每种类型的单行。

但是，运行SELECT MAX(CASE WHEN important=1 THEN version END) FROM versions GROUP BY type似乎会生成全表扫描。

由于我有一个版本索引，我希望 MySQL 从最高版本开始扫描，并在找到第一个具有“important=1”的版本时停止。通常，我的数据只需要扫描几行。

查看performance_schema.events_statements_history（ROWS_EXAMINED列）似乎 MySQL 扫描整个表以检索我需要的行。

任何有关如何提高此查询性能的想法都将不胜感激。

mysql performance

2 个回答

  1. Best Answer

    对于简单查询（不带GROUOP BY），您可以将它们重写为：

    SELECT version 
FROM versions 
-- WHERE important = 1
ORDER BY version DESC
LIMIT 1 ;

    并检查执行计划是否不进行表扫描并以您期望的方式使用索引。

    此外，您可以在(important, version).

    对于GROUP BY type查询，索引(important, type, version)更适合。

    重写该查询并非易事，LIMIT 1因为我们需要许多最大值（每种类型一个）。但是，如果单独使用索引并没有太大的改进，这里有一个不同的查询，它将使用上述复合索引。

    当不同类型值很少时，通常使用此方法提高性能：

    SELECT dt.type, v.version 
FROM 
    ( SELECT type 
      FROM versions 
      WHERE important = 1
      GROUP BY type
    ) AS dt
  -- LEFT
  JOIN versions AS v
  ON  v.important = 1
  AND v.type = dt.type 
  AND v.version = ( SELECT vi.version
                    FROM versions AS vi
                    WHERE vi.important = 1
                      AND vi.type = dt.type
                    ORDER BY vi.version DESC
                    LIMIT 1 
                  ) ;

    您可以尝试多种变化。例如，删除important = 1 （仅从派生表内部dt）并LEFT JOIN在那里使用。这将为您提供所有类型（以及没有版本标记为“重要”的类型的 NULL 值。

    如果您有一个单独的表，包含所有类型（即PRIMARY KEY (type)），您可以完全dt用该表替换。

    • 1 
  2. SELECT type, MAX(version)
    FROM tbl
    WHERE important = 1;

INDEX(important, type, version)

    许多优化（包括您提到的第一个优化）并非“免费提供”，但如果认为“值得努力”，则必须添加。我不知道我刚才介绍的SELECT和是否包含在优化中。INDEX试试看。

    笔记：

    • 它是“覆盖”的，因为 中的所有列SELECT都在一个索引中。（它可以覆盖您的未简化版本吗？）
    • 在索引 where 的部分内important=1，使用type和version工作就像原来的优化一样。但是优化器是否足够聪明地注意到它？
    • 这INDEX不应该是PRIMARY KEY。
    • 1

相关问题