|
????????在批量查询的时候，有时为了提高检索效率，会使用Unique配合IN查询。此时注意IN中不要写子查询，否则时间复杂度会大大增加。不过对于已经确定的检索范围，如果有Unique索引，效率还是可以的。
????????但有时候由于种种原因，并不总会有Unique索引。那么当表的数据量比较大（达到千万级）的时候，查询的时间就比较长了。因为IN会进行全表搜索。而且如果想去重的话，那就要配合Group By语句了。
????????已知的改进的方案有若干种：①用OR取代IN②在WHERE子句中限定IN查询范围的上下限，③用UNION的方式合并查询结果，等等。
? ? ? ? 如果单纯用UNION执行单次查询的话，查询的效率通常还是会受到影响。它还是会进行全表检索，而且会检索多次。而且，如果是单次检索，可能会构造出行数很多的SQL语句，MYSQL服务器解析SQL语句并处理的时候也会慢下来。
????????下面这种改进方案是基于查询范围上限进行的。
????????①在应用程序中（如JVM），将查询范围的集合元素按升（或降，后面注意相应调整）序排序。
? ? ? ? ②在MYSQL中建立两个函数：func_get_split_string，用来截取字符串；func_get_split_string_total，获取被分隔符（如','）分隔的查询元素的总数。如下：
-- 截取字符串
DROP FUNCTION IF EXISTS func_get_split_string;
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(
f_string varchar(16382),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
-- Get the separated number of given string.
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END$$
DELIMITER ;
-- 元素总数
DROP FUNCTION IF EXISTS func_get_split_string_total;
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`(
f_string varchar(16382),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
-- Get the total number of given string.
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER ;
????????②写一个存储过程，入参为IN的结果集用一个特定的字符拼接而成的（因为Mysql的存储过程不支持集合）。如果搜索集元素较多或较长，参数类型应设为TEXT或LONGTEXT。这是入口存储过程。
? ? ? ? ④在入口存储过程中，用函数func_get_split_string拆解输入的范围的元素，然后在循环中调用执行子任务的存储过程。
????????
DROP PROCEDURE IF EXISTS Pro_entry;
DELIMITER $$
CREATE PROCEDURE Pro_entry(
_cIds TEXT) -- 定义参数
BEGIN
DECLARE cnt int default 0;
DECLARE i int default 0;
DECLARE _cId VARCHAR(128);//当前元素
DECLARE _priorCid VARCHAR(128) DEFAULT NULL;//前一元素
SET cnt = func_get_split_string_total(_cIds,',');//获得元素个数
while i < cnt
do
set i = i + 1;
SET _cId = func_get_split_string(_cIds,',',i);
CALL Proc_sub(_cId, _priorCid);//调用子存储过程进行查询操作
END WHILE;
END$$
DELIMITER ;
? ? ? ? ⑤定义执行存储过程Proc_sub，可用Limit限制每次的检索行数。注意用到动态指令PREPARE、EXECUTE执行：????????
DROP PROCEDURE IF EXISTS Pro_sub;
DELIMITER $$
CREATE PROCEDURE Pro_sub (
INOUT annualAssetsSql LONGTEXT,
IN _cId VARCHAR ( 128 ),
IN _priorCid VARCHAR ( 128 )
)
BEGIN -- 按照Sql的逻辑层次排布代码
DECLARE thisSql LONGTEXT;
SET thisSql = CONCAT(
' SELECT * FROM 【表名】 tb
WHERE tb.c_id = ''', _cId, ''' '
);
IF _priorCid IS NOT NULL THEN
SET thisSql = CONCAT(thisSql, ' AND tb.c_id > ''', _priorCid, ''' ');
END IF;
SET thisSql = CONCAT(thisSql, ' LIMIT 1 )');
set @annual_assets_sql = thisSql;
PREPARE testSelectTemp FROM @annual_assets_sql;
EXECUTE testSelectTemp;
DEALLOCATE PREPARE testSelectTemp;
END$$
DELIMITER ;
????????其实，更推荐的方式是方法②，即在确定了上下限的情况下配合IN使用。但这也是会产生较大的SQL语句。
|