CREATETABLE `list` ( `id` int(11) unsigned NOTNULL AUTO_INCREMENT, `ctime` timestampNOTNULLDEFAULTCURRENT_TIMESTAMP, PRIMARY KEY (`id`,`ctime`) ) ENGINE=InnoDB AUTO_INCREMENT=175496881DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 PARTITIONBYRANGE (UNIX_TIMESTAMP(ctime)) (PARTITION p201501 VALUES LESS THAN (1420041600) ENGINE = InnoDB, PARTITION p201601 VALUES LESS THAN (1451577600) ENGINE = InnoDB, PARTITION p201701 VALUES LESS THAN (1483200000) ENGINE = InnoDB, PARTITION p201801 VALUES LESS THAN (1517414400) ENGINE = InnoDB, PARTITION p201901 VALUES LESS THAN (1548950400) ENGINE = InnoDB);
这样的分表也是极为常见的,但是如果业务上都是以天为维度的,类似的执行sql:
1
select DATE_FORMAT(ctime, "%Y-%m-%d"),count(1) as total from list where ctime >='2018-12-16'groupby DATE_FORMAT(ctime, "%Y-%m-%d");
这样包含函数的sql语句执行的效率并不会很高,因此,我们可以这样设计表格:
1 2 3 4 5 6 7 8 9 10 11 12
CREATETABLE `list` ( `id` int(11) unsigned NOTNULL AUTO_INCREMENT, `ctime` timestampNOTNULLDEFAULTCURRENT_TIMESTAMP, `in_date` dateNOTNULLDEFAULT'0000-00-00' COMMENT '冗余的入库时间', PRIMARY KEY (`eid`,`in_date`) ) ENGINE=InnoDB AUTO_INCREMENT=176000000DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 PARTITIONBYRANGE (to_days(in_date)) ( PARTITION p201501 VALUES LESS THAN (to_days('2015-01-01')) ENGINE = InnoDB, PARTITION p201601 VALUES LESS THAN (to_days('2016-01-01')) ENGINE = InnoDB, PARTITION p201701 VALUES LESS THAN (to_days('2017-01-01')) ENGINE = InnoDB, PARTITION p201801 VALUES LESS THAN (to_days('2018-01-01')) ENGINE = InnoDB, PARTITION p201901 VALUES LESS THAN (to_days('2019-01-01')) ENGINE = InnoDB);
sql修改为:
1
select in_date,count(1) as total from list where in_date >='2018-12-16'groupby in_date;
这种方式执行的效率比第一种来的快将近3倍!!!
分区类型
RANGE:基于一个给定连续区间范围,把数据分配到不同的分区
1 2 3 4 5 6 7 8 9 10
createtable list ( id int unsigned notnull AUTO_INCREMENT, age int, PRIMARY KEY(id, age) )engine = innodb partitionbyrange(age) ( partition p1 values less than (10), partition p2 values less than (20), partition p3 values less than MAXVALUE );
LIST:类似 RANGE 分区,区别在 LIST 分区是基于枚举出的值列表分区,RANGE 是局域给定的连续区间范围分区。
1 2 3 4 5 6 7 8 9 10
createtable list ( id int unsigned notnull AUTO_INCREMENT, area int, PRIMARY KEY(id, area) )engine innodb partitionby list(area) ( partition bj valuesin (1), partition ah valuesin (2), partition xb valuesin (4,5,6) );
HASH:基于给定的分区个数,把数据分配到不同的分区。
1 2 3 4 5 6
createtable list ( id int unsigned notnull AUTO_INCREMENT, birthday date, PRIMARY KEY(id, birthday) )engine innodb partitionby hash (month(birthday)) patitions 12;
KEY:按照某个字段取余
1 2 3 4 5 6
createtable list ( id int unsigned notnull AUTO_INCREMENT, limit int, PRIMARY KEY(id, limit) )engine = innodb partitionby key (limit) partitions 5;