系统高并发量优化之数据库分区表操作

lancelottsang

发布于 2022-4-25 16:42

浏览

0收藏

作者 | 跟鸟叔学编程
来源 | 今日头条

前言：

书接上回，上一篇文章讲了通过数据库读写分离的设计，来提升系统性能!感兴趣的小伙伴可以看系统高并发量优化之数据库读写分离这篇文章，来学习相关的知识。

本章我们来系统的学习下，数据库层面的另一种优化设计，通过数据库分区表的实现，来提升系统的访问性能。本篇文章我们采用Sqlserver数据库来进行案例的讲解，LET‘S GO !

分区表：

我们首先来了解下分区表的概念，以及相关特点!在数据库分表的操作，主要可以分为两类。第一类：水平分区，第二类：垂直分区。

水平分区：就是将一张数据量非常大的表，按同样的表结构，拆分成若干个表!这些表物理上是分开的，但是逻辑上是一个完整的整体，这样对于程序与sql来说，还是非常友好的!

垂直分区：这种方式将一张表，根据需求将表的列进行拆分，存放在不同的分区，减少表的宽度，每个分区都保存了列的所在行。

本文主要讨论水平分区的实现方式，因为这种方式在项目中的使用频率，还是非常高的!

分区表的好处是什么?

随着项目的运行，表中的数据也是日积月累的!随着数据越来越多，那么势必会影响数据的响应速度，这时候各种优化手段，都会映入眼帘!分区表则是一种不错的选择，一劳永逸地解决很多问题。

数据库作为存储系统,所有业务访问数据的操作，最终都会转化为底层数据库系统的IO行为，通过分区的设置，可以将数据按照某种规则，存储在不同的分区，充分利用硬件的支持，从而达到性能的提升。

什么情况下采用分区的策略?

数据量大肯定要考虑到优化，但未必所有的情况，都能够用分区表的方式解决问题!可以用明确分区字段，例如：时间!其次，对于旧的数据，仅仅是查询需求，不需要频繁地修改，这种情况使用分区表最佳。

例如：订单信息、手机充值记录、银行账号流水、短信发送记录等等，这些功能产生的数据量巨大，而且历史数据只存在查询的需求。

分区表相关概念以及作用：

在进行实操之前，让我们先来搞懂分区表实现，需要的相关知识，以及这些知识之间的关系。

文件组：文件组是实现表中数据分区存放的首要步骤，设计好文件存放的逻辑，这是数据表拆分的基础所在。犹如QQ的用户分组一样，我们可以将好友按照关系，进行分组，方便查找与管理。
文件：文件的角色，相当于QQ用户分组中的用户，负责具体数据存储的功能。每个用户必须有分组，文件也是同样。
分区函数：功能特殊的SQL函数，结合实际需求，自定义开发。在SQL函数中，根据需求设置数据层面的分组分配方案。例如：哪些数据应该去到哪个文件组对应的文件!
分区方案：分区方案，又称分区计划!分区方案依赖于分区函数，他们的关系例如：我有一个计划，计划的具体内容与步骤，就是SQL函数。
分区表：默认情况下，表没有分区功能!如果需要将表中的数据，按照我们制定的计划，进行分区存储的话，那么表就需要引用分区方案。

总结一下：

文件组与文件息息相关，先设置好分组，然后是每个分组对应的具体存放数据的文件。分区表依赖分区方案，分区方案依赖分区函数。

分区表实践：

上述讲解的知识点，就是分区表的实现步骤。首先就是建立文件组，那么在这里又分成俩种情况，第一种是新建库，第二种是在先有库上进行操作。

首先来看第一种情况：

这里我们创建4个文件组：

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

第二种情况设置：数据库右键属性，文件组添加。

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

接下来为每个文件组添加文件(4个文件组，创建4个对应的文件)：这里可以设置文件的存放路径，实现分区存储数据，并可以在设置的磁盘路径下，找到文件。

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

创建后我们通过SQL语句进行文件组与文件的查询：

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区接下来我们开始自定义分区函数，分区函数主要是数据进行分组化存储的依据，入表的数据会根据SQL函数的设置，将数据分配到不同的分区：

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

CREATE PARTITION FUNCTION PartByDateTime ( DATETIME )
AS RANGE RIGHT
FOR VALUES('2010-01-01', '2015-01-01','2020-01-01')

这里重点来讲解下，分区函数的相关知识：

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

参数Datetime是数据表里面日期列，通过该列进行数据分区判断!当然这里如果以ID也是可以的，那么这个参数换成int即可。

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

另一个重点就是Range后面的Right，这里有两个选项，其一是LEFT，另外一个为Right，这个属于分割标识，根据你的时间设置，将数据按日期之前，还是之后，进行存放的依据。

按照我们的设置，数据分了四段，小于2010-1-1的数据占用一个分区，大于2010-1-1并且小于2015-1-1的数据，存放一个分区。大于2015-1-1并且小于2020-1-1的一个分区，大于2020-1-1的数据占用一个分区。

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

接下来设置分区计划了，有了分区函数具体的实现，分区计划也是分分钟搞定的事情。

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

接下来创建表，如果希望表具备分区功能，那么除了建表语句之外，必须还要引入分区计划(分区策略)，这样每当数据进入表中，那么会根据策略中的分区函数设置，将数据规划到不同的文件组的文件之中，一种基于分发的策略。

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区

完成上述的操作之后，分区表如愿建立成功，接下来导入一波数据，让我们看看结果，是否与预期相同。

--插入模拟数据 
--2010之前 4条
INSERT  INTO SaleDetails VALUES(10,'2000-1-1');
INSERT  INTO SaleDetails VALUES(20,'1986-6-1');
INSERT  INTO SaleDetails VALUES(30,'1999-2-1');
INSERT  INTO SaleDetails VALUES(40,'2009-12-12');
--2010之后 3条
INSERT  INTO SaleDetails VALUES(10,'2012-6-6');
INSERT  INTO SaleDetails VALUES(20,'2010-1-1');
INSERT  INTO SaleDetails VALUES(30,'2014-6-30');
--2015之后 2条
INSERT  INTO SaleDetails VALUES(20,'2019-6-6');
INSERT  INTO SaleDetails VALUES(20,'2015-1-1');
--2020之后 5条
INSERT  INTO SaleDetails VALUES(10,'2020-1-1');
INSERT  INTO SaleDetails VALUES(10,'2020-6-1');
INSERT  INTO SaleDetails VALUES(10,'2021-2-1');
INSERT  INTO SaleDetails VALUES(10,'2022-12-12');
INSERT  INTO SaleDetails VALUES(10,'2026-12-1');

数据导入之后，我们通过普通的查询语句，观测表中数据完全正常，这就是分区表的好处，物理层面是分开的，但数据层面是一体的!

系统高并发量优化之数据库分区表操作-鸿蒙开发者社区