MySQL分区----range

最近公司新项目上线,领导说数据量较大,后期也会有大量频繁的查询,让我按月份划分建个分区表。

首先查看一下目标数据库有没有分区表

mysql> select * from information_schema.PARTITIONS where table_schema='wxy' \G;
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: wxy
                   TABLE_NAME: DVDs
               PARTITION_NAME: NULL
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: NULL
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: NULL
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: NULL
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL

                   TABLE_ROWS: 8
               AVG_ROW_LENGTH: 2048
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2018-04-09 15:55:17
                  UPDATE_TIME: 2018-04-09 15:55:17
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: 
              TABLESPACE_NAME: NULL

可以看到该表不是分区表,下面看一下表结构

mysql> show create table DVDs;
+----------+---------------+------+
| Table       | Create Table  |DVDs | 
+------------------------------------------------+
 CREATE TABLE `DVDs` (
  `ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `Name` varchar(60) NOT NULL,
  `NumDisks` tinyint(4) NOT NULL DEFAULT '1',
  `RatingID` varchar(4) NOT NULL,
  `StatID` char(3) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |

这里ID是主键,但是没有时间类型的字段,接下来添加createTime 和updateTime字段,分别用不同的时间类型看看有什么区别;

mysql> alter table DVDs add createTime timestamp NOT NULL;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table DVDs add updateTime date NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> desc DVDs;
+------------+-------------+------+-----+-------------------+-----------------------------+
| Field      | Type        | Null | Key | Default           | Extra                       |
+------------+-------------+------+-----+-------------------+-----------------------------+
| ID         | smallint(6) | NO   | PRI | NULL              | auto_increment              |
| Name       | varchar(60) | NO   |     | NULL              |                             |
| NumDisks   | tinyint(4)  | NO   |     | 1                 |                             |
| RatingID   | varchar(4)  | NO   |     | NULL              |                             |
| StatID     | char(3)     | NO   |     | NULL              |                             |
| createTime | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updateTime | date        | NO   |     | NULL              |                             |

+------------+-------------+------+-----+-------------------+-----------------------------+

7 rows in set (0.00 sec)

接下来按时间分区,分区的类型有range分区,list分区,hash分区,key分区;

mysql> alter table DVDs partition by range(to_days(updateTime))(partition p1 values less than (to_days('2018-02-01')),partition p2 values less than (to_days('2018-03-01')));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

分区字段必须包含在主键字段内,只能通过创建复合主键解决该问题,或者用主键作为分区字段;

mysql> alter table DVDs drop primary key;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

创建复合主键之前需要先删除原来的主键,该表是自增主键,需要先删除自增长;

mysql> alter table DVDs change ID ID smallint(6) NOT NULL;

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'updateTime' at row 1

不正确的时间类型,5.7的sql_mode默认为严格模式,先看一下sql_mode

mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

由于sql模式的限制,时间字段不允许为0值,所以去掉该模式;


mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter table DVDs change ID ID smallint(6) NOT NULL;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

修改主键字段属性成功;

mysql> alter table DVDs drop primary key;
Query OK, 8 rows affected (0.03 sec)

Records: 8  Duplicates: 0  Warnings: 0

删除主键成功;接下来创建复合主键;

mysql> alter table DVDs add primary key(ID,updateTime);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc DVDs;
+------------+-------------+------+-----+-------------------------+--------------------------------------+
| Field            | Type            | Null | Key  | Default                          | Extra                                                |
+------------+-------------+------+-----+-------------------------+--------------------------------------+
| ID                | smallint(6)   | NO   | PRI  | NULL                             |                                                         |
| Name          | varchar(60)  | NO   |        | NULL                             |                                                         |
| NumDisks   | tinyint(4)      | NO   |        | 1                                    |                                                         |
| RatingID      | varchar(4)    | NO   |        | NULL                             |                                                         |
| StatID          | char(3)         | NO   |        | NULL                             |                                                         |
| createTime  | timestamp   | NO   |        | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updateTime | date             | NO   | PRI | NULL                              |                                                        |
+-------------+-------------+------+-----+-------------------------+-------------------------------------+
7 rows in set (0.00 sec)

接下来创建分区

mysql> alter table DVDs partition by range(to_days(updateTime))(partition p1 values less than (to_days('2018-02-01')),partition p2 values less than (to_days('2018-03-01')));
Query OK, 8 rows affected (0.03 sec)

Records: 8  Duplicates: 0  Warnings: 0

查看表结构

+----------+---------------+------+
| Table       | Create Table  |DVDs | 

+------------------------------------------------+

|CREATE TABLE `DVDs` (
  `ID` smallint(6) NOT NULL,
  `Name` varchar(60) NOT NULL,
  `NumDisks` tinyint(4) NOT NULL DEFAULT '1',
  `RatingID` varchar(4) NOT NULL,
  `StatID` char(3) NOT NULL,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updateTime` date NOT NULL,
  PRIMARY KEY (`ID`,`updateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(updateTime))
(PARTITION p1 VALUES LESS THAN (737091) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (737119) ENGINE = InnoDB) */ |

时间分区创建成功。

删除分区

mysql> alter table DVDs remove partitioning;
Query OK, 8 rows affected (0.02 sec)

Records: 8  Duplicates: 0  Warnings: 0

如果要用timestamp类型的日期,需要修改to_days为unix_timestamp;





















































阅读更多

MySQL分区RANGE分区

11-05

环境:rnmysql> select version()\Grn*************************** 1. row ***************************rnversion(): 5.5.21-logrn1 row in set (0.00 sec)rnrnmysql> SHOW VARIABLES LIKE '%partition%'\Grn*************************** 1. row ***************************rnVariable_name: have_partitioningrn Value: YESrn1 row in set (0.00 sec)rnrn rn主要应用场景:rn rnRANGE分区主要用于日期列的分区rn 例如历史通话清单表,可以根据年月来分区清单记录rn如下是对scpcdr表进行分区rncreate table scpcdr(rn calltime datetime not null,rn msisdn char(11) not null,rn calltype char(2) not null,rn othercallno char(32) not null,rn calldura integer not null,rn key scpcdridx1(calltime,msisdn)rn)engine=innodbrn partition by range(year(datetime)) (rn partition p2013 values less than (2014),rn partition p2014 values less than (2015),rn partition p2015 values less than (2016),rn partition p2016 values less than (2017),rn partition p2017 values less than (2018),rn partition p2018 values less than (2019),rn partition p2019 values less than (2020),rn partition p2020 values less than (2021)rn);rnrnSELECT table_schema,table_name,partition_name,rn PARTITION_ORDINAL_POSITION,PARTITION_METHOD,rn PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWSrn FROM information_schema.PARTITIONSrn WHERE table_name='scpcdr'; rn图片rnrninsert into scpcdr values('2012-10-18 10:12:13','13602447301','01','13189149999',125);rninsert into scpcdr values('2013-10-18 10:12:13','13602447302','01','13189149999',125);rninsert into scpcdr values('2014-10-18 10:12:13','13602447303','01','13189149999',125);rninsert into scpcdr values('2015-10-18 10:12:13','13602447304','01','13189149999',125);rninsert into scpcdr values('2016-10-18 10:12:13','13602447305','01','13189149999',125);rninsert into scpcdr values('2017-10-18 10:12:13','13602447306','01','13189149999',125);rninsert into scpcdr values('2018-10-18 10:12:13','13602447307','01','13189149999',125);rninsert into scpcdr values('2019-10-18 10:12:13','13602447308','01','13189149999',125);rninsert into scpcdr values('2020-10-18 10:12:13','13602447309','01','13189149999',125);rnrnselect * from scpcdr order by calltime;rncalltime msisdn calltype othercallno calldurarn2012-10-18 10:12:13 13602447301 01 13189149999 125rn2013-10-18 10:12:13 13602447302 01 13189149999 125rn2014-10-18 10:12:13 13602447303 01 13189149999 125rn2015-10-18 10:12:13 13602447304 01 13189149999 125rn2016-10-18 10:12:13 13602447305 01 13189149999 125rn2017-10-18 10:12:13 13602447306 01 13189149999 125rn2018-10-18 10:12:13 13602447307 01 13189149999 125rn2019-10-18 10:12:13 13602447308 01 13189149999 125rn2020-10-18 10:12:13 13602447309 01 13189149999 125rnrn便于对scpcdr表管理,如果要删除2013年的数据,我们就不需要执行:rndelete from sales where calltime>= '2013-01-01 00:00:00' and date<'2013-12-31 23:59:59'rn而只需删除2013年数据所在的分区即可rnalter table scpcdr drop partition p2013; rn rnrnmysql> explain partitionsrn -> select * from scpcdrrn -> where (calltime >= '2013-01-01 00:00:00') and (calltime <= '2013-12-31 23:59:59')\Grn*************************** 1. row ***************************rn id: 1rn select_type: SIMPLErn table: scpcdrrn partitions: p2013rn type: rangernpossible_keys: scpcdridx1rn key: scpcdridx1rn key_len: 8rn ref: NULLrn rows: 1rn Extra: Using wherern1 row in set (0.00 sec)rnrn这里SQL优化器会只查询P2013分区,提高查询性能 rnrn注意:这里的查询条件里必须明确指出calltime字段,如果用year(calltime)=2013这样的条件,那么还是会遍历所有分区,还有对RANGE分区的查询,优化器只能对year(),to_days(),to_seconds()和unix_timestamp()这类函数进行优化选择rn rnmysql> explain partitions select * from scpcdr where year(calltime) = 2013\Grn*************************** 1. row ***************************rn id: 1rn select_type: SIMPLErn table: scpcdrrn partitions: p2013,p2014,p2015,p2016,p2017,p2018,p2019,p2020rn type: ALLrnpossible_keys: NULLrn key: NULLrn key_len: NULLrn ref: NULLrn rows: 9rn Extra: Using wherern1 row in set (0.00 sec)rnrn如果一条记录不属于任何分区会提示出错:rnmysql> insert into scpcdr values('2021-10-18 10:12:13','13602447309','01','13189149999',125);rnERROR 1526 (HY000): Table has no partition for value 2021rnrn这时需要新增加分区:rnmysql> alter table scpcdr add partition( partition p2021 values less than maxvalue ); rnQuery OK, 0 rows affected (0.31 sec)rnRecords: 0 Duplicates: 0 Warnings: 0rnrn增加分区,分区表达式的值,必须是递增的,不能在已有分区前面插入分区,只能递增新的分区rnrnSELECT table_schema,table_name,partition_name,rnPARTITION_ORDINAL_POSITION,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,rnTABLE_ROWS FROM information_schema.PARTITIONS WHERE table_name='scpcdr'rnrntable_schema table_name partition_name PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWSrntest scpcdr p2013 1 RANGE year(calltime) 2014 2rntest scpcdr p2014 2 RANGE year(calltime) 2015 1rntest scpcdr p2015 3 RANGE year(calltime) 2016 1rntest scpcdr p2016 4 RANGE year(calltime) 2017 1rntest scpcdr p2017 5 RANGE year(calltime) 2018 1rntest scpcdr p2018 6 RANGE year(calltime) 2019 1rntest scpcdr p2019 7 RANGE year(calltime) 2020 1rntest scpcdr p2020 8 RANGE year(calltime) 2021 1rntest scpcdr p2021 9 RANGE year(calltime) MAXVALUE 0rnrnmysql> insert into scpcdr values('2021-10-18 10:12:13','13602447309','01','13189149999',125);rnQuery OK, 1 row affected (0.00 sec)rnrnselect * from scpcdrrn where (calltime >= '2021-10-18 00:00:00') and (calltime <= '2021-10-18 23:59:59');rnrncalltime msisdn calltype othercallno calldurarn2021-10-18 10:12:13 13602447309 01 13189149999 125rnrn对RANGE分区的查询,分区表达的选择与查询条件的写法必须相匹配,建议这个最好explain解释查询一下看看查询性能rn

没有更多推荐了,返回首页