乘风原创程序

  • postgresql 中position函数的性能详解
  • 2021/2/1 14:11:28
  • 起因:

    postgresql中position函数提供从头查找返回第一个匹配到字符串的下标。

    而我需要返回从后向前查找第一个匹配到的坐标,但是postgressql并未提供相关函数,所以自己写了如下代码提供相关功能:

    create or replace function lastindexof(text, character)
     returns integer as
    $body$
    begin
     if $1 is null then return null;
     end if;
     for i in reverse length($1) .. 1
     loop
      if substr($1,i,1) = $2 
      then
       return i;
      end if;
     end loop;
     return null;
    end
    $body$
    language plpgsql immutable strict

    本来以为事情完美解决,但是性能的差距却让人感觉很失望,如下图

    数据库原生的“position”和自己提供“lastindexof”,居然产生了30倍以上的性能差距,那么探究缘由就变成一个有意思的事情。也算第一次尝试翻阅数据库源码,中间总是有点小麻烦,不过当我找到如下代码的时候,那种恍然大悟的欣喜也算满足了自己求知欲。

    注意看1054行,运用了指针~ 可见数据库底层运算,用了引用传递,而我自己写的函数是一个拷贝传递。

    原因找到了,解决问题也就手到擒来,拿c写一个扩展?或者?

    本可以拿c写一些底层代码练练手,不过那又需要重新编译等等,时间有限,留给以后去做吧,先想个简单的办法去解决它。

    解决方法如下

    select length(dir) -position('/' in reverse(dir)) +1 from log_hup_ftp_30

    测试性能截图

    好吧,虽然由于函数的复杂性增加,性能还是慢了一倍多,但是比起之前5s之久还是快了不少。

    tips:

    最近得到德哥的回答,引用传递可以使用游标类型!再次谢谢德哥~

    补充: sql之查询函数locate、position、instr、find_in_set、in、like

    locate()

    返回要查询的字符串在被查询字符串里第一次出现的位置

    注:当在 mysql 4.0 中时,如有任一参数是一个二进制字符串,它才是字母大小写敏感的

    locate(substr,str)

    返回substr字符串在str里第一次出现的位置,没有返回0

    select locate('.',t.str)from `table` t;
    >5

    locate(substr,str,pos)

    返回substr字符串在str里pos(起始位置)出现的位置,没有返回0

    注:pos必须大于第一次出现的位置,才能显示第二次出现的位置

    select locate('.',t.str,6)from `table` t;
    >9//当小于等于第一次出现的位置(5)时,返回的还是第一次出现的位置
    

    position()

    返回要查询的字符串在被查询字符串里第一次出现的位置(和locate用法一样,查了很多资料position是locate的别名)

    position(substr in str)

    返回substr字符串在str出现的位置,没有返回0

    select position('cn' in t.str)from `table` t;
    >10

    instr()

    返回要查询的字符串在被查询字符串里第一次出现的位置。这和locate()的双参数形式相同,只是参数的顺序被颠倒。

    instr(str,substr)

    返回substr字符串在str出现的位置,没有返回0

    select instr(t.str,'com')from `table` t;
    >6

    find_in_set()

    返回在集合中的索引位置(竖向发展)

    find_in_set(str,strlist)

    返回str1在strlist集合中的索引位置

    select find_in_set('demo.com.cn',t.str) from `table` t;
    >1//返回索引

    in()

    返回在集合中的索引位置(同find_in_set)

    str in (strlist)

    返回str1在strlist集合中的索引位置

    select 'demo.com.cn' in(t.str) from `table` t;
    >1//返回索引

    like

    返回类似(模糊)字符的集合

    like %str%

    返回以str类似的集合

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持本教程网。如有错误或未考虑完全的地方,望不吝赐教。