重写这个子查询？

Question

我正在尝试构建一个新表，以便现有表中的值不包含在另一个表中（但显然以下检查包含）。以下是我的表结构：

mysql> explain t1;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| id        | int(11)             | YES  |     | NULL    |       | 
| point     | bigint(20) unsigned | NO   | MUL | 0       |       | 
+-----------+---------------------+------+-----+---------+-------+

mysql> explain whitelist;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| x           | bigint(20) unsigned | YES  |     | NULL    |                | 
| y           | bigint(20) unsigned | YES  |     | NULL    |                | 
| geonetwork  | linestring          | NO   | MUL | NULL    |                | 
+-------------+---------------------+------+-----+---------+----------------+

我的查询如下所示：

SELECT point 
  FROM t1 
 WHERE EXISTS(SELECT source 
                FROM whitelist 
               WHERE MBRContains(geonetwork, GeomFromText(CONCAT('POINT(', t1.point, ' 0)'))));

解释：

    +----+--------------------+--------------------+-------+-------------------+-----------+---------+------+------+--------------------------+
| id | select_type        | table              | type  | possible_keys     | key       | key_len | ref  | rows | Extra                    |
+----+--------------------+--------------------+-------+-------------------+-----------+---------+------+------+--------------------------+
|  1 | PRIMARY            | t1                 | index | NULL              | point     | 8       | NULL | 1001 | Using where; Using index | 
|  2 | DEPENDENT SUBQUERY | whitelist          | ALL   | _geonetwork       | NULL      | NULL    | NULL | 3257 | Using where              | 
+----+--------------------+--------------------+-------+-------------------+-----------+---------+------+------+--------------------------+

对于 t1 中的 1000 条记录，查询需要 6 秒执行，这对我来说是不可接受的。如果我没有要连接的列，如何使用连接（或者可能是更快的方法，如果存在）重写此查询？我想在最坏的情况下，即使是存储过程也是可以接受的。我的目标是最终创建一个包含 t1 中的条目的新表。有什么建议吗？

Answer 1

除非查询优化器失败，否则 WHERE EXISTS 构造应产生与使用 GROUP 子句的联接相同的计划。看看优化 MBRContains(geonetwork, GeomFromText(CONCAT('POINT(', t1.point, ' 0)'))))，这可能是您的查询花费所有时间的地方。我对此没有建议，但这是用 JOIN 编写的查询：

Select t1.point
from t1
join whitelist on MBRContains(whitelist.geonetwork, GeomFromText(CONCAT('POINT(', t1.point, ' 0)'))))
group by t1.point
;

或者获取 t1 中不在白名单中的点：

Select t1.point
from t1
left join whitelist on MBRContains(whitelist.geonetwork, GeomFromText(CONCAT('POINT(', t1.point, ' 0)'))))
where whitelist.id is null
;

Answer 2

这似乎是一种反规范化 t1 可能有益的情况。添加值为 GeomFromText(CONCAT('POINT(', t1.point, ' 0)')) 的 GeomFrmTxt 列可以加快您已有的查询速度。