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表的执行速度稍微快一点

第二组sql:这组sql查询条件稍微复杂点,有where和order,这是merge表的速度要快于myIsam表

第三组sql:

第四组:

测试结论:

整体上myIsam表和merge表的查询效率差距很小;

Mysql Merge 引擎分表方式,古老的榕树,5-wow.com