Mysql模糊查询LIKE语句结合CONCAT函数导致索引失效问题

场景

数据库多表联查，但因数据原因，导致只能使用字段内的数据进行模糊关联，表内数据十几万，数据量不大，但查询时间却有十几秒。
最先想到的是为相应字段加索引，提高查询效率。加入索引后索引未失效，由此可见sql语句有问题导致索引失效。

索引失效原因

在MySQL中，LEFT JOIN连接左表不走索引可能有几个原因：

1. 索引选择性不高：如果左表的连接列的索引选择性不高（即列中重复的值很多），MySQL优化器可能认为全表扫描比使用索引更高效。
2. 类型不匹配：连接的列在两个表中的数据类型必须完全匹配。如果类型不匹配，即使有索引，MySQL也可能不使用索引。
3. 查询条件：查询中的其他条件可能影响优化器的决策。例如，涉及到函数的操作（如WHERE LEFT(column, 3) = 'abc'）可能导致索引失效。
4. 索引未被更新或损坏：如果索引未被更新或因为某种原因损坏，MySQL可能无法利用这些索引。
5. 优化器评估：MySQL查询优化器基于统计信息做出决策，有时候可能评估使用索引不如其他方法（如全表扫描）效率高。这可能与表的大小、索引的存储方式等因素有关。

通过检查表字段索引，并非上述相关情况，模糊查询导致索引失效，但那只是左模糊情况，上述条件为右模糊查询，可以走索引。

不走索引原因

当在JOIN条件中使用LIKE配合CONCAT时，这种操作通常会导致索引失效，因为MySQL无法利用索引来直接匹配模式。索引是基于整个值的匹配来优化的，而LIKE和CONCAT的组合生成了一个新的字符串，这使得MySQL很难使用原有索引。

后续解决

因sql语句无法实现，最终决定使用java逻辑进行功能实现。将以下sql拆分为两份，单独查询biz_category_info 和关联查询centlib_product_temp 、centlib_wasted_record ，通过后端程序对两个数据进行处理。

相关sql

EXPLAIN SELECT
	bci.id,
	bci.parent_id parentId,
	bci.category_code categoryCode,
	bci.category_name categoryName,
	count( cwr.ProductInfoID ) total 
FROM
	biz_category_info bci
	LEFT JOIN centlib_product_temp cpt ON cpt.cus_category_code LIKE CONCAT( '', bci.category_code, '%' ) 
	LEFT JOIN centlib_wasted_record cwr ON cpt.source_id = cwr.ProductInfoID 
GROUP BY
	bci.category_code,
	bci.category_name

效率

模糊查询索引是否失效

• 前缀模糊查询（例如LIKE 'abc%'）: 在这种情况下，如果字段上有索引，MySQL可以利用索引进行查询。这是因为MySQL能够利用索引查找到以指定前缀开始的第一个记录，然后继续读取直到不再匹配为止。这类查询可以有效地利用索引加速。
• 后缀模犊查询（例如LIKE '%abc'）或包含模糊查询（例如LIKE '%abc%'）: 这类查询通常不会走索引（或者说不会有效利用索引），因为MySQL需要扫描全表以找到匹配的行。这是由于索引是按顺序存储的，而当查询的开始部分是一个通配符时，MySQL无法直接利用索引确定在哪里开始查找。
• 原文公众号：华仔的项目记录