【计算机基础知识】数据库（突击）

目录

• 一、基础知识
  • 1.1 概念
  • 1.2 ER图
  • 1.3 范式（共6）
  • 1.4 存储过程
  • 1.5 数据库设计步骤
• 二、MySQL
  • 2.1 基本架构
  • 2.2 存储引擎
    • 存储引擎架构
    • InnoDB和MyISAM
  • 2.3 查询缓存
  • 2.4 事务（ACID）
    • 特性
    • 并发事务的问题
    • 事务隔离等级（4）（默认可重复读）
  • 2.5 MySQL锁
    • 表级锁和行级锁
    • 共享锁和排他锁
    • 意向锁
    • InnoDB行锁
  • 重点
    • drop、delete、truncate区别
    • 关系型数据库
    • MySQL索引
      • 优缺点
      • 底层结构
      • 索引类型
      • 创建注意事项
    • 事务四点性质
    • 同时有多个事务可能产生的问题
    • 事务隔离等级
    • 范式
    • having 和where的区别

一、基础知识

1.1 概念

• 数据库（DB）：信息的集合或者说是由数据库管理系统管理的数据的集合。
• 数据库管理系统（DBMS）：操纵和管理数据库的大型软件，建立、使用和维护数据库。由数据库语言和数据库管理例行程序组成。
• 数据库系统（DBS）：通常由软件、数据库（DB）和数据库管理员（DBA）组成。
• 数据库管理员（DBA）：负责全面管理和控制数据库系统。

其中数据库系统和数据库应用系统的组成成分中都包含有数据库管理系统，这两者都是通过数据库管理系统来实现对数据库的管理和操控。

• 元组：元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。

• 码：码就是能唯一标识实体的属性，对应表中的列。

• 候选码：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：{学号}和{姓名，班级}都是候选码。

• 主属性：候选码中出现过的属性称为主属性。

• 非主属性：不包含在任何一个候选码中的属性称为非主属性。

• 主码：也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码。

• 外码：外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。

  • 主键与外键的区别：

  • 	主键	外键
    作用	唯一标识	和其他表建立联系
    重复	不可重复	可重复
    为空	不可为空	可以为空
    数目	只能一个	可以有多个

    一切外键概念必须在应用层解决：外键与级联更新适用于单机低并发，不适合分布式、高并发集群; 级联更新是强阻塞，存在数据库更新风暴的风险; 外键影响数据库的插入速度。

    缺点：增加了复杂性；对分库分表不友好；增加了额外维护工作。

    优点：保证了数据库数据的一致性和完整性；级联操作方便，减轻了程序代码量。

1.2 ER图

E-R 图 也称实体-联系图(Entity Relationship Diagram)：用来描述现实世界的概念模型。

举例：

转化为如下模型：

1.3 范式（共6）

设计关系数据库式呈递次规范，越高的范式数据库冗余越小。

• 1NF（第一范式）
  • 属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。
  • 1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。
• 2NF（第二范式）
  • 消除了非主属性对于码的部分函数依赖。第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。
  • 
    • 函数依赖（functional dependency） ：若在一张表中，在属性X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。
    • 部分函数依赖（partial functional dependency） ：如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。
    • 完全函数依赖(Full functional dependency) ：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。
    • 传递函数依赖 ： 在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X。
• 3NF（第三范式）
  • 消除了非主属性对于码的传递函数依赖 。
  • 基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。
  • 一般来说，数据库只需满足第三范式(3NF）
• BCNF（巴斯-科德范式）
  • 任何主属性不能对主键子集依赖（在3NF基础上消除主属性对主码子集的依赖）
• 4NF（第四范式）
• 5NF（第五范式）

1.4 存储过程

可以把存储过程看成是一些 SQL 语句的集合，中间加了点逻辑控制语句。

存储过程在业务比较复杂的时候是非常实用的，比如很多时候我们完成一个操作可能需要写一大串 SQL 语句，这时候我们就可以写有一个存储过程，这样也方便了我们下一次的调用。

存储过程一旦调试完成通过后就能稳定运行，使用存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。

但在阿里巴巴 Java 开发手册里要求禁止使用存储过程。因为其难以调试和扩展，没有移植性。

1.5 数据库设计步骤

1. 需求分析 : 分析用户的需求，包括数据、功能和性能需求。
2. 概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图。
3. 逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。
4. 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。
5. 数据库实施 : 包括编程、测试和试运行
6. 数据库的运行和维护 : 系统的运行与数据库的日常维护

二、MySQL

2.1 基本架构

MySQL：

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式存储引擎 ： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

2.2 存储引擎

MySQL 支持多种存储引擎，你可以通过 show engines 命令来查看 MySQL 支持的所有存储引擎。

从上图我们可以查看出， MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

存储引擎架构

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

InnoDB和MyISAM

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎。但它不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

MySQL 5.5 之后默认存储引擎是InnoDB。

1. MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
   • MyISAM 一锁就是锁住了整张表
2. MyISAM 不提供事务支持。
   • InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）
3. MyISAM 不支持外键，而 InnoDB 支持外键。
   • 外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。
4. InnoDB 支持数据库异常崩溃后的安全恢复，MyISAM不支持。
   • 使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。
5. InnoDB 支持MVCC，MyISAM不支持MVCC。
   • MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。
6. 索引实现不同。
   • MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。
   • InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

2.3 查询缓存

执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。

2.4 事务（ACID）

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

特性

1. 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

原子性，隔离性和持久性是数据库的属性，而一致性（在 ACID 意义上）是应用程序的属性。应用可能依赖数据库的原子性和隔离属性来实现一致性，但这并不仅取决于数据库。因此，字母 C 不属于 ACID 。

并发事务的问题

• 脏读（Dirty read）:
  • 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。
  • 这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”（未操作完的数据），依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）
  • 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。
  • 第一个事务内的修改结果就被丢失，因此称为丢失修改。
  • 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
• 不可重复读（Unrepeatable read）
  • 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。
  • 那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）
  • 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。
  • 在随后的查询中，第一个事务（T1）就会发现多了一些【重点关注的是数据查到的记录增加】原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

事务隔离等级（4）（默认可重复读）

• READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读) 【InnoDB默认】： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。（效率极低）

SERIALIZABLE 隔离级别，是通过锁来实现的。除了 SERIALIZABLE 隔离级别，其他的隔离级别都是基于 MVCC 实现。

2.5 MySQL锁

表级锁和行级锁

• MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。

  • MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

• 行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁）。

  • MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

• 除此之外还有页级锁

• InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。

  • 执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）

• 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取写锁。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

意向锁

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁之间是互相兼容的。意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

InnoDB行锁

MySQL InnoDB 支持三种行锁定方式：

• 记录锁（Record Lock） ：属于单个行记录上的锁。
• 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
• 临键锁（Next-key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

InnoDB 的默认隔离级别 RR（可重读）是可以解决幻读问题发生的，主要有下面两种情况：

• 快照读（一致性非锁定读） ：由 MVCC 机制来保证不出现幻读。
• 当前读 （一致性锁定读）： 使用 Next-Key Lock 进行加锁来保证不出现幻读。

重点

其他补充面试题：https://blog.csdn.net/adminpd/article/details/122910606

drop、delete、truncate区别

	drop	delete	truncate
用法	drop table 表名 ，直接将表都删除掉	delete from 表名 where 列名=值，删除某一行的数据	truncate table 表名 ，清空表中的数据
所属	DDL 是数据定义语言，立即生效不可回滚	DML 是数据库操作语言，可回滚需提交	DDL 是数据定义语言，立即生效不可回滚
速度	把表占用的空间全部释放掉。最快	会产生数据库的binlog日志，而日志记录是需要消耗时间	不会产生数据库日志，因此比delete要快

关系型数据库

关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

• 大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。
• 常见的有：MySQL、PostgreSQL、Oracle、SQL Server、SQLite

MySQL索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。索引的作用就相当于书的目录。

优缺点

• 优点：
  • 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 缺点：
  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

底层结构

1. Hash表
   • 哈希表可以快速检索数据
   • Hash 冲突 问题
   • Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。
2. B树&B+树
   • 多路平衡查找树
   • B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
   • B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
   • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

索引类型

按照逻辑：

1. 主键索引(Primary Key)

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

   ​ 当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

   CREATE TABLE `table_name`( 
       ID INT NOT NULL, 
       username VARCHAR(16) NOT NULL, 
       PRIMARY KEY(ID) 
   );
   
   ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
   

2. 唯一索引(Unique Key)

   ​ 唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

   CREATE UNIQUE INDEX indexName ON table(column(length))
   
   ALTER TABLE `table_name` ADD UNIQUE indexName ON (column(length))
   

3. 普通索引(Index)

   ​ 普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

   CREATE INDEX index_name ON table(column(length))
   
   ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
   
   DROP INDEX index_name ON table
   

4. 全文索引(Full Text)

   ​ 全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。

   ​ 值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

   ALTER TABLE article ADD FULLTEXT index_content(content)
   
   CREATE FULLTEXT INDEX index_content ON article(content)
   

5. 多列索引（组合索引）

   ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
   

物理结构：

• 聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引；
• 非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

创建注意事项

1. 选择合适的字段创建索引
2. 被频繁更新的字段应该慎重建立索引。
3. 尽可能的考虑建立联合索引而不是单列索引。
4. 注意避免冗余索引 。
5. 考虑在字符串类型的字段上使用前缀索引代替普通索引。

事务四点性质

同时有多个事务可能产生的问题

事务隔离等级

范式

having 和where的区别

1. 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。

   HAVING是先分组再筛选记录，WHERE在聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前；而 HAVING子句在聚合后对组记录进行筛选。

2. WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。

3. WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。

4. WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。

5. WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。