数据库相关

数据库 导航：

一. 数据库架构

1.1 关系数据库主要考点

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

1.2 数据库的设计要点：

1.3 索引模块

• 为什么要使用索引？

  • 答： 快速查询数据

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

  • 主键、唯一键以及普通等；

• 索引的数据结构：

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

1.4 二叉查找树概述

• 二叉排序树或者是一棵空树，或者是具有下列性质的二叉树：
  （1）若左子树不空，则左子树上所有结点的值均小于它的根结点的值；
  （2）若右子树不空，则右子树上所有结点的值均大于或等于它的根结点的值；
  （3）左、右子树也分别为二叉排序树；

使用二叉查找树，首先能O(logn)的速度查找到数据，每行都有两个孩子，做孩子和右孩子，减少了IO次数，提高了查询效率；

1.5 二叉查找树图示：

1.6 B-Tree树概述：

• B-树的查找很简单，是二叉排序树的扩展，二叉排序树是二路查找，B-树是多路查找，因为B-树结点内的关键字是有序的，在结点内进行查找时除了顺序查找外，还可以用折半查找来提升效率。B-树的具体查找步骤如下（假设查找的关键字为key）：
  1）先让key与根结点中的关键字比较，如果key等于k[i]（k[]为结点内的关键字数组），则查找成功
  2）若key<k[1]，则到p[0]所指示的子树中进行继续查找（p[]为结点内的指针数组），这里要注意B-树中每个结点的内部结构。
  3）若key>k[n]，则道p[n]所指示的子树中继续查找。
  4）若k[i]<key<k[i+1]，则沿着指针p[I]所指示的子树继续查找。
  5）如果最后遇到空指针，则证明查找不成功。

1.7 B-Tree树图示：

相比于二叉树，B树可以进行顺序查找和二叉查找，性能一样很高效；而且每阶层能挂载的孩子更多，减少了io操作，性能更好

1.8 B±Tree树

• B±Tree树的定义和B树差不多，在区别上它比B树更矮，IO流的操作更小，即性能更高；

1.9 B±Tree树图示：

1.10 总结：

• B+Tree更适合用来做存储索引
  • B+树的磁盘读写代价更低
  • B+树的查询效率更加稳定
  • B+树更有利于对数据库的扫描

1.11 Hash索引

• 优点：

  • Hash索引可以一次直接查询出对应的数据，查询性能更高；

• 缺点：

  • 只能用于等值查询，不能用于范围查询；
  • 无法被用来避免数据的排序操作
  • 不能利用部分索引键查询
  • 不能避免表扫描（不同索引键可能会有一样的hash值）
  • 遇到大量Hash值相等的情况后性能不一定就比B-Tree索引高；

不稳定，且不支持范围查询，导致它不能成为一个主流的索引引擎；

1.12 位图索引–BitMap

只适合并发较少的系统；

二. 索引的问题

2.1 密集索引和稀疏索引的区别？

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

2.2 InnoDB

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

2.3 常见sql问题：

• 如何定位并优化慢查询sql
  • 思路：
    • 根据慢日志定位慢查询sql
    • 使用explain等工具分析sql
    • 修改sql或者尽量让sql走索引
  • 慢日志操作：
    • 命令： shwo variables like ‘%quer%’;
    • 查看三个相关的：
      • long_query_time: 查询耗时
      • slow_query_log: 慢日志
      • slow_query_log_file: 日志地址
    • 慢查询数量： show status like ‘%show_queries%’; //如果关闭mysql后，慢查询条数会被清零；
    • 打开慢查询： set global show_query_log=on;
    • 设置慢查询时间： set global long_query_time=1;
  • 分析SQL:
    • 在查询语句前加上expalin即可；
    • Explain关键字段：
      • type: 如果出现了index或者all，则需要进行优化；
        • 最优到最差排序：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge_unique_subquery>index_subquery>range>index>all
      • extra: 如果出现了下面2项意味着MYSQL根本不能使用索引，效率会受到重大影响。应尽可能对此进行优化；
        • Using filesort: 表示Mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。Mysql中无法利用索引完成的排序操作称为"文件排序"；
        • Using temporary：表示Mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by;
  • 修改sql
    • 修改的时候，尽量走索引，可以让查询效率提升；
    • 方式：
      • 修改查询语句
      • 或者给字段加索引；

Mysql查询优化器会自己选择一个合适的索引来查询数据，我们可以通过force index() 来强制使用索引；一般情况下，Mysql查询优化器选择的索引方式是最优的；

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

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

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

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

三. 锁模块

3.1 常见问题：

• MyISAM与InnoDB关于锁方面的区别是什么？
• 数据库事务的四大特性
• 事务隔离级别以及各级别下的并发访问问题
• InnoDB可重复读隔离级别下如何避免幻读
• RC、RR级别下的InnoDB的非阻塞读如何实现

3.2 MyISAM与InnoDB关于锁方面的区别是什么？

• 答：
  1. MyISAM默认使用的是表级锁，不支持行级锁，表级锁会锁住整张表。
  2. InnoDB默认用的是行级锁，也支持表级锁

读锁又叫做共享锁，它的使用其他的操作仍可进行；
查看是否是自动提交：

	show variables like 'autocommit';			//查看是否是自动提交
    set autocommit=0;					//关闭自动提交

3.3 MyISAM适合的场景

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

3.4 InnoDB适合的场景

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

3.5 数据库锁的分类

• 按锁的粒度划分：表级锁、行级锁、页级锁（逻辑块的锁）
• 按锁的级别划分，可分为共享锁、排它锁
• 按加锁方式划分，可分为自动锁、显式锁；
• 按操作划分，可分为DML锁，DDL锁；
• 按使用方式划分，可分为乐观锁、悲观锁（比如全局排它锁就是一种悲观锁的体现；会容易造成死锁，增大开销，并发降低）；

//乐观锁示例：
# 将session设定为自动提交
set autocommit=1;
# 1. 先读取test_innodb的数据，得到Version的值为versionValue
select version from test_innodb where id=2;	#0
# 2. 每次更新test_innodb表中的money字段时候，为了防止发生冲突，先去检查version再做更新；
# 更新成功的话version+1
update test_innodb set money=123 ,version =0+1 where version=0 and id=2;

先查询到了版本号，如果版本号等于0，而实际上的版本上也为0的话则更新成功，如果不为0则说明版本已经被其他人给修改过了，则修改失败；

3.6 数据库事务的四大特性

• ACID：
  • 原子性（Atomic） 要么全部执行，要么全部回退
  • 一致性（Consistency） 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变；
  • 隔离性（Isolation） 多个事务并发执行时，一个事务执行不应该影响其他事务的执行；
  • 持久性（Durability） 系统出现故障确保已提交的事务能恢复，能承受系统故障

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

• 事务并发访问引起的问题以及如避免：
  • 更新丢失— mysql所有事务隔离级别在数据库层面上均可避免
  • 脏读— READ-COMMITTED事务隔离级别以上可避免
  • 不可重复读— REPEATABLE-READ 事务隔离级别以上可避免[侧重于对同一数据的修改]
  • 幻读— SERIALIZABLE事务隔离级别可避免 [侧重于新增或者删除]

– 如何避免图示：

事务隔离性越高，并发度越低；orcale默认为已提交读，mysql默认为可重复读；我们要选择合适的事务级别，并不是越高越好；

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

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

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

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

3.10 next-key(行锁+gap锁)

• 行锁： 锁定一行数据
• Gap锁： 锁定一个范围 它是一个间隙锁，锁住的是两条记录之间可能出现幻读的地方，来预防幻读的发生

3.11 对主键索引或者唯一索引会用Gap锁吗？

• 如果where 条件全部命中，则不会用用Gap锁，只会加记录锁
• 如果where条件部分命中或者全不命中则会加Gap锁；

3.12 Gap锁会用在非唯一索引或者不走索引的当前读中

• 非唯一索引 [Gap锁是为了防止插入的]
• 不走索引 [所有的都上锁]

四. 小结以及其他内容

4.1 常见问题

• MyISAM与InnoDB关于锁方面的区别是什么？
• 数据库事务的四大特性
• 事务隔离界别以及各级别下的并发访问问题
• InnoDB可重复读隔离级别下如何避免幻读
• RC、RR级别下的InnoDB的非阻塞读如何实现

4.2 语法部分

• 关键语法：
  • GROUP BY
  • HAVING
  • 统计相关： COUNT,SUM,MAX,MIN,AVG

4.3 GROUP BY

• 满足"SELECT 子句中的列名必须为分组列或列函数"
• 列函数对于group by 字句定义的每个组各返回一个结果

group by 里出现某个表的字段，select 里面的列要么是group by 里出现的列，要么是别的表的列或者带有函数的列

4.4 HAVING

• 通常与GROUP BY子句一起使用
• WHERE过滤行，HAVING过滤组
• 出现在同一sql的顺序： WHERE>GROUB BY>HAVING

HAVING必须加上GROUP BY 才能执行；
sql语句示例： select stu.student_id,stu.name from student stu,score s where stu.student_id=s.student_id group by s.student_id having count() <(select count() from course)
大多数的复杂sql都与group by 和 Having相关

4.5 面试的三层架构

• 首轮： 面试技术基本功
• 次轮： 架构设计，通过技术解决某些场景下的问题
• 末轮： 稳定性以及未来规划，压工资

并非所有公司都是三轮，但是大部分的面试都会涉及到这些；

4.6 如何备战心仪公司

• 认真研究职位要求，提炼出特别需要准备的知识点
• 通过内部人士打听团队所做的项目的信息，重点备战这些知识点

4.7 面试中项目介绍如何表达比较好

• 站在码农的角度介绍项目，专注技术指标以及解决思路；
• 自信，脉路要清晰： 项目用途->自己的角色->如何解决问题
• 项目若找不到难点，则谈谈改进，前提是熟悉相关涉及的知识点
• 事前用图形将你的项目勾画清楚；