MySQL InnoDB页面实时搜索慢查询优化技术问询

问题背景 #

页面具备实时搜索功能，支持按开发者、发行商、类型、时间等过滤与排序，但存在以下性能问题：

• 实时输入时每输入一个字符触发请求，延迟明显；仅保留开发者参数的单次查询约0.14秒，实时场景下更慢
• 分页时获取符合条件的总条目数耗时约1.97秒，且耗时随页码增加而上升

核心查询（带LIMIT） #

SELECT  games.id AS id
    FROM  games
    LEFT 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 LIKE 'de%'
                  )
    ORDER BY  games.rating DESC
    LIMIT  6

分页总数查询 #

SELECT games.id AS id FROM games LEFT 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 LIKE 'de%') ORDER BY games.rating

表结构 #

CREATE TABLE games ( 
  id int(10) unsigned NOT NULL AUTO_INCREMENT, 
  rating double NOT NULL DEFAULT 0, 
  date date DEFAULT NULL, 
  date_type int(1) NOT NULL DEFAULT 1, 
  img varchar(500) DEFAULT NULL, 
  img_type int(10) NOT NULL, 
  url varchar(100) NOT NULL, 
  PRIMARY KEY (`id`), 
  KEY rating_index (`rating`) USING BTREE, 
  KEY date_index (`date`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=326678 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci 

CREATE TABLE games_titles ( 
  game_id int(50) NOT NULL, 
  title varchar(150) DEFAULT NULL, 
  lang int(10) NOT NULL DEFAULT 1, 
  KEY game_id_index (`game_id`) USING BTREE, 
  KEY lang (`lang`,`title`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci 

CREATE TABLE games_devs ( 
  game_id int(50) NOT NULL, 
  dev varchar(150) NOT NULL, 
  PRIMARY KEY (`game_id`,`dev`), 
  KEY dev (`dev`,`game_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci 

优化方案 #

1. 前端防抖处理 #

• 给输入框添加**防抖（Debounce）**逻辑，设置300-500毫秒延迟，仅在用户停止输入后触发请求，避免频繁发送查询
• 限制触发请求的最小输入长度（比如2个字符），减少输入"d"这类匹配数据过多的无效查询

2. 数据库索引优化 #

• 优化games_titles索引：创建联合索引(lang, game_id)，替代现有game_id_index和单独的lang索引，让查询直接通过索引过滤语言并关联游戏ID，减少回表开销
• 更新索引统计信息：执行ANALYZE TABLE games_devs;，确保优化器能准确评估索引使用成本
• 优化排序索引：给games表创建(rating, id)联合索引，让排序时直接获取ID，减少后续关联的行扫描

3. 查询语句改写 #

• 将LEFT JOIN games_titles改为INNER JOIN：WHERE games_titles.lang=1会过滤无对应语言标题的游戏，LEFT JOIN实际等价于INNER JOIN，改写后减少不必要的行处理
• 用JOIN替代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
WHERE games_titles.lang = 1
  AND games_devs.dev LIKE 'de%'
ORDER BY games.rating DESC
LIMIT 6

• 分页总数查询简化：无需返回所有ID，直接统计总数减少数据传输：

SELECT COUNT(DISTINCT games.id) AS total
FROM games
INNER JOIN games_titles ON games.id = games_titles.game_id
INNER JOIN games_devs ON games.id = games_devs.game_id
WHERE games_titles.lang = 1
  AND games_devs.dev LIKE 'de%'

4. LIKE运算符替代方案 #

• 前缀匹配保留索引：当前dev LIKE 'de%'是前缀匹配，可继续使用现有索引，无需替换
• 全文索引方案：给games_devs的dev字段创建全文索引，适合模糊匹配场景，示例：

-- 创建全文索引
ALTER TABLE games_devs ADD FULLTEXT INDEX ft_dev (dev);

-- 查询语句
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
WHERE games_titles.lang = 1
  AND MATCH(games_devs.dev) AGAINST('+de*' IN BOOLEAN MODE)
ORDER BY games.rating DESC
LIMIT 6

• 预生成前缀字典：提前将开发者名称的1-3字符前缀存入单独表，搜索时先匹配前缀表再关联游戏，缩小扫描范围

5. 分页性能优化 #

• 基于游标的分页：避免大OFFSET导致的全表扫描，用上一页最后一条记录的rating和id作为条件，示例：

SELECT 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
WHERE games_titles.lang = 1
  AND games_devs.dev LIKE 'de%'
  AND (games.rating < :last_rating OR (games.rating = :last_rating AND games.id < :last_id))
ORDER BY games.rating DESC, games.id DESC
LIMIT 6

• 缓存总数结果：对相同搜索条件的总数查询结果设置5分钟左右的缓存，避免重复计算

6. 进阶优化 #

• 热门搜索缓存：将高频搜索条件的结果缓存到Redis等内存数据库，实时搜索优先返回缓存数据
• 读写分离：将查询请求分流到只读从库，减轻主库压力
• 数据库分区：若games表数据量极大，可按rating或date分区，减少查询扫描的数据范围

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