MySQL5.7.11使用自定义函数查询极慢

ccxpts

MySQL5.7.11（8G内存16C)测试自定义函数


自定义函数放在select语句很快，放在where中或ON中就慢，

例:
A表600条数据，B表3万条数据
自定义函数:getSfz,将15位证件号转为18位

select getSfz(A.身份证号码),B.姓名 from A
left join B ON A.身份证号码=B.身份证号码;
自定义函数放在select中极快，约十几秒完成

select A.身份证号码, B.姓名 from A
left join B ON getSfz(A.身份证号码) = getSfz(B.身份证号码)
自定义函数放在where或ON条件中，这种方式查询就变得极慢，约15分钟才完成，但在MYSQL5.5.28环境下约几十秒完成
------------------------------------------------------------------------------
测试中的真实数据:
1-将18位的身份证号码转换为15位的号码
DELIMITER $
DROP FUNCTION IF EXISTS get_sfz$
CREATE DEFINER = CURRENT_USER FUNCTION get_sfz(
in_SFZ CHAR(32))
RETURNS CHAR(32)
    NO SQL
BEGIN
  DECLARE c_str CHAR(32) default '';

  SET c_str = in_SFZ;  
  IF LENGTH(in_SFZ) = 18 THEN
    SET c_str = concat( left(in_SFZ,6), substring(in_SFZ,9,9) );
  END IF;  

  RETURN c_str;  
END;$
DELIMITER ;

2-查询语句：
select a.KHJLFLNM, a.GSWDBRNO, get_sfz(a.ZJHMCFNO) as sfz1, b.CIP_CSTNO as khh1
from GLFMKHJL as a
left join PB_CSTINF_PRO as b ON get_sfz( b.CIP_CTFNO ) = get_sfz(a.ZJHMCFNO)
where length(a.KHJLSTAF)=7 and a.XTDLBOOL<>'9';

ccxpts

一剑倾城 发表于 2016-01-20 09:56
一个小项目里用了一下5.7，一个嵌套了较多子查询的SQL，在5.6里可以1-2秒就出结果，放到5.7里等了几十分钟也无法出现结果，把所有能想到的配置都改了，还是没有效果，因为项目时间较紧，无奈放弃5.7换回5.6，等以后有机会再研究吧。

mysql每个新功能、每个稳定性的提升都是以性能下降为代价的

ccxpts

1-将18位的身份证号码转换为15位的号码
DELIMITER $
DROP FUNCTION IF EXISTS get_sfz$
CREATE DEFINER = CURRENT_USER FUNCTION get_sfz(
in_SFZ CHAR(32))
RETURNS CHAR(32)
    NO SQL
BEGIN
  DECLARE c_str CHAR(32) default '';

  SET c_str = in_SFZ;  
  IF LENGTH(in_SFZ) = 18 THEN
    SET c_str = concat( left(in_SFZ,6), substring(in_SFZ,9,9) );
  END IF;  

  RETURN c_str;  
END;$
DELIMITER ;
2-查询SQL
select a.* from (
  select KHJLFLNM as xm, GSWDBRNO as jg, get_sfz( ZJHMCFNO ) as sfz1
  from GLFMKHJL where length(KHJLSTAF)=7   and XTDLBOOL<>'9'
) as a
left join (
  select CIP_CSTNO as khh1, get_sfz( CIP_CTFNO ) as sfz2
  from IBS_PB_CSTINF_PRO
) as b on a.sfz1=b.sfz2;
---------------------------------------------------------------------------------------------
对SQL进行计划查看：
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: GLFMKHJL
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 900
     filtered: 90.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: IBS_PB_CSTINF_PRO
   partitions: NULL
         type: index
possible_keys: NULL
          key: sfz_idx
      key_len: 63
          ref: NULL
         rows: 39411
     filtered: 100.00
        Extra: Using where; Using index; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.01 sec)
------------------------------------------------------------------------------------------------------------------
show warnings;
| Note  | 1003 | /* select#1 */ select `xxgl2`.`GLFMKHJL`.`KHJLFLNM` AS `xm`,`xxgl2`.`GLFMKHJL`.`GSWDBRNO` AS `jg`,`get_sfz15to18`(`xxgl2`.`GLFMKHJL`.`ZJHMCFNO`) AS `sfz1` from `xxgl2`.`GLFMKHJL` left join (`xxgl2`.`IBS_PB_CSTINF_PRO`) on((`get_sfz15to18`(`xxgl2`.`GLFMKHJL`.`ZJHMCFNO`) = `get_sfz15to18`(`xxgl2`.`IBS_PB_CSTINF_PRO`.`CIP_CTFNO`))) where ((length(`xxgl2`.`GLFMKHJL`.`KHJLSTAF`) = 7) and (`xxgl2`.`GLFMKHJL`.`XTDLBOOL` <> '9')

从警告信息看，这条SQL被自动转换为：
select KHJLFLNM AS xm,GSWDBRNO AS jg, get_sfz15to18(ZJHMCFNO) AS sfz1
from GLFMKHJL
left join IBS_PB_CSTINF_PRO on get_sfz15to18(ZJHMCFNO) = get_sfz15to18(CIP_CTFNO)
where length(KHJLSTAF) = 7 and XTDLBOOL<>'9'

执行转换后我的这条SQL很慢，需25分以上
-------------------------------------------------------------------------------------------------------------------
上述SQL在5.5环境下进行计划查看：
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 668
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 39803
        Extra:
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: IBS_PB_CSTINF_PRO
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 39803
        Extra:
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: GLFMKHJL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 905
        Extra: Using where