作者:pan2502851807 | 来源:互联网 | 2023-09-18 14:12
我们的项目中有好多不等于的情况。今天写这篇文章简单的分析一下怎么个优化法。
这里的分表逻辑是根据t_group表的user_name组的个数来分的。
因为这种情况单独user_name字段上的索引就属于烂索引。起不了啥名明显的效果。
1、试验PROCEDURE.
DELIMITER $$
DROP PROCEDURE `t_girl`.`sp_split_table`$$
CREATE PROCEDURE `t_girl`.`sp_split_table`()
BEGIN
declare done int default 0;
declare v_user_name varchar(20) default '';
declare v_table_name varchar(64) default '';
-- Get all users' name.
declare cur1 cursor for select user_name from t_group group by user_name;
-- Deal with error or warnings.
declare continue handler for 1329 set dOne= 1;
-- Open cursor.
open cur1;
while done <> 1
do
fetch cur1 into v_user_name;
if not done then
-- Get table name.
set v_table_name = concat(&#39;t_group_&#39;,v_user_name);
-- Create new extra table.
set @stmt = concat(&#39;create table &#39;,v_table_name,&#39; like t_group&#39;);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
-- Load data into it.
set @stmt = concat(&#39;insert into &#39;,v_table_name,&#39; select * from t_group where user_name = &#39;&#39;&#39;,v_user_name,&#39;&#39;&#39;&#39;);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
end if;
end while;
-- Close cursor.
close cur1;
-- Free variable from memory.
set @stmt = NULL;
END$$
DELIMITER ;