数据库-底层原理

基础知识

数据库三大范式

数据库三大范式是指在关系型数据库设计中，为了避免数据不一致和数据不一致性而遵循的三个规范化级别

• 第一范式：确保每个属性都是原子性的，即每个属性都不能再分割为更小的数据单元。同时每个属性都要具有唯一的名称，不允许重复。

• 第二范式：保证非主键属性完全依赖主键属性。如果存在这样的情况，需要将表进行拆分，使每个表只包含一个实体类型

• 第三范式：保证非主键属性之间不存在依赖关系。如果存在，需要将表拆分，使每个表只包含一个实体类型。

关系型数据库：建立在关系模型基础上，适用于需要严格数据一致性和结构化数据的场景，使用SQL进行数据操作，支持ACID事务

非关系型数据库：适用于数据模型灵活、需要高性能和高扩展性场景，通常不需要预定义数据模型，采用最终一致性模型

MySQL的存储引擎MyIsam和InnoDB有什么区别？

存储引擎：负责数据的储存和提取

• 事务支持：MyIsam不支持事务；InnoDB支持事务，拥有ACID四大特性（原子性，一致性，隔离性，持久性），另外InnoDB可以在高并发环境下可以更好地处理数据一致性和完整性的问题（脏读、幻读、不可重复读）

  • 原子性确保事务的全有全无特性。

  • 一致性确保数据库在事务前后保持一致。

  • 隔离性确保并发事务之间的独立性。

  • 持久性确保事务结果的永久性。

• 行级锁定：InnoDB支持行级锁定，可以提高并发性能；而MyIsam不可以，高并发环境下性能较差

• 外键约束：InnoDB支持外键约束，可以确保数据的一致性和完整性，MyIsam不支持

• 索引方式：InnoDB使用的是B+树来管理数据，MyIsam使用的是B树

• 磁盘空间：InnoDB的数据存储方法比MyIsam更为复杂，它需要更多的磁盘空间

还有其他引擎吗？

还有Memory引擎

Memory 引擎适用于高性能访问、临时存储数据的场景，但由于其数据易失性的特点，不适合用于存储需要持久化的数据。

索引

什么是索引？

索引是一种帮助MySQL快速获取数据的数据结构，相当于是数据的目录

索引有哪些类型？有什么优点和缺点？

索引主要分为主键索引和普通索引（二级索引），从类型上可以分为唯一索引，单列索引，联合索引和全文索引等

• 优点：可以加快查询的效率

• 缺点：维护起来成本很高且需要占用很多的存储空间

B树和B+树有什么区别？

总：B树和B+树都是属于一种自平衡的多叉树，每个节点可以包含多个子节点，树高较低，整体呈现一种矮胖型，另外它们I/o次数少，查询效率较高

分：它们的区别在于B树是把数据储存在所有的节点中，包括内部节点；而B+树是把数据存在叶子节点中，并且所有叶子节点通过有序的链表相连，内部节点只储存索引，另外MySQL还对B+树进行了优化，它将B+树的叶子节点改成了数据页，数据页上面缀的是有序链表，数据页之间使用的是双向指针，每个数据页中不超过16KB的数据，查询数据是用的二分查找。

MySQL为什么不用红黑树和二叉树？

二叉树和红黑树都属于二叉树，对于数据较多时，树高较高，磁盘进行IO操作时会耗费大量时间和资源，另外红黑树还属于平衡二叉树，在对数据进行插入、删除时，还需要一系列的反转和改变节点的颜色等操作，消耗资源也比较多，相比B+树，B+树能更好的提供数据访问的效率，更适合处理大规模的数据需求，并且B+树更好的进行范围查询，另外B+树实现了聚集索引，数据和索引放在一起，查询效率高

红黑树 O（log（n））

性质

• 二叉搜索树（左节点<根节点<右节点）（左根右）

• 根和叶子节点（Null）都是黑色的（根叶黑）

• 不存在连续的两个红色节点（不红红）

• 任一节点到叶子结点所有路径中黑节点数量相同（黑路同）

插入：插入的节点默认是红色节点，因为插入红色节点要比插入黑色节点对红黑树的影响要小

聚集索引和非聚集索引是什么？有什么区别？

聚集索引：就是将索引和数据放在一起，数据和索引都是按照相同的逻辑顺序排序

非聚集索引：指索引与对应的数据分开

区别

• 空间分配：聚集索引需要一整块的内存空间，并且一张表最多只能有一个聚集索引，通常是主键索引；非聚集索引不需要一整块的内存空间，并且一张表中可以有多个非聚集索引

• 性能：聚集索引的索引与数据都是有序的，查询速度会比较快，但是在进行更新数据后，可能会导致对数据重新排序，影响性能；非聚集索引需要先找索引，再找数据，因此查找速率会比较慢，但是对于更新数据操作，不会影响其性能

为什么每张表都有一个主键ID？

主键ID使用的是聚集索引，聚集索引的数据和索引放在一起，在查询的时候根据索引查询即可；而二级索引使用的是非聚集索引：非聚集索引的索引和主键ID放在一起，在使用二级索引查询时，如果索引中没有我们需要的字段时，它就会根据主键ID到原表中查询，这个过程就是回表。如果没有主键ID，那么MySQL就会自动给我们设置一个隐藏的主键ID：rowID，它看不见，摸不着，为了方便我们对数据库进行操作，所以我们需要自行设置一个主键ID

如何设置主键ID？

bigint类型、主键、自增、非空

为什么会有回表？如何减少回表？

回表是因为在使用二级索引查询数据时，没有找到想要的数据，返回原表中查询。

一般采用覆盖索引来减少回表现象

覆盖索引：创建一个包含查询需要所有字段的索引，简单来说就是把常用的数据和索引放在一起

聚集索引：可以将一些查询频繁的字段设置为聚集索引

索引失效的情况有哪些？

• 使用左或者左右模糊匹配（like %xx 或者 like %xx%），xx%可以走索引；where 子句中的 or 比如 where id = 1 or age = 18，null、!=操作符

• 对索引使用函数 （where sum（age）= 5、where length（name） = 6 等）

• 对索引进行表达式计算 （where id + 1 = 10），但是进行计算式可以的，如 where id = 10 - 1

• 对索引进行隐式类型转换，索引是字符串类型，查询条件中，输入的是整型，此时就不会走索引，直接走全表扫描，用 where age = 60 去查 age varchar 此时不会走索引，直接走全表扫描；但是，如果索引是整型，查询条件中即使是字符串，也会走索引 （用 where age = ‘ 60’ 去查 age int）

• 联合索引非最左匹配。创建一个（a，b，c）联合索引

  • where a=1；

  • where a=1 and b=2 and c=3；

  • where a=1 and b=2；

  • 以上三种情况是走索引

  • where b=2；

  • where c=3；

  • where b=2 and c=3；

  • 这三种情况不走索引

• 当Mysql估计使用全表扫描比使用索引快时，也会走全文

索引调优的方法有哪些？

在建立索引时

• 分析数据模式，选择合适的索引策略，可以将一些比较常用的列设置为索引，也可以使用联合索引、聚集索引减少回表等

• 避免创建过多的索引，过多的索引会增加数据维护的开销，可能导致索引的失效

在使用查询语句时

• 尽量避免使用导致索引失效的情况

• 使用索引提示强制Mysql走索引，如force index 等语句

在查询过后

• 使用性能测试和检测工具，如Explain、Slow Query log等来进行检测Mysql的查询情况，从今进行下一步的优化和调整

一张表最多有多少个索引？

取决于使用数据库管理系统（DBMS）和版本

MySQL

• InnoDB ： 64个索引

• MyISAM ：64个索引

• 聚集索引 ： 1 个

• 非聚集索引：多个

索引的数量和长度有什么限制吗？

• InnoDB ：最多64个索引，最长767字符，行大小限制为64KB

• MyIsam ： 最多64个索引，最长1000个字符

怎么分析一个SQL语句有没有使用索引？

使用 explain

explain select * from your_table where column = 'value'

Explain语句能查出什么？

• 查询是否使用了索引。

• 使用了哪些索引。

• 查询的执行顺序。

• 估计的行数和执行成本。

• 是否使用了联接、排序、临时表等。

为什么分页场景容易产生索引失效？

主要原因在于大数据偏移量、复杂的排序和过滤条件等使索引失效

事务

什么是事务？Mysql的事务隔离级别有哪些？

事务：指一组作为一个单一工作单元执行的操作，这些操作要么全部执行，要么全部不执行

Mysql的事务隔离级别

• 读未提交：一个事务还未提交时可以被别的事务读取数据

• 读已提交：一个事务提交之后，所做的变更可以被其他事务看到

• 可重复度：事务在执行中用到的数据与最开始所看到的数据是一样的，默认的隔离级别

• 串行化：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突，后访问的事务必须等前一个事务执行完成，才能继续执行

脏读、幻读、不可重复读是什么？如何解决这些问题？

• 脏读：指一个事务读到另一个事务未提交的且修改过的数据

• 幻读：指一个事务在两次查询之间，令一个事务插入了新的数据行，导致前后查询的数据不一致

• 不可重复读：指一个事务多次读取同一数据，过程中，另一个事务对该数据进行了修改或删除，导致前一次读取和后一次读取的结果不一致

解决

• 锁机制：通过加锁来保证事务的隔离性

• 事务隔离级别：采用不同的事务隔离级别

• MVCC（多版本并发控制）：使用版本控制的方式来实现并发控制，每个事务在读取数据时会获取数据版本，每个事务不受其他事务影响

• 乐观并发控制：在事务提交前不加锁，但在更新数据时会检查数据是否被其他事务修改过，如果有冲突，则进行回滚操作

这些隔离级别会引发哪些问题？解决了哪些问题？

• 读未提交：三个问题都会引发

• 读已提交：解决了脏读，在该隔离级别下，事务只能读取到其他事务提交的数据，不可能读到其他事务未提交的数据

• 可重复读：解决了脏读和不可重复读，没解决幻读

• 串行化：三个问题都解决了

MVCC是什么？如何实现的？

MVCC又叫多版本并发控制，工作在读已提交和可重复读的隔离级别下，采用了read view 和undo log版本链来解决隔离性的问题，其中read view是读视图，记录了一些如活跃事务id、创建该read view事务的事务id、最大事务id，最小事务id等一些重要字段，通过比较事务id来判断一个事务能否读取到其他事务的数据，undo log是事务链表，用来记录事务在执行过程中对数据的修改，以便于回滚操作，工作原理就是查看当前事务是否在当前read view中，如果在，会寻找上一个版本的事务id，如果不在，该事物就是查找的事务；读已提交和可重复读差别就在这里

快照读和当前读

• 快照读和当前读是数据库中两种常见的读取数据的方式

• 快照读：指数据库中某个时间点的数据快照。保证事务提交前和事务开始前的数据是一至的，一般读取的是在事务开启前某个时间点的数据。快照读不会受到正在进行并发事务的修改数据的影响，能够提供一致性和可重复性的读取

• 当前读：指读取数据库中最新的数据，即读取操作发生时的数据。但是受到并发事务的影响，因为读取到的数据可能在事务提交后发生变化。

• 在数据库中，快照读通常用于一致性要求比较高的数据，如读取历史记录、生成报表等；当前读适用于读取最新数据状态的场景，如在线交易、实时查询等

事务的使用事项

• 读取操作一般不使用事务

• 写操作之前开始事务，加锁，事务提交后释放锁

• 大的事务可以拆成小事务

锁

Mysql中锁的分类

• 全局锁：主要用于全库逻辑备份

• 表锁

  • 普通表锁

  • MDL（元数据锁）：不需要显示使用，数据库会自动加锁，对一个表做增删 改查操作时会加MDL读锁，对表做结构变更操作时，加MDL写锁

• 行锁 ：相比于表锁开销大，加锁慢，可能会发生死锁，表锁不会死锁

  • 行记录锁：当查询没有索引时，会走全表，把查到的每一行都加锁，在读提交下，加锁的语句执行完成后，就会直接释放掉不符合要求的行锁。因此，如果一条更新语句没有走索引，会花费极大的开销

  • 间隙锁：锁住两个行之间的数据，不允许其他事务向中间写入新数据

  • NK锁（mysql默认加的锁）：加锁后无论主键索引还是二级索引都会加上间隙锁

  • 插入意向锁：只有在插入的时候会使用，和间隙锁冲突，但彼此不冲突

加锁的时机是什么，什么时候释放锁？

遵循两阶段协议，在语句执行开始时加锁，事务提交结束后释放

锁加在哪？

一般加在主键索引上，如果没有主键索引则加到row_id上

MDL锁什么时候加？

• 事务修改数据：当一个事务对数据库中的数据进行修改时，会获取相应的锁来确保事务的原子性和一致性，这样可以防止其他并发事务同时修改相同的数据，避免数据冲突和不一致性

• 并发事务访问同一数据：当多个事务同时访问同一数据，可能会存在读写冲突和数据不一致性问题，为了保证数据的隔离性，需要使用锁来协调并发事务之间的访问

• 数据库对象的元数据访问：当多个事务同时访问数据库对象的元数据时（如表、索引、视图的定义）,需要采用元数据锁来管理并发访问。元数据锁用于保护元数据的一致性，防止事务之间的冲突

MDL有什么危害？

• 并发性能下降：过多的元数据锁操作可能导致并发性能下降。当多个事务需要访问相同的元数据对象时，如果存在过多的元数据锁竞争，会引发锁冲突和阻塞，从而导致事务等待和执行时间延长，对并发性能产生负面影响。

• 阻塞和长时间等待：如果一个事务持有元数据锁并长时间不释放，其他事务需要等待该锁的释放。这可能导致其他事务阻塞和长时间等待，从而影响系统的响应性能和并发能力。

• 死锁：如果事务之间的元数据锁请求存在循环依赖，可能引发死锁。当多个事务相互等待对方所持有的元数据锁时，系统无法继续执行，需要通过死锁检测和解决机制来解决。

• 数据库对象的不一致性：如果对数据库对象的元数据访问没有正确加锁或使用不恰当的隔离级别，可能导致数据库对象的不一致性。例如，一个事务正在修改表的结构或索引定义时，其他事务可能读取到不一致的元数据，导致数据库对象定义的不一致性。

如何避免出问题？

应该在设计数据库架构时合理规划和管理元数据锁的使用。可以考虑优化元数据的访问频率和方式，减少对元数据的并发访问冲突，使用合适的隔离级别，以及定期进行性能监控和调优。同时，合理并发控制和锁管理策略也是确保元数据锁正常运行和性能的重要措施。

NK锁的两个优化

• 索引上的等值查询，给唯一索引加锁的时候，NK锁退化为行锁

• 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NK锁退化为间隙锁

乐观锁和悲观锁

• 悲观锁是一种比较保守的锁机制，它认为在整个事务过程中，数据很有可能会被其他事务修改，因此在对数据进行操作时，必须先对其进行加锁，以防止其他事务的干扰。悲观锁通常使用数据库的锁机制实现，如行锁、表锁等，可以有效地控制并发访问，但会对系统性能产生影响。

• 乐观锁是一种比较乐观的锁机制，它认为在整个事务过程中，数据很少会被其他事务修改，因此在对数据进行操作时，并不对其进行加锁，而是在事务提交之前检查数据是否被其他事务修改过，如果被修改过，则回滚事务，重新进行操作。乐观锁通常使用版本号或时间戳等机制实现，可以减少锁的使用，提高系统性能，但需要增加一些额外的开销来实现数据版本控制。

插入意向锁

只有在insert的时候会使用，和间隙锁冲突，但是彼此不冲突。比如两个写入的事务都有（1，5）的意向锁，一个写入2，一个写入4，不会发生冲突。如果（1，5）之间有间隙锁，那么他们都会个间隙锁发生冲突。

读写锁

1. 读锁，又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁

2. 写锁，又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。

3. 以上都基于当前读。在快照读下，无论加不加锁，都可以直接读。

读写意向锁

表锁和行锁是互相冲突的。如果一个行锁只锁住了一行数据，这时要申请一下表锁，那么会遍历表，看看是否存在行锁，开销很大。为了解决这个问题，会先在表上加上意向锁，然后再执行行锁操作。这样就可以避免上述问题。

意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

• 意向锁之间互不冲突；

• S 锁只和 S/IS 锁兼容，和其他锁都冲突；

• X 锁和其他所有锁都冲突；

• AI 锁只和意向锁兼容；

加锁的规则

根据极客时间的《Mysql实战45讲》中的说明，对于加锁的基本规则大致为5个，包含了两个“原则”、两个“优化”和一个“bug”：

1. 原则 1：加锁的基本单位是 next-key lock。，next-key lock 是前开后闭区间

2. 原则 2：查找过程中访问到的对象才会加锁

3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁

4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁

5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

日志

日志的分类：

• undo_log

• bin_log

• redo_log

bin_log和redo_log的区别

• redo_log是InnoDB引擎特有的，bin_log是Mysql的server层实现的，所有引擎都可以使用

• redo_log是物理日志，记录的是“在某个数据页上做了什么修改”；bin_log是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行c字段加1”

• redo_log的大小有限，超过后会循环写入；bin_log是可以追加写入的。

• redo_log主要用于断电等故障恢复；bin_log主要用于备份恢复、主从复制等方面。

什么是WAL机制？

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB引擎就会把记录写到redo_log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

什么时候刷脏页？

• redo_log写满时，需要暂停更新操作，此时会停止所有的写入操作

• 机器的物理内存满了的时候

• Mysql处于空闲状态时

• Mysql重启时

什么是两阶段提交？

两阶段提交是分布式系统中比较常见的一种事务提交算法。在Innodb的日志提交时用到了这个协议，用来保证事务提交时，redo_log和bin_log 都处于完成状态。具体而言：

1. 操作完更新语句，把数据保存到内存

2. 写入Redo_Log，处于prepare阶段

3. 写入Bin_Log

4. 提交事务，Bin_Log和Redo_Log都处于完成状态。

Redo_log和Bin_log是如何配合工作实现持久化的？

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 RedoLog：

• 如果碰到既有 prepare、又有 commit 的 RedoLog，就直接提交；

• 如果碰到只有 prepare、而没有 commit 的RedoLog，就拿着 XID 去BinLog 找对应的事务。

数据切分

• 垂直切分：按字段

• 水平切分

  • 取余操作

  • 按日期切分