如何进一步优化派生表查询，使其比JOIN等效查询性能更好？

Question

如何进一步优化派生表查询，使其比JOIN等效查询性能更好？

sqlmysqloptimizationquery-optimizationderived-table

21

更新：我找到了解决方法，请参见我的下面的回答。

我的问题

如何优化此查询以最小化停机时间？我需要更新50多个架构，其中门票数量从100,000到200万不等。是否建议一次性设置tickets_extra中的所有字段？我感觉这里有一个解决方案，但我只是没有看到。我已经为这个问题苦恼了一天多。

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

背景

我正在尝试为需要运行的报告优化数据库。我需要聚合的字段非常耗费计算资源，因此我正在对我的现有模式进行一些去规范化以适应此报告。请注意，我通过删除一些不相关的列来简化了票务表。

我的报告将根据创建时经理和解决时经理对门票计数进行聚合。这种复杂的关系在此处进行了说明:

_{（来源：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

这个查询在包含超过1.7百万张票的模式上运行需要一个多小时。对于我拥有的维护窗口来说，这是无法接受的。此外，它甚至不能处理计算manager_resolved字段，因为将其合并到同一查询中会将查询时间推到极限。我目前的倾向是将它们分开，并使用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)

非常感谢您的阅读！

- hobodave

1

离题：你用什么工具生成数据库图？ - czuk

3

我不知道你是否有意为之，也不知道它是否会提高你的查询效率，但我注意到shift_times是InnoDB类型，而其他所有表都是MyISAM类型。也许连接两个不同引擎类型的表可能会导致一些减速。暂时就这些了。 - Ionuț G. Stan

2

+1 非常好的准备问题。 - Robert Harvey

@Ionut：谢谢，我刚刚看了。我从来没有遇到过MySQL查询优化器和BETWEEN的任何问题。对于我来说，这方面的性能本来就很差，因为TIME（created）不使用我在created字段上的索引，所以无论如何都会很糟糕。我尝试切换BETWEEN，但性能没有明显改善。 - hobodave

@Ionut：奇怪，不知道怎么会这样。我会改变它并查看它是否会影响性能。 - hobodave

显示剩余4条评论

4个回答

2

你应该使用Postgres，哈哈。如果你有足够的RAM来避免磁盘抖动，这样简单的查询不应该超过几十秒钟。

无论如何。

=>问题出在SELECT还是INSERT中？

（在测试服务器上运行SELECT并计时）。

=>你的查询是磁盘绑定还是CPU绑定？

在测试服务器上运行它并检查vmstat输出。如果它是CPU绑定的，请跳过此步骤。如果它是磁盘绑定的，请检查工作集大小（即数据库的大小）。如果工作集比RAM小，则不应该是磁盘绑定的。您可以通过启动像SELECT sum（某些列）FROM table这样的虚拟选择来强制在执行查询之前在OS缓存中加载表。如果查询以随机顺序从未缓存到RAM中的表中选择许多行，则这可能很有用...您触发了对表的顺序扫描，从而将其加载到缓存中，然后随机访问速度更快。通过一些诡计，您还可以缓存索引（或仅将数据库目录打包到> / dev / null中，哈哈）。

当然，增加更多RAM可能有所帮助（但您需要首先检查查询是否杀死磁盘还是CPU）。或者告诉MySQL在配置中使用更多RAM（key_buffer等）。

如果你正在进行数百万次随机HDD搜索，你会很痛苦。

=>好了，现在是查询

首先，分析你的表。

LEFT JOIN shift_positions ON su.shift_position_id = shift_positions.id WHERE shift_positions.level = 1

为什么要LEFT JOIN然后再添加WHERE？ LEFT没有意义。如果shift_positions中没有行，LEFT JOIN将生成NULL，而WHERE将拒绝它。

解决方案：使用JOIN代替LEFT JOIN并将（level = 1）移动到JOIN ON（）条件中。

顺便说一下，还可以摆脱其他LEFT JOIN（替换为JOIN），除非您真的对所有这些NULL感兴趣？（我猜你不是）。

现在你可能可以摆脱子选择。

接下来。

WHERE TIME（t.created）BETWEEN shift_times.start AND shift_times.end）

这是不可索引的，因为您在条件中有一个TIME（）函数（使用Postgres，哈哈）。让我们看看它：

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）

理想情况下，您希望在shift_times（shift_id，DAYOFWEEK（t.created），TIME（t.created））上具有多列索引，以便可以对此JOIN进行索引。

解决方案：在shift_times表中添加“day”和“time”两列，用触发器在INSERT或UPDATE时填充正确的值，这些值可以使用DAYOFWEEK(t.created)和TIME(t.created)获得。

现在在（shift_id，day，time）上创建多列索引。

- bobflux

转换到Postgres不是一个选项。查询受CPU限制。转换为JOIN没有提供显着的改进。 - hobodave

@peufeu：感谢您的建议。LEFT JOINs是无意的，它们只是这个查询在经过多次尝试后的当前状态。 - hobodave

嗨，很高兴看到你解决了它 ;)如果索引创建非常慢，您需要调整MySQL配置以增加在索引创建期间使用的大排序缓冲区的大小。我不记得参数的名称，但应该有这个选项。还有key_buffer...请注意，在Postgres上为200万行表创建索引只需不到5秒钟，我认为这很慢，哈哈。就我的经验而言，MySQL在索引创建方面一直都非常慢。 - bobflux

0

这将让您在更改期间拥有只读访问权限。

create table_new (new schema);
insert into table_new select * from table order by primary_key_column;
rename table to table_old;
rename table_new to table;
-- recreate triggers if necessary

在向InnoDB表插入数据时，关键是按照主键的顺序进行操作（否则，在大型数据集中会慢几个数量级）。

- Kornel

0

关于BETWEEN

SELECT * FROM a WHERE a.column BETWEEN x AND y

可索引，并对应于在索引 a.column 上进行范围查找（如果有）
与 a.column >= x AND a.column <= y 完全等效

而这个：

SELECT * FROM a WHERE somevalue BETWEEN a.column1 AND a.column2

与somevalue >= a.column1 AND somevalue <= a.column2完全等价
与上面的第一个非常不同
无法通过范围查找进行索引（这里没有范围，你有两列）
通常会导致可怕的查询性能

我认为在上面关于“between”的辩论中存在混淆。

OP拥有第一种类型，所以不用担心。

- bobflux

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- hobodave · Accepted Answer

好的，我找到了一个解决方案。经过了很多实验和一些盲目的运气，但是这里就是解决方案：

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子句，但没有改进。我尝试使用包含派生表的临时表，但由于临时表未索引，速度仍然太慢。

我意识到我必须合理处理班次、时间和位置的计算。我想，也许使用视图是正确的方法。如果我创建了一个包含以下信息的视图：（shop_id，shift_id，dow，start，end，manager_id）。然后，我只需要通过shop_id和整个DAYOFWEEK/TIME计算连接票务表，就可以开始工作了。当然，我忘记了MySQL处理视图的方式相当糟糕。它根本不会实现它们，它只为您运行您用于获取视图的查询。因此，将票务表连接到此视图上时，本质上是再次运行原始查询，没有改进。

因此，我决定使用临时表，而不是视图。如果一次只获取一个经理（created或resolved），这种方法效果很好，但速度仍然很慢。另外，我发现在MySQL中无法在同一查询中两次引用相同的表（我必须加入两次我的临时表，以便能够区分manager_created和manager_resolved）。这是一个大问题，因为只要不指定“TEMPORARY”，我就可以做到这一点——这就是CREATE TABLE ENGINE=MEMORY魔法的运用场景。

有了这个伪临时表，我尝试着只对 manager_created 进行 JOIN。它的表现不错，但仍然相当缓慢。然而，当我在同一个查询中再次进行 JOIN 以获取 manager_resolved 时，查询时间又飙升到了极高的水平。查看 EXPLAIN 显示了 tickets 表（约2百万行）的全表扫描，与每个魔法表的 JOIN 约为 2,087。我似乎又陷入了失败的境地。

我开始考虑如何完全避免 JOIN，这时我找到了一篇关于使用子查询的模糊古老帖子（无法在我的历史记录中找到链接）。这就是导致上面显示的第二个 SELECT 查询（tickets_extra 创建）的原因。在只选择单个 manager 字段的情况下，其表现很好，但如果同时选择两个字段则表现很差。我查看了 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)

啊，可怕的依赖子查询。通常建议避免使用它们，因为MySQL通常会以外部内部的方式执行它们，对于每个外部行执行内部查询。我无视了这一点，并想知道：“那么……如果我只是给这个愚蠢的魔法表创建索引会怎样呢？”于是，ADD index（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)

现在就是我所说的！

结论

这绝对是我第一次在运行时创建一个非临时表，并在运行时为其建立索引，只是为了高效地执行单个查询。我猜我总是认为在运行时添加索引是一项代价高昂的操作。（在我的包含200万行的票务表上添加索引可能需要超过一小时）。然而，对于仅有的3,000行来说，这是轻而易举的。

不要害怕依赖子查询、创建并不真正存在的临时表、在运行时建立索引或外星人。在正确的情况下，它们都可以成为好事情。

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