数据库常识

1、关系型数据库和非关系型数据库

关系型数据库和非关系型数据库（NoSQL数据库）的主要区别包括：

• 适用性不同。关系型数据库通常采用表格形式存储数据，适合处理结构化数据；非关系型数据库采用其他数据模型，如文档、键值对、图形等，适合处理半结构化和非结构化数据。123456

• 数据一致性的要求不同。关系型数据库强调数据的一致性，确保数据的完整性和一致性；非关系型数据库则更加关注数据的可用性和灵活性，采用最终一致性模型，允许系统在一定时间内自动同步数据。
• 扩展性不同。关系型数据库的扩展性相对较差，通常只能通过升级硬件或增加节点来提高性能；非关系型数据库采用分布式架构，可以通过添加节点来水平扩展性能。
• 数据模型不同。关系型数据库采用ACID事务模型，保证数据的安全性和稳定性；非关系型数据库通常采用BASE模型，重视系统的可用性和性能，而不是强一致性。
• 数据查询语言不同。关系型数据库使用结构化查询语言（SQL）进行数据查询，支持复杂的查询和分析；非关系型数据库使用简单的键值对查询语言，如MongoDB的查询语言，限制了查询的复杂性。

关系型数据库和非关系型区别 - 知乎

关系型数据库和非关系型区别 - 知乎

二、SQL Server、Oracle和MySQL的区别

三、SQL行转列

https://www.cnblogs.com/guwei4037/p/17158658.html

四、 索引

1、 关于索引

索引类似于书的目录，它注明了表中各行数据所在的存储位置，使对数据的查找避免全表扫描，从而提升数据查询速度。

优点：提高查询效率

缺点：占用额外的存储空间，增加数据插入、更新和删除的开销

使用场景：适用于大数据量、复杂查询和频繁查询的场景

2、 索引的分类

索引通常分为两类：聚簇索引（Clustered Index）和非聚簇索引（Nonclustered Index）。

聚簇索引

CLUSTERED用于指定创建聚簇索引

特点：索引顺序与物理存储顺序相同，按索引关键字对表中的数据进行排序，然后重新存储到磁盘上（物理排序）；一个数据表只能建一个聚簇索引

使用场景：排序和连续范围值的查找，不适宜建立在频繁更改的列上；与非聚簇索引相比，聚簇索引通常提供更快的数据访问

举例：CREATE CLUSTERED INDEX index_nm

非聚簇索引

NONCLUSTERED用于指定创建非聚簇索引，默认创建的是非聚簇索引

特点：索引顺序与物理存储顺序不同，具有完全独立于数据的索引结构，不改变数据的物理存储位置；一个数据表可以建多个非聚簇索引

使用场景：频繁更新的列

举例：CREATE INDEX index_nm

3、 索引关键字

建立索引所基于的列称为索引关键字。根据索引关键字的选择，索引又具备多种叫法。

• 单列索引：当索引建立在单个列上时，该索引称为单列索引
• 复合索引：当索引建立在多个列上时，该索引称为复合索引
• 唯一索引：当索引关键字能保证其所包含的各列值不重复时，该索引是唯一索引，UNIQUE表示创建的是唯一索引；唯一+允许为空
• 主键索引：是唯一索引的特定类型，表中创建主键时自动创建的索引，一个表只能建立一个主索引；唯一的+不允许为空

注意，以上叫法并不是索引的分类，可以理解为索引的一种属性。比如，聚簇索引和非聚簇索引都可以是唯一的：

CREATE UNIQUE INDEX index_nm

CREATE UNIQUE CLUSTERED INDEX index_nm

4、 索引的原理

聚簇索引和非聚簇索引都采用了B+树的结构

五、 数据库性能优化

数据库优化是提升数据库性能和响应速度的关键步骤。下面是一些常见的数据库优化方面的经验：

1. 合理设计数据库结构

• 使用合适的数据类型：选择适合数据存储的数据类型，避免过长或过短的数据类型，以减少存储空间和提高查询速度。
• 使用合适的索引：在频繁搜索的字段上创建索引，以加快查询速度。但要注意，在频繁更新的字段上创建索引可能会影响性能。
• 正确使用主键和外键：使用适当的主键和外键来建立表与表之间的关联，提高查询效率和数据完整性。

2. 优化查询语句

• 使用合适的查询语句：根据需求选择合适的查询语句，避免使用过于复杂的查询语句，以提高查询性能。
• 避免全表扫描：使用索引或合适的查询条件来避免全表扫描，以提高查询效率。
• 合理利用缓存：根据业务需求，合理使用数据库缓存，减少查询次数和数据库的压力。

3. 数据库分区和分表

• 分区：将大型表按照一定的规则拆分成多个小表，以减少表的大小和索引的大小，提高查询效率。
• 分表：根据数据的特性，将表按照某种规则分成多个小表，提高查询效率和数据存取性能。

4. 定期维护和优化

• 定期清理无用数据：删除不再使用的数据，减少表的大小，提高查询效率。
• 定期更新统计信息：通过更新统计信息，优化查询计划，提高查询效率。
• 定期备份和恢复：定期进行数据库备份，并测试备份数据的完整性和可恢复性。

5. 硬件和网络优化

• 使用高性能的硬件设备：选择性能强大的服务器和存储设备，提高数据库的处理能力和响应速度。
• 优化网络配置：确保数据库服务器和应用服务器之间的网络连接稳定可靠，减少网络延迟和传输错误。

六、 行存储和列存储

数据库中的行式存储和列式存储

七、 数据仓库分层

数据仓库分层_数据仓库分那几层-CSDN博客原文链接：https://blog.csdn.net/weixin_42292594/article/details/131071024

数据分层是数据仓库设计中十分重要的一个环节，优秀的分层设计能够让整个数据体系更易理解和使用。

数据分层的好处：
清晰数据结构：每一个数据分层都有它的作用域和职责，在使用表的时候能更方便地定位和理解 减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算
统一数据口径：通过数据分层，提供统一的数据出口，统一对外输出的数据口径
复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层解决特定的问题。

一种通用的数据分层设计：
为了满足前面提到数据分层带来的好处，我们将数据模型分为三层：数据运营层（ ODS ）、数据仓库层（DW）和数据应用层（APP）。如下图所示。简单来讲，我们可以理解为：**ODS层存放的是接入的原始数据，DW层是存放我们要重点设计的数据仓库中间层数据，APP是面向业务定制的应用数据。**下面详细介绍这三层的设计。

1、数据运营层：ODS（Operational Data Store）
“面向主题的”数据运营层，也叫ODS层，是最接近数据源中数据的一层，数据源中的数据，经过抽取、洗净、传输，也就说传说中的 ETL 之后，装入本层。本层的数据，总体上大多是按照源头业务系统的分类方式而分类的。

一般来讲，为了考虑后续可能需要追溯数据问题，因此对于这一层就不建议做过多的数据清洗工作，原封不动地接入原始数据即可，至于数据的去噪、去重、异常值处理等过程可以放在后面的DWD层来做。

2、数据仓库层：DW（Data Warehouse）
数据仓库层是我们在做数据仓库时要核心设计的一层，在这里，从 ODS 层中获得的数据按照主题建立各种数据模型。DW层又细分为 DWD（Data Warehouse Detail）层、DWM（Data WareHouse Middle）层和DWS（Data WareHouse Servce）层。

2.1 数据明细层：DWD（Data Warehouse Detail）-维度退化层

该层一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证。同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度退化至事实表中，减少事实表和维表的关联。

另外，在该层也会做一部分的数据聚合，将相同主题的数据汇集到一张表中，提高数据的可用性，后文会举例说明。

2.2 数据中间层：DWM（Data WareHouse Middle）

该层会在DWD层的数据基础上，对数据做轻度的聚合操作，生成一系列的中间表，提升公共指标的复用性，减少重复加工。

直观来讲，就是对通用的核心维度进行聚合操作，算出相应的统计指标。

2.3 数据服务层：DWS（Data WareHouse Servce）

又称数据集市（Datamart）或宽表。按照业务划分，如流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。

一般来讲，该层的数据表会相对比较少，一张表会涵盖比较多的业务内容，由于其字段较多，因此一般也会称该层的表为宽表。

在实际计算中，如果直接从DWD或者ODS计算出宽表的统计指标，会存在计算量太大并且维度太少的问题，因此一般的做法是，在DWM层先计算出多个小的中间表，然后再拼接成一张DWS的宽表。由于宽和窄的界限不易界定，也可以去掉DWM这一层，只留DWS层，将所有的数据在放在DWS亦可。

3、数据应用层：APP（Application）
在这里，主要是提供给数据产品和数据分析使用的数据，一般会存放在 ES、PostgreSql、Redis等系统中供线上系统使用，也可能会存在 Hive 或者 Druid 中供数据分析和数据挖掘使用。比如我们经常说的报表数据，一般就放在这里。

4、维表层（Dimension）
最后补充一个维表层，维表层主要包含两部分数据： 高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。 低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万。 至此，我们讲完了数据分层设计中每一层的含义，这里做一个总结便于理解，如下图。

                        

八、 三范式

数据库三范式（Normalization）是数据库设计中的一种规范标准，旨在减少数据冗余并建立结构合理的数据库，以提高数据存储和使用的性能。三范式是按照数据依赖性的程度来划分的，包括第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。

第一范式（1NF）

第一范式要求关系型数据库中的每个列都必须是原子的，即每列的值不能再分解成其他几列。这意味着每个列中不能包含多个值或多个重复的值。如果存在多个值，应该拆分成多个列或多个表。

第二范式（2NF）

第二范式在第一范式的基础上，进一步要求每列数据完全依赖于主键。如果表中存在非主键部分依赖（即某些字段只依赖于主键的一部分），就不符合第二范式。为了满足第二范式，应将非主键部分依赖的字段抽取出来，建立新的表，并使用外键关联。

第三范式（3NF）

第三范式在第二范式的基础上，要求表中的非主键字段不依赖于其他非主键字段。如果存在传递依赖（即非主键字段依赖于其他非主键字段），就不符合第三范式。为了满足第三范式，应将传递依赖的字段抽取出来，建立新的表，并使用外键关联。

通过遵循数据库三范式，可以减少数据冗余、提高数据库的整体性能、简化数据维护和更新操作、确保数据的一致性和完整性。

九、 星型模型和雪花模型

 星型模型：所有的维度表都是和事实表直接相连，不存在渐变维度，所以有一定的数据冗余，因为有数据的冗余，很多的统计情况下，不需要和外表关联进行查询和数据分析，因此效率相对较高。

雪花模型：当有多个维度表没有直接和事实表相连，而是通过其它的维度表，间接的连接在事实表上，雪花模型的优点是减少了数据冗余，在进行数据统计或分析的时候，需要和其他的表进行关联。

星型模型冗余度高，设计简单，可读性高，关联的维度表少，查询效率高，可扩展性低。雪花模型去除了冗余，设计复杂，可读性差，关联的维度表多，查询效率低，但是可扩展性好。

十、 数据库和数据仓库的区别

数据库 Database (Oracle, Mysql, PostgreSQL)
数据仓库 Datawarehouse (Amazon Redshift, Hive)

数据库是一种具体技术，而数据仓库是一种结构体系。数据仓库是伴随着信息与决策支持系统的发展过程产生的，并不是要取代数据库。企业中一般先有数据库，然后有数据仓库。数据仓库不是“大型的数据库”，只是一个数据分析的平台。

数据库是面向事务的设计，而数据仓库是面向主题的设计。具体而言，数据库与数据仓库的区别有以下几点：

1、目的不同：数据库主要用于在线事务处理（OLTP），面向业务操作，为捕获数据而设计；而数据仓库则主要用于数据分析和决策支持。

2、数据类型不同：数据库存储的一般是交易型数据，关心的是短期内每一笔交易的细节信息，并对这些交易记录进行增删改的操作；数据仓库存储的一般是历史数据，只涉及从数据集中观察数据，并不进行增删改等操作。

3、完成任务的要求不同：数据库要求具有实时性、交互性；数据仓库则需要涉及大范围的数据计算，复杂的基于多个层次的查询语言。

4、响应时间不同：数据库要求响应时间短；数据仓库要求响应时间合理。

5、使用目的不同：数据库主要用于支持业务流程和应用程序，如交易处理、订单管理、库存控制等；而数据仓库主要用于支持决策制定、商业智能、数据挖掘、预测分析等

6、数据库设计是尽量避免冗余，一般针对某一业务应用进行设计。数据仓库在设计时有意引入冗余，依照分析需求，分析维度、分析指标进行设计。

数据库与数据仓库的区别实际讲的是OLTP与OLAP的区别。

操作型处理，叫联机事务处理OLTP（On-Line Transaction Processing，），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理。

分析型处理，叫联机分析处理OLAP（On-Line Analytical Processing）一般针对某些主题的历史数据进行分析，支持管理决策。

1、基本含义不同

OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，记录即时的增、删、改、查，比如在银行存取一笔款，就是一个事务交易。OLAP即联机分析处理，是数据仓库的核心部心，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态报表系统。

2、实时性要求不同

OLTP实时性要求高，OLTP 数据库旨在使事务应用程序仅写入所需的数据，以便尽快处理单个事务。OLAP的实时性要求不是很高，很多应用顶多是每天更新一下数据。

3、数据量不同

OLTP数据量不是很大，一般只读/写数十条记录，处理简单的事务。OLAP数据量大，因为OLAP支持的是动态查询，所以用户也许要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大。

4、用户和系统的面向性不同

OLTP是面向顾客的，用于事务和查询处理。OLAP是面向市场的,用于数据分析。

5、数据库设计不同

OLTP采用实体-联系ER模型和面向应用的数据库设计。OLAP采用星型或雪花模型和面向主题的数据库设计。