首页 >数据库 >mysql教程 >MySQL分表优化试验_MySQL

MySQL分表优化试验_MySQL

WBOY
WBOY原创
2016-06-01 13:53:33795浏览

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

  这里的分表逻辑是根据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 ;

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn