MariaDB：带FULLTEXT索引的mediumtext按datetime排序过慢如何优化？

问题原因 #

你的查询在添加ORDER BY datetime DESC后变慢，核心原因是：MySQL先通过全文索引匹配到符合条件的行，随后需要对这些行执行文件排序（filesort）。当匹配结果集较大时，磁盘排序的IO开销会急剧上升，导致查询耗时剧增。而你尝试创建(text, datetime)联合索引失败，是因为TEXT/BLOB类型字段无法直接作为普通联合索引的前缀（指定长度的方式对全文检索场景无意义）。

可行优化方案 #

1. 子查询+主键关联优化

先通过全文索引快速获取匹配行的主键，再通过主键关联原表并利用datetime索引排序，避免对大结果集直接排序：

SELECT d.*
FROM data d
INNER JOIN (
    -- 先通过全文索引拿到匹配的主键
    SELECT `primary` 
    FROM data 
    WHERE MATCH(text) AGAINST('keyword' IN NATURAL LANGUAGE MODE)
) AS matched ON d.`primary` = matched.`primary`
ORDER BY d.datetime DESC;

原理：子查询仅返回主键（数据量极小），主查询关联时可借助datetime索引完成排序，减少排序的数据量级。

2. 辅助表优化（适合频繁执行此类查询的场景）

创建一个仅存储主键和datetime的辅助表，并建立(datetime DESC, primary)的联合索引，让排序操作直接利用索引完成：

• 创建辅助表：

CREATE TABLE data_aux (
    `primary` INT PRIMARY KEY,
    datetime DATETIME,
    INDEX idx_dt_pk (datetime DESC, `primary`)
);

• 同步初始数据：

INSERT INTO data_aux (`primary`, datetime) SELECT `primary`, datetime FROM data;

• 维护数据一致性：通过触发器实现主表数据增删改时自动同步辅助表：

-- 新增触发器
DELIMITER //
CREATE TRIGGER trg_data_after_insert
AFTER INSERT ON data
FOR EACH ROW
BEGIN
    INSERT INTO data_aux (`primary`, datetime) VALUES (NEW.`primary`, NEW.datetime);
END //
DELIMITER ;

-- 更新触发器
DELIMITER //
CREATE TRIGGER trg_data_after_update
AFTER UPDATE ON data
FOR EACH ROW
BEGIN
    UPDATE data_aux SET datetime = NEW.datetime WHERE `primary` = NEW.`primary`;
END //
DELIMITER ;

-- 删除触发器
DELIMITER //
CREATE TRIGGER trg_data_after_delete
AFTER DELETE ON data
FOR EACH ROW
BEGIN
    DELETE FROM data_aux WHERE `primary` = OLD.`primary`;
END //
DELIMITER ;

• 优化后的查询语句：

SELECT d.*
FROM data_aux a
INNER JOIN data d ON a.`primary` = d.`primary`
WHERE MATCH(d.text) AGAINST('keyword' IN NATURAL LANGUAGE MODE)
ORDER BY a.datetime DESC;

原理：辅助表的联合索引idx_dt_pk直接支持按datetime降序排序，MySQL无需再做文件排序，大幅提升性能。

3. 限制返回结果行数（适合仅需前N条数据的场景）

如果业务允许只返回部分结果，添加LIMIT可以大幅减少排序的数据量：

SELECT *
FROM data
WHERE MATCH(text) AGAINST('keyword' IN NATURAL LANGUAGE MODE)
ORDER BY datetime DESC
LIMIT 100; -- 根据业务需求调整行数

原理：仅对指定数量的结果排序，避免全量结果集的磁盘排序操作。

4. 按datetime分区（适合时间范围明确的查询场景）

对data表按datetime字段分区，缩小查询和排序的数据范围：

-- 按月份分区（示例，可根据数据分布调整分区粒度）
ALTER TABLE data PARTITION BY RANGE (TO_DAYS(datetime)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    -- 后续分区依次类推
);

查询时如果可以指定时间范围，MySQL会仅扫描对应分区，减少需要排序的数据量：

SELECT *
FROM data
WHERE MATCH(text) AGAINST('keyword' IN NATURAL LANGUAGE MODE)
  AND datetime >= '2024-01-01' AND datetime < '2024-02-01'
ORDER BY datetime DESC;

5. 调整MySQL配置参数（系统层面优化）

增加排序相关的内存缓冲区，避免磁盘排序：

• 修改my.cnf（或my.ini）配置：

sort_buffer_size = 2M -- 根据服务器内存调整，建议不超过8M
read_rnd_buffer_size = 1M

• 重启MySQL服务生效。
  原理：让排序操作尽可能在内存中完成，减少磁盘IO开销。

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