mysql表分区上亿的数据_面对亿量级数据，除了分库分表，Mysql分区表你也应该了解一下...

什么是分区表

分区就是根据一定的规则，把一个表分解成多个更小的、更容易管理的部分，在逻辑上就只有一个表，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。

小试牛刀

看mysql是否支持分区

#查看一下mysql版本

mysql> select version();

+------------+

| version() |

+------------+

| 5.7.11-log |

+------------+

1 row in set (0.02 sec)

#查看是否支持表分区

mysql> show plugins;

+----------------------------+----------+--------------------+-----------------+---------+

| Name | Status | Type | Library | License |

+----------------------------+----------+--------------------+-----------------+---------+

| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |

+----------------------------+----------+--------------------+-----------------+---------+

45 rows in set (0.14 sec)

出现partition的status为active说明mysql支持分区。

2.创建一个分区表

mysql> CREATE TABLE tb_partition_demo (

-> id int not null auto_increment,

-> name varchar(20) DEFAULT NULL,

-> create_time date DEFAULT NULL,

-> KEY (id)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-> PARTITION BY RANGE (YEAR(create_time))

-> (PARTITION partition_2018 VALUES LESS THAN (2019) ENGINE = InnoDB,

-> PARTITION partition_2019 VALUES LESS THAN (2020) ENGINE = InnoDB,

-> PARTITION partition_2020 VALUES LESS THAN (2021) ENGINE = InnoDB,

-> PARTITION partition_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Query OK, 0 rows affected (0.87 sec)

表创建成功后，我们会发现这个表(tb_partition_demo)包含了一个.frm文件和4个.ibd文件(每一个分区对应一个.ibd文件)也就是说：

对于引擎层来说，这是4个表

对于Server层来说，这是1个表

所以分区对应用来说是完全透明的，我们就可以像使用普通表一样来使用分区表，不影响应用的业务逻辑。

3.数据写入分区表

mysql> insert into tb_partition_demo(name,create_time) values ('刘备','2018-01-01'),('关羽','2019-01-01'),('张飞','2020-01-01');

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

#查询数据，看是否写入成功

mysql> select * from tb_partition_demo;

+----+--------+-------------+

| id | name | create_time |

+----+--------+-------------+

| 1 | 刘备 | 2018-01-01 |

| 2 | 关羽 | 2019-01-01 |

| 3 | 张飞 | 2020-01-01 |

+----+--------+-------------+

3 rows in set (0.02 sec)

上面写入的3条数据，”刘备“会被写入到partition_2018分区中，”关羽“会被写入到partition_2019分区中，”张飞“会被写入partition_2020分区中,可以通过下面的sql语句来验证一下：

mysql> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.PARTITIONS a where a.TABLE_NAME='tb_partition_demo';

+-------------------+------------------+------------+

| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |

+-------------------+------------------+------------+

| tb_partition_demo | partition_2018 | 1 |

| tb_partition_demo | partition_2019 | 1 |

| tb_partition_demo | partition_2020 | 1 |

| tb_partition_demo | partition_others | 0 |

+-------------------+------------------+------------+

4 rows in set (0.02 sec)

可以看出 partition_2018、partition_2019、partition_2020分区的确各有一条数据，而且对数据的更新和删除操作都和普通表使用方式一样，是不是很方便。

下面我们来看看分区表和普通表的性能对比吧

为了方便对比我们先创建一张和上面分区表一样的普通表，创建语句如下：

mysql> CREATE TABLE tb_normal_demo (

-> id int not null auto_increment,

-> name varchar(20) DEFAULT NULL,

-> create_time date DEFAULT NULL,

-> KEY (id)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.04 sec)

我们分别先往tb_normal_demo和tb_partition_demo表中增加200W条数据后(写入数据的程序就不在这里展示了)，再分别执行以下sql

#查询普通表

mysql> select count(1) from tb_normal_demo where create_time between '2019-01-01' and '2019-12-31';

+----------+

| count(1) |

+----------+

| 744352 |

+----------+

1 row in set (2.12 sec)

#查询分区表

mysql> select count(1) from tb_normal_demo where create_time between '2019-01-01' and '2019-12-31';

+----------+

| count(1) |

+----------+

| 744352 |

+----------+

1 row in set (0.22 sec)

可见根据分区字段查询表数据时，分区表的优势一下就体现出来了。

当然以上用了Range分区类型，其实Mysql来有很多种分区类型如：

Range分区

List分区

Columns分区

Hash分区

Key分区

子分区

用法都差不多，这里就不在过多描述。

使用分区表的优势

分区表最大优势就是对业务透明，相对于分区表来说，使用分区表的业务代码更简洁，还有分区表可以很方便的清理历史数据(只需要drop掉某个历史分区就可以了，语法为：alter table t drop partition ....)使用分区表时要注意：

分区并不是越细越好，实际上，单表或者单分区的数据1000W行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。

分区也不要提前预留太多，在使用之前预先创建即可，又如按月分区，每年年底再把下一年的12个分区创建上即可，对于没有数据的历史分区，要及时drop掉。

查询数据尽量不要跨很多分区取数据，这样可能导致查询会很慢，所以使用分区前要提前做好充分的考虑。

使用分区表的劣势

Mysql在第一次打开分区表的时候，需要访问所有的分区(打开的文件较多)

在Server层，Mysql认为这是同一张表，因此所有分区共同使用一个MDL锁(锁的粒度大)，影响并发。