校招面试准备——数据库

数据库范式：

第一范式：保证每列（每个字段）都是不可分解的

第二范式：保证表中的每一个非主属性都和主键的每一列（针对联合主键）相关(一个表只能说明一种信息)

• 候选码中的每一个属性都是主属性，其他的是非主属性
• 反例： 学号，姓名，年龄，课程名称，课程ID，成绩，学分
• 这个表说明了两个信息：学生基本信息和学生课程信息；主键应该是学号和课程ID（这样才能映射到唯一一条数据），然而学生姓名和学号和课程ID没关系，课程名称也和学号没有关系。
• 问题：数据冗余（同一个课的课程名和课程ID被存了好多次），删除异常（当把学生信息都删了以后，课程信息也没了）
• 更新异常（当更新一门课程的名称时，需要更新很多行数据），插入异常（当一个学生没选课的时候，没有课程ID，主键不完整，就没法进入数据库）

第三范式：保证表中的每一列都和主键直接相关，而不是间接相关（非主键之间没有依赖）

• 反例：学号，姓名，年龄，班级，班级所在教室
• 主键是学号，然而班级所在教室和学号没有直接的关系，它和主键的关系是 学号->班级->班级所在教室
• 问题：数据冗余（班级所在教室被存了好几次），更新异常，删除异常，没有插入异常了（因为有学号就能插入）

BCNF: 主属性对主键不存在部分依赖

• 反例：仓库号，存储物品号，管理员号，物品数量 （一个管理员只在一个仓库工作，一个仓库只能有一个管理员）
• 仓库号和存储物品号能决定物品数量，而存储物品和管理员号也能决定物品数量，也就是说这两组都是候选码，仓库号，存储物品号和管理员号都是主属性
• 但是当以仓库号和存储物品号为主键是，主属性管理员号部分依赖主键（因为它只依赖仓库号），就不符合BCNF
• 问题：数据冗余，删除异常（一个仓库不放东西，就丢失了管理员信息），插入异常（当一个仓库分配了管理员但是没有存物品，就无法存储管理员信息），更新异常（当给仓库换管理员时，要更新好多条数据）

 

事务的四大特性：

原子性：事务中的一系列操作，要么都做，要么都不做

一致性：事务结束后，数据库的一致性没有被破坏；比如银行转账，转账之后，银行的总钱数不变

隔离性：多个事务对同一个表进行操作时，互不干扰

持久性：一个事务一旦被提交，那么就一定会对数据库起到永久性的改变，即使数据库系统遇到故障也不会丢失提交事务的操作

 

事务的隔离级别，会遇到什么并发问题

刚刚提到事物具有隔离性，这个隔离性也是分级别的；首先看事务并发会带来哪些潜在问题：

• 脏读：读到了别的事务还未提交的数据；别的事务还没提交呢，说明它有可能回滚，那另一个事务读到的数据就是错误的
• 不可重复读：一个事务对同一条数据读好多次，但它某一次读到的数据和之后再读到的数据不一样了，因为在这个间隔中有其他的事务修改了数据
• 幻读：一个事务对某一块数据读取或其他操作，都操作完了以后 发现怎么还有数据没操作呢？因为在它读的时候，有其他的事务加进来一些数据；或者说两次查询得到的数据量不同

那不同的隔离级别能避免这些问题吗：

• 读未提交：很明显这个名字符合脏读中的条件，所以他不能避免脏读，其他两种情况就更不能避免了
• 不可重复读：见名知意，这个隔离级别保证了事务在未提交时，别的事务读不到它的修改；但是不能保证在一个事务进行读操作时，别的事务不会对数据进行修改；还是会不可重复读以及幻读的
• 可重复读：这个隔离级别保证了在进行读操作时，别的事务不会修改数据，但是别的事务可以插入数据；也就是说还是会幻读的
• 串行化：排着队一个一个来，那就不会有什么并行问题

MySQL默认的隔离级别是“可重复读”；

 

MySQL常用存储引擎

存储引擎可以决定数据在内存中以何种格式存储，怎么查找（比如利用索引），以及有些存储引擎支持事务，还决定怎么处理并发。面试常见的两种引擎是InnoDB和MyISAM。

 

InnoDB

• 支持事务，因此就可以提交、回滚
• 支持外键
• 进行存储时，InnodDB会将数据表分为两个文件存储（.frm和idb）且它有共享表空间存储和独占表空间存储两种方式；

• 锁粒度：InnoDB采用MVCC（多版本并发控制）来支持并发，支持行级锁和表级锁；所谓的四个隔离级别就是它来完成的
• https://blog.csdn.net/SnailMann/article/details/94724197?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.channel_param 【MVCC的帖子，讲得太好了】
• 索引：InnoDB的主索引是聚簇索引，而且主索引要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。  一个表可以有多个辅助索引，辅助索引的数据结构也是B+树，但不是聚簇的，叶子节点存的是主键的内容，而不是位置，这样在主索引位置发生变化时也不用更新辅助索引。
• 行数：在查找表的总行数的时候需要全表扫描

 

MyISAM

• 不支持事务和索引
• 存储式分三个文件，frm-->存储表定义；myd-->存储数据；myi-->存储索引

• 锁粒度：只支持表级锁
• 索引：它首先不是聚簇索引，数据域放的是数据的物理地址，也不强求有主索引（辅助索引自己知道地址，不像InnoDB那样样需要去主索引里找），其次它还支持全文索引、压缩等
• 行数：有一个变量表示了行数，因此查找总行数的时候直接返回

 

索引

如果没有索引，数据库中的数据就会想excle一样被存在磁盘里，这样的缺点是在查询的时候需要进行全表搜索；索引的作用就是利用数据结构和算法，能够更快得进行查询。索引的缺点是：1. 增加了存储空间   2. 在修改数据库（插入删除）时也要花费时间去修改索引。

 

索引的优点

• 加快检索速度
• 加速表与表之间的链接，对实现数据参考完整性也很有意义（给主键和外键加索引）
• 在使用分组和排序子句时，可以减少时间（给分组用的属性和排序用的属性加索引）

 

哪些属性适合加索引，哪些不适合

适合的

• 需要经常搜索的属性（直接搜索或者范围搜索）
• 主键
• 经常需要连接的列上，比如外键
• 经常需要排序的属性
• 经常用在where语句中的属性
• 经常组合的属性

不适合的

• 不经常用的（不经常搜索，连接，排序，组合）
• 定义为text image bit类型的
• 取值种类很少的（比如性别，一般也就两种，用索引的话，区分度太低）

 

索引类型（数据结构方面）

Hash： 只有heap和memory引擎才有Hash索引；优点是可以一次定位，缺点是不适用于范围查找

B+树索引（innodb默认用这个）

 

为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？ https://www.cnblogs.com/wenxiaofei/p/9853682.html

1.B+的磁盘读写代价更低

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2.B+tree的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

 

为什么不用红黑树和AVL

https://blog.csdn.net/qq_21993785/article/details/80580679

https://blog.csdn.net/qq_21993785/article/details/80576642

 

索引分类，索引失效条件

索引类型	概念
普通索引	最基本的索引，没有任何限制
唯一索引	与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引	它是一种特殊的唯一索引，不允许有空值。
全文索引	针对较大的数据，生成全文索引很耗时好空间。
组合索引	为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则

 

组合索引的最左匹配原则：

在最左匹配原则中，有如下说明：

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

 

数据库的锁（按存储引擎来说）https://zhuanlan.zhihu.com/p/29150809

MyISAM只有表锁

在select之前，会自动给表加读锁；在执行更新操作（UPDATE,DELETE,INSERT）前会给表加写锁；在自动加锁前，它会一次性获得SQL语句所需要的全部锁，所以它不会出现死锁。

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死” ，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。

 

InnoDB

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

对于UPDATE DELETE INSERT语句，会自动加排它锁；对于select语句，不会自动加任何锁

InnoDB 行锁实现方式：

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 

 

查询优化 Loading...