SELECT DISTINCT `talker_character_stat`.`character_id` FROM `talker_character` LEFT JOIN `talker_character_tag_rel` ON `talker_character_tag_rel`.`character_id` = `talker_character`.`id` INNER JOIN `talker_character_stat` ON `talker_character_stat`.`character_id` = `talker_character`.`id` WHERE (`talker_character`.`status` = 1 AND `talker_character`.`check_status` = 0 AND `talker_character`.`rec_status` NOT IN (2,3)) AND (`talker_character`.`region` IN ('SG','MY','HK','CN','MO','TW') OR `talker_character`.`id` IN (2751,4224,2945,3270,2698,1934,2447,6292,2841,4964,2598,103,2986,3949,2862,2167,2744,9312,9449,6959,7794,8632,9658,8841,8722,9166,8784,10037,9913,10152,10155,7945,2283,10449,8562,4353,3783,1224,1995,16,722,5269,1497,1692,1821,993,3555,2852,4196,1255,3120,4145,4599,4858,1979,2876,1534,5710,7030,9294,8134,6645,8162,9065,9713,8692,9403,8651,8729,10067,8396,8242,10586,10641)) AND `talker_character`.`id` NOT IN (16351,15528,14466,14220,13892,42232,41348,39475,38925,29391,18620,41189,39979,40609,42089,42144,77087,76406,74015,47564,77005,38396,87612) ORDER BY `talker_character_stat`.`r_value_fast_7` DESC LIMIT 40
表结构
CREATE TABLE `talker_character_stat` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`character_id` bigint(10) NOT NULL DEFAULT '0',
`r_value_fast_7` bigint(10) NOT NULL DEFAULT '0',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uniq_character_id` (`character_id`) USING BTREE,
KEY `idx_char_rfast` (`character_id`, `r_value_fast_7`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;
CREATE TABLE `talker_character_tag_rel` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`character_tag_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`character_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_character_tag` (`character_id`, `character_tag_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;
CREATE TABLE `talker_character` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint(3) unsigned NOT NULL DEFAULT '1',
`check_status` tinyint(3) NOT NULL DEFAULT '1',
`rec_status` tinyint(3) unsigned NOT NULL DEFAULT '1',
`region` varchar(50) NOT NULL DEFAULT '',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_creator_id` (`creator_id`),
KEY `idx_status` (`status`, `rec_status`, `check_status`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;
这个sql加了排序之后查询很慢,不加就很快,但是需求要求一定要加,要如何优化
先用子查询找到 character_id,再和 stat 表关联
更新新一下索引策略:
如果数据量不是特别大,可以用临时表: