华为OD技术面真题 - 数据库Mysql

文章目录

什么是事务？介绍一下事务的特性
介绍一下Mysql隔离级别，以及会存在什么问题
MyIASM和InnoDB的区别
SQL语句执行顺序
数据库三大范式是什么
数据库为什么使用B+树而不是B树
为什么推荐使用自增主键

什么是事务？介绍一下事务的特性

事物简单理解为，将一系列的操作整合为一个操作，这些操作只能全部都成功或者全部都执行失败。

事务的四大特性(ACID)：

原子性(Atomicity)
- 单个事务，为一个不可分割的最小工作单元，指的是一个事务中的操作要么全部成功，要么全部失败
一致性(Consistency)
- 事务的执行保证数据库的一致性。事务开始和结束时，数据库必须满足所有的完整性约束，即事务执行的结果必须使数据库从一个有效状态转变到另一个有效状态。
隔离性(Isolation):
- 并发执行的多个事务之间应该相互隔离，每个事务都应该感知不受其他事务影响的中间状态。
- 多个事务并发执行互不干扰。
持久性(Durability):
- 事务一旦提交，结果永久生效。

介绍一下Mysql隔离级别，以及会存在什么问题

并发事务会出现的问题主要分为下面三种

脏读(Dirty Read):
- 可以读到另一个事务未提交的数据。
- 一旦对方事务进行回滚，之前读取到数据被认为是脏数据。
不可重复读(Non-repeatable Read):
- 前后多次读取，数据内容不一致
- 同一事务中，两次读取同一行数据结果不同，原因就是事务运行过程中其它事务update行数据。
幻读(Phantom Read):
- 同一事务中，前后多次读取，数据总量不一致
- 其他事务 insert / delete

Mysql有四种隔离级别分析：

读未提交(Read Uncommitted):
- 特点：一个事务可以读到另一个未提交的事务数据
- 会出现脏读、不可重复读、幻读问题。
读已提交(Read Committed):
- 特点：只能读到已经提交的数据
- 会出现不可重读、幻读问题。
可重复读(Repeatable Read)
- 特点：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
- 不会出现脏读、不可重读问题，并且Mysql的InnodDB在级别下通过MVCC和Next-Key Lock(间隙锁 + 行锁)机制，很大程度也解决了幻读问题。
可串行化(SERIALIZABLE)：
- 特点：最高事务隔离级别，所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。
- 不会出现脏读、不可重复读、幻读问题。性能较差。

MyIASM和InnoDB的区别

比较点	MyISAM	InnoDB
存储结构	每张表被存放在三个文件：frm-表定义、MYD-数据文件、MYI-索引文件	所有表信息都保存在一个数据文件(数据过大，可能拆分为多个保存)
存储顺序	按照记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持	只支持表锁	支持表锁、行锁。行锁力度，并发能力强

两种存储引擎适用场景：

MyISAM：无需支持事务、外键、行锁的情景，并且读多写少的场景。
InnoDB: 更新、操作频繁以及需要保证数据完整，并发量需要高的。需要支持事务、外键、行锁的场景。(现在一般都选这个)

SQL语句执行顺序

SELECT DISTINCT()..c FROM T1,T2 ON < ON 条件> WHERE <WHERE 条件> GROUP BY c1,c2 HAVING <条件> ORDER BY <排序规则>

FROM：对FROM子句中的前两个表执行交叉连接，生成虚拟表VT1。
ON：对VT1应用ON筛选器，只有筛选通过才保存到虚拟表VT2。
WHERE: 对VT2表应用WHERE筛选器，只有筛选通过的记录才保存到VT3.
GROUP BY: 按照GROUP BY子句中的列列表对VT3进行分组，生成VT4
Having :对VT4表应用Having筛选器，只有通过筛选的行才会插入VT5
SELECT: 处理SELECT列表，生成VT6
DISTINCT: 将重复行移除VT6,产生VT7
ORDER BY: 将VT7中行按照ORDER BY 子列表排序规则进行排序，生成VT8

数据库三大范式是什么

第一范式：每个列都不可以再拆分,属性不可分割。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。(要有主键,其他字段都依赖于主键)

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键(消除传递依赖,消除冗余,就是各种信息只在一个地方存储,不出现在多张表中)。

在设计数据库表结构的时候，要尽量遵守三大范式，如果不遵守，必须有足够的理由。

数据库为什么使用B+树而不是B树

B+树的叶子节点存储了所有的数据，非叶子节点中存储的是比较关键字。而B树所有的节点都会存储数据。B+树的叶子节点之间存在一个指针连接，B树不存在指针连接。B+树这种设计结构能带来什么好处呢？B+树所有的数据都存储在叶子节点，那么顺着叶子节点从左往右即可完成对数据的遍历，极大了简化了排序操作。这也是mysql设计索引是采用B+树的原因，不仅仅能方便查找，而且有助于排序，在mysql的索引中叶子节点之间数双向链表可正反遍历，更加灵活；
B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。