如何根据解释结果改进此查询
我有以下查询：
SELECT DISTINCT f1.match_static_id,
f2.comments_no,
f2.maxtimestamp,
users.username,
users.id,
matches_of_comments.localteam_name,
matches_of_comments.visitorteam_name,
matches_of_comments.localteam_goals,
matches_of_comments.visitorteam_goals,
matches_of_comments.match_status,
new_iddaa.iddaa_code
FROM comments AS f1
INNER JOIN (
SELECT match_static_id,
MAX( TIMESTAMP ) maxtimestamp,
COUNT( match_static_id ) AS comments_no
FROM comments
GROUP BY match_static_id
) AS f2 ON f1.match_static_id = f2.match_static_id
AND f1.timestamp = f2.maxtimestamp
INNER JOIN users ON users.id = f1.user_id
INNER JOIN matches_of_comments ON matches_of_comments.match_id = f2.match_static_id
LEFT JOIN new_iddaa ON new_iddaa.match_id = matches_of_comments.match_id
WHERE matches_of_comments.flag =1
ORDER BY f2.maxtimestamp DESC
这是该查询的EXPLAIN计划：
+----+-------------+---------------------+--------+-----------------------------------+-----------+---------+------------------------------------------+-------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+----+-------------+---------------------+--------+-----------------------------------+-----------+---------+------------------------------------------+-------+------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 542 | Using temporary; Using filesort |
| 1 | PRIMARY | f1 | ref | timestamp,match_static_id,user_id | timestamp | 4 | f2.maxtimestamp | 1 | Using where |
| 1 | PRIMARY | users | eq_ref | PRIMARY | PRIMARY | 4 | skormix_db1.f1.user_id | 1 | |
| 1 | PRIMARY | matches_of_comments | ALL | match_id | NULL | NULL | NULL | 20873 | Range checked for each record (index map: 0x8) |
| 1 | PRIMARY | new_iddaa | ref | match_id | match_id | 4 | skormix_db1.matches_of_comments.match_id | 1 | |
| 2 | DERIVED | comments | ALL | NULL | NULL | NULL | NULL | 933 | Using temporary; Using filesort |
+----+-------------+---------------------+--------+-----------------------------------+-----------+---------+------------------------------------------+-------+------------------------------------------------+
如果此匹配至少有一条评论，我使用此查询获取匹配信息。
我得到了团队的名字，代码（iddaa code），评论的数量，最后一次commrnt的timstamp，最后评论的作者。
我有一个很大的数据库，预计在接下来的几个月里会更大，我对MySQL的查询很新，我想确保从一开始就使用优化查询，所以我想知道如何阅读这解释了使查询更好更快的信息。
我发现表中有很多地方不使用索引，尽管我构建了这些索引。
我也看到在表列中派生，我不知道如何使这个查询更快，以及如何摆脱filesort，因为我不能让派生查询索引??
我使用索引（键）在查询中记下了使用表的结构，我希望能够提前得到我的问题的一些提示或简单答案。
评论（f1）表结构是：
CREATE TABLE `comments` (
`id` int(25) NOT NULL AUTO_INCREMENT,
`comments` text COLLATE utf8_unicode_ci NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`date` date NOT NULL,
`time` time NOT NULL,
`match_static_id` int(25) NOT NULL,
`ip` varchar(255) CHARACTER SET latin1 NOT NULL,
`comments_yes_or_no` int(25) NOT NULL,
`user_id` int(25) NOT NULL,
PRIMARY KEY (`id`),
KEY `timestamp` (`timestamp`),
KEY `match_static_id` (`match_static_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=935 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
用户表结构是：
CREATE TABLE `users` (
`id` int(25) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`gender` int(25) NOT NULL,
`first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`avatar` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`alert` int(25) NOT NULL,
`daily_tahmin` int(25) NOT NULL,
`monthly_tahmin` int(25) NOT NULL,
`admin` int(25) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=995 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
matches_of_comments_结构是：
CREATE TABLE `matches_of_comments` (
`id` int(25) NOT NULL AUTO_INCREMENT,
`en_tournament_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`tournament_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`country_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`match_status` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`match_time` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`match_date` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`static_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`fix_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`match_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`localteam_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`localteam_goals` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`localteam_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`visitorteam_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`visitorteam_goals` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`visitorteam_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`ht_score` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`flag` int(25) NOT NULL,
PRIMARY KEY (`id`),
KEY `match_status` (`match_status`),
KEY `match_date` (`match_date`),
KEY `match_id` (`match_id`),
KEY `localteam_id` (`localteam_id`),
KEY `visitorteam_id` (`visitorteam_id`),
KEY `flag` (`flag`)
) ENGINE=MyISAM AUTO_INCREMENT=237790 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
new_iddaa表结构是：
CREATE TABLE `new_iddaa` (
`id` int(25) NOT NULL AUTO_INCREMENT,
`match_id` int(25) NOT NULL,
`iddaa_code` int(25) NOT NULL,
`tv_channel` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`skormix_tahmin` varchar(255) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`),
KEY `match_id` (`match_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8191 DEFAULT CHARSET=latin1
从更紧迫的问题开始，讨论选项。
第一个直接的问题是：
SELECT DISTINCT …
select distinct是很慢的。 非常非常慢：它基本上比较了你的集合返回的每一行的每个字段。 当有一个ID保证每行都是唯一的时候，自然会有优化的空间，但是你自己的查询看起来并不像它提供任何这样的可能性：最多只有一个来自matches_of_comments和new_iddaa的元组。
要解决这个问题，请在两个或多个部分中分解查询，并只提取实际需要的内容。 这似乎是按照最新的评论日期排列matches_of_comments ，然后从users和new_iddaa获取额外的美容数据。
下一个是imho最大的问题：
INNER JOIN (
SELECT match_static_id,
MAX( TIMESTAMP ) maxtimestamp,
COUNT( match_static_id ) AS comments_no
FROM comments
GROUP BY match_static_id
) AS f2 ON f1.match_static_id = f2.match_static_id
AND f1.timestamp = f2.maxtimestamp
你在一个没有索引的(match_static_id, timestamp)元组上加入一个表，然后在那里获取一个庞大的集合。 你有一个保证合并的道路 - 不是你想要的。
最后一个令人eye目的问题是：
ORDER BY f2.maxtimestamp DESC
首先，你在那里没有限制。 这意味着你要构建，排序并返回一个庞大的集合。 当然你要分页数据，所以在查询中加入一个限制条款。
一旦你添加了限制，你需要考虑添加额外行的方式，以及如何订购。 基于你的模式，我想像new_iddaa 。 你是否以这样一种方式对事物进行分页，即后者的信息需要成为该查询的一部分以及它返回的行数？ 我想不是，因为你对这些行如何排序并不感兴趣。
扫描完您的模式之后，会弹出另外一个模板：
`match_id` varchar(255)
引用这个的行是整数，对吗？ 所以它也应该是一个整数，以避免将varchars投射到整数或反之亦然，并允许在任何情况下使用索引。
虽然与此特定查询无关，但以下两个字段也需要注意并进行正确的转换：
`tournament_id` varchar(255)
`match_time` varchar(255)
`match_date` varchar(255)
`static_id` varchar(255)
`fix_id` varchar(255)
`localteam_id` varchar(255)
`visitorteam_id` varchar(255)
为了改善查询...
在我阅读它时，您正在通过最新评论订购matches_of_comments 。 您还需要评论数量，所以我们首先这样做。 假设您正在为许多前10个分页，则查询将如下所示：
SELECT match_static_id,
MAX( TIMESTAMP ) maxtimestamp,
COUNT( match_static_id ) AS comments_no
FROM comments
GROUP BY match_static_id
ORDER BY maxtimestamp DESC
LIMIT 10 OFFSET 0
就这样。
它为您提供了10个ID - 如果您增加限制，则会提供更多ID。 在你的应用中遍历它们并构建一个in (…)子句，这将允许您根据需要从其他表中获取每个单独的数据位; 你可以用一个或几个查询来做到这一点，这很重要。 重点是避免加入该聚集，以便索引可用于后续查询。
通过完全删除上述查询，您可以更加戏剧性地改进事物。
为此，请向matches_of_comments添加三个字段，即last_comment_timestamp ， last_comment_user_id和num_comments 。 使用触发器维护它们，并在(flag, last_comment_timestamp)上添加一个索引。 这将允许您运行以下高效查询：
SELECT matches_of_comments.static_id,
matches_of_comments.num_comments,
matches_of_comments.last_comment_timestamp,
matches_of_comments.last_comment_user_id,
matches_of_comments.localteam_name,
matches_of_comments.visitorteam_name,
matches_of_comments.localteam_goals,
matches_of_comments.visitorteam_goals,
matches_of_comments.match_status
FROM matches_of_comments
WHERE matches_of_comments.flag = 1
ORDER BY matches_of_comments.last_comment_timestamp DESC
LIMIT 10 OFFSET 0
然后，您只需从users和new_iddaa选择所需的数据 - 使用单独的查询和in (…)子句， new_iddaa 。
这个查询的主要问题是连接的数量。 我会建议：
使用嵌套查询f2并将其输出插入到表或临时表中，使用索引更好。 （使用索引时，它会将全表扫描更改为索引，从而加快速度，这样的表可能会重用）。
或者换个1或同时换掉f1和f2 。 你需要尽早消除行。 所以首先得到你需要的匹配和时间戳列表，并且只加入必要的日期。 此查询需要大量的注释表并过滤出不必要的行。 对于相同的结果，采取10％比通过90％更容易。
删除Distinct因为它在这里什么都不做：所有连接都是平等的。 如果有任何重复项从所有表中获取数据并找出差异。 然后添加必要的JOIN条件来选择你需要的行。
考虑这些PRIMARY KEY s：comments： PRIMARY KEY (match_static_id) matches_of_comments： PRIMARY KEY (match_id) new_iddaa： PRIMARY KEY (match_id)
原因：主键应在具有含义的经常使用的列上完成。 在技术上使用AUTOINCREMENT ing列不会增加表的值。
您可以考虑将flag作为第一列添加到PRIMARY KEY 。 当flag改变时它会增加开销，但是会加速这个查询，因为所有flag=1记录将在单个范围内并且在hdd的顺序上。 希望这可以帮助。
如果你把这件事变成了什么：
SELECT match_static_id,
MAX( TIMESTAMP ) maxtimestamp,
COUNT( match_static_id ) AS comments_no
FROM comments
GROUP BY match_static_id
成一张桌子，你用触发器填充这个？ 当新记录保存在评论中时，它会在'comments_counter'表中触发更新或类似的内容？ 这应该会给你一些性能，因为你不需要操作的Max，Counts，group。
此外，matches_of_comments.match_id = f2.match_static_id具有不同的数据类型，第一个是varchar（255），第二个是int（25） - 它们都是int（25）应该有助于整体性能。
最后，我会使user_id在注释中成为user.id的外键。
链接地址: http://www.djcxy.com/p/75365.html上一篇: How can I improve this query depending on the explain results