分库分表技术及技术方案

一、分库分表的必要性

分库分表技术的使用，主要是数据库产生了瓶颈，如单库的并发访问或单表的查询都超出了阈值。对系统使用造成一定的影响，不得已而产生的技术。

通过分库分表技术来解决此类问题，但正因为使用此技术，会产生ACID一系列的问题，各类中间件解决此类问题各有各的优势。

提示：如场景无必要，千万不要使用分库分表。

二、分库分表的思路

1、垂直区分

垂直分库：从业务角度，一个库分成多个库，如把订单和用户信息分成两个库来存储。这样的好处就是可以微服务了。每块的业务单独部署，互不影响，通过接口去调用。

垂直分表：把大表分成多个小表，如热点数据和非热点数据分开，提高查询速度。

2、水平区分

水平分表：同一业务如数据量大了以后，根据一定的规则分为不同的表进行存储。

水平分库：如订单分成多个库存储，分解服务器压力。

以上一般来说，垂直分库和水平分表用的会多些。

三、分库分表的原理分析

分库分表常用的方案：Hash取模方案和range范围方案；

路由算法为最主要的算法，指得是把路由的Key按照指定的算法进行存放；

1、Hash取模方案

根据取余分配到不同的表里。要根据实际情况确认模的大小。此方案由于平均分配，不存在热点问题，但数据迁移很复杂。

2、Range范围方案

range根据范围进行划分，如日期，大小。此方案不存在数据迁移，但存在热点问题。

四、分库分表的技术选型

1、技术选型

解决方案主要分为4种：MySQL的分区技术、NoSql、NewSQL、MySQL的分库分表。

（1）mysql分区技术：把一张表存放在不同存储文件。由于无法负载，使用较少。

（2）NoSQL（如MongoDB）：如是订单等比较重要数据，强关联关系，需约束一致性，不太适应。

（3）NewSql(具有NoSQL对海量数据的存储管理能力，还保持了传统数据库支持ACID和SQL等特性):如TiDB可满足需求。

（4）MySQL的分库分表：如使用mysql,此种方案为主流方式。

2、中间件

解决此类问题的中间件主要为：Proxy模式、Client模式。

（1）Proxy模式

（2）Client模式

把分库分表相关逻辑存放在客户端，一版客户端的应用会引用一个jar，然后再jar中处理SQL组合、数据库路由、执行结果合并等相关功能。

（3）中间件的比较

由于Client模式少了一层，运维方便，相对来说容易些。

五、分库分表的实践

根据容量（当前容量和增长量）评估分库或分表个数 ->选key（均匀）->分表规则（hash或range等）->执行（一般双写）->扩容问题（尽量减少数据的移动）。

在这里我们选用中间件share-jdbc。

1、引入maven依赖

2、spring boot规则配置

行表达式标识符可以使用${...}或$->{...}，但前者与Spring本身的属性文件占位符冲突，因此在Spring环境中使用行表达式标识符建议使用$->{...}。

3、创建DataSource

通过ShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource，ShardingDataSource实现自JDBC的标准接口DataSource。然后即可通过DataSource选择使用原生JDBC开发，或者使用JPA, MyBatis等ORM工具。

打开MsSQL2008,找到作业该项,如果打不开或者SQL Server代理是未启动状态,请先在windows服务中启动SQL Server代理(参考图片),

2

右击MsSQL2008对象资源管理器中的作业,选择新建作业,输入该作业你想用的名称,类别不用管,说明里面是输入一些该作业完成的功能,可不写,请务必勾选已启用复选框.

3

点击新建作业窗体左侧的步骤项,点击右侧区域下方的新建按钮,输入步骤名称,类型请选择Transact-SQL脚本(T-SQL),运行身份默认,数据库请选择要进行分区的数据库,请不要选择master默认的,命令文本框中输入如下代码:

/*--------------------创建数据库的文件组和物理文件------------------------*/

declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath

varchar(50), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50)

set @tableName='要分区的数据库名称'

set @newDay=CONVERT(varchar(100), GETDATE(), 23)--23:按天 114:按时间

set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-1,getdate()), 120 ) as datetime)

set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')

set @fileGroupName=N'G'+@newNameStr

set @ndfName=N'F'+@newNameStr+''

set @fullPath=N'E:\\SQLDataBase\\UserData\\'+@ndfName+'.ndf'

set @partFunName=N'pf_Time'

set @schemeName=N'ps_Time'

--创建文件组

if exists(select * from sys.filegroups where name=@fileGroupName)

begin

print '文件组存在,不需添加'

end

else

begin

exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')

print '新增文件组'

if exists(select * from sys.partition_schemes where name =@schemeName)

begin

exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')

print '修改分区方案'

end

if exists(select * from sys.partition_range_values where function_id=(select function_id from

sys.partition_functions where name =@partFunName) and value=@oldDay)

begin

exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')

print '修改分区函数'

end

end

--创建NDF文件

if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))

begin

print 'ndf文件存在,不需添加'

end

else

begin

exec('ALTER DATABASE '+@tableName+'ADD FILE (NAME ='+@ndfName+',

FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')

print '新创建ndf文件'

end

/*--------------------以上创建数据库的文件组和物理文件------------------------*/

--分区函数

if exists(select * from sys.partition_functions where name =@partFunName)

begin

print '此处修改需要在修改分区函数之前执行'

end

else

begin

exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHTFOR VALUES ('''+@newDay

+''')')

print '新创建分区函数'

end

--分区方案

if exists(select * from sys.partition_schemes where name =@schemeName)

begin

print '此处修改需要在修改分区方案之前执行'

end

else

begin

exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO

(''PRIMARY'','''+@fileGroupName+''')')

print '新创建分区方案'

end

print '---------------以下是变量定义值显示---------------------'

print '当前数据库:'+@tableName

print '当前日期:'+@newDay+'(用作随机生成的各种名称和分区界限)'

print '合法命名方式:'+@newNameStr

print '文件组名称:'+@fileGroupName

print 'ndf物理文件名称:'+@ndfName

print '物理文件完整路径:'+@fullPath

print '分区函数:'+@partFunName

print '分区方案:'+@schemeName

/*

--查看创建的分区函数

select * from sys.partition_functions

--查看分区函数的临界值

select * from sys.partition_range_values

--查询分区方案

select * from sys.partition_schemes

--查询表数据在哪个分区中存储,where条件查询第一个分区中存在的数据

select *,$partition.pf_SaveTime(分区字段) as Patition from 表名 where $partition.pf_SaveTime(分区字段)=1

*/

GO

点击确定按钮

上述代码中的变量名称,路径等均可自行修改,上述是按天为单位,以G开头的日期作为文件组名称,以F开头的日期作为物理分区文件名即ndf文件名称

4

选择新建分区左侧的计划项,然后点击右侧区域下方的新建按钮,设定新建分区的时间间隔,图中设置的是每天创建一个新的分区,用户也可以自行修改,按月,按周,按自定义时间等

其他的条目,通知,警报,目标可自行设置,也可不设置,至此自动创建分区的计划任务已成功设置.

END

步骤二:对表应用分区方案和分区函数

右击要分区的表,选择存储菜单下的创建分区,上述步骤一中创建的分区函数是按datetime类型进行的分区,所以创建分区的时候需要选择相应类型的字段作为分区依据,用户也可以根据int型或其他类型的字段进行分区,选择下一步,使用现有分区函数下一步使用现有分区方案,下一步会自动按照分区方案执行的日期进行分区,继续点击下一步选择立即执行,完成后即可完成的整体的表分区自动执行.

需注意:刚设置完第一步的计划任务,可能不会执行第一步的分区方案的代码,也就意味着没有创建分区函数和分区方案,第二步设置的时候使用现有分区函数和使用现有分区方案也就不可用,可先把第一步的代码执行一遍即可.

---