首页 > 代码库 > 数据库分表设计-任我行
数据库分表设计-任我行
本文只阐述一个完整的实例,直接可以复制过去用,不作过多的解释。
简单说一下分表与分区:
分区的原理:我在深圳市,但我也在中国,如果中国没有划分区域的话,搜索范围将是整个中国,查询起来很费力。现在既然中国已经划分区域当然可以直接来深圳市找我了,是不是快了很多。
分表的原理:我在中国,通过某种算法(比如查户口)知道了我在深圳,是不是也可以直接来深圳市找我,其他区域就不用去看了,本文的算法为dbo.GetTableName(),通过这个算法知道了数据存储在哪张表里面。
--用于保存所有日志ID,与操作项的Id,在操作日志的时候,--可以依据此表来获取最后一次日志插入的ID,依据此ID来决定插入日志子表CREATE TABLE LogId( Id INT IDENTITY(1,1) PRIMARY KEY, ItemId int)GO--创建日志表,共创建8张表CREATE TABLE Logs_0( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP)GOCREATE TABLE Logs_1( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP)GOCREATE TABLE Logs_2( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP)GOCREATE TABLE Logs_3( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP)GOCREATE TABLE Logs_4( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP)GOCREATE TABLE Logs_5( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP)GOCREATE TABLE Logs_6( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP)GOCREATE TABLE Logs_7( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP)GOCREATE TABLE Logs_8( LogId INT PRIMARY KEY, LogType NVARCHAR(100),--日志类型 Operator NVARCHAR(100),--操作者 ItemId INT,--操作项主键 [Description] NVARCHAR(2000),--操作描述 OperateDate DATETIME,--操作日期 OperateIP NVARCHAR(50)--操作IP);GO--创建视图,关联起所有的日志子表,查询时直接操作此视图CREATE VIEW LogsAS SELECT * FROM dbo.Logs_0 UNION ALL SELECT * FROM dbo.Logs_1 UNION ALL SELECT * FROM dbo.Logs_2 UNION ALL SELECT * FROM dbo.Logs_3 UNION ALL SELECT * FROM dbo.Logs_4 UNION ALL SELECT * FROM dbo.Logs_5 UNION ALL SELECT * FROM dbo.Logs_6 UNION ALL SELECT * FROM dbo.Logs_7 UNION ALL SELECT * FROM dbo.Logs_8GO--根据dbo.LogId最后一次插入的Id值来确定到底需要插入到哪张日志子表,并返回表名CREATE FUNCTION [dbo].[GetTableName](@Id INT,@table NVARCHAR(50)=‘Logs‘)RETURNS NVARCHAR(60)ASBEGIN DECLARE @tablename NVARCHAR(60) DECLARE @tableId INT, @delimiter INT SET @delimiter = 20000 --注意,一旦定下,不可再修改,因项目而异,可以更大小,建议不要超过50W SET @tableId = FLOOR(@Id/@delimiter) SET @tablename = ‘dbo.‘ + @table + ‘_‘ + CAST(@tableId AS nvarchar) RETURN @tablename RETURN ‘‘;ENDGO--添加日志CREATE PROC [dbo].[LogAdd]@logid int,@logtype nvarchar(100),@operator nvarchar(100),@itemid int,@description nvarchar(2000),@operatedate datetime,@operateip nvarchar(50)as declare @tablename nvarchar(100) = ‘Logs‘,@sql nvarchar(1000) = ‘‘ insert LogId(ItemId)values(@itemid) set @logid = @@IDENTITY set @tablename = dbo.GetTableName(@logid, ‘Logs‘) set @sql=N‘insert ‘+@tablename+‘(LogId,LogType,[Operator],ItemId,[Description],OperateDate,OperateIP)values(@logid,@logtype,@operator,@itemid,@description,@operatedate,@operateip);SELECT @@IDENTITY‘ exec sp_executesql @sql,N‘@logid int,@logtype nvarchar(100),@operator nvarchar(100),@itemid int,@description nvarchar(2000),@operatedate datetime,@operateip nvarchar(50)‘ ,@logid=@logid,@logtype=@logtype,@operator=@operator,@itemid=@itemid,@description=@description,@operatedate=@operatedate,@operateip=@operateipGO--修改日志CREATE PROC [dbo].[LogUpdate]@logid int,@logtype nvarchar(100),@operator nvarchar(100),@itemid int,@description nvarchar(2000),@operatedate datetime,@operateip nvarchar(50)as declare @tablename nvarchar(100)=‘Logs‘,@sql nvarchar(1000)=‘‘ set @tablename=dbo.GetTableName(@logid,‘Logs‘) set @sql=N‘update ‘+@tablename+‘ set LogType,[Operator],[Description],OperateDate,OperateIP where LogId=@logid;SELECT @@IDENTITY‘ exec sp_executesql @sql,N‘@logid int,@logtype nvarchar(100),@operator nvarchar(100),@itemid int,@description nvarchar(2000),@operatedate datetime,@operateip nvarchar(50)‘ ,@logid=@logid,@logtype=@logtype,@operator=@operator,@itemid=@itemid,@description=@description,@operatedate=@operatedate,@operateip=@operateipGO--删除日志CREATE PROC [dbo].[LogDelete]@logid intASBEGIN declare @tablename nvarchar(100)=‘Logs‘,@sql nvarchar(1000)=‘‘ set @tablename=dbo.GetTableName(@logid,‘Logs‘); set @sql=N‘delete ‘+@tablename + ‘ where LogId=‘+@logid; exec(@sql);ENDGO--得到日志查询的总记录数CREATE PROC [dbo].[LogSearchCount]@condition NVARCHAR(1000)AS declare @sql nvarchar(2000)=‘‘ set @sql = ‘select count(0) from Logs where 1=1 ‘+@condition exec(@sql);GO--查询日志,直接操作Logs视图CREATE PROC [dbo].[LogSearch]@begin NVARCHAR(10),@end NVARCHAR(10),@condition NVARCHAR(1000)ASdeclare @sql nvarchar(2000)=‘‘ set @sql =N‘SELECT * FROM (SELECT ROW_NUMBER()OVER(ORDER BY LogId) line,* FROM Logs WHERE 1=1 ‘+@condition+‘ )A WHERE A.line>‘+@begin+‘ AND A.line<=‘+@end;
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。