SQLServer亿万级数据优化

--创建分区文件组 alter database seclab_sgk_db add filegroup seclab_sgk_db_01 alter database seclab_sgk_db add filegroup seclab_sgk_db_02 alter database seclab_sgk_db add filegroup seclab_sgk_db_03 alter database seclab_sgk_db add filegroup seclab_sgk_db_04 alter database seclab_sgk_db add filegroup seclab_sgk_db_05 alter database seclab_sgk_db add filegroup seclab_sgk_db_06 alter database seclab_sgk_db add filegroup seclab_sgk_db_07 alter database seclab_sgk_db add filegroup seclab_sgk_db_08 alter database seclab_sgk_db add filegroup seclab_sgk_db_09 alter database seclab_sgk_db add filegroup seclab_sgk_db_10

alter database seclab_sgk_db add file(name='seclab_sgk_db_01' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_01.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_01; alter database seclab_sgk_db add file(name='seclab_sgk_db_02' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_02.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_02; alter database seclab_sgk_db add file(name='seclab_sgk_db_03' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_03.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_03; alter database seclab_sgk_db add file(name='seclab_sgk_db_04' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_04.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_04; alter database seclab_sgk_db add file(name='seclab_sgk_db_05' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_05.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_05; alter database seclab_sgk_db add file(name='seclab_sgk_db_06' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_06.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_06; alter database seclab_sgk_db add file(name='seclab_sgk_db_07' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_07.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_07; alter database seclab_sgk_db add file(name='seclab_sgk_db_08' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_08.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_08; alter database seclab_sgk_db add file(name='seclab_sgk_db_09' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_09.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_09; alter database seclab_sgk_db add file(name='seclab_sgk_db_10' ,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_10.mdf' ,size=3mb ,filegrowth=10mb ,maxsize=unlimited) to filegroup seclab_sgk_db_10;

create partition function Part_Fun(int) as range right for values ('10000000','20000000','30000000','40000000','50000000','60000000','70000000','80000000','90000000','100000000')

create partition scheme -- 创建分区方案 Part_Plan -- 分区方案名称 as partition Part_Fun -- 分区函数名称 to ('seclab_sgk_db_01','seclab_sgk_db_02','seclab_sgk_db_03','seclab_sgk_db_04','seclab_sgk_db_05','seclab_sgk_db_06','seclab_sgk_db_07','seclab_sgk_db_08','seclab_sgk_db_09','seclab_sgk_db_10','seclab_sgk_db_10') -- 分区文件组

CREATE TABLE sgk( [id] [int] primary key IDENTITY(1,1) NOT NULL, [Username1] [nvarchar](max) NULL, [Username2] [nvarchar](max) NULL, [Username3] [nvarchar](max) NULL, [Username4] [nvarchar](max) NULL, [Username5] [nvarchar](max) NULL, [Name] [nvarchar](max) NULL, [IDcard] [nvarchar](max) NULL, [Address] [nvarchar](max) NULL, [Company] [nvarchar](max) NULL, [Post] [nvarchar](max) NULL, [Phone1] [nvarchar](max) NULL, [Phone2] [nvarchar](max) NULL, [Phone3] [nvarchar](max) NULL, [Phone4] [nvarchar](max) NULL, [Phone5] [nvarchar](max) NULL, [Email1] [nvarchar](max) NULL, [Email2] [nvarchar](max) NULL, [Email3] [nvarchar](max) NULL, [Email4] [nvarchar](max) NULL, [Email5] [nvarchar](max) NULL, [QQ1] [nvarchar](max) NULL, [QQ2] [nvarchar](max) NULL, [QQ3] [nvarchar](max) NULL, [QQ4] [nvarchar](max) NULL, [QQ5] [nvarchar](max) NULL, [WeiXin1] [nvarchar](max) NULL, [WeiXin2] [nvarchar](max) NULL, [WeiXin3] [nvarchar](max) NULL, [WeiXin4] [nvarchar](max) NULL, [WeiXin5] [nvarchar](max) NULL, [Password1] [nvarchar](max) NULL, [Password2] [nvarchar](max) NULL, [Password3] [nvarchar](max) NULL, [Password4] [nvarchar](max) NULL, [Password5] [nvarchar](max) NULL, [md51] [nvarchar](max) NULL, [md52] [nvarchar](max) NULL, [md53] [nvarchar](max) NULL, [md54] [nvarchar](max) NULL, [md55] [nvarchar](max) NULL, [sha1601] [nvarchar](max) NULL, [sha1602] [nvarchar](max) NULL, [sha1603] [nvarchar](max) NULL, [sha1604] [nvarchar](max) NULL, [sha1605] [nvarchar](max) NULL, [sha2241] [nvarchar](max) NULL, [sha2242] [nvarchar](max) NULL, [sha2243] [nvarchar](max) NULL, [sha2244] [nvarchar](max) NULL, [sha2245] [nvarchar](max) NULL, [sha2501] [nvarchar](max) NULL, [sha2502] [nvarchar](max) NULL, [sha2503] [nvarchar](max) NULL, [sha2504] [nvarchar](max) NULL, [sha2505] [nvarchar](max) NULL, [Hobby] [nvarchar](max) NULL, [Color] [nvarchar](max) NULL, [Girlfriend] [nvarchar](max) NULL, [Boyfriend] [nvarchar](max) NULL, [Classmate] [nvarchar](max) NULL, [md5161] [nvarchar](max) NULL, [ip1] [nvarchar](max) NULL, [birthdate] [nvarchar](max) NULL, [md51salt] [nvarchar](max) NULL, [md52salt] [nvarchar](max) NULL, [md53salt] [nvarchar](max) NULL, [md54salt] [nvarchar](max) NULL, [md55salt] [nvarchar](max) NULL, [BankInfo] [nvarchar](max) NULL, [BankNumber] [nvarchar](max) NULL, [BankName] [nvarchar](max) NULL, ) on Part_Plan(ID)

create nonclustered index Part_Non_Name on sgk(id) on Part_Plan(ID)