當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL基本分区表

發布時間：2023/12/6 数据库 36 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL基本分区表小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

2019獨角獸企業重金招聘Python工程師標準>>>

準備工作
1、查看數據庫的信息

了解當前的Mysql數據庫的版本和平臺以及字符集等相關信息

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 secThreads: 2 Questions: 35 Slow queries: 2 Opens: 294 Flush tables: 1 Open tables: 286 Queries per second avg: 0.015 --------------

2、檢查是否支持分區
MySQL從5.1版本開始支持分區的功能。

? ? 注意：之前版本的MySQL有have_partitioning這個變量，可以查看是否支持分區，但MySQL 5.6.1開始該變量被廢棄并經被刪除。

? ? 同樣可以查詢INFORMATION_SCHEMA.PLUGINS表來驗證是否支持分區。

? ? 如果輸出數據中無Active狀態的partition插件，那么說明該MySQL版本不支持分區（partitioning ）。

分區類型
?? ?分區有利于管理非常大的表，分區鍵用于根據某個區間值（或者范圍值）、特定值列表或者HASH函數值執行數據的聚集，讓數據根據規則分布在不同的分區中。
?? ?RANGE分區：基于一個給定連續區間范圍，把數據分配到不同的分區。
?? ?LIST分區：類似RANGE分區，區別在LIST分區是基于枚舉出的值列表分區，RANGE是基于給定的連續區間范圍分區。
?? ?HASH分區：基于給定的分區個數，把數據分配到不同的分區。
?? ?KEY分區：類似于HASH分區。
?? ?Columns分區：支持多列分區。
?? ?注意：
?? ??? ?RANGE分區、LIST分區、HASH分區都要求分區鍵必須是INT類型，或者通過表達式返回INT類型。
?? ??? ?無論是哪種MySQL分區類型，要么分區表上沒有主鍵/唯一鍵，要么分區表的主鍵/唯一鍵都必須包含分區鍵，也就是說不能使用主鍵/唯一鍵字段之外的其他字段作為分區鍵盤。

-- 原始數據 CREATE TABLE `my_sample_bdmain` (`id` int(12) COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) DEFAULT NULL COMMENT '不可使用UNIQUE約束',`isip` int(4) DEFAULT NULL COMMENT '1:ip,2:域名',`isImport` int(4) DEFAULT NULL COMMENT '1:手動,2:審核',`isreopen` int(4) DEFAULT NULL COMMENT '1:封堵,2:解封',`blocktime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '時間',`reason` varchar(200) DEFAULT NULL COMMENT '原因',`remark` varchar(200) DEFAULT NULL COMMENT '備注',`isabroad` varchar(8) DEFAULT NULL COMMENT '歸屬情況(0-國內，1-國際)',`is_ip_block` int(11) DEFAULT '0' COMMENT '是否為IP封堵（默認為0;1為ip）',`block_user` varchar(16) DEFAULT NULL COMMENT '人員' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='測試數據（2009-2015），共計500W條'-- 創建測試使用的表 create table my_test_bdmain like my_sample_bdmain;-- 對于域名及封堵時間列分別添加索引 alter table my_test_bdmain add index sample_bdmain_sdmain_idx (dmain(255)); alter table my_test_bdmain add index sample_bdmain_blktime_idx (blocktime);-- 導入數據到test表，后續分區表測試與該表對比，原始數據不變 insert into my_test_bdmain select * from my_sample_bdmain;-- 原始數據 CREATE TABLE `my_test_sdmain` (`id` int(11) NOT NULL DEFAULT 0 COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) COMMENT '名稱, 不可使用UNIQUE約束',`ipstr` varchar(1024) COMMENT '對應IP',`iplocal` varchar(8) COMMENT '網內網外(0:網內; 1:網外; 2:網內+網外)',`ipabroad` varchar(8) COMMENT '境內境外(0:境內; 1:境外; 2:境內+境外)',`sendtime` datetime COMMENT '上報時間',`dmaintype` int(3) DEFAULT '1' COMMENT '0:正常;1:不良;2:違法:3其他',`dmainsource` varchar(16) COMMENT '來源(1、2、3、4、5、6)',`accpoint` varchar(70) COMMENT '網站類型(0為WAP;1為WWW;2為WAP+WWW)',`serviceip` varchar(150) NOT NULL COMMENT '匯聚服務器IP地址',`sourcename` varchar(150) COMMENT '原始ZIP包名稱',`dmain_handle_flag` varchar(150) COMMENT '原始ZIP包標識',`dmainflag` int(11) DEFAULT 4 COMMENT '1:疑似,2:特定名稱撥測,3:釣魚',`flag` int(1) COMMENT '處理標識(1:需處理; 2:過濾; 3:重現; 4:處理中)',`createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='測試數據（4月份），共80W條數據'

RANGE分區?

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

-- 示例分區表： CREATE TABLE t_employees_range_part (id INT NOT NULL,name VARCHAR(30),hired DATE NOT NULL DEFAULT '1970-01-01',dept_id INT NOT NULL comment '部門' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='測試RANAGE分區' PARTITION BY RANGE (dept_id) (PARTITION p1 VALUES LESS THAN (6),PARTITION p2 VALUES LESS THAN (11),PARTITION p3 VALUES LESS THAN (16),PARTITION p4 VALUES LESS THAN MAXVALUE );-- 按年范圍創建RANGE分區表 mysql> CREATE TABLE `t_bdmain_year_range_part` (`id` int(12) COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) DEFAULT NULL COMMENT '不可使用UNIQUE約束',`isip` int(4) DEFAULT NULL COMMENT '1:ip,2:名稱',`isImport` int(4) DEFAULT NULL COMMENT '1:人工,2:非人工',`isreopen` int(4) DEFAULT NULL COMMENT '1:封堵,2:解封',`blocktime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '日期時間類型，不可為timestamp',`reason` varchar(200) DEFAULT NULL COMMENT '原因',`remark` varchar(200) DEFAULT NULL COMMENT '備注',`isabroad` varchar(8) DEFAULT NULL COMMENT '歸屬情況(0-國內，1-國際)',`is_ip_block` int(11) DEFAULT '0' COMMENT '是否為IP封堵（默認為0;1表示IP）',`block_user` varchar(16) DEFAULT NULL COMMENT '人員') ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按年份創建的RANGE分區表'PARTITION BY RANGE(YEAR(blocktime)) (PARTITION p1 VALUES LESS THAN (2010),PARTITION p2 VALUES LESS THAN (2011),PARTITION p3 VALUES LESS THAN (2012),PARTITION p4 VALUES LESS THAN (2013),PARTITION p5 VALUES LESS THAN (2014),PARTITION p6 VALUES LESS THAN (2015),PARTITION p7 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (2.70 sec)-- 對于dmain及時間列分別添加索引 alter table t_bdmain_year_range_part add index year_part_bdmain_sdmain_idx (dmain(255)); alter table t_bdmain_year_range_part add index year_part_bdmain_blktime_idx (blocktime);mysql> SELECT TABLE_NAME, TABLE_SCHEMA, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTHFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA = 'big_data' AND TABLE_NAME ='t_bdmain_year_range_part'; +--------------------------+--------------+----------------+------------+----------------+-------------+ | TABLE_NAME | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +--------------------------+--------------+----------------+------------+----------------+-------------+ | t_bdmain_year_range_part | big_data | p1 | 0 | 0 | 16384 | | t_bdmain_year_range_part | big_data | p2 | 0 | 0 | 16384 | | t_bdmain_year_range_part | big_data | p3 | 0 | 0 | 16384 | | t_bdmain_year_range_part | big_data | p4 | 0 | 0 | 16384 | | t_bdmain_year_range_part | big_data | p5 | 0 | 0 | 16384 | | t_bdmain_year_range_part | big_data | p6 | 0 | 0 | 16384 | | t_bdmain_year_range_part | big_data | p7 | 0 | 0 | 16384 | +--------------------------+--------------+----------------+------------+----------------+-------------+ 7 rows in set (0.04 sec)-- 添加分區表數據 insert into t_bdmain_year_range_part select * from my_sample_bdmain;-- 驗證分區表 mysql> SELECT TABLE_NAME, TABLE_SCHEMA, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTHFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA = 'big_data' AND TABLE_NAME ='t_bdmain_year_range_part'; +--------------------------+--------------+----------------+------------+----------------+-------------+ | TABLE_NAME | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +--------------------------+--------------+----------------+------------+----------------+-------------+ | t_bdmain_year_range_part | big_data | p1 | 5055 | 314 | 1589248 | | t_bdmain_year_range_part | big_data | p2 | 377145 | 112 | 42549248 | | t_bdmain_year_range_part | big_data | p3 | 1949760 | 77 | 151715840 | | t_bdmain_year_range_part | big_data | p4 | 784214 | 78 | 61440000 | | t_bdmain_year_range_part | big_data | p5 | 558226 | 104 | 58294272 | | t_bdmain_year_range_part | big_data | p6 | 1179288 | 109 | 128614400 | | t_bdmain_year_range_part | big_data | p7 | 335621 | 145 | 48840704 | +--------------------------+--------------+----------------+------------+----------------+-------------+

????分區表在磁盤上的存儲結構為（在我本機上innodb_file_per_table=1）

????分區表于基本表查詢效率對比

????a. 普通表

-- \G后面如果有;號會有錯誤輸出ERROR: No query specified mysql> explain select * from my_test_bdmain where dmain = 'www.19lou.com'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: my_test_bdmaintype: ref possible_keys: sample_bdmain_sdmain_idxkey: sample_bdmain_sdmain_idxkey_len: 768ref: constrows: 3Extra: Using where 1 row in set (0.00 sec)mysql> mysql> explain select * from my_test_bdmain where blocktime >= '2013-01-01 00:00:00' and blocktime <= '2013-01-10 23:59:59'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: my_test_bdmaintype: range possible_keys: sample_bdmain_blktime_idxkey: sample_bdmain_blktime_idxkey_len: 6ref: NULLrows: 71422Extra: Using index condition 1 row in set (0.00 sec)-- 當blocktime的結束時間到某一特定的時刻時就會出現不走索引的情況 mysql> explain select * from my_test_bdmain where blocktime >= '2013-01-01 00:00:00' and blocktime <= '2013-08-31 23:59:59'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: my_test_bdmaintype: ALL possible_keys: sample_bdmain_blktime_idxkey: NULLkey_len: NULLref: NULLrows: 5020180Extra: Using where 1 row in set (0.03 sec)

? ? b. 分區表

mysql> explain select * from t_bdmain_year_range_part where dmain = 'www.19lou.com'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_bdmain_year_range_parttype: ref possible_keys: year_part_bdmain_sdmain_idxkey: year_part_bdmain_sdmain_idxkey_len: 768ref: constrows: 6Extra: Using where 1 row in set (0.00 sec)mysql> explain partitions select * from t_bdmain_year_range_partwhere dmain = 'www.19lou.com'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_bdmain_year_range_partpartitions: p1,p2,p3,p4,p5,p6,p7type: ref possible_keys: year_part_bdmain_sdmain_idxkey: year_part_bdmain_sdmain_idxkey_len: 768ref: constrows: 6Extra: Using where 1 row in set (0.00 sec)mysql> explain partitions select * from t_bdmain_year_range_partwhere blocktime >= '2013-01-01 00:00:00' and blocktime <= '2013-01-10 23:59:59'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_bdmain_year_range_partpartitions: p5type: range possible_keys: year_part_bdmain_blktime_idxkey: year_part_bdmain_blktime_idxkey_len: 6ref: NULLrows: 68108Extra: Using where 1 row in set (0.00 sec)-- 分區全掃描，如果是從blocktime查詢的時候是從2013-2014則掃描兩個分區 mysql> explain partitions select * from t_bdmain_year_range_partwhere blocktime >= '2013-01-01 00:00:00' and blocktime <= '2013-12-20 23:59:59'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_bdmain_year_range_partpartitions: p5type: ALL possible_keys: year_part_bdmain_blktime_idxkey: NULLkey_len: NULLref: NULLrows: 558226Extra: Using where 1 row in set (0.83 sec)

????創建月或天分區的分區表

-- 按月范圍創建的RANGE分區表 mysql> CREATE TABLE `t_bdmain_month_range_part` (`id` int(12) COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) DEFAULT NULL COMMENT '不可使用UNIQUE約束',`isip` int(4) DEFAULT NULL COMMENT '1:ip,2:名稱',`isImport` int(4) DEFAULT NULL COMMENT '1:人工,2:非人工',`isreopen` int(4) DEFAULT NULL COMMENT '1:封堵,2:解封',`blocktime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '日期時間類型，不可為timestamp',`reason` varchar(200) DEFAULT NULL COMMENT '原因',`remark` varchar(200) DEFAULT NULL COMMENT '備注',`isabroad` varchar(8) DEFAULT NULL COMMENT '歸屬情況(0-國內，1-國際)',`is_ip_block` int(11) DEFAULT '0' COMMENT '是否為IP封堵（默認為0;1為ip）',`block_user` varchar(16) DEFAULT NULL COMMENT '人員') ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按月創建的RANGE分區表'PARTITION BY RANGE(MONTH(blocktime)) (PARTITION p1 VALUES LESS THAN (2),PARTITION p2 VALUES LESS THAN (3),PARTITION p3 VALUES LESS THAN (4),PARTITION p4 VALUES LESS THAN (5),PARTITION p5 VALUES LESS THAN (6),PARTITION p6 VALUES LESS THAN (7),PARTITION p7 VALUES LESS THAN (8),PARTITION p8 VALUES LESS THAN (9),PARTITION p9 VALUES LESS THAN (10),PARTITION p10 VALUES LESS THAN (11),PARTITION p11 VALUES LESS THAN (12),PARTITION p12 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (10.69 sec)mysql> -- 對于名稱及封堵時間列分別添加索引 alter table t_bdmain_month_range_part add index month_part_bdmain_sdmain_idx (dmain(255)); alter table t_bdmain_month_range_part add index month_part_bdmain_blktime_idx (blocktime);mysql> explain select * from t_bdmain_month_range_part where dmain = 'www.19lou.com'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_bdmain_month_range_parttype: ref possible_keys: month_part_bdmain_sdmain_idxkey: month_part_bdmain_sdmain_idxkey_len: 768ref: constrows: 11Extra: Using where 1 row in set (0.00 sec)mysql> explain partitions select * from t_bdmain_month_range_partwhere blocktime >= '2013-01-01 00:00:00' and blocktime <= '2013-01-31 23:59:59'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_bdmain_month_range_partpartitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12type: range possible_keys: month_part_bdmain_blktime_idxkey: month_part_bdmain_blktime_idxkey_len: 6ref: NULLrows: 471106Extra: Using where 1 row in set (0.03 sec)mysql> explain partitions select * from t_bdmain_month_range_partwhere blocktime >= '2013-01-01 00:00:00' and blocktime <= '2013-10-31 23:59:59'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_bdmain_month_range_partpartitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12type: ALL possible_keys: month_part_bdmain_blktime_idxkey: NULLkey_len: NULLref: NULLrows: 5201742Extra: Using where 1 row in set (0.00 sec)-- 按日期范圍創建的RANGE分區表 mysql> CREATE TABLE `t_bdmain_day_range_part` (`id` int(12) COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) DEFAULT NULL COMMENT '不可使用UNIQUE約束',`isip` int(4) DEFAULT NULL COMMENT '1:ip,2:名稱',`isImport` int(4) DEFAULT NULL COMMENT '1:人工,2:非人工',`isreopen` int(4) DEFAULT NULL COMMENT '1:封堵,2:解封',`blocktime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '日期時間類型，不可為timestamp',`reason` varchar(200) DEFAULT NULL COMMENT '原因',`remark` varchar(200) DEFAULT NULL COMMENT '備注',`isabroad` varchar(8) DEFAULT NULL COMMENT '歸屬情況(0-國內，1-國際)',`is_ip_block` int(11) DEFAULT '0' COMMENT '是否為IP封堵（默認為0;1為ip）',`block_user` varchar(16) DEFAULT NULL COMMENT '人員') ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按時間范圍創建的RANGE分區表'PARTITION BY RANGE(TO_DAYS(blocktime)) (PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-01-01 00:00:00')),PARTITION p2 VALUES LESS THAN (TO_DAYS('2011-01-01 00:00:00')),PARTITION p3 VALUES LESS THAN (TO_DAYS('2012-01-01 00:00:00')),PARTITION p4 VALUES LESS THAN (TO_DAYS('2013-01-01 00:00:00')),PARTITION p5 VALUES LESS THAN (TO_DAYS('2014-01-01 00:00:00')),PARTITION p6 VALUES LESS THAN (TO_DAYS('2015-01-01 00:00:00')),PARTITION p7 VALUES LESS THAN MAXVALUE);Query OK, 0 rows affected (3.65 sec)-- 對于名稱及封堵時間列分別添加索引 alter table t_bdmain_day_range_part add index day_part_bdmain_sdmain_idx (dmain(255)); alter table t_bdmain_day_range_part add index day_part_bdmain_blktime_idx (blocktime);mysql> explain select * from t_bdmain_day_range_part where dmain = 'www.19lou.com'\G *************************** 1. row ***************************id: 1 select_type: SIMPLEtable: t_bdmain_day_range_parttype: ref possible_keys: day_part_bdmain_sdmain_idxkey: day_part_bdmain_sdmain_idxkey_len: 768ref: constrows: 4Extra: Using where 1 row in set (0.00 sec)mysql> explain partitions select * from t_bdmain_day_range_partwhere blocktime >= '2013-01-01 00:00:00' and blocktime <= '2014-08-31 23:59:59'\G *************************** 1. row ***************************id: 1 select_type: SIMPLEtable: t_bdmain_day_range_part partitions: p1,p2type: ALL possible_keys: day_part_bdmain_blktime_idxkey: NULLkey_len: NULLref: NULLrows: 4574554Extra: Using where 1 row in set (0.13 sec)--001:blocktime 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)，分區鍵必須要包含unique約束的一部分-- 按時間范圍創建的RANGE分區表 mysql> CREATE TABLE `t_sdmain_time_range_part` (`id` int(11) NOT NULL DEFAULT 0 COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) COMMENT '名稱, 不可使用UNIQUE約束',`ipstr` varchar(1024) COMMENT '對應IP',`iplocal` varchar(8) COMMENT '網內網外(0:網內; 1:網外; 2:網內+網外)',`ipabroad` varchar(8) COMMENT '境內境外(0:境內; 1:境外; 2:境內+境外)',`sendtime` datetime COMMENT '上報時間',`dmaintype` int(3) DEFAULT '1' COMMENT '0:正常;1:不良;2:違法:3其他',`dmainsource` varchar(16) COMMENT '來源(1、2、3、4、5、6)',`accpoint` varchar(70) COMMENT '網站類型 0為WAP;1為WWW;2為WAP+WWW',`serviceip` varchar(150) NOT NULL COMMENT '解析入庫的匯聚服務器IP地址',`sourcename` varchar(150) COMMENT '原始ZIP包名稱',`dmain_handle_flag` varchar(150) COMMENT '原始ZIP包標識',`dmainflag` int(11) DEFAULT 4 COMMENT '1 疑似違規;2 特定撥測;3 疑似釣魚',`flag` int(1) COMMENT '處理標識(1:需處理; 2:過濾; 3:重現; 4:處理中)',`createtime` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間,只能為timestamp' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按時間創建的RANGE分區表'PARTITION BY RANGE(UNIX_TIMESTAMP(createtime)) (PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2014-06-01 00:00:00')),PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2015-06-01 00:00:00')),PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),PARTITION p5 VALUES LESS THAN MAXVALUE);Query OK, 0 rows affected (3.65 sec)Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.) -- Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.)

??LIST分區

????List分區類似于Range分區，只是Range提供了范圍，List提供以逗號分割的數值列表。

????http://dev.mysql.com/doc/refman/5.6/en/partitioning-list.html

????示例程序

CREATE TABLE employees_list_part_by_int (id INT NOT NULL,name VARCHAR(30),hired DATE NOT NULL DEFAULT '1970-01-01',dept_id INT NOT NULL comment '部門' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='測試LIST分區-基于整型' PARTITION BY LIST(dept_id) (PARTITION p1 VALUES IN (1, 2, 3, 4, 5),PARTITION p2 VALUES IN (6, 7, 8, 9 ,10),PARTITION p3 VALUES IN (11, 12, 13, 14, 15),PARTITION p5 VALUES IN (16, 17, 18, 19, 20) ); -- 說明：List分區無MaxValue的概念，必須指定所有值CREATE TABLE employees_list_part_by_varchar (id INT NOT NULL,name VARCHAR(30),hired DATE NOT NULL DEFAULT '1970-01-01',job_code VARCHAR(30) DEFAULT NULL COMMENT '職務:manager,staff' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='測試LIST分區-基于VARCHAR類型' PARTITION BY LIST(job_code) (PARTITION p1 VALUES IN ('manager'),PARTITION p2 VALUES IN ('staff') ); ERROR 1697 (HY000): VALUES value for partition 'p1' must have type INT

? ? MySQL 5.6 provides support for LIST COLUMNS partitioning. This is a variant of LIST partitioning that enables you to use columns of types other than integer types for partitioning columns, as well as to use multiple columns as partitioning keys.

CREATE TABLE `t_sdmain_dmainsource_list_part` (`id` int(11) NOT NULL DEFAULT 0 COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) COMMENT '名稱, 不可使用UNIQUE約束',`ipstr` varchar(1024) COMMENT '對應IP',`iplocal` varchar(8) COMMENT '網內網外(0:網內; 1:網外; 2:網內+網外)',`ipabroad` varchar(8) COMMENT '境內境外(0:境內; 1:境外; 2:境內+境外)',`sendtime` datetime COMMENT '上報時間',`dmaintype` int(3) DEFAULT '1' COMMENT '0:正常;1:不良;2:違法:3其他',`dmainsource` int(3) COMMENT '來源(1、2、3、4、5、6)--必須為int類型',`accpoint` varchar(70) COMMENT '網站類型 0為WAP;1為WWW;2為WAP+WWW',`serviceip` varchar(150) NOT NULL COMMENT '解析入庫的匯聚服務器IP地址',`sourcename` varchar(150) COMMENT '原始ZIP包名稱',`dmain_handle_flag` varchar(150) COMMENT '原始ZIP包標識',`dmainflag` int(11) DEFAULT 4 COMMENT '1 疑似違規;2 特定撥測;3 疑似釣魚',`flag` int(1) COMMENT '處理標識(1:需處理; 2:過濾; 3:重現; 4:處理中)',`createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='根據大區類型創建的分區'PARTITION BY LIST(dmainsource) (PARTITION p1 VALUES IN (1),PARTITION p2 VALUES IN (2),PARTITION p3 VALUES IN (3),PARTITION p4 VALUES IN (4),PARTITION p5 VALUES IN (5),PARTITION p6 VALUES IN (6) );-- dmainsource = 1或者dmainsource in (1)或者dmainsource in ('1')均走分區 mysql> explain partitions select * from t_sdmain_dmainsource_list_partwhere dmainsource = 1\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_sdmain_dmainsource_list_partpartitions: p1type: ALL possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 221144Extra: Using where 1 row in set (0.00 sec)mysql> explain partitions select * from t_source_sdmain_201504 where dmainsource = 1\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_source_sdmain_201504partitions: NULLtype: ALL possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 700330Extra: Using where 1 row in set (0.00 sec)

COLUMNS分區

????包括RANGE COLUMNS 和 LIST COLUMNS兩種，分別為RANGE及LIST類型分區的變種。

??? 允許作為分區鍵數據類型如下：
?? ?整型類型
?? ??? ?支持TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), 以及BIGINT，其他數值類型不支持，如DECIMAL、FLOAT
?? ?日期類型
?? ??? ?支持DATE 和 DATETIME，不支持其他日期、時間類型
?? ?字符類型
?? ??? ?支持 CHAR, VARCHAR, BINARY, 以及 VARBINARY，不支持TEXT、 BLOB

????RANGE COLUMNS分區：????
????????RANGE columns分區類似于range分區，允許基于多列值定義分區范圍。另外，可以使用非整型類型來定義分區范圍。
?? ??? ?RANGE COLUMNS分區主要在如下方面區別于RANGE分區：
?? ??? ?a、RANGE COLUMNS不接受表達式，只允許提供列名稱；
?? ??? ?b、RANGE COLUMNS可以使用一列或多列；
?? ??? ??? ?RANGE Columns分區鍵的比較是基于元組的比較，也就是基于字段組的比較。
?? ??? ?c、RANGE COLUMNS不再限定分區鍵必須為整型（integer），字符串, DATE 以及 DATETIME同樣允許使用。
?? ??? ?創建RANGE COLUMNS分區表的語法如下：

CREATE TABLE partition_table_name PARTITIONED BY RANGE COLUMNS(column_list) (PARTITION partition_name VALUES LESS THAN (value_list)[,PARTITION partition_name VALUES LESS THAN (value_list)][,...] ) column_list:column_name[, column_name][, ...] value_list:value[, value][, ...] -- 示例如下 CREATE TABLE my_range_column_part_001 (a INT,b INT ) PARTITION BY RANGE COLUMNS(a,b) (PARTITION p0 VALUES LESS THAN (0,10),PARTITION p1 VALUES LESS THAN (10,20),PARTITION p2 VALUES LESS THAN (10,30),PARTITION p3 VALUES LESS THAN (10,35),PARTITION p4 VALUES LESS THAN (20,40),PARTITION p5 VALUES LESS THAN (MAXVALUE, MAXVALUE));mysql> CREATE TABLE my_range_column_part_002 (a INT,b INT,c CHAR(3),d INT)PARTITION BY RANGE COLUMNS(a,d,c) (PARTITION p0 VALUES LESS THAN (5,10,'ggg'),PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),PARTITION p2 VALUES LESS THAN (15,30,'sss'),PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)); Query OK, 0 rows affected (0.15 sec) CREATE TABLE my_range_column_part_003 (first_name VARCHAR(25),last_name VARCHAR(25),street_1 VARCHAR(30),street_2 VARCHAR(30),city VARCHAR(15),renewal DATE ) PARTITION BY RANGE COLUMNS(renewal) (PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),PARTITION pWeek_4 VALUES LESS THAN('2010-03-01') ); -- 不再需要講Date類型轉換成返回整型的表達式了

?LIST COLUMNS分區：
? ? ? ? 分區鍵只能使用列名稱，而不允許使用表達式。???

CREATE TABLE my_list_column_part_001 (first_name VARCHAR(25),last_name VARCHAR(25),street_1 VARCHAR(30),street_2 VARCHAR(30),city VARCHAR(15),renewal DATE ) PARTITION BY LIST COLUMNS(city) (PARTITION pRegion_1 VALUES IN('Oskarshamn', 'H?gsby', 'M?nster?s'),PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'V?stervik'),PARTITION pRegion_3 VALUES IN('N?ssj?', 'Eksj?', 'Vetlanda'),PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'V?xjo') );CREATE TABLE my_list_column_part_002 (first_name VARCHAR(25),last_name VARCHAR(25),street_1 VARCHAR(30),street_2 VARCHAR(30),city VARCHAR(15),renewal DATE ) PARTITION BY LIST COLUMNS(renewal) (PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03','2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10','2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17','2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24','2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28') );

? ?

HASH分區

?????http://dev.mysql.com/doc/refman/5.6/en/partitioning-hash.html

????指定分區字段，由MySQL數據庫來決定數據存儲到哪一個分區。

CREATE TABLE () PARTITION BY HASH (expr)

說明：
?? ?expr表達式需要返回一個整數值（must return a nonconstant, nonrandom integer value）。
?? ?每一次的insert或update操作都會執行expr的計算，因此負責的expr將會引發性能問題，特別是影響大量數據行的操作（如批量添加）。

CREATE TABLE employees_hash_part_by_int (id INT NOT NULL,name VARCHAR(30),hired DATE NOT NULL DEFAULT '1970-01-01',dept_id INT NOT NULL comment '部門' ) PARTITION BY HASH(dept_id) PARTITIONS 4;CREATE TABLE employees_hash_part_by_varchar (id INT NOT NULL,name VARCHAR(30),hired DATE NOT NULL DEFAULT '1970-01-01',dept_id INT NOT NULL comment '部門' ) PARTITION BY HASH(name) PARTITIONS 4; -- ERROR 1659 (HY000): Field 'name' is of a not allowed type for this type of partitioning -- 說明：若未指定PARTITIONS NUM語句，默認的partitions數量為1，如果省略NUM則報錯。

最有效的hash功能是基于單個表中值固定增加或減少的列。

MySQL通過HASH函數來確認數據應該存儲于哪一個分區，計算方式為N = MOD(expr, num)????

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4;

????當向表t1的col3這一列插入值'2015-08-20'時，該行數據存入哪一個分區按照如下語句確認：

MOD(YEAR('2015-08-20'),4) = MOD(2015,4) = 3

????業務示例

CREATE TABLE `t_sdmain_day_hash_part` (`id` int(11) NOT NULL DEFAULT 0 COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) COMMENT '名稱, 不可使用UNIQUE約束',`ipstr` varchar(1024) COMMENT '對應IP',`iplocal` varchar(8) COMMENT '網內網外(0:網內; 1:網外; 2:網內+網外)',`ipabroad` varchar(8) COMMENT '境內境外(0:境內; 1:境外; 2:境內+境外)',`sendtime` datetime COMMENT '上報時間',`dmaintype` int(3) DEFAULT '1' COMMENT '0:正常;1:不良;2:違法:3其他',`dmainsource` int(3) COMMENT '來源(1、2、3、4、5、6)--必須為int類型',`accpoint` varchar(70) COMMENT '網站類型 0為WAP;1為WWW;2為WAP+WWW',`serviceip` varchar(150) NOT NULL COMMENT '解析入庫的匯聚服務器IP地址',`sourcename` varchar(150) COMMENT '原始ZIP包名稱',`dmain_handle_flag` varchar(150) COMMENT '原始ZIP包標識',`dmainflag` int(11) DEFAULT 4 COMMENT '1 疑似違規;2 特定撥測;3 疑似釣魚',`flag` int(1) COMMENT '處理標識(1:需處理; 2:過濾; 3:重現; 4:處理中)',`createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按天區表'PARTITION BY HASH(DAY(createtime))PARTITIONS 4;-- 按日期查詢 mysql> explain partitions select * from t_sdmain_day_hash_partwhere createtime = '2015-04-10 07:49:15'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_sdmain_day_hash_partpartitions: p2type: ALL possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 143910Extra: Using where 1 row in set (0.00 sec)-- HASH分區有個問題，就是where條件是指定的一個范圍的話，將會掃描所有分區， -- 這就達不到使用分區表來減少掃描范圍獲得性能的提高的目的。mysql> explain partitions select * from t_sdmain_day_hash_partwhere createtime >= '2015-03-31 00:03:22' and createtime <= '2015-03-31 01:49:15'\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_sdmain_day_hash_partpartitions: p0,p1,p2,p3type: ALL possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 789530Extra: Using where 1 row in set (0.00 sec)-- 以上語句就是掃描所有分區.

線性HASH分區
? ? MySQL同樣支持線性哈希分區，采用powers-of-two algorithm來確認數據存儲于哪一個分區中。

CREATE TABLE employees_linear_part (id INT NOT NULL,name VARCHAR(30),hired DATE NOT NULL DEFAULT '1970-01-01',dept_id INT NOT NULL comment '部門' ) PARTITION BY LINEAR HASH( YEAR(hired) ) PARTITIONS 4;

????對于給定的expr，分區數目num，MySQL計算數據存儲于具體哪個分區算法如下：

1、Find the next power of 2 greater than num. We call this value V; it can be calculated as:V = POWER(2, CEILING(LOG(2, num)))(Suppose that num is 13. Then LOG(2,13) is 3.7004397181411. CEILING(3.7004397181411) is 4, and V = POWER(2,4), which is 16.)2、Set N = F(column_list) & (V - 1).3、While N >= num:Set V = CEIL(V / 2)Set N = N & (V - 1)

?示例如下：

CREATE TABLE my_linear_part_001 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6;insert into my_linear_part_001(col1, col2, col3) values(1, 'hello', '2003-04-14'); insert into my_linear_part_001(col1, col2, col3) values(2, 'hello', '1998-10-19'); V = POWER(2, CEILING( LOG(2,6) )) = 8 a. 判定2003-04-14這一行數據存儲于哪個分區 N = YEAR('2003-04-14') & (8 - 1)= 2003 & 7= 3(3 >= 6 is FALSE: record stored in partition #3)b. 判定1998-10-19這一行數據存儲于哪個分區 N = YEAR('1998-10-19') & (8-1)= 1998 & 7= 6(6 >= 6 is TRUE: additional step required)N = 6 & CEILING(8 / 2)= 6 & 3= 2(2 >= 6 is FALSE: record stored in partition #2)

? ?

KEY分區

????http://dev.mysql.com/doc/refman/5.6/en/partitioning-key.html

????Key分區類似于Hash分區，只是Hash分區是基于用戶提供的expr來計算屬于哪個分區，而Key分區的hash函數是由MySQL Server提供的，這些函數是基于與PASSWORD()一樣的運算法則。
????Key分區于Hash分區區別：
????1、PARTITION BY KEY而非PARTITION BY HASH
????2、KEY只能指定零個或多個列名稱。

????KEY takes only a list of zero or more column names. Any columns used as the partitioning key must comprise part or all of the table's primary key, if the table has one. Where no column name is specified as the partitioning key, the table's primary key is used, if there is one.???

CREATE TABLE k_part_by_pk (id INT NOT NULL PRIMARY KEY,name VARCHAR(20) DEFAULT NULL ) PARTITION BY KEY() -- 采用主鍵來分區 PARTITIONS 2; mysql> ALTER TABLE k_part_by_pk DROP PRIMARY KEY; ERROR 1488 (HY000): Field in list of fields for partition function not found in tableCREATE TABLE k_part_by_unique_key (id INT NOT NULL,name VARCHAR(20) DEFAULT NULL,UNIQUE KEY (name) ) PARTITION BY KEY() -- 采用UNIQUE KEY來分區 PARTITIONS 2;CREATE TABLE k_part_by_unique_key_with_null (id INT,name VARCHAR(20) DEFAULT NULL,UNIQUE KEY (id) ) PARTITION BY KEY() -- UNIQUE KEY未加NOT NULL將報錯 PARTITIONS 2; ERROR 1488 (HY000): Field in list of fields for partition function not found in tableCREATE TABLE k_part_by_varchar (id INT NOT NULL,name VARCHAR(20) DEFAULT NULL ) PARTITION BY KEY(name) PARTITIONS 2;CREATE TABLE k_part_by_int_and_varchar (id INT NOT NULL,name VARCHAR(20) DEFAULT NULL ) PARTITION BY KEY(id, name) PARTITIONS 2;-- MySQL支持Linear Key形式的分區表 CREATE TABLE k_part_of_linear_key (col1 INT NOT NULL,col2 CHAR(5),col3 DATE ) PARTITION BY LINEAR KEY (col1) PARTITIONS 3;-- 業務示例 CREATE TABLE `t_sdmain_sdmain_key_part` (`id` int(11) NOT NULL DEFAULT 0 COMMENT '不能使用主鍵，不可自增',`dmain` varchar(512) COMMENT '名稱, 不可使用UNIQUE約束',`ipstr` varchar(4000) COMMENT '對應IP',`iplocal` varchar(8) COMMENT '網內網外(0:網內; 1:網外; 2:網內+網外)',`ipabroad` varchar(8) COMMENT '境內境外(0:境內; 1:境外; 2:境內+境外)',`sendtime` datetime COMMENT '上報時間',`dmaintype` int(3) DEFAULT '1' COMMENT '0:正常;1:不良;2:違法:3其他',`dmainsource` varchar(16) COMMENT '來源(1、2、3、4、5、6)',`accpoint` varchar(70) COMMENT '網站類型 0為WAP;1為WWW;2為WAP+WWW',`serviceip` varchar(150) NOT NULL COMMENT '解析入庫的匯聚服務器IP地址',`sourcename` varchar(150) COMMENT '原始ZIP包名稱',`dmain_handle_flag` varchar(150) COMMENT '原始ZIP包標識',`dmainflag` int(11) DEFAULT 4 COMMENT '1 疑似違規;2 特定撥測;3 疑似釣魚',`flag` int(1) COMMENT '處理標識(1:需處理; 2:過濾; 3:重現; 4:處理中)',`createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按名稱名稱分區表'PARTITION BY KEY(dmain)PARTITIONS 6;-- dmain字段的長度不能超過1023，否則報錯如下 -- ERROR 1660 (HY000): The total length of the partitioning fields is too large -- 說明：該問題暫未解決（對于截取名稱作為分區依據同樣報錯KEY(LEFT(dmain, 50))）。mysql> explain partitions select * from t_sdmain_sdmain_key_partwhere dmain in ('s3-us-west-2.amazonaws.com', 'aaa077.com', 'mydress.com')\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_sdmain_sdmain_key_partpartitions: p2,p4type: ALL possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 516467Extra: Using where 1 row in set (0.00 sec)-- Key分區和HASH分區一樣，有范圍掃描的問題。

轉載于:https://my.oschina.net/dolphinboy/blog/1438880

總結

以上是生活随笔為你收集整理的MySQL基本分区表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： gdb可以查询执行文件的宏, 但是查询不
下一篇： shell 脚本加密