MariaDB多列模糊查询优化咨询（大数据集场景）

一、利用现有联合索引减少扫描范围，消除Filesort #

当前执行计划显示存在Using filesort，原因是虽已使用idx_company_branch_status_appt_date联合索引，但过滤后的记录需先完成OR条件的模糊匹配再排序。可以调整SQL逻辑，先通过索引快速获取符合company_no和status的有序记录，再关联原表做模糊搜索，这样排序直接复用索引的顺序，避免额外排序操作：

SELECT 
    o.order_no
FROM 
    (
        SELECT order_no, appointment_date
        FROM `order`
        WHERE company_no = 297 AND status IN ('open','progress')
        ORDER BY appointment_date DESC
    ) AS filtered
JOIN `order` AS o ON filtered.order_no = o.order_no
WHERE 
    o.v_vin LIKE '%searchvalue%' 
    OR o.v_brand LIKE '%searchvalue%' 
    OR o.v_model LIKE '%searchvalue%' 
    OR o.v_license_number LIKE '%searchvalue%' 
    OR o.c_first_name LIKE '%searchvalue%' 
    OR o.c_last_name LIKE '%searchvalue%'
ORDER BY filtered.appointment_date DESC;

子查询会直接借助联合索引返回有序的order_no和appointment_date，后续关联原表仅需对筛选后的小范围记录做模糊匹配，大幅降低扫描行数。

二、用全文索引替换多字段模糊匹配 #

%xxx%格式的模糊查询无法利用普通B-tree索引，MariaDB的全文索引（Full-Text Index）可完美适配这类多字段模糊搜索场景，性能提升显著：

1. 创建联合全文索引 #

ALTER TABLE `order` ADD FULLTEXT INDEX ft_search_fields (v_vin, v_brand, v_model, v_license_number, c_first_name, c_last_name);

2. 修改SQL为全文搜索语法 #

SELECT 
    o.order_no
FROM 
    `order` AS o
WHERE 
    o.company_no = 297
    AND o.status IN ('open','progress') 
    AND MATCH(o.v_vin, o.v_brand, o.v_model, o.v_license_number, o.c_first_name, o.c_last_name)
        AGAINST ('searchvalue' IN BOOLEAN MODE)
ORDER BY 
    o.appointment_date DESC;

注意：如果涉及中文内容，需启用ngram分词插件以支持中文全文索引：

INSTALL SONAME 'ha_ngram';
ALTER TABLE `order` ADD FULLTEXT INDEX ft_search_fields (v_vin, v_brand, v_model, v_license_number, c_first_name, c_last_name) WITH PARSER ngram;

三、结合两种方案的最优写法 #

将全文索引与现有联合索引结合，进一步压缩扫描范围，达到最优性能：

SELECT 
    o.order_no
FROM 
    (
        SELECT order_no, appointment_date
        FROM `order`
        WHERE company_no = 297 AND status IN ('open','progress')
        ORDER BY appointment_date DESC
    ) AS filtered
JOIN `order` AS o ON filtered.order_no = o.order_no
WHERE 
    MATCH(o.v_vin, o.v_brand, o.v_model, o.v_license_number, o.c_first_name, o.c_last_name)
        AGAINST ('searchvalue' IN BOOLEAN MODE)
ORDER BY filtered.appointment_date DESC;

该写法既借助联合索引快速筛选并排序，又通过全文索引高效完成多字段模糊匹配，在大数据集下能显著缩短查询耗时。

四、额外优化建议 #

• 若order_no是主键，子查询返回的order_no关联原表时会是常数级别的高效查询。
• 定期执行OPTIMIZE TABLE order``，整理表碎片，提升索引使用效率。
• 始终只查询所需字段（当前SQL已做到），减少数据传输和内存占用。

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