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
子句有两个条件:
LENGTH(p1.calling_number) < 11
:这是对字符串长度的判断。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%'