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

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

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

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

        3
  •  1
  •   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;
    

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

        4
  •  1
  •   Gandalf    16 年前

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

        5
  •  1
  •   Michael Durrant    13 年前

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

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