数据库面试

倔强的张小凡
96 阅读2分钟

一个面试题引发的【血案】

- 如何设计一个关系型数据库？

设计

存储（文件系统）

  • 类似文件系统，将数据持久化到存储设备当中。

程序实例

  • 存储管理：将数据的逻辑关系转换成物理存储管理
  • 缓存机制：优化执行效率
  • SQL解析 （将sql缓存起来）：将sql语句执行解析
  • 日志管理：操作日志处理
  • 权限划分
  • 容灾机制（异常机制）：灾难恢复
  • 索引管理：优化数据查询效率
  • 锁管理：支持并发操作

索引

常见问题

  • 为什么要使用索引
  • 什么样的信息能成为索引
  • 索引的数据结构
  • 密集索引和稀疏索引的区别

1. 为什么要使用索引？

  • 快速查询数据
  • 避免全表扫描查找数据

2. 什么样的信息能成为索引

  • 主键、唯一键和普通键等能让数据具备一定区分性的字段

3. 索引的数据结构

  • 生成索引，建立二叉查找树进行二分查找
  • 生成索引，建立B-Tree结构进行查找
  • 生成索引，建立B+-Tree结构进行查找
  • 生成索引，建立Hash结构进行查找

4.优化索引

二叉查找树

二叉查找树（平衡二叉树-左子树和右子树高度差不超过1），查询采用二分查找，查询的复杂度为O(logn)。

缺点：
  • 容易变成线性二叉树，此时时间复杂度为 O(n)，可以通过树的旋转保证这棵树是平衡二叉树；
  • IO是影响程序运行速度的瓶颈，数据查询过程检索深度每增加1就发生一次IO

B-Tree

image.png B树-平衡多路查找树，每个节点最多可以有m个孩子那么这样的树就是m阶B树，每个存储块中包含关键字，指向孩子的指针，m是存储块的容量和数据库配置决定一般会很大。

  • 根节点至少包括两个孩子
  • 树中每个节点最多含有m个孩子（m>=2）
  • 除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子 ceil（向上取整）取上限
  • 所有叶子节点都位于同一层，叶子节点的高度都一样；目的是让每个索引块尽可能存储更多的信息，控制树的高度尽可能减少IO的次数
  • 假设每个非终端节点中包含n个关键字信息，其中：关键字升序排列；关键字的个数比子树的个数小1；同位置子树的值小于同位置关键字的值即 Ki-1<Pi<Ki (P子树值，K关键字值)

B+-Tree

image.png B+树是B树的变体，定义基本相同，除了：

  • 非叶子节点的子树指针个数与关键字个数相同
  • 非叶子节点的子树指针P[i],指向关键字值[K[i],K[i+1]]的子树
  • 非叶子节点仅用来索引，数据都保存在叶子节点中
  • 所有叶子节点均有一个链指针指向下一个叶子节点，按大小顺序链接（范围统计，大于10的查询可以直接叶子节点横向统计）
B+树更适合做存储索引
  • B+树的磁盘读写代价更低（存储块中的关键字越多，树的高度越小，一次读入内存的关键字越多，减少了磁盘的IO）
  • 查询效率更加稳定（O(logn)，每次查询的深度一样）
  • 更有利于对数据库的扫描（只用对叶子节点进行扫描）

Hash索引

image.png 缺点：

  • 仅仅能满足“=”，“IN”，不能使用范围查询
  • 无法被用来避免数据的排序操作
  • 不能利用部分索引键查询
  • 不能避免表扫描
  • 遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

5. 密集索引和稀疏索引的区别

  • 密集索引文件中的每个搜索码值都对应一个索引值
  • 稀疏索引文件只为索引码的某些值建立索引项 image.png

InnoDB

  • 若一个主键被定义，该主键则作为密集索引
  • 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
  • 若不满足以上条件，innoDB内部会生成一个隐藏索引（密集索引）
  • 非主键索引存储相关键位和其对应的主键值，包含两次查找

MyISAM

MyISAM均为稀疏索引

6. 如何定位并优化慢查询Sql

  • 根据慢日志定位慢查询sql
  1. 打开slow_query_log
  2. slow_query_log_file慢日志查询sql存储位置
  3. long_query_time当sql执行时间超过这个值就会将sql记录到slow_query_log_file对应的慢日志；
  4. 执行set global slow_query_log = on;打开慢查询日志
  5. 执行set global slow_query_time = 1;设置慢查询时间
  • 使用explain工具分析sql explain select name from sys_user order by name desc;分析sql语句，如果结果中type是index/all则sql是全表扫描（这时sql需要优化），另一个结果字段extra如下图： image.png
  • 修改sql或者尽量让sql走索引提升效率 添加索引：alter table sys_user add index idx_name(name);

7. 联合索引的最左匹配原则的成因

8. 索引是建立的越多越好吗

  • 数据量小的表不需要建立索引，建立会增加额外的索引开销
  • 维护成本提高
  • 更多的索引意味着需要更多的空间

锁模块

1.MyISAM与InnoDB关于锁方面的区别是什么

  • MyISAM默认用的是表级锁，不支持行级锁；
  • InnoDB默认用的是行级锁，也支持表级锁

表级锁

MyISAM存储引擎在进行select操作是，会自动对表加上读锁；对表进行增删改的时候，会对表增加一个写锁。当加上读锁时，另一个session对表进行写操作时会阻塞，直到读锁释放。

  • lock tables sys_user read | write 添加读/写锁
  • unlock tables 释放所有锁
  • 上了读锁（共享锁），支持继续再上读锁，不支持上写锁
  • 当上了写锁，就不能上读|写锁，需等写锁释放；写锁又称排它锁
InnoDB用的二段锁-分加锁和解锁两个步骤（解锁即commit）
  • show variables like 'autocommit';查看是否自动提交事务
  • set autocommit = 0;关闭自动提交
  • innoDb存储引擎对select语句进行了改进，该select并未对当前行上锁（非阻塞select）
  • 在sql没有用索引的时候，采用表级锁

MyISAM适合的场景

  • 频繁执行全表count语句
  • 对数据进行增删改的频率不高，查询非常频繁
  • 没有事务的场景

InnoDB适合的场景

  • 数据增删改查都相当频繁
  • 可靠性要求比较高，需要支持事务

数据库锁的分类

  • 按锁的粒度划分：表级锁、行级锁、业级锁（不常用的BDB引擎）
  • 按锁级别划分：共享锁、排它锁
  • 按加锁方式划分：自动锁、显示锁
  • 按操作划分：DML锁（对数据进行操作上的锁）、DDL锁（对表结构变更加上的锁）
  • 按使用方式划分：乐观锁、悲观锁

2.数据库事务的四大特性

ACID

  • 原子性（Atomic）：事务包含的所有操作要么全部执行要么全部失败回滚
  • 一致性（Consistency）：事务应确保数据库的状态，从一个一致状态到另一个一致状态
  • 隔离性（Isolation）：事务与事务之间互不影响
  • 持久性（Durability）：事务提交后对数据库的修改永久保存到数据库中（redo_log_file文件保存事务对数据库的操作）

3.事务隔离级别以及各级别下的并发访问问题

事务并发访问引起的问题以及如何避免

  1. 更新丢失（一个事务的更新覆盖了另一个事务的更新）--mysql所有事务隔离级别（最低READ-UNCOMMITTED）在数据库层面上均可避免
  2. 脏读--READ-COMMITTED事务隔离级别以上可避免 可通过select @@tx_isolation;查看事务隔离级别，设置事务隔离级别:set session transaction isolation level read uncommitted;
  3. 不可重复读--REPEATABLE-READ事务隔离级别以上可避免
  4. 幻读--SERIALIZABLE事务隔离级别以上可避免 image.png
  • 事务隔离级别在SERIALIZABLE级别，所有的sql都会加上锁
  • 出于性能考虑，事务隔离级别越高，安全性越高，串行化执行越严重，降低数据库的并发度
  • ORACLE默认为READ-COMMITTED
  • MYSQL默认为REPEATABLE-READ

4.InnoDB可重复读隔离级别下如何避免幻读

  • 表象：快照读（非阻塞读）--伪MVCC
  • 内在：next-key锁（行锁+gap锁）

5.RC、RR级别下的InnoDB的非阻塞读如何实现

  • 数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段
  • undo日志
  • read view

关键语法

  • GROUP BY
  1. 满足"SELECT子句中的列名必须为分组列或列函数"
  2. 列函数对于group by子句定义的每个组各返回一个结果
  • HAVING
  1. 通常与GROUP BY子句一起使用
  2. WHERE过滤行，HAVING过滤组
  3. 出现在同一sql的顺序：WHERE>GROUP BY>HAVING
  4. 省略GROUP BY子句，HAVING和WHERE一样
  5. 查询平均成绩大于60的语句select student_id,avg(score) from score group by student_id having avg(score)>60
  • 统计相关：COUNT, SUM, MAX, MIN, AVG
avatar
文章
阅读
粉丝