MySQL分区之RANGE分区

[笑天居士]

环境：
mysql> select version()\G
*************************** 1. row ***************************
version(): 5.5.21-log
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%partition%'\G
*************************** 1. row ***************************
Variable_name: have_partitioning
Value: YES
1 row in set (0.00 sec)


主要应用场景：

RANGE分区主要用于日期列的分区
例如历史通话清单表，可以根据年月来分区清单记录
如下是对scpcdr表进行分区
create table scpcdr(
calltime datetime not null,
msisdn char(11) not null,
calltype char(2) not null,
othercallno char(32) not null,
calldura integer not null,
key scpcdridx1(calltime,msisdn)
)engine=innodb
partition by range(year(datetime)) (
partition p2013 values less than (2014),
partition p2014 values less than (2015),
partition p2015 values less than (2016),
partition p2016 values less than (2017),
partition p2017 values less than (2018),
partition p2018 values less than (2019),
partition p2019 values less than (2020),
partition p2020 values less than (2021)
);

SELECT table_schema,table_name,partition_name,
PARTITION_ORDINAL_POSITION,PARTITION_METHOD,
PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE table_name='scpcdr';
图片

insert into scpcdr values('2012-10-18 10:12:13','13602447301','01','13189149999',125);
insert into scpcdr values('2013-10-18 10:12:13','13602447302','01','13189149999',125);
insert into scpcdr values('2014-10-18 10:12:13','13602447303','01','13189149999',125);
insert into scpcdr values('2015-10-18 10:12:13','13602447304','01','13189149999',125);
insert into scpcdr values('2016-10-18 10:12:13','13602447305','01','13189149999',125);
insert into scpcdr values('2017-10-18 10:12:13','13602447306','01','13189149999',125);
insert into scpcdr values('2018-10-18 10:12:13','13602447307','01','13189149999',125);
insert into scpcdr values('2019-10-18 10:12:13','13602447308','01','13189149999',125);
insert into scpcdr values('2020-10-18 10:12:13','13602447309','01','13189149999',125);

select * from scpcdr order by calltime;
calltime msisdn calltype othercallno calldura
2012-10-18 10:12:13 13602447301 01 13189149999 125
2013-10-18 10:12:13 13602447302 01 13189149999 125
2014-10-18 10:12:13 13602447303 01 13189149999 125
2015-10-18 10:12:13 13602447304 01 13189149999 125
2016-10-18 10:12:13 13602447305 01 13189149999 125
2017-10-18 10:12:13 13602447306 01 13189149999 125
2018-10-18 10:12:13 13602447307 01 13189149999 125
2019-10-18 10:12:13 13602447308 01 13189149999 125
2020-10-18 10:12:13 13602447309 01 13189149999 125

便于对scpcdr表管理，如果要删除2013年的数据，我们就不需要执行：
delete from sales where calltime>= '2013-01-01 00:00:00' and date<'2013-12-31 23:59:59'
而只需删除2013年数据所在的分区即可
alter table scpcdr drop partition p2013;


mysql> explain partitions
-> select * from scpcdr
-> where (calltime >= '2013-01-01 00:00:00') and (calltime <= '2013-12-31 23:59:59')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: scpcdr
partitions: p2013
type: range
possible_keys: scpcdridx1
key: scpcdridx1
key_len: 8
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)

这里SQL优化器会只查询P2013分区，提高查询性能

注意：这里的查询条件里必须明确指出calltime字段，如果用year(calltime)=2013这样的条件，那么还是会遍历所有分区，还有对RANGE分区的查询，优化器只能对year(),to_days(),to_seconds()和unix_timestamp()这类函数进行优化选择

mysql> explain partitions select * from scpcdr where year(calltime) = 2013\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: scpcdr
partitions: p2013,p2014,p2015,p2016,p2017,p2018,p2019,p2020
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9
Extra: Using where
1 row in set (0.00 sec)

如果一条记录不属于任何分区会提示出错：
mysql> insert into scpcdr values('2021-10-18 10:12:13','13602447309','01','13189149999',125);
ERROR 1526 (HY000): Table has no partition for value 2021

这时需要新增加分区：
mysql> alter table scpcdr add partition( partition p2021 values less than maxvalue );
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0

增加分区，分区表达式的值，必须是递增的，不能在已有分区前面插入分区，只能递增新的分区

SELECT table_schema,table_name,partition_name,
PARTITION_ORDINAL_POSITION,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,
TABLE_ROWS FROM information_schema.PARTITIONS WHERE table_name='scpcdr'

table_schema table_name partition_name PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS
test scpcdr p2013 1 RANGE year(calltime) 2014 2
test scpcdr p2014 2 RANGE year(calltime) 2015 1
test scpcdr p2015 3 RANGE year(calltime) 2016 1
test scpcdr p2016 4 RANGE year(calltime) 2017 1
test scpcdr p2017 5 RANGE year(calltime) 2018 1
test scpcdr p2018 6 RANGE year(calltime) 2019 1
test scpcdr p2019 7 RANGE year(calltime) 2020 1
test scpcdr p2020 8 RANGE year(calltime) 2021 1
test scpcdr p2021 9 RANGE year(calltime) MAXVALUE 0

mysql> insert into scpcdr values('2021-10-18 10:12:13','13602447309','01','13189149999',125);
Query OK, 1 row affected (0.00 sec)

select * from scpcdr
where (calltime >= '2021-10-18 00:00:00') and (calltime <= '2021-10-18 23:59:59');

calltime msisdn calltype othercallno calldura
2021-10-18 10:12:13 13602447309 01 13189149999 125

对RANGE分区的查询，分区表达的选择与查询条件的写法必须相匹配，建议这个最好explain解释查询一下看看查询性能

[rucypli]

[ybgba]

很详细，感谢。