我有以下查询：
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代码),评论数量,最后一个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,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,KEY `match_id` (`match_id`) ) ENGINE=MyISAM AUTO_INCREMENT=8191 DEFAULT CHARSET=latin1
在讨论选项之前,先从更紧迫的问题开始.
第一个直接问题是：
SELECT DISTINCT …
选择区别很慢.非常非常慢：它基本上比较了您的集合返回的每一行的每个字段.当有一个ID确保每行唯一时,自然存在优化的空间,但是你自己的查询看起来并不像它提供任何这样的可能性：最好是来自matches_of_comments和new_iddaa的元组.
要解决此问题,请在两个或多个部分中断查询,并仅获取您正在执行的操作所需的内容.这似乎是按照最新评论日期对matches_of_comments进行排序,然后从用户和new_iddaa获取额外的化妆品数据.
下一个是imho最大的问题：
INNER JOIN (
SELECT match_static_id,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)元组上的表,该表没有索引,并且获取了一个巨大的集合.你有一个有保障的合并加入 – 不是你想要的.
最后一个引人注目的问题是：
ORDER BY f2.maxtimestamp DESC
首先,你没有限制.这意味着你要构建,排序并返回一个庞大的集合.当然,您正在对这些数据进行分页,因此在查询中通过添加limit子句来执行此操作.
添加限制后,您需要考虑添加额外行的内容以及应如何排序.根据您的架构,我想new_iddaa.您是否以这样的方式对事物进行分页,即后一个信息需要成为该查询的一部分以及它返回的行数？我想不到,因为你对这些行的排序方式显然不感兴趣.
扫描您的架构后,会弹出另外一个：
`match_id` varchar(255)
引用它的行是整数,对吗？所以它也应该是一个整数,以避免将varchars转换为int的开销,反之亦然,并允许在任何一种情况下使用索引.
虽然与此特定查询无关,但以下两个字段也需要注意并正确转换：
`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.您还需要评论数量,因此我们首先要这样做.假设您正在对前十个中的前十个进行分页,则查询如下所示：
SELECT match_static_id,COUNT( match_static_id ) AS comments_no FROM comments GROUP BY match_static_id ORDER BY maxtimestamp DESC LIMIT 10 OFFSET 0
就这样.
它为您提供10个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.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
