MariaDB 10.3双表全文搜索查询缓慢优化方案咨询

• 首先修正原SQL的逻辑错误：原WHERE条件未添加括号明确优先级，由于SQL中AND优先级高于OR，实际执行逻辑和你预期的「匹配文章字段 或 匹配标签字段」完全不符，会扫描大量不符合a.type = 'article'和approve != '0'的无效记录，这是性能劣化的核心原因之一。
• 替换LEFT JOIN + OR的低效率写法为分离子查询+UNION模式：分别查询文章字段匹配、标签匹配的结果后合并去重，避免JOIN产生的笛卡尔积和全文索引失效问题，优化后的SQL示例如下：

SELECT * FROM (
    -- 子查询1：匹配文章本身的全文字段
    SELECT 
        a.approve,a.aid,a.sid,a.articleFormat,title,cachedTitle,subtitle,body,abstract,
        a.linkUrl,a.byline,a.poster,a.allowComments,a.allowRatings,a.gmt,a.lastModified,
        a.modifier,a.type,UNIX_TIMESTAMP(a.gmt) AS DATETIME,a.commentCount,a.ratingCount,
        a.ratingDetails,MATCH(a.body, a.title, a.subtitle, a.abstract) AGAINST('OS X' IN NATURAL LANGUAGE MODE) AS relevanceScore,
        a.readCount
    FROM uninet_articles a
    WHERE MATCH(a.body, a.title, a.subtitle, a.abstract) AGAINST('OS X' IN NATURAL LANGUAGE MODE)
        AND a.type = 'article' AND a.approve != '0'
    UNION DISTINCT
    -- 子查询2：匹配标签关联的文章
    SELECT 
        a.approve,a.aid,a.sid,a.articleFormat,title,cachedTitle,subtitle,body,abstract,
        a.linkUrl,a.byline,a.poster,a.allowComments,a.allowRatings,a.gmt,a.lastModified,
        a.modifier,a.type,UNIX_TIMESTAMP(a.gmt) AS DATETIME,a.commentCount,a.ratingCount,
        a.ratingDetails,MATCH(tags.name) AGAINST('OS X' IN NATURAL LANGUAGE MODE) AS relevanceScore,
        a.readCount
    FROM uninet_tags tags
    INNER JOIN uninet_articles a ON tags.paid = a.aid
    WHERE MATCH(tags.name) AGAINST('OS X' IN NATURAL LANGUAGE MODE)
        AND a.type = 'article' AND a.approve != '0'
) t
ORDER BY approve DESC, gmt DESC, relevanceScore DESC
LIMIT 0,10;

• 新增联合索引：给uninet_articles表新增联合索引idx_type_approve_gmt(type, approve, gmt)，可以直接通过索引完成基础条件过滤，避免回表扫描，同时匹配排序逻辑，避免产生文件排序开销。
• 长期扩容适配：如果后续单表数据量超过10万条，建议新增全文搜索聚合冗余字段，把文章的标题、摘要、正文、关联标签名全部冗余到uninet_articles表的单独fulltext_content字段，统一创建全文索引，直接单表查询即可，性能提升幅度可达数倍。

• 全文索引参数调整：
  • 将innodb_ft_min_token_size设置为2（默认值为4），适配短词、中文搜索场景，修改后需要重建全文索引生效。
  • 调大innodb_ft_cache_size到16M（默认值为8M），提升全文索引查询的缓存命中率。
  • 如无停用词过滤需求，可开启innodb_ft_enable_stopword = OFF，避免常用词被过滤的同时降低查询计算开销。
• 内存分配优化：将innodb_buffer_pool_size设置为服务器可用内存的50%~70%，InnoDB的全文索引数据会自动纳入缓冲池管理，高频查询场景下性能会反超MyISAM。
• 存储格式优化：针对文章这类读多写少的业务场景，可以将表的行格式设置为ROW_FORMAT=COMPRESSED，减少磁盘IO开销，查询性能可提升10%~20%。
• 排序参数调优：InnoDB默认排序效率低于MyISAM的核心原因是临时表开销，将sort_buffer_size调整为2M、read_rnd_buffer_size调整为4M，针对你这类LIMIT 10的小结果集排序场景，性能可以追平甚至超过MyISAM。

内容的提问来源于stack exchange，提问作者Timothy R. Butler