sql - 我如何进一步优化派生表查询，其性能要优于JOINed等效表？

Question

更新:我找到了解决方案。请参阅下面的“我的答案”。

我的问题

如何优化此查询以最大程度地减少停机时间？我需要更新50多个模式，票证的数量从100,000到200万不等。是否建议尝试同时设置tickets_extra中的所有字段？我觉得这里没有解决的办法。一天来，我一直在努力解决这个问题。

另外，我最初尝试不使用子SELECT，但是性能比我现在的要差得多。

背景

我正在尝试针对需要运行的报告优化数据库。我需要汇总的字段计算起来非常昂贵，因此我对existing schema进行了一些非规范化处理以适应此报告。请注意，我通过删除几十个不相关的列大大简化了票证表。

我的报告将汇总 Manager(创建时)和 Manager(已解决)时的工单计数。这个复杂的关系在这里图解说明:


(来源:mosso.com)

为了避免计算此即时消息所需的半打讨厌的联接，我将下表添加到了我的模式中:

mysql> show create table tickets_extra\G
*************************** 1. row ***************************
       Table: tickets_extra
Create Table: CREATE TABLE `tickets_extra` (
  `ticket_id` int(11) NOT NULL,
  `manager_created` int(11) DEFAULT NULL,
  `manager_resolved` int(11) DEFAULT NULL,
  PRIMARY KEY (`ticket_id`),
  KEY `manager_created` (`manager_created`,`manager_resolved`),
  KEY `manager_resolved` (`manager_resolved`,`manager_created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

现在的问题是，我还没有在任何地方存储此数据。管理器始终是动态计算的。我在几个具有相同模式的数据库中有 数百万的票据，需要填充此表。我想以一种尽可能有效的方式来执行此操作，但是在优化我用于执行此操作的查询方面一直没有成功:

INSERT INTO tickets_extra (ticket_id, manager_created)
SELECT
  t.id, 
  su.user_id
FROM (
  SELECT 
    t.id, 
    shift_times.shift_id AS shift_id 
  FROM tickets t
  JOIN shifts ON t.shop_id = shifts.shop_id 
  JOIN shift_times ON (shifts.id = shift_times.shift_id
  AND shift_times.dow = DAYOFWEEK(t.created)
  AND TIME(t.created) BETWEEN shift_times.start AND shift_times.end)
) t
LEFT JOIN shifts_users su ON t.shift_id = su.shift_id
LEFT JOIN shift_positions ON su.shift_position_id = shift_positions.id
WHERE shift_positions.level = 1

此查询需要一个多小时才能在具有> 170万张票证的架构上运行。这对于我的维护时段是 Not Acceptable 。另外，它甚至不处理计算manager_resolved字段，因为尝试将它组合到同一查询中会将查询时间 push 平流层。我目前的倾向是将它们分开，并使用UPDATE来填充manager_resolved字段，但是我不确定。

最后，这是该查询的SELECT部分​​的EXPLAIN输出:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 167661
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: su
         type: ref
possible_keys: shift_id_fk_idx,shift_position_id_fk_idx
          key: shift_id_fk_idx
      key_len: 4
          ref: t.shift_id
         rows: 5
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: shift_positions
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where; Using join buffer
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: t
         type: ALL
possible_keys: fk_tickets_shop_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 173825
        Extra: 
*************************** 5. row ***************************
           id: 2
  select_type: DERIVED
        table: shifts
         type: ref
possible_keys: PRIMARY,shop_id_fk_idx
          key: shop_id_fk_idx
      key_len: 4
          ref: dev_acmc.t.shop_id
         rows: 1
        Extra: 
*************************** 6. row ***************************
           id: 2
  select_type: DERIVED
        table: shift_times
         type: ref
possible_keys: shift_id_fk_idx
          key: shift_id_fk_idx
      key_len: 4
          ref: dev_acmc.shifts.id
         rows: 4
        Extra: Using where
6 rows in set (6.30 sec)

非常感谢您的阅读!

Answer 1

好吧，我找到了解决方案。经过大量的实验，我认为运气不佳，但这是:

CREATE TABLE magic ENGINE=MEMORY
SELECT
  s.shop_id AS shop_id,
  s.id AS shift_id,
  st.dow AS dow,
  st.start AS start,
  st.end AS end,
  su.user_id AS manager_id
FROM shifts s
JOIN shift_times st ON s.id = st.shift_id
JOIN shifts_users su ON s.id = su.shift_id
JOIN shift_positions sp ON su.shift_position_id = sp.id AND sp.level = 1

ALTER TABLE magic ADD INDEX (shop_id, dow);

CREATE TABLE tickets_extra ENGINE=MyISAM
SELECT 
  t.id AS ticket_id,
  (
    SELECT m.manager_id
    FROM magic m
    WHERE DAYOFWEEK(t.created) = m.dow
    AND TIME(t.created) BETWEEN m.start AND m.end
    AND m.shop_id = t.shop_id
  ) AS manager_created,
  (
    SELECT m.manager_id
    FROM magic m
    WHERE DAYOFWEEK(t.resolved) = m.dow
    AND TIME(t.resolved) BETWEEN m.start AND m.end
    AND m.shop_id = t.shop_id
  ) AS manager_resolved
FROM tickets t;
DROP TABLE magic;

冗长的解释

现在，我将解释为什么这样做有效，以及我的相对流程和到达此处的步骤。

首先，我知道由于巨大的派生表以及随后的JOIN，我正在尝试查询。我正在使用索引良好的票证表，并将所有的shift_times数据连接到表上，然后让MySQL在尝试连接shifts和shift_positions表时对其进行咀嚼。这个派生的庞然大物将多达200万行未索引的困惑。

现在，我知道这正在发生。我之所以走这条路，是因为使用严格的JOIN来执行此操作的“正确”方法花费了更长的时间。这是由于确定给定类次的经理是谁所需要的困惑情况。我必须加入shift_times才能找出正确的类次，同时还要加入shift_positions以了解用户的水平。我认为MySQL优化器不能很好地处理此问题，最终会导致巨大的连接临时表怪异，然后过滤掉不适用的内容。

因此，由于派生表似乎是“前进的道路”，因此我顽固地坚持了一段时间。我尝试将其分解为一个JOIN子句，没有任何改善。我尝试使用派生表创建临时表，但是由于临时表未建立索引，因此它又太慢了。

我意识到我必须理智地处理这种计算类次，时间和位置的方法。我以为，也许VIEW是要走的路。如果我创建了一个包含以下信息的VIEW，该怎么办:(shop_id，shift_id，dow，start，end，manager_id)。然后，我只需要通过shop_id和整个DAYOFWEEK / TIME计算来连接门票表，就可以了。当然，我没有忘记MySQL相当轻松地处理VIEW。它根本没有实现它们，它只是运行用于获取 View 的查询。因此，通过将票证加入其中，我实际上是在运行原始查询-没有任何改善。

因此，我决定使用TEMPORARY TABLE代替VIEW。如果我一次只获取一位经理(创建或解决)，则此方法效果很好，但是仍然很慢。另外，我发现使用MySQL不能在同一查询中两次引用相同的表(我必须两次连接临时表才能区分manager_created和manager_resolved)。这是一个很大的WTF，只要不指定“TEMPORARY”就可以做到-这就是CREATE TABLE magic ENGINE = MEMORY发挥作用的地方。

有了这个伪临时表之后，我再次尝试只使用manager_created的JOIN。它表现不错，但是仍然很慢。但是，当我再次加入以在同一查询中获得manager_resolved时，查询时间又回到了平流层。看着EXPLAIN，可以看到票的全表扫描(行数约为2百万)，正如预期的那样，将JOIN插入魔术表的次数为每张2,087。再一次，我似乎要失败了。

我现在开始考虑如何完全避免JOIN，那是当我发现一些晦涩的古老留言板帖子时，有人建议使用子选择(在我的历史中找不到链接)。这就是导致上面显示的第二个SELECT查询(ticket_extra创建一个)的原因。在只选择​​一个经理字段的情况下，它的表现很好，但是两者都显得很糟糕。我看着EXPLAIN看到了:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 173825
        Extra: 
*************************** 2. row ***************************
           id: 3
  select_type: DEPENDENT SUBQUERY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2037
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2037
        Extra: Using where
3 rows in set (0.00 sec)

确认，可怕的DEPENDENT子查询。通常建议避免这些情况，因为MySQL通常将以外部方式执行它们，对外部的每一行执行内部查询。我忽略了这个，想知道:“好吧……如果我只是索引这个愚蠢的魔术表怎么办？”。因此，ADD索引(shop_id，dow)诞生了。

看一下这个:

mysql> CREATE TABLE magic ENGINE=MEMORY
<snip>
Query OK, 3220 rows affected (0.40 sec)

mysql> ALTER TABLE magic ADD INDEX (shop_id, dow);
Query OK, 3220 rows affected (0.02 sec)

mysql> CREATE TABLE tickets_extra ENGINE=MyISAM
<snip>
Query OK, 1933769 rows affected (24.18 sec)

mysql> drop table magic;
Query OK, 0 rows affected (0.00 sec)

现在 这就是我在说什么!

结论

这绝对是我第一次动态创建非TEMPORARY表，并快速对其进行索引，只是为了高效地执行单个查询。我猜想我一直以为在运行中添加索引是一项非常昂贵的操作。 (在200万行的票证表上添加索引可能需要一个多小时)。然而，对于仅3,000行而言，这是一个小小的尝试。

不要担心DEPENDENT子查询，创建实际上不是临时表，动态索引或外星人。在正确的情况下，它们都是好东西。

感谢您提供的所有StackOverflow帮助。 :-D