mysql动态sql是否影响索引使用_mysql预处理语句优化

动态SQL中OR #{xxx} IS NULL使索引失效，因破坏SARGable特性致优化器弃用索引；应改用动态拼接纯净条件，并配合合理索引设计与查询拆分。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

动态 SQL 中的 OR #{xxx} IS NULL 为什么让索引失效

很多开发者会困惑：明明字段上有索引，为什么查询还是慢如蜗牛？问题往往就出在 OR #{xxx} IS NULL 这种写法上。准确地说，它并非“一定不走索引”，而是MySQL优化器在绝大多数实际业务场景下，会倾向于放弃使用索引，转而进行全表扫描。其根本原因在于，这种写法彻底破坏了查询条件的“SARGable”特性。

什么是SARGable？简单理解，就是你的查询条件要能让数据库引擎直接用索引字段去和常量进行比较（等值或范围）。一旦你混入了 OR IS NULL，优化器就懵了：它无法预先判断这个分支的真假，整个WHERE子句变得“不可预估”。结果就是，EXPLAIN 结果里刺眼地显示着 type: ALL 和 key: NULL，索引形同虚设。

• 虽然MySQL 5.7及以上版本支持 index_merge 优化（尝试合并多个索引的结果），但它要求每个 OR 分支都对应独立的、高选择性的索引。现实是，当条件里混合了普通等值查询和 IS NULL 判断时，基本不会触发此优化。
• #{startTime} IS NULL 这类动态判断，会让优化器认为条件存在不确定性，从而放弃整个WHERE子句的索引路径评估，选择最“保险”的全表扫描。
• 退一步讲，即使涉及的每个字段都单独建立了索引，这种分散的联合筛选效率，也远不如一个精心设计的联合索引来得高效。

MyBatis 动态 SQL 的安全写法：用 替代 OR IS NULL

那么，正确的姿势是什么？答案是：让最终生成的SQL语句本身就是“干净”的。所谓干净，是指没有冗余的 OR 分支，没有运行时才能确定的逻辑，每个条件都是明确、可被优化器直接评估的。这才是保证索引命中率的关键。

这种写法在后台管理系统、数据导出接口或多条件筛选场景中尤为常见。下面是一个标准的示例：

  
    AND user_id = #{userId}
  
  
    AND status = #{status}
  
  
    AND create_time >= #{startTime}
  

• 这样一来，当 userId 和 startTime 有值而 status 为空时，生成的SQL就是纯粹的 WHERE user_id = ? AND create_time >= ?。优化器可以毫无障碍地评估这两个条件，并选择最合适的索引。
• 这里有个细节需要注意：判空逻辑要严格。对于字符串类型，使用 test="userId != null and userId != ''" 比单纯的 != null 更稳妥，能有效防止空字符串参数误触发条件。
• 另外，切忌在 的test属性中嵌入函数调用，例如 test="startTime != null and DATE(startTime) == '2024-01-01'"。这会导致MyBatis将函数表达式拼接到SQL中，直接导致索引失效。

预处理语句（PREPARE / EXECUTE）本身不解决索引问题

这里存在一个普遍的误解：有人认为只要使用了预编译语句，数据库就会自动进行性能优化。其实不然。预处理语句的核心价值在于防止SQL注入、减少重复解析SQL的开销，但最终的执行计划，仍然由拼接完成后的那条“最终SQL”来决定。

关于性能，还有一个容易忽略的点：预处理语句会在首次执行时生成执行计划并缓存。但如果每次执行的参数组合差异巨大（比如一次查询只返回几条记录，另一次却要扫描大部分数据），MySQL可能会错误地复用一个次优的执行计划，导致性能不升反降。

• 所以，别指望 PREPARE ...; EXECUTE ... 这套机制能挽救 OR IS NULL 导致的索引失效问题。症结在于SQL结构本身，而非参数传递方式。
• 如果必须在存储过程等场景下动态构建SQL，优先考虑使用 CONCAT 配合条件判断，直接拼接出完整的WHERE条件字符串，而不是堆砌一堆 OR 分支。
• 在高并发场景下，如果大量结构不同的SQL都走预处理，还可能快速占满 performance_schema.prepared_statements_instances 表，带来额外的内存管理压力。

真要兼容空参？试试应用层控制 + 多个固定 SQL

当查询条件组合非常复杂时，与其追求一个“万能”的动态SQL，不如换个思路：将常见的查询模式拆分成几条固定的、优化的SQL语句，由应用层的业务逻辑来负责路由。

这种方法特别适用于高频查询路径相对明确的场景，比如参数组合通常不超过5到6种的情况。

• 例如，可以在Mapper中定义三个独立的方法：selectByUser（仅按用户查询）、selectByStatusAndTime（按状态和时间查询）、selectByAll（全条件查询）。每个方法对应一条最优化的SQL语句和索引设计。
• 在Service层，根据传入的参数组合进行判断，调用对应的Mapper方法。这比把所有参数都塞进一个庞大的 selectDynamic 要清晰、高效得多。
• 这样做的好处是，每条SQL的执行计划（EXPLAIN 结果）都非常稳定，能够稳定地走 ref 或 range 类型的索引访问。DBA也更容易针对每一条具体SQL进行索引优化。

最后，必须提一个最容易被忽略的要点：索引设计得再精妙，也架不住 SELECT *（特别是查询不需要的字段）和 LIKE '%xxx'（前导通配符）这类操作的拖累。动态SQL优化的边界，不仅仅在于“怎么拼接条件”，更在于深入思考：哪些条件真的应该放进WHERE子句？哪些过滤其实更适合放在应用层完成？厘清这一点，才能从根源上提升查询性能。