面试笔记-2.数据库

素颜马尾好姑娘i 2022-11-07 14:39 94阅读 0赞

一.如何设计一个关系型数据库

> 程序实例模块：  
> 1.存储模块：逻辑关系转化成物理关系的存储管理  
> 2.存机制：优化执行效率  
> 3.SQL解析：进行SQL语句的解析  
> 4.日志管理：记录操作日志  
> 5.权限划分：进行多用户管理的权限划分  
> 6.容灾机制：灾难恢复模块  
> 7.索引管理：优化数据查询效率  
> 8.锁管理：使得数据库支持并发操作

> 存储模块（文件系统）：  
> 1.磁盘或者固态硬盘存储所有数据

二.索引

**1.简介**

> 索引是为了加速对表中数据行的检索而创建的一种分散存储的（不连续的）数据结构，硬盘级的。

**2.为什么使用索引**

> 索引能极大的减少存储引擎需要扫描的数据量，索引可以把随机IO变成顺序IO。索引可以帮助我们在进行分组、排序等操作时，避免使用临时表。

**2.索引的优缺点**

> 优点  
> 1.创建索引可以提高数据检索的效率，降低数据库的IO成本。  
> 2.可以加速表与表之间的连接。  
> 3.可以通过唯一索引的创建，保证数据的唯一性。  
> 4.可以显著减少分组与排序的时间。

> 缺点  
> 1.索引的创建和维护需要时间成本。表中的数据量越大，插入或删除数据时，调整索引所需要的时间就越长。  
> 2.索引需要单独存储，占用磁盘空间，如果设置了大量的索引，占用的空间甚至比记录本身更大。  
> 3.在对数据进行增、删、改时，需要同时更新索引中的数据，因此会影响增删改的速度。

**3.索引的数据结构分类**

> 1.二叉查找树  
> 2.B-Tree  
> 3.B+Tree  
> 4.Hash

**4.什么样的信息能成为索引**

> 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引

**5.二叉查找树结构简介和使用**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70]

> 1.简介  
> 二叉树实际上是数据域有序的二叉树，即对树上的每个结点，都满足其左子树上所有结点的数据域均小于或等于根结点的数据域，右子树上所有结点的数据域均大于根结点的数据域。

> 2.原理  
> 二叉树查找时，会先将根节点页的数据读取到内存与条件数据相比，如果条件数据大于根节点数据，将会再次读取根节点左边的数据到内存对比，反之则读取根节点右边树的数据。直到满足条件为止。

> 3.缺点  
> 1.经过频繁的修改（增删改）操作之后，二叉树会可能变成一条（满二叉树），这时会大大增加了磁盘IO的次数。  
> 2.如果二叉树的层级过多，会加大IO的读写次数，降低性能。  
> 3.每页存储的数据太少（一个节点一页），幸幸苦苦做了一次的IO操作，却只加载了一个关键字，浪费性能和空间。

**6.B-Tree简介和使用以及优缺点**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 1]

> 简介：  
> 1.根节点至少包括两个关键字  
> 2.树中每个节点最多包含m个子节点（m>=2）  
> 3.除跟节点和叶节点外，其他每个节点至少有ceil(m/2)个子节点（cell是四入五入的意思）  
> 4.所有叶子节点都位于同一层

**7.B+Tree简介和使用以及优缺点**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 2]

> 简介：
> 
> 1.  非叶子节点的子节点的数量和其跟节点的关键字数量相等  
>     2.非叶子节点只用来保存关键字，数据都保存在叶子结点中  
>     3.所有叶子节点之间都有指针相连，便于范围查询  
>     4.因为非叶子节点只保存关键字，意味着树的宽度会越多，层数会越少。

> 总结：  
> 1.B Tree保证树的平衡的过程中，每次关键字的变化，都会导致结构发生很大的变化，这个过程是特别浪费时间的

**7.Hash的简介和使用以及优缺点**

> 简介：  
> 添加的hash索引的列，引擎会对此列每一行的数据计算一个哈希码，保存在哈希表中，并保存指向此数据行的指针。  
> 哈希表只包含哈希值和指向数据行的行指针。引擎先将条件数据进行哈希运算，然后用该值找打对应哈希表的指针所指向的数据，然后取出来，再进行比较是否正确。

> 优缺点：  
> 1.哈希表的指针是顺序存储的，但是数据行的存储不是顺序的，索引无法用于排序。  
> 2.哈希索引不支持部分匹配，因为哈希值是用列的全部内容计算的，部分内容计算的值不一样。 3，哈希索引只支持等值比较，不支持任何范围查询。  
> 4.不同值得哈希计算结果可能相同，会出现哈希冲突，引擎必须遍历表中所有的行指针，逐行进行比较，如果冲突越多，代价越大。

> 总结：  
> MySql的InnoDB引擎默认为B+Tree索引结构。但也有自适应的Hash索引，根据表的使用情况自动生成哈希索引，不能人为干涉。

**8.密集索引和稀疏索引的简介和区别**

> 1.密集索引(聚集索引):表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录（整行数据）。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主键索引（mysql默认为密集索引）  
> 2.稀疏索引(非聚集索引):结构是按B+Tree组织的一个索引结构，只不过叶子节点保存的是处主键外其他建立过索引的字段值，和主键信息。  
> 3.回表：当使用非聚集索引查询，查询的数据字段并不是非聚集索引的叶子结点保存的字段的值，会根据主键信息再去密集索引查找想要的数据（密集索引的叶子节点包含了整条数据），此为回表  
> 4.覆盖索引：也称联合索引，多个字段一起建立稀疏索引(非聚集索引)，以其中一个字段为条件，在稀疏索引(非聚集索引)进行扫描即可得到结果，不用回表

**9.索引分类**

> 普通索引：仅加速查询  
> 唯一索引：加速查询 + 列值唯一（可以有null）  
> 主键索引：加速查询 + 列值唯一（不可以有null）+表中只有一个  
> 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并  
> 全文索引：对文本的内容进行分词，进行搜索

三.根据索引衍生出来的问题

**1.如何定位并优化慢sql**

1.根据慢日志定位慢查询

> 1.SHOW VARIABLES LIKE ‘%query%’ 查询慢日志相关信息  
> \-------slow\_query\_log 默认是off关闭的，使用时，需要改为on 打开　　set global slow\_query\_log = on　　　  
> \-------slow\_query\_log\_file 记录的是慢日志的记录文件  
> \-------long\_query\_time 默认是10S，每次执行的sql达到这个时长，就会被记录 set global long\_query\_time = 1 设置为1秒  
> 此配置会记录查询超过1秒的sql到文件中  
> 此配置在数据库重启后会还原，如果想永久更改，可修改配置文件my.ini

> 2.SHOW STATUS LIKE ‘%slow\_queries%’ 查看慢查询状态  
> \-------slow\_queries 数量  
> \-------slow\_queries 记录的是慢查询数量,当有一条sql执行一次比较慢时，这个vlue就是1 （记录的是本次会话的慢sql条数）

2.使用explain等工具分析sql explain 慢查询语句

> 属性：  
> 1.select\_type (sql的执行类型)：  
> \-------1.simple - 简单的select，不使用表连接或子查询  
> \-------2.primary- 复杂查询时最外层的sql  
> 2.table:输出结果集的表名或表别名  
> 3.type (sql执行的方式)  
> \-------1.null 完全不需要去访问索引和表就可以得到结果  
> \-------2.system,const 主键（primary key）或者唯一索引(unique key)的等值查询，只有一条记录会命中，速度很快  
> \-------3.eq\_ref 连接查询时，以主键（primary key）或唯一索引(unique key)作为连接条件的查询，一次查询的结果总是唯一对应(高效)  
> \-------4.ref 以普通索引为连接条件的查询（索引对应着记录可能是多条记录）  
> \-------5.range 索引的范围扫描，一般会出现在条件中带有“>,<,>=,<=,in,between,like”等范围操作  
> \-------6.index 对所有的索引进行了扫描  
> \-------7.all 全表扫描  
> 4.possible\_keys NULL 不走索引 索引名称 引擎分析后可能会走的索引  
> 5.key NULL 没有使用索引 实际走的索引  
> 6.key\_len 使用索引字段的长度  
> 7.rows: 扫描行的数量  
> 8.extra：  
> \-------1.Using Index 表示索引覆盖，不会回表查询  
> \-------2.Using Where 表示进行了回表查询  
> \-------3.Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行  
> EXPLAIN SELECT \* FROM user WHERE name=‘qqq’ AND id>=300 AND id<=400;  
> 优化器首先使用复合索引name过滤出符合条件name=‘qqq’ 的记录，然后根据复合索引name,回表获取记录，最终根据条件id>=300 AND id<=400过滤出最后的查询结果  
> \-------4.Using Flesort 没有通过索引进行排序

3.修改sql或者尽量让sql走索引

**2.联合索引最左匹配原则的成因**

> 只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合，所以在建立联合索引的时候查询最频繁的条件要放在左边

**3.索引建的越多越好吗**

> 1.数据量小的表不需要建立索引，建立会增加额外的索引开销  
> 2.不经常作为条件的列不要建立索引  
> 3.经常频繁更新的列不要建立索引，当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。  
> 4.数据重复且分布平均的字段不建立，因此他建立索引就没有太大的效果（例如性别字段，是否上架等等，不适合建立索引），如果是Oracle，可以建立BitMap位图索引

**4.有索引和没索引时的区别**

> 1.维护成本的区别  
> 2.查询数据的区别（数据量大的时候）  
> 3.空间占用和更新速的区别

**5.MyISAM和InooDB的区别**

> 锁方面  
> 1.MyISAM默认的用的是表级锁，不支持行级锁。  
> 2.InnoDB默认的是行级锁，也支持表级锁。  
> 事务  
> MyISAM:不支持事务  
> InooDB:支持事务

三.锁

**1.数据库锁的分类**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 3]

> 各种锁介绍：  
> 1.表级锁：对整个表加锁，开销小，加锁块，但锁粒度大，并发较低。  
> 2.行级锁：对一行记录加锁，开销大，加锁慢，锁粒度小，并发高。  
> 3.共享锁：读锁，多个线程可以对同一数据加共享锁，但加了共享锁的数据不能在加排他锁，用于查询。  
> 4.排他锁：写锁，当一个线程多数据加了排他锁，其他线程变不能读取此资源，用于更新操作。  
> 5.乐观锁：多个线程可以同时修改一条数据，一般通过version来控制，当多个线程的其中一个线程对数据更改时，先读取版本号，然后更改提交的时候,若版本号没变，则更新成功，否则重新更新，其他线程亦如此。并发高。  
> 6.悲观锁：就是排他锁和共享锁。并发低。一个数据同时只能被一个线程操作。

**2.MyISAM与InnoDB关于锁方面的区别**

> 1.MyISAM默认的用的是表级锁，不支持行级锁。  
> 2.InnoDB默认的是行级锁，也支持表级锁。

**2.数据库事务**

> 数据库事务(transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

**3.数据库事务的四大特性ACID**

> 原子性(Atomic)：包含的所有操作序列要么全部成功，要么全部失败回滚

> 一致性(Consistency)：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。  
> 举个例子。就是A和B的钱是1000元，A给你100元，无论最后双方转了多少次，总的钱一定是1000元。

> 隔离性(Isolation)：多个事务一起并发操作数据时，每个事务互不干扰。比如通过锁等方式实现。

> 持久性(Durabulity)：持久性，当一个事物提交之后，数据库状态永远的发生了改变，这个事物只要提交了，哪怕提交后宕机，他也确确实实的提交了，不会出现因为刚刚宕机了而让提交不生效，是要事物提交，他就像洗不掉的纹身，永远的固化了，除非你毁了硬盘

**4.事务隔离级别**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 4]  
**5.隔离级别下的并发访问的问题和解决办法**

> \[更新丢失\]  
> \-------详情：事务A读取了某数据的值，然后准备再该值上加1再写回数据库。在事务A读取后还未更新提交的时候，事务B对数据进行了修改并成功提交，而事务A还是在旧数据加1。这样，事务B的更新对于事务A而言就像被丢弃了一样  
> \[脏度\]  
> \-------详情：事务A读取了事务B更新的数据，然后B进行了回滚操作，那么A读取到的数据是脏数据  
> \[不可重复读\]  
> \-------详情：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。  
> \[幻读\]  
> \-------详情：事务A读取某些行的内容后作更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，事务A不知不觉对事务B插入的数据进行了更改

解决办法：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 5]

> 当前读  
> 像select lock in share mode(共享锁), select for update(排他锁); update,  
> insert ,delete这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁（前面的两个select语句加共享锁，update,insert,delete加排他锁）

> 快照读：不加锁的select操作就是快照读，提高并发，但安全性不够，可能读取得版本是历史版本。

> InnoDB可重复读RR隔离级别下如何避免幻读：  
> InnoDB实现了伪MVCC来避免幻读。  
> MVCC：mvcc全称是multiversion concurrent control（多版本并发控制）。mysql把每个操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号

四.语法部分

**1.mysql分页查询**

> mysql:  
> SELECT \* FROM table LIMIT x,y 从第(x+1)条记录开始，取y条记录。  
> oracle:
> 
> select \* from (select \* from (select **rownum** rn,p.\* from **table** p) where rownum <= 10) where rn > 6

**2.关键语法**

> 1.count 总条数  
> sum 总和  
> max 最大值  
> min 最小值  
> avg 平均值  
> 2.group by 分组  
> 3.having ：gropu by聚合之后使用，where实在聚合前使用，having是聚合后使用

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70]: /images/20221023/b42c161cfff54d068910037f27f72fee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 1]: /images/20221023/56c18efe15314c869792943d942cc251.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 2]: /images/20221023/860b5b519001499eac782addc5dadc58.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 3]: /images/20221023/ce781a228e3c439aa9af703e910a0e9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 4]: /images/20221023/b148075044814ab4a1cb9ea450ee6fa8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RpZ2xsZQ_size_16_color_FFFFFF_t_70 5]: /images/20221023/41b71271cd0741c68751b7504484d720.png