我还能做些什么来优化这个 MySQL 查询吗？

Question

我有两个表，表 A 有 700,000 个条目，表 B 有 600,000 个条目。结构如下：

表 A：

+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| number    | bigint(20) unsigned | YES  |     | NULL    |                | 
+-----------+---------------------+------+-----+---------+----------------+

表 B：

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| number_s    | bigint(20) unsigned | YES  | MUL | NULL    |                | 
| number_e    | bigint(20) unsigned | YES  | MUL | NULL    |                | 
| source      | varchar(50)         | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

我试图使用以下代码查找表 A 中的任何值是否存在于表 B 中：

$sql = "SELECT number from TableA";
$result = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
        $number = $row['number'];
        $sql = "SELECT source, count(source) FROM TableB WHERE number_s < $number AND number_e > $number GROUP BY source";
        $re = mysql_query($sql) or die(mysql_error);
        while($ro = mysql_fetch_array($re)) {
                echo $number."\t".$ro[0]."\t".$ro[1]."\n";
        }
}

我希望查询会很快，但由于某种原因，速度并不快。我对选择（具有特定的“数字”值）的解释给出了以下内容：

mysql> explain SELECT source, count(source) FROM TableB WHERE number_s < 1812194440 AND number_e > 1812194440 GROUP BY source;
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table      | type | possible_keys           | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | TableB     | ALL  | number_s,number_e       | NULL | NULL    | NULL | 696325 | Using where; Using temporary; Using filesort | 
+----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

我可以从中挤出任何优化吗？

我尝试为同一任务编写一个存储过程，但它似乎一开始就不起作用...它没有给出任何语法错误...我尝试运行它一天，但它仍在运行感觉很奇怪。

CREATE PROCEDURE Filter() 
Begin 
  DECLARE number BIGINT UNSIGNED; 
  DECLARE x INT; 
  DECLARE done INT DEFAULT 0; 
  DECLARE cur1 CURSOR FOR SELECT number FROM TableA; 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
  CREATE TEMPORARY TABLE IF NOT EXISTS Flags(number bigint unsigned, count int(11)); 
  OPEN cur1; 
  hist_loop: LOOP 
    FETCH cur1 INTO number; 
    SELECT count(*) from TableB WHERE number_s < number AND number_e > number INTO x; 
    IF done = 1 THEN 
      LEAVE hist_loop; 
    END IF; 
    IF x IS NOT NULL AND x>0 THEN 
      INSERT INTO Flags(number, count) VALUES(number, x); 
    END IF; 
  END LOOP hist_loop; 
  CLOSE cur1;
END

Answer 1

您正在尝试查找包含点的间隔。对于 B-tree 索引（大多数情况下的默认索引类型），这并不是那么快数据库），但是 R-tree 索引非常适合此类查询。 MySQL 不允许您直接更改索引的类型，但您可以通过使用 GEOMETRY 列类型强制 MySQL 使用 R-Tree。

Quassnoi 在 这篇关于 MySQL 中嵌套集的文章。虽然不完全相同，但非常相似。引用文章中的话：

还有特定类别的任务
需要搜索所有范围
包含已知值：

* 在 IP 范围禁止列表中搜索 IP 地址
* 搜索指定日期范围内的日期

还有其他几个。这些任务可以是
通过使用 R-Tree 功能进行改进
MySQL的

Answer 2

在我看来，您在 number_e 和 number_s 列上有单独的索引，可能是使用单独的 ADD INDEX(number_e) 和 创建的添加 INDEX(number_s) 列。

如果您添加一个包含这两列的索引，您可能会获得更好的性能，因为它们都在您的查询中使用，并且 MySQL 显然不会选择使用任何一个单列索引，判断整个表扫描会更快（如果您的查询跨越很大范围的值，这种情况并不罕见）。

ALTER TABLE tblB ADD INDEX(number_s,number_e);

此后您将不再需要单独的 number_s 索引，因为 MySQL 可以使用您刚刚创建的索引仅针对 number_s 进行查询，因此您不妨删除该索引。

Answer 3

首先，我假设所需的输出是将输入位于之间的所有“源”分组
number_e 和 number_s 及其计数。

我对语法不太了解，但您可能会考虑使用“BETWEEN”子句，而不是使用小于/大于运算符进行显式比较

编辑：Zombat 所说的也适用；索引也会有所帮助。