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

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

阅读 3k
avatarAI BotBETA
要优化带有排序的复杂SQL查询速度,你可以考虑以下几个策略:

1. **索引优化**:
   - 确保`talker_character`表的`status`、`check_status`、`rec_status`、`region`和`id`字段上有适当的索引。
   - 确保`talker_character_stat`表的`character_id`和`r_value_fast_7`字段上有索引。
   - 由于查询中使用了`IN`和`NOT IN`子句,考虑在`talker_character`表的`id`字段上创建覆盖索引(covering index),这可以加速这些子句的查找。

2. **查询重写**:
   - 尝试将`OR`条件拆分为多个独立的查询,然后使用`UNION`合并结果,这有时可以提高查询性能,特别是当`OR`条件中的一部分能够显著减少结果集大小时。
   - 检查是否可以通过调整表结构或查询逻辑来减少`LEFT JOIN`和`INNER JOIN`的使用,或者确保这些连接是必要的,并且连接条件上有索引。

3. **限制结果集大小**:
   - 尽管你已经使用了`LIMIT`子句,但排序操作仍然可能需要在整个结果集上进行。考虑是否可以通过其他方式(如增加更多的过滤条件)来进一步限制需要排序的数据量。

4. **硬件和配置**:
   - 确保数据库服务器的硬件资源(如CPU、内存和磁盘I/O)足够支持复杂的查询。
   - 调整数据库的配置参数,如查询缓存、连接池大小等,以优化性能。

5. **分析执行计划**:
   - 使用`EXPLAIN`或类似的工具来分析查询的执行计划,找出性能瓶颈。
   - 根据执行计划的结果,调整索引、查询结构或数据库配置。

6. **考虑使用物化视图**:
   - 如果查询结果的变化不是非常频繁,可以考虑使用物化视图来存储排序后的结果,然后定期更新这个视图。

7. **数据库分区**:
   - 如果表非常大,考虑使用数据库分区技术来将表分成更小的、更易于管理的部分,这可以加速查询和排序操作。

通过上述方法,你应该能够显著提高带有排序的复杂SQL查询的性能。不过,请注意,每个数据库系统和每个查询的具体情况都可能有所不同,因此可能需要尝试多种方法来找到最佳的优化策略。
3 个回答

先用子查询找到 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;

优化方法:

  1. 索引优化:

    • ORDER BY 所涉及的列上添加索引。
    • 如果查询有条件筛选,可以创建复合索引,包含排序列和过滤列。
  2. 分区与分片:

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

    • 只返回必要的列,避免 SELECT *
    • 使用 LIMITOFFSET 优化分页查询。
  4. 临时表优化:

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

      CREATE TEMPORARY TABLE temp_table AS
      SELECT col1, col2
      FROM large_table
      WHERE conditions;
      
      SELECT *
      FROM temp_table
      ORDER BY col1;
  5. 执行计划分析:

    • 使用 EXPLAINEXPLAIN ANALYZE 查看查询计划,调整索引和排序逻辑。

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

以下联合索引

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

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

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