MySQL--7--数据库分表分区--2

I.利用`merge`存储引擎来实现分表

回目录

适用范围：已经有大量数据的表

如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，用merge存储引擎来实现分表, 这种方法比较适合。

merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。

使用merge基本上不用改代码
此处理解：可以类比LVS，主表≈调度器（director），子表（real server）。

下面我们来实现一个简单的利用merge存储引擎来实现分表的演示：

1.创建一个完整表存储着所有的成员信息(表名为`member`)

回目录

mysql> drop database IF EXISTS test;
mysql> use test;
create table member(
id bigint auto_increment primary key,
name varchar(20),
sex tinyint not null default '0'
)engine=myisam default charset=utf8 auto_increment=1;

2.加入数据：

回目录

mysql> insert into member(name,sex) values('tom1',1);
mysql> insert into member(name,sex) select name,sex from member;

第二条语句多执行几次就有了很多数据

mysql> select * from member;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom1 |   1 |
|  2 | tom1 |   1 |
|  3 | tom1 |   1 |
|  4 | tom1 |   1 |
|  5 | tom1 |   1 |
|  6 | tom1 |   1 |
|  7 | tom1 |   1 |
|  8 | tom1 |   1 |
|  9 | tom1 |   1 |
| 10 | tom1 |   1 |
| 11 | tom1 |   1 |
| 12 | tom1 |   1 |
| 13 | tom1 |   1 |
| 14 | tom1 |   1 |
| 15 | tom1 |   1 |
| 16 | tom1 |   1 |
+----+------+-----+

3.下面我们进行分表,这里我们把`member`分两个表`tb_member1`,`tb_member2`

回目录

mysql> use test;
DROP table IF EXISTS tb_member1;
create table tb_member1(
    id bigint primary key ,
    name varchar(20),
    sex tinyint not null default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; 
DROP table IF EXISTS tb_member2;
create table tb_member2(
    id bigint primary key,
    name varchar(20),
    sex tinyint not null default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 
//创建tb_member2也可以用下面的语句  create table tb_member2 like tb_member1;

4.创建主表`tb_member`

回目录

    DROP table IF EXISTS tb_member;
    create table tb_member(
    id bigint primary key ,
    name varchar(20),
    sex tinyint not null default '0'
    ) ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 ;

注：INSERT_METHOD,此参数

INSERT_METHOD = NO 表示该表不能做任何写入操作只作为查询使用
INSERT_METHOD = LAST表示插入到最后的一张表里面
INSERT_METHOD = first表示插入到第一张表里面

5.查看一下`tb_member`表的结构

回目录

mysql> desc tb_member;
mysql> desc tb_member;
+-------+-------------+------+-----+---------+-----------------------------------------+
| Field | Type        | Null | Key | Default | Extra              |
+-------+-------------+------+-----+---------+-----------------------------------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment  |
| name  | varchar(20) | YES  |     | NULL  |                |
| sex   | tinyint(4)  | NO   |     | 0      |                |
+-------+-------------+------+-----+---------+------------------------------------------+
3 rows in set (0.00 sec)

注：查看子表与主表的字段定义要一致接下来，我们把数据分到两个分表中去：

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;
mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;

6.查看两个子表的数据

回目录

mysql> select * from tb_member1;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 16 | tom1 |   1 |
| 14 | tom1 |   1 |
| 12 | tom1 |   1 |
| 10 | tom1 |   1 |
|  8 | tom1 |   1 |
|  6 | tom1 |   1 |
|  4 | tom1 |   1 |
|  2 | tom1 |   1 |
+----+------+-----+
8 rows in set (0.00 sec)

mysql> select * from tb_member2;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  3 | tom1 |   1 |
|  1 | tom1 |   1 |
|  5 | tom1 |   1 |
|  7 | tom1 |   1 |
|  9 | tom1 |   1 |
| 11 | tom1 |   1 |
| 13 | tom1 |   1 |
| 15 | tom1 |   1 |
+----+------+-----+
8 rows in set (0.00 sec)

7.查看一下主表的数据

回目录

mysql> select * from tb_member;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 16 | tom1 |   1 |
| 14 | tom1 |   1 |
| 12 | tom1 |   1 |
| 10 | tom1 |   1 |
|  8 | tom1 |   1 |
|  6 | tom1 |   1 |
|  4 | tom1 |   1 |
|  2 | tom1 |   1 |
| 15 | tom1 |   1 |
| 13 | tom1 |   1 |
| 11 | tom1 |   1 |
|  9 | tom1 |   1 |
|  7 | tom1 |   1 |
|  5 | tom1 |   1 |
|  3 | tom1 |   1 |
|  1 | tom1 |   1 |
+----+------+-----+
16 rows in set (0.00 sec)

mysql> select * from tb_member where id=3;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  3 | tom1 |   1 |
+----+------+-----+
1 row in set (0.00 sec)

注意：总表只是一个外壳，存取数据发生在一个一个的子表里面。注意：每个子表都有自已独立的相关表文件，而主表只是一个壳，并没有完整的相关表文件

[root@localhost ~]# ls -l /usr/local/mysql/data/test/tb_member*
-rw-r-----. 1 mysql mysql 8614 Sep 15 21:49 /usr/local/mysql/data/test/tb_member1.frm
-rw-r-----. 1 mysql mysql  320 Sep 16 00:02 /usr/local/mysql/data/test/tb_member1.MYD
-rw-r-----. 1 mysql mysql 2048 Sep 16 00:43 /usr/local/mysql/data/test/tb_member1.MYI
-rw-r-----. 1 mysql mysql 8614 Sep 15 21:50 /usr/local/mysql/data/test/tb_member2.frm
-rw-r-----. 1 mysql mysql  180 Sep 16 00:02 /usr/local/mysql/data/test/tb_member2.MYD
-rw-r-----. 1 mysql mysql 2048 Sep 16 00:43 /usr/local/mysql/data/test/tb_member2.MYI
-rw-r-----. 1 mysql mysql 8614 Sep 16 21:12 /usr/local/mysql/data/test/tb_member3.frm
-rw-r-----. 1 mysql mysql    0 Sep 16 21:12 /usr/local/mysql/data/test/tb_member3.MYD
-rw-r-----. 1 mysql mysql 1024 Sep 16 21:12 /usr/local/mysql/data/test/tb_member3.MYI
-rw-r-----. 1 mysql mysql 8614 Sep 16 21:14 /usr/local/mysql/data/test/tb_member.frm
-rw-r-----. 1 mysql mysql   53 Sep 16 21:14 /usr/local/mysql/data/test/tb_member.MRG

II.`range`分区例子

回目录

下面我们先演示一个按照范围(range)方式的表分区

1.创建`range`分区表

回目录

mysql> use test2;
mysql> create table if not exist user (
    -> id int not null auto_increment,
    -> name varchar(30) not null default '',
    -> sex int(1) not null default '0',
    -> primary key(id)
    -> )default charset=utf8 auto_increment=1
    -> partition by range(id) (
    -> partition p0 values less than (3),
    -> partition p1 values less than (6),
    -> partition p2 values less than (9),
    -> partition p3 values less than (12),
    -> partition p4 values less than maxvalue
-> );

2.插入数据

回目录

mysql> insert into test2.user(name,sex)values ('tom1','0');
mysql> insert into test2.user(name,sex)values ('tom2','1');
mysql> insert into test2.user(name,sex)values ('tom3','1');
mysql> insert into test2.user(name,sex)values ('tom4','0');
mysql> insert into test2.user(name,sex)values ('tom5','0');
mysql> insert into test2.user(name,sex)values ('tom6','1');
mysql> insert into test2.user(name,sex)values ('tom7','1');
mysql> insert into test2.user(name,sex)values ('tom8','1');
mysql> insert into test2.user(name,sex)values ('tom9','1');
mysql> insert into test2.user(name,sex)values ('tom10','1');
mysql> insert into test2.user(name,sex)values ('tom11','1');
mysql> insert into test2.user(name,sex)values ('tom12','1');
mysql> insert into test2.user(name,sex)values ('tom13','1');
mysql> insert into test2.user(name,sex)values ('tom14','1');

3.到存放数据库表文件的地方看一下

回目录

[root@localhost ~]# ls -l /usr/local/mysql/data/test2/user*
-rw-r-----. 1 mysql mysql  8614 Sep 16 21:46 /usr/local/mysql/data/test2/user.frm
-rw-r-----. 1 mysql mysql 98304 Sep 16 21:48 /usr/local/mysql/data/test2/user#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 Sep 16 21:48 /usr/local/mysql/data/test2/user#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 Sep 16 21:49 /usr/local/mysql/data/test2/user#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 Sep 16 21:49 /usr/local/mysql/data/test2/user#P#p3.ibd
-rw-r-----. 1 mysql mysql 98304 Sep 16 21:49 /usr/local/mysql/data/test2/user#P#p4.ibd
mysql> select count(id) as count from user;
+-------+
| count |
+-------+
|    14 |
+-------+
1 row in set (0.00 sec)

4.从`information_schema`系统库中的`partitions`表中查看分区信息

回目录

mysql> select * from information_schema.partitions where table_schema='test2' and table_name='user'\G;

5.从某个分区中查询数据

回目录

mysql> select * from test2.user partition(p0);

7.新增分区

回目录

mysql> alter table test2.user add partition (partition partionname values less than (n));

8.删除分区

回目录

当删除了一个分区，也同时删除了该分区中所有的数据。

ALTER TABLE test2.user DROP PARTITION p3;

9.分区的合并

回目录

下面的SQL，将p1 – p3合并为2个分区p01 – p02

mysql> alter table test2.user
    -> reorganize partition p1,p2,p3 into
    -> (partition p01 values less than (8),
    -> partition p02 values less than (12)
    -> );

[root@localhost ~]# ls -l /usr/local/mysql/data/test2/user*
-rw-r-----. 1 mysql mysql  8614 Sep 16 22:06 /usr/local/mysql/data/test2/user.frm
-rw-r-----. 1 mysql mysql 98304 Sep 16 22:06 /usr/local/mysql/data/test2/user#P#p01.ibd
-rw-r-----. 1 mysql mysql 98304 Sep 16 22:06 /usr/local/mysql/data/test2/user#P#p02.ibd
-rw-r-----. 1 mysql mysql 98304 Sep 16 21:48 /usr/local/mysql/data/test2/user#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 Sep 16 21:49 /usr/local/mysql/data/test2/user#P#p4.ibd

mysql> select * from test2.user partition(p01);
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  3 | tom3 |   1 |
|  4 | tom4 |   0 |
|  5 | tom5 |   0 |
|  6 | tom6 |   1 |
|  7 | tom7 |   1 |
+----+------+-----+
5 rows in set (0.00 sec)

III.未分区表和分区表性能测试

回目录

1.创建一个未分区的表

回目录

mysql> create table test2.tab1(c1 int,c2 varchar(30),c3 date);

2.创建分区表,按日期的年份拆分

回目录

mysql> CREATE TABLE test2.tab2 ( c1 int, c2 varchar(30) , c3 date )    
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),  
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,  
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,  
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,  
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,  
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),  
PARTITION p11 VALUES LESS THAN MAXVALUE );

注意:最后一行，考虑到可能的最大值通过存储过程插入100万条测试数据

3.创建存储过程

回目录

mysql> delimiter $$      //指定存储过程结束符
mysql>CREATE PROCEDURE load_part_tab()  
       begin  
     declare v int default 0;  
     while v < 2000000  
     do  
         insert into test2.tab1  
         values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));  
          set v = v + 1;  
     end while;  
     end  
   $$

注：RAND()函数在0和1之间产生一个随机数，如果一个整数参数N被指定，它被用作种子值。每个种子产生的随机数序列是不同的。

4.执行存储过程`load_part_tab`向`test2.tab1`表插入数据

回目录

mysql> delimiter ;
mysql> call load_part_tab();

5.向test2.tab2表中插入数据

回目录

mysql> insert into test2.tab2 select * from test2.tab1;

6.测试SQL性能

回目录

a.未分区(`test2.tab1`)

回目录

mysql> select count(*) from test2.tab1 where c3 > '1995-01-01' and c3 < '1995-12-31';
+----------+
| count(*) |
+----------+
|   219642 |
+----------+
1 row in set (0.84 sec)

b.分区(`test2.tab2`)

回目录

mysql> select count(*) from test2.tab2 where c3 > '1995-01-01' and c3 < '1995-12-31'; 
+----------+
| count(*) |
+----------+
|   219642 |
+----------+
1 row in set (0.09 sec)

结果表明分区表比未分区表的执行时间少很多。

通过explain语句来分析执行情况

mysql> flush tables;
mysql> explain select count(*) from test2.tab1 where c3 > '1995-01-01' and c3 < '1995-12-31'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2001552
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from test2.tab2 where c3 > '1995-01-01' and c3 < '1995-12-31'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab2
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 220206
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

explain语句显示了SQL查询要处理的记录数目可以看出分区表比未分区表的明显扫描的记录要少很多。

创建索引后情况测试

回目录

mysql> create index idx_of_c3 on test2.tab1(c3);
Query OK, 0 rows affected (5.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_of_c3 on test2.tab2(c3);
Query OK, 0 rows affected (4.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> flush tables;
mysql> select count(*) from test2.tab1 where c3 > '1996-01-01' and c3 < '1996-12-31';
+----------+
| count(*) |
+----------+
|   220264 |
+----------+
1 row in set (0.12 sec)

重启mysqld服务

mysql> select count(*) from test2.tab2 where c3 > '1996-01-01' and c3 < '1996-12-31';
+----------+
| count(*) |
+----------+
|   220264 |
+----------+
1 row in set (0.11 sec)

创建索引后分区表比未分区表相差不大（数据量越大差别会明显些）

IV.`mysql`分区的类型

回目录

1.`RANGE`分区

回目录

基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。以下是实例。

CREATE TABLE employees (
    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到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。对于包含数据(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)的一个新行，可以很容易地确定它将插入到p2分区中，但是如果增加了一个编号为第21的商店，将会发生什么呢？在这种方案下，由于没有规则把store_id大于20的商店包含在内，服务器将不知道把该行保存在何处，将会导致错误。要避免这种错误，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在的分区中。

mysql> alter table test2.user add partition (partition p4 values less than maxvalue);

2.`LIST`分区

回目录

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。LIST分区通过使用PARTITION BY LIST(expr)来实现，其中expr”是某列值或一个基于某个列值.并返回一个整数值的表达式，然后通过VALUES IN (value_list)的方式来定义每个分区，其中value_list是一个通过逗号分隔的整数列表。要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的CREATE TABLE语句：

CREATE TABLE employees (
    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,
    store_id INT
)  
 PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
)；

(root@localhost)(11:21:36) [test]> insert into employees(id,fname, lname,job_code, store_id) values ('1', 'jason', 'borurn', '01', '22');
ERROR 1526 (HY000): Table has no partition for value 22
(root@localhost)(11:22:21) [test]>

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询ALTER TABLE employees DROP PARTITION pWest；来进行删除，它与具有同样作用的DELETE （删除）查询DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；比起来，要有效得多。

要点：如果试图插入列值不在分区值列表中的一行时，那么INSERT查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的插入将失败：

INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

这是因为store_id列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是，LIST分区没有类似如VALUES LESS THAN MAXVALUE这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

(root@localhost)(11:22:21) [test]> alter table employees add partition ( partition p4 values less than maxvalue);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@localhost)(11:32:03) [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| employees      |
| member         |
| tb_member      |
| tb_member1     |
| tb_member2     |
+----------------+
5 rows in set (0.00 sec)

(root@localhost)(11:32:08) [test]> insert into employees(id,fname, lname,job_code, store_id) values ('1', 'jason', 'bourn', '01', '22');
Query OK, 1 row affected (0.01 sec)

(root@localhost)(11:32:23) [test]> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | jason | bourn | 1970-01-01 | 9999-12-31 |        1 |       22 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)

如何查看某个分区？

3.`hash`分区

回目录

这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。 hash分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一致。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MYSQL自动完成这些工作，用户所要定一个列值或者表达式，以及指定被分区的表将要被分割成的分区数量。

mysql> create table t_hash( a int(11), b datetime) partition by hash(year(b)) partitions 4;

hash的分区函数页需要返回一个整数值。partitions子句中的值是一个非负整数，不加的partitions子句的话，默认为分区数为1。

mysql> insert into t_hash values(1,'2010-04-01');

该记录会被放入分区p2中。因为插入2010-04-01进入表t_hash,那么

MOD(YEAR('2010-04-01'),4)=2

mysql> select * from information_schema.partitions where table_schema='test2' and table_name='t_hash'\G;
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: t_hash
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: year(b)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-09-16 22:48:59
                  UPDATE_TIME: 2016-09-17 23:36:22
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: t_hash
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: year(b)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-09-16 22:48:59
                  UPDATE_TIME: 2016-09-17 23:36:22
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 3. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: t_hash
               PARTITION_NAME: p2
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: year(b)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 1
               AVG_ROW_LENGTH: 16384
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-09-16 22:48:59
                  UPDATE_TIME: 2016-09-17 23:23:26
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 4. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: t_hash
               PARTITION_NAME: p3
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: HASH
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: year(b)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: NULL
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2016-09-16 22:48:59
                  UPDATE_TIME: 2016-09-17 23:23:26
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
4 rows in set (0.00 sec)

可以看到P2分区有一条记录。当前这个例子并不能把数据均匀的分布到各个分区，因为按照YEAR函数进行的，该值本身是离散的。如果对连续的值进行HASH分区，如自增长的主键，则可以较好地将数据平均分布。请思考：

mysql> insert into t_hash values(1,'2012-04-01');

记录会插入哪个分区？

4.`key`分区

回目录

key分区和hash分区相似，不同在于hash分区是用户自定义函数进行分区，key分区使用mysql数据库提供的函数进行分区，NDB cluster使用MD5函数来分区，对于其他存储引擎mysql使用内部的hash函数。

mysql> create table t_key( a int(11), b datetime) partition by key(b) partitions 4;

上面的RANGE.LIST.HASH.KEY四种分区中，分区的条件必须是整形，如果不是整形需要通过函数将其转换为整形。

5.`columns`分区

回目录

mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型：

所有整形，如INT,SMALLINT, TINYINT, BIGINT。FLOAT和DECIMAL则不支持。
日期类型，如DATE和DATETIME。其余日期类型不支持。
字符串类型，如CHAR.VARCHAR.BINARY和VARBINARY。BLOB和TEXT类型不支持。

COLUMNS可以使用多个列进行分区。

回目录

7-mysql-数据库分表分区-2

MySQL--7--数据库分表分区--2

目录

I.利用`merge`存储引擎来实现分表

1.创建一个完整表存储着所有的成员信息(表名为`member`)

2.加入数据：

3.下面我们进行分表,这里我们把`member`分两个表`tb_member1`,`tb_member2`

4.创建主表`tb_member`

5.查看一下`tb_member`表的结构

6.查看两个子表的数据

7.查看一下主表的数据

II.`range`分区例子

1.创建`range`分区表

2.插入数据

3.到存放数据库表文件的地方看一下

4.从`information_schema`系统库中的`partitions`表中查看分区信息

5.从某个分区中查询数据

7.新增分区

8.删除分区

9.分区的合并

III.未分区表和分区表性能测试

1.创建一个未分区的表

2.创建分区表,按日期的年份拆分

3.创建存储过程

4.执行存储过程`load_part_tab`向`test2.tab1`表插入数据

5.向test2.tab2表中插入数据

6.测试SQL性能

a.未分区(`test2.tab1`)

b.分区(`test2.tab2`)

创建索引后情况测试

IV.`mysql`分区的类型

1.`RANGE`分区

2.`LIST`分区

3.`hash`分区

4.`key`分区

5.`columns`分区

results matching ""

No results matching ""

MySQL--7--数据库分表分区--2

目录

I.利用merge存储引擎来实现分表

1.创建一个完整表存储着所有的成员信息(表名为member)

2.加入数据：

3.下面我们进行分表,这里我们把member分两个表tb_member1,tb_member2

4.创建主表tb_member

5.查看一下tb_member表的结构

6.查看两个子表的数据

7.查看一下主表的数据

II.range分区例子

1.创建range分区表

2.插入数据

3.到存放数据库表文件的地方看一下

4.从information_schema系统库中的partitions表中查看分区信息

5.从某个分区中查询数据

7.新增分区

8.删除分区

9.分区的合并

III.未分区表和分区表性能测试

1.创建一个未分区的表

2.创建分区表,按日期的年份拆分

3.创建存储过程

4.执行存储过程load_part_tab向test2.tab1表插入数据

5.向test2.tab2表中插入数据

6.测试SQL性能

a.未分区(test2.tab1)

b.分区(test2.tab2)

创建索引后情况测试

IV.mysql分区的类型

1.RANGE分区

2.LIST分区

3.hash分区

4.key分区

5.columns分区

results matching ""

No results matching ""

I.利用`merge`存储引擎来实现分表

1.创建一个完整表存储着所有的成员信息(表名为`member`)

3.下面我们进行分表,这里我们把`member`分两个表`tb_member1`,`tb_member2`

4.创建主表`tb_member`

5.查看一下`tb_member`表的结构

II.`range`分区例子

1.创建`range`分区表

4.从`information_schema`系统库中的`partitions`表中查看分区信息

4.执行存储过程`load_part_tab`向`test2.tab1`表插入数据

a.未分区(`test2.tab1`)

b.分区(`test2.tab2`)

IV.`mysql`分区的类型

1.`RANGE`分区

2.`LIST`分区

3.`hash`分区

4.`key`分区

5.`columns`分区