如何在本地实现单字符搜索时100ms内从250k条记录取10k数据？

问题背景 #

本地数据库存有250k条记录，当前采用的SQL方案包含多索引创建与UNION并行查询逻辑，但单字符搜索时无法将10k条记录的获取时间控制在100ms以内，现有表结构、索引及查询语句如下：

现有表结构与索引 #

create database task250;
use  task250;
CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    department VARCHAR(50)
);

CREATE TABLE Subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(100)
);

CREATE TABLE Marks (
    student_id INT,
    subject_id INT,
    marks DECIMAL(5, 2),  
    PRIMARY KEY (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(id)
);

-- 存在重复创建的冗余索引
CREATE INDEX idx_firstname ON Students(firstname); 
CREATE INDEX idx_subject_name ON Subjects(subject_name);
CREATE INDEX idx_department ON Students(department);
CREATE INDEX idx_firstname ON Students(firstname); 
CREATE INDEX idx_subject_name ON Subjects(subject_name);
CREATE INDEX idx_department ON Students(department);

现有查询语句 #

SET @searchKey = 'Mana%';

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.department LIKE @searchKey
    LIMIT 10000
) AS dept_results

UNION all

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.firstname LIKE @searchKey
    LIMIT 10000
) AS name_results

UNION all

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE sub.subject_name LIKE @searchKey
    LIMIT 1000
) AS subject_results

ORDER BY 
    CASE 
        WHEN firstname LIKE @searchKey THEN 1   -- Names starting with searchKey first
        WHEN firstname LIKE CONCAT('%', @searchKey, '%') THEN 2  -- Names containing searchKey anywhere next
        ELSE 3
    END,
    firstname 
LIMIT 10000;

优化方案 #

1. 清理冗余索引，构建覆盖索引减少回表开销 #

• 先删除重复创建的索引，避免写入和维护开销：

  DROP INDEX idx_firstname ON Students;
  DROP INDEX idx_subject_name ON Subjects;
  DROP INDEX idx_department ON Students;
  

• 创建覆盖索引，让查询直接从索引获取所需字段，避免回表查询：

  -- 覆盖department查询所需的所有Students字段
  CREATE INDEX idx_dept_cover ON Students(department, id, firstname, lastname);
  -- 覆盖firstname查询所需的所有Students字段
  CREATE INDEX idx_firstname_cover ON Students(firstname, id, lastname, department);
  -- 覆盖subject_name查询所需的Subjects字段
  CREATE INDEX idx_subject_cover ON Subjects(subject_name, id);
  

2. 重构查询逻辑，缩小排序数据集 #

现有查询先合并所有结果再排序，单字符搜索时结果集可能远大于10k，排序开销极高。调整为子查询先按优先级取数，再合并排序：

SET @searchKey = 'M%';
SET @innerLimit = 3500; -- 每个子查询取略多于10000/3的数量，避免某类结果过少导致总结果不足

SELECT * FROM (
    -- 优先级1：firstname前缀匹配
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks, 1 AS priority
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.firstname LIKE @searchKey
    ORDER BY s.firstname
    LIMIT @innerLimit

    UNION ALL

    -- 优先级2：firstname包含匹配（排除已在优先级1的记录）
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks, 2 AS priority
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.firstname LIKE CONCAT('%', @searchKey, '%')
      AND s.firstname NOT LIKE @searchKey
    ORDER BY s.firstname
    LIMIT @innerLimit

    UNION ALL

    -- 优先级3：department前缀匹配
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks, 3 AS priority
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.department LIKE @searchKey
    ORDER BY s.firstname
    LIMIT @innerLimit

    UNION ALL

    -- 优先级4：subject_name前缀匹配
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks, 4 AS priority
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE sub.subject_name LIKE @searchKey
    ORDER BY s.firstname
    LIMIT @innerLimit
) AS combined
ORDER BY priority, firstname
LIMIT 10000;

这种方式将排序的数据集从数万条压缩到14000条以内，大幅降低排序耗时。

3. 用全文索引替代普通LIKE查询（MySQL 8.0+适用） #

单字符模糊查询下，普通前缀索引扫描范围极大，全文索引效率提升明显：

• 创建全文索引：

  ALTER TABLE Students ADD FULLTEXT INDEX ft_students(firstname, department);
  ALTER TABLE Subjects ADD FULLTEXT INDEX ft_subjects(subject_name);
  

• 替换LIKE为全文查询：

  -- 匹配firstname包含搜索词的记录
  WHERE MATCH(s.firstname) AGAINST(@searchKey IN BOOLEAN MODE)
  -- 匹配department包含搜索词的记录
  WHERE MATCH(s.department) AGAINST(@searchKey IN BOOLEAN MODE)
  

4. 数据库配置优化 #

• 调整innodb_buffer_pool_size为服务器内存的50%-70%，确保热数据全部缓存到内存，避免磁盘IO。
• 对高频搜索词的结果做应用层缓存，直接返回缓存结果跳过数据库查询。

内容的提问来源于stack exchange，提问作者Shameer Ali