mysql - 需要建议优化 SQL 查询(更新 MySQL)

Question

我使用慢速查询日志对我的数据库进行了性能分析。事实证明这是头号烦恼:

UPDATE
    t1
SET
  v1t1 =
  (
    SELECT
        t2.v3t2
    FROM
        t2
    WHERE
        t2.v2t2 = t1.v2t1
    AND t2.v1t2 <= '2012-04-24'
    ORDER BY
        t2.v1t2 DESC,
        t2.v3t2 DESC
    LIMIT 1
);

子查询本身已经很慢了。我尝试了 DISTINCT、GROUP BY 和更多子查询的变体，但没有执行低于 4 秒。例如下面的查询

SELECT v2t2, v3t2
FROM t2
WHERE t2.v1t2 <= '2012-04-24'
GROUP BY v2t2
ORDER BY v1t2 DESC    

需要:

mysql> SELECT ...
...    
69054 rows in set (5.61 sec)    

mysql> EXPLAIN SELECT ...
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | t2          | ALL  | v1t2          | NULL | NULL    | NULL | 5203965 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------------------------------------+

mysql> SHOW CREATE TABLE t2;
...
  PRIMARY KEY (`v3t2`),
  KEY `v1t2_v3t2` (`v1t2`,`v3t2`),
  KEY `v1t2` (`v1t2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8  

SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|    77070 |
+----------+

SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
|  5203965 |
+----------+

我正在尝试获取最新条目 (v3t2) 及其父条目 (v2t2)。应该没什么大不了的，不是吗？有人对我应该转动哪个旋钮有什么建议吗？非常感谢任何帮助或提示!

这应该是一个更合适的 SELECT 语句:

SELECT
    t1.v2t1,
  (
    SELECT
        t2.v3t2
    FROM
        t2
    WHERE
        t2.v2t2 = t1.v2t1
    AND t2.v1t2 <= '2012-04-24'
    ORDER BY
        t2.v1t2 DESC,
        t2.v3t2 DESC
    LIMIT 1
) AS latest   
FROM
    t1

Answer 1

你的 ORDER BY ... LIMIT 1强制数据库对表执行完整扫描以仅返回 1 行。它看起来非常像索引的候选对象。

在构建索引之前，通过运行检查字段选择性:

SELECT count(*), count(v1t2), count(DISTINCT v1t2) FROM t2;

如果您有大量非 NULL您列中的值和不同值的数量超过非 NULL 的 40% s，那么建立索引是一件好事。

如果索引没有帮助，您应该分析列中的数据。您正在使用 t2.v1t2 <= '2012-04-24'条件，如果您的表中有一组历史记录，则不会给计划者任何信息，因为所有行都应该是过去的，因此无论如何全扫描是最好的选择。因此，indexe 是无用的。

相反，您应该做的是考虑如何以仅检查有限的记录子集的方式重写您的查询。你的构造 ORDER BY ... DESC LIMIT 1显示您可能想要最近的条目 '2012-04-24' (包括)。为什么不尝试将查询重写为类似以下内容:

SELECT v2t2, v3t2
FROM t2
WHERE t2.v1t2 => date_add('2012-04-24' interval '-10' DAY)
GROUP BY v2t2
ORDER BY v1t2 DESC;

这只是一个示例，了解您的数据库的设计和数据的性质可以构建更精确的查询。