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(calltime)) (
  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','13302117301','01','13089141234',125);
insert into scpcdr values('2013-10-18 10:12:13','13302117302','01','13089141234',125);
insert into scpcdr values('2014-10-18 10:12:13','13302117303','01','13089141234',125);
insert into scpcdr values('2015-10-18 10:12:13','13302117304','01','13089141234',125);
insert into scpcdr values('2016-10-18 10:12:13','13302117305','01','13089141234',125);
insert into scpcdr values('2017-10-18 10:12:13','13302117306','01','13089141234',125);
insert into scpcdr values('2018-10-18 10:12:13','13302117307','01','13089141234',125);
insert into scpcdr values('2019-10-18 10:12:13','13302117308','01','13089141234',125);
insert into scpcdr values('2020-10-18 10:12:13','13302117309','01','13089141234',125);

select * from scpcdr order by calltime;
calltime           msisdn calltype othercallno calldura
2012-10-18 10:12:13 13302117301 01 13089141234 125
2013-10-18 10:12:13 13302117302 01 13089141234 125
2014-10-18 10:12:13 13302117303 01 13089141234 125
2015-10-18 10:12:13 13302117304 01 13089141234 125
2016-10-18 10:12:13 13302117305 01 13089141234 125
2017-10-18 10:12:13 13302117306 01 13089141234 125
2018-10-18 10:12:13 13302117307 01 13089141234 125
2019-10-18 10:12:13 13302117308 01 13089141234 125
2020-10-18 10:12:13 13302117309 01 13089141234 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','13302117309','01','13089141234',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','13302117309','01','13089141234',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 13302117309 01 13089141234 125

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