MySQL分区----range

原创 2018年04月17日 10:42:13

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

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

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;





















































U1 学会U盘分区与启动

-
  • 1970年01月01日 08:00

mysql分区之range分区

随着互联网的发展,各方面的数据越来越多,从最近两年大数据越来越强的呼声中就可见一斑。 我们所做的项目虽算不上什么大项目,但是由于业务量的问题,数据也是相当的多。 数据一多,就很容易出现性能问题,而...
  • tuzongxun
  • tuzongxun
  • 2016-11-22 17:15:02
  • 2325

mysql 关于range 分区 的认识

  • superhosts
  • superhosts
  • 2014-05-22 20:05:07
  • 4451

mysql 分区 按 PARTITION BY RANGE (TO_DAYS(startTime))

to_days() Given a date date, returns a day number (the number of days since year 0). 给定一个date 日期,返...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2016-08-26 10:23:48
  • 1528

mysql分区表----column partition

author:skate time:2012/11/28 mysql分区----column partition   mysql从5.5开始支持column分区,也可以认为是range和l...
  • wyzxg
  • wyzxg
  • 2012-11-28 20:00:33
  • 4734

Mysql --分区表(5)Columns分区

COLUMNS分区COLUMNS分区是RANGE和LIST分区的变种。COLUMNS分区支持多列作为分区键进行分区 RANGE COLUNMS分区和LIST COLUMNS都支持非INT型列作为分区...
  • ashic
  • ashic
  • 2016-08-09 17:41:49
  • 1132

修改mysql hash 分区为range 分区,并删除一个分区

----将hash 分区修改为range 分区,并删除一个分区 mysql> ALTER TABLE orders_range  DROP PARTITION p0; ERROR 1512 (HY00...
  • royjj
  • royjj
  • 2016-01-05 21:22:12
  • 1475

MySQL数据表range分区例子,按年/按月

某些行业数据量的增长速度极快,随着数据库中数据量的急速膨胀,数据库的插入和查询效率越来越低。此时,除了程序代码和查询语句外,还得在数据库的结构上做点更改;在一个主读辅写的数据库中,当数据表数据超过10...
  • feicongcong
  • feicongcong
  • 2017-10-18 11:57:20
  • 385

MySQL分区之RANGE分区(续)

前一篇展示了用year(calltime)做分区函数的例子,为了能分区更细致,不只按年分,可以按日期分,改用to_days(calltime)函数,但是发现这个to_days函数分区扫描时有点问题。 ...
  • xtjsxtj
  • xtjsxtj
  • 2013-11-05 22:22:28
  • 1936

记录一次给mysql加日期分区的经历

使用range建立分区,先是在本地5.0.45版本mysql上建立,一直失败。 于是到测试服务器5.5.35上建立,也失败,而且错误提示信息模糊。 于是从网上最简单的做起,最后发现,跟表的主键有关...
  • shuwei003
  • shuwei003
  • 2015-02-13 17:32:50
  • 961
收藏助手
不良信息举报
您举报文章:MySQL分区----range
举报原因:
原因补充:

(最多只允许输入30个字)