MySQL MyISAM 两个相同表之间的计数字段差异

Question

我有以下 2 个相同的表，使用 MySQL:

DROP TABLE IF EXISTS `DB`.`tblNew`;
CREATE TABLE  `DB`.`tblNew` (
 `NumberPK` int(10) unsigned NOT NULL,
 `Count` int(10) unsigned NOT NULL,
 PRIMARY KEY (`NumberPK`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

和

DROP TABLE IF EXISTS `DB`.`tblPrev`;
CREATE TABLE  `DB`.`tblPrev` (
 `NumberPK` int(10) unsigned NOT NULL,
 `Count` int(10) unsigned NOT NULL,
 PRIMARY KEY (`NumberPK`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

我想根据两个表在“NumberPK”字段上的连接来获取两个表之间“Count”字段的差异。

我写了这个查询:

SELECT
 gvNew.NumberPK
FROM
 tblNew AS gvNew
LEFT OUTER JOIN
 tblPrev AS gvPrev ON gvNew.NumberPK = gvPrev.NumberPK
ORDER BY gvNew.Count - IFNULL(gvPrev.Count, 0) DESC
LIMIT 10

为了得到差异最大的10条“NumberPK”记录。

这样做的问题是 MySql 必须执行表扫描才能获得 Counts 的差异。连接非常有效，因为它在主键上，并且尽管有大约 50000 条记录，但结果几乎是即时返回的，我想知道扫描(在每个表中有两个 4 字节字段的表上)是否会是对于 1 到 4 百万行之间的表，速度也同样快。

或者，有没有更好的方法来做到这一点？

提前致谢，蒂姆

Answer 1

在 MyISAM 中使用 PRIMARY KEY 与使用任何其他索引相同:深入索引 BTree，然后对数据进行随机查找。

在您的情况下，您需要扫描 first 表的所有行，因为您无法预测哪 10 行会感兴趣。然后查找另一个表中的每一行，因为您无法判断需要哪 10 行。

拥有INDEX(NumberPK, Count)将避免上面提到的额外步骤。将其添加到两个表中，因为您无法预测优化器将按哪个顺序命中表。 (使用 InnoDB，PK 的聚类会使该索引变得多余。)

或者...这是另一种可能性。

SELECT NumberPk, MAX(`Count`) - MIN(`Count`) AS diff
  FROM (
    SELECT * FROM tblNew
    UNION ALL
    SELECT * FROM tblPrev
  ) X
  GROUP BY NumberPk
  ORDER BY diff DESC
  LIMIT 10

这消除了所有的查找，但增加了一个大的排序。所以，我无法预测它是否会更快。