数据库相关知识--二

灵魂100问学习添加链接描述

1.Mysql索引

1.类型：普通索引、唯一索引、主键索引、组合索引、全文索引
2.创建方法：ａ.　建表的时候一起创建。b. 建表后，直接创建索引c. 修改表结构
主键索引只有（a.c)
3.限制：
普通索引（最基本的索引，它没有任何限制，用于加速查询。）
唯一索引（索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。）
主键索引（是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。）
组合索引（指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。）
全文索引（主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
fulltext索引配合match against操作使用，而不是一般的where语句加like。
它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。）
注：前三种索引如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）
4.总结
虽然索引可以增加查询数据，但对于更新、创建或者删除的时候，需要去维护索引，导致性能会受影响，因此，索引也不能建立太多。
索引详细参考自
MySQL索引详解

2.OLAP和OLTP的区别

联机事务处理OLTP（on-line transaction processing）:传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。
联机分析处理OLAP（On-Line Analytical Processing）:数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。
下表列出了OLTP与OLAP之间的比较。
操作特点、响应速度、吞吐量、并发访问量、资源消耗、索引类型、索引量
参考自

3.数据库

1. 什么是数据库？
   数据库=多张表+各表之间的关系
   2. 数据库表长什么样？
   数据库中每个表由一个名字标识。表包含带有列名的列，和记录数据的行。
   ID是数据库中重要的概念，叫做唯一标识符或者主键，用来表示数据的唯一性。
2. 数据库里各个表之间如何建立联系呢？
   数据库中，如果你要关联其他表，一般会以「表名_ID」作为联接。
   联系就是数据能够对应匹配，在数据库中正式名称叫联接，对应的操作叫做join。
   4.数据库和SQL是什么关系？
   数据库里面放着数据，而SQL是用来操作数据库里数据的语言（工具）。
   作者：猴子聊人物
   链接：https://www.jianshu.com/p/7b55d958860b
   来源：简书
   著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
   数据库模型有三种，分别为层次式数据库，网络式数据库和关系型数据库。而在当今的互联网中，最常用的数据库模型主要是两种，即关系型数据库和非关系型数据库
   1）键值(Key-Value)存储数据库
   键值数据库就类似传统语言中使用的哈希表。可以通过key来添加，查询或者删除数据（增删查），因为使用key主键访问，所以会获得很高的性能及扩展性。
   键值（key-value）数据库主要是使用一个哈希表，这个表中有一个特定的键和一个指针指向特定的数据。Key/value模型对于IT系统来说的优势在于简单，易部署，高并发。
   经典产品：Memcached，redis，memcacheDB，Berkeley DB
   2）列存储（column-oriented）数据库
   列存储数据库将数据存在列族（column family）中，一个列族存储经常被一起查询的相关数据。举个例子，如果我们有一个person类，我们通过会一起查询他们得姓名和年龄而不是薪资。这种情况下，名字和年龄就会被放入一个列族中，而薪资则在另一个列族中。而薪资则在另一个列族中。
   这部分数据库通常是用来应对分布式存储的海量数据。键仍然存在，但是它们的特点是指向了多个列。这些列是由列家族来安排的
   典型产品：Cassandra,HBase 基于列的存储系统
   3)面向文档（Document-Oriented）数据库
   文档型数据库的灵感是来自于Lotus Notes办公软件的，而且它同第一种键值存储相类似。该类型的数据模型是版本化的文档，半结构化的文档以特定的格式存储，比如JSON.文档型数据库可以看作是键值数据库的升级版，允许之间嵌套键值。而且文档型数据库比键值数据库的查询效率更高。
   面向文件数据库会将数据以文档的形式存储。每个文档都是自包含的数据单元，是一系列数据项的集合。每个数据项都有一个名称与对应的值，值既可以是简单的数据类型，如字符串，数字和日期等；也可以是复杂的类型，如有序列表和关联对象。数据存储的最小单位是文档，同一个表中存储的文档属性可以是不同的，数据可以使用XML，JSON或者JSNOB等多种形式存储。
   典型产品：MongoDB，GouchDB
   4)图形（Graph）数据库
   图形数据库允许我们将数据以图的方式存储。实体会被作为顶点，而实体之间的关系则会被作为边。比如我们有三个实体，Steve jobs，Apple和Next，则会有两个“Founded by”的边将Apple和Next连接到Steve Jobs。
   图形结构的数据库同其他行列以及刚性结构的SQL数据库不同，它是使用灵活的图形模型，并且能够扩展到多个服务器上。NoSQL数据库没有标准的查询语言（SQL），因此进行数据库查询需要制定数据类型。许多NoSQL数据库都有RSET式的数据接口或者查询API。
   典型产品：Neo4J,InfoGrid

4.数据库、数据仓库的区别

联系：数据仓库是由数据库以一种方式组织起来的。
区别：
（1）数据库强调范式，尽可能减少冗余；
数据仓库强调查询分析的速度，优化读取的操作，主要目的是做大量数据的查询。
(2) 数据库是行存储，数据仓库是列存储。
（3）数据库面向事务的，在线交易处理（OLTP）
数据仓库是面向主题的、集成、相对稳定、反应历史变化，存储历史数据（OLAP）。
（4）数据仓库定期写入新数据，而不覆盖原有数据，而是给数据加上时间戳。
（5）数据仓库两个基本元素：事实表和维度表
事实表存储要查询的数据；维度是看待问题的角度，如时间、部门等。

5.行存储、列存储的区别

行存储：传统数据库的存储方式，同一张表内的数据存放在一起，插入更新快。
缺点：选择(Selection)时即使只涉及某几列，所有数据也都会被读取。
列存储：优点：查询时只有涉及到的列会被读取，投影很高效，任何列都能作为索引。
缺点：选择完成时，需要对选择的列进行重新组装； INSERT/UPDATE比较慢
区别详细介绍

6.外部表和内部表有什么不同？

1. 定义：未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；
2. 区别(4个）：
   内部表数据由Hive自身管理，外部表数据由HDFS管理；
   内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定；
   删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除（这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。）；
   对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）
3. 需要注意的是
   传统数据库对表数据验证是 schema on write（写时模式），而 Hive 在load时是不检查数据是否符合schema的，hive 遵循的是 schema on read（读时模式），只有在读的时候hive才检查、解析具体的数据字段、schema。
4. 读时模式VS写时模式
   读时模式的优势是load data 非常迅速，因为它不需要读取数据进行解析，仅仅进行文件的复制或者移动。
   写时模式的优势是提升了查询性能，因为预先解析之后可以对列建立索引，并压缩，但这样也会花费要多的加载时间。
5. 生产环境中为什么建议使用外部表？

1. 因为外部表不会加载数据到hive，减少数据传输、数据还能共享。
2. hive不会修改数据，所以无需担心数据的损坏
3. 删除表时，只删除表结构、不删除数据。
   参考：添加链接描述

7.数据倾斜如何处理？

1.什么是数据倾斜？
数据分配严重不均就会产生数据倾斜。
数据倾斜，指的是并行处理的过程中，某些分区或节点处理的数据，显著高于其他分区或节点，导致这部分的数据处理任务比其他任务要大很多，从而成为这个阶段执行最慢的部分，进而成为整个作业执行的瓶颈，甚至直接导致作业失败。
2.数据倾斜的危害
（1）任务长时间挂起，资源利用率低下
（2）引发内存溢出，导致任务失败
（3）作业执行时间超出预期，导致后续依赖作业结果的作业出错
3.数据倾斜的原因
（1）读入数据的时候就是倾斜的
读入数据是计算任务的开始，但是往往这个阶段就可能已经开始出现问题了。
对于一些本身就可能倾斜的数据源，在读入阶段就可能出现个别partition执行时长过长或直接失败，如读取id分布跨度较大的mysql数据、partition分配不均的kafka数据或不可分割的压缩文件。
这些场景下，数据在读取阶段或者读取后的第一个计算阶段，就会容易执行过慢或报错。
（2）shuffle产生倾斜
在shuffle阶段造成倾斜，在实际的工作中更加常见，比如特定key值数量过多，导致join发生时，大量数据涌向一个节点，导致数据严重倾斜，个别节点的读写压力是其他节点的好几倍，容易引发OOM错误。
（3）过滤导致倾斜
有些场景下，数据原本是均衡的，但是由于进行了一系列的数据剔除操作，可能在过滤掉大量数据后，造成数据的倾斜。
例如，大部分节点都被过滤掉了很多数据，只剩下少量数据，但是个别节点的数据被过滤掉的很少，保留着大部分的数据。这种情况下，一般不会OOM，但是倾斜的数据可能会随着计算逐渐累积，最终引发问题。
4.如何预防或解决数据倾斜问题？
（1）尽量保证数据源是均衡的
程序读入的数据源通常是上个阶段其他作业产生的，那么我们在上个阶段作业生成数据时，就要注意这个问题，尽量不要给下游作业埋坑。
小建议：在程序输出写文件时，尽量不要用coalesce，而是用repartition，这样写出的数据，各文件大小往往是均衡的。
（2）对大数据集做过滤，结束后做repartition
对比较大的数据集做完过滤后，如果过滤掉了绝大部分数据，在进行下一步操作前，最好可以做一次repartition，让数据重回均匀分布的状态，否则失衡的数据集，在进行后续计算时，可能会逐渐累积倾斜的状态，容易产生错误。
（3）对小表进行广播
如果两个数据量差异较大的表做join时，发生数据倾斜的常见解决方法，是将小表广播到每个节点去，这样就可以实现map端join，从而省掉shuffle，避免了大量数据在个别节点上的汇聚，执行效率也大大提升。
（4）编码时要注意，不要人为造成倾斜
（5）join前优化
个别场景下，两个表join，某些特殊key值可能很多，很容易产生数据倾斜，这时可以根据实际计算进行join前优化。
如计算是先join后根据key聚合，那可以改为先根据key聚合然后再join。又如，需求是join后做distinct操作，在不影响结果的前提下，可以改为先distinct，然后再join。这些措施都是可以有效避免重复key过多导致join时倾斜。
4.6.具体问题具体分析
例如，读入mysql数据时倾斜，这通常是由于mysql的id分布严重不均，中间存在跨度很大的区间造成的。解决方法有两种，一是加大读取时的分区数，将倾斜的区间划分开；另一种是，先把id取出来进行等宽切割，确保每个区段的id数量一致，之后再对各区间进行数据读取。
参考：添加链接描述

8.关系型数据库通过索引提升查询效率的背后原理 ？

1. 如果没有索引，数据库引擎需要通过全表扫描来查找数据，这会产生大量的磁盘IO。
2. 关系型数据库使用B+树构建索引来加速加快查询。B+树是一种二叉查找树（每个节点的键值必须：比保存在左子树的任何键值都要大，比保存在右子树的任何键值都要小），这样随机查找某个键值时可以通过从根节点执行二叉查找来加速查询，查询成本取决于树的层数。
3. 针对范围查询和排序的优化：在每个叶子节点保存其下一个叶子节点的指针，这样当指定范围范围查询时，先从根节点根据范围的左值找到其叶子节点，之后通过向后遍历叶子节点即可找到对应范围右值，这样可以加速范围查询、排序、分组等数据库查询动作。
4. 针对磁盘读写速度的优化：除了叶子节点之外的其他节点只保存键值，这样对磁盘的单次读写可以获取到尽可能多的数据。以MySQL为例，一个1000万行的表对应的B+树按照主键查找理论上只需要3次磁盘IO，这相对于全表扫描带来的磁盘IO是多个量级的性能提升。
5. MySQL等数据库引擎在实际实现B+树索引的时候，针对磁盘读写做了优化：非叶子节点中只存放key值，叶子节点中除了key值也会存放数据，按照存放数据的不同索引区分为主索引（聚簇索引）和辅助索引：
   a) 主索引的叶子节点中存放该key值对应的完整记录，使用主索引进行查找时，可以直接输出记录；一个表只能创建一个主索引。
   b) 普通索引的叶子节点则存放对应主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找；一个表可以创建多个辅助索引。
6. 除了B+树，关系型数据库一般也支持哈希索引，哈希索引能够非常高效地进行随机查找，但是对于范围查询、排序和分组都不支持。

9.数据库事务是什么？

数据库事务是指一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性
原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中

10.常见的OLAP多维数据分析包括

向上钻取、向下钻取、切片、切块和旋转等操作

11.关系型VS非关系型数据库及其优缺点

1.常见的关系型数据库包括SQLite、Oracle、mysql，
（1）它们的特点是：
1、关系型数据库是指采用了关系模型来组织数据的数据库；
2、 事务的一致性是关系型数据库的最大特点；
3、关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
（2）其优点如下：
1、便于理解：二维表结构的关系模型相对网状、层次等其他模型来说更容易理解；
2、使用方便：使用SQL语言十分方便；
3、易于维护：大大减低了数据冗余和数据不一致的概率；
（3）而缺点是读写性能比较差。
2.典型的NoSQL数据库：临时性键值存储（memcached、Redis）、永久性键值存储（ROMA、Redis）、面向文档的数据库（MongoDB、CouchDB）、面向列的数据库（Cassandra、HBase）
（1）非关系型数据库的特点：
1、一般不支持ACID特性；
2、非关系型数据库严格上不是一种数据库，而是一种数据结构化存储方法的集合。
（2）它的优点是：
1、相对于关系型数据库，读写性能很高；
2、 数据没有耦合性，容易扩展；
3、存储数据的格式可以是多种的：nosql的存储格式是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等
（3）缺点在于不支持SQL，同时使用的成本也比较高。
注：耦合性也叫块间联系。指软件系统结构中各模块间相互联系紧密程度的一种度量。模块之间联系越紧密，其耦合性就越强，模块之间越独立则越差，模块间耦合的高低取决于模块间接口的复杂性，调用的方式以及传递的信息。
参考自
参考自

12.索引：B树、B+树区别

B树：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点；
B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；
所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3
参考自
区别:
b-树，叶子节点跟非叶子节点都储存数据
b+树，只有在叶子节点储存数据
B树：有序数组+平衡多叉树；
B+树：有序数组链表+平衡多叉树；
为啥索引使用 b+树
数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。
B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。
b+树遍历只需要遍历叶子节点以及下一个叶子节点即可

转载自

13.数据库视图

视图（子查询）：是从一个或多个表导出的虚拟的表，其内容由查询定义。具有普通表的结构，但是不实现数据存储。单表视图一般用于查询和修改，会改变基本表的数据；多表视图一般用于查询，不会改变基本表的数据。
1、视图的作用
（1）简化了操作，把经常使用的数据定义为视图
  我们在使用查询时，在很多时候我们要使用聚合函数，同时还要 显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我们只需要select * from view就可以啦，这样很方便。
（2）安全性，用户只能查询和修改能看到的数据
  因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图不可以随意的更改和删除，可以保证数据的安全性。
（3）逻辑上的独立性，屏蔽了真实表的结构带来的影响
  视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
2、视图的缺点
（1）性能差
  数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。
（2）修改限制
  当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。
  参考自

14 .热备份、冷备份

1.热备份
是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。即热备份是系统处于正常运转状态下的备份。
所以，如果你有一个冷备份而且又有热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog()方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。
1.1 优点
1． 可在表空间或数据库文件级备份，备份的时间短。
2． 备份时数据库仍可使用。
3． 可达到秒级恢复（恢复到某一时间点上）。
4． 可对几乎所有数据库实体做恢复
5． 恢复是快速的，在大多数情况下在数据库仍工作时恢复。
1.2 不足
1． 不能出错，否则后果严重
2． 若热备份不成功，所得结果不可用于时间点的恢复
3． 因难于维护，所以要特别仔细小心，不允许“以失败告终”。
2.冷备份
冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份是将关键性文件拷贝到另外的位置的一种说法。对于备份Oracle信息而言**，冷备份是最快和最安全的方法**。
2.1 冷备份的优点是：
1、 是非常快速的备份方法（只需拷文件）
2、 容易归档（简单拷贝即可）
3、 容易恢复到某个时间点上（只需将文件再拷贝回去）
4、 能与归档方法相结合，做数据库“最佳状态”的恢复。
5、 低度维护，高度安全。
不足
1、 单独使用时，只能提供到**“某一时间点上”的恢复。**
2、 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
3、 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。
4、 不能按表或按用户恢复。
如果可能的话（主要看效率），应将信息备份到磁盘上，然后启动数据库（使用户可以工作）并将备份的信息拷贝到磁带上（拷贝的同时，数据库也可以工作）。
转载自：https://baike.baidu.com/item/%E7%83%AD%E5%A4%87%E4%BB%BD/8862202#5

15.数据库的完整性

1. 约束是用来确保数据的准确性和一致性。数据的完整性就是对数据的准确性和一致性的一种保证。
   数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。
2. 分为以下四类：

1. 实体完整性：规定表的每一行在表中是惟一的实体。
2. 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
3. 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
4. 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

3. 完整性约束的类型
   可分为三种类型：与表有关的约束、域(Domain)约束、断言(Assertion)

1. 与表有关的约束:是表中定义的一种约束。可在列定义时定义该约束，此时称为列约束，也可以在表定义时定义约束，此时称为表约束。
2. 域(Domain)约束：在域定义中被定义的一种约束，它与在特定域中定义的任何列都有关系。
3. 断言(Assertion)：在断言定义时定义的一种约束，它可以与一个或多个表进行关联。