mysql函数求优化
小弟刚开始接触Mysql数据库,需要基于Mysql数据库做报表开发,写的一个函数查询时候效率特别低下。
备注:传进来比较的参数数据量 5W条左右。请高手指教优化方法。
另外,一般Mysql调试的技巧及工具都用什么
函数如下:
SQL code
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 ;
1
DELIMITER$$DROPFUNCTIONIFEXISTS`nqrptr`.`get_ip_type_test`$$CREATEDEFINER=`netqos`@`%`FUNCTION`get_ip_type_test`(ip_nobigint(10),code_typeint(10),return_typevarchar(20))RETURNSvarchar(40)CHARSETlatin1BEGINDECLAREreturn_valuevarchar(30);DECLAREtemp_provvarchar(30);DECLAREtemp_cityvarchar(30);DECLAREtemp_busivarchar(30);DECLAREiftrueint(10);selectcount(1)intoiftruefromnqrptr.idc_ip_mappingwherestatus=1ANDtype_id=code_typeANDstart_ip_no<=ip_noANDend_ip_no>=ip_no;ififtrue=0thensetreturn_value='Unkown Ip';elseSELECTprovinces_id,city_id,busi_idINTOtemp_prov,temp_city,temp_busiFROMnqrptr.idc_ip_mappingWHEREstatus=1ANDtype_id=code_typeANDstart_ip_no<=ip_noANDend_ip_no>=ip_no;ifreturn_type='PRO'thensetreturn_value=temp_prov;elseifreturn_type='CITY'thensetreturn_value=temp_city;elseifreturn_type='BUSI'thensetreturn_value=temp_busi;elseifreturn_type='IPFromTo'thensetreturn_value=concat(temp_start,' to ',temp_end);elsesetreturn_value='other';endif;endif;RETURNreturn_value;END$$DELIMITER;
欢迎大家阅读《Mysql函数求优化解决思路》,跪求各位点评,by 搞代码
------解决方案--------------------
索引建立没有,将
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
------解决方案--------------------
应该是这句话慢
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)
------解决方案--------------------
索引建立没有
type_id start_ip_no end_ip_no 建立复合索引
------解决方案--------------------
把字段type_id start_ip_no end_ip_no建立复合索引。
------解决方案--------------------
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';
....
试试
------解决方案--------------------
贴建表及插入记录的SQL,及要求结果出来看看
看看能否用SQL语句直接解决
------解决方案--------------------
SQL code
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 ;
原创文章,转载请注明: 转载自搞代码
微信 赏一包辣条吧~
支付宝 赏一听可乐吧~