Mysql函数求优化

Question

小弟刚开始接触Mysql数据库，需要基于Mysql数据库做报表开发，写的一个函数查询时候效率特别低下。
备注：传进来比较的参数数据量 5W条左右。请高手指教优化方法。
另外，一般Mysql调试的技巧及工具都用什么函数如下：
DELIMITER $$DROP FUNCTION IF EXISTS `nqrptr`.`get_ip_type_test`$$CREATE DEFINER=`netqos`@`%` FUNCTION `get_ip_type_test`(ip_no bigint(10),code_type int(10), return_type varchar(20)) RETURNS varchar(40) CHARSET latin1
BEGIN
   DECLARE return_value varchar(30);
   DECLARE temp_prov varchar(30);
   DECLARE temp_city varchar(30);
   DECLARE temp_busi varchar(30);
   DECLARE iftrue int(10);   
 select count(1) into iftrue  
   from nqrptr.idc_ip_mapping where  status = 1
            AND type_id = code_type
    AND start_ip_no <= ip_no
    AND end_ip_no >= ip_no;
 if iftrue = 0 then 
set return_value = 'Unkown Ip';
 else
SELECT provinces_id,city_id,busi_id
          INTO temp_prov,temp_city,temp_busi FROM nqrptr.idc_ip_mapping
         WHERE status = 1
          AND type_id = code_type
  AND start_ip_no <= ip_no
  AND end_ip_no >= ip_no;  
     if return_type = 'PRO' then
set return_value = temp_prov;
      elseif return_type = 'CITY' then
set return_value = temp_city;
      elseif return_type = 'BUSI' then
set return_value = temp_busi;
      elseif return_type = 'IPFromTo' then
set return_value = concat(temp_start,' to ',temp_end);
      else
set return_value='other';
     end if;    
  end if;
   RETURN return_value;
 
END$$DELIMITER ;

Answer 1

索引建立没有，将
if return_type = 'PRO' then
    set return_value = temp_prov;
      elseif return_type = 'CITY' then
    set return_value = temp_city;
      elseif return_type = 'BUSI' then
    set return_value = temp_busi;
      elseif return_type = 'IPFromTo' then
    set return_value = concat(temp_start,' to ',temp_end);
      else
    set return_value='other';
     end if;                       
  end if;
set return_valuE=IF(return_type = 'CITY',temp_city,...)
修改为IF OR CASE WHEN

Answer 2

应该是这句话慢
 select count(1) into iftrue  
       from nqrptr.idc_ip_mapping where  status = 1
            AND type_id = code_type
        AND start_ip_no <= ip_no
        AND end_ip_no >= ip_no;
加索引
alter table idc_ip_mapping add index(type_id)

Answer 3

if esle修改成case when后依然不理想。
涉及到的表是字典表idc_ip_mapping ，有500条记录左右。有5W条数据需要传进来和字典表比对。 
 select get_ip_type_test(serverid,4,'BUSI'),p.* from pm_hou_c p

Answer 4

--rucypli
有索引，这句话不会慢。
如果调用函数的数据有100条所有，则很快；随着数据量变动大，变得越来越慢，1W条数据的时候就40s了。是不是mysql的性能就是不好。还是我函数语法写的有问题？

Answer 5

索引建立没有
type_id start_ip_no end_ip_no 建立复合索引

Answer 6

把字段type_id start_ip_no end_ip_no建立复合索引。

Answer 7

 select count(1) into iftrue  
       from nqrptr.idc_ip_mapping where  status = 1
            AND type_id = code_type
        AND start_ip_no <= ip_no
        AND end_ip_no >= ip_no;
 if iftrue = 0    then 
    set return_value = 'Unkown Ip';->
if not exists(select 1  
       from nqrptr.idc_ip_mapping where  status = 1
            AND type_id = code_type
        AND start_ip_no <= ip_no
        AND end_ip_no >= ip_no) then
set return_value = 'Unkown Ip';
....试试

Answer 8

联合索引是有的。
改成exists后快了一些。但总体上还是慢。
怎么看开销花在了什么地方，是查询，还是赋值，还是那一块。一次调用用0.015秒，感觉总的时间就是这个0.015*调用次数。没有优化在里边。 

Answer 9

联合索引是有的。用FORCE INDEX  强制使用此索引

Answer 10

感谢wwwwb的解答，添加强制索引后，又快了些。但是总体效果不是很明显我觉得mysql的效率应该是和orcle差不了多少的，我写法的问题比较大。其实函数挺简单，就是传进几万个IP，去一个几百行的字典表里比对，比对通过，返回一种结果，比对不通过返回一种结果。
不知道为什么这么慢，调用这个函数查500条数据的时候会有6s现在的函数是这样的：DELIMITER $$DROP FUNCTION IF EXISTS `nqrptr`.`get_ip_type_test`$$CREATE DEFINER=`netqos`@`%` FUNCTION `get_ip_type_test`(ip_no bigint(10),code_type int(10), return_type varchar(20)) RETURNS varchar(40) CHARSET latin1
BEGIN
   DECLARE return_value varchar(30);
   DECLARE temp_prov varchar(30);
   DECLARE temp_city varchar(30);
   DECLARE temp_busi varchar(30);
   DECLARE temp_start varchar(30);
   DECLARE temp_end varchar(30); 
   DECLARE iftrue int(10);   
if not exists(select 1   
  from nqrptr.idc_ip_mapping force index(union_func_ind) where status = 1
  AND type_id = code_type
  AND start_ip_no <= ip_no
  AND end_ip_no >= ip_no) then
set return_value = 'Unkown Ip'; else
SELECT provinces_id,city_id,busi_id,start_ip,end_ip 
          INTO temp_prov,temp_city,temp_busi,temp_start,temp_end    FROM nqrptr.idc_ip_mapping  force index(union_func_ind)
         WHERE status = 1
          AND type_id = code_type
  AND start_ip_no <= ip_no
  AND end_ip_no >= ip_no;  
     /*if return_type = 'PRO' then
set return_value = temp_prov;
      elseif return_type = 'CITY' then
set return_value = temp_city;
      elseif return_type = 'BUSI' then
set return_value = temp_busi;
      elseif return_type = 'IPFromTo' then
set return_value = concat(temp_start,' to ',temp_end);
      else
set return_value='other';
     end if; */
     select 
case return_type 
when 'PRO' then   temp_prov
        when 'CITY' then  temp_city
        when 'BUSI' then  temp_busi
when 'IPFromTo'  then concat(temp_start,' to ',temp_end)
        else 'other' 
end into return_value;
  end if;
   RETURN return_value;
 
END$$DELIMITER ;

Answer 11

感谢wwwwb的解答，添加强制索引后，又快了些。但是总体效果不是很明显我觉得mysql的效率应该是和orcle差不了多少的，我写法的问题比较大。其实函数挺简单，就是传进几万个IP，去一个几百行的字典表里比对，比对通过，返回一种结果，比对不通过返回一种结果。
不知道为什么这么慢，调用这个函数查500条数据的时候会有6s现在的函数是这样的：DELIMITER $$DROP FUNCTION IF EXISTS `nqrptr`.`get_ip_type_test`$$CREATE DEFINER=`netqos`@`%` FUNCTION `get_ip_type_test`(ip_no bigint(10),code_type int(10), return_type varchar(20)) RETURNS varchar(40) CHARSET latin1
BEGIN
   DECLARE return_value varchar(30);
   DECLARE temp_prov varchar(30);
   DECLARE temp_city varchar(30);
   DECLARE temp_busi varchar(30);
   DECLARE temp_start varchar(30);
   DECLARE temp_end varchar(30); 
   DECLARE iftrue int(10);   
if not exists(select 1   
  from nqrptr.idc_ip_mapping force index(union_func_ind) where status = 1
  AND type_id = code_type
  AND start_ip_no <= ip_no
  AND end_ip_no >= ip_no) then
set return_value = 'Unkown Ip'; else
SELECT provinces_id,city_id,busi_id,start_ip,end_ip 
          INTO temp_prov,temp_city,temp_busi,temp_start,temp_end    FROM nqrptr.idc_ip_mapping  force index(union_func_ind)
         WHERE status = 1
          AND type_id = code_type
  AND start_ip_no <= ip_no
  AND end_ip_no >= ip_no;  
     /*if return_type = 'PRO' then
set return_value = temp_prov;
      elseif return_type = 'CITY' then
set return_value = temp_city;
      elseif return_type = 'BUSI' then
set return_value = temp_busi;
      elseif return_type = 'IPFromTo' then
set return_value = concat(temp_start,' to ',temp_end);
      else
set return_value='other';
     end if; */
     select 
case return_type 
when 'PRO' then   temp_prov
        when 'CITY' then  temp_city
        when 'BUSI' then  temp_busi
when 'IPFromTo'  then concat(temp_start,' to ',temp_end)
        else 'other' 
end into return_value;
  end if;
   RETURN return_value;
 
END$$DELIMITER ;

Answer 12

贴建表及插入记录的SQL，及要求结果出来看看看看能否用SQL语句直接解决

Answer 13

DELIMITER $$DROP FUNCTION IF EXISTS `nqrptr`.`get_ip_type_test`$$CREATE DEFINER=`netqos`@`%` FUNCTION `get_ip_type_test`(ip_no bigint(10),code_type int(10), return_type varchar(20)) RETURNS varchar(40) CHARSET latin1
BEGIN
   DECLARE return_value varchar(30);
   DECLARE temp_prov varchar(30);
   DECLARE temp_city varchar(30);
   DECLARE temp_busi varchar(30);
   DECLARE iftrue int(10);   
   
SELECT provinces_id,city_id,busi_id
INTO temp_prov,temp_city,temp_busi 
FROM nqrptr.idc_ip_mapping
WHERE status = 1
AND type_id = code_type
AND start_ip_no <= ip_no
AND end_ip_no >= ip_no;   if FOUND_ROWS() = 0    then 
set return_value = 'Unkown Ip';
else
if return_type = 'PRO' then
set return_value = temp_prov;
elseif return_type = 'CITY' then
set return_value = temp_city;
elseif return_type = 'BUSI' then
set return_value = temp_busi;
elseif return_type = 'IPFromTo' then
set return_value = concat(temp_start,' to ',temp_end);
else
set return_value='other';
end if;                       
end if;
   
   RETURN return_value;
     
END$$DELIMITER ;

Answer 14

另外创建如下两个索引。create index xx on idc_ip_mapping(type_id,status,start_ip_no);
create index xx2 on idc_ip_mapping(type_id,status,end_ip_no);