Home | 簡體中文 | 繁體中文 | 雜文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Email

6.3. 新聞資料庫分表案例

這裡我通過一個新聞網站為例,解決分表的問題

避免開發中經常拼接表,我採用一個一勞永逸的方法,建立一個 news 表使用黑洞引擎,然後通過出發器將數據分流到匹配的表中。同時採用uuid替代數字序列,可以保證未來數年不會出現ID用盡。

		
CREATE TABLE IF NOT EXISTS `news` (
  `uuid` varchar(36) NOT NULL COMMENT '唯一ID',
  `title` varchar(50) NOT NULL COMMENT '新聞標題',
  `body` text NOT NULL COMMENT '新聞正文',
  `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '創建時間',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  `atime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '訪問時間',
  PRIMARY KEY (`uuid`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;
		
		

該表僅僅用於舉例,結構比較簡單。接下來創建年份分表,你也可以每個月一個表,根據你的許下靈活調整。表結構與上面的news表相同,注意 ENGINE=InnoDB。

		
CREATE TABLE IF NOT EXISTS `news_2012` (
  `uuid` varchar(36) NOT NULL COMMENT '唯一ID',
  `title` varchar(50) NOT NULL COMMENT '新聞標題',
  `body` text NOT NULL COMMENT '新聞正文',
  `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '創建時間',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  `atime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '訪問時間',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='news 表';

CREATE TABLE IF NOT EXISTS `news_2013` (
  `uuid` varchar(36) NOT NULL COMMENT '唯一ID',
  `title` varchar(50) NOT NULL COMMENT '新聞標題',
  `body` text NOT NULL COMMENT '新聞正文',
  `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '創建時間',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  `atime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '訪問時間',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='news 表';
		
		

uuid 索引表,主要的功能是通過uuid查詢出該記錄在那張表中。更好的方案是將數據放入solr中處理,包括標題與內容搜索等等。

		
CREATE TABLE `news_index` (
	`uuid` VARCHAR(36) NOT NULL,
	`tbl_name` VARCHAR(10) NOT NULL,
	PRIMARY KEY (`uuid`)
)
COMMENT='news uuid 索引表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

		
		

news_insert 過程,用於向目標表中插入數據,可以單獨call 但不建議。因為insert 遠比 call 更通用,要考慮移植性與通用性

		
DELIMITER //
CREATE DEFINER=`neo`@`%` PROCEDURE `news_insert`(IN `uuid` vARCHAR(36), IN `title` VARCHAR(50), IN `body` TEXT, IN `ctime` TIMESTAMP)
BEGIN
	if year(ctime) = '2012' then
		insert into news_2012(uuid,title,body,ctime) values(uuid,title, body, ctime);
	end if;
	if year(ctime) = '2013' then
		insert into news_2013(uuid,title,body,ctime) values(uuid,title, body, ctime);
	end if;
	insert into news_index values(uuid, year(ctime));
END//
DELIMITER ;
		
		

插入觸發器,負責獲取 uuid 然後調用存儲過程

		
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `news_before_insert` BEFORE INSERT ON `news` FOR EACH ROW BEGIN
	IF new.uuid is null or new.uuid = '' or length(new.uuid) != 36 THEN
		set new.uuid=uuid();
	END IF;
	call news_insert(new.uuid,new.title,new.body,new.ctime);
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;
		
		

這個觸發器用戶保護表中的 uuid 值不被修改。

		
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `news_before_update` BEFORE UPDATE ON `news_2013` FOR EACH ROW BEGIN
	set new.uuid = old.uuid;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;