主页 / dba / 问题 / 45679
Accepted
user658182
Asked: 2013-07-04 12:23:05 +0800 CST

如何更好地优化我的表和 LEFT JOIN 查询以查找正确表中尚不存在的项目？

  • 772

我有两个表，它们代表 url 列表及其相关的单词索引。以下是供参考的表定义。

desc urllist;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| url   | text                | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

和

desc wordlocation;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| urlid    | bigint(20) unsigned | NO   |     | NULL    |       |
| wordid   | bigint(20) unsigned | NO   |     | NULL    |       |
| location | int(10) unsigned    | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+

该软件应用程序是一个网络蜘蛛。它爬取一个 url 列表，提取这些 url，并将它们插入到urllist表中。然后，索引器检查哪些 url 还没有被索引，然后继续索引这些 url。

这是我用来在左表 ( urllist) 中查找尚未在右表 ( wordlocation) 中编制索引的项目的查询。此查询与mysql.com 网站上的建议一致：

select * from urllist ul 
left join wordlocation wl on ul.id = wl.urlid 
where wl.urlid IS NULL;

在撰写本文时，我的测试数据库只有 600 个索引 url，而 wordlocation 表有 130 万行。但是，我的 CPU 是 100%，我等待查询是否完成的最长时间是半小时（顺便说一句，它从来没有这样做过）。

为了彻底，这里是查询的解释：

explain select * from urllist ul left join wordlocation wl on ul.id = wl.urlid where wl.urlid IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                   |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
|  1 | SIMPLE      | ul    | ALL  | NULL          | NULL | NULL    | NULL |   50364 |                         |
|  1 | SIMPLE      | wl    | ALL  | NULL          | NULL | NULL    | NULL | 1351371 | Using where; Not exists |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+

我需要这个查询在几秒钟内完成，而不是几分钟。另外，我担心可扩展性。我有 40,000 个唯一的 url 等待添加到索引中，那么如何在我的表和查询设计中考虑到这一点？400,000 个网址？

关于我对当前表结构的决定的​​几点说明。

我无意停留在 400,000 个 url，但也许 bigint(20) 有点过分热心？

网址作为文本是出于更实际的原因。我索引了很多亚洲和其他外语域，这些域在数据库中不显示为对应的汉字或其他字符，并且经常占用超过 255 个字符。

我正在使用 MySQL。我绝对愿意接受有关更好的表和查询设计的建议。如果我能提供更多信息，请告诉我。

mysql database-design

2 个回答

  1. 首先，您的查询是正确的。不过，您不需要这些wordlocation列（无论如何它们都将是全部NULL），因此我将更 select *改为select ul.*：

    SELECT ul.* FROM urllist AS ul 
LEFT JOIN wordlocation AS wl ON ul.id = wl.urlid 
WHERE wl.urlid IS NULL;

    这种（反连接或反半连接）查询通常还有两种编写方式。NOT IN如果 2 个连接列都不能为空，则不建议使用which。您的urlid列确实不可为空，因此这也可以：

    SELECT ul.* FROM urllist AS ul 
WHERE ul.id NOT IN 
      ( SELECT wl.urlid
        FROM wordlocation AS wl 
      ) ;

    或使用NOT EXISTS：

    SELECT ul.* FROM urllist AS ul 
WHERE NOT EXISTS
      ( SELECT 1
        FROM wordlocation AS wl
        WHERE ul.id = wl.urlid 
      ) ;

    在 MySQL 中，所有三个查询都导致非常相似的执行计划和效率。因此，您应该测试您的数据分布（和表大小）和您使用的 MySQL版本，以决定使用哪个查询。

    现在查询当然很慢，因为wordlocation (urlid). 当您添加此索引时，效率会（显着）提高，但这并不是该表中缺少的全部。

    您没有定义主键。检查并考虑您的建模并找出哪些列唯一标识此表中的行。我的猜测是您正在处理网页，从中提取单词并存储所有（或部分）单词及其在网页中的位置。如果这是正确的，您可以使用(urlid, location)作为主键：

    ALTER TABLE wordlocation
  ADD PRIMARY KEY (urlid, location) ;

    如果你这样做，你就不需要在(urlid). 不过，您可能会有其他查询，即搜索单词（我猜您也有一个wordlist表），因此您还需要一个索引(wordid)- 或 on(wordid, location)或其他一些组合。您需要哪些索引取决于您计划对这些表运行哪些查询。

    对于urllist.url数据类型，我更喜欢使用VARCHAR(x)而不是TEXT. 您可以拥有的最长网址是多少？长度x可以长达 65535，如果你想索引它，索引最多可以是前 255 个字符。（我假设您将 URL 存储在那里，而不是实际的网页。如果您确实存储了网页，请忽略本段。）

    • 3
  2. Best Answer

    为了帮助您解决眼前的问题，在 wordlocation.urlid 上添加索引将对您的查询有很大帮助。

    对于可扩展性，听起来您最好的方法可能是向 urllist 添加一个字段，该字段可以轻松引用以查看哪些 url 已被索引。例如indexed，以默认值零调用的 tinyint 列（因此新条目始终为零）。然后，当您索引一个 url 时，将此列更新为一个。

    • 1

相关问题