mysql - 过滤按列分组的大型数据库记录

Question

交易历史表:

    CREATE TABLE `TransactionHistory` (
      `id` varchar(200) NOT NULL,
      `transactionType` varchar(200) DEFAULT NULL,
      `startDate` bigint(20) DEFAULT NULL,
      `completionDate` bigint(20) DEFAULT NULL,
      `userId` varchar(200) DEFAULT NULL,
      `status` varchar(200) DEFAULT NULL,
      `error_code` varchar(200) DEFAULT NULL,
      `transactioNumber` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `transactioNumber_index` (`transactioNumber`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

用户表:

    CREATE TABLE `User` (
      `userId` varchar(200) NOT NULL,
      `name` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`userId`),
      KEY `userId_index` (`userId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

场景:

• 按 transactioNumber 对 TransactionHistory 进行分组
  • 如果 groupSize == 1，
    • 在transactionType、startDate、completionDate、status、error_code中显示值
  • 如果 groupSize > 1
    • 为交易类型显示“”
    • 显示 MIN 开始日期和 MAX 开始日期
    • STATUS 和 ERROR_CODE
      • 显示状态 = SUCCESS，如果组中的所有状态 = SUCCESS，error_code = '0'，
      • 如果组中的所有状态都为 FAILED，则显示状态 = FAILED，error_code = '99'，
      • 显示 status = WARNING, error_code = '-1' if mixed
  • userName 的显示名称(如果交易有 userId)

我想到了这个查询:

    SELECT tx.id, 
        CASE WHEN COUNT(*) = 1 THEN transactionType ELSE '' END as transactionType,
        CASE WHEN COUNT(*) = 1 THEN status ELSE ( 
            CASE WHEN COUNT(CASE WHEN STATUS = 'SUCCESS' THEN 1 END) = 0 THEN 'FAILED' 
            WHEN COUNT(CASE WHEN STATUS = 'FAILED' THEN 1 END) = 0 THEN 'SUCCESS' 
            ELSE 'WARNING' END) END as status,
        CASE WHEN COUNT(*) = 1 THEN error_code ELSE ( 
            CASE WHEN COUNT(CASE WHEN STATUS = 'SUCCESS' THEN 1 END) = 0 THEN '99' 
            WHEN COUNT(CASE WHEN STATUS = 'FAILED' THEN 1 END) = 0 THEN '0' 
            ELSE '-1' END) END as status
        MAX(completionDate) as completionDate, 
        MIN(startDate) as startDate,
        a.userId, a.name,
        transactioNumber
    FROM TransactionHistory tx LEFT JOIN User a ON tx.userId = a.userId 
    GROUP BY transactioNumber
    LIMIT 0, 20 //pagination

但是，如果我需要添加过滤，则查询需要很长时间才能完成。我读到将 WHERE 过滤器放在 GROUP BY 之前而不是 HAVING 会更快，但我无法正确过滤状态和错误代码，因为 WARNING 和 -1 值仅在 GROUP BY 之后出现

    HAVING STATUS = 'WARNING'

此外，如果我需要计算分组条目的总数，则需要的时间太长。

我的解释显示如下

    select_type: SIMPLE
    table: tx
    type: ALL
    possible_keys: NULL
    key_len: NULL
    ref: NULL
    rows: 1140654
    Extra: Using temporary; Using filesort

    select_type: SIMPLE
    table: e
    type: eq_ref
    possible_keys: PRIMARY,id_index
    key_len: 202
    ref: db.tx.userId
    rows: 1
    Extra: Using where   

Answer 1

COUNT(CASE WHEN STATUS = 'SUCCESS' THEN 1 END)

可以缩短为

SUM(STATUS = 'SUCCESS')

这些必须按这个顺序写，它们会按这个顺序执行:WHERE, GROUP BY, HAVING。您正确地观察到您的 HAVING 不能变成 WHERE。

Also if I need to count the total number of grouped entries, it takes too long.

我不明白你的意思 -- 你多次使用 COUNT(*)。

transactioNumber 是否与 id 是 1:1 的关系？如果不是，则 GROUP BY 无效。

您没有 ORDER BY，因此(从技术上讲)LIMIT 定义不正确。

运行 EXPLAIN SELECT ... 以查看优化器如何执行查询。

这里有一种技术可能会有所帮助 - 通过延迟 JOIN。首先，从查询中删除所有提及 User 的内容。然后，使 SELECT 中的子查询:

SELECT z.id,
       z.transactionType,
       ...
       a.userId, a.name,
       z.transactioNumber
FROM ( SELECT id, 
              IF(COUNT(*) = 1, transactionType, '') as transactionType,
              ...
           FROM TransactionHistory
           GROUP BY transactioNumber
           ORDER BY transactioNumber
           LIMIT 0, 20
     ) z
LEFT JOIN User a ON z.userId = a.userId 

那样的话，JOIN 只会出现 20 次，而不是在 TransactionHistory 中每行出现一次。

编辑

如果没有 WHERE 子句，优化器将寻找有助于 GROUP BY 的索引。如果 ORDER BY 与 GROUP BY 相同，则它可以同时执行 GROUP BY 和 ORDER BY同时。如果它们不同，则 ORDER BY 成为一个单独的排序步骤。

具有混合方向(例如 startdate DESC、transactionType ASC)的 ORDER BY 永远不能使用索引。注定需要一个tmp表和排序。使用 startdate DESC、transactionType DESC(均为 DESC)可能会工作得更好，而不会过多改变语义。

如果优化器不能为 GROUP BY 和 ORDER BY 使用索引，那么它必须收集所有 行并在应用 LIMIT 之前对它们进行排序。

对于 1140654 行，您想努力让查询和 INDEX 让优化器一直通过 ORDER BY 进行查询 - 这样它只需要查看 20 行，而不是 1140654。My pagination blog进入其中一些。

EXPLAIN 可能会说“使用临时文件，使用文件排序”。这可能是针对 GROUP BY 和/或 ORDER BY。但是，这隐藏了需要两种 排序的情况，一种用于GROUP BY，一种用于ORDER BY。 EXPLAIN FORMAT=JSON 确实明确说明了何时需要多种排序。

不过，“文件排序”并不是坏事。真正的性能 killer 是需要处理 1140654 行而不是仅仅 20 行。