数据库精选知识点

1. 数据库实现的重要功能有两条：计算、事务！也就是我们常说的 OLAP 和 OLTP，数据库的存储都是为这两件事服务的，单纯的存储并不是数据库的目标。

事务类功能主要解决数据在写入和读出时要保持的一致性，实现这件事的难度并不小，但对于应用程序的接口却非常简单，用于操纵数据库读写的代码也很简单。

其实 SQL 中的 Q 就是查询的意思，发明它的初衷主要是为了做查询（也就是计算），这才是 SQL 的主要目标。然而，SQL 在描述计算任务时，却很难说是很胜任的。

2. 这就是 SPL 的由来。它的理论基础不再是关系代数，称为离散数据集。基于这个新代数设计的形式语言，起名为SPL（Structured Process Language)。

SPL 针对 SQL 的不足（更确切地说法是，离散数据集针对关系代数的各种缺陷）进行了革新。SPL 重新定义了并扩展许多结构化数据中的运算，增加了离散性、强化了有序计算、实现了彻底的集合化、支持对象引用、提倡分步运算。

离散数据集是即有离散性又有集合化的代数体系，关系代数只有集合化。

ps：两个Integer比较时，比较的是它们指向的引用（即内存地址）是否相等。

因为Integer有一个常量池，-128~127直接的Integer数据直接缓存进入常量池。所以1在常量池，而128不在。👇

例子：Integer d3 = 1;
Integer d4 = 1;
Integer d5 = 128;
Integer d6 = 128;
System.out.println(d3 == d4);
//结果：true
System.out.println(d5 == d6);
//结果：false

字符串没有自动拆箱的功能，这一点需要特别注意。
此外，两个new出来的字符串对象使用==号判断时，也返回false。

equals方法其实是Object类中的方法

该方法非常简单，只判断两个对象的引用是否相等。

String e = “abc”;
String f = “abc”;
System.out.println(e.equals(f));
//结果：true

Objects类位于java.util包下，它是里面提供了很多对象操作的辅助方法

equals方法的判断逻辑如下：

1. 该方法先判断对象a和b的引用是否相等，如果相等则直接返回true。
2. 如果引用不相等，则判断a是否为空，如果a为空则返回false。
3. 如果a不为空，调用对象的equals方法进一步判断值是否相等。

也就是说，如果调用了Integer的equals方法，必须要求入参也是Integer类型，否则该方法会直接返回false。

由此可见，我们在使用Objects.equals方法，判断两个值是否相等时，一定要保证两个入参的类型要一致。否则即使两个值相同，但其结果仍然会返回false，这是一个大坑。

3. InnoDB 是 MySQL 默认的事务型存储引擎，只要在需要它不支持的特性时，才考虑使用其他存储引擎。

InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准隔离级别(未提交读、提交读、可重复读、可串行化)。其默认级别时可重复读（REPEATABLE READ），在可重复读级别下，通过 MVCC + Next-Key Locking 防止幻读

主索引时聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对主键查询有很高的性能。

InnoDB 支持真正的在线热备份，MySQL 其他的存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合的场景中，停止写入可能也意味着停止读取。

2. MyISAM不支持事务。
   不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

3. B+ Tree 是 B 树的一种变形，它是基于 B Tree 和叶子节点顺序访问指针进行实现，通常用于数据库和操作系统的文件系统中。

B+ 树有两种类型的节点：内部节点（也称索引节点）和叶子节点，内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存在叶子节点。

4. 为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道。每次会读取页的整数倍。

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

5. 索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

6. 对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。

7. 事务是指满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

8. 多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

事务的隔离级别实际上都是定义的当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”的隔离性，就需要通过加锁来实现了。

在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。

9. Phantom Proble 是指在同一事务下，连续执行两次同样的 sql 语句可能返回不同的结果，第二次的 sql 语句可能会返回之前不存在的行。

幻影读是一种特殊的不可重复读问题。

10. 记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。

对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。

对于 A->B，B->C，则 A->C 是一个传递函数依赖

11. 高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

第一范式 (1NF)
属性不可分。

第二范式 (2NF)
每个非主属性完全函数依赖于键码。
可以通过分解来满足。

第三范式 (3NF)
非主属性不传递函数依赖于键码。
（不存在传递依赖）

13. Entity-Relationship，有三个组成部分：实体、属性、联系。

14. 在早期的MySQL版本，DDL变更都会导致全表被锁，阻塞表上的DML操作，影响业务正常运行，好的一点就是，随着MySQL版本的迭代，DDL的执行方式也在变化。

15. 因为DML在执行期间会持有SHARED_READ锁，要执行DDL时获取SHARED_UPGRADABLE（共享可升级锁，缩写为SU，允许并发更新和读同一个表）锁成功，但是获取EXCLUSIVE MetaData Lock锁失败，处于暂挂PENDING状态。

16. 截止MySQL 8.0，OnLine DDL有三种方式COPY，INPLACE，INSTANT，MySQL会自动根据执行的DDL选择使用哪种方式，一般会优先选择INSTANT方式，如果不支持，就选择INPLANCE方式，再不支持就只能选择COPY方式了。

17. 一般MySQL binlog格式都是ROW，pt-osc在拷贝数据的过程也会产生大量的binlog，也可能导致主从延时，需要控制好每次拷贝数据的大小和频率，在执行期间，也会降低DML的并发度。

18. 用过Oracle的都知道，DDL变更都是修改元数据，上亿的表在Oracle中DDL变更都是瞬间完成。

19.主从复制原理

• MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中；
• 主库推送二进制日志文件Binlog中的事件到从库的中继日志Relay Log中，之后从库根据中继日志重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性；
• MySql通过三个线程来完成主从库间的数据复制，其中Binlog Dump线程跑在主库上，I/O线程和SQL线程跑着从库上；
• 当在从库上启动复制时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用，如下图所示。
  

20. Oracle数据库来说，SQL的执行计划可以缓存在library cache中避免再次执行相同SQL发生硬解析（语法分析->语义分析->生成执行计划），SQL执行结果缓存在RESULT CACHE内存组件中，有效的将物理IO转化成逻辑IO，提高SQL执行效率。

在不使用QueryCache的时候，每次执行相同的查询语句，都要发生一次硬解析，消耗大量的资源。

开启之后：

21. 从业务系统的操作类型，可以分为
OLTP（OnLine Transaction Processing 联机事务处理系统）和
OLAP（OnLine Analysis Processing联机分析处理系统），对于政企业务，也可以分为BOSS（Business Operation Support System-业务操作支撑系统，简称业支）
和BASS（Business Analysis Support System-业务分析支撑系统，简称经分）

22. MySQL 不会直接去修改磁盘的数据，因为这样做太慢了，MySQL 会先改内存，然后记录 redo log，等有空了再刷磁盘，如果内存里没有数据，就去磁盘 load。

而这些数据存放的地方，就是 Buffer Pool。

Buffer Pool 是a linked list of pages，一个以页为元素的链表。

为什么是链表？因为和缓存一样，它也需要一套淘汰算法来管理数据。

23. 于是如果 MySQL 发现你要修改的页，不在内存里，就把你要对页的修改，先记到一个叫 Change Buffer 的地方，同时记录 redo log，然后再慢慢把数据 load 到内存，load 过来后，再把 Change Buffer 里记录的修改，应用到内存（Buffer Pool）中，这个动作叫做 merge；而把内存数据刷到磁盘的动作，叫 purge：

• merge：Change Buffer -> Buffer Pool
• purge：Buffer Pool -> Disk

24. 其实只要理解了 InnoDB 里的所有表数据，都以索引（聚簇索引+二级索引）的形式存储起来，就知道索引已经包含了表数据。

25. 用 DDD 的话来讲，这张图可以告诉你，MySQL 里有哪些「域」（子域、核心域、通用域、支撑域），配合文字介绍，可以知道这些「域」之间都有什么样的能力、行为，知道「域」之间一些简单的交互。

26. MySQL 5.6开始支持ICP（Index Condition Pushdown），不支持ICP之前，当进行索引查询时，首先根据索引来查找数据，然后再根据where条件来过滤，扫描了大量不必要的数据，增加了数据库IO操作。

在支持ICP后，MySQL在取出索引数据的同时，判断是否可以进行where条件过滤，将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据，减少了不必要数据被扫描带来的IO开销。

在某些查询下，可以减少Server层对存储引擎层数据的读取，从而提供数据库的整体性能。

27. 开启profiling进行跟踪SQL执行期间每个阶段的资源使用情况。

利用 Using where; Using index 这种索引扫描不回表的方式减少资源开销来提升性能

在MySQL中，对于完全模糊匹配%%查询的SQL可以通过全文索引提高效率。

28. 执行比较大的一个查询后，执行mysql_reset_connection可以重新初始化连接资源。这个过程相比上面一种会好点，不需要重连，但是会初始化连接的状态。

1.优化表结构

（1）尽量使用数字型字段

（2）尽可能的使用 varchar 代替 char
变长字段存储空间小，可以节省存储空间。

（3）当索引列大量重复数据时，可以把索引删除掉
比如有一列是性别，几乎只有男、女、未知，这样的索引是无效的。

2.优化查询

3.索引优化

在 select 语句之前增加 explain 关键字，会返回执行计划的信息。

type 列：最重要的列之一。表示关联类型或访问类型，即 MySQL 决定如何查找表中的行。从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

30. 优化慢查询思路：

• 分析语句，是否加载了不必要的字段/数据
• 分析 SQL 执行句话，是否命中索引等
• 如果 SQL 很复杂，优化 SQL 结构
• 如果表数据量太大，考虑分表

31. （1）一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。

（2）聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

（3）索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

（4）聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

B+ 树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

33. 遇到过。我排查死锁的一般步骤：
    （1）查看死锁日志 show engine innodb status; （2）找出死锁Sql （3）分析sql加锁情况 （4）模拟死锁案发 （5）分析死锁日志 （6）分析死锁结果

34. MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

• 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
• 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

36. 字符序就是字符的排序和比较规则，一般以 _ci 结尾的表示大小写不敏感，_cs 结尾的表示大小写敏感，_bin 结尾的表示用编码值进行比较。

37. WAL 就是 Write-Ahead Logging，其实就是「所有的修改都先被写入到日志中，然后再写磁盘」，用于保证数据操作的原子性和持久性。

38. 回表就是先通过数据库索引扫描出该索引树中数据所在的行，取到主键 id，再通过主键 id 取出主键索引数中的数据，即基于非主键索引的查询需要多扫描一棵索引树.

39. 唯一索引更加适合查询的场景,普通索引更适合插入的场景

40. binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的文件。

undolog 是 InnoDB 存储引擎的日志，用于保证数据的原子性。保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修改之前的数据，可以用于回滚」，同时可以提供多版本并发控制下的读（MVCC）

relaylog 是中继日志，「在主从同步的时候使用到」，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容。

redolog 是 「InnoDB 存储引擎所特有的一种日志」，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。

可以做「数据恢复并且提供 crash-safe 能力」

当有增删改相关的操作时，会先记录到 Innodb 中，并修改缓存页中的数据，「等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中」。

• 1.「redolog」 是 「Innodb」 独有的日志，而 「binlog」 是 「server」 层的，所有的存储引擎都有使用到
• 2.「redolog」 记录了「具体的数值」，对某个页做了什么修改，「binlog」 记录的「操作内容」
• 3.「binlog」 大小达到上限或者 flush log 「会生成一个新的文件」，而 「redolog」 有固定大小「只能循环利用」
• 4.「binlog 日志没有 crash-safe 的能力」，只能用于归档。而 redo log 有 crash-safe 能力。

42. 当定义varchar长度小于等于255时，长度标识位需要一个字节(utf-8编码)
    当大于255时，长度标识位需要两个字节，并且建立的「索引也会失效」

43. 磁盘读写的最小单位是扇区，扇区的大小只有 512B 大小，操作系统一次会读写多个扇区，所以操作系统的最小读写单位是块（Block）。Linux 中的块大小为 4KB，也就是一次磁盘 I/O 操作会直接读写 8 个扇区。

44. 由于树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作（假设一个节点的大小「小于」操作系统的最小读写单位块的大小），也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能

但是 Innodb 使用的 B+ 树有一些特别的点，比如：

• B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
• B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB
  

聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。

46. 在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？

在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。

47. 熟悉Oracle数据库的都知道，索引里除了索引列还存储了row_id（代表数据的物理位置，由四部分组成：对象编号+数据文件号+数据块号+数据行号）

48. MySQL优化器是基于Cost的，COST主要包括IO_COST和CPU_COST，MySQL的CBO（Cost-Based Optimizer基于成本的优化器）总是选择Cost最小的作为最终的执行计划去执行

49. 索引下推一定是在联合索引的情况下，根据联合索引本身就有的数据直接做一次过滤，而不用再进行多次无用的回表再到Server层进行过滤

Using index condition并不代表一定是使用了索引下推，只是代表可以使用，但是不一定用了。。。

50. 唯一索引等值查询：

• 当查询的记录是存在的，next-key lock 会退化成「记录锁」。
• 当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。
  非唯一索引等值查询：
• 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
• 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。
  非唯一索引和主键索引的范围查询的加锁规则不同之处在于：
• 唯一索引在满足一些条件的时候，next-key lock 退化为间隙锁和记录锁。
• 非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

索引失效：

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列使用函数，就会导致索引失效。
• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
• MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

主要参考：
https://mp.weixin.qq.com/s/cQqc1r8jydbxMzav2FpPdA