代码之家 › 专栏 › 技术社区 › kovshenin

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

query-optimization optimization mysql

kovshenin · 技术社区 · 16 年前

以下是问题:

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 . 都有超过1000000行,InnoDB引擎。两者都有索引 twitter_id , 关系 在上有额外的索引( 特特特里德 , followed_by )执行查询需要6秒钟,这真的让我很沮丧。我知道我可以加入进来,但是我的MySQL知识并不那么酷,这就是为什么我需要你的帮助。

提前谢谢大家=)

干杯, K~

更新的

好吧,我设法降到了2,5秒。我使用了内部联接并添加了三个索引对。以下是解释结果:

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

哇,真是一团糟!对不起!

5 回复 | 直到 16 年前

Quassnoi 16 年前

创建以下复合索引:

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 查询可以从该索引中检索所需的所有值。

Greg 16 年前

一个连接看起来像这样:

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…在两个查询上。

Alex Martelli 16 年前

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;

可能有点快,但你需要测量并检查是否确实如此。

Gandalf 16 年前

伯爵(*) 在InnoDB引擎下是一个非常昂贵的操作,您是否尝试过不使用该块查询?如果它导致了最多的处理时间,那么也许您可以保留一个正在运行的值,而不是每次都查询它。

Michael Durrant 13 年前

我将从程序员的角度来处理这个问题;我将有一个单独的表(或某个存储区域),它存储了与原始查询中每个字段相关联的最大值、最小值和和和值,并在每次更新和添加表记录时更新这些值。(尽管如果处理不正确,删除可能有问题)。

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