MySQL索引优化难题：排序字段引发查询性能两极分化

当对主键（PK）以外的字段使用ORDER BY时，部分查询速度变慢。添加索引后，数值字段排序的查询速度提升，但文本字段排序的查询耗时从约20秒增至2分钟以上。为文本字段添加索引无效，EXPLAIN显示这类查询会使用数值字段的索引。现寻求一种方案，既能利用索引提升各类（数值或文本）字段排序的查询速度，至少保证数值字段的优化效果，又不会拖慢文本字段排序的重要查询。

正在开发与本地MySQL数据库交互的后端，存储科研论文元数据，测试时发现部分查询耗时过长。数据库包含以下表：

+-------------------------+
| Tables_in_padaone       |
+-------------------------+
| classification_2ndLay   |
| classifications_1stLay  |
| curationStatus_User     |
| geneIDsPerPMIDs_Counts  |
| geneIDs_PMIDs           |
| geneIDs_taxInfo_AccNumb |
| metadataPub             |
| padaone_query_cache     |
| taxID_taxName           |
| taxIDsPerPMIDs_Counts   |
| taxPath                 |
+-------------------------+

padaone_query_cache是查询主表，整合其他表多列数据，结构如下（SHOW CREATE TABLE padaone_query_cache输出）：

CREATE TABLE `padaone_query_cache` (
  `pmid` int(11) NOT NULL,
  `title` varchar(2000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `abstract` varchar(10000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `year_pub` int(11) DEFAULT NULL,
  `last_author` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `citations` int(11) DEFAULT NULL,
  `journal` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `language` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `first_layer_probability` decimal(8,7) NOT NULL,
  `second_layer_probability` decimal(8,7) NOT NULL,
  `taxon_names` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`pmid`),
  CONSTRAINT `padaone_query_cache_ibfk_1` FOREIGN KEY (`pmid`) REFERENCES `metadataPub` (`PMID`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE = latin1_swedish_ci;

该表结构与前端结果表一致，比JOIN查询更快，数据量不大且存储不受限，数据变更少，作为中间表可行。后端部分查询分两步：先查询关联表获取pmid集合，再用IN子句查询主表获取符合过滤条件的论文，但部分查询耗时超20秒，影响用户体验。

无索引时的查询示例 #

以下查询速度较快（按主键pmid排序）：

SELECT
    pmid
FROM
    padaone_query_cache
WHERE
    (((first_layer_probability BETWEEN 0.5 AND 1.0)
        AND (second_layer_probability BETWEEN 0.0 AND 1.0))
        AND (title LIKE "%mouse%"
            OR title LIKE "%vaccine%"
            OR abstract LIKE "%mouse%"
            OR abstract LIKE "%vaccine%"))
ORDER BY
    pmid DESC
LIMIT 20;

耗时不到1秒。但修改ORDER BY字段后，查询速度骤降：

SELECT
    pmid
FROM
    padaone_query_cache
WHERE
    (((first_layer_probability BETWEEN 0.5 AND 1.0)
        AND (second_layer_probability BETWEEN 0.0 AND 1.0))
        AND (title LIKE "%mouse%"
            OR title LIKE "%vaccine%"
            OR abstract LIKE "%mouse%"
            OR abstract LIKE "%vaccine%"))
ORDER BY
    year_pub DESC
LIMIT 20;

仅排序字段不同，耗时却超20秒。由于year_pub等字段非唯一，分页会出现结果不一致问题，因此添加pmid作为排序平局决胜字段，但耗时仍约20秒。

复合查询（带子查询和JOIN）按pmid排序时速度快，但按first_layer_probability排序时耗时约12秒，按文本字段排序时耗时更久。

确认ORDER BY字段是核心问题后，添加数值字段索引：

CREATE INDEX first_layer_probability_index ON padaone_query_cache (first_layer_probability, pmid, second_layer_probability);

CREATE INDEX second_layer_probability_index ON padaone_query_cache (second_layer_probability, pmid, first_layer_probability);

此类索引使按概率字段排序的查询耗时降至1秒以内，但按文本字段（如title）排序的查询耗时增至2分钟以上，EXPLAIN显示其使用了数值字段的索引。

为其他数值字段添加索引有效，但为文本字段添加索引后，查询仍不使用该索引，速度比无索引时更慢。复合查询也存在同样问题：按数值字段排序快，按文本字段排序极慢。

补充信息 #

索引定义及表信息 #

创建索引的语句：

-- 概率层索引，对对应排序查询有效
CREATE INDEX first_layer_probability_index ON padaone_query_cache (first_layer_probability, pmid, second_layer_probability);
CREATE INDEX second_layer_probability_index ON padaone_query_cache (second_layer_probability, pmid, first_layer_probability);

-- 文本字段索引，无效果
CREATE INDEX title_index ON padaone_query_cache (title(35), pmid, first_layer_probability, second_layer_probability);

-- 其他数值字段索引，对对应排序查询有效
CREATE INDEX year_pub_index ON padaone_query_cache (year_pub, pmid, first_layer_probability, second_layer_probability);

SHOW INDEX输出见原文，尝试过多种索引组合均无效，最终重置索引后重新测试。

其他关联表结构 #

geneIDs_PMIDs和geneIDs_taxInfo_AccNumb的SHOW CREATE TABLE输出见原文。

其他说明 #

无法聘请DBA，本人是巴西公立大学遗传学博士生，零预算开发；考虑过用FULLTEXT替代LIKE，但后端使用Rust的Diesel ORM，不确定是否支持全文查询；数据库版本为MariaDB 10.11.2；padaone_query_cache表共525047行。

求问问题根源、索引使用误区，以及兼顾各类排序查询性能的优化方案。

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