如何加速缓慢的SQL查询

Question

Table ideas: id, author_id, some_columns

Table ideas_tags: idea_id, tag_name

Table ideas_seen: idea_id, user_id

Table user: uid, ban, some_columns

我需要从列表中得到10个有标记的想法，它们的作者没有被禁止，并且不是在ideas_seen中为当前的用户。

现在，我的查询如下：

SELECT 
ideas.*,  GROUP_CONCAT(DISTINCT IT_V.tag_name SEPARATOR '|||') AS tags 
FROM `ideas` 
LEFT JOIN ideas_tags IT 
ON ideas.id=IT.idea_id 
LEFT JOIN ideas_tags IT_V 
ON ideas.id=IT_V.idea_id 
LEFT JOIN ideas_seen IV 
ON ideas.id=IV.idea_id AND IV.user_id=145974517 
LEFT JOIN users ON users.uid=ideas.author_id 

WHERE author_id!=145974517 AND IV.id IS NULL AND ( (IT.tag_name = 'some_tag') OR (IT.tag_name = 'another_tag') OR (IT.tag_name IS NULL) ) AND active=1 AND deleted=0 AND (users.ban=0 OR users.ban IS NULL) 

GROUP BY ideas.id 
ORDER BY id DESC 
LIMIT 10

这是网站上最慢的查询，我不知道如何加快速度。

解释：

​

CREATE TABLE IF NOT EXISTS `ideas` (
`id` int(11) NOT NULL,
  `tutorial` tinyint(4) NOT NULL,
  `text` text NOT NULL,
  `author_id` int(11) NOT NULL,
  `active` bit(1) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `views` int(11) NOT NULL,
  `views_all` int(11) DEFAULT '0',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `many_users` tinyint(4) DEFAULT NULL,
  `game_id` int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=35983 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ideas_seen` (
`id` int(11) NOT NULL,
  `idea_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3694368 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ideas_tags` (
`id` int(11) NOT NULL,
  `idea_id` int(11) NOT NULL,
  `tag_name` tinytext NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=86832 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(11) NOT NULL,
  `email` tinytext,
  `password_hash` tinytext,
  `restore_code` tinytext NOT NULL,
  `last_action` timestamp NULL DEFAULT NULL,
  `score` float NOT NULL,
  `date_register` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `posts_length` int(11) DEFAULT NULL,
  `settings` text,
  `titles` int(11) NOT NULL DEFAULT '1',
  `filter` int(11) NOT NULL DEFAULT '1',
  `note` text NOT NULL,
  `ban` tinyint(4) DEFAULT NULL,
  `mod_send` smallint(6) DEFAULT '0',
  `mod_get` int(11) DEFAULT '0',
  `fp_notified` int(11) NOT NULL DEFAULT '0',
  `skilled` tinyint(4) NOT NULL DEFAULT '0',
  `show_only_skilled` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `ideas`
 ADD PRIMARY KEY (`id`), ADD KEY `author_id` (`author_id`), ADD KEY `game_id` (`game_id`), ADD KEY `active` (`active`), ADD KEY `many_users` (`many_users`), ADD KEY `deleted` (`deleted`), ADD KEY `tutorial` (`tutorial`), ADD FULLTEXT KEY `idea_text` (`text`);

ALTER TABLE `ideas_seen`
 ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`), ADD KEY `idea_id` (`idea_id`);

ALTER TABLE `ideas_tags`
 ADD PRIMARY KEY (`id`), ADD KEY `tag_name` (`tag_name`(255)), ADD KEY `idea_id` (`idea_id`);

ALTER TABLE `users`
 ADD PRIMARY KEY (`uid`), ADD UNIQUE KEY `email` (`email`(255)), ADD KEY `ban` (`ban`), ADD KEY `fp_notified` (`fp_notified`), ADD KEY `skilled` (`skilled`);


ALTER TABLE `ideas`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=35983;
ALTER TABLE `ideas_seen`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3694368;
ALTER TABLE `ideas_tags`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=86832;

ALTER TABLE `ideas`
ADD CONSTRAINT `ideas_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `games` (`id`) ON DELETE NO ACTION;

ALTER TABLE `ideas_seen`
ADD CONSTRAINT `ideas_seen_ibfk_1` FOREIGN KEY (`idea_id`) REFERENCES `ideas` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE `ideas_tags`
ADD CONSTRAINT `ideas_tags_ibfk_2` FOREIGN KEY (`idea_id`) REFERENCES `ideas` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

Answer 1

• 不要使用TINYTEXT；改为VARCHAR(255)，去掉索引上的“前缀”。也就是说，将INDEX email(255)更改为INDEX(email)。
• 不要索引“标志”，这样的索引不会被使用，因为它们不会有用。示例：deleted
• 不要让一个索引成为另一个索引的“左”部分。例如PRIMARY KEY(id)和INDEX(id, ...)。在PRIMARY KEY的情况下，保留它；将另一个抛出，因为它没有提供额外的好处。
• 我不认为有必要两次加入idea_tags，看看是否可以避免这种情况。
• 这个问题受到了“膨胀-紧缩”综合症的困扰。它首先使用JOINs来膨胀行数，然后使用GROUP BY返回到原来的行(较少一些被过滤掉的行)。在这样做的过程中，庞大的ideas.*会在临时表中来回移动。
• TEXT (包括TINYTEXT)阻止了对tmp表更有效地使用MEMORY。

让我们走过去，消除膨胀-排气。

首先，让我们来构造外部部分：

SELECT ideas.*, ( ??? ) as tags
    FROM ideas
    WHERE ???
    ORDER BY ideas.id DESC
    LIMIT 10;

假设我们可以填写???，那么我们现在有了一条更快的评估路径。这将需要在id上建立索引，这是您在PRIMARY KEY(id)中所拥有的。幸运的话(没有WHERE)，只需要触摸10行。(在您的版本中，必须收集、分组、排序整个表，然后才交付10个表。)

由于您的所有JOINs都是LEFT JOINs，因此可以证明，与ideas以外的表有关的WHERE子句不会过滤掉任何行。只剩下

WHERE author_id!=145974517
  AND  active=1
  AND  deleted=0

为此，让我们使用(虽然我不确定它是否会被使用)：

INDEX(active, deleted, author_id)

回到AS tags..。现在，去掉查询，只获取tag_name值，为给定的ideas.id生成GROUP_CONCAT

SELECT GROUP_CONCAT(DISTINCT IT_V.tag_name SEPARATOR '|||') AS tags
    FROM       ideas_tags IT_V  ON ideas.id = IT_V.idea_id
      AND  (      IT.tag_name = 'some_tag'
              OR  IT.tag_name = 'another_tag'
              OR  IT.tag_name IS NULL
           )

(这就是为什么有两个连接到idea_tags的原因。)同时，我建议SELECT可以作为获取tags的子查询。

嗯..。关于

LEFT JOIN  users ON users.uid = ideas.author_id 
WHERE ( users.ban=0  OR  users.ban IS NULL )

这似乎没有过滤，因为它是LEFT。它似乎没有提供任何列，因为在其他地方没有提到users。那么，我必须假设它是浪费代码？

同为

LEFT JOIN  ideas_seen IV    ON ideas.id = IV.idea_id
                 AND  IV.user_id=145974517
WHERE   IV.id IS NULL

因此，它归结为删除一些索引，添加一个索引，并将查询重写为

SELECT  ideas.*, 
        ( SELECT  GROUP_CONCAT(DISTINCT IT_V.tag_name SEPARATOR '|||')
            FROM  ideas_tags IT_V
            WHERE  ideas.id = IT_V.idea_id
              AND  (   IT.tag_name = 'some_tag'
                   OR  IT.tag_name = 'another_tag'
                   OR  IT.tag_name IS NULL ) 
        ) as tags
    FROM  ideas
    WHERE  author_id!=145974517
      AND  active=1
      AND  deleted=0
    ORDER BY  ideas.id DESC
    LIMIT  10;

也许，DISTINCT是不必要的。