Mysql性能优化-7.分区

1.分区的概念

• 均匀分区，hash，key
• 条件分区，range，list

将一张表中的数据和索引，分散到不同的文件中进行存储，称之为分区操作。

Innodb：xx.idb
myisam：xx.myd，xx.myi

通常一个表对应一组数据和索引文件。一个表的数据和索引集中存储在这组文件中。

当一个表出现了大量的记录时，可以将其分布到不同的数据和索引文件中进行存储。

Innodb来说：一个表对应多个ibd文件。
myisam：一个表对应多个myi，myd文件。

分布之后，每个文件中包含的记录数量显著减少，保证单独文件的执行效率。

演示如下：最常见的分区语法，使用id，将数据分不到10个分区中：
id，主键最主要的检索要素。
在设计表时，使用partition选项，可以完成分区的配置：
需要提供分区算法和算法参数，完成分区操作。

mysql分区.png

分区完毕后，客户端看到的还是一张表，而服务器端，将数据分散到不同的分区中存储。

当前的例子使用id，作为hash算法的参数。hash算法，就是一个利用id值求余的算法，通过余数将记录分布到某个分区中。 （很明显，p1时间有变化）

mysql分区插入数据后.png

hash(id)算法，是最常用最通用的分区算法。逻辑上表示将记录均匀分布到不同的区域中。当数据表中的数据量很大，同时没有明显的逻辑区分时，使用该算法。

2.hash算法

使用一个整数的值，将记录分布到分区中，采用求余方案。
hash：哈希，一类算法的总称。求余，md5，sha，都是哈希算法。只要可以使用某个输入，得到某个特定的输出的算法就是哈希算法，要求，相同的输入应该得到相同的输出。
hash-table，哈希表。特殊的数据结构，关联数组既是哈希表，key就是输入，value就是输出。
hash分区算法，在业余逻辑层面，表示均匀分配。

mysql> create table articles(
    -> id int unsigned auto_increment primary key,
    -> subject varchar(255),
    -> context text
    -> ) engine=innodb charset=utf8
    -> partition by hash(id) partitions 10;
Query OK, 0 rows affected (0.33 sec)

3.key算法

也是一个hash算法，更加通用的hash算法。在hash中，仅仅可以针对于整数进行求与运算。而在key算法中，可以使用非整型字段，输入数据不一定为整数。
也可以使用标题，将文章均匀分布到10个区中
subject，字符串型字段，需要key算法：
核心是先通过subject计算出一个整型值，在完成求余操作。
分区字段必须为主键的一部分，分区字段一定是强检索字段。

mysql> create table articles_key(
    -> id int unsigned auto_increment primary key,
    -> subject varchar(255),
    -> context text
    -> ) engine=innodb charset=utf8
    -> partition by key(subject) partitions 10;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> create table articles_key(
    -> id int unsigned auto_increment,
    -> subject varchar(255),
    -> context text,
    -> primary key (id, subject)      //分区字段必须为主键的一部分
    -> ) engine=innodb charset=utf8
    -> partition by key(subject) partitions 10;
Query OK, 0 rows affected (0.33 sec)

根据key算法分区.png

4.range算法

范围条件算法，主要使用<小于来实现条件，分区时严格制定每个分区的条件，不能仅仅指定分区的数量了。
利用文章的发布时间，将其分布到不同的区域中：

mysql> create table articles_range(
    -> id int unsigned auto_increment,
    -> subject varchar(255),
    -> context text,
    -> publish_time int,
    -> primary key (id, publish_time)
    -> ) engine=innodb charset=utf8
    -> partition by range(publish_time)(
    -> partition p201903 values less than (1554047999),
    -> partition p201904 values less than (1556639999),
    -> partition p201905 values less than (1559318399)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> select unix_timestamp('2019-05-31 23:59:59');
+---------------------------------------+
| unix_timestamp('2019-05-31 23:59:59') |
+---------------------------------------+
|                            1559318399 |
+---------------------------------------+
1 row in set (0.00 sec)

range分区.png

range分区是一种条件分区算法。指的是，将数据使用某种条件分散到不同的区中。

5.list算法

另一种条件分区，表示列表值条件。
条件使用in（值列表）来表示。

mysql> create table articles_list(
    -> id int unsigned auto_increment,
    -> subject varchar(255),
    -> context text,
    -> publish_time int,
    -> status tinyint,
    -> primary key (id, status)
    -> ) engine=innodb charset=utf8
    -> partition by list(status)(
    -> partition pwriting values in (1, 2),
    -> partition ppublished values in(3)
    -> );
Query OK, 0 rows affected (0.04 sec)

list分区.png

6.分区管理语法

list，range，可以删除或者新增分区。
删除分区后，相应的分区数据一并删除。

hash，key，可以修改分区数量。
减少分区不影响数据。

mysql> alter table articles_range add partition(
    -> partition p201902 values less than (1551369599)
    -> );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

mysql> alter table articles_range add partition(
    -> partition p201906 values less than (1561910399),
    -> partition p201907 values less than (1564588799)
    -> );
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table articles_range drop partition p201906;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

range增.png
range删.png

mysql> alter table articles add partition partitions 3;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table articles coalesce partition 5;
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

hash增.png
hash删.png

7.分区的使用

在客户端程序不变的情况下，将服务器端的数据分布到不同的物理文件中，进而做到提供数据表的处理能力。
当数据表中数据量很大时（相对很大，千万，亿），分区可以提升效率。
只有检索字段，为分区字段时，分区效率才会比较明显。因此分区字段的选择很重要。你的业务逻辑应该根据分区字段做出调整。
分区可以将分区文件部署到不同的磁盘上，充分利用磁盘的性能。