我如何进一步优化执行比加入相当于更好的派生表查询？(How can I further optimi

更新：我找到了解决办法。 见我的回答如下。

我的问题

如何优化这个查询，以尽量减少停机时间我？ 我需要凭门票从10万至200万的数量更新超过50架构。 它是不明智的，同时设置在tickets_extra所有领域？ 我觉得这里有一个解决方案，我只是没有看到。 我一直敲我的头靠在这个问题过了一天。

另外，我最初尝试不使用子SELECT，但表现比我现在有更糟糕 。

背景

我试图优化我的数据库需要运行报告。 我需要聚合上的字段是计算非常昂贵的，所以我非规范化我现有的模式有点适应这种报告。 请注意，我简化了票表颇有几分去除几十无关列。

我的报告会由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万票的模式运行。 这是维护窗口我不能接受的。 此外，它甚至不处理计算manager_resolved领域，如设法将其写入相同的查询推查询时间进入平流层。 我目前倾向是让他们分开，并使用UPDATE来填充manager_resolved场，但我不知道。

最后，这里是该查询的SELECT部分​​的解释输出：

*************************** 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的咀嚼，虽然它试图加入转变和shift_positions表。 这个庞然大物导出将高达2万行的没有索引的混乱。

现在，我知道这是怎么回事。 虽然我会沿着这条道路的原因是因为做了“正确”的方式本，采用严格的加入正在采取的时间甚至更长的时间量。 这是由于确定给定移的经理是谁需要混乱的讨厌位。 我不得不加入到shift_times找出正确的转变，甚至是，同时加入到shift_positions找出用户的水平。 我不认为MySQL优化处理这很好，并最终创造的加入了一个临时表的一个巨大的怪物，然后过滤出什么不适。

所以，作为派生表似乎是“的路要走”我固执地坚持这个了一会儿。 我试着撑船它分解成一个JOIN子句，没有任何改善。 我试图在它创建与派生表的临时表，但同样是因为临时表是没有索引的速度太慢。

我认识到，我只好三立处理这个计算平移，时间，位置。 我想，也许一个看法是要走的路。 如果我创建了一个包含此信息的视图：（shop_id，shift_id，陶氏，开始，结束，经理标识）。 然后，我也只会通过shop_id和整个DAYOFWEEK /时间计算加入票表，我会在企业。 当然，我没有记住，MySQL的处理意见，而assily。 它不会兑现他们所有，它只是运行你会用来获取您的视图的查询。 因此，通过加入门票到这一点，我基本上运行我的原始查询 - 无改善。

因此，而不是一个视图我决定用一个临时表。 这种运作良好，如果我只取一次经理（创建或解决）之一，但它仍然是相当缓慢。 另外，我发现，与MySQL你不能在同一个查询引用同一个表两次（我会加入我的临时表两次，才能manager_created和manager_resolved区分）。 这是一个很大的跆拳道，我可以，只要我不指定“临时”做到这一点 - 这是那里的CREATE TABLE魔术ENGINE = MEMORY发挥了作用。

有了这些伪临时表，我想我的JOIN只是再次manager_created。 它表现良好，但仍相当缓慢。 然而，当我加入再次在同一个查询得到manager_resolved查询时间勾选备份到平流层。 纵观EXPLAIN表现出票（行〜2mln），如预期的全表扫描，并在〜2087每个JOIN的到魔术表。 同样，我似乎遇到了故障。

我现在开始思考如何完全避免这些连接，这时候我发现了一些晦涩的古留言板帖子里有人使用子查询（不能找到我的历史链接）建议。 这是什么导致了上面显示的第二个SELECT查询（在tickets_extra创建一个）。 在选择只是一个单一的管理域的情况下，两者是废话表现不错，但一次。 我看了看解释并看到这个：

*************************** 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)

确认时，可怕的依赖SUBQUERY。 人们常常认为避免这些，因为MySQL将通常在由外而内的方式执行它们，执行内部查询外的每一行。 我忽略了这一点，并想知道：“嗯......如果我只是索引这个愚蠢的魔表”。 因此，添加索引（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)

现在，这是我对你说一下！

结论

这绝对是我第一次创造了在飞行中的非临时表和索引它的飞行，简单有效地完成一个单一的查询。 我想我一直认为在飞行中添加索引是一个昂贵的操作。 （在我的车票2mln行表添加一个索引可能需要一个多小时）。 然而，对于区区3000行，这是小菜一碟。

不要害怕依赖的子查询，对飞，还是外星人创建临时表那真的不是，索引。 他们都可以在正确的情况下的好东西。

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

Answer 2:

你应该使用Postgres的，哈哈。 像这样的简单的查询不应该超过数十秒钟前提是你有足够的内存来避免磁盘颠簸。

无论如何。

=>是SELECT或INSERT的问题？

（测试服务器和时间它单独运行SELECT）。

=>是您查询磁盘结合或CPU限制？

启动它在测试服务器上，并检查vmstat的输出。 如果CPU绑定，请跳过此。 如果磁盘约束，检查工作集的大小（即你的数据库的大小）。 如果工作组是比你小的RAM，它不应该被盘约束。 您可以强制在操作系统的缓存表的加载通过启动伪选择像SELECT SUM（一些列）FROM表执行查询之前。 如果查询来自未在RAM中缓存表中选择以随机顺序多行这可能是有用的...你触发表，它加载它在高速缓存中的顺序扫描，然后随机存取速度要快得多。 随着一些挂羊头卖狗肉，你也可以缓存索引（或只是焦油数据库目录>的/ dev / null的，哈哈）。

当然，增加更多的RAM可以帮助（但你需要检查查询第一杀磁盘或CPU）。 或者告诉MySQL使用更多的RAM的配置（的key_buffer等）。

如果你赚了几百万随机硬盘寻道你是在痛苦中。

=> OK，现在查询

第一，请分析表。

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

为什么你LEFT JOIN，然后在其上添加一个WHERE？ 左边是没有意义的。 如果在shift_positions没有行，LEFT JOIN会产生一个NULL，而WHERE会拒绝它。

解决方案：使用JOIN代替LEFT的JOIN和在JOIN ON（）状态移动（等级= 1）。

当你在它，也摆脱对方LEFT JOIN（替代由JOIN），除非你是在所有的NULL真正感兴趣的？ （我猜你都没有）。

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

下一个。

WHERE TIME（t.created）之间shift_times.start和shift_times.end）

这不是可转位，因为你有一个函数TIME（）中的条件（使用Postgres的，哈哈）。 让我们看看吧：

JOIN shift_times ON（shifts.id = shift_times.shift_id AND shift_times.dow = DAYOFWEEK（t.created）和时间（t.created）BETWEEN shift_times.start AND shift_times.end）

理想情况下，你想对shift_times（shift_id，DAYOFWEEK（t.created），时间（t.created））多列索引所以这种连接可以被索引。

解决方法：添加列“天”，“时间”来shift_times，包含DAYOFWEEK（t.created），时间（t.created），填充使用触发器触发的INSERT或UPDATE正确的价值观。

现在，创建多列索引（shift_id，日，时间）

Answer 3:

这将让你拥有只读更改的时间访问：

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表这是至关重要的，你在主键的顺序做到这一点（另有大型数据集这大小的几个订单慢）。

Answer 4:

关于BETWEEN

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

• 是indexable和对应于索引a.column的范围内查找（如果有的话）
• 等同100％至a.column >= x AND a.column <= y

虽然这样的：

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

• 等同100％至somevalue >= a.column1 AND somevalue <= a.column2
• 从上面的第一个非常不同的事情
• 是不是一个范围查找可转位（没有范围，你有2列在这里）
• 通常会导致可怕的查询性能

我认为有以上“之间”的争论这个混乱。

OP具有第一种，所以不用担心。

文章来源: How can I further optimize a derived table query which performs better than the JOINed equivalent?