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

打印 上一主题 下一主题

主题 518|帖子 518|积分 1554

  1. SELECT
  2.         p1.*
  3.         FROM cdr_voice_202407_0 AS p1
  4. WHERE
  5.         LENGTH( p1.calling_number ) < 11
  6.         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 的长度,并在该列上创建索引。
  1. ALTER TABLE cdr_voice_202409_0
  2. 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 索引),你可以使用它来加速这种模式匹配。
      1. ALTER TABLE cdr_voice_202409_0
      2. 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为 
  1. SELECT p1.*
  2. FROM cdr_voice_202407_0 AS p1
  3. WHERE LENGTH(p1.calling_number) < 11
  4.   AND p1.calling_number LIKE '%10086%'
复制代码
 
 


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览

快速回复

您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

诗林

金牌会员
这个人很懒什么都没写!

标签云

快速回复 返回顶部 返回列表