18.sql server横向切分、纵向拆分表、相对平均分配数据、复杂sql语句、数据库存储原理（by-朝夕）

数据库分库分表

1.横向切分

分库分表案例

按照时间横向切分
订单表，历史表；
订单表：按照年份来拆分表，每一年（月），每一年对应一个订单表；
成本：
保存：1.选择保存到哪个表中去

需要定义规则来选择表

查询：

1. 确定到哪个表中去查询
2. 还是按照规则来呗

规则：

OrderInfo, 年来分， OrderInfo_2020 / OrderInfo_2019,
保存+查询=获取当前年份，确定表，不建议提前建好，建议在程序中动态生成；
每一次都需要判断是否存在表，可以使用缓存-----在系统初始化----读取相关表名，保存到内存
中，后续判断基于内存来；时间不能在数据库中获取；

2.纵向拆分表

注意：数据之间的关系—一堆一的关联
查询就基于主外键来做关联

3.相对平均分配数据

如果有一大批数据（大数据量的），我明知道数据库直接保存一个表，压力很大；计划就使用10个表
来存储；
100000，十个表相对平均分摊，差不过每个表存储的数据接近一万；

复杂Sql语句

1.行转列

--方法一：
select  StuName as '学员',
max(case courseName when '思想政治' then courseScore else 0 end) as '思想政治',
max(case courseName when '数学' then courseScore else 0 end) as '数学',
max(case courseName when '语文' then courseScore else 0 end) as '语文',
max(case courseName when '物理' then courseScore else 0 end) as '物理',
max(case courseName when '化学' then courseScore else 0 end) as '化学',
max(case courseName when '英语' then courseScore else 0 end) as '英语'
from course group by StuName
 


--方法二:
select ROW_NUMBER() over(order by a.StuName asc) as ID,a.StuName as '学号',MAX(a.思想政治) as '思想政治',MAX(a.数学) as '数学',MAX(a.语文) as '语文',MAX(a.物理) as '物理',MAX(a.化学) as '化学',MAX(a.英语) as '英语'
from course pivot(max(courseScore) for courseName in(思想政治,数学,语文,物理,化学,英语))a 
group by a.StuName

2.列转行

USE [CustomerDB]
GO

/****** Object:  Table [dbo].[CourseInfo]    Script Date: 2020/9/27 16:17:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CourseInfo](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[思想政治] [decimal](18, 0) NULL,
	[数学] [decimal](18, 0) NULL,
	[语文] [decimal](18, 0) NULL,
	[物理] [decimal](18, 0) NULL,
	[化学] [decimal](18, 0) NULL,
	[英语] [decimal](18, 0) NULL,
	[学员名称] [nvarchar](50) NULL,
 CONSTRAINT [PK_CourseInfo] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO [dbo].[CourseInfo]
           ([思想政治]
           ,[数学]
           ,[语文]
           ,[物理]
           ,[化学]
           ,[英语]
           ,[学员名称])
     VALUES
           (60 ,65  ,84  ,70 ,76 ,54 ,'李四'),
		   (86 ,70  ,80  ,90 ,65 ,96 ,'张三'),
		    (86 ,21  ,56  ,23 ,89 ,96 ,'王五') 


---select * from [dbo].[CourseInfo]

select 学员名称 from [dbo].[CourseInfo]  ---需要多行展示怎么办？

select 学员名称 from [dbo].[CourseInfo] 

-----列转行
select * from ( 
     select 学员名称 as StuName,courseName='思想政治',courseScore=思想政治 from [dbo].[CourseInfo]
     union all
     select 学员名称 as StuName ,courseName='数学',courseScore=数学 from [dbo].[CourseInfo]
     union all
     select 学员名称 as StuName,courseName='语文',courseScore=语文  from [dbo].[CourseInfo]
     union all
     select 学员名称 as StuName,courseName='物理',courseScore=物理 from [dbo].[CourseInfo]
     union all
     select 学员名称 as StuName,courseName='化学',courseScore=化学 from [dbo].[CourseInfo]
     union all
     select 学员名称 as StuName,courseName='英语',courseScore=英语 from [dbo].[CourseInfo] 
) t order by  t.StuName 

执行T-Sql

一条Sql语句执行的全过程；

1. 客户端
2. 关系引擎 接收到命令，制定执行计划（缓存—存储过程，参数化SQL语句）
3. 存储引擎
4. 缓冲区
5. 数据库文件
6. 数据库日志

你们觉得数据库是什么？

仓储—数据持久化容器—仓库；
数据库肯定是一个应用程序–可以做到数据的持久化（数据是保存在硬盘，读取慢—SSD），多线程操作
需要加锁；类似于一个仓库，保存数据，在需要的时候可以把数据再取出来；
这么去提高找数据的速度呢？—需要索引；
数据库存储机制

数据页

1.数据页

8kb/page, 8个数据页为一个Extent存储区，
数据存储，不能跨页存储，nvarchar()----8kb
text,Img

文本图像页

page页中保存的是数据的引用，具体数据保存在文本图像页中；

管理数据页

记录数据页在硬盘中的具体问题。

索引页：保存索引，某个字段的数据&&具体数据位置，索引是一个独立的存储，体积小—读取就快；
数据库索引探究

聚集索引：

类似的数据聚集在一起，可以排序，主键中默认就是聚集索引；
把数据有序摆放，物理排序，修改索引，会重新排序，重新调整物理排序，会很耗时，在生产环境要谨
慎；
聚集索引：只能有一个聚集索引，可以一个索引包含多个列，就是多个列都排序
保存在独立的索引页，包含字段的数据和位置信息；
查询的时候，排序，大于，小于，between查询很快；
就像字典中的拼音查字法：阿， a----可以定位到大概在哪个区域；
字典：拼音查字法

非聚集索引

字典：偏旁部首查字法
不影响数据的物理排序，重复存储一个数据和位置------字典中的偏旁部首查字法
陈-----找到偏旁，再确定这个字；存储会有重复，体积小，查找快，快速定位，直达目标；
找数据：通过索引直接定位到硬盘中的某个位置；
可以创建多个，每个索引页可以包含多个字段；
修改数据的时候：需要维护索引；

索引的创建建议：

1.主键必须建立索引，（根据实际情况选择主键类型，如果是in–聚集索引,guid–非聚集索引），创建时
间
2.外键也需要建立索引
3.经常需要Where 的字段—需要建立索引
4.Order by /group by distinct 如果查询频繁，建议建立索引
5.索引不要太多；
6.重复的数据较多的，不要建立索引，sex—1和0
7.text,img不要建立索引