比较MySQL中两个查询的性能表现。

Question

比较MySQL中两个查询的性能表现。

3

我正在尝试优化我创建的mysql表上的查询。我预计表中将有许多行。查看这个问题，被接受的答案和最高票答案提出了两种不同的方法。我编写了这两个查询，并想知道哪一个更有效。

SELECT uv.* 
FROM UserVisit uv INNER JOIN 
    (SELECT ID,MAX(visitDate) visitDate 
     FROM UserVisit GROUP BY ID) last 
    ON (uv.ID = last.ID AND uv.visitDate = last.visitDate);

使用EXPLAIN运行此命令会得到以下结果：

+----+-------------+------------+--------+---------------+---------+---------+--------------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref                            | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+--------------------------------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL                           |    2 |             |
|  1 | PRIMARY     | uv         | eq_ref | PRIMARY       | PRIMARY | 11      | last.playscanID,last.visitDate |    1 |             |
|  2 | DERIVED     | UserVisit  | index  | NULL          | PRIMARY | 11      | NULL                           |    4 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+--------------------------------+------+-------------+
3 rows in set (0.01 sec)

另一个查询如下：

SELECT lastVisits.* 
FROM ( SELECT * FROM UserVisit ORDER BY visitDate DESC ) lastVisits 
GROUP BY lastVisits.ID

使用EXPLAIN运行该查询的结果如下：

+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary; Using filesort |
|  2 | DERIVED     | UserVisit  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort                  |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.00 sec)

我不确定如何解释这两个EXPLAIN的结果。
这两个查询中哪一个更快，为什么呢？

编辑： 这是UserVisit表的样子：

+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| ID             | bigint(20) unsigned | NO   | PRI | NULL    |       |
| visitDate      | date                | NO   | PRI | NULL    |       |
| visitTime      | time                | NO   |     | NULL    |       |
| analysisResult | decimal(3,2)        | NO   |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

- Emil L

从技术上讲，第二个查询不能可靠地返回您所需的答案。实际上，它在迄今为止所有支持子查询的版本中都可以运行。但实际上，它很少优于第一个查询。 - Strawberry

@Strawberry，你能详细说明一下为什么第二个查询无法可靠地返回正确的答案吗？这是因为不能保证GROUP BY会选择顶部行吗？此外，你能解释一下为什么这些查询的性能差异很小吗？ - Emil L

1

准确地说，虽然实际上它总是这样做的（我只是在替恶魔辩护），但 Neville K 的论点有些牵强，因为未来版本中可能会发生各种变化。我更喜欢坚持第二个解决方案未经记录（除非您将相关页面的注释部分计算在内）。我对 MySQL 的机制不了解（读作“什么都不了解”），无法说明性能差异为什么可以忽略不计，但我已在大型（索引）表上测试了两个查询。绝对赞同 NK 关于复合键的观点。 - Strawberry

1个回答

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

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

- Neville Kuyt · Accepted Answer

首先，您可能需要阅读关于EXPLAIN的手册。这是一篇密集的阅读材料，但它应该提供大部分您想要的信息。

其次，正如Strawberry所说，第二个查询结果只是偶然出现的。行为可能在将来的版本中更改，而您的查询将不会返回错误，只是不同的数据。这几乎总是一件坏事。

最后，EXPLAIN表明版本1将更快。在EXTRA中，它表示正在使用索引，这比文件排序要快得多。没有模式，很难确定，但我认为您还将受益于在ID和visitdate上进行复合键。