mysql模糊搜索索引失效的情况探究

问题引入

目前在工作中，对于以前编写的sql语句进行性能分析，为了提高查询性能，进行索引的优化。 之前有了解到过使用模糊搜索时，索引会失效，那么还有没有必要对需要模糊搜索的字段加索引呢？

左查询

左查询是指模糊搜索的通配符位于搜索字符串的右侧，例如 name LIKE 'John%'。在这种情况下，索引的选择性可能会受到一定影响。

1. 过程分析验证：

   • 使用EXPLAIN语句分析查询计划并验证索引的使用情况：

     EXPLAIN SELECT * FROM users WHERE name LIKE 'John%';
     

   • 检查查询计划中的"possible_keys"字段和"key"字段：
     • 如果"possible_keys"字段中列出了索引，且"key"字段显示了使用的索引，则索引被成功使用。
     • 如果"possible_keys"字段为空，或者"key"字段显示为NULL，则索引失效。

2. 原因及思考：

   • 当左查询的通配符位于搜索字符串的右侧时，索引在匹配过程中可能无法利用索引的选择性，导致索引失效。
   • 这可能是由于通配符在搜索字符串的开头位置，使得索引无法有效地进行匹配。

右查询

右查询是指模糊搜索的通配符位于搜索字符串的左侧，例如 name LIKE '%John'。在这种情况下，索引的选择性可能对索引的使用产生影响。

1. 过程分析验证：

   • 使用EXPLAIN语句分析查询计划并验证索引的使用情况：

     EXPLAIN SELECT * FROM users WHERE name LIKE '%John';
     

   • 检查查询计划中的"possible_keys"字段和"key"字段：
     • 如果"possible_keys"字段中列出了索引，且"key"字段显示了使用的索引，则索引被成功使用。
     • 如果"possible_keys"字段为空，或者"key"字段显示为NULL，则索引失效。

2. 原因及思考：

   • 当右查询的通配符位于搜索字符串的左侧时，索引的选择性对索引的使用可能产生一定影响。
   • 在某些情况下，索引的选择性可能较好，可以利用索引进行快速匹配。但在其他情况下，选择性可能较差，导致索引失效。

全查询

全查询是指模糊搜索的通配符位于搜索字符串的两侧，例如 name LIKE '%John%'。在这种情况下，索引的选择性和索引字段的长度可能会影响索引的使用情况。

1. 过程分析验证：

   • 使用EXPLAIN语句分析查询计划并验证索引的使用情况：

     EXPLAIN SELECT * FROM users WHERE name LIKE '%John%';
     

   • 检查查询计划中的"possible_keys"字段和"key"字段：
     • 如果"possible_keys"字段中列出了索引，且"key"字段显示了使用的索引，则索引被成功使用。
     • 如果"possible_keys"字段为空，或者"key"字段显示为NULL，则索引失效。

2. 原因及思考：

   • 在全查询的情况下，索引的选择性和索引字段的长度可能对索引的使用产生影响。
   • 索引选择性较好且索引字段长度足够长时，索引可以有效地过滤出匹配的结果，提高查询性能。
   • 索引选择性较差或索引字段长度过短时，索引可能无法有效过滤数据，导致索引失效。

结论

模糊查询的字段增加索引可以显著增加查询效率，但是只有在左查询的情况下索引才会生效，右查询和全查询的情况下索引会失效。但是由于目前当前所有的模糊查询使用的是全索引，所以不考虑加索引。