MySQL分表和分区相关介绍

为什么要分表和分区？
随着数据库中的数据越来越大，会导致单个表中数据太多。以至于查询速度变慢，而且由于表的锁机制导致应用操作也受到严重影响，出现了数据库性能瓶颈。
MySQL中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。当出现这种情况时，可以考虑分表或分区。
由于MySQL默认是以文件的形式将表存储在磁盘上，如果一个表存放了太多数据，那么这个文件的体积就会很大，如果一个表文件就几十个G，这种情况对查询效率和数据安全都是不利的。
为了解决这个问题就需要对表进行表分区操作，表分区和分表是不同的两个概念，表分区是将一个大表分成了多个逻辑块，在磁盘上也会按照表名+分区名创建出多个文件，但是对应用来说这些分区表是透明的，看上去仍然是一张表一个表名，属于集中式。分表则是将一张大表拆成了多张小表，可以存放在不同的实例上，属于分布式。
除了分区分表外，还可以根据业务来对数据进行分库操作，比如用户信息都单独放在一个用户库里，又或者每个地区有每个地区自己的用户库等。

一、分表简介

1.什么是分表？
分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件：.MYD数据文件、.MYI索引文件、.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。
将单个数据库表进行拆分，拆分成多个数据表，然后用户访问时，根据一定的算法(如用hash的方式，也可以用求余(取模)的方式)，让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就是减小数据库的负担，缩短查询时间。
2.MySQL分表的类别
(1)垂直切分
指数据表列的拆分，把一张列比较多的表拆分为多张表。

a.把不常用的字段单独放在一张表;

b.把text，blob(binary large object，二进制大对象)等大字段拆分出来放在附表中;

c.经常组合查询的列放在一张表中;

d.垂直拆分是在数据表设计之初就执行的步骤，然后查询时用join关联起来即可。

(2)水平拆分
指数据表行的拆分，把一张的表的数据拆成多张表来存放。

a.通常情况下，使用hash、取模等方式来进行表的拆分，比如一张有400W的用户表users，为提高其查询效率把其分成4张表users1，users2，users3，users4通过用ID取模的方法把数据分散到四张表内Id%4= [0,1,2,3]，然后查询、更新、删除也通过取模的方法来查询;

b.部分业务逻辑也可以通过地区，年份等字段来进行归档拆分;

c.进行拆分后的表，这时就要约束用户查询行为。比如是按年来进行拆分的，这时在页面设计上就约束用户必须要先选择年，然后才能进行查询。

3.分表的几种方式
(1)mysql集群
它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写压力，从而提升数据库性能。
(2)预估会出现大数据量并且访问频繁的表，将其分为若干个表。
根据一定的算法(如用hash的方式，也可以用求余(取模)的方式)让用户访问不同的表。
例如论坛里发表帖子的表，时间长了这张表肯定很大，几十万，几百万都有可能。聊天室里的信息表，时间长了，这张表的数据肯定很大。像这样的情况很多。所以这种能预估出来的大数据量表，就事先分出N个表，这个N是多少，根据实际情况而定。
以聊天信息表为例：
事先建100个这样的表，message_00,message_01,message_02…message_98,message_99。然后根据用户的ID来判断这个用户的聊天信息放到哪张表，可以用hash的方式来获得，也可以用求余的方式来获得，方法很多。或者可以设计每张表容纳的数据量是N条，那么如何判断某张表的数据是否容量已满呢？可以在程序段对于要新增数据的表，在插入前先做统计表记录数量的操作，当<N条数据，就直接插入，当已经到达阀值，可以在程序段新创建数据库表(或者已经事先创建好)，再执行插入操作。
(3)利用merge存储引擎来实现分表
如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，用merge存储引擎来实现分表，这种方法比较适合。
merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中。可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。
下面实现一个简单的利用merge存储引擎来实现分表的演示。
创建一个完整表存储着所有的成员信息(表名为member)。

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> 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;
Query OK, 0 rows affected (0.01 sec)

插入数据

mysql> insert into member(name,sex) values('tom1',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into member(name,sex) select name,sex from member;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

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 |
+----+------+-----+
16 rows in set (0.00 sec)

下面进行分表，把member分两个表tb_member1和tb_member2。

mysql> use test;
Database changed
mysql> create table tb_member1(
    -> id bigint primary key,
    -> name varchar(20),
    -> sex tinyint not null default '0'
    -> ) engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> create table tb_member2(
    -> id bigint primary key,
    -> name varchar(20),
    -> sex tinyint not null default '0'
    -> ) engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

创建tb_member2也可以用下面的语句

create table tb_member2 like tb_member1;

创建主表tb_member

mysql> 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;
Query OK, 0 rows affected (0.00 sec)

注意

insert_method=no：表示该表不能做任何写入操作只作为查询使用;

insert_method=last：表示插入到最后的一张表里面;

insert_method=first：表示插入到第一张表里面;

查看一下tb_member表的结构

mysql> desc tb_member;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| 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;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

查看两个子表的数据

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

mysql> select * from tb_member2;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom1 |   1 |
|  3 | 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)

查看一下主表的数据

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

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

[root@localhost test]# pwd
/usr/local/mysql/data/test
[root@localhost test]# ll
总用量 80
-rw-r-----. 1 mysql mysql   65 1月  18 19:15 db.opt
-rw-r-----. 1 mysql mysql 8614 1月  18 19:17 member.frm
-rw-r-----. 1 mysql mysql  320 1月  18 19:20 member.MYD
-rw-r-----. 1 mysql mysql 2048 1月  18 19:20 member.MYI
-rw-r-----. 1 mysql mysql 8614 1月  18 19:25 tb_member1.frm
-rw-r-----. 1 mysql mysql  160 1月  18 19:35 tb_member1.MYD
-rw-r-----. 1 mysql mysql 2048 1月  18 19:35 tb_member1.MYI
-rw-r-----. 1 mysql mysql 8614 1月  18 19:26 tb_member2.frm
-rw-r-----. 1 mysql mysql  160 1月  18 19:35 tb_member2.MYD
-rw-r-----. 1 mysql mysql 2048 1月  18 19:35 tb_member2.MYI
-rw-r-----. 1 mysql mysql 8614 1月  18 19:30 tb_member.frm
-rw-r-----. 1 mysql mysql   42 1月  18 19:30 tb_member.MRG

二、分区简介

1.什么是分区？
分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置了。
app读写的时候操作的还是表名字，db自动去组织分区的数据。
2.分区主要有两种形式
(1)水平分区(Horizontal Partitioning)
这种形式分区是对表的行进行分区，所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
(2)垂直分区(Vertical Partitioning)
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
举个简单例子：一个包含了大text和blob列的表，这些text和blob列又不经常被访问，这时候就要把这些不经常使用的text和blob划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
分区技术支持在MySQL5.6版本之前，使用这个参数查看当将配置是否支持分区。

mysql> show variables like '%partition%';
+-----------------------+---------------+
|Variable_name          | Value         |
+-----------------------+---------------+
| have_partition_engine | YES           |
+-----------------------+---------------+

在MySQL5.6版本之后，则采用如下方式进行查看。

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
44 rows in set (0.00 sec)

在显示结果中，可以看到partition是ACTIVE的，表示支持分区。
3.创建range分区表

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> use test2;
Database changed
mysql> create table 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
    -> );
Query OK, 0 rows affected (0.02 sec)

4.插入数据

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

5.到存放数据库表文件的地方查看

[root@localhost test2]# pwd
/usr/local/mysql/data/test2
[root@localhost test2]# ll
总用量 496
-rw-r-----. 1 mysql mysql    65 1月  18 19:48 db.opt
-rw-r-----. 1 mysql mysql  8614 1月  18 19:52 user.frm
-rw-r-----. 1 mysql mysql 98304 1月  18 19:54 user#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 1月  18 19:54 user#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 1月  18 19:54 user#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 1月  18 19:54 user#P#p3.ibd
-rw-r-----. 1 mysql mysql 98304 1月  18 19:54 user#P#p4.ibd

6.给查询结果指定列名

mysql> select count(id) as count from user;
+-------+
| count |
+-------+
|    14 |
+-------+
1 row in set (0.00 sec)

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

mysql> select * from information_schema.partitions where table_schema='test2' and table_name='user'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: user
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 3
                   TABLE_ROWS: 2
               AVG_ROW_LENGTH: 8192
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2021-01-18 19:52:52
                  UPDATE_TIME: 2021-01-18 19:54:46
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: user
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 6
                   TABLE_ROWS: 3
               AVG_ROW_LENGTH: 5461
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2021-01-18 19:52:52
                  UPDATE_TIME: 2021-01-18 19:54:46
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 3. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: user
               PARTITION_NAME: p2
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 9
                   TABLE_ROWS: 3
               AVG_ROW_LENGTH: 5461
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2021-01-18 19:52:52
                  UPDATE_TIME: 2021-01-18 19:54:46
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 4. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: user
               PARTITION_NAME: p3
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 12
                   TABLE_ROWS: 3
               AVG_ROW_LENGTH: 5461
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2021-01-18 19:52:52
                  UPDATE_TIME: 2021-01-18 19:54:46
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 5. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test2
                   TABLE_NAME: user
               PARTITION_NAME: p4
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 5
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: MAXVALUE
                   TABLE_ROWS: 3
               AVG_ROW_LENGTH: 5461
                  DATA_LENGTH: 16384
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2021-01-18 19:52:52
                  UPDATE_TIME: 2021-01-18 19:54:49
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
5 rows in set (0.00 sec)

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

mysql> select * from user partition(p0);
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom1 |   0 |
|  2 | tom2 |   1 |
+----+------+-----+
2 rows in set (0.00 sec)

9.新增分区

mysql> alter table user add partition (partition partitionname values less
than (n));  #可能会报错，因为maxvalue只能被用来做最后一个定义;

或者

mysql> alter table user reorganize partition p4 into (partition p5 values less than (13),partition p6 values less than maxvalue);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

10.删除分区
当删除了一个分区，也同时删除了该分区中所有的数据，所以删除分区前请备份数据。

alter table user drop partition p3;

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

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

12.查看分区信息

[root@localhost test2]# pwd
/usr/local/mysql/data/test2
[root@localhost test2]# ll
-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

13.查看合并后的分区

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)

14.MySQL表分区限制

(1)从5.6开始每张表支持8192个分区;

(2)分区字段必须有主键/唯一索引列;

(3)不能使用外键约束，不能使用存储过程，不支持全文索引，不支持临时表;

(4)分区还可以建立子分区，也叫复合分区;

15.MySQL表分区类型

range：范围分区，适合按照月份或者编号范围进行分区，比如id1-10000为一个分区，以此类推;

hash：哈希分区，按照hash算法来随机分到每个区;

list：列表分区，适合对字符进行分区，比如按省份分区，但是数据不会太均匀;

key：类似hash分区;

三、未分区表和分区表性能测试

1.创建一个未分区的表

mysql> use test2;
Database changed
mysql> create table tab1 (c1 int,c2 varchar(30),c3 date);
Query OK, 0 rows affected (0.01 sec)

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

create table 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万条测试数据：

delimiter $$
create procedure insert_tab1()
begin
declare v int default 0;
while v < 2000000
do
insert into tab1 values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
set v = v + 1;
end while;
end $$
delimiter ;

注意：rand()函数在0和1之间产生一个随机数，如果一个整数参数n被指定，它被用作种子值。每个种子产生的随机数序列是不同的。
执行存储过程insert_tab1向tab1表插入数据。

mysql> call insert_tab1();
Query OK, 1 row affected (7 min 32.07 sec)

向tab2表中插入数据

mysql> insert into tab2 select * from tab1;
Query OK, 2000000 rows affected (11.53 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

3.测试SQL性能

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

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

结果表明分区表比未分区表的执行时间少很多。
4.通过explain语句来分析执行情况

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from 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: 1884834
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(*) from 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: 199332
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

explain语句显示了SQL查询要处理的记录数目可以看出分区表比未分区表的明显扫描的记录要少很多。
5.创建索引后情况测试

mysql> create index index_c3 on tab1(c3);
Query OK, 0 rows affected (3.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index index_c3 on tab2(c3);
Query OK, 0 rows affected (2.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from tab1 where c3 > '1996-01-01' and c3 < '1996-12-31';
+----------+
| count(*) |
+----------+
|   199363 |
+----------+
1 row in set (0.13 sec)

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

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

四、分区时将不同分区放到不同存储位置

1.建表时，提前创建好存储目录，并授权给mysql

[root@mysql ~]# mkdir -p /data/area{1..3}
[root@mysql ~]# ls /data/
area1  area2  area3
[root@www ~]# chown -R mysql:mysql /data/
[root@mysql ~]# ll /data/
总用量 0
drwxr-xr-x 2 mysql mysql 6 1月  21 11:30 area1
drwxr-xr-x 2 mysql mysql 6 1月  21 11:30 area2
drwxr-xr-x 2 mysql mysql 6 1月  21 11:30 area3

2.创建表格
注意：
使用mysql默认的存储引擎innodb时，只需要指定data directory就可以，因为innodb的数据和索引在一个文件中。
但是创建表格时指定engine=myisam时，修改分区的存储位置，需要同时指定data directory和index directory。

create table user1 (
id int not null primary key auto_increment,
name varchar(30) not null default '')
default charset=utf8 auto_increment=1
partition by range(id)(
partition p1 values less than (3) data directory '/data/area1',
partition p2 values less than (6) data directory '/data/area2',
partition p3 values less than (9) data directory '/data/area3');

3.查看分区情况

[root@mysql ~]# tree -C /data
/data
├── area1
│   └── test2
│       └── user1#P#p1.ibd
├── area2
│   └── test2
│       └── user1#P#p2.ibd
└── area3
    └── test2
        └── user1#P#p3.ibd

6 directories, 3 files

4.查看默认数据存储位置的文件

[root@mysql ~]# cd /usr/local/mysql/data/test2/
[root@mysql test2]# ll
-rw-r----- 1 mysql mysql      8586 1月  21 11:34 user1.frm
-rw-r----- 1 mysql mysql        32 1月  21 11:34 user1#P#p1.isl
-rw-r----- 1 mysql mysql        32 1月  21 11:34 user1#P#p2.isl
-rw-r----- 1 mysql mysql        32 1月  21 11:34 user1#P#p3.isl

5.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)
);

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

mysql> alter table employees add partition (partition p4 values less than maxvalue);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

create table employees1 (
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 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)
);

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。
那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“alter table employees1 drop partition pWest;”来进行删除。
它与具有同样作用的delete查询“delete from employees1 where store_id in (4,12,13,14,18);”比起来，要有效得多。
注意：如果试图插入列值不在分区值列表中的一行时，那么“insert”查询将失败并报错。
例如，假定list分区采用上面的方案，下面的插入将会失败。

mysql> insert into employees1 values(224,'Linus','Torvalds','2002-05-01','2004-10-12',42,21);
ERROR 1526 (HY000): Table has no partition for value 21

这是因为“store_id”列值21不能在用于定义分区pNorth、pEast、pWest和pCentral的值列表中找到。
list分区没有类似如“values less than maxvalue”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
(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;
Query OK, 0 rows affected (0.02 sec)

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

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

因为插入2010-04-01进入表t_hash,那么mod(year(‘2010-04-01’),4)=2，所以该记录会被放入分区p2中。

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: 2021-01-21 11:59:52
                  UPDATE_TIME: NULL
                   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: 2021-01-21 11:59:52
                  UPDATE_TIME: NULL
                   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: 2021-01-21 11:59:52
                  UPDATE_TIME: 2021-01-21 12:00:38
                   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: 2021-01-21 11:59:52
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
4 rows in set (0.00 sec)

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

增加分区

mysql> alter table t_hash add partition (partition p4);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意：增加分区后，数据会重新进行分配。

删除分区

mysql> alter table t_hash coalesce partition 1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意：删除分区后，数据会重新进行分配。
(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;
Query OK, 0 rows affected (0.02 sec)

上面的range、list、hash、key四种分区中，分区的条件必须是整型，如果不是整形需要通过函数将其转换为整型。

增加分区

mysql> alter table t_key add partition (partition p4);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意：增加分区后，数据会重新进行分配。

删除分区

mysql> alter table t_key coalesce partition 1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意：删除分区后，数据会重新进行分配。
(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可以使用多个列进行分区。

五、分表和分区的区别

1.实现方式上
(1)mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表，都对
应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件。
(2)分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了
2.数据处理上
(1)分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表
里面。
(2)分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表，数据处理还是由自己来完成。
3.提高性能上
(1)分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因
为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。
(2)mysql提出了分区的概念，主要是想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。
在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。
4.实现的难易度上
(1)分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式跟分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
(2)分区实现是比较简单的，建立分区表，跟建平常的表没什么区别，并且对开代码端来说是透明的。

六、mysql分表和分区的联系

1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。
2.分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，可以采取分区的方式等。
3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。
4.表分区相对于分表，操作方便，不需要创建子表。