数据库面经

最新推荐文章于 2025-04-12 20:41:36 发布

surpass0728

最新推荐文章于 2025-04-12 20:41:36 发布

阅读量1k

点赞数 3

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/surpass0728/article/details/80244486

版权

总结一下自己找实习来又问道过的数据库相关问题

一、数据库的索引

1.数据库底层实现：

底层是B+树的结构，所有节点中都存了指向下一个叶子节点的指针，只有叶子节点存储数据，InnoDB建议为大部分表使用默认自增的主键作为主索引。

2.为什么是B+树不是红黑树或者B树二叉树

B树即二叉搜索树：

所有非叶子节点最多有两个儿子
所有节点存储一个关键字
非叶子节点左指针指向比关键字小的，右指针指向关键字大的

B-树即B树，不要读成B-树，它是多路搜索树

关键字分布在整棵树中
搜索性能等价于二分查找
搜索有可能在非叶子节点结束

B+树是B-树的变体，也是多路搜索树

所有关键字都在叶子节点中
为所有叶子节点添加一个链指针

B+树在B-树基础上为叶子节点增加链表指针，所有关键字都在叶子节点中出现，非叶子节点作为叶子节点的索引，更适合文件索引系统
相对于B树，B+树空间利用率高，因为B+树的内部节点只是作为索引使用，而不像B-树那样每个节点都需要存储硬盘指针

3.存储引擎InnoDB和Myisam区别

1）InnoDB支持事务，Myisam不支持事务

2）InnoDB支持外键，Myisam不支持外键

3）MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。InnoDB数据文件本身就是索引文件

聚簇索引和非聚簇索引

聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍然是索引节点。
非聚簇索引的主键要比聚簇索引主键要快，主键约束要求主键不能重复出现，保证不出现重复的话只能检索，对于非聚簇索引检索节点少，聚簇索引检索节点多，所以非聚集索引效率更高

4.数据库的事务和隔离级别

1）原子性：原子性是指事务包含的所有操作要么全部成功，要么全部失败，事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

2）一致性：一个事务执行之前和执行之后都必须处于一致性状态。转账的例子就是事务的一致性。

3）隔离性：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。（不能并发执行）

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

数据库事物的隔离级别有四种：

由低到高是Read uncommitted、Read committed、Repeatable read、Serializable在并发操作中可能会出现脏读、不可重复读、幻读

Read uncommitted：读未提交，就是一个事务可以读取另一个未提交事物的数据
Read committed:读提交，就是一个事务要等另一个事务提交后才能读取数据
Repeatable read:重复读，就是在开始读数据(事务开启)时，不再允许修改操作
Serializable:最高的事务隔离级别，事务串行化顺序执行，这种事务隔离级别效率低，比较消耗数据库性能。

5.数据库的优化及索引失效

四个效果依次减少
① SQL语句及索引的优化
② 数据库表结构的优化
③ 系统配置的优化
④ 硬件的优化

字段的长度、联合查询、使用外键和索引、使用事务、优化sql语句

什么情况下设置了索引但无法使用
① 以“%”开头的LIKE语句，模糊匹配
② OR语句前后没有同时使用索引
③ 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

二、数据库的锁机制

1.锁的分类

按粒度划分：行级索、表级锁、页级索

按锁的级别划分：共享锁、排它锁、更新锁

按使用方式：乐观锁、悲观锁

行级索：对当前操作的行进行加锁，减少数据库操作的冲突，开销大

页级索：表级锁速度快，但冲突多，行级冲突少，但速度慢。所以页级索取了折衷的页级

2.锁的使用

2.1、共享锁

共享锁又称读锁，是读取操作时创建的锁，其他用户可以并发的读取数
据，但任何事务都不能修改，知道释放掉所有的共享锁

一个事务T对数据加上共享锁，则其他事物也可以访问，再次加共享锁，获取共享锁的事务只能读数据，不能修改数据。

用法：SELECT … LOCK IN SHARE MODE;

在查询语句后面加上 LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有线程对查询结果集使用排它锁时，可以申请共享锁，否则的话线程会被阻塞。

2.2、排他锁

排他锁又称写锁，如果事务T对数据A加上排他锁，则其他事物不允许对A加任何类型的锁，获得排他锁的得事务既可以读数据也可以修改数据数据

用法：SELECT … FOR UPDATE;;

在查询语句后面加SELECT … FOR UPDATE;，Mysql会对查询结果中的每行都加排他锁，当其他线程对查询结果集中的任何一行使用排它锁时，可以申请排它锁，否则会被阻塞。

2.3、悲观锁

在关系数据库管理系统里，悲观并发控制（又称悲观锁）是一种并发控制的方法，它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行引用了锁，那只有当这个事务把锁释放，其他事物才能执行与该锁冲突的操作。

悲观并发控制用于数据征用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁的流程：

1）在对任意记录修改前，先尝试为该记录加上排他锁（exclusive locking）。

2）如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。

3）如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。

4）其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

Mysql InnoDB中使用悲观锁

要是用一个悲观锁，必须们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。set autocommit=0;

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

上面的查询语句中，我们使用了select…for update的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在t_goods表中，id为1的那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意

悲观锁的优点与不足：

在并发控制中实施“先取锁再访问”的保守策略，为数据处理提供了保证，在效率方面，处理加锁的机制会让数据库产生额外的开销，还会增加死锁的机会；重点是在只读型事务中由于不会产生冲突，所以也没必要使用锁，这样只会增加系统的负载，降低了并行性。

2.4、乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

实现方式：实现乐观锁的方式：使用版本号（version）操作后version+1 、使用时间戳、待更新字段

使用版本号实现乐观锁

使用版本号时，在数据初始化时指定一个版本号，每次数据更新操作对版本号+1操作，将提交数据的版本号与数据表记录的版本号对比，如果提交的数据的版本号大于数据表当前版本号，则予以更新，否则认为是过期数据。

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

乐观锁的优点与不足

乐观锁在并发执行下，相信事务之间数据竞争的概率是比较小的，直到提交的时候才去锁定，所以不会产生加锁和死锁。例如两个事物都读取了数据库的某一行，进过修改写回数据库，就会出现问题。
、

一个简单的例子描述悲观锁与乐观锁：

悲观锁：

在银行金融系统中，当某个操作员读取用户的数据时，并在用户数据上进行修改，如果采用悲观锁机制，那就意味着整个过程中（从读出用户信息开始修改知道提交甚至包括中间上厕所的时间），数据库记录处于加锁状态，这样的话，面对几千个并发执行，情况可想而知。

乐观锁：

在这一层次上，乐观锁切实的解决了这一问题，基于数据版本version的，读取数据时会增加一个version字段来实现。更新提交后对此版本号+1，若提交的记录版本号大于数据库当前版本号则予以更新，否则视为过期数据。

一个用户卡中有100元，
1）操作员A将其读出，从账户中扣除50（100-50）；
2）操作员B在A操作过程中也读入用户信息，从账户中扣除20（100-20）；
3）操作员A提交数据更新数据库 version更新+1变成了2
4）操作员B提交数据更新数据库 version更新+1变成了2，不满足提交版本号大于当前版本号，所以操作员B的提交请求被驳回。这样的话避免了基于旧数据覆盖操作员A的操作。