如何优化带有排序的复杂SQL查询速度？

Question

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加了排序之后查询很慢，不加就很快，但是需求要求一定要加，要如何优化

Answer 1

先用子查询找到 character_id，再和 stat 表关联

SELECT t.character_id
FROM (
    SELECT cs.character_id, cs.r_value_fast_7
    FROM talker_character_stat cs
    WHERE cs.character_id IN (
        SELECT id 
        FROM talker_character c
        WHERE c.status = 1 
        AND c.check_status = 0 
        AND c.rec_status NOT IN (2,3)
        AND (c.region IN ('SG','MY','HK','CN','MO','TW')  
             OR c.id IN (2751,4224,...))
        AND c.id NOT IN (16351,15528,...)
    )
) t
ORDER BY t.r_value_fast_7 DESC
LIMIT 40;

更新新一下索引策略：

-- talker_character表的索引
ALTER TABLE talker_character ADD INDEX idx_status_composite (status, check_status, rec_status);

-- talker_character_stat表的索引，把排序字段放在最前面
ALTER TABLE talker_character_stat DROP INDEX idx_char_rfast;
ALTER TABLE talker_character_stat ADD INDEX idx_rvalue (r_value_fast_7 DESC);

如果数据量不是特别大，可以用临时表：

CREATE TEMPORARY TABLE tmp_characters (
    character_id bigint PRIMARY KEY
);
INSERT INTO tmp_characters
SELECT id 
FROM talker_character c
WHERE c.status = 1 
AND c.check_status = 0 
AND c.rec_status NOT IN (2,3)
AND (c.region IN ('SG','MY','HK','CN','MO','TW')  
     OR c.id IN (2751,4224,...))
AND c.id NOT IN (16351,15528,...);
SELECT cs.character_id
FROM talker_character_stat cs
INNER JOIN tmp_characters tc ON cs.character_id = tc.character_id
ORDER BY cs.r_value_fast_7 DESC
LIMIT 40;
DROP TEMPORARY TABLE IF EXISTS tmp_characters;

Answer 2

优化方法：

1. 索引优化：

   • 在 ORDER BY 所涉及的列上添加索引。
   • 如果查询有条件筛选，可以创建复合索引，包含排序列和过滤列。

2. 分区与分片：

   • 对大表进行分区，将数据分散存储，减少排序时的扫描范围。

3. 减少数据扫描：

   • 只返回必要的列，避免 SELECT *。
   • 使用 LIMIT 和 OFFSET 优化分页查询。

4. 临时表优化：

   • 使用临时表保存中间结果，减少重复计算：

     CREATE TEMPORARY TABLE temp_table AS
     SELECT col1, col2
     FROM large_table
     WHERE conditions;
     
     SELECT *
     FROM temp_table
     ORDER BY col1;

5. 执行计划分析：

   • 使用 EXPLAIN 或 EXPLAIN ANALYZE 查看查询计划，调整索引和排序逻辑。

Answer 3

看了下你的表结构，用了联合索引，根据MySQL最左匹配原则

以下联合索引

KEY `idx_char_rfast` (`character_id`, `r_value_fast_7`)

r_value_fast_7在最右边，所以索引失效，单独创建r_value_fast_7索引问题就可以解决。