我如何根据解释结果改进此查询

debugcn 发表于 Dev

巴塞尔

我有以下查询：

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代码），评论数，最后一次交流的时间，最后评论的作者。
我有一个很大的数据库，并且预计在接下来的几个月中会更大，并且我对MySQL查询非常陌生，并且我想确保从一开始就使用优化查询，所以我想知道如何阅读这说明了信息，以使查询更好，更快。

我看到表中有很多地方即使我建立索引也不会使用索引。
我还在表列中看到了派生，并且我不知道如何使此查询更快，以及如何摆脱文件排序，因为我无法为派生查询创建索引？

我用索引（键）写下查询中使用表的结构，并希望对我的问题得到一些提示或简单答案，在此先感谢。

注释（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

matchs_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 …

Aselect distinct很慢。非常非常慢：它基本上比较集合返回的每一行的每个字段。当有一个ID保证每行唯一的优化时，自然就有优化的空间，但是您自己的查询看起来并没有提供任何这种可能性：最好是matches_of_commentsand的元组new_iddaa。

要解决此问题，请将查询分为两部分或更多部分，并且仅获取您正在执行的操作实际需要的内容。这似乎是按照matches_of_comments他们的最新评论日期排序的，然后从users和获取额外的化妆品数据new_iddaa。

下一个是恕我直言最大的问题：

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)没有索引的元组上的表连接起来，并从中获取一个巨大的集合。您有保证可以并购的方式，而不是您想要的。

最后一个令人震惊的问题是：

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按最新评论排序。您还需要评论的数量，因此我们首先要进行评论。假设您要对前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 (…)子句的单独查询即可，如前所述。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-06-4

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章