我正在寻找一些建议或优化。表定义:
CREATE TABLE IF NOT EXISTS MilestonesAndFlags(
id SERIAL,
site_id BIGINT,
milestone BIGINT,
value BIGINT,
TIMESTAMP BIGINT,
timestamp_confirmation BIGINT,
COMMENT TEXT,
INDEX(site_id),
INDEX(milestone),
INDEX(milestone,site_id)
);
在这个表中,我用时间戳存储了每个不同站点的不同里程碑(以便能够查看任何更改的历史视图)。
问题发生时,我试图获得最新的实际里程碑值每个网站使用查询,如
SELECT site_id,
value
FROM SitesMilestonesAndFlags
WHERE id IN
(SELECT max(id)
FROM SitesMilestonesAndFlags
WHERE milestone=1
GROUP BY milestone,
site_id);
这个请求的执行时间在我的PC上更高5分钟。EXPLAIN
似乎没问题:
+----+--------------------+--------------------+------+-----------------------+-------------+---------+-------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------+------+-----------------------+-------------+---------+-------+--------+--------------------------+
| 1 | PRIMARY | MilestonesAndFlags | ALL | NULL | NULL | NULL | NULL | 1111320| Using where |
| 2 | DEPENDENT SUBQUERY | MilestonesAndFlags | ref | milestone,milestone_2 | milestone_2 | 9 | const | 180660| Using where; Using index |
+----+--------------------+--------------------+------+-----------------------+-------------+---------+-------+--------+--------------------------+
关于更正确的查询或表结构有什么建议吗?
我将尝试并建议您使用temporary aliased table
而不是dependent subquery
的where语句。不确定mysql是否优化或运行子查询的每一行的主/外部查询。
如果你在大数据上运行查询并返回结果,那将是非常有趣的。
例子:
SELECT *
FROM MilestonesAndFlags AS MF,
(SELECT max(id) AS id
FROM MilestonesAndFlags
WHERE milestone=1
GROUP BY milestone,
site_id) AS MaxMF
WHERE MaxMF.id = MF.id;
SQLFiddle: http://sqlfiddle.com/# !2/a0d628/10
利弊:
职业:避免依赖子查询。
缺点:
- Join导致投影和选择。这将导致临时表的所有行与原始表的行"相乘",然后在条件过滤的地方。
我也怀疑mysql的版本在所做的优化中起着主要作用。下面解释了两个不同版本的mysql的结果,其中一个将子查询定义为依赖的,而另一个则不是。
MySQL 5.5.32ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 PRIMARY MilestonesAndFlags ALL (null) (null) (null) (null) 29 Using where; Using filesort
2 DEPENDENT SUBQUERY MilestonesAndFlags ref milestone,milestone_2 milestone_2 9 const 15 Using where; Using index
http://sqlfiddle.com/!2/a0d628/11
MySQL MySQL 5.6.6 m9
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 PRIMARY MilestonesAndFlags ALL (null) (null) (null) (null) 29 Using where; Using filesort
2 SUBQUERY MilestonesAndFlags ref milestone,milestone_2 milestone_2 9 const 15 Using where; Using index
http://sqlfiddle.com/!9/a0d62/2