百万级游戏库多条件搜索慢SQL优化求助（含LEFT JOIN/子查询）

一、紧急索引优化（立竿见影） #

当前EXISTS子查询触发全表扫描，核心是索引设计不符合查询逻辑，需调整以下索引：

• games_devs表：创建复合索引 (dev, game_id)。查询先匹配dev IN (...)，再关联game_id，复合索引能直接定位符合条件的game_id集合，避免全表遍历。
• games_publishers表：创建复合索引 (publisher, game_id)，逻辑同上。
• games_titles表：创建复合索引 (lang, title, game_id)。该索引同时覆盖过滤条件lang=1、排序字段title和关联字段game_id，查询时可直接从索引中获取所需数据并完成排序，彻底消除临时表排序开销。

二、SQL语句改写优化 #

1. 替换LEFT JOIN为INNER JOIN #

原SQL中LEFT JOIN games_titles搭配WHERE games_titles.lang=1，实际等同于INNER JOIN（会过滤掉无对应title的游戏），改为INNER JOIN可减少数据库不必要的行处理：

SELECT DISTINCT games.id AS id 
FROM games 
INNER JOIN games_titles ON games.id = games_titles.game_id 
WHERE games_titles.lang=1
  AND EXISTS (SELECT 1 FROM games_devs WHERE games_devs.game_id = games.id AND games_devs.dev IN (...))
  AND EXISTS (SELECT 1 FROM games_publishers WHERE games_publishers.game_id = games.id AND games_publishers.publisher IN (...))
ORDER BY games_titles.title

2. 可选：用JOIN替代EXISTS（视数据分布调整） #

若游戏对应的开发商/发行商数量较少，可改用INNER JOIN+DISTINCT替代EXISTS，部分场景下性能更优：

SELECT DISTINCT games.id AS id 
FROM games 
INNER JOIN games_titles ON games.id = games_titles.game_id 
INNER JOIN games_devs ON games.id = games_devs.game_id 
INNER JOIN games_publishers ON games.id = games_publishers.game_id 
WHERE games_titles.lang=1
  AND games_devs.dev IN (...)
  AND games_publishers.publisher IN (...)
ORDER BY games_titles.title

三、辅助表重构（适配百万级数据） #

之前的games_search辅助表未发挥作用，核心是未做预聚合和索引优化，建议重构为宽表+多值索引：

1. 创建预聚合宽表 #

以MySQL 8.0为例，将游戏的多语言标题（仅保留常用lang=1）、开发商、发行商预聚合到一张表：

CREATE TABLE games_search (
  game_id INT PRIMARY KEY COMMENT '游戏ID',
  title VARCHAR(255) NOT NULL COMMENT 'lang=1的游戏标题',
  devs JSON NOT NULL COMMENT '开发商ID/名称数组',
  publishers JSON NOT NULL COMMENT '发行商ID/名称数组',
  INDEX idx_title (title),
  -- 针对JSON数组创建索引，支持快速匹配IN条件
  INDEX idx_devs ((CAST(devs AS UNSIGNED ARRAY))),
  INDEX idx_publishers ((CAST(publishers AS UNSIGNED ARRAY)))
);

2. 查询改写 #

直接从宽表查询，避免多表关联：

SELECT game_id AS id 
FROM games_search 
WHERE JSON_CONTAINS(devs, '["dev_id_1", "dev_id_2"]')
  AND JSON_CONTAINS(publishers, '["pub_id_1", "pub_id_2"]')
ORDER BY title;

3. 数据同步 #

通过触发器或定时任务（如crontab+脚本）将games、games_titles、games_devs、games_publishers的变更同步到games_search表，保证数据一致性。

四、架构升级（应对百万级及以上数据） #

当数据量突破百万后，数据库单表查询性能瓶颈会凸显，建议引入搜索引擎：

• 选型：Elasticsearch（功能全面，适合复杂搜索场景）或Meilisearch（轻量易用，适合快速部署）。
• 落地步骤：
  1. 将游戏数据（ID、多语言标题、开发商、发行商、标签等）同步到搜索引擎索引。
  2. 搜索请求直接转发到搜索引擎，利用其高效的倒排索引完成多条件过滤、排序、分页。
  3. 若需关联其他业务数据，可从搜索引擎获取游戏ID后，再查询数据库补充细节。
• 优势：搜索引擎天生适合高并发多维度搜索，支持毫秒级响应，且横向扩展能力远优于关系型数据库。

五、数据库配置调优 #

调整MySQL/PostgreSQL核心参数，进一步提升性能：

• innodb_buffer_pool_size：设置为物理内存的50%-70%，让更多数据缓存到内存，减少磁盘IO。
• sort_buffer_size：适当调大（如8M），避免排序时使用临时文件（磁盘排序）。
• read_rnd_buffer_size：调大该参数，优化排序后的数据读取性能。

内容的提问来源于stack exchange，提问作者Mark Uuy