instr hive函数 hive中instr函数效率

转载

编程小天匠 2024-08-26 08:11:59

文章标签 instr hive函数数据库字段 oracle 数据 文章分类 Hive 大数据

　　今天上午10点左右，boss告诉我们生产环境一客户在某功能下录入信息时，出现加载数据很慢的情况，因为严重影响了客户使用，就让我们赶紧查实原因，组长和我就根据领导提供的用户登录系统，找到出问题的那个功能，选择买方和银行信息后加载限额信息时组长的电脑出现的反应是浏览器卡死，我的电脑上是去趟厕所回来坐下来之后才加载出来，我靠3分钟左右的时间，崩溃！！！赶紧查代码经查询代码发现，加载的数据是从千万量级（有2321w余条）的表中查询，查询语句中使用了like操作符，经研究发现确实是因为查询的sql中使用like操作符造成。查询sql如下：

　　select sum(t.a*t.b) from table t where t.c = 'A' and t.d like 'AM%' and t.e = 'B'....

　　其中表t的d字段值的开头2个字母表达某种类型的数据有AM、BM、CM等几类数据，且d字段的其他位均为0-9的数字

      下边是分析问题的步骤：
      1.经查询该表的索引策略，该表的为 d > c > e （备注：d、c、e为table表的字段）
      2.将d的索引失效，之后用上述语句查询，速度很快，但是对于select * from table t where t.d = 'AM1245'的使用无疑会是灾难，因为变成全表检索了
      3.然后使d的索引生效，查询该表的默认的索引策略依旧为 d > c > e
      4.使用instr(t.d,'AM')>0 替换sql语句中的t.d like 'AM%',发现效率很快。 -- 在生产环境验证的结果是使用like查询三分钟是用instr后瞬间查询出来
      由此问题，引出了本文的重点,即instr与like的模糊查询效率问题

经问度娘得知：

　　　使用Oracle的instr函数与索引配合提高模糊查询的效率。　　

          一般来说，在Oracle数据库中，我们对tb表的name字段进行模糊查询会采用下面两种方式：
               1.select * from tb where name like '%XX%';
               2.select * from tb where instr(name,'XX')>0;
          若是在name字段上没有加索引，两者效率差不多，基本没有区别。
         为提高效率，我们在name字段上可以加上非唯一性索引：
              create index idx_tb_name on tb(name);
         这样，再使用 select * from tb where instr(name,'XX')>0;这样的语句查询，效率可以提高不少，表数据量越大时两者差别越大（我们遇到的问题就是属于这种问题）。但也要顾及到name字段加上索引后DML语句会使索引数据重新排序的影响。

　　本文参看文章：http://wenku.baidu.com/link?url=7C70-3mTzeKx2IdVjEdhgHKK8m5elJE5CYyPNivfzj4w8eU9snrbNMNMrfOH6kPZ-G150BXJVIJaHciHsTw-i9erGMtMwRIGRzKXp-rAlmK

解决该问题涉猎到的知识点：

　　1.oracle直方图

　　　　相关介绍：参看 http://czmmiao.iteye.com/blog/1484298

2.索引策略