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

使用index返回不同的结果

optimization mysql

David · 技术社区 · 14 年前

以前从未见过。运行相同的查询时,1强制执行索引。如果没有索引,结果就不正确(顺序错误),而索引的结果则是正确的顺序。使用索引的唯一问题是,由于某种原因,它的速度变慢了。索引位于注释ID和用户ID上

无索引:

SELECT DISTINCT topic_id FROM comments
WHERE user_id=9384
AND (status = 1 or status = 0)
ORDER BY comment_id DESC LIMIT 15

带索引:

SELECT DISTINCT topic_id FROM comments force index(index_comment_user)
WHERE user_id=9384
AND (status = 1 or status = 0)
ORDER BY comment_id DESC LIMIT 15

有什么想法吗?我真的想在不减慢查询速度的情况下获得正确的顺序。我可以通过一个索引来做到这一点。

这是SQL结构。

CREATE TABLE  `db`.`comments` (
  `comment_id` int(10) unsigned NOT NULL auto_increment,
  `old_comments_id` mediumint(8) unsigned default NULL,
  `user_id` mediumint(8) unsigned default NULL,
  `content` text character set latin1,
  `status` tinyint(3) unsigned default NULL,
  `added_date` datetime default NULL,
  `category_id` tinyint(3) unsigned default NULL,
  `helpful` tinyint(3) unsigned default NULL,
  `modified_date` datetime default NULL,
  `topic_id` mediumint(8) unsigned default NULL,
  `last_mod_user_id` mediumint(8) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`comment_id`),
  KEY `Index_user_id` (`user_id`),
  KEY `Index_added_date` (`added_date`),
  KEY `Index_comments_status` USING BTREE (`status`),
  KEY `Index_user_activity` USING BTREE (`comment_id`,`user_id`),
  KEY `Index_user_activity2` USING BTREE (`user_id`,`topic_id`),
  KEY `Index_question_id` USING BTREE (`topic_id`,`status`),
  KEY `Index_user_activity3` (`user_id`,`status`,`topic_id`,`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2040237 DEFAULT CHARSET=utf8;

4 回复 | 直到 14 年前

Mark Byers 14 年前

在未选择的列上使用distinct和order by会产生问题。尝试使用分组依据:

SELECT topic_id, MAX(comment_id) AS comment_id
FROM comments
WHERE user_id=9384 AND status IN (0, 1)
GROUP BY topic_id
ORDER BY comment_id DESC
LIMIT 15

你不需要强制索引。只要添加正确的索引,它就会自动使用。您可能需要尝试索引中列的不同组合和顺序,以查看哪种组合最有效。

Rup 14 年前

这个 manual says

如果表有多列索引,优化器可以使用索引的任何最左边的前缀来查找行。例如,如果在(col1、col2、col3)上有三列索引,则在(col1)、(col1、col2)和(col1、col2、col3)上有索引搜索功能。

我想你需要一个索引 user_id, column_id 对于您的查询-听起来不像mysql可以使用 column_id, user_id . 你应该跑步 EXPLAIN SELECT 对这两个查询进行验证。

为什么其他索引出错了?对不起。

ceteras 14 年前

请提供以下结果:

EXPLAIN SELECT topic_id, MAX(comment_id) AS commentId
FROM comments
WHERE user_id=9384 AND status IN (0, 1)
GROUP BY topic_id
ORDER BY commentId DESC
LIMIT 15

我想知道max(comment_i d)的别名是否与列同名是否重要。这是不明确的,您如何判断MySQL是使用列还是使用聚合函数进行排序?

Tomalak 14 年前

这是常见的“每组最大值”问题,通常是这样解决的:

SELECT
  comment.user_id, 
  comment.topic_id,
  comment.comment_id
FROM
  comment
  INNER JOIN (
      SELECT user_id, topic_id, MAX(comment_id) AS comment_id
        FROM comments 
    GROUP BY user_id, topic_id
  ) AS max ON max.comment_id = comment.comment_id
WHERE
  comment.user_id = 9384
  AND comment.status IN (1, 0)
ORDER BY
  comment.comment_id DESC