mysql - 优化特定查询mysql

Question

所以我一直在寻找解决方案和阅读书籍，但一直无法弄清楚，问题很简单，我有 2 个表。在一张 table 上我有 2 个字段:

table_1:"chromosome"和 "position"都是整数。

table_2:"chromosome""start"和 "end"，也是整数。

我想要一个查询，返回 table_1 中 table_2 开始和结束之间的所有行。查询如下所示:

SELECT 
    table_1 . *
FROM
    table_1,
    table_2
WHERE
    table_1.chromosome = table_2.chromosome
        AND table_1.position > table_2.start
        AND table_1.position < table_1.end;

所以这个查询工作正常，但我的表分别有数百万行 (7092713) 和 (215909)。我索引了染色体，位置和染色体，开始，结束。奇怪的是，如果我一个一个地执行查询(perl DBI，对 table_2 的每一行执行一个语句)，这会运行得更快。不知道我在哪里搞砸了。任何帮助将不胜感激。

乔治·影山

Answer 1

为了清楚起见，让我们首先使用标准 JOIN 语法重新构造您的查询。该查询是等效的，但更易于阅读。

SELECT table_1 . *
  FROM table_1 
  JOIN table_2 ON (     table_1.chromosome = table_2.chromosome
                    AND table_1.position > table_2.start
                    AND table_1.position < table_1.end)

其次，在搜索大型表(或与此相关的任何表)时避免在 SELECT 子句中使用 * 是明智的。使用 * 拒绝向优化器提供有关您在结果集中需要做什么或不需要什么的有用数据。所以让我们说

SELECT table_1.chromosome, table_1.position

用于选择。

因此，很明显，您的结果集和您的连接需要染色体和位置，而不是您较大表中的其他任何东西。尝试在该表上创建复合 BTREE 索引，如下所示。

CREATE INDEX ON table_1(chromosome,position) USING BTREE

类似地，尝试按如下方式在 table_2 上创建索引。

CREATE INDEX ON table_2(chromosome,start, end) USING BTREE

这些被称为覆盖索引。它们包含足够的列，可以从索引中满足查询而不必返回到原始表。

BTREE 索引(顺便说一句，默认值)是固有排序的。 table_1 中的适当记录可以通过对以 (chromosome,start) 开始并以 (chromosome,end) 结束的索引进行范围扫描来找到。

第三，您的结果集中的 table_1 中的行可能会出现大量组合爆炸。对于两个表中与 ON() 子句匹配的每个行组合，您都会得到一行。在不了解您的数据的情况下，很难知道情况是否如此。

您可以尝试使用以下方法减少组合爆炸

SELECT DISTINCT table_1.chromosome, table_1.position

试一试。如果您仍然一无所获，也许另一个具有完整表定义的问题和 EXPLAIN 的结果会有所帮助。