MySQL优化需要解释

Question

我需要从“最新”(即最高记录 ID)记录中获取字段的每个值(在本例中为 server_name)。

我已经在 server_name 和 id 上添加了 server_name_id 索引。

我的第一次尝试需要几分钟才能运行。

SELECT server_name, state
FROM replication_client as a
WHERE id = (
  SELECT MAX(id) 
  FROM replication_client 
  WHERE server_name = a.server_name)
ORDER BY server_name

我的第二次尝试运行了 0.001 秒。

SELECT rep.server_name, state FROM (
  SELECT server_name, MAX(id) AS max_id
  FROM replication_client
  GROUP BY server_name) AS newest,
replication_client AS rep
WHERE rep.id = newest.max_id
ORDER BY server_name

这种优化背后的原理是什么？ (我希望能够编写优化的查询而无需反复试验。)

附注解释如下:

mysql> EXPLAIN
    ->
    ->   SELECT server_name, state
    ->   FROM replication_client as a
    ->   WHERE id = (SELECT MAX(id) FROM replication_client WHERE server_name = a.server_name)
    ->   ORDER BY server_name
    -> ;
+----+--------------------+--------------------+------+----------------+----------------+---------+-------------------+--------+-----------------------------+
| id | select_type        | table              | type | possible_keys  | key            | key_len | ref               | rows   | Extra                       |
+----+--------------------+--------------------+------+----------------+----------------+---------+-------------------+--------+-----------------------------+
|  1 | PRIMARY            | a                  | ALL  | NULL           | NULL           | NULL    | NULL              | 630711 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | replication_client | ref  | server_name_id | server_name_id | 18      | mrg.a.server_name |  45050 | Using index                 |
+----+--------------------+--------------------+------+----------------+----------------+---------+-------------------+--------+-----------------------------+

mysql> explain  
    ->   SELECT rep.server_name, state FROM (
    ->     SELECT server_name, MAX(id) AS max_id
    ->     FROM replication_client
    ->     GROUP BY server_name) AS newest,
    ->   replication_client AS rep
    ->   WHERE rep.id = newest.max_id
    ->   ORDER BY server_name
    -> ;
+----+-------------+--------------------+--------+---------------+----------------+---------+---------------+------+---------------------------------+
| id | select_type | table              | type   | possible_keys | key            | key_len | ref           | rows | Extra                           |
+----+-------------+--------------------+--------+---------------+----------------+---------+---------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>         | ALL    | NULL          | NULL           | NULL    | NULL          |    2 | Using temporary; Using filesort |
|  1 | PRIMARY     | rep                | eq_ref | PRIMARY       | PRIMARY        | 4       | newest.max_id |    1 |                                 |
|  2 | DERIVED     | replication_client | range  | NULL          | server_name_id | 18      | NULL          |   15 | Using index for group-by        |
+----+-------------+--------------------+--------+---------------+----------------+---------+---------------+------+---------------------------------+

Answer 1

好吧，当您查看第一个解释计划中的两个词时，整个事情是不言自明的:DEPENDENT SUBQUERY

这意味着，对于 where 条件检查的每一行，都会执行子查询。当然，这可能会非常慢。

另请注意，执行查询时存在操作顺序。

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
ORDER BY clause 
SELECT clause

当您可以在 FROM 子句中进行过滤时，它比在 WHERE 子句中进行过滤要好...