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的篩選好像對速度影響不大，有影響可以砍掉

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/280108.html

標籤：疑難問題

上一篇：根據顏色得到序號

下一篇：救救孩子