【0201】 数据库面试-架构索引相关

目录

数据库-架构索引相关

关系型数意味关系型数主要考点

  • 架构
  • 索引
  • 锁
  • 语法
  • 理论范式

数据库架构

面试题

面试题：如何设计一个关系型数据库？

可以设计分两大部分，其中程序实例部分又可划分成多个小模块

  1. 程序实例
    1. 存储管理
    2. 缓存机制
    3. SQL解析
    4. 日志管理
    5. 权限划分
    6. 容灾机制
    7. 索引管理
    8. 锁管理
  2. 存储（文件系统）

索引模块

常见问题

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

为什么要使用索引？

  • 快速查询数据
  • 避免全表扫描查询数据，提升检索效率

什么样的信息能成为索引？

  • 主键、唯一键以及普通键 (MyISAM均属于稀疏索引)

索引的数据结构

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

优化你的索引-运用二叉查找树

（平衡二叉树）
复杂度：O（logn）
f7e39c914d311840d1fa10beedf3a5c7.png

优化你的索引-运用B树

5b4ace6f474dfc178cf8db5b0e03097e.png

定义

  • 根结点至少包括两个孩子
  • 树中每个结点最多含有m个孩子（m>=2）
  • 除根节点合叶节点外，其他每个节点至少有ceil（m/2）个孩子
  • 所有叶子节点都位于同一层
  • 假设每个非终端结点中包含有n个关键字信息，其中
    • ki(i=1...n)为关键字，且关键字按顺序升序排序K（i-1）< Ki
    • 关键字的个数n必须满足：[ceil(m/2)]<=n<=m-1
    • 非叶子结点的指针：P[1],P[2],...,P[M];其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其他P[i]指向关键字属于（K[i-1],K[i]）的子树

优化你的索引-运用B+树

053cabe07233dcf84dd775617ab65971.png

B+树是B树的变体，其中定义基本与B树相同，除了：

  • 非叶子节点的子树指针与关键字个数相同
  • 非叶子节点的子树指针P[i],指向关键字[K[i],K[i+1]]的子树
  • 非叶子节点仅用来索引，数据都保存在叶子节点中
  • 所有叶子节点均有一个链指针指向下一个叶子节点

结论

B+Tree 更适合用来做存储索引

  • B+树的磁盘读写代价更低

  • B+树的查询效率更加稳定

  • B+树更有利于对数据库的扫描

优化你的索引-运用Hash以及BitMap

f59b6820b1c328320007039abea83928.png

缺点

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

BitMap索引是个神器

6499b1457f7a0a89757aba717447c1ac.png

索引模块

密集索引合稀疏索引的区别

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

a33d865f0a3785fc8e1e2ad44e9e15f3.png

额外的知识

InnoDB

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

72deaf0eb4f10694a0d738395cd056f6.png

InnoDB数据跟索引是存在一个文件中的
MyINSM数据跟索引文件时分开存放的

索引额外的问题之如何调优SQL

衍生出来的问题，以Mysql为例

  • 如何定位并优化慢查询Sql？
  • 联合索引的最左匹配原则成因？
  • 索引是建立得越多越好吗？

面试题：如何定位并优化慢查询Sql？

  1. 根据慢日志定位查询sql
  2. 使用explain等工具分析sql
  3. 修改sql或者尽量让sql走索引
show variables like '%quer%'
  • show_query_log 慢日志
  • show_query_log_file 慢查询日志记录文件地址
  • long_query_time 慢日志记录得阈值
//本次会话得慢查询条数
show status like '%slow_queries%';
  • Slow_queries 慢查询得数量
//重启数据库会重置，永久需要修改my.ini
//打开慢查询记录
set global slow_query_log=on;
//设值慢查询得时间为1秒
set global long_query_time=1;


explain select name from person_info_large order by name desc;

Explain关键字段

  • id 表示执行顺序，id越大得越先执行

  • type system->const->..->range->index>all

  • extra （出现以下两项意味根本不能使用索引，效率影响大，应尽可能优化）

    • Using filesort 表示mysql会对结果使用一个外部索引配许，而不是从表里按索引次序读到相关内容，可能存在内存或磁盘上进行排序
    • Using temporay 表示Mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by.

3b06f69a1fff0edeaf23dfb2a6c1848e.png

//修改方式一
explain select account from person_info_large order by account desc;

//修改方式二，给name加索引
alter table person_info_large add index idx_name(name);
explain select name from person_info_large order by name desc;

select count(id) from person_info_large;
select count(id) from person_info_large force index(primary)

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

4be97d2218224db880535b87684f7112.png

explain select * from person_info_large where title='fsdk23423fgg'
  1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>,<,between,like)就停止匹配，比如 a=3 anfd b=4 and c>5 and d=6 如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，abd 的顺序可以任意调整。
  2. =和in可以乱序，比如a=1 and b=2 and c=3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

46e1dd96b63e46bc14ab244941785d44.png

索引是建立得越多越好吗？

  1. 数据量得小得表不需要建立索引，建立会增加额外得索引开销
  2. 数据变更需要维护索引，因此更多得索引意味着更多得维护成本
  3. 更多得索引意味着也需要更多得空间
posted @ 2020-08-02 17:50  菜徐鸭  阅读(161)  评论(0)  编辑  收藏  举报