MySQL5.6.17分区表

ertwer

环境说明：
操作系统：Windows7 旗舰版 SP1 64位，Inte I5-2520M CPU，4G内存
数据库：MySQL Community Server 5.6.17

参考MySQL官方文档：
http://dev.mysql.com/doc/refman/5.6/en/partitioning.html
准备工作
1、查看数据库的信息
了解当前的Mysql数据库的版本和平台以及字符集等相关信息

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> status -------------- mysql  Ver 14.14 Distrib 5.6.17, for Win64 (x86_64) Connection id:          4 Current database: Current user:           root@localhost SSL:                    Not in use Using delimiter:        ; Server version:         5.6.17-log MySQL Community Server (GPL) Protocol version:       10 Connection:             localhost via TCP/IP Server characterset:    utf8 Db     characterset:    utf8 Client characterset:    gbk Conn.  characterset:    gbk TCP port:               3306 Uptime:                 37 min 55 sec Threads: 2  Questions: 35  Slow queries: 2  Opens: 294  Flush tables: 1   Open tables: 286  Queries per second avg: 0.015 --------------

2、检查是否支持分区

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> show plugins; +----------------------------+----------+--------------------+---------+---------+ | Name                       | Status   | Type               | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     | | mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     | | sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     | | CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     | | InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | // 中间查询出的部分plugin省略                                                  | | PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | +----------------------------+----------+--------------------+---------+---------+ 42 rows in set (0.01 sec)

    注意：之前版本的MySQL有have_partitioning这个变量，可以查看是否支持分区，但MySQL 5.6.1开始该变量被废弃并经被删除。

    MySQL5.6.17

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> select version(); +------------+ | version()  | +------------+ | 5.6.17-log | +------------+ 1 row in set (0.07 sec) mysql> show variables like 'have_partitioning'; Empty set (0.00 sec) mysql> show variables like 'have_part%'; Empty set (0.00 sec)

    MySQL5.5.19

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.19    | +-----------+ 1 row in set (0.00 sec) mysql> show variables like 'have_part%'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | have_partitioning | YES   | +-------------------+-------+ 1 row in set (0.00 sec)

    同样可以查询INFORMATION_SCHEMA.PLUGINS表来验证是否支持分区。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_TYPE = 'STORAGE ENGINE'; +--------------------+----------------+---------------+ | PLUGIN_NAME        | PLUGIN_VERSION | PLUGIN_STATUS | +--------------------+----------------+---------------+ | binlog             | 1.0            | ACTIVE        | | CSV                | 1.0            | ACTIVE        | | MEMORY             | 1.0            | ACTIVE        | | MyISAM             | 1.0            | ACTIVE        | | MRG_MYISAM         | 1.0            | ACTIVE        | | ARCHIVE            | 3.0            | ACTIVE        | | BLACKHOLE          | 1.0            | ACTIVE        | | FEDERATED          | 1.0            | DISABLED      | | InnoDB             | 5.6            | ACTIVE        | | PERFORMANCE_SCHEMA | 0.1            | ACTIVE        | | partition          | 1.0            | ACTIVE        | +--------------------+----------------+---------------+ 11 rows in set (0.03 sec)

    如果输出数据中无Active状态的partition插件，那么说明该MySQL版本不支持分区（partitioning support）。
    查询MySQL建表的命令的帮助信息

1 2 3 4 5 6 7

mysql> help create table; CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name     [(create_definition,...)]     [table_options]     [partition_options]     select_statement

RANGE分区
http://dev.mysql.com/doc/refman/5.6/en/partitioning-range.html

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE TABLE t_employees_range_part_001 (     id INT NOT NULL,     fname VARCHAR(30),     lname VARCHAR(30),     hired DATE NOT NULL DEFAULT '1970-01-01',     separated DATE NOT NULL DEFAULT '9999-12-31',     job_code INT NOT NULL,     store_id INT NOT NULL ) PARTITION BY RANGE (store_id) (     PARTITION p0 VALUES LESS THAN (6),     PARTITION p1 VALUES LESS THAN (11),     PARTITION p2 VALUES LESS THAN (16),     PARTITION p3 VALUES LESS THAN (21) );

业务示例

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

CREATE TABLE `t_domain_range_part` (   `id` int(11) NOT NULL DEFAULT 0 COMMENT '主键,自增',   `domain` varchar(1024) DEFAULT NULL COMMENT '域名',   `ipstr` varchar(1024) DEFAULT NULL COMMENT '对应IP',   `iplocal` varchar(8) DEFAULT NULL COMMENT '网内网外(0:网内; 1:网外; 2:网内+网外)',   `ipabroad` varchar(8) DEFAULT NULL COMMENT '境内境外(0:境内; 1:境外; 2:境内+境外)',   `sendtime` datetime DEFAULT NULL COMMENT '上报时间',   `domaintype` tinyint(3) DEFAULT '1' COMMENT ' 0：正常；1：涉黄信息；2：违法信息；3其他；',   `protocolid` varchar(4) DEFAULT '0001' COMMENT '协议类别, 0000:httpget；0001:httppost  0002:httpget+httppost',   `domainsource` varchar(16) DEFAULT NULL COMMENT '数据来源（1：北京，2：上海，3：广东，4：用户投诉，5：重拨数据）',   `accpoint` varchar(70) DEFAULT NULL COMMENT '网站类型 0为WAP;1为WWW;2为WAP+WWW',   `serviceip` varchar(150) NOT NULL COMMENT '解析入库的汇聚服务器IP地址',   `sourcename` varchar(150) DEFAULT NULL COMMENT '大区上报的原始ZIP包名称',   `domain_handle_flag` varchar(150) DEFAULT NULL COMMENT '大区上报的原始ZIP包标识',   `crawlflag` int(11) DEFAULT 2 COMMENT '主动爬取标识。0旁路采集；1特定域名拨测。',   `domainflag` int(11) DEFAULT 4 COMMENT '1 疑似送审 2 特定域名拨测 3 疑似钓鱼网站',   `phishing_flag_type` varchar(3) DEFAULT '0' COMMENT  '1:域名判疑似；2:内容判定疑似；3:域名、内容均判定为疑似',   `flag` tinyint(1) DEFAULT NULL COMMENT '处理标识，1:需人工审核; 2:二次过滤; 3:封堵域名重现; 4:审核中,无需处理',   `createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' )  ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='分区表-存储原始数据'    PARTITION BY RANGE(MONTH(createtime)) (     PARTITION p1 VALUES LESS THAN (3),     PARTITION p2 VALUES LESS THAN (5),     PARTITION p3 VALUES LESS THAN (7),     PARTITION p3 VALUES LESS THAN (9),     PARTITION p3 VALUES LESS THAN (11),     PARTITION p12 VALUES LESS THAN MAXVALUE ); --001:createtime timestamp --    ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed --  说明：分区函数不允许为常量、随机数或者与时区相关联的表达式，如RANGE(1970)、RANGE(rand()) -- 002:`id` int(11) PRIMARY KEY AUTO_INCREMENT --    ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function --    说明：这里先把id的主键属性(PRIMARY KEY AUTO_INCREMENT)给去掉，后面“Partitioning Keys, Primary Keys, and Unique Keys”部分再分析

HASH分区

LIST分区

KEY分区

COLUMNS分区

子分区

MySQL分区如何处理NULL

分区管理