全文索引需匹配字段类型、查询语法及中文解析器，前缀索引仅优化前缀匹配，instr/locate不提升性能，多表模糊join应拆解为应用层过滤。
全文索引不是“加了就快”，得看字段内容和查询方式
MySQL 的 FULLTEXT 索引只对 CHAR、VARCHAR、TEXT 类型生效，且要求表引擎是 InnoDB 或 MyISAM（8.0+ 推荐 InnoDB）。它不支持单字符或极短词（默认最小长度为 4），比如搜 “李” 或 “AI” 会直接被忽略——除非你改配置 innodb_ft_min_token_size 并重建索引。
更关键的是：全文搜索必须用 MATCH() AGAINST()，不能混用 LIKE 或 =。例如：
SELECT * FROM t_users WHERE MATCH(real_name) AGAINST('张三' IN NATURAL LANGUAGE MODE);
如果写成 WHERE real_name LIKE '%张三%'，哪怕该列有全文索引也完全不生效。
常见误判点：
-
AGAINST('张*')这种带星号的写法仅在布尔模式下有效，自然语言模式下会被当作普通词处理 - 中文需搭配
ngram解析器，建索引时要显式指定：CREATE FULLTEXT INDEX idx_real_name ON t_users(real_name) WITH PARSER ngram - 停用词（如“的”“了”“在”）默认被过滤，查不到结果别急着删索引，先查
INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
前缀索引能救 LIKE 'xxx%'，但对 '%xxx' 和 '%xxx%' 没用
如果你的模糊查询固定以某段文字开头（比如查“手机号以 138 开头”或“用户名以 admin 开头”），给字段建前缀索引是最轻量有效的办法：
CREATE INDEX idx_mobile_prefix ON t_users(mobile(4));
这样 WHERE mobile LIKE '138%' 就能走索引；但 LIKE '%381' 或 LIKE '%38%' 依然全表扫描。
注意三个实际限制：
- 前缀长度不能瞎设：太短（如
mobile(2)）会导致大量重复值，索引区分度低；太长（如mobile(11)）又浪费空间。建议用SELECT COUNT(DISTINCT LEFT(mobile, N)) / COUNT(*) FROM t_users测区分率，选第一个 >0.95 的N -
VARCHAR列建前缀索引后，ORDER BY或GROUP BY无法利用该索引做排序/分组 - 联合索引里如果把前缀列放后面（如
(status, username(10))），那LIKE 'xxx%'在username上依然用不上这个索引
INSTR() 和 LOCATE() 不提速，只是换种写法
有人看到 INSTR(username, 'abc') > 0 就以为能绕过 LIKE '%abc%' 的性能问题——其实不会。这两个函数和 LIKE '%...%' 一样，都是逐行计算字符串位置，执行计划里照样显示 type: ALL（全表扫描）。
它们唯一价值是语义更明确（比如想表达“包含子串”，比 LIKE '%...%' 更直白），或者配合其他条件做逻辑组合。例如：
WHERE status = 1 AND INSTR(real_name, '王') > 0
这时如果 status 有索引，至少能先过滤出活跃用户再做字符串扫描，比纯 LIKE 略好一点。
但别指望靠它优化大表——数据量一过百万，响应时间就明显拉长。
真正要命的是多表 JOIN + LIKE，先拆开查再内存合并
你提到“学生表和四张表联合模糊查询”，这种场景下，哪怕每张表都加了索引，JOIN 后的数据集膨胀 + LIKE '%...%' 会直接让优化器放弃所有索引，退化成嵌套循环+全表扫描。
可行解法只有两个方向：
- 把模糊条件尽量推到驱动表（即
FROM后的第一张表），例如FROM t_students s WHERE s.real_name LIKE '李%'，再JOIN其他维度表 - 用应用层分两步：先用高效条件（如主键范围、状态、时间）查出 ID 列表，再用这些 ID 去查关联表，最后在代码里用
String.contains()或正则做过滤
后者看似绕，但在学生管理系统这类读多写少、并发不高的业务里，实测比硬扛 SQL 模糊 JOIN 快 5–10 倍，而且可控、可加缓存、可降级。
最易被忽略的一点：LIKE 查询的性能拐点不在百万级，而常出现在几十万行 + 多字段 OR 条件组合时——这时候别调优 SQL，该上 Elasticsearch 就上。
