后端面试知识点总结数据库 mysql

最新推荐文章于 2024-05-06 21:06:18 发布

&LinFans

最新推荐文章于 2024-05-06 21:06:18 发布

阅读量1.4k

点赞数 1

分类专栏：知识点总结面试知识点 mysql 文章标签：后端

本文链接：https://blog.csdn.net/weixin_42699130/article/details/108977798

版权

关系型和非关系型数据库

关系型数据库

指采用了关系模型来组织数据的数据库。关系模型指的是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。数据库事务必须具备ACID特性。

关系：一张二维表，每个关系都具有一个关系名，也就是表名
元组：二维表中的一行，在数据库中被称为记录
属性：二维表中的一列，在数据库中称为字段
域：属性的取值范围，也就是数据库中某一列的取值限制
关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或者多个列组成
关系模型：指对关系的描述。格式为：关系名(属性1，属性2，...属性N)，在数据库中称为表结构

主要有：MySQL，Oracle，SQLite，MariaDB，SQL server等

关系型数据库优点

容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解
使用方便：通用的SQL语言使得操作关系型数据库非常方便
易于维护：丰富的完整性（实体完整性，参照完整性和用户定义的完整性）大大降低了数据冗余和数据不一致的概率

关系型数据库缺点

网站的用户并发非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘IO是一个很大的瓶颈
网站每天产生的数据量是巨大的，对于关系型数据库来说，在一张包含海量数据的表中查询，效率很低
表结构固定，灵活性欠佳
在基于web的结构中，数据库是最难进行横向扩展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没法像web server和app server那样简单的通过添加更多的硬件和服务节点来扩展性能和负载能力。当需要对数据库系统进行升级或者扩展的时候，通常需要停机维护和数据迁移。
性能欠佳：在关系型数据库中，导致性能欠佳的主要原因是多表的关联查询，以及复杂的数据分析类型的复杂的sql报表查询。为了保证数据库的ACID原则，必须尽量按照其要求的范式进行设计，关系型数据库中的表都是存储一个格式化的数据结构。

非关系型数据库

NoSQL（Not Only SQL）指非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。

非关系型数据库以键值对存储，且结构不固定，每个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

非关系型数据库严格上不是一种数据库，应该是一种数据机构话存储方法的集合，可以是文档或者键值对等。

方便扩展（数据之间没有关系，很好扩展）
大数据量高性能（Redis一秒可以写8万次，读11万次，NoSQL的缓存记录级是一种细粒度的缓存，性能比较高）
数据类型多样（不需要事先设计数据表，随取随用，如果是数据量十分大的表则无法设计）

四大分类

key-value键值对型
- Redis，Oracle BDB
- 典型应用场景：内容缓存，主要用于处理大量数据的高访问负载，也用于一些日志系统等
- 数据模型：Key指向Value的键值对，通常用Hash Table来实现
- 优点：查找速度快
- 缺点：数据无结构化，通常只被当作字符串或者二进制数据
文档型数据库（bson格式）
- MongoDB
  - 是一个基于分布式文件存储的数据库，主要涌过来处理大量的文档
  - 是一个介于关系型数据库和非关系型数据库中间的产品；是非关系型数据库中功能最丰富、最像关系型数据库的。
- 典型应用场景：web应用（与key-value类似，value是结构化的，不同的是数据库能够了解value的内容）
- 数据模型：key-value对应的键值对，value为结构化数据
- 优点：数据结构要求不严格，表结构可以变化，不需要像关系型数据库一样需要预先定义表结构
- 缺点：查询性能不高，而且缺乏统一的查询语法
列存储数据库
- HBase
- 典型应用场景：分布式文件系统
- 数据模型：以列簇式存储，将同一列数据存在一起
- 优点：查找速度快，可扩展性强，更容易进行分布式扩展
- 缺点：功能相对有限
图关系数据库
- Neo4J，InfoGrid，不是存储图形的，存放的是关系，比如朋友圈社交网络，广告推荐
- 典型应用场景：社交网络，推荐系统等。专注于构建关系图谱
- 数据结构：图结构
- 优点：利用图结构相关算法。比如最短路径寻址，N度关系查找等
- 缺点：很多时候需要对整个图做计算才能得到需要的信息，而却这种结构不太好做分布式的集群方案。

非关系型数据库优点

用户可以根据需要添加自己需要的字段，为了获取用户的不同信息，不像关系型数据库中要对多表进行关联查询。仅需要根据ID取出相应的value就可以完成查询。
适用于SNS(Social Network Services)中，例如社交网站。系统的升级，功能的增加往往意味着数据结构巨大变动，这一点关系型数据库难以应付，需要新的结构化数据存储。由于不可能用一种数据结构化存储应付所有的新的需求，因此，非关系型数据库严格上不是一种数据库，应该是一种数据机构话存储方法的集合。

非关系型数据库缺点

只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库更合适。不适合持久存储海量数据。
没有强大的事务关系，没有保证数据的完整性和安全性

数据库索引

索引概念

是什么：索引（Index）是帮助MySQL高效获取数据的数据结构。
作用：DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。
优点：

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
分类：
- 聚集索引（主键索引）：在数据库里面，所有行数都会按照主键索引进行排序。
- 非聚集索引：就是给普通字段加上索引。
- 联合索引：就是好几个字段组成的索引，称为联合索引。遵循左前缀原则。
  - 通过字段顺序依次排序
  - 最左前缀法则：

索引不适合的场景

情景①

****数据唯一性差（一个字段的取值只有几种时）的字段****不要使用索引

比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描

情景②

频繁更新的字段不要使用索引

比如logincount登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率

情景③

****字段不在where语句出现时不要添加索引，****只有在where语句出现，mysql才会去使用索引

如果where****后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件****，不建议使用索引

情景④

where子句里对索引列使用不等于（<>），使用索引效果一般

索引失效的场景

条件中带有or

or语句两侧只有一个使用了索引，那么索引就会失效
只有当or语句前后都有索引，那么才能根据索引进行查询

复合(组合)索引没有完全使用

对于复合索引（组合索引），****如果没有使用左边的列，那么组合索引也失效****

在索引字段上使用not、<>、!=

**不等于操作符是永远不会用到索引的，**因此对它的处理只会产生全表扫描
优化方法： key<>0 改为 key>0 or key<0

对索引字段进行计算操作

如果where语句中对字段****进行了计算操作，那么也不会使用索引****

B-Tree

满足以下条件
- 出度d大于一的整数，一个节点有几个子节点
- h为正整数为树的高度
- 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d（指针和key互相间隔，节点两端是指针）
- 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶子节点的指针均为null
- 所有叶子节点具有相同的深度，等于树高h
- 一个节点中的key从左到右非递增排列
- 每个指针要么指向另外一个节点，要么指向null

查找：由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。B-Tree上查找算法的伪代码如下：

BTree_Search(node, key) {
    if(node == null) return null;
    foreach(node.key)
    {
        if(node.key[i] == key) return node.data[i];
            if(node.key[i] > key) return BTree_Search(point[i]->node);
    }
    return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);

复杂度：关于B-Tree有一系列有趣的性质，例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)O(logdN)。从这点可以看出，B-Tree是一个非常有效率的索引数据结构。另外，由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质

B+Tree

相比于B-Tree的区别
- 每个节点的指针上限为2d而不是2d+1(少了最左侧的指针)
- 内节点不再存储data，只存储key；叶子节点也不存储指针，只存放data
- 基于上一条不同点，B+Tree中叶节点和内节点所分配的空间一般不同；而B-Tree由于每个节点存放的指针和key值上限是一致的，所以在实现B-Tree往往对每个节点申请同等大小的空间。
- B+Tree叶子节点带有顺序访问指针，提高区间访问的能力；如果需要查询跨越连续叶子节点范围内的数据，不必再次从根节点出发，而是直接通过顺序访问指针遍历

索引为什么使用B-Tree（B+Tree）

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
- 主存存储：主存存储时间仅仅与存储次数呈现线性相关性
- 外存存储：外存磁盘io时间消耗巨大，通常外存会进行预读（页的整数倍）
B+/-树索引性能分析
- 红黑树：h很深，逻辑上很近的父子兄弟节点在物理上可能很远，无法利用局部性，所以红黑树的渐进复杂度也为O(h)，但是h要大很多。
- hash：查询单个数据很快，但是不常用主要是因为不支持范围查找
- B-Tree：
  1. 检索一次最多需要访问h（高度）个节点，将一个节点大小设置为一个页可以让每个节点只需要一次io就可以完全读入,所以在新建节点的时候直接申请一个页的空间。
  2. B-Tree中一次检索最多需要h-1次io（根节点常驻内存），渐进复杂度为O（h）=O(logdN)。实际应用中，出度d是一个非常大的数字，通常超过100，所以h一般很小，不超过3。一亿个，四层
- B+Tree:
  1. 比B-Tree更适合外存索引，原因和出度d有关，d越大那么h越小，那么渐进复杂度就越小。而出度取决于节点内key和data的大小：dmax=floor(pagesize/(keysize+datasize+pointsize)) 最大出度=页大小/（键值大小+数据大小+指针大小）
  2. 因为B+Tree中内节点只有key和指针两项，所以空间大小下就可以比B-数拥有更多的出度，拥有更好的性能；
  3. B+Tree更支持范围查找，叶子节点键具有相连的双向指针。
问题
- 既然磁盘io的时间远大于在内存中查找的时间，为什么不直接把所有数据一次读入内存。浪费空间
- 根节点16kb 16384 ，一个key值用big int（8字节）一个指针（6字节），根节点出度16kb/14b=1170，那么叶子节点可以放两千万个1k数据
- 索引结构存储位置，根目