数据库分表设计-任我行

本文只阐述一个完整的实例,直接可以复制过去用,不作过多的解释。

简单说一下分表与分区:

分区的原理:我在深圳市,但我也在中国,如果中国没有划分区域的话,搜索范围将是整个中国,查询起来很费力。现在既然中国已经划分区域当然可以直接来深圳市找我了,是不是快了很多。

分表的原理:我在中国,通过某种算法(比如查户口)知道了我在深圳,是不是也可以直接来深圳市找我,其他区域就不用去看了,本文的算法为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
)
GO
CREATE TABLE Logs_1
(
    LogId INT PRIMARY KEY,
    LogType NVARCHAR(100),--日志类型
    Operator NVARCHAR(100),--操作者
    ItemId INT,--操作项主键
    [Description] NVARCHAR(2000),--操作描述
    OperateDate DATETIME,--操作日期
    OperateIP NVARCHAR(50)--操作IP
)
GO
CREATE TABLE Logs_2
(
    LogId INT PRIMARY KEY,
    LogType NVARCHAR(100),--日志类型
    Operator NVARCHAR(100),--操作者
    ItemId INT,--操作项主键
    [Description] NVARCHAR(2000),--操作描述
    OperateDate DATETIME,--操作日期
    OperateIP NVARCHAR(50)--操作IP
)
GO
CREATE TABLE Logs_3
(
    LogId INT PRIMARY KEY,
    LogType NVARCHAR(100),--日志类型
    Operator NVARCHAR(100),--操作者
    ItemId INT,--操作项主键
    [Description] NVARCHAR(2000),--操作描述
    OperateDate DATETIME,--操作日期
    OperateIP NVARCHAR(50)--操作IP
)
GO
CREATE TABLE Logs_4
(
    LogId INT PRIMARY KEY,
    LogType NVARCHAR(100),--日志类型
    Operator NVARCHAR(100),--操作者
    ItemId INT,--操作项主键
    [Description] NVARCHAR(2000),--操作描述
    OperateDate DATETIME,--操作日期
    OperateIP NVARCHAR(50)--操作IP
)
GO
CREATE TABLE Logs_5
(
    LogId INT PRIMARY KEY,
    LogType NVARCHAR(100),--日志类型
    Operator NVARCHAR(100),--操作者
    ItemId INT,--操作项主键
    [Description] NVARCHAR(2000),--操作描述
    OperateDate DATETIME,--操作日期
    OperateIP NVARCHAR(50)--操作IP
)
GO
CREATE TABLE Logs_6
(
    LogId INT PRIMARY KEY,
    LogType NVARCHAR(100),--日志类型
    Operator NVARCHAR(100),--操作者
    ItemId INT,--操作项主键
    [Description] NVARCHAR(2000),--操作描述
    OperateDate DATETIME,--操作日期
    OperateIP NVARCHAR(50)--操作IP
)
GO
CREATE TABLE Logs_7
(
    LogId INT PRIMARY KEY,
    LogType NVARCHAR(100),--日志类型
    Operator NVARCHAR(100),--操作者
    ItemId INT,--操作项主键
    [Description] NVARCHAR(2000),--操作描述
    OperateDate DATETIME,--操作日期
    OperateIP NVARCHAR(50)--操作IP
)
GO
CREATE 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 Logs
AS
    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_8
GO
--根据dbo.LogId最后一次插入的Id值来确定到底需要插入到哪张日志子表,并返回表名
CREATE FUNCTION [dbo].[GetTableName](
@Id INT,
@table NVARCHAR(50)=Logs)
RETURNS NVARCHAR(60)
AS
BEGIN
    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 ‘‘;
END
GO
--添加日志
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=Ninsert +@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=@operateip
GO
--修改日志
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=Nupdate +@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=@operateip

GO
--删除日志
CREATE PROC [dbo].[LogDelete]
@logid int
AS
BEGIN
    declare @tablename nvarchar(100)=Logs,@sql nvarchar(1000)=‘‘

    set @tablename=dbo.GetTableName(@logid,Logs);
    set @sql=Ndelete +@tablename +  where LogId=+@logid;
    exec(@sql);
END
GO
--得到日志查询的总记录数
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)
AS
declare @sql nvarchar(2000)=‘‘
    set @sql =NSELECT * FROM (SELECT ROW_NUMBER()OVER(ORDER BY LogId) line,* FROM Logs WHERE 1=1 +@condition+ )A WHERE A.line>+@begin+ AND A.line<=+@end;

 

数据库分表设计-任我行,布布扣,bubuko.com

数据库分表设计-任我行

上一篇:Mysql数据库导出压缩并保存到指定位置备份脚本


下一篇:Oracle自增序列