MySQL分表优化试验

MySQL分表优化试验2010-09-06我们的项目中有好多不等于的情况。今天写这篇文章简单的分析一下怎么个优化法。

这里的分表逻辑是根据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（"t_group_",v_user_name）;
   -- Create new extra table.
   set @stmt = concat（"create table ",v_table_name," like t_group"）;
   prepare s1 from @stmt;
   execute s1;
   drop prepare s1;
   -- Load data into it.
   set @stmt = concat（"insert into ",v_table_name," select * from t_group where user_name = """,v_user_name,""""）;
   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 ;