ALTER TABLE `goods`  DROP INDEX `goods_sn_2`;
ALTER TABLE goods PARTITION BY RANGE (goods_id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (20000),
    PARTITION p2 VALUES LESS THAN (30000),
    PARTITION p3 VALUES LESS THAN (40000),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

ALTER TABLE goods PARTITION BY HASH(goods_id) PARTITIONS 10;

ALTER TABLE goods  PARTITION BY KEY (is_on_sale) PARTITIONS 2;

ALTER TABLE goods PARTITION BY HASH(YEAR(FROM_UNIXTIME(add_time))) PARTITIONS 4;
		
		

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

避免開發中經常拼接表，我採用一個一勞永逸的方法，建立一個 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;