MySQL查询优化、EXPLAIN和慢查询执行

Question

MySQL查询优化、EXPLAIN和慢查询执行

mysqloptimizationquery-optimizationexplain

3

我发现有一些查询出现了问题，尤其是这一个。以下是相关信息。

tgmp_games表格，大约有20,000行数据。

CREATE TABLE IF NOT EXISTS `tgmp_games` (
  `g_id` int(8) NOT NULL AUTO_INCREMENT,
  `site_id` int(6) NOT NULL,
  `g_name` varchar(255) NOT NULL,
  `g_link` varchar(255) NOT NULL,
  `g_url` varchar(255) NOT NULL,
  `g_platforms` varchar(128) NOT NULL,
  `g_added` datetime NOT NULL,
  `g_cover` varchar(255) NOT NULL,
  `g_impressions` int(8) NOT NULL,
  PRIMARY KEY (`g_id`),
  KEY `g_platforms` (`g_platforms`),
  KEY `site_id` (`site_id`),
  KEY `g_link` (`g_link`),
  KEY `g_release` (`g_release`),
  KEY `g_genre` (`g_genre`),
  KEY `g_name` (`g_name`),
  KEY `g_impressions` (`g_impressions`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

tgmp_reviews - 约有200k行

CREATE TABLE IF NOT EXISTS `tgmp_reviews` (
  `r_id` int(8) NOT NULL AUTO_INCREMENT,
  `site_id` int(6) NOT NULL,
  `r_source` varchar(128) NOT NULL,
  `r_date` date NOT NULL,
  `r_score` int(3) NOT NULL,
  `r_copy` text NOT NULL,
  `r_link` text NOT NULL,
  `r_int_link` text NOT NULL,
  `r_parent` int(8) NOT NULL,
  `r_platform` varchar(12) NOT NULL,
  `r_impressions` int(8) NOT NULL,
  PRIMARY KEY (`r_id`),
  KEY `site_id` (`site_id`),
  KEY `r_parent` (`r_parent`),
  KEY `r_platform` (`r_platform`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

这里是查询语句，大约需要3秒钟的时间

SELECT * FROM tgmp_games g
RIGHT JOIN tgmp_reviews r ON g_id = r.r_parent
WHERE g.site_id = '34'
GROUP BY g_name
ORDER BY g_impressions DESC LIMIT 15

解释

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  r   ALL     r_parent    NULL    NULL    NULL    201133  Using temporary; Using filesort
1   SIMPLE  g   eq_ref  PRIMARY,site_id     PRIMARY     4   engine_comp.r.r_parent  1   Using where

我只想获取最受欢迎的15个游戏，并为每个游戏获取一条评论（具体哪条评论并不重要，我猜最高评分的评论可能是理想的，r_score）。

有人可以帮我弄清楚为什么这样非常低效吗？

- Horse

WHERE g.site_id = '34' 这个条件使查询等同于一个 INNER 连接。 - ypercubeᵀᴹ

@ypercube 你能解释一下为什么吗？ - Horse

外连接会选择所有已连接的数据，加上（在右连接的情况下）右侧的所有数据 r，这些数据不匹配左侧 g 的任何行。然后这些额外的行被拒绝了，因为 g.site_id 为空（当然不等于34）。请记住，这些是在 g 表中没有匹配的行。 - ypercubeᵀᴹ

而且，RIGHT JOIN 在这里没有意义。我假设你永远不会有一个评论与游戏无关。对吗？ - ypercubeᵀᴹ

2个回答

1

你的MyISAM表很小，你可以尝试转换它来解决问题。你使用MyISAM而不是InnoDB有什么原因吗？

你也可以尝试对每个表运行分析以更新统计信息，看看优化器是否选择了不同的东西。

- Kadaan

我需要全文索引 :( 我会尝试分析。 - Horse

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- vyegorov · Accepted Answer

I don't understand what is the purpose of having a GROUP BY g_name in your query, but this makes MySQL performing aggregates on the columns selected, or all columns from both table. So please try to exclude it and check if it helps.
Also, RIGHT JOIN makes database to query tgmp_reviews first, which is not what you want. I suppose LEFT JOIN is a better choice here. Please, try to change the join type.
If none of the first options helps, you need to redesign your query. As you need to obtain 15 most viewed games for the site, the query will be:
```
SELECT g_id
  FROM tgmp_games g
 WHERE site_id = 34
 ORDER BY g_impressions DESC
 LIMIT 15;
```
This is the very first part that should be executed by the database, as it provides the best selectivity. Then you can get the desired reviews for the games:
```
SELECT r_parent, max(r_score)
  FROM tgmp_reviews r
 WHERE r_parent IN (/*1st query*/)
 GROUP BY r_parent;
```
Such construct will force database to execute the first query first (sorry for the tautology) and will give you the maximal score for each of the wanted games. I hope you will be able to use the obtained results for your purpose.