mysql - 强制查询优化器使用主键

Question

我正在运行一个跨越 3 个表的查询，其中没有一个表的行数超过 55K。此查询运行时间超过 20 秒，这似乎过长:

SELECT
    `cp`.`author`,
    `cc`.`contents`
FROM
    `challenge_properties` as `cp`,
    `challenges` as `c`,
    `challenge_contents` as `cc`
WHERE
    `cp`.`followup_id` = `c`.`latest_followup` AND
    `cp`.`status` = 'new' AND
    `c`.`id` = `cp`.`challenge_id` AND
    `c`.`id` = `cc`.`challenge_id`

这是EXPLAIN查询的结果:

*************************** 1. row ***************************
       id: 1
  select_type: SIMPLE
        table: c
         type: ALL
possible_keys: PRIMARY,latest_followup_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 13817
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: cc
         type: ref
possible_keys: challenge_id
          key: challenge_id
      key_len: 5
          ref: cts.c.id
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: cp
         type: ref
possible_keys: challenge_id,followup_id
          key: followup_id
      key_len: 5
          ref: cts.c.latest_followup
         rows: 1
        Extra: Using where

如您所见，第一个表 challenges 有一个主键，但未被使用。我已经尝试将 FORCE KEY(PRIMARY) 子句添加到 challenges 表声明中，但它仍然没有被使用。

我可以做些什么来加速这个查询？谢谢。

Answer 1

您的查询正在从 challenges 中选择所有记录表——因此不需要对该表的记录使用任何索引。基本上 MySQL 正在选择 challenges 中的每条记录, 然后在其他两个表中找到匹配的记录。

你不能省略掉 challenges 吗？一起表？您没有从该表中选择数据，并且该表限制所选数据的唯一时间是您的其他表无效 challenge_id s，哪些外键可以处理...

SELECT
    `cp`.`author`,
    `cc`.`contents`
FROM
    `challenge_properties` as `cp`,
    `challenge_contents` as `cc`
WHERE
    `cp`.`status` = 'new' AND
    `cp`.`challenge_id` = `cc`.`challenge_id`

编辑:你说你不能删除 challenges来自查询的表...我会尝试指定您的 JOIN JOIN 中的条件子句而不是 WHERE :

SELECT
    `cp`.`author`,
    `cc`.`contents`
FROM `challenge_properties` as `cp`
JOIN `challenges` as `c`
    ON `cp`.`challenge_id` = `c`.`id`
    AND `cp`.`followup_id` = `c`.`latest_followup`
JOIN `challenge_contents` as `cc`
    ON `cc`.`challenge_id` = `c`.`id`
WHERE `cp`.`status` = 'new'

查询优化器可能已经为您完成了这项工作，但尝试一下也没什么坏处，而且我认为使用这种语法可以更轻松地了解连接是如何发生的。

您还可以尝试向 challenge_properties 添加另一个索引在 ( challenge_id, followup_id ) , 另一个到 challenges在 ( challenge_id, latest_followup ) — 复杂的键可以帮助 MySQL 更快地工作。但也有可能问题出在你的查询之外……通常当你 EXPLAIN并且在 rows 中只看到一张大数字表列，您的查询已得到很好的优化。 MySQL 只查看 challenge_properties 中的一行和 challenge_contents 中的一行，并扫描 challenges 中的每一行找到一个匹配项。

编辑 2:

不幸的是，我不确定还能做些什么来优化这个查询。如果使用的索引(cc.challenge_id 和 cp.followup_id)是 UNIQUE NOT NULL，您可以获得稍微更高的性能。索引，您应该使用 cp 的复杂索引获得更好的性能在 (cp.challenge_id, cp.followup_id) .这将使那些 type: ref进入type: eq_ref , 稍微好一点。但仅此而已......您是否有任何其他查询的问题？您的查询理论上应该返回 13817 行......数据量可能是问题所在吗？如果您只选择 COUNT(*) 是否会显着加快速度？而不是返回所有行？