mysql分区设置

mysql分区

应用背景

mysql的数据表会有三个文件分别是:索引文件,数据文件,表结构文件都保存在磁盘中
数据库对数据进行操作时就会对数据文件进行io读写
这就会照成一个问题,随着数据的增多文件就会随之增大,导致io的效率变慢
即使数据查询使用了索引在查询效率上也会变慢
这时就要考虑给数据文件进行瘦身,以下有两种瘦身方式:
方式一: 分表
分表故名思议,就是将数据根据条件由一张表分配到多张表中从而减少单表的数据量,这样也会有个问题就是之前的sql需要修改才能把拆分的数据查出来

方式二: 分区
分区是在不该表结构的基础上对表数据文件的拆分,不影响之前sql语句的查询效果,当对操作查询时数据库会使用并行io读取所有分区的文件数据,但是分区也有瓶颈,一张表最大支持1024个分区 因此推荐使用此方法

分区操作

#修改表配置分区
#注意: 第一次设置分区如果分区条件没有匹配所有数据则会报错,所以在设置分区的匹配条件时需要涵盖所有数据
#修改  表          分区       使用 分区类型 匹配的值
ALTER TABLE <表名> PARTITION BY RANGE (YEAR(<列名>))
(
    #设置分区  分区名称                 分区的范围值<
    PARTITION p2020 VALUES LESS THAN (2021),
	PARTITION p2021 VALUES LESS THAN (2022)
)

#追加分区
ALTER TABLE <表名> ADD PARTITION
(
PARTITION p2022 VALUES LESS THAN (2023)
)

#查看表分区情况
SELECT 
	PARTITION_NAME '分区名称',
	TABLE_ROWS '插入数据'
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'log_operate_v1'

解释:

#分区类型分别有:
RANGE: 分区(portioning)：根据列值所属的范围区间，将元组分配到各个分区。
LIST: 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH: 分区：根据用户定义的函数的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY: 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。