有人愿意帮助优化 MySQL 查询吗？

Question

查询如下：

SELECT COUNT(*) AS c, MAX(`followers_count`) AS max_fc, 
       MIN(`followers_count`) AS min_fc, MAX(`following_count`) AS max_fgc,
       MIN(`following_count`) AS min_fgc, SUM(`followers_count`) AS fc,
       SUM(`following_count`) AS fgc, MAX(`updates_count`) AS max_uc,
       MIN(`updates_count`) AS min_uc, SUM(`updates_count`) AS uc
FROM `profiles`
WHERE `twitter_id` IN (SELECT `followed_by` 
                       FROM `relations` 
                       WHERE `twitter_id` = 123);

两个表是 profiles 和 relations。 两者都拥有超过 1,000,000 行的 InnoDB 引擎。 两者都在 twitter_id 上有索引，relations 在 (twitter_id, followed_by) 上有一个额外的索引。 查询执行时间超过 6 秒，这真的让我很沮丧。 我知道我可以以某种方式加入这个，但我的 MySQL 知识不太好，这就是为什么我请求你的帮助。

预先感谢大家=）

干杯， K ~

已更新

好的，我设法将时间缩短到 2.5 秒。 我使用 INNER JOIN 并添加了三个索引对。 这是解释结果：

id, select_type, table, type, possible_keys, 
    key, key_len, ref, rows, Extra

1, 'SIMPLE', 'r', 'ref', 'relation', 
    'relation', '4', 'const', 252310, 'Using index'

1, 'SIMPLE', 'p', 'ref', 'PRIMARY,twiter_id,id_fc,id_fgc,id_uc', 
    'id_uc', '4', 'follerme.r.followed_by', 1, ''

希望这有帮助。

另一个更新

这是两个表的 SHOW CREATE TABLE 语句：

CREATE TABLE `profiles` (
  `twitter_id` int(10) unsigned NOT NULL,
  `screen_name` varchar(45) NOT NULL default '',
  `followers_count` int(10) unsigned default NULL,
  `following_count` int(10) unsigned default NULL,
  `updates_count` int(10) unsigned default NULL,
  `location` varchar(45) default NULL,
  `bio` varchar(160) default NULL,
  `url` varchar(255) default NULL,
  `image` varchar(255) default NULL,
  `registered` int(10) unsigned default NULL,
  `timestamp` int(10) unsigned default NULL,
  `relations_timestamp` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`twitter_id`,`screen_name`),
  KEY `twiter_id` (`twitter_id`),
  KEY `screen_name` USING BTREE (`screen_name`,`twitter_id`),
  KEY `id_fc` (`twitter_id`,`followers_count`),
  KEY `id_fgc` (`twitter_id`,`following_count`),
  KEY `id_uc` (`twitter_id`,`updates_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `relations` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `twitter_id` int(10) unsigned NOT NULL default '0',
  `followed_by` int(10) unsigned default NULL,
  `timestamp` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`id`,`twitter_id`),
  UNIQUE KEY `relation` (`twitter_id`,`followed_by`)
) ENGINE=InnoDB AUTO_INCREMENT=1209557 DEFAULT CHARSET=utf8

哇，真是一团糟 =) 抱歉！

Answer 1

连接看起来像这样：

SELECT COUNT(*) AS c,
MAX(p.`followers_count`) AS max_fc,
MIN(p.`followers_count`) AS min_fc,
MAX(p.`following_count`) AS max_fgc,
MIN(p.`following_count`) AS min_fgc,
SUM(p.`followers_count`) AS fc,
SUM(p.`following_count`) AS fgc,
MAX(p.`updates_count`) AS max_uc,
MIN(p.`updates_count`) AS min_uc,
SUM(p.`updates_count`) AS uc
FROM `profiles` AS p
INNER JOIN `relations` AS r ON p.`twitter_id` = r.`followed_by`
WHERE r.`twitter_id` = 123;

为了帮助优化它，您应该在两个查询上运行 EXPLAIN SELECT ...。

Answer 2

创建以下复合索引：

profiles (twitter_id, followers_count)
profiles (twitter_id, following_count)
profiles (twitter_id, updates_count)

并发布查询计划，看在上帝的份上。

顺便问一下，这个 COUNT(*) 返回了多少行？

更新：

您的表格行很长。 在您选择的所有字段上创建复合索引：

profiles (twitter_id, followers_count, following_count, updates_count)

以便 JOIN 查询可以从该索引检索所需的所有值。

Answer 3

SELECT COUNT(*) AS c,
  MAX(`followers_count`) AS max_fc, MIN(`followers_count`) AS min_fc,
  MAX(`following_count`) AS max_fgc, MIN(`following_count`) AS min_fgc,
  SUM(`followers_count`) AS fc, SUM(`following_count`) AS fgc,
  MAX(`updates_count`) AS max_uc, MIN(`updates_count`) AS min_uc, SUM(`updates_count`) AS uc
FROM `profiles`
JOIN `relations`
  ON (profiles.twitter_id = relations.followed_by)
WHERE relations.twitted_id = 123;

可能会快一点，但您需要测量并检查是否确实如此。

Answer 4

count(*) 在 InnoDB 引擎下是一个非常昂贵的操作，您是否在没有那部分的情况下尝试过这个查询？ 如果它导致最多的处理时间，那么也许您可以保留一个运行值，而不是每次都查询它。

Answer 5

我会从程序员的角度来解决这个问题； 我有一个单独的表（或某处的存储区域），用于存储与原始查询中每个字段关联的最大值、最小值和总和值，并在每次更新和添加表记录时更新这些值。 （尽管如果处理不当，删除可能会出现问题）。

填充这些值的原始查询完成后（这与您发布的查询几乎相同），您实际上是将最终查询减少为从数据表中获取一行，而不是一次计算所有内容。