数据库知识点整理（秋招面试）

不念不忘少年蓝@ 2022-01-22 14:07 374阅读 0赞

## 数据库 ##

**1.数据库事务的四个特性：原子性、一致性、隔离性、持久性**  
事务是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，他是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事物结束时，都能保持数据一致性。  
1）**原子性**：事务是数据库的逻辑工作单位，它数据库的修改要么全部执行，要么全部不执行。  
2）**一致性**：事务前后，数据库的转台都满足所有的完整性约束。  
3）**隔离性**：并发执行的事务是隔离的，一个不影响一个。如果有两个事务，运行在相同的时间内，执行相同的任务，同一时间仅有一个请求用于同一数据。设置数据库的隔离级别，可以达到不同的隔离的效果。  
4）**持久性**：在事务完成以后，该事务所对数据库所做的更改便持久的保存在数据库之中，并不会被回滚。

**2.数据库之存储过程**  
**什么是存储过程**：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码块像Java语言里的方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。  
**存储过程的好处：**  
1）由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。  
2）一个存储过程在程序在网络交互时可以替代大堆的T-SQL语句，所以能降低网络的通信量，提高通信速率。  
3）通过存储过程能够使没有权限的用户早控制之下间接的存储数据库，从而确保数据的安全。  
**为什么使用存储过程：**  
1、增加性能 本地存储发送的内容少、调用快、预编译、高速缓存  
一般语句的执行：检查权限、检查语法，建立执行计划处理语句的要求  
存储过程：创建时已经检查了语法；第一次执行的时候执行计划被创建，被编译；  
再次执行时不需要重检查语法、不需要重编译、根据已经缓存的计划来决定是否需要重创建执行计划。

**3.数据库建表的三种范式**  
第一范式：(**确保每列保持原子性**)。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。  
第二范式：（**确保表中的每列都和主键相关**）。第二范式在第一范式的基础上更进一步。第二范式需要确保数据库表中的每列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。  
第三范式：（**确保每列都和主键直接相关，而不是间接相关**）。

**4.数据库隔离级别，每个级别会引发什么问题**  
1 .**未提交读**：事务中的修改。即使没有提交，对其他事物也是可见的。这样会提高性能，但是会导致脏读问题。  
2. **提交读**：一个事务只能读取已经提交的的事务所做的修改，换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。该级别可以解决脏读问题，但不能避免不可重复读。  
3. **可重复读**： 保证在同一个事务中多次读取同样数据的结果是一样的。可以解决不可重复读的问题，但还是不能避免幻读的问题。  
4 **可串行化**：强制事务串行执行。可以解决所有的问题。最高级别的隔离  
**MySQL默认的隔离级别是可重复读**

并发一致性问题：
    丢失修改：T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改。
    脏读数据：T1修改一个数据，T2随后读取这个数据。如果T1撤销了这次修改，那么T2读取的数据是脏数据。
    不可重复读：T2读取一个数据，T1对该数据做了修改。如果T2再次读取这个数据，此时读取的结果和第一次读取的结果不同。
    幻读：T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和第一次读取的结果不同。

**5.锁的分类**  
一般可以分为两种锁，一个是悲观锁，一个是乐观锁，悲观锁就是数据库锁机制，乐观锁一般是指用户自己实现的一种锁机制。  
**悲观锁**：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。  
**悲观锁按使用性质划分：**  
1）共享锁（Share locks简记为S锁）：也称读锁，事务A对对象T加s锁，其他事务也只能对T加S，多个事务可以同时读，但不能有写操作，直到A释放S锁。  
2）排它锁（Exclusivelocks简记为X锁）：也称写锁，事务A对对象T加X锁以后，其他事务不能对T加任何锁，只有事务A可以读写对象T直到A释放X锁。  
3）更新锁（简记为U锁）：用来预定要对此对象施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的对象将要被更新时，则升级为X锁，主要是用来防止死锁的。  
**悲观锁按照作用划分：**  
1）行锁：锁的作用范围是行级别，数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。  
2）表锁：锁的作用范围是整张表。  
\*\*乐观锁：\*\*顾名思义，就是很乐观，每次自己操作数据的时候认为没有人回来修改它，所以不去加锁，但是在更新的时候会去判断在此期间数据有没有被修改。  
**两种锁使用场景**：从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

**6.drop、delete与truncate的区别：**  
1）delete和truncate只删除标的数据不删除的结构  
2）速度，一般来说：drop>truncate> delete  
3)delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;  
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.  
4)不在需要一张表的时候，用drop（**删除标的结构**）  
5）想删除部分数据行的时候。用delete，并且带上where字句（**删除部分数据**）  
6）保留表而删除所有数据的时候用turncate（**删除全部数据**）

**7.超键，候选键、主键、外键分别是什么：**  
**超键**：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。  
**候选键**：是最小超键，即没有冗余元素的超键。  
**主键**：数据库表中对存储数据对象予以唯一和完整标示的数据列和属性组合。一个数据列只能有一个主键，且主键的取值不能为空（Null）。  
**外键**：在一个表中存在另一个表的主键称为此表的外键。

**8.什么是视图，以及视图的使用场景：**  
视图是一种**虚拟的表**，具有和物理表相同的功能，可以对视图进行增、改、查操作，视图通常是由一个表或者多个表的行或列的子集，对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。  
1）只暴露部分字段给访问者，所以就建一个虚表，就是视图。  
2）查询的数据来源于不同的表，而查询者希望一统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接在视图中获取数据，不必考虑数据来源于不同的表所带来的差异。

**9.char和varchar的区别：**  
Char的长度是固定的，而varchar的长度是可以变化的，比如，存储字符串“abc”对于char(20)，表示你存储的字符将占20个字节，包含17个空，而同样的varchar（20）只占了3个字节，20只是最大值，当你存储的字符小于20时，按实际长度存储。 char的效率要被varchar2的效率高。

**10.order by与group by的区别：**  
order by排序查询、asc升序、desc降序。group by分组查询，having 只能用于group by子句、作用于组内，having条件子句可以直接跟函数表达式。使用group by 子句的查询语句需要使用聚合函数。

**11.列举几种表的连接方式及区别：**  
内连接、子连接、外连接、（左、右、全）、交叉连接  
内连接：只有两个元素表相匹配的才能在结果集中显示。  
外连接：左外连接:左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 全外连接：连接的表中不匹配的数据全部会显示出来。  
交叉连接：笛卡尔效应，显示的结果是连接表数的乘积。

**12.在数据库中查询语句速度很慢，如何优化？**  
1.建索引 2.减少表之间的关联 3.优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面 4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据 5.尽量用PreparedStatement来查询，不要用Statement。

**13.什么是索引？索引有那几种?**  
索引是用来快速的寻找那些具有特定值的记录，所有的MySQL索引都以B+树的形式保存。如果没有索引，执行查询的时候MySQl必须从第一个记录开始扫面整个表中的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。虽然索引大大提高查询速度，同时会降低更新表的速度，如对表进行insert、update、delete。因为更新表时，不仅要保存数据，还要保存索引文件。建立索引会占用磁盘空间的索引文件，一般情况这个问题不太严重，但如果在一个大表上建立多种组合，索引文件会增长很快。索引只是提高效率的一种因素，如果有大数据量的表，就要花时间建立最优秀的索引，或优化查询语句。

索引的类型：  
1）**普通索引**：这是最基本的索引类型，而且它没有唯一性之类的限制，即允许该列有重复的值

CREATE INDEX index_name ON table_name (column_name)

2）**唯一性索引**： 这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一

CREATE UNIQUE INDEX index_name
    ON table_name (column_name)

3）**主键索引**：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在建表的时候同时自动创建主键索引。  
4）**组合索引**：指多个自字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀集合。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5）**全文索引**：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

**14.索引原理：**  
想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉)，也就是b tree或者 b+ tree，重要的事情说三遍：“平衡树，平衡树，平衡树”。当然， 有的数据库也使用哈希桶作用索引的数据结构 ， 然而， 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。  
我们平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。 事实上， 一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。没错， 再说一遍， 整个表变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTgwOTIwNg_size_16_color_FFFFFF_t_70]  
索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。  
非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。  
**聚集索引**:表记录的排列顺序和与数据的存储顺序一致(主键)，一个表只能有一个,但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。数据行的物理顺序与列值的顺序相同  
–>聚集索引规定了数据文件的存储顺序  
**非聚集索引**：表记录的排列顺序和与数据的存储顺序不一致(唯一)，一个表可以有多个  
–>是一个单独存放的指针表,用来快速找到对应点的地址  
***不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。***

**15.MySQL的两种存储引擎区别，各自的使用场景：**  
1）事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。  
2）并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。  
3）外键：InnoDB 支持外键。  
4）备份：InnoDB 支持在线热备份。  
5）崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。  
6）其它特性：MyISAM 支持压缩表和空间数据索引。  
应用场景：  
**MyISAM**管理非事务表。他提供高速存储和检索，依旧全文搜素能力。如果应用需要执行大量的SELECT查询，那么MyISAM是更好的选择。  
**InnoDB**用于事务处理用于程序，具有众多特性，包括ACID事务支持，如果应用中需要执行大量的INSERT或ＵＰＤＡＴＥ操作，则应使用InnoDB，这样可以提高用户并发操作的性能。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTgwOTIwNg_size_16_color_FFFFFF_t_70]: /images/20220122/ff9f6e1d31fa4a718290ffa8882ed909.png