Database
- 数据库分库分表
- 1.横向切分
- 分库分表案例
- 需要定义规则来选择表
- 查询：
- 规则：
- 2.纵向拆分表
- 3.相对平均分配数据
- 复杂Sql语句
- 一条Sql语句执行的全过程；
- 你们觉得数据库是什么？
- 数据页
- 管理数据页
- 聚集索引：
- 非聚集索引
- 索引的创建建议：
数据库分库分表
1.横向切分
分库分表案例
按照时间横向切分
订单表，历史表；
订单表：按照年份来拆分表，每一年（月），每一年对应一个订单表；
成本：
保存：1.选择保存到哪个表中去
需要定义规则来选择表
查询：
- 确定到哪个表中去查询
- 还是按照规则来呗
规则：
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.StuName2.列转行
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语句执行的全过程；
- 客户端
- 关系引擎 接收到命令，制定执行计划（缓存—存储过程，参数化SQL语句）
- 存储引擎
- 缓冲区
- 数据库文件
- 数据库日志
你们觉得数据库是什么？
仓储—数据持久化容器—仓库；
数据库肯定是一个应用程序–可以做到数据的持久化（数据是保存在硬盘，读取慢—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不要建立索引
