mysql 数据库表分区

来源：互联网发布：移民澳洲知乎编辑：程序博客网时间：2024/06/05 06:03

CREATE TABLE IF NOT EXISTS `demo_range` (
`eventid` int(11) unsigned NOT NULL,
`event_sk` int(11) NOT NULL,
`product_sk` int(11) NOT NULL,
`date_sk` int(11) NOT NULL,
`deviceid` varchar(128) DEFAULT NULL,
`useridentifier` varchar(128) DEFAULT NULL,
`category` varchar(50) DEFAULT NULL,
`event` varchar(50) NOT NULL,
`label` varchar(50) DEFAULT NULL,
`attachment` varchar(512) DEFAULT NULL,
`clientdate` datetime NOT NULL,
`number` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (date_sk)
(PARTITION p_2014 VALUES LESS THAN (1828) ENGINE = InnoDB,
PARTITION p_2015 VALUES LESS THAN (2193) ENGINE = InnoDB,
PARTITION p_201616 VALUES LESS THAN (2375) ENGINE = InnoDB,
PARTITION p_2016712 VALUES LESS THAN (2559) ENGINE = InnoDB,
PARTITION p_201701 VALUES LESS THAN (2590) ENGINE = InnoDB,
PARTITION p_201702 VALUES LESS THAN (2618) ENGINE = InnoDB,
PARTITION p_201703 VALUES LESS THAN (2649) ENGINE = InnoDB,
PARTITION p_201704 VALUES LESS THAN (2679) ENGINE = InnoDB,
PARTITION p_201705 VALUES LESS THAN (2710) ENGINE = InnoDB,
PARTITION p_201706 VALUES LESS THAN (2740) ENGINE = InnoDB,
PARTITION p_201707 VALUES LESS THAN (2771) ENGINE = InnoDB,
PARTITION p_201708 VALUES LESS THAN (2802) ENGINE = InnoDB,
PARTITION p_201709 VALUES LESS THAN (2832) ENGINE = InnoDB,
PARTITION p_201710 VALUES LESS THAN (2863) ENGINE = InnoDB,
PARTITION p_201711 VALUES LESS THAN (2893) ENGINE = InnoDB,
PARTITION p_201712 VALUES LESS THAN (2924) ENGINE = InnoDB);

ALTER TABLE `demo_range`
ADD PRIMARY KEY (`eventid`,`date_sk`),
ADD KEY `date_sk` (`date_sk`);

ALTER TABLE `demo_range`

MODIFY `eventid` int(11) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

超大的表（十几亿条数据）被几天轻松紧张中解决了，内心还是当一下

mysql的小爬虫还是可以处理这些数据的。

如果数据量更多会用来做什么，继续分析处理数据吧加油mysql

0 0