求一条mysql的sql语句,查询某条件下,以某字段为分组,各查询2条,要求尽量别用sql循环

飞檐走壁的容MM
  • 417
业务场景
查询所有楼层的 前两条楼中楼回复
CREATE TABLE `post` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '回复ID',
  `subject_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '主体ID',
  `scene_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '场景ID',
  `reply_post_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '被评论的回复ID',
  `content` text NOT NULL COMMENT '回复内容',
  `is_adopted` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '是否被采纳',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '回复人的用户ID',
  `create_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
  `status` enum('open','close','delete') NOT NULL DEFAULT 'open' COMMENT '状态',
  PRIMARY KEY (`id`),
  KEY `idx_subject_scene_level` (`subject_id`,`scene_id`,`reply_post_id`),
  KEY `idx_user_scene_id_level` (`user_id`,`scene_id`,`reply_post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=183 DEFAULT CHARSET=utf8mb4 COMMENT='回复表';
//目前的sql语句
explain select * from post 
where 
        post.subject_id=126
         and post.scene_id=1
        and post.reply_post_id!=0
    and      
    (  select count(p.id) from post as p 
           where p.subject_id=126
         and p.scene_id=1
        and p.reply_post_id=post.reply_post_id
         and p.id < post.id  limit 2
    ) <= 1

从执行计划中可以看出,子查询数量是依赖外部行数的,虽然命中多条索引,但是当单个reply_post_id数据非常多时,将非常消耗性能

clipboard.png

回复
阅读 965
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏