MySQL分区表-电商数据库设计及优化学习笔记

MySQL分区表-电商数据库设计及优化学习笔记

一、背景

这里是我的电商数据库设计及优化学习笔记中关于MySQL分区表部分的内容。

二、MySQL分区表

1、使用分区表需要注意的： 

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

使用mysql>show plugins来查看 

分区表的特点： 

*在逻辑上为一个表，在物理上存储在多个文件中 

2、常用的分区表的类型： 

2.1 哈希类型的分区： 

主要特点： 

根据MOD（分区键，分区数）的值把数据行存储到表的不同分区中，这样的分区称为哈希分区。 

hash分区的键值必须是一个INT类型的数据，或者通过函数可以转为INT类型 

如何建立哈希分区： 

在建表语句最末尾加上 PARTITION BY HASH(分区键) PARITION 分区数 

如 

PARTITION BY HASH(customer_id) PARITION 4； 

分区表和非分区表的差异主要还是在物理文件上： 

分区表会包含一个以frm结尾的源数据文件，还有按照分区的id建立的数据文件 

分区表的使用跟普通表是一样的 

2.2按范围分区 

特点： 

根据分区键值的范围把数据行存储到表的不同分区中 

多个分区的范围要连续，但是不能重叠 

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

建立RANGE分区： 

在建表语句最末尾加上 PARTITION BY RANGE(customer_id) ( 

PARTITION p0 VALUES LESS THAN(10000), 

PARTITION p1 VALUES LESS THAN(20000), 

… 

PARTITION pn VALUES LESS THAN MAXVALUES, 

); 

这里表示，分区键，也就是customer_id范围在0-9999的用户存储在p0分区，10000-19999的用户在p1分区，在范围分区中最好包括一个VALUES LESS THAN MAXVALUES的分区。 

范围分区适用场景： 

分区键为日期或是时间类型 

所有查询中都包括分区键 

定期按照范围清理历史数据 

2.3 LIST分区 

按分区键取值的列表进行分区 

同范围分区一样，个分区列表不能重复 

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

建立LIST分区 

在语法上和哈希、范围分区的区别： 

PARTITION BY LIST(login_type)( 

PARTITION p0 VALUES IN(1,3,5,7,9), 

PARTITION p0 VALUES IN(2，4，6，8) 

）；

3.为登录日志表分区 

3.1业务场景分析： 

记录用户每次登录的日志，用户每次登录都会记录customer_login_log日志 

用户登录日志保存一年，一年后可以删除 

3.2表的分区类型及分区键 

根据业务场景分析，这里适合使用范围分区，选择分区键要尽量避免产生跨分区查询，在登录日志表中，只有login_time是日期类型的，而且之后需要根据日期删除，所以选择login_time作为分区键 

3.3分区用户登录日志表 

CREATE TABLE customer_login_log( 

customer_id INT UNSIGNED NOT NULL, 

login_time DATETIME NOT NULL, 

login_ip INT UNSIGNED NOT NULL, 

login_type TINYINT NOT NULL 

)ENGINE =INNODB 

PARTITION BY RANGE(YEAR(login_time))( 

PARTITION p0 VALUES LESS THAN(2017), 

PARTITION p1 VALUES LESS THAN(2018), 

PARTITION p2 VALUES LESS THAN(2019) 

往表中添加几条数据 

INSERT INTO customer_login_log(customer_id,login_time,login_ip,login_type)VALUES(1001,’2017-01-25’,0,1),(1001,’2017-07-25’,0,1),(1001,’2018-01-25’,0,1),(1001,’2018-03-25’,0,1),(1001,’2016-01-25’,0,1); 

通过MYSQL系统日志查询查看分区情况，语句如下： 

SELECT table_name,partition_name,partition_description,table_rows 

FROM information_schema.

PARTITIONS

WHERE table_name=’customer_login_log’

在这个表中没有建立MAXVIEW分区是为了维护方便，为了避免插入数据到RANGE分区失败，要做修改并添加分区的计划任务。

增加分区的语句 

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p4 VALUES LESS THAN(2020)); 

删除分区 

ALTER TABLE customer_login_log DROP PARTITION P0; 

归档过期数据 

除了删除数据，还有可能需要对过期数据进行归档 

分区数据归档迁移条件： 

1、MYSQL>=5.7 

2、结构相同 

3、归档到的数据表一定要是非分区表 

4、非临时表，不能有外键约束 

5、归档引擎要是：archive 

步骤1、先建立一个非分区表 

步骤2、数据交换 

ALTER TABLE customer_login_log exchange PARTITION P1 WITH TABLE arch_customer_login_log; 

将分区p1，也就是2017年以前的数据交换到归档表中， 

此时日志表中p1分区中已经没有数据，但是分区还存在，p1分区中的数据已经存在刚刚建的归档表中，但是之后再往日志表中插入2017年以前的数据还是会存到p1分区表中，所以此时要对p1分区进行删除。 

语句： ALTER TABLE customer_login_log DROP PARTITION P1; 

步骤3、如果有需要，可以把日志表存储引擎改为归档引擎： 

ALTER TABLE arch_customer_login_log ENGINE=archive 

归档引擎比innodb占用的内存更小，但是归档引擎的表只能进行查询操作，而不能进行写操作。

使用分区表的注意事项 

结合业务场景选择分区键，避免跨分区查询 

对分区表进行查询最好在where从句中包含分区键 

具有主键或者唯一索引的表，主键或唯一索引必须是分区键的一部分 

（所以在用户登录表中把自增id主键去掉了，不然会影响分区）

三、小结

学习了常用的分区类型：哈希分区，范围分区，LIST分区。还有增加分区、删除分区的方法。 

表分区的优点： 

1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 

2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 

3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； 

4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。 

但是也有缺点： 

分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。

使用分区要注意 

原文地址https://blog.csdn.net/qq_40916110/article/details/80782191