esfpicture 分表过程

esfpicture有千万以上的数据量,根据bid对100取模分表.

1.先对bid取模,保存到sitecode字段

UPDATE esfpicture_all esf SET esf.sitecode = esf.bid%100 ;

 2.编写存储过程,创建分表,并将总表数据插入到分表

DELIMITER $$

USE `public`$$

DROP PROCEDURE IF EXISTS `esfpicture`$$

CREATE DEFINER=`jiwudev`@`192.168.0.%` PROCEDURE `esfpicture`()
BEGIN
DECLARE `@i` INT(11);
DECLARE `@siteCount` INT(11);
DECLARE `@sqlstr` VARCHAR(2560);
DECLARE `@sqlinsert` VARCHAR(2560); 

SET `@i`=1;
WHILE `@i`<=100 DO 
SET @sqlstr = CONCAT('
CREATE TABLE esfpicture',`@i`,' (
  `BPID` int(11) NOT NULL,
  `Type` smallint(6) NOT NULL,
  `FileName` varchar(100) NOT NULL,
  `Path` varchar(500) NOT NULL,
  `Status` smallint(6) NOT NULL,
  `Suffix` varchar(10) NOT NULL,
  `Remark` text,
  `BID` int(11) NOT NULL,
  `CTime` date NOT NULL,
  `NID` int(11) NOT NULL,
  `NIP` varchar(20) DEFAULT NULL,
  `SiteCode` smallint(6) NOT NULL,
  `SourceName` varchar(200) NOT NULL,
  `PictureSize` int(11) NOT NULL,
  `AllPath` varchar(200) NOT NULL,
  `Vtimes` int(11) NOT NULL,
  `IsCover` smallint(6) NOT NULL,
  `HID` int(11) NOT NULL,
  UNIQUE KEY `BPID` (`BPID`),
  KEY `AllPath` (`AllPath`),
  KEY `HID` (`HID`),
  KEY `BID_ALLpath` (`BID`,`AllPath`)
) ENGINE=MyISAM AUTO_INCREMENT=13392873 DEFAULT CHARSET=utf8
');
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
SET @sqlinsert = CONCAT('INSERT INTO esfpicture',`@i`,' (`BPID`,`Type`,`FileName`,`Path`,`Status`,`Suffix`,`Remark`,`BID`,`CTime`,`NID`,`NIP`,`SiteCode`,`SourceName`,`PictureSize`,`AllPath`,`Vtimes`,`IsCover`,`HID`)
SELECT `BPID`,`Type`,`FileName`,`Path`,`Status`,`Suffix`,`Remark`,`BID`,`CTime`,`NID`,`NIP`,`SiteCode`,`SourceName`,`PictureSize`,`AllPath`,`Vtimes`,`IsCover`,`HID` FROM `esfpicture_all` where sitecode = ',`@i`,'');
PREPARE stmt FROM @sqlinsert;
EXECUTE stmt;
SET `@i`= `@i`+1;
END WHILE;
END$$

DELIMITER ;

---------------------------------2600w数据的测试情况----------------------------------------

1.先对bid取模,保存到sitecode字段

2.执行存储过程分表的时间