如何优化具有多个外部联接到大型表、group by和order by子句的查询的执行计划?

Bill Karwin建议,如果查询使用的索引的前导列为 manufacture . 我赞成那个建议。尤其是如果这是非常有选择性的。

我还注意到我们正在做 GROUP BY t.id 在哪里 id 是表的主键。

除了 tracking 在中引用 SELECT 名单。

这表明我们只对返回 t ,而不是由于多个外部联接而创建重复项。

似乎是 COUNT() 如果在 tracking_item 和 bikes , cars , trucks . 如果有三排匹配的汽车,四排匹配的自行车,…count()聚合将返回值12,而不是7。(或者可能在数据中有一些保证,这样就不会有多个匹配行。)

如果 制造 是非常有选择性的,它返回一组相当小的行 跟踪 ,如果查询可以使用索引…

因为我们不返回除 跟踪 ,除了计数或相关项…

我将尝试在选择列表中测试相关的子查询,以获取计数,并使用HAVING子句筛选出零计数行。

像这样:

SELECT SQL_NO_CACHE `t`.*
     , ( ( SELECT COUNT(1)
             FROM `tracking_items` `tic`
             JOIN `cars` `c`
               ON `c`.`car_id`           = `tic`.`tracking_object_id`
              AND `c`.`car_text`      LIKE '%europe%'
            WHERE `tic`.`tracking_id`    = `t`.`id`
              AND `tic`.`tracking_type`  = 1
         )
       + ( SELECT COUNT(1)
             FROM `tracking_items` `tib`
             JOIN `bikes` `b`
               ON `b`.`bike_id`          = `tib`.`tracking_object_id` 
              AND `b`.`bike_text`     LIKE '%europe%'
            WHERE `tib`.`tracking_id`    = `t`.`id`
              AND `tib`.`tracking_type`  = 2
         )
       + ( SELECT COUNT(1)
             FROM `tracking_items` `tit`
             JOIN `trucks` `tr`
               ON `tr`.`truck_id`        = `tit`.`tracking_object_id`
              AND `tr`.`truck_text`   LIKE '%europe%'
            WHERE `tit`.`tracking_id`    = `t`.`id`
              AND `tit`.`tracking_type`  = 3
         ) 
       ) AS cnt_filtered_items
  FROM `tracking` `t`
 WHERE `t`.`manufacture` IN ('1256703406078', '9600048390403', '1533405067830')
HAVING cnt_filtered_items > 0
 ORDER
    BY `t`.`date_last_activity` ASC
     , `t`.`id` ASC

我们希望查询能够有效地使用 跟踪 带前导柱 制造 .

以及关于 tracking_items 表中,我们需要一个索引,其前导列为 type 和 tracking_id . 并且包括 tracking_object_id 在该索引中,意味着可以从索引中满足查询,而无需访问基础页。

对于 汽车 , 自行车 和 卡车 查询应使用前导列为的索引的表 car_id , bike_id 和 truck_id 分别。在扫描的时候 car_text , bike_text , truck_text 匹配字符串的列…我们能做的最好的就是缩小需要执行该检查的行数。

这种方法(只是 跟踪 外部查询中的表)应消除 GROUP BY ,标识和折叠重复行所需的工作。

但是 这种方法,用相关的子查询替换联接,最适合于存在 小的 外部查询返回的行数。为执行这些子查询 每一个 由外部查询处理的行。这些子查询必须具有适当的索引。即使有了这些调优,对于大型设备来说仍然有可能出现糟糕的性能。

这仍然会使我们对 ORDER BY .

如果相关项的计数应该是乘法的乘积,而不是加法,那么我们可以调整查询来实现这一点。(我们必须处理返回零的问题,HAVING子句中的条件需要更改。)

如果没有要求返回相关项的count(),那么我将尝试将相关子查询从选择列表向下移动到 EXISTS 中的谓词 WHERE 条款。

附加说明:附议Rick James关于索引的评论…似乎定义了冗余索引。即

KEY `manufacture` (`manufacture`)
KEY `manufacture_date_last_activity` (`manufacture`, `date_last_activity`)

单件列上的索引不是必需的,因为有另一个索引将该列作为前导列。

任何可以有效利用 制造 索引将能够有效地利用 manufacture_date_last_activity 索引。也就是说, 制造 无法删除索引。

同样适用于 跟踪项目 表,以及这两个索引:

KEY `tracking_id` (`tracking_id`)
KEY `tracking_id_tracking_object_id` (`tracking_id`,`tracking_object_id`)

这个 轨迹跟踪 索引可以删除,因为它是多余的。

对于上面的查询,我建议添加一个覆盖索引:

KEY `tracking_items_IX3` (`tracking_id`,`tracking_type`,`tracking_object_id`)

-或者-至少是一个非覆盖索引,其中有两列前导:

KEY `tracking_items_IX3` (`tracking_id`,`tracking_type`)

解释显示您正在进行索引扫描(“index”在 type 列)在跟踪表上。索引扫描与表扫描成本相当,尤其是当扫描的索引是主索引时。

这个 rows 该列还显示,此索引扫描正在检查355000行(因为此图只是一个粗略估计,实际上检查的是所有400K行)。

有索引吗 t.manufacture ?我看到两个索引 possible keys 这可能包括该列(我不能确定是否仅基于索引的名称),但出于某种原因,优化器没有使用它们。也许您搜索的值集与表中的每一行都匹配。

如果列表 manufacture 值的目的是匹配表的一个子集,然后您可能需要向优化器提供提示,使其使用最佳索引。 https://dev.mysql.com/doc/refman/5.6/en/index-hints.html

使用 LIKE '%word%' 模式匹配永远不能使用索引,并且必须对每一行评估模式匹配。查看我的演示文稿, Full Text Search Throwdown .

你的物品有多少 IN(...) 名单?MySQL有时会遇到长列表的问题。见 https://dev.mysql.com/doc/refman/5.6/en/range-optimization.html#equality-range-optimization

P.S.:当您询问查询优化问题时,应始终包括 SHOW CREATE TABLE 查询中引用的每个表的输出,因此回答的人不必猜测您当前拥有的索引、数据类型和约束。

首先:您的查询对字符串内容做了假设,但不应该这样做。可能 car_text like '%europe%' 指示?类似的东西 'Sold in Europe only' 也许吧?或 Sold outside Europe only ?两个可能有矛盾含义的字符串。所以,一旦你发现 europe 在字符串中,您应该能够在数据库中介绍这些知识——例如,使用欧洲国旗或地区代码。

不管怎么说,你显示的是一些带有欧洲运输计数的轨道。所以选择轨迹,选择运输计数。您可以在 SELECT 条款或您的 FROM 条款。

中的子查询 选择 条款:

select
  t.*,
  (
    select count(*)
    from tracking_items ti
    where ti.tracking_id = t.id
    and (tracking_type, tracking_object_id) in
    (
      select 1, car_id from cars where car_text like '%europe%'
      union all
      select 2, bike_id from bikes where bike_text like '%europe%'
      union all
      select 3, truck_id from trucks where truck_text like '%europe%'
    )
from tracking t
where manufacture in ('1256703406078', '9600048390403', '1533405067830')
order by date_last_activity, id;

中的子查询 从 条款:

select
  t.*, agg.total
from tracking t
left join
(
  select tracking_id, count(*) as total
  from tracking_items ti
  and (tracking_type, tracking_object_id) in
  (
    select 1, car_id from cars where car_text like '%europe%'
    union all
    select 2, bike_id from bikes where bike_text like '%europe%'
    union all
    select 3, truck_id from trucks where truck_text like '%europe%'
  )
  group by tracking_id
) agg on agg.tracking_id = t.id
where manufacture in ('1256703406078', '9600048390403', '1533405067830')
order by date_last_activity, id;

指标:

• 跟踪(制造、日期、上次活动、ID)
• 跟踪项目(跟踪对象、跟踪类型、跟踪对象)
• 汽车(汽车文本,汽车ID)
• 自行车(自行车文本、自行车ID)
• 卡车(卡车文本,卡车ID)

有时候,mysql在简单连接上比其他任何连接上都强大,因此可能值得一试,盲目地加入运输记录,然后才知道它是汽车、自行车还是卡车:

select
  t.*, agg.total
from tracking t
left join
(
  select
    tracking_id,
    sum((ti.tracking_type = 1 and c.car_text like '%europe%')
        or
        (ti.tracking_type = 2 and b.bike_text like '%europe%')
        or
        (ti.tracking_type = 3 and t.truck_text like '%europe%')
       ) as total
  from tracking_items ti
  left join cars c on c.car_id = ti.tracking_object_id
  left join bikes b on c.bike_id = ti.tracking_object_id
  left join trucks t on t.truck_id = ti.tracking_object_id
  group by tracking_id
) agg on agg.tracking_id = t.id
where manufacture in ('1256703406078', '9600048390403', '1533405067830')
order by date_last_activity, id;

如果我的猜测是正确的 cars , bikes 和 trucks 彼此独立(即特定的预聚合结果只包含其中一个结果的数据)。您最好联合三个更简单的子查询(每个子查询一个)。

虽然您不能对涉及前导通配符的like做太多索引方面的工作,但是将其拆分为联合查询可以避免评估 p.fb_message LIKE '%Europe%' OR p.fb_from_name LIKE '%Europe% 为了所有 汽车 和 自行车 匹配,以及 c 所有的条件 b 和 t 匹配,等等。

当有 order by 和 group by 子句查询运行非常慢,例如,完成上述配置需要10-15秒。但是,如果我省略了这些子句中的任何一个,那么查询的运行速度相当快(~0.2秒)。

这很有趣…一般来说,我所知道的最佳优化技术是充分利用临时表,这听起来在这里效果很好。因此,首先要创建临时表:

create temporary table tracking_ungrouped (
    key (id)
)
select sql_no_cache `t`.*
from `tracking` as `t` 
inner join `tracking_items` as `ti` on (`ti`.`tracking_id` = `t`.`id`)
    left join `cars` as `c` on (`c`.`car_id` = `ti`.`tracking_object_id` AND `ti`.`tracking_type` = 1)
    left join `bikes` as `b` on (`b`.`bike_id` = `ti`.`tracking_object_id` AND `ti`.`tracking_type` = 2)    
    left join `trucks` as `tr` on (`tr`.`truck_id` = `ti`.`tracking_object_id` AND `ti`.`tracking_type` = 3)
where 
    (`t`.`manufacture` in('1256703406078', '9600048390403', '1533405067830')) and 
    (`c`.`car_text` like '%europe%' or `b`.`bike_text` like '%europe%' or `tr`.`truck_text` like '%europe%');

然后查询您需要的结果:

select t.*, count(`t`.`id`) as `cnt_filtered_items`
from tracking_ungrouped t
group by `t`.`id` 
order by `t`.`date_last_activity` asc, `t`.`id` asc 
limit 15;

ALTER TABLE cars ADD FULLTEXT(car_text)

然后尝试

select  sql_no_cache
        `t`.*,  -- If you are not using all, spell out the list
        count(`t`.`id`) as `cnt_filtered_items`  -- This does not make sense
                         -- and is possibly delivering an inflated value
    from  `tracking` as `t`
    inner join  `tracking_items` as `ti`  ON (`ti`.`tracking_id` = `t`.`id`)
    join   -- not LEFT JOIN
         `cars` as `c`  ON `c`.`car_id` = `ti`.`tracking_object_id`
                                     AND  `ti`.`tracking_type` = 1 
    where  `t`.`manufacture` in('1256703406078', '9600048390403', '1533405067830')
      AND  MATCH(c.car_text)  AGAINST('+europe' IN BOOLEAN MODE)
    group by  `t`.`id`    -- I don't know if this is necessary
    order by  `t`.`date_last_activity` asc, `t`.`id` asc
    limit  15;

看看能不能给你一个合适的15分 汽车 .

如果看起来不错,那么将这三个因素结合起来:

SELECT  sql_no_cache
        t2.*,
        -- COUNT(*)  -- this is probably broken
    FROM (
        ( SELECT t.id FROM ... cars ... )  -- the query above
        UNION ALL     -- unless you need UNION DISTINCT
        ( SELECT t.id FROM ... bikes ... )
        UNION ALL
        ( SELECT t.id FROM ... trucks ... )
         ) AS u
    JOIN tracking AS t2  ON t2.id = u.id
    ORDER BY t2.date_last_activity, t2.id
    LIMIT 15;

注意内部 SELECTs 只交付 t.id 不是 t.* .

需要其他索引:

ti:  (tracking_type, tracking_object_id)   -- in either order

索引

当你拥有 INDEX(a,b) 你也不需要 INDEX(a) . (这对所讨论的查询没有帮助,但对磁盘空间和 INSERT 性能。

当我看到 PRIMARY KEY(id), UNIQUE(x) 我想找个好理由不放弃 id 改为 PRIMARY KEY(x) . 除非在模式的“简化”中有重要意义,否则这样的更改会有所帮助。是啊, car_id 是大的,等等,但是它是一个大表,额外的查找(从index btree到data btree)是有害的,等等。

我认为这不太可能 KEY 索特菲尔德 (date_order) 将永远被使用。要么丢弃它(节省几GB),要么以某种有用的方式组合它。让我们看看您认为它可能有用的查询。(同样,与此问题不直接相关的建议。)

重新评论(s)

我对我的配方做了一些实质性的修改。

我的配方有4个 GROUP BYs ,3在“派生”表中(即, FROM ( ... UNION ... ) 一个在外面。由于外部限制为3*15行,所以我不担心那里的性能。

进一步注意,派生表只提供 T.ID. ,然后重新探测 tracking 获取其他列。这使得派生表运行得更快,但只需花费一点额外的 JOIN 在外面。

请详细说明 COUNT(t.id) 在我的公式中它不起作用,我不知道它在计算什么。

我必须摆脱 ORs 他们是第二性能杀手。(第一个杀手是 LIKE '%...' )

SELECT t.*
FROM (SELECT * FROM tracking WHERE manufacture 
                IN('1256703406078','9600048390403','1533405067830')) t
INNER JOIN (SELECT tracking_id, tracking_object_id, tracking_type FROM tracking_items
    WHERE tracking_type IN (1,2,3)) ti 
    ON (ti.tracking_id = t.id)
LEFT JOIN (SELECT car_id, FROM cars WHERE car_text LIKE '%europe%') c 
ON (c.car_id = ti.tracking_object_id AND ti.tracking_type = 1)
    LEFT JOIN (SELECT bike_id FROM bikes WHERE bike_text LIKE '%europe%') b 
ON (b.bike_id = ti.tracking_object_id AND ti.tracking_type = 2)
    LEFT JOIN (SELECT truck_id FROM trucks WHERE truck_text LIKE '%europe%') tr 
ON (tr.truck_id = ti.tracking_object_id AND ti.tracking_type = 3)
    ORDER BY t.date_last_activity ASC, t.id ASC

子查询在连接和过滤掉大量记录时执行得更快。

子查询 跟踪 桌子会过滤掉很多其他不需要的东西 制造 结果是一张小桌子 T 加入。

同样适用于 跟踪项目 我们只感兴趣的桌子 跟踪类型1、2和3 ;创建较小的表 钛 . 如果有很多跟踪对象,您甚至可以在此子查询中添加跟踪对象筛选器。

表的类似方法 汽车、自行车、卡车 他们各自的条件 包含欧洲的文本 帮助我们创建较小的表 C、B、TR 分别。

另外,由于t.id是唯一的,因此删除group by t.id,我们正在该表或结果表上执行内部联接和左联接,因为不需要这样做。

最后,我只选择 每个表中的必需列 这是必要的,这也将减少内存空间和运行时的负载。

希望这有帮助。请让我知道您的反馈和运行统计。

我不确定它是否有效,在on子句中的每个表(汽车、自行车和卡车)上应用过滤器如何,在连接之前,它应该过滤掉行?