高性能可扩展mysql 笔记（三）Hash分区、RANGE分区、LIST分区

迈不过友情╰ 2023-06-05 05:58 21阅读 0赞

### 个人博客网：[https://wushaopei.github.io/][https_wushaopei.github.io]    (你想要这里多有) ###

### 一、MySQL分区表操作 ###

1、定义**：数据库表分区是数据库基本设计规范之一，分区表在物理上表现为多个文件，在逻辑上表现为一个表；**

2、表分区的弊端**： 要谨慎选择分区键，错误的操作可能导致跨分区查询效率降低。**

建议 **采用物理分表的方式管理大数据。**

3、确认MySQL服务器是否支持分区表

使用 SHOW PLUGINS；在mysql命令行查看是否具有分区表的功能：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70][]

查询结果中的"partition                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL   "这一行代表当前数据库可以进行数据库分区表操作。

4、普通数据库表的物理结构与分区表的物理结构的区别：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1][]

左边为普通表的物理结构，右边为分区后的数据库表物理结构。

### 一、Hash分区表 （按HASH分区） ###

1、HASH分区的特点

>  根据MOD（分区键，分区数）的值把数据行存储到表的不同分区中，使数据可以平均的分布在各个分区中。

注意： HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型。

2、创建HASH分区：

use hash;
    CREATE TABLE `hash`.`customer_login_log`(
    	customer_id int UNSIGNED not null,
    	login_time TIMESTAMP,
    	login_ip int UNSIGNED,
    	login_type TINYINT NOT NULL
    ) PARTITION by hash(login_ip) PARTITIONS 6;

查看 customer\_login\_log 分区表物理结构：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2][]

customer\_login\_log 普通非分区表物理结构：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3][]

向HASH分区表customer\_login\_log中插入数据：

INSERT INTO customer_login_log(customer_id,login_time,login_ip,login_type)
    VALUES (1,now(),11111,1);

查看分区表数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 4][]

### 二、RANGE 分区表（按范围分区） ###

1、RANGE分区特点：

>  RANGE分区 是根据**分区键值的范围**把数据行存储到表的不同分区中，并且 多个分区的范围**要连续**，但是不能**重叠**。

注意： 默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值

2、创建RANGE分区表：

create table `customer_login_log`(
     `customer_id` int(10) UNSIGNED not null,
     `login_time` TIMESTAMP not null,
     `login_ip` int(10) UNSIGNED not null,
     `login_type` TINYINT(4) NOT NULL
    ) ENGINE=INNODB
    PARTITION BY RANGE( customer_id)(
    	 PARTITION P0 VALUES LESS THAN (10000),
       PARTITION P1 VALUES LESS THAN (20000),
       PARTITION P2 VALUES LESS THAN (30000),
       PARTITION P3 VALUES LESS THAN MAXVALUE
    )

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 5][]

**分区范围说明**：

> 当插入的数据为30000到40000分区范围的数据时，没有创建分区范围为40000的分区的情况下，会返回错误提示；但，当存在图中p3分区的MAXVALUE这一分区时，所以没有指明分区范围的数据都会被插入到p3中

3、RANGE分区的使用场景

>  *   分区键为日期或是时间类型
>  *   所有查询中都包括分区键
>  *  定期按分区范围清理历史数据

### 三、List分区（按分区键取值分区） ###

1、LIST分区的特点

>  定义： LIST分区按分区键取值的列表进行分区，并且同范围分区一样，各分区的列表只不能重复

注意：每一行数据必须能找到对应 分区列表，否则数据插入失败

2、创建LIST分区表：

create table `customer_login_log_list`(
     `customer_id` int(10) UNSIGNED not null,
     `login_time` TIMESTAMP not null,
     `login_ip` int(10) UNSIGNED not null,
     `login_type` TINYINT(4) NOT NULL
    ) ENGINE=INNODB
    PARTITION BY LIST (login_type)(
    	PARTITION P0 VALUES IN (1,3,5,7,9),
    	PARTITION P1 VALUES IN (2,4,6,8)
    )

插入包含未建立分区的分区键的值，会返回错误：

INSERT INTO customer_login_log_list(customer_id,login_time,login_ip,login_type)VALUES(100,now(),1,10)

错误截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 6][]

根据login\_type 的值进行分区 p0存储login\_type为 1,3,5,7,9;p1存储login\_type为2,4,6,8的数据，而插入的数据的login\_type为10，不包含在p0或p1的login\_type范围中，所以插入失败，返回错误提示。

[https_wushaopei.github.io]: https://wushaopei.github.io/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70]: /images/20230601/8e06ddb3582345dca8e1b68f5d194bc6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1]: /images/20230601/7a8eba8336534e37bffb07c4bdff4038.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2]: /images/20230601/83eb5f436f1f4acd8275a53dd713bb4d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3]: /images/20230601/d594ab050a034d84b0452db077c8e0ee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 4]: /images/20230601/90be16aa6ced4838b68953dffe81fdef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 5]: /images/20230601/3ba69bd7659b49f0918208855cda93f4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 6]: /images/20230601/e1d31be25ac34d5084d32c37b1ac9103.png