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;





















































阅读更多
上一篇MySQL5.7-show engine innodb status
想对作者说点什么? 我来说一句

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

关闭
关闭