MySQL中的Update-Join比没有索引的Select查询要慢得多。

Question

MySQL中的Update-Join比没有索引的Select查询要慢得多。

5

我有一个MySQL表格，其中包含10万行记录，记录了一些服务器日志，格式如下：

CREATE TABLE `logs` (  
  `id` INT NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(16) NULL,
  `date` DATETIME NULL,
  `session_time` SMALLINT UNSIGNED NULL,
  PRIMARY KEY (`id`));

我正在尝试计算会话时间，即相同IP地址的连续行之间的时间差。我可以通过以下选择查询来实现这一目标，并且查询时间不到一秒钟：

SELECT * FROM logs AS a
LEFT JOIN (
    SELECT id,
        from_unixtime(@diff) AS starttime,
        date AS endtime,
        IF(@diff = 0, 0, (unix_timestamp(date) - @diff)/60) AS session_time1,
        @diff := unix_timestamp(date)
    FROM logs,
        (SELECT @diff := 0) AS x
    ORDER  BY ip, logs.date
) AS b ON
    a.id = b.id

然而，当我试图使用之前的查询来进行更新连接以更新时间会话时，以下更新查询需要超过600秒：

UPDATE logs AS a
LEFT JOIN (
    SELECT id,
        from_unixtime(@diff) AS starttime,
        date AS endtime,
        IF(@diff = 0, 0, (unix_timestamp(date) - @diff)/60) AS session_time1,
        @diff := unix_timestamp(date)
    FROM logs,
        (SELECT @diff := 0) AS x
    ORDER  BY ip, logs.date
) AS b ON
    a.id = b.id
SET session_time = session_time1;

我错过了什么？

谢谢！

更新：这里是select的EXPLAIN：

+----+-------------+------------+--------+---------------+------+--------+
| id | select_type |   table    |  type  | possible_keys | key  |  rows  |
+----+-------------+------------+--------+---------------+------+--------+
|  1 | PRIMARY     | a          | ALL    | NULL          | NULL | 109029 |
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL | 108680 |
|  2 | DERIVED     | <derived3> | system | NULL          | NULL | 1      |
|  2 | DERIVED     | logs       | ALL    | NULL          | NULL | 109029 |
|  3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL   |
+----+-------------+------------+--------+---------------+------+--------+

- kahlo

1

WHERE ip = '...'？看起来你正在更新所有的10万条目，但是你选择的那些（LEFT JOIN = 不符合第一组选择规则的元素）。尝试使用INNER JOIN？ - Alejandro Iván

谢谢Alejandro，但我不太理解你的评论。为什么内连接会更好？你用“where”子句是什么意思？ - kahlo

关于INNER JOIN，我只是想知道它是否比LEFT JOIN更好。当你有两个相交的组（假设为A、B和A&B作为它们的交集）时，如果你使用INNER JOIN，你将得到来自A&B的元素（所有与你的ON条件匹配且属于两个组的元素）。LEFT JOIN将给你在A上不符合你的ON条件的元素，RIGHT JOIN将对B组执行相同的操作。因此，在你的SQL语句中需要匹配两个组的记录，那么INNER JOIN应该更好（假设我理解了你的问题）。 - Alejandro Iván

我只是想给你一些尝试的选择。希望它们能对你有所帮助！ - Alejandro Iván

@AlejandroIván 实际上，使用内连接解决了问题。我很抱歉，没有尝试你的建议就将其抛弃了！ - kahlo

显示剩余4条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rick James · Answer 1

从所有行开始，将session_time设置为NULL。以两种方式更改查询：

在UPDATE的结尾处添加WHERE session_time IS NULL
如果没有结束时间，则将session_time设置为NULL，否则将其正确设置。

第一晚，它与现在一样慢，但之后，它将快得多，因为它仅处理少量“新”记录。

编辑

JOIN通常需要ON子句。如何使用logs的PRIMARY KEY连接logs和a。 EXPLAIN显示它需要运行109K * 108K个logs和a的组合；它应该只是109K。

此外，除非有必要，否则删除LEFT关键字。