数据库基础知识

  1. 数据库范式

    • 1NF: 属性不可再分割
    • 2NF：1NF基础之上，消除非主属性对于码的部分函数依赖
    • 3NF：2NF基础之上，消除了非主属性对于码的传递函数依赖

  2. drop、delete、truncate区别
    用法不同

    • drop(丢弃数据)：drop table 表名，直接删除整张表
    • truncate(清空数据)：truncate table 表名，清空表中的数据
    • delete(删除数据)：delete from 表名 where 列名=值， 删除某一列数据，如果不加 where 字句的话，与 truncate 类似

    truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据，但是 truncate 和 delete 只删除数据不删除表的结构(定义)，执行 drop 语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在


    属于不同的数据库语言

    • trucate 和 drop 属于 DDL(数据定义语言)，语句，操作立即生效，原数据不放到 rollback segment中，不可回滚，操作不触发 trigger
    • delete 属于 DML(数据库操作语言)，操作会放到 rollback segment 中，事务提交后才生效

  3. DML 语句与 DDL 区别

    • DML 是数据库操作语言(Data Manipulation Language)， 指对数据库中表记录的操作，包括增、删、改、查
    • DDL 是数据库定义语言(Data Definition Language)，指对数据库内部的对象进行增、删、改的语言
    • 两者最大的区别是，DML一般只修改表中的数据，不修改表的定义、结构，而DDL则会对这些进行修改
    • 同时 DML 对表的修改需要添加到 rollback segment中，可以回滚，事务提交之后才生效，而DDL则不需要添加，立即生效，不可回滚

MySQL知识点：

  1. 关系型数据库
    关系型数据库是建立在关系模型基础上的数据库，关系模型表明了数据库中存储的数据之间的关系(一对一，一对多，多对多)
    在关系型数据库中，数据被存放在各种表中，表的每一行存放着一条数据

  2. MyISAM 和 InnoDB的区别

    • MySQL5.5之前使用的存储引擎是MyISAM，之后换为了InnoDB
    • MyISAM 不支持事务和行级锁，崩溃之后无法安全恢复
    • 从是否支持行级锁来看
      • MyISAM 只支持表级锁
      • InnoDB 支持行级锁和表级锁，默认为行级锁
    • 从是否支持事务来看
      • MyISAM 不提供事务支持
      • InnoDB 提供事务支持，既有提交和回滚事务的能力
    • 从是否支持外键来看
      • MyISAM 不支持外键
      • InnoDB 支持外键
    • 从崩溃后能否安全回复来看
      • MyISAM 不支持崩溃后的安全恢复
      • InnoDB 通过 redo log 实现崩溃后的安全恢复
    • 从是否支持 MVCC来看
      • MyISAM 不支持
      • InnoDB 支持

  3. InnoDB 如何实现 ACID

    • 通过 redo log 实现事务的持久性
    • 通过 undo log 实现事务的原子性
    • 通过 锁机制 、MVCC 来保证事务的隔离性
    • 持久性、原子性、隔离性保证之后，一致性得到保证

  4. 表级锁与行级锁的对比

    • 表级锁：MySQL 锁定 粒度最大 的一种锁，对当前操作的整张表进行加锁，实现简单，资源消耗比较少，加锁快，不会出现死锁，锁定粒度最大，出发锁冲突的概率最高，并发度很低
    • 行级锁：MySQL 锁定 粒度最小 的一种锁，对当前操作的行进行加锁，行级锁大大减小数据库操作的冲突，并发度很高，但是开销较大，加锁慢，可能会出现死锁

  5. InnoDB 锁算法

    • Record lock： 记录锁，单个行记录上的锁
    • Gap lock： 间隙锁，锁定一个范围，不包括记录本身
    • Next-key lock： record + gap 临键锁，锁定一个范围，可以包括记录本身

  6. 什么是事务

    • 事务是逻辑上的一组操作，要么都执行，要么都不执行

  7. 什么是数据库事务

    • 数据库事务可以保证多个对数据库的操作构成一个逻辑上的整体，这些操作要么全部成功执行，要么都不执行

  8. 数据库 ACID 特性

    • 原子性： 事务是最小的执行单位，不允许继续分割，原子性确保动作要么全部成功，要么都不起作用
    • 一致性： 执行事务前后，数据保持一致
    • 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各个并发事务之间数据库应该是独立的
    • 持久性： 一个事务被提交之后，对数据库中数据的改变是持久的，即使数据库发生故障也不应该对数据有任何影响

  9. 数据事务的实现原理是什么

    • 以 InnoDB 为例，即 InnoDB 实现ACID的原理

  10. 并发事务带来了哪些问题

    • 脏读：
      一个事务正在访问某个数据并且对其进行了修改，这种修改尚未提交到数据库中，此时另外一个事务同样访问到当前的数据并使用了当前这个数据，由于当前数据尚未提交，因此第二个事务读取到的是脏数据，此时根据脏数据所做的操作极有可能是不正确的
    • 丢失修改：
      在一个事务读取一个数据时，另外一个事务同样访问到该数据。在第一个事务修改了数据，尚未提交时，第二个事务同样对数据进行了修改，那么此时第一个事务对数据的修改就丢失了
    • 不可重复读：
      在一个事务内多次重复读取同一个数据，在第一个事务尚未结束时，另一个事务访问到这个数据并对其进行了修改并提交，那么第一个事务在第二个事务提交之后读到的数据与其之前读到的数据是不同的，即会出现同一事务两次读取到的同一个数据不一致的情况
    • 幻读：
      与不可重复读类似，在第一个事务读取了部分数据后，另一个并发事务插入了新的数据，随后第一个事务再次读取数据时发现多出了一些之前并不存在的记录，称为幻读

  11. 不可重复读与幻读的区别

    • 不可重复读：重点是修改，即在读取时发现某些记录被修改
    • 幻读：重点是添加或删除，即在读取时发现之前的数据丢失或多出来之前并不存在的数据

  12. 事务隔离级别有哪些

    • READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读、不可重复读
    • READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但不能阻止幻读、不可重复读
    • REPEATED-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据被自己本身的事务所修改，可以阻止脏读、不可重复读，但有可能发生幻读
    • SERIAIZABLE(可串行化)：最高的隔离级别，完全服从ACID，所有事物依次逐个执行，这样所有事物之间就不会发生干扰，即该级别可以防止脏读、不可重复读、幻读

  13. MySQL 的默认隔离级别是什么

    • InnoDB 的默认隔离级别是：REPEATED-READ(可重复读)

MySQL 索引详解

  1. 何为索引，有什么用

    • 索引是一种用于快速查询和检索数据的数据结构，产检的索引结构有：B树，B+树 和 Hash

  2. 索引的优缺点

    • 优点：
      • 索引可以大大加快数据的检索速度
      • 通过创建唯一索引，可以保证数据表中每一行数据的唯一性
    • 缺点：
      • 创建索引和维护索引需要耗费大量时间，当对表中增删改的时候，如果数据有索引，那么索引也需要动态修改，会降低 SQL 执行的效率
      • 不仅数据库的表需要使用物理空间来存储，索引同样需要物理空间来进行存储，也会耗费一定的空间

  3. 索引的底层数据结构

    • Hash表
      哈希表是键值对(key-value)的集合，通过键可以快速取出对应的值，因此哈希表可以快速检索数据

    • 为什么MySQL 不使用哈希表作为索引的数据结构呢

      • 哈希表会有哈希冲突问题
      • 哈希索引不支持顺序以及范围查找

  4. B 树 & B+ 树有什么异同之处呢

    • B 树又称 B - 树，全称为多路平衡查找树， B+树是 B 树的一种变体
    • B树的叶子节点都是独立的，B+树的叶子节点中有一条引用链指向与它相邻的叶子节点
    • B树 的所有结点既存放key也存放value，而 B+ 树只有叶子节点存放key 和 data， 其他的内节点只存放 key
    • B树 的检索过程相当于对范围内的每个节点的关键字做二分查找，可能尚未达到叶子节点检索就结束了，而 B+树检索效率很稳定，任何查找都是从根节点到叶子结点的过程，叶子节点的顺序检索很明显

索引类型

  1. 主键索引(Primary Key)

    数据表的主键列使用的就是主键索引，一张数据表只能有一个主键，并且主键不能为空，不能重复

  2. 二级索引(辅助索引)
    二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键，即是说，通过二级索引，可以确定主键的位置

    常见的二级索引有：前缀索引，普通索引，唯一索引

    • 唯一索引(Unique Key):
      唯一索引也是一种约束，唯一索引的属性列不能出现重复的数据，但是允许数据为空，一张表允许创建多个唯一索引，建立唯一索引的目的是为了该属性列的唯一性
    • 普通索引(Index):
      普通索引的唯一目的就是为了加快查询数据，一张表允许创建多个普通索引，并且允许数据重复和空
    • 前缀索引(Prefix): 前缀索引只适用于字符串类型的数据，是对文本的前几个字符创建索引，相比普通索引建立的数据更小
    • 全文索引(Full Text)：全文索引主要是为了检索大文本数据中关键字的信息，是目前搜索引擎数据库中使用的一种技术

  3. 聚集索引
    聚集索引即索引结构和数据一起存放的索引
    主键索引属于聚集索引
    在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，表中的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和数据

    聚集索引的优点：

    • 聚集索引的查询速度很快，因为B+树本身就是多叉平衡树，叶子节点也是有序的，因此定位到索引的节点就相当于定位到了数据

    聚集索引的缺点：

    • 依赖于有序的数据：
      由于B+树是多路平衡树，如果索引的数据不是有序的，那么需要在插入时对其进行排序，如果数据是较难排序的类型，那么插入和查找时的效率就会受到影响
    • 更新代价大：
      如果索引列的数据被修改，那么对应的索引也需要被修改，并且，由于聚集索引的叶子节点存放着数据，因此修改的代价比较大，因此主键索引一般不允许修改

  4. 非聚集索引
    非聚集索引就是将索引和数据分开存放的索引， 二级索引属于非聚集索引
    非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点存放的本来就是主键，根据主键返回数据表中查找数据

    非聚集索引的优点

    • 更新代价比聚集索引小
      非聚集索引的缺点
    • 非聚集索引同样依赖于有序的数据
    • 可能会二次查询(回表)：
      在非聚集索引中查到对应的索引和主键之后，可能还需要根据指针、主键返回到数据文件、表中进行查询

  5. 覆盖索引
    覆盖索引即需要查询的字段正好是索引的字段，那么根据索引就可以直接查到数据，不需要回表查询

  6. 非聚集索引一定回表吗
    不一定，如果是覆盖索引，那么就不需要回表查询，查到的索引对应的值就是目标值

  7. 创建索引的注意事项

    • 选择合适的字段创建索引
      • 不为NULL的字段
      • 被频繁查询的字段
      • 被作为条件查询的字段
      • 频繁需要排序的字段
      • 被经常频繁用于连接的字段
    • 被频繁更新的字段应该慎重建立索引
    • 尽可能考虑建立联合索引而不是单列索引
    • 注意避免冗余索引
    • 考虑在字符串类型的字段上使用前缀索引代替普通索引

  8. 一致性非锁定读与锁定读

    • **一致性非锁定读(Consistent Nonlocking Reads)**的实现通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳，查询时，将当前可见的版本号对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见
    • InnoDB 存储引擎中，多版本控制(Multi Versioning)就是对非锁定读的实现，如果读取的行正在执行UPDATE或DELETE操作，这时读取操作不会等待行上锁的释放，相反，InnoDB会去读取行的一个快照数据，这种读取历史数据的方式称为快照读(snapshot read)
    • 在可重复读与读已提交两个隔离级别下，如果执行select，则会使用一致性非锁定读(MVCC)，并且在可重复读下MVCC实现了可重复读和防止部分幻读

  9. InnoDB 对于 MVCC 的实现

    • MVCC 的实现依赖于：
      • 隐藏字段
      • Read View
      • undo log

MySQL 基础架构分析

  1. MySQL 基本架构概览
    简单来说 MySQL 主要分为 Server层和存储引擎层

    • Server层：
      • 连接器: 登录 MySQL时的身份认证和权限相关
      • 查询缓存: 执行查询语句时，会首先查询缓存
      • 分析器：如果没有命中缓存的话，就会由分析器来检查SQL语句是否正确
      • 优化器：按照MySQL认为最优的方案进行查询
    • 存储引擎：
      主要负责数据的存储和读取