Mysql 多表联查order by优化问题（需去重）

表一 data_detail

    SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for data_detail
-- ----------------------------
DROP TABLE IF EXISTS `data_detail`;
CREATE TABLE `data_detail`  (
  `id` bigint(20) UNSIGNED NOT NULL,
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '文本内容',
  `create_by` int(11) NULL DEFAULT NULL COMMENT '创建人',
  `data_set_id` int(11) NOT NULL COMMENT '数据集编号',
  `size` double(255, 0) NULL DEFAULT NULL COMMENT '文件大小',
  `status` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '0:未标注 2:人工标注 1:自动标注',
  `source` tinyint(3) UNSIGNED NULL DEFAULT 2 COMMENT '数据来源：1：上传； 2：手动创建； 3：运营管理改正；',
  `mkey` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '',
  `remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '备注，业务系统用',
  `origin_id` bigint(20) UNSIGNED NOT NULL COMMENT '基于那一条初始数据变化过来的',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `version_id` int(11) NULL DEFAULT NULL,
  `update_by` int(11) NULL DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (`id`, `data_set_id`) USING BTREE,
  INDEX `status_data_set_id`(`status`, `data_set_id`) USING BTREE,
  INDEX `id`(`id`) USING BTREE,
  INDEX `create_time`(`create_time`) USING BTREE,
  INDEX `indexid`(`create_time`, `id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '数据详情内容' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

表二 data_detail_delete
 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for data_detail_delete
-- ----------------------------
DROP TABLE IF EXISTS `data_detail_delete`;
CREATE TABLE `data_detail_delete`  (
  `data_detail_id` bigint(20) UNSIGNED NOT NULL COMMENT '数据集详情id',
  `origin_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '最原始的id\r\n最原始的数据详情id',
  `version_id` int(11) NULL DEFAULT NULL COMMENT '版本id',
  `data_set_id` int(11) NOT NULL COMMENT '数据集id',
  PRIMARY KEY (`data_detail_id`, `data_set_id`) USING BTREE,
  INDEX `version_data_set_id_index`(`version_id`, `data_set_id`) USING BTREE COMMENT '索引'
) ENGINE = MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

表三 data_tag_reference
 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for data_tag_reference
-- ----------------------------
DROP TABLE IF EXISTS `data_tag_reference`;
CREATE TABLE `data_tag_reference`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `detail_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '数据详情id',
  `type` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '表类型：1 ，类型1 ，2，类型2， 3，类型3',
  `data_set_id` int(11) NULL DEFAULT NULL COMMENT '数据集id',
  `tag_id` int(11) NULL DEFAULT NULL COMMENT '标签id',
  `tag_set_id` int(11) NULL DEFAULT NULL COMMENT '标签库id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `detail_id`(`detail_id`) USING BTREE,
  INDEX `isUesd`(`type`, `tag_id`, `tag_set_id`) USING BTREE,
  INDEX `detail_id_2`(`detail_id`, `data_set_id`) USING BTREE,
  INDEX `tagid`(`tag_id`, `detail_id`, `data_set_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 58544606 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

需要优化的sql

     SELECT
 DISTINCT(dd.id), dd.* 
FROM
    data_detail dd
    straight_join data_tag_reference dtr ON dd.id = dtr.detail_id 
    AND dd.data_set_id = dtr.data_set_id 
WHERE
    1024 >= dd.version_id 
    AND dd.data_set_id = 253
    AND dd.id NOT IN ( SELECT data_detail_id FROM data_detail_delete WHERE 1002 >= version_id AND data_set_id = 253 ) 
    AND dtr.tag_id in(-94110690 ) 
    AND    `content` like concat ('%','一' ,'%')

    ORDER BY
    dd.create_time  Desc
    
    LIMIT 0,
    50

三个表的数据量都比较大。
group by id 和order by create_time DESC这两个字段的冲突，不好解决。同时用了 group by和order by这两个字段，会使用filesort文件排序 ，全表排序，导致时间至少在4-5秒，如果没有order by create_time，时间只用了0.03s，在mysql8.0中测试没有这个冲突，会使用Backward index scan降序索引，就会非常快。系统用的是mysql5.7只能找其他优化方法。数据集详情又必须有按照创建时间降序排序这个需求，sql的这个order by也不能去掉，在这里花了不少时间，加各种索引都试过。

group by  id是为了去重。
在mysql8里面，可以用distinct（id）,  而且用到了 Backward index scan，mysql 8的这个新特性。速度就非常快，但是公司又不能用mysql8。MariaDb10.5试过，同样的语句，索引也一样， mysql8 里运行了0.03s,mariaDb用了7s，我看解释里面区别就是这个 Backward index scan。除了mysql8 ，其他数据库都是用到了filesort。 

各位大神，看看有没有办法能优化一下这个句子。

主要是索引的添加。

其中最重要的核心部分，造成卡的地方就是
ORDER BY
    dd.create_time  Desc
DISTINCT(dd.id)
还有limit 50.
这三个条件是必须的，核心需求。
其他where的筛选好像对速度影响不大，有影响可以砍掉

应要求执行计划 mysql 5.7的。

下面是mysql8.0的，多了个 Backward index scan

能不能对时间字段加索引呢？没有索引的order by是比较慢

您好，我是有问必答小助手，你的问题已经有小伙伴为您解答了问题，您看下是否解决了您的问题，可以追评进行沟通哦~

如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～

ps:问答VIP仅需29元，即可享受5次/月 有问必答服务，了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632

执行计划能贴一下吗，我个人觉得你的分析不准确，分组和排序冲突，这怎么理解，我没明白...

非常感谢您使用有问必答服务，为了后续更快速的帮您解决问题，现诚邀您参与有问必答体验反馈。您的建议将会运用到我们的产品优化中，希望能得到您的支持与协助！

速戳参与调研>>>https://t.csdnimg.cn/Kf0y

你好，我也遇到了相同的问题
有个分页查询，主表和从表是一对多关系，主从表都有很多查询条件
最后发现可以采用如下模式，经测试，能达到500ms效果，望采纳，谢谢
SELECT
distinct s.*
,
(select max(policy_num) from policy where insurance_id = s.id),
(select max(applicant) from policy where insurance_id = s.id),
(select max(policy_num) from policy where insurance_id = s.id),
(select max(company_name) from policy where insurance_id = s.id),
(select max(insurant) from policy where insurance_id = s.id),
(select max(insurant_phone) from policy where insurance_id = s.id)
FROM insurance AS s
ORDER BY s.payment_time desc
limit 0,10000
;