Mysql Merge 引擎分表方式

测试表news表,数据量大小为150w左右,测试步骤如下

1.创建第一张表news1, nsid <750000的数据保存在news1;

CREATE TABLE `news3` (
`NSID` int(11) NOT NULL,
`NewsName` varchar(100) NOT NULL,
`Title` varchar(100) NOT NULL,
`Content` mediumtext NOT NULL,
`NID` int(11) NOT NULL,
`NIP` varchar(100) NOT NULL,
`Editor` varchar(50) NOT NULL,
`Status` smallint(6) NOT NULL,
`CityID` int(11) NOT NULL,
`CTime` datetime NOT NULL,
`OrderNum` int(11) NOT NULL,
`LogoID` int(11) NOT NULL,
`SignTag` varchar(50) NOT NULL,
`Summary` varchar(5000) DEFAULT NULL,
`Type` smallint(6) NOT NULL,
`PageNum` int(11) NOT NULL,
`Own` int(11) NOT NULL,
`KeyWord` varchar(200) DEFAULT NULL,
`WySupport` int(11) NOT NULL DEFAULT '0',
`NewEditor` varchar(20) DEFAULT '',
`Source` varchar(200) DEFAULT '',
`RemoteID` varchar(100) DEFAULT NULL,
`BatchId` int(11) NOT NULL DEFAULT '0',
`UTime` datetime DEFAULT NULL COMMENT '修改时间',
`isTop` int(11) NOT NULL DEFAULT '0',
`EndTime` datetime DEFAULT NULL COMMENT '置顶到期时间',
`IsComment` smallint(6) DEFAULT '0',
`BID` int(11) NOT NULL DEFAULT '0',
`VTimes` int(11) NOT NULL,
`Tags` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`NSID`),
KEY `CTime` (`CTime`),
KEY `OrderNum` (`OrderNum`),
KEY `NID` (`NID`),
KEY `NewsName` (`CTime`),
KEY `IsComment` (`BID`),
KEY `RemoteID` (`RemoteID`),
KEY `CityID` (`CityID`,`CTime`,`WySupport`,`Type`,`Status`)
) ENGINE=MyISAM AUTO_INCREMENT=635147 DEFAULT CHARSET=utf8

2.创建第二张表news4,nsid>=750000的数据保存在news4;

CREATE TABLE `news4` (
`NSID` int(11) NOT NULL,
`NewsName` varchar(100) NOT NULL,
`Title` varchar(100) NOT NULL,
`Content` mediumtext NOT NULL,
`NID` int(11) NOT NULL,
`NIP` varchar(100) NOT NULL,
`Editor` varchar(50) NOT NULL,
`Status` smallint(6) NOT NULL,
`CityID` int(11) NOT NULL,
`CTime` datetime NOT NULL,
`OrderNum` int(11) NOT NULL,
`LogoID` int(11) NOT NULL,
`SignTag` varchar(50) NOT NULL,
`Summary` varchar(5000) DEFAULT NULL,
`Type` smallint(6) NOT NULL,
`PageNum` int(11) NOT NULL,
`Own` int(11) NOT NULL,
`KeyWord` varchar(200) DEFAULT NULL,
`WySupport` int(11) NOT NULL DEFAULT '0',
`NewEditor` varchar(20) DEFAULT '',
`Source` varchar(200) DEFAULT '',
`RemoteID` varchar(100) DEFAULT NULL,
`BatchId` int(11) NOT NULL DEFAULT '0',
`UTime` datetime DEFAULT NULL COMMENT '修改时间',
`isTop` int(11) NOT NULL DEFAULT '0',
`EndTime` datetime DEFAULT NULL COMMENT '置顶到期时间',
`IsComment` smallint(6) DEFAULT '0',
`BID` int(11) NOT NULL DEFAULT '0',
`VTimes` int(11) NOT NULL,
`Tags` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`NSID`),
KEY `CTime` (`CTime`),
KEY `OrderNum` (`OrderNum`),
KEY `NID` (`NID`),
KEY `NewsName` (`CTime`),
KEY `IsComment` (`BID`),
KEY `RemoteID` (`RemoteID`),
KEY `CityID` (`CityID`,`CTime`,`WySupport`,`Type`,`Status`)
) ENGINE=MyISAM AUTO_INCREMENT=635147 DEFAULT CHARSET=utf8

3.创建第三张表news34,这张表为merge表.

CREATE TABLE `news34` (
`NSID` INT(11) NOT NULL,
`NewsName` VARCHAR(100) NOT NULL,
`Title` VARCHAR(100) NOT NULL,
`Content` MEDIUMTEXT NOT NULL,
`NID` INT(11) NOT NULL,
`NIP` VARCHAR(100) NOT NULL,
`Editor` VARCHAR(50) NOT NULL,
`Status` SMALLINT(6) NOT NULL,
`CityID` INT(11) NOT NULL,
`CTime` DATETIME NOT NULL,
`OrderNum` INT(11) NOT NULL,
`LogoID` INT(11) NOT NULL,
`SignTag` VARCHAR(50) NOT NULL,
`Summary` VARCHAR(5000) DEFAULT NULL,
`Type` SMALLINT(6) NOT NULL,
`PageNum` INT(11) NOT NULL,
`Own` INT(11) NOT NULL,
`KeyWord` VARCHAR(200) DEFAULT NULL,
`WySupport` INT(11) NOT NULL DEFAULT '0',
`NewEditor` VARCHAR(20) DEFAULT '',
`Source` VARCHAR(200) DEFAULT '',
`RemoteID` VARCHAR(100) DEFAULT NULL,
`BatchId` INT(11) NOT NULL DEFAULT '0',
`UTime` DATETIME DEFAULT NULL COMMENT '修改时间',
`isTop` INT(11) NOT NULL DEFAULT '0',
`EndTime` DATETIME DEFAULT NULL COMMENT '置顶到期时间',
`IsComment` SMALLINT(6) DEFAULT '0',
`BID` INT(11) NOT NULL DEFAULT '0',
`VTimes` INT(11) NOT NULL,
`Tags` VARCHAR(1000) DEFAULT NULL,
PRIMARY KEY (`NSID`),
KEY `CTime` (`CTime`),
KEY `OrderNum` (`OrderNum`),
KEY `NID` (`NID`),
KEY `NewsName` (`CTime`),
KEY `IsComment` (`BID`),
KEY `RemoteID` (`RemoteID`),
KEY `CityID` (`CityID`,`CTime`,`WySupport`,`Type`,`Status`)
) TYPE=MERGE UNION=(news3,news4) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;

4.插入测试数据

INSERT INTO news3
SELECT * FROM news WHERE nsid < 750000

INSERT INTO news4
SELECT * FROM news WHERE nsid >= 750000

5.news表与news34表进行测试比较

SELECT * FROM NEWS34 WHERE cityid = 14 AND ctime LIKE '%2014%';
SELECT * FROM NEWS WHERE cityid = 14 AND ctime LIKE '%2014%';

SELECT * FROM NEWS34 WHERE cityid = 14 AND TYPE = 3 ORDER BY nsid;
SELECT * FROM NEWS WHERE cityid = 14 AND TYPE = 3 ORDER BY nsid;

SELECT * FROM NEWS34 WHERE cityid = 14 AND TYPE = 2 AND source = '吉屋网络整理' AND vtimes <10 ORDER BY nsid DESC;
SELECT * FROM NEWS WHERE cityid = 14 AND TYPE = 2 AND source = '吉屋网络整理' AND vtimes <10 ORDER BY nsid DESC;

SELECT * FROM NEWS34 WHERE cityid = 12 AND TYPE = 2 AND source = '吉屋网络整理' AND editor='sjt' AND STATUS = 2 AND keyword LIKE '%吉屋%' AND vtimes <20 ORDER BY nsid DESC;
SELECT * FROM NEWS WHERE cityid = 12 AND TYPE = 2 AND source = '吉屋网络整理' AND editor='sjt' AND STATUS = 2 AND keyword LIKE '%吉屋%' AND vtimes <20 ORDER BY nsid DESC;

第一组sql :myIsam表,也就是news表的执行速度稍微快一点

Mysql Merge 引擎分表方式