sql 中使用like%、函数导致索引失效的解决方案

SELECT
	p1.*
	FROM cdr_voice_202407_0 AS p1 
WHERE
	LENGTH( p1.calling_number ) < 11 
	AND p1.calling_number LIKE '%10086%'

上边的sql中如果 calling_number  是索引  会导致索引失效

涉及的 WHERE 子句有两个条件：

1. LENGTH(p1.calling_number) < 11：这是对字符串长度的判断。
2. p1.calling_number LIKE '%10086%'：这是一个通配符匹配，且通配符 % 位于前后（全局匹配）。

对于这两个条件，如果有索引，它们的表现如下：

1. LENGTH(p1.calling_number) < 11：

• 索引失效：一般来说，使用函数（如 LENGTH）会导致索引失效，因为数据库无法利用常规的 B-tree 索引或其他索引类型。
• 解决办法：可以考虑创建一个虚拟列或生成列，然后在该列上创建索引。例如，创建一个虚拟列存储 calling_number 的长度，并在该列上创建索引。

ALTER TABLE cdr_voice_202409_0
ADD calling_number_len INT AS (LENGTH(calling_number)) VIRTUAL;

使用生成列（computed column），这些列可以基于已有的列进行计算 

MySQL 中的虚拟列

在 MySQL 中，你可以使用生成列（generated column）来创建虚拟列，生成列可以是存储的（即在物理上存储在磁盘中）或虚拟的（即动态计算的）。

GENERATED 列（虚拟列）是从 MySQL 5.7 及以上版本支持的。如果你使用的 MySQL 版本低于 5.7，将不支持这个功能。请先确认你的 MySQL 版本 

这样，在查询 LENGTH(calling_number) 时，数据库可以使用索引。

2. p1.calling_number LIKE '%10086%'：

• 索引失效：因为 % 放在了字符串的开头，常规的 B-tree 索引无法被有效使用。B-tree 索引是顺序索引，只有在字符串开头匹配时（例如 LIKE '10086%'）才能利用索引。
• 解决办法：

  • 全文索引（Full-Text Index）：如果数据库支持全文索引（Oracle 使用 Text 索引，MySQL 支持 FULLTEXT 索引），你可以使用它来加速这种模式匹配。

  • ALTER TABLE cdr_voice_202409_0
    ADD FULLTEXT INDEX idx_calling_number (calling_number);

• FULLTEXT：指定为全文索引类型。
• idx_calling_number：是索引的名称，可以自行命名。
• calling_number：是需要加全文索引的列。

 

总结

• LIKE '%10086%' 更灵活，但对大数据集性能较差，因为它通常会导致全表扫描，无法利用索引。
• MATCH ... AGAINST('10086') 依赖于全文索引，性能更高，但需要先为目标字段创建全文索引，适用于较大文本的关键词搜索。
• MATCH(calling_number)：表示要搜索的列。
• AGAINST('10086')：表示要搜索的关键字。

优化后的sql为 

SELECT p1.*
FROM cdr_voice_202407_0 AS p1
WHERE LENGTH(p1.calling_number) < 11 
  AND p1.calling_number LIKE '%10086%'