mysql 分表分区小记(二)_MySQL-mysql教程-PHP中文网

mysql 分表分区小记(二)_MySQL

WBOY

发布： 2016-06-01 13:11:43

原创

892 人浏览过

举个列子利用merge存储引擎来实现分表

数据表1450W数据进行八张表拆分花费时间大概6分钟左右

1.创建八张分表 systemlog1,systemlog2,systemlog3,systemlog4...

2.把数据根据主键范围塞入分表中

INSERT INTO systemlog1(systemlog1.SLID,systemlog1.CODE,systemlog1.AID,systemlog1.ADID,systemlog1.CTime,systemlog1.NIP,systemlog1.CityName,systemlog1.UserName) SELECT systemlog.SLID,systemlog.CODE,systemlog.AID,systemlog.ADID,systemlog.CTime,systemlog.NIP,systemlog.CityName,systemlog.UserName FROM systemlog WHERE systemlog.SLID INSERT INTO systemlog2(systemlog2.SLID,systemlog2.CODE,systemlog2.AID,systemlog2.ADID,systemlog2.CTime,systemlog2.NIP,systemlog2.CityName,systemlog2.UserName) SELECT systemlog.SLID,systemlog.CODE,systemlog.AID,systemlog.ADID,systemlog.CTime,systemlog.NIP,systemlog.CityName,systemlog.UserName FROM systemlog WHERE systemlog.SLID =2000000;
INSERT INTO systemlog3(systemlog3.SLID,systemlog3.CODE,systemlog3.AID,systemlog3.ADID,systemlog3.CTime,systemlog3.NIP,systemlog3.CityName,systemlog3.UserName) SELECT systemlog.SLID,systemlog.CODE,systemlog.AID,systemlog.ADID,systemlog.CTime,systemlog.NIP,systemlog.CityName,systemlog.UserName FROM systemlog WHERE systemlog.SLID =4000000;
INSERT INTO systemlog4(systemlog4.SLID,systemlog4.CODE,systemlog4.AID,systemlog4.ADID,systemlog4.CTime,systemlog4.NIP,systemlog4.CityName,systemlog4.UserName) SELECT systemlog.SLID,systemlog.CODE,systemlog.AID,systemlog.ADID,systemlog.CTime,systemlog.NIP,systemlog.CityName,systemlog.UserName FROM systemlog WHERE systemlog.SLID =6000000;
INSERT INTO systemlog5(systemlog5.SLID,systemlog5.CODE,systemlog5.AID,systemlog5.ADID,systemlog5.CTime,systemlog5.NIP,systemlog5.CityName,systemlog5.UserName) SELECT systemlog.SLID,systemlog.CODE,systemlog.AID,systemlog.ADID,systemlog.CTime,systemlog.NIP,systemlog.CityName,systemlog.UserName FROM systemlog WHERE systemlog.SLID =8000000;
INSERT INTO systemlog6(systemlog6.SLID,systemlog6.CODE,systemlog6.AID,systemlog6.ADID,systemlog6.CTime,systemlog6.NIP,systemlog6.CityName,systemlog6.UserName) SELECT systemlog.SLID,systemlog.CODE,systemlog.AID,systemlog.ADID,systemlog.CTime,systemlog.NIP,systemlog.CityName,systemlog.UserName FROM systemlog WHERE systemlog.SLID =10000000;
INSERT INTO systemlog7(systemlog7.SLID,systemlog7.CODE,systemlog7.AID,systemlog7.ADID,systemlog7.CTime,systemlog7.NIP,systemlog7.CityName,systemlog7.UserName) SELECT systemlog.SLID,systemlog.CODE,systemlog.AID,systemlog.ADID,systemlog.CTime,systemlog.NIP,systemlog.CityName,systemlog.UserName FROM systemlog WHERE systemlog.SLID =12000000;
INSERT INTO systemlog8(systemlog8.SLID,systemlog8.CODE,systemlog8.AID,systemlog8.ADID,systemlog8.CTime,systemlog8.NIP,systemlog8.CityName,systemlog8.UserName) SELECT systemlog.SLID,systemlog.CODE,systemlog.AID,systemlog.ADID,systemlog.CTime,systemlog.NIP,systemlog.CityName,systemlog.UserName FROM systemlog WHERE systemlog.SLID =14000000;

注意：以下很关键

3.原表进行重命名进行数据备份，再重新创建一张空表systemlog

CREATE TABLE IF NOT EXISTS `systemlog` (
   `SLID` INT(11) NOT NULL AUTO_INCREMENT,
   `Code` SMALLINT(6) NOT NULL,
   `AID` INT(11) NOT NULL DEFAULT '0',
   `ADID` INT(11) NOT NULL DEFAULT '0',
   `CTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
   `NIP` VARCHAR(20) DEFAULT NULL,
   `CityName` VARCHAR(8000) DEFAULT NULL,
   `UserName` VARCHAR(50) DEFAULT NULL,
   PRIMARY KEY (`SLID`),
   KEY `AID` (`AID`),
   KEY `CTime` (`CTime`),
   KEY `ADID` (`ADID`)
) TYPE=MERGE UNION=(systemlog1,systemlog2,systemlog3,systemlog4,systemlog5,systemlog6,systemlog7,systemlog8) INSERT_METHOD=LAST AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

4.TEST

每次更新修改删除 systemlog 相应的MYSQL会引导去分表动作

每次新增的话是数据进入第一个或者最后一个 myisam 表（取决于 insert_method 选项的值）自己测试进入的是最后一个

应用程序代码不需要改变