数据库面试核心知识

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

• 存储（文件系统）
• 程序实例 逻辑结构映射物理结构
  • 存储管理 减少io次数 一次性读取多行
  • 缓存机制
  • SQL解析
  • 日志管理
  • 权限划分
  • 容灾机制
  • 索引管理**
  • 锁模块**

索引模块

为什么要索引

• 快速查询数据

什么信息成为索引

• 主键 唯一键 普通键

索引的数据结构

• 二叉查找树
• 平衡树
• b+树

数可能偏移了——>平衡树

B-Tree 最多n个孩子

根节点至少2孩子

每个节点最多m个孩子

除了根节点和叶节点，每个节点至少m/2 孩子

所有叶子节点都在一层

每个非终端结点包含n个关键字信息

• ki 为关键字 ki-1 < k升序排列
• 关键字个数大于m一半 小于m-1
• 非叶子节点和父节点有大小关系

b树合并分裂 保证不会线性增长

B±Tree

• B+是b的变体

• 非叶子节点关键字个数相同

• 非叶子节点有大小关系

• 非叶子节点做索引，数据保存在叶子中

• 所有叶子节点均有一个链指针指向一个叶子节点

优点

• 读写代价更低
• 查询效率更加稳定
• 有利于对数据库的扫描 遍历叶子节点即可

HASH索引

缺点

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

BitMap索引

bitMap索引是个神器

很少数据库支持Oracle

锁粒度很大，并发性不行

不适合OLTP 查询适合OTAP统计

索引模块

• 密集索引和稀疏索引区别
  • 密集索引为文件每个搜索玛对应一个索引值
  • 稀疏索引文件只为索引码的某些值建立索引项

MarSAM 都是稀疏索引

InnoBD

• 主键定义，主键作为密集索引
• 若没有主键定义，第一个非空索引密集索引
• 都不满足，innodb内部生成一个隐藏主键 密集索引

索引优化

• 索引
  • 提升检索效率
• 什么信息索引
  • 主键 其他建
• 索引数据结构
  • b+树
  • hash
• 密集索引 稀疏索引区别

定位优化满查询

• 慢日志查询
• explain工具分析sql
• 修改sql 尽量走索引

type index all 需要优化

extra Using filesort using temporary 没有使用索引，效率较低

联合索引的最左匹配成因

• 向右匹配一直等到范围查询
• 第一个字段有序，用第二个字段的话就无序了，不能走联合索引了

索引建立越多越好吗？

不

• 维护成本高
• 空间浪费
• 小数据overhead太高

锁

MyISAM InnoDB锁区别

MyISAM 表锁 不支持行锁

InnoDB 行锁 也支持表锁

共享锁和排斥锁 兼容性

InnoDB支持事务 行锁

MyISAM

执行count 语句 更快

数据增删改不高，查询频繁

没有事务

InnoDB

增删改茶频繁 粒度小

事务

锁分类

粒度 表锁 行锁 页级锁

锁级别 共享锁 排他锁

加锁 自动锁 显式锁

操作 DML 锁 DDL锁

方式 乐观锁 版本号 时间戳 悲观锁

数据库四大特性

ACID

原子性

一致性

隔离性

持久性

事务隔离级别 并发访问问题

• 更新丢失- 加锁避免
• 脏读 已提交读以上隔离级别
• 不可重复读——可重复读事务隔离以上
• 幻读——序列化事务隔离级别以上

事物隔离级别并发访问问题

InnoDB可重复读下避免幻读

表象 快照读 非阻塞读 MVCC伪

内在 Next-key 锁 行锁+gap锁

当前读 select…lock in share mode select for update

当前读 update delete insert

快照读 不加锁的非阻塞读 select

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

• 数据行DB_TRX_ID DB_ROLL_PTR DB_ROW_ID
• undi日志
• read view

Next-key 锁 行锁+gap锁

• 行锁
• gap锁

where全部命中 则不会用gap锁 只会加记录锁

• 非唯一索引
• 不走索引

关键语法

GROUP BY

• select 列名必须三分组或者列函数
• 列函数对于group by 每个组返回一个结果

HAVING 统计

COUNT SUM MAX MIN AVG