数据库基础

1 数据库事务以及四个特性

数据库面试问题参考：https://blog.csdn.net/si444555666777/article/details/82111355
事务（Transaction）是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。

事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID。

1. 原子性（Atomicity）
   原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，

2. 一致性（Consistency）
   一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，
   拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

3. 隔离性（Isolation）
   隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
   多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。

4. 持久性（Durability）
   持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

不同的隔离级别：

• Read Uncommitted（读取未提交内容）：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
• Read Committed（读取提交内容）：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题。
• Repeated Read（可重复读）：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读
• Serialization（可串行化）：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。

2 数据库的三大范式

第一范式：列不可分，所有属性都不能再分解为更基本的数据单位时，称R是满足第一范式
第二范式：有主键，保证完全依赖。所有非主属性都完全依赖于所有的主键，称R满足第二范式
第三范式：无传递依赖。即非主属性不传递依赖于主键。

3 mysql的MVCC机制

MVCC是一种多版本并发控制机制，是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。MVCC是通过保存数据在某个时间点的快照来实现该机制，其在每行记录后面保存两个隐藏的列，分别保存这个行的创建版本号和删除版本号，然后Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。

4 MySQL引擎和区别

MySQL引擎最常用的是InnoDB和Mylsam。
InnoDB和Mylsam的区别：
1）事务：MyISAM类型不支持事务处理，而InnoDB类型支持，提供事务支持以及外部键等高级数据库功能。
2）性能：MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快。
3）行数保存：InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。
4）索引存储：对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持。

MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
5）服务器数据备份：InnoDB必须导出SQL来备份，LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
6）锁的支持：MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

5 数据库索引

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。
使用的是B+树，具体见数据结构中B+树的描述；
https://blog.csdn.net/si444555666777/article/details/82111355

数据库索引为何使用B+树：

• B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
• B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；
• B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

索引的优缺点

优点：

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

• 加速表和表之间的连接；

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

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

缺点：

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；

• 空间方面：索引需要占物理空间。

索引的分类

• 普通索引和唯一性索引：索引列的值的唯一性

• 单个索引和复合索引：索引列所包含的列数

• 聚簇索引：表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。

• 非聚簇索引：叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。在数据页的上一级索引页它为每一个数据行存储一条索引记录。

聚集索引可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了；而非聚集索引是把一个很大的范围，转换成一个小的地图，然后你需要在这个小地图中找你要寻找的信息的位置，最后通过这个位置，再去找你所需要的记录。

6 如何优化MySQL

MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面。
1、SQL语句的优化
　　SQL语句的优化主要包括三个问题，即如何发现有问题的SQL、如何分析SQL的执行计划以及如何优化SQL。
　　a. 怎么发现有问题的SQL?（通过MySQL慢查询日志对有效率问题的SQL进行监控）
　　b. 通过explain查询和分析SQL的执行计划
　　c. SQL语句的优化
　　 　　优化insert语句：一次插入多值；
　　 　　应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
　　 　　应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；
　　 　　优化嵌套查询：子查询可以被更有效率的连接(Join)替代；
2、索引优化
建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引。但必须注意以下几种可能会引起索引失效的情形：

以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
OR语句前后没有同时使用索引；
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
对于多列索引，必须满足最左匹配原则(eg,多列索引col1、col2和col3，则 索引生效的情形包括col1或col1，col2或col1，col2，col3)。很多时候用 exists 代替 in 是一个好的选择。
3、数据库表结构的优化
数据库表结构的优化包括选择合适数据类型、表的范式的优化、表的垂直拆分和表的水平拆分等手段。

7 存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。存储过程具有以下特点：

• 存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率；
• 当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码；
• 减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小；
• 通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

8 简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

• Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；
• Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；
• Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在删除表的时候用drop；在想删除部分数据行时候用delete；在保留表而删除所有数据的时候用truncate。

9 关系和非关系型数据库区别

9.1 关系型数据库

关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

• 优点：
  容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解
  使用方便：通用的SQL语言使得操作关系型数据库非常方便
  易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率
• 缺点（瓶颈）
  导致性能欠佳的最主要原因是多表的关联查询，以及复杂的数据分析类型的复杂SQL报表查询。
  1） 并发读写性能较差：为了维护一致性所付出的巨大代价就是其读写性能比较差
  2） 海量数据查询效率低
  3） 扩展性和可用性差：具有固定的表结构，因此，其扩展性极差
  在基于web的结构当中，数据库是最难进行横向扩展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库却没有办法像web server和app server那样简单的通过添加更多的硬件和服务节点来扩展性能和负载能力。对于很多需要提供24小时不间断服务的网站来说，对数据库系统进行升级和扩展是非常痛苦的事情，往往需要停机维护和数据迁移。

9.2 非关系型数据库

非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，这样就不会局限于固定的结构，可以减少一些时间和空间的开销。使用这种方式，用户可以根据需要去添加自己需要的字段，这样，为了获取用户的不同信息，不需要像关系型数据库中，要对多表进行关联查询。仅需要根据id取出相应的value就可以完成查询。
但非关系型数据库由于很少的约束，他也不能够提供像SQL所提供的where这种对于字段属性值情况的查询。并且难以体现设计的完整性。他只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，SQL数据库显的更为合适。

依据结构化方法以及应用场合的不同，主要分为以下几类：

1. 面向高性能并发读写的key-value数据库：Redis,Tokyo Cabinet,Flare就是这类的代表
2. 面向海量数据访问的面向文档数据库：特点是可以在海量的数据中快速的查询数据，典型代表为MongoDB以及CouchDB
3. 面向可扩展性的分布式数据库：这类数据库想解决的问题就是传统数据库存在可扩展性上的缺陷，这类数据库可以适应数据量的增加以及数据结构的变化

9.3 关系型 VS 非关系型

关系型数据库的最大特点就是事务的一致性：传统的关系型数据库读写操作都是事务的，具有ACID的特点，这个特性使得它可以用于几乎所有对一致性有要求的系统中，如典型的银行系统。

相反地，关系型数据库为了维护一致性所付出的巨大代价就是其读写性能比较差，而像微博、facebook这类SNS的应用，对并发读写能力要求极高，关系型数据库已经无法应付(在读方面，传统上为了克服关系型数据库缺陷，提高性能，都是增加一级memcache来静态化网页，而在SNS中，变化太快，memchache已经无能为力了)，因此，必须用新的一种数据结构存储来代替关系数据库。
关系数据库的另一个特点就是其具有固定的表结构，因此，其扩展性极差，而在SNS中，系统的升级，功能的增加，往往意味着数据结构巨大变动，这一点关系型数据库也难以应付，需要新的结构化数据存储。
于是，非关系型数据库应运而生，由于不可能用一种数据结构化存储应付所有的新的需求，因此，非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。
必须强调的是，数据的持久存储，尤其是海量数据的持久存储，还是需要一种关系数据库这员老将。

SQL面试50题（b站）

数据库关系表示例：
student(s_id,s_name,s_birth,s_sex)学生编号，姓名，生日，性别
Course(c_id,c_name,t_id)课程编号，课程名，教师编号
Teacher(t_id,t_name)教师编号，教师姓名
Score(s_id,c_id,s_score)学生编号，课程编号，分数

1、查询课程编号为01的课程比02的课程成绩高的所有学生的学号

--1.将01，02课程成绩表相关数据提取处理
SELECT s_id,c_id,s_score from score WHERE c_id='01' 
SELECT s_id,c_id,s_score from score WHERE c_id='02'
--2.合并得到 s_id,s_score(01),s_score(02)这样的一张表
select a.s_id "s_id",a.s_score "01",b.s_score "02" FROM
(
SELECT s_id,c_id,s_score from score WHERE c_id='01' 
)as a --将01课程相关的学生id提取到表a
inner JOIN --交集
(
SELECT s_id,c_id,s_score from score WHERE c_id='02'
)as b on a.s_id=b.s_id --合并条件为学生id相同
WHERE a.s_score>b.s_score
--3.在上面基础上再加上学生的名字（完整版）
select a.s_id "s_id",c.s_name "name",a.s_score "01",b.s_score "02" FROM
(
SELECT s_id,c_id,s_score from score WHERE c_id='01' 
)as a
inner JOIN
(
SELECT s_id,c_id,s_score from score WHERE c_id='02'
)as b on a.s_id=b.s_id
INNER JOIN student as c on c.s_id=a.s_id--再关联学生表可以得到姓名
WHERE a.s_score>b.s_score

2、查询平均成绩大于60分的学生的学号和平均成绩

select s_id,avg(s_score)
	FROM score
GROUP BY s_id HAVING avg(s_score)>60--HAVING用来删选

3、查询所有学生的学号，姓名，选课数，总成绩（统计一般使用GROUP BY）

--1.得到的表包含s_id,s_name,s_birth,s_sex,s_id1,c_id,s_score
SELECT a.*,b.*
FROM student as a
LEFT JOIN score as b ON a.s_id=b.s_id
--2.在1基础上修改（做统计）
select a.s_id,a.s_name,count(b.c_id),sum(b.s_score)--select中的字段应该是出现在group by当中的字段或者是统计值
FROM student as a
LEFT JOIN score as b ON a.s_id=b.s_id
group by s_id,a.s_name
--3.左连接的时候匹配不到的字段处理
select a.s_id,a.s_name,count(b.c_id),
sum(case when b.s_score is NULL then 0 else b.s_score END)--NULL值显示为0
FROM student as a
LEFT JOIN score as b ON a.s_id=b.s_id
group by s_id,a.s_name