MySQL 水平分区

分区概念

分区是把表分成几个小部分，即对表的行进行划分。

分区的优点

• 可以提高数据库的性能；
• 对大表(行较多)的维护更快、更容易，因为数据分布在不同的逻辑文件上；
• 删除分区或它的数据是容易的，因为它不影响其他表。

分区类型

分区具有如下4种类型：

• Range分区：是对一个连续性的行值，按范围进行分区；比如：id小于100；id大于100小于200；日期在201906到201907之间；
• List分区：跟range分区类似，不过它存放的是一个离散值的集合。
• Hash分区：对用户定义的表达式所返回的值来进行分区。可以写partitions (分区数目)，或直接使用分区语句,比如partition p0 values in……。
• Key分区：与hash分区类似，只不过分区支持一列或多列，并且MySQL服务器自身提供hash函数。

具体描述：

分区语法：

按range分区

CREATE TABLE `log` (
  `server` int(11) NOT NULL,
  `log_ym` int(11) NOT NULL,
  `date_time` datetime NOT NULL,
  `online` int(11) DEFAULT NULL,
  `register` int(11) DEFAULT NULL,
  `pay` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`server`,`date_time`,`log_ym`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (log_ym)
(PARTITION p201903 VALUES LESS THAN (201904) ENGINE = InnoDB,
 PARTITION p201904 VALUES LESS THAN (201905) ENGINE = InnoDB,
 PARTITION p201905 VALUES LESS THAN (201906) ENGINE = InnoDB,
 PARTITION p201906 VALUES LESS THAN (201907) ENGINE = InnoDB,
 PARTITION p201907 VALUES LESS THAN (201908) ENGINE = InnoDB,
 PARTITION p201908 VALUES LESS THAN (201909) ENGINE = InnoDB,
 PARTITION p201909 VALUES LESS THAN (201910) ENGINE = InnoDB,
 PARTITION p201910 VALUES LESS THAN (201911) ENGINE = InnoDB,
 PARTITION p201911 VALUES LESS THAN (201912) ENGINE = InnoDB,
 PARTITION p201912 VALUES LESS THAN (202001) ENGINE = InnoDB,
 PARTITION another VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

按list分区

create table orders_list
(
id int auto_increment,
customer_surname varchar(30),
store_id int,
salesperson_id int,
order_Date date,
note varchar(500),
index idx(id)
) engine=myisam  partition by list(store_id)
(
partition p0 values in(1,3),
partition p1 values in(2,4,6),
partition p3 values in(10)
);

按key分区

create table orders_key
(
id int auto_increment,
customer_surname varchar(30),
store_id int,
alesperson_id int,
order_Date date,
note varcahr(500),
index_idx(id)
) engine=myisam  partition by key(order_date) partitions 4;

按hash分区

create table orders_hash
(
id int auto_increment primary key,
cutomer_surname varchar(30),
store_id int,
salesperon_id int,
order_date date,
note varcahr(500)
) engine=myisam  partition by hash(id) partitions 4;

如果分为4个分区，那当我插入数据时，哪些数据是放在哪些分区里呢？ 
当我对某个id值进行检索时，它明确说放到哪个分区里？或者说是有什么内部机制？
使用hash分区，最主要就是确保数据的分配，它是基于create table时提供的表达式。
不必定义单独的分区，只要使用partitions关键字和所需要分多少个区的数字。语句如上所述。

1.测试添加分区和删除分区
###添加删除range分区
(1)创建一个分区：
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by range columns(from_date)
(partition p01 values less than ('1985-12-31'),
partition p02 values less than ('1990-12-31'),
partition p03 values less than ('1995-12-31'),
partition p04 values less than ('2000-12-31'),
partition p05 values less than ('2005-12-31'),
partition p06 values less than ('2010-12-31'),
partition p07 values less than ('2015-12-31'),
partition p08 values less than ('2020-12-31'),
partition p09 values less than ('2025-12-31'),
partition p10 values less than ('2030-12-31')
);
导入数据
mysql> source titles.sql
 
(2)添加分区：
备注：不能超过p04的范围，严格递增每个分区，即最小不能小于前一个分区
下面新曾了两个分区n01和n02
 alter table titles
 reorganize partition p04 into(
 partition n01 values less than('1997-12-31'),
 partition n02 values less than('1998-12-31'),
 partition p04 values less than('2000-12-31')
 );
 
(3)删除分区：
删除分区即删除数据。
mysql> select count(*) from titles where from_date<'1985-12-31';
+----------+
| count(*) |
+----------+
|    18238 |
+----------+
1 row in set (0.09 sec)
 
mysql> alter table titles drop partition p01;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> select count(*) from titles where from_date<'1985-12-31';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)
 
###添加删除list分区
(1)创建list分区
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by list columns(title)
(
partition p0 values in ('Assistant Engineer'),
partition p1 values in ('Engineer'),
partition p2 values in ('Manager'),
partition p3 values in ('Senior Engineer'),
partition p4 values in ('Senior Staff'),
partition p5 values in ('Staff'),
partition p6 values in ('Technique Leader')
);
导入数据
titles.sql 是employees数据库导出的数据
mysql> source titles.sql
 
(2)添加分区
mysql> alter table titles add partition(partition p7 values in('CEO'));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
(3)删除分区：
mysql> alter table titles drop partition p0;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
###添加删除hash分区
(1)创建hash分区
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by hash(emp_no)
partitions 4;
导入数据：
mysql> source titles.sql
 
(2)删除hash分区
4个分区减少到两个，4-2=2
mysql> alter table titles coalesce partition 2;
Query OK, 443308 rows affected (12.41 sec)
Records: 443308  Duplicates: 0  Warnings: 0
 
(3)添加hash分区
增加到5个，2+3=5
mysql> alter table titles add partition partitions 3;
Query OK, 443308 rows affected (11.54 sec)
Records: 443308  Duplicates: 0  Warnings: 0
 
###添加删除key分区
(1)创建key分区
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by key(emp_no)
partitions 4;
导入数据
mysql> source titles.sql
 
(2)删除分区，同hash
mysql> alter table titles coalesce partition 2;
Query OK, 443308 rows affected (7.86 sec)
Records: 443308  Duplicates: 0  Warnings: 0
 
(3)添加分区，同hash
mysql> alter table titles add partition partitions 3;
Query OK, 443308 rows affected (6.17 sec)
Records: 443308  Duplicates: 0  Warnings: 0