mysql partition 实战

系统管理员 2022-04-02 04:14 135阅读 0赞

一般情况下我们创建的表对应一组存储文件，使用`MyISAM`存储引擎时是一个`.MYI`和`.MYD`文件，使用`Innodb`存储引擎时是一个`.ibd`和`.frm`（表结构）文件。当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率。[读写分离][Link 1]分散数据库读写操作压力，[分库分表][Link 2]**分散存储压力。**

**目录**

1. hash(field)

2. key(field)

3. range(field)

4. list(field)

5. 分区管理

5.1 key/hash

5.2 range/list

6. 分区的使用

--------------------

最常见的分区方案是按`id`分区，如下将`id`的哈希值对10取模将数据均匀分散到10个`.ibd`存储文件中：

create table article(
    	id int auto_increment PRIMARY KEY,
    	title varchar(64),
    	content text
    )PARTITION by HASH(id) PARTITIONS 10

物理存储data目录：

![20190222151801183.png][]

MySQL提供的分区算法

## 1. hash(field) ##

*  相同的输入得到相同的输出
 *  适用于整型字段。

## 2. key(field) ##

和`hash(field)`的性质一样，只不过`key`是**处理字符串**的，比`hash()`多了一步从字符串中计算出一个整型在做取模操作。

create table article_key(
    	id int auto_increment,
    	title varchar(64),
    	content text,
    	PRIMARY KEY (id,title)	-- 要求分区依据字段必须是主键的一部分
    )PARTITION by KEY(title) PARTITIONS 10

## 3. range(field) ##

*  条件分区算法
 *  按照数据大小范围分区（将数据使用某种条件，分散到不同的分区中）
 *  条件运算符（less than）

create table article_range(
    	id int auto_increment,
    	title varchar(64),
    	content text,
    	created_time int,	-- 发布时间到1970-1-1的毫秒数
    	PRIMARY KEY (id,created_time)	-- 要求分区依据字段必须是主键的一部分
    )charset=utf8
    PARTITION BY RANGE(created_time)(-- 注意：分区的定义顺序依照created_time数值范围从小到大，不能颠倒
    	PARTITION p201808 VALUES less than (1535731199),	-- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
    	PARTITION p201809 VALUES less than (1538323199),	-- 2018-9-30 23:59:59
    	PARTITION p201810 VALUES less than (1541001599)	-- 2018-10-31 23:59:59
    );

![20190222152505421.png][]

## 4. list(field) ##

*  条件分区算法
 *  按照列表值分区（`in (值列表)`）

create table article_list(
    	id int auto_increment,
    	title varchar(64),
    	content text,
    	status TINYINT(1),	-- 文章状态：0-草稿，1-完成但未发布，2-已发布
    	PRIMARY KEY (id,status)	-- 要求分区依据字段必须是主键的一部分
    )charset=utf8
    PARTITION BY list(status)(
    	PARTITION writing values in(0,1),	-- 未发布的放在一个分区	
    	PARTITION published values in (2)	-- 已发布的放在一个分区
    );

![20190222152953530.png][]

## 5. 分区管理 ##

如何查看某个表的所有分区？

show create table stat

### 5.1 key/hash ###

-- 增加分区
    -- 尝试使用range对文章按照月份归档，随着时间的增加，我们需要增加一个月份
    alter table article_range add partition(
    	partition p201811 values less than (1543593599)	-- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
    	-- more
    );
    -- 删除分区
    alter table article_range drop PARTITION p201808

注意：删除分区后，分区中原有的数据也会随之删除！

### 5.2 range/list ###

`key/hash`分区的管理不会删除数据，但是每一次调整（新增或删除分区）都会将所有的数据重写分配到新的分区上。**效率极低，最好在设计阶段就考虑好分区策略。**

-- 新增分区（在原理的基础上新增）
    alter table article_key add partition partitions 4
    -- 删除分区
    alter table article_key coalesce partition 6
    
    -- 也可以按日期
    ALTER TABLE stat
        PARTITION BY RANGE(TO_DAYS(dt)) (
            PARTITION p0 VALUES LESS THAN(0),
            PARTITION p190214 VALUES LESS THAN(TO_DAYS('2019-02-14')),
            PARTITION pm VALUES LESS THAN(MAXVALUE)
        );
    -- 如何进行动态扩容？
    -- 增加分区需要用到 REORGANIZE 命令，它的作用是对某个分区重新分配。 比如明天是 15 号，那我们要给 15 号也增加个分区，实际上就是把 pm 分区拆分成2个分区：
    ALTER TABLE stat
        REORGANIZE PARTITION pm INTO (
            PARTITION p190215 VALUES LESS THAN(TO_DAYS('2019-02-15')),
            PARTITION pm VALUES LESS THAN(MAXVALUE)
        );

## 6. 分区的使用 ##

*  当数据表中的数据量很大时，分区带来的效率提升才会显现出来
 *  只有检索字段为分区字段时，分区带来的效率提升才会比较明显
 *  分区字段的选择很重要（业务逻辑要尽可能地使用分区字段作为查询条件）

分区有利于管理非常大的表，它采用[分而治之][Link 3]的逻辑。![Link 1][]

[Link 1]: /images/20220402/a185c25dacb84293b7971686c24e2ae5.png
[Link 2]: https://blog.csdn.net/alex_xfboy/article/details/77740665
[20190222151801183.png]: /images/20220402/d3288e01f68c434f81ee2fe427256775.png
[20190222152505421.png]: /images/20220402/fa71371afbe94644b937ce4faf9f9fd0.png
[20190222152953530.png]: /images/20220402/f8f2ba953f934e2c8fd1ae0e613c3b38.png
[Link 3]: https://www.baidu.com/s?wd=%E5%88%86%E8%80%8C%E6%B2%BB%E4%B9%8B&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd