MySQL面试(3): 综合

数据库原理

事务

概念

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用
Rollback 进行回滚。

ACID

1. 原子性(Atomicity)：

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

2. 一致性(consistency)：

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的.

3. 隔离性(Isolation)

一个事务所做的修改在最终提交以前，对其它事务是不可见的

4. 持久性(Durability)

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

使用重做日志来保证持久性。

ACID之间不是平级关系：

• 只有满足一致性，事务的执行结果才是正确的

• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性

• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性

• 事务满足持久化是为了能应对数据库崩溃的情况

CAP理论

C(Consistency) 一致性

一致性指*“all nodes see the same data at the same
time”*，即更新操作成功并返回客户端完成后，所有节点在同一时间的数据完全一致，所以，一致性，说的就是数据一致性。

对于一致性，可以分为从客户端和服务端两个不同的视角。从客户端来看，一致性主要指的是多并发访问时更新过的数据如何获取的问题。从服务端来看，则是更新如何复制分布到整个系统，以保证数据最终一致。

一致性是因为有并发读写才有的问题，因此在理解一致性的问题时，一定要注意结合考虑并发读写的场景。从客户端角度，多进程并发访问时，更新过的数据在不同进程如何获取的不同策略，决定了不同的一致性。

三种一致性策略：

对于关系型数据库，要求更新过的数据能被后续的访问都能看到，这是强一致性

如果能容忍后续的部分或者全部访问不到，则是弱一致性

如果经过一段时间后要求能访问到更新后的数据，则是最终一致性

CAP中说，不可能同时满足的这个一致性指的是强一致性

A(Availability) 可用性

可用性指*“Reads and writes always
succeed”*，即服务一直可用，而且是正常响应时间。

对于一个可用性的分布式系统，每一个非故障的节点必须对每一个请求作出响应。所以，一般我们在衡量一个系统的可用性的时候，都是通过停机时间来计算的。

P(Partition Tolerance) 分区容错性

分区容错性指*“the system continues to operate despite arbitrary message loss or
failure of part of the system”*，
即分布式系统在遇到某节点或网络分区故障的时候，仍然能够对外提供满足一致性和可用性的服务。

比如现在的分布式系统中有某一个或者几个机器宕掉了，其他剩下的机器还能够正常运转满足系统需求，或者是机器之间有网络异常，将分布式系统分隔未独立的几个部分，各个部分还能维持分布式系统的运作，这样就具有好的分区容错性。

简单点说，就是在网络中断，消息丢失的情况下，系统如果还能正常工作，就是有比较好的分区容错性。

CAP理论的3进2

CAP理论的核心是：一个分布式系统不可能同时很好的满足一致性，可用性和分区容错性这三个需求，最多只能同时较好的满足两个。

根据CAP原理将NoSQL数据库分成了满足CA原则、满足CP原则和满足AP原则三大类：

CA -
单点集群，满足一致性，可用性的系统，通常在可扩展性上不太强大。没有P意味着舍弃了分布式系统，通常的关系型数据库就是这种。

CP -
满足一致性，分区容忍性的系统，通常性能不是特别高。很多分布式的数据库都是设计成CP的，在发生极端情况下，优先保证数据的强一致性，代价就是舍弃系统的可用性。比如丢失一些请求等。比如redis、MongoDB等，Zookeeper中也使用了CP。

AP -
满足可用性，分区容忍性的系统，通常可能对一致性要求低一些。要高可用并允许分区，则需放弃一致性。一旦网络问题发生，节点之间可能会失去联系。为了保证高可用，需要在用户访问时可以马上得到返回，则每个节点只能用本地数据提供服务，而这样会导致全局数据的不一致性。而由于当前的网络硬件肯定会出现延迟丢包等问题，所以分区容忍性是我们必须需要实现的。

BASE理论

BASE是指基本可用（Basically Available）、软状态（ Soft State）、最终一致性（
Eventual Consistency）。

基本可用

基本可用是指分布式系统在出现故障的时候，允许损失部分可用性，即保证核心可用。

电商大促时，为了应对访问量激增，部分用户可能会被引导到降级页面，服务层也可能只提供降级服务。这就是损失部分可用性的体现。

软状态

软状态是指允许系统存在中间状态，而该中间状态不会影响系统整体可用性。分布式存储中一般一份数据至少会有三个副本，允许不同节点间副本同步的延时就是软状态的体现。MySQL
replication的异步复制也是一种体现。

最终一致性

最终一致性是指系统中的所有数据副本经过一定时间后，最终能够达到一致的状态。弱一致性和强一致性相反，最终一致性是弱一致性的一种特殊情况。

并发一致性问题

丢失修改

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了
T1 的修改。

读脏数据

T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2
读取的数据是脏数据。

不可重复读

T2 读取一个数据，T1 对该数据做了修改。如果 T2
再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻影读

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1
再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

隔离级别

未提交读(read uncommitted)

提交读(read committed)

可重复读(repeatable read)

可串行化(serializable)

数据库基础

SQL相关

连接查询

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL
语句以及连接相同表。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

SELECT A.value, B.value

FROM tablea AS A INNER JOIN tableb AS B

ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE
中将两个表中要连接的列用等值方法连接起来。

SELECT A.value, B.value

FROM tablea AS A, tableb AS B

WHERE A.key = B.key;

在没有条件语句的情况下返回笛卡尔积。

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名

SELECT e1.name

FROM employee AS e1 INNER JOIN employee AS e2

ON e1.department = e2.department

AND e2.name = “Jim”;

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value, B.value

FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

左连接又称左向外连接，查询的结果集包括SQL语句中左表的所有行，右表中匹配的行。如果左表的某行在右表中没有匹配行，则用空值表示。

右连接也成右向外连接，查询的结果集包括SQL语句中右表的所有行，左表中匹配的行。如果右表的某行在左表中没有匹配的行，则用空值表示

分组

分组就是把具有相同的数据值的行放在同一组中。可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num

FROM mytable

GROUP BY col

ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num

FROM mytable

WHERE col > 2

GROUP BY col

HAVING num >= 2;

分组规定：

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；

• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；

• NULL 的行会单独分为一组；

• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

Distinct

相同值只会出现一次。当后面跟多个列时，它作用于所有列，也就是说所有列的值都相同才算相同。

Limit

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0
开始(默认，可以不写)；第二个参数为返回的总行数。

排序

默认是asc(升序)，降序是desc

可以按多个列进行排序，并且为每个列指定不同的排序方式：

SELECT *

FROM mytable

ORDER BY col1 DESC, col2 ASC;

组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N
行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col

FROM mytable

WHERE col = 1

UNION

SELECT col

FROM mytable

WHERE col =2;

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

• 简化复杂的 SQL 操作，比如复杂的连接；

• 只使用实际表的一部分数据；

• 通过只给用户访问视图的权限，保证数据的安全性；

• 更改数据格式和表示

触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE
关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER
用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable

FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; – 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW
是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处：

• 代码封装，保证了一定的安全性

• 代码复用

• 由于是预先编译，因此具有很高的性能

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ;
为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量