数据库原理知识点总结归纳;数据库原理及应用知识点总结 收藏 0

五分钟！快速读懂数据库原理

一、事务

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

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。

强一致性、弱一致性、最终一致性：*对于关系型数据库，要求更新过的数据能被后续的访问都能看到，这是强一致性；如果能容忍后续的部分或者全部访问不到，则是弱一致性**；如果经过一段时间后要求能访问到更新后的数据，则是最终一致性。*

一个事务所做的修改在最终提交以前，对其它事务是不可见的。

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

SQL Server中使用**了WAL（Write-Ahead Logging，预写日志）**技术来保证事务日志的ACID特性，在数据写入到数据库之前，先写入到日志，再将日志记录变更到存储器中。

undo日志用于记录事务开始前的状态，用于事务失败时的回滚操作；redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是5，修改后的值为15，那么Undo日志为，Redo日志为。

梳理下事务执行的各个阶段：

写undo日志到log buffer；

执行事务，并写redo日志到log buffer；

如果innodb_flush_log_at_trx_commit=1，则将redo日志写到log file，并刷新落盘。

提交事务。

innodb_flush_log_at_trx_commit=1标识：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。

因为data buffer中的数据会在合适的时间 由存储引擎写入到data file，如果在写入之前，数据库宕机了，根据落盘的redo日志，完全可以将事务更改的数据恢复。这种先持久化日志的策略就叫做Write Ahead Log。参考数据库事物如何保证。

事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

只有满足一致性，事务的执行结果才是正确的。

在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。

在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。

事务满足持久化是为了能应对数据库崩溃的情况。

MySQL 默认采用自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务(使用COMMIT来提交)，那么每个查询都会被当做一个事务自动提交。

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

考虑飞机订票系统中的一个活动序列:

甲售票点（甲事务）读出某航班的机票余额A,设A=16.

乙售票点（乙事务）读出同一航班的机票余额A,也为16.

甲售票点卖出一张机票,修改余额A←A-1.所以A为15,把A写回数据库.

乙售票点也卖出一张机票,修改余额A←A-1.所以A为15,把A写回数据库.

结果明明卖出两张机票，数据库中机票余额只减少1。

T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。即脏读是指读取了事务回滚前的脏数据。

T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配。

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

或者用更通俗地说，事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。

也就是说，当前事务读第一次取到的数据比后来读取到数据条目少。

幻读和不可重复读的区别：幻读是读到之前没有出现过的数据，不可重复读是同一条数据两次读的结果不一样。不可重复读针对的是update或delete，幻读针对的insert。

产生并发不一致性问题主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

MySQL 中提供了两种封锁粒度：行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

这里的读写锁是说行级锁。

排它锁（Exclusive），简写为 X 锁，又称写锁。

共享锁（Shared），简写为 S 锁，又称读锁。

有以下两个规定：

一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。

一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。

锁的兼容关系如下：

使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；

一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

各种锁的兼容关系如下：

解释如下：

任意 IS/IX 锁之间都是兼容的，因为它们只是表示想要对表加锁，而不是真正加锁；

S 锁只与 S 锁和 IS 锁兼容，也就是说事务 T 想要对数据行加 S 锁，其它事务可以已经获得对表或者表中的行的 S 锁。

一级封锁协议

事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

二级封锁协议

在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。

可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

三级封锁协议

在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

两段锁协议是指所有的事务必须分两个阶段对数据项加锁和解锁。即事务分两个阶段，第一个阶段是获得封锁。事务可以获得任何数据项上的任何类型的锁，但是不能释放；第二阶段是释放封锁，事务可以释放任何数据项上的任何类型的锁，但不能申请。（即在某段时间只能申请锁，在某段时间只能释放锁）

第一阶段是获得封锁的阶段，称为扩展阶段：其实也就是该阶段可以进入加锁操作，在对任何数据进行读操作之前要申请获得S锁，在进行写操作之前要申请并获得X锁，加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。就是加锁后就不能解锁了。

第二阶段是释放封锁的阶段，称为收缩阶段：当事务释放一个封锁后，事务进入封锁阶段，在该阶段只能进行解锁而不能再进行加锁操作。

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。

但不是必要条件，例如以下操作不满足两段锁协议，但是它还是可串行化调度。

MySQL 的 InnoDB 存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。

InnoDB 也可以使用特定的语句进行显示锁定：

为了避免丢失更新、脏读、不可重复读和幻读，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

只处理更新丢失。如果一个事务已经开始写数据，则不允许其他事务同时进行写操作，但允许其他事务读此行数据。事务中的修改，即使没有提交，对其它事务也是可见的。可通过“排他写锁”实现。

处理更新丢失、脏读。读取数据的事务允许其他事务继续访问改行数据，但是未提交的写事务将会禁止其他事务访问。也就是说一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。可通过“瞬间共享读锁”和“排他写锁”实现。

**处理更新丢失、脏读和不可重复读取。**多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）；

提供严格的事务隔离。对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。解决了幻读问题。

参考：数据库四大特性及数据库隔离级别和数据库隔离级别。

隔离级别脏读可能性不可重复读可能性幻读可能性加锁读读未提交√√√×读提交×√√×可重复读××√×序列化×××√

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

MVCC在大多数情况下代替了行锁。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

但是，使用MVCC每行记录都需要额外的存储空间，需要做更多的行维护和检查工作。

在不考虑redo log 的情况下利用undo log工作的简化过程为：

开始事务

记录数据行数据快照到undo log

更新数据

将undo log写到磁盘

将数据写到磁盘

提交事务

Innodb通过undo log保存了已更改行的旧版本的信息的快照(read-view，数据库快照就可以理解为数据库某一时刻的照片，它记录了此时数据库的数据信息)。该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。通过读取快照读取的信息都是过去的信息。

MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

创建版本号：指示创建一个数据行的快照时的系统版本号；

删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

具体如下：

列名长度(字节)作用DB_TRX_ID6插入或更新行的最后一个事务的事务标识符。（删除视为更新，将其标记为已删除）DB_ROLL_PTR7写入回滚段的撤消日志记录（若行已更新，则撤消日志记录包含在更新行之前重建行内容所需的信息）

以下实现过程针对可重复读隔离级别。

当开始新一个事务时，该事务的版本号肯定会大于当前所有数据行快照的创建版本号，理解这一点很关键。

1. SELECT

InnoDB 会根据两个条件来检查每行记录：

InnoDB只查找版本(DB_TRX_ID)早于当前事务版本的数据行（行的系统版本号<=事务的系统版本号,这样可以确保数据行要么是在开始之前已经存在了，要么是事务自身插入或修改过的）

行的删除版本号(DB_ROLL_PTR)要么未定义（未更新过），要么大于当前事务版本号（在当前事务开始之后更新的）。这样可以确保事务读取到的行，在事务开始之前未被删除。

2. INSERT

将当前系统版本号作为数据行快照的创建版本号。

将当前系统版本号作为数据行快照的删除版本号。

4. UPDATE

将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

MySQL的InnoDB存储引擎默认事务隔离级别是RR(可重复读), 是通过 \”行排他锁+MVCC\” 一起实现的，在innodb中的操作可以分为当前读(current read)和快照读(snapshot read)。

1. 快照读

使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销。简单的select操作(当然不包括 select … lock in share mode, select … for update)是快照读。

2. 当前读

读取的是最新的数据，需要加锁。以下第一个语句需要加 S 锁，其它都需要加 X 锁。

在RR级别下，快照读是通过MVVC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。

innodb在快照读的情况下并没有真正的避免幻读, 但是在当前读的情况下避免了不可重复读和幻读。

参考：『浅入浅出』MySQL 和 InnoDBMySQL-InnoDB-MVCC多版本并发控制MySQL中InnoDB的多版本并发控制(MVCC)轻松理解MYSQL MVCC 实现机制数据库内核月报数据库内核月报

Next-Key Lock 是 MySQL 的 InnoDB 存储引擎的一种锁实现。

MVCC 不能解决幻读的问题，Next-Key Lock 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Lock 可以解决幻读问题。

InnoDB有三种行锁的算法：Record Lock、Gap Lock和Next-Key Lock，其中Next-Key Lock是前两者的结合。

锁定一个记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Lock 依然可以使用。

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

它是 Record Lock 和 Gap Lock 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

本文来源：http://8nn.co/anJW

最后

刚整理 2020 年全套最新精品技术资料免费发给你！ (原价最少8999元，超2000G！)

领取 看看下面！！

1、点赞 + 评论 （勾选 “转发” ）

2、关注小编私信。点击头像，关注。并私信回复关键词： 1024

为什么你精通CRUD，却搞不懂数据库的基本原理？

作者：黄小斜

来源：https://juejin.im/post/5e5528b7e51d4526ce61451d

作为一个程序员，不了解数据库怎么能行，那么数据库到底是个啥呢，作为一个Java工程师，平时和数据库打交道着实不少，所谓的CRUD其实就是对数据库进行增删改查的操作。

根据百度百科的介绍，数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。

数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。

是不是没听懂，好吧，简单来说，像MySQL这样的数据库，就是用于存储结构化数据的，比如一个学生的信息数据，一个商品的数据，或者是一个学生购买商品的消费数据。

联想到平时我们经常使用的Excel，其实和数据库挺像的，数据库其实就是一个表格，里面有很多的数据类型，比如字符串，比如数字，再比如长文本等等。

而这类数据库也叫关系型数据库，典型代表就是MySQL。

再看看百度百科的介绍，关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

除了关系型数据库之外，近些年来还有很多nosql（not only sql）数据库在兴起，比如MongoDB，以及图数据库、列式数据库等等，这些数据库对于新手程序员来说用的并不多，所以本文我们只讨论关于关系型数据库的内容。

关系型数据库是我们程序员平时用的最多，也最简单易上手的数据库类型，所以，学习数据库一般也从MySQL这类关系型数据库入手，一来它简单好学，二来它是免费的。

我第一次听说“数据库”这个词还是在大学的时候，那时候正值大家找工作，有一个同学和我们分享自己的求职经验，据他所说，每个公司都会考察你的数据库知识，学好了数据库，面试什么公司都不怕。

当时我还觉得挺玄乎的，不过我们学校确实没来什么大公司，我对此半信半疑，至于到底是不是这样，后来也就没再追究了。

第二次接触数据库，是在学习Java的时候，记得当初自己跟着一个小教程，下载安装了MySQL，然后从网上下载了demo代码，用当时先进的开发工具eclipse跑通了别人家的代码，其实就是增删改查。

只不过当时对这些东西也没啥概念，后来又下载了一个db的可视化工具，可以更方便地了解自己的数据库里到底都有啥。

再到后来，我开始慢慢接触项目实战，使用数据库的时间也越来越长，自以为对于数据库的理解还算可以，直到我真正地接触了数据库相关的面试题，才让我发现自己对于数据库的理解是多么的肤浅。

那个时候，我只懂写一些简单的crud，甚至连内外连接、group by和count等一些简单的sql用法都不了解，除此之外，我对于数据库的一些原理性内容几乎也是一窍不通的，比如数据库的索引、事务、锁机制，我统统不知道，更别谈如何回答相关的面试题了。

于是，我开始意识到学习数据库原理是多么重要的一件事情，我开始看数据库方面的书籍，从最基础的数据库教材，再到MySQL的原理性内容，再到mybatis这类orm框架的实现，我都逐渐地开始学习和了解，直到我能够对大部分的数据库面试题都较好地掌握。

而现在，我在一个每天要处理海量数据的公司里工作，对于数据方面的学习已经不仅仅停留在数据库了，我还要接触数据仓库、分布式数据库以及各种大数据的应用场景，学习数据知识的道路看来短时间内是走不完了。

说到MySQL，就不得不谈谈SQL了，sql，按照百科的说法是这样的

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

对于不同的数据库，sql的写法可能有一些差异，但是大致都是相同的，就拿MySQL来说，平时我们用到最多的就是select、update、delete和insert了，这类操作统称为数据操纵语言DML（Data Manipulation Language），用户通过它可以实现对数据库的基本操作。

当然还有操作数据库和表的一些ddl，数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。 简单来说，ddl其实就是用来执行建表，删表，更改表结构等操作的

以前我的sql写的并不多，毕竟简单的增删改查并不需要多复杂的sql技巧，最多就是做一下表连接。

再到后来，我开始做一些项目，接触了ORM的一些框架，比如hibernate和mybatis，这些框架甚至可以让你完全不写sql，只要调用一些增删改查的API即可，这让我很长一段时间都忽略了SQL的重要性。

其实学习sql最复杂的地方并不是一些高级用法，而是在于实际场景中的应用和优化。两条sql，可能因为简单的差异就会有很大的性能差距，差异点可能是，比如有没有走到索引，是否是全表扫描，又或者走到了哪个索引，而这样的性能分析只有在实际的场景里才有意义。

当然了，对于新手来说，学习sql的语法是放在第一位的，然后才能逐渐过渡到会使用，会优化，会分析。

想要真正学好数据库，搞定面试题中的相关问题，那你就必须要搞懂数据库的基本原理。

如果你不相信，那我随便举几个面试题常见的题型，比如”数据库事务的ACID是什么意思”、“数据库中有哪些锁”、“数据库的索引用的是什么数据结构”

上面几道题算是简易版的，在平常的面试中也经常能够看到，就算你只是背一背答案，可能也多少能答对一些，那么如果再进一步，考察一下数据库的原理性知识，恐怕就没这么简单了。

举个栗子“数据库事务的ACID是如何实现的”，“数据库中有哪些锁，分别是如何实现的，如何使用”、“数据库中的索引是如何实现的，何时命中索引，何时不能，为什么“

是不是感觉难度一下子就上来了。毕竟深入到原理性的东西，懂的人就越来越少了。除此之外，还有很多进阶的面试题，更多时候会结合实际场景来考察。

比如考察你对MySQL存储引擎innodb的理解，对于sql优化的理解 ，以及对于分布式场景下数据库能力的应用，比如分布式锁，分布式事务，以及分库分表的一些问题。

知道了面试题大概都考啥之后，接下来我们要做的就是找到症结所在，然后一一击破

学习数据库，你需要先打好基础，数据库原理是计算机科班的一门必修课，非科班的我选择的是自学，我当时也是拿着那本大学教材《数据库系统概论》来自学的。

教材这种东西，肯定还是偏理论的一点，我们要了解的内容主要包含几个方面

1、数据库模型有哪些：包括层次模型、网状模型和关系模型，我们所用的关系型数据库就是基于关系模型实现的。

2、关系数据库的基础和sql：了解关系数据库的基本概念，了解sql的基本使用方法，至少CRUD和一些常见用法要搞懂

3、数据库的安全性和完整性，这两部分内容其实理论的东西比较枯燥，实际上对应到实际场景中，安全性就是要保护数据的安全，包括权限控制和数据备份，而完整性则是通过一些约定和规范来限制数据库的存储内容，比如我们可以用主键、唯一键、非空等要求来限制字段的取值。

4、关系数据理论 这部分内容很有意思，也比较复杂，讲到了数据库的范式理论，从一范式到四范式，各有各的用法和要求，某互联网公司“第四范式”就是用这个概念来命名的。

5、事务和锁 最后一部分内容，就是数据库的两个重要组成部分，事务和锁，事务可以保证一组数据库操作的ACID特性，非常适用于需要数据一致性的场景，而数据库的锁不但是实现事务的基础，还可以灵活地适用于不同的数据库应用场景，我们还可以通过sql语句来完成加锁和释放，对于并发场景尤其管用。

学习完了数据库基础之后，接下来就该学习MySQL了，毕竟很多时候我们的数据库应用就是MySQL。

其实MySQL里的很多知识点和我们上面提到的数据库基础大同小异，而回到MySQL的实现层，我们就得来看看MySQL的存储引擎了。

MySQL的存储引擎分为innodb和myisam，相信大家对于这两个引擎区别的面试题看了也不少，比如innodb支持事务，支持行级锁，而myisam不支持。

由于现在innodb基本上是主流，所以我们讨论MySQL的时候基本上就是在讨论innodb。对于MySQL的实现原理，我认为大概有这么一些内容需要我们去学习。

首先，我们了解MySQL里有哪些数据类型，一般的用法如何，然后，尝试用MySQL去跑一些sql语句，建库建表，加索引加主键，总之，这些实践能帮助你更好地学习上述内容。

想要更好地了解MySQL的原理，我们就必须要了解MySQL的整体架构，一般来说，MySQL从上到下可以分为这么几层

也就是我们经常用的可视化工具，比如Navicat for MySQL

就是我们安装的MySQL程序，其实打开它它就是一个MySQL的服务端进程

sql执行层主要负责解析执行sql，里面包含了很多复杂的组成部分，比如解释器，分析器，优化器等等，执行层会生成一个sql的执行计划，这个计划也经常是我们分析sql性能的一个重要参考内容

存储引擎层是innodb了，比如数据要如组织和存放，索引要如何建立和管理等等，加锁怎么加，事务如何实现，都是这一层要考虑的内容。

存储引擎的下一层就是文件系统了，数据库的数据如何和文件系统进行交互，就是这一层要做的事情了。

不得不说，索引绝对是数据库中最经常考察，考点也最多的内容了。

比如给你一条sql，那么它能否命中索引，能命中哪些索引，如果想要命中某个索引，你应该如何修改，这种问题面试时是不是经常看到，变来变去这么多题型，其实面试官就是想考察你对于索引的理解。

还有就是，索引的数据结构，一样是非常热门的考点之一，索引其实是基于B+树来实现的，不知道b+树是啥，请回去看数据结构。

简单来说，它是一种多路搜索树，致力于更短的时间来完成数据检索，因为它的高度比二叉树要低，而比起普通的b树，它的非叶节点只起索引作用，而叶子节点是顺序串联的，所以非常适合做搜索树。

如果你理解了这一点，那么面对此类面试题就可以更加从容一些，面试官其实就是想知道你对b+树的了解到了什么样的程度而已。

除了索引之外，数据库中最复杂的内容可能就是事务和锁了。

就拿事务的ACID特性来说，你需要了解每个大写字母背后的实现原理，比如原子性是如何实现的，一致性是如何保证的，背后的原理是什么。

我们平时常用的事务可能就是spring里的事务模板，在事务里执行的同库数据库操作，要么都成功，要么都失败，这就是原子性。

两个事务之间互不影响，这就是隔离性，当然了，这里又涉及到了事务隔离级别。

事务隔离级别包括读未提交，读已提交，可重复读和序列化，每个事务隔离级别都适用于某种数据库读写场景，很多时候，我们都需要搞懂隔离级别背后的原理，才能更好地适用它。

MySQL里默认使用可重复读的隔离级别，这个级别基本上可以保证我们的事务按照预期执行，在MySQL中，这个事务隔离级别甚至可以解决幻读的问题。

在MySQL的事务背后，其实有一个隐藏的boss，那就是数据库的锁，很多事务的隔离级别都是通过锁来实现的，比如可重复读只要加行锁就可以实现了，而幻读则需要加上间隙锁next-key lock来实现。

行级锁和表级锁是MySQL中的两种锁，表级锁顾名思义，会直接锁表，一次只有一个事务能够访问，而行级锁其实锁的也并非是一行，在MySQL中，这个锁加在索引上，而这个索引对应的数据往往不止一行，所以这个行级锁只是理论意义上的\”行级锁\”

说了这么多，要理解MySQL的事务和锁，还是要多看看相关的书籍和文章，理解其内部的实现原理，知其然也要知其所以然。

下面又到了我们熟悉的资源推荐环节

数据库

0 《数据库系统概论》

数据库原理应该是教材吧，这本书作为数据库入门来说还是可以的，毕竟不是专门做DB的，看大厚书用处不大，这本书把数据库的基本概念都讲完了。

1 sql必知必会

​

​这本书主要是讲解sql语句怎么写，毕竟数据库最重要的一点就是要熟练地使用sql语句，当然这本书也可以当做工具书来使用。

2 深入浅出MySQL

​

​这本书适合作为MySQL的学习书籍，当你有了一定的MySQL使用经验后，可以看看它，该书从数据库的基础、开发、优化、管理维护和架构5个方面对MySQL进行了详细的介绍，讲的不算特别深，但是足够我们使用了。这本书我也只看了一部分。

3 MySQL技术内幕：innodb存储引擎

​

看完上面那本书以后，对MySQL算是比较熟悉了，不过对于面试中常考的innodb引擎，还是推荐一下这本书把，专门讲解了innodb存储引擎的相关内容。我还没有细看，但是内容足够你学好innodb了。

4 高性能Mysql

​​

这本书可以说是很厚了，更适合DBA拜读，讲的太详细了，打扰了。 如果需要获取到这些书籍的PDF版本，关注转发本文章后私信【文档】即可免费获取到

从原理到优化，深入浅出数据库索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。数据库查询是数据库的最主要功能之一，我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化，这篇文章对索引做一个系统的梳理，希望对大家有帮助。

索引的分类可以从多个角度进行，下面分别从数据结构，物理存储和业务逻辑三个维度进行划分。

关于B+树索引，后面会深入解析

• 仅仅能满足\”=\”,\”IN\”和\”<=>\”查询，不能使用范围查询
• 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
• 只有Memory存储引擎显示支持hash索引

现在MyISAM和InnoDB引擎都支持了

用于对GIS数据类型创建SPATIAL索引

• 正文内容按照一个特定维度排序存储，这个特定的维度就是聚集索引；
• Innodb存储引擎中行记录就是按照聚集索引维度顺序存储的，Innodb的表也称为索引表；因为行记录只能按照一个维度进行排序，所以一张表只能有一个聚集索引。

• 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
• 非聚集索引索引项顺序存储，但索引项对应的内容却是随机存储的；

举个例子说明下：

该表中主键id是该表的聚集索引、name为非聚集索引；表中的每行数据都是按照聚集索引id排序存储的；比如要查找name=\’Arla\’和name=\’Arle\’的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引表节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行数据的真实内容。

主键索引是一种特殊的唯一索引，不允许有空值

复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建.

• unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；
• index和key为同义词，两者作用相同，用来指定创建索引
• col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；
• index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• asc或desc指定升序或降序的索引值存储

查看表结构

show create table table_name;可以使 EXPLAIN 语句查看索引是否被使用

全文索引只能在char，varchar或者text 类型的字段上。而且，只有MyISAM 储存引擎支持全文索引。

这里需要注意的，subject 的长度为255，但是index4_st索引只有10。这样做的目的还是为了提高查询速度。对于字符型的数据，可以不用查询全部信息，只查询其前面的若干字符信息。

这是我们可以看到，name 和sex字段上已经创建了index_ns索引。

在example0() 表中的id 创建名为index7_id 的索引。

在name字段上创建名为indx_name 的索引

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，那么索引树是如何维护的？

查找是数据结构和算法中一个非常重要的概念。

• 线性查找：一个个找；实现简单；太慢
• 二分查找：有序；简单；要求是有序的，插入特别慢
• HASH查找：查询快；占用空间；不太适合存储大规模数据
• 二叉查找树：插入和查询很快(log(n))；无法存大规模数据，复杂度退化
• 平衡树：解决 BST 退化问题，树是平衡的；节点非常多的时候，依然树高很高
• 多路查找树：一个父亲多个孩子节点（度）；节点过多树高不会特别深
• 多路平衡查找树：B-Tree

B-Tree是一种多路搜索树（并不是二叉的）：

1. 定义任意非叶子结点最多只有M个儿子；且M>2；
2. 根结点的儿子数为[2, M]；
3. 除根结点以外的非叶子结点的儿子数为[M/2, M]；
4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5. 非叶子结点的关键字个数=指向儿子的指针个数-1；
6. 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7. 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8. 所有叶子结点位于同一层；
9. 每个k对应一个data。如：（M=3）相当于一个2–3树，2–3树是一个这样的一棵树， 它的每个节点要么有2个孩子和1个数据元素，要么有3个孩子和2个数据元素，叶子节点没有孩子，并且有1个或2个数据元素。

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；B-Tree上查找算法的伪代码如下：

1. 关键字集合分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；
4. 其搜索性能等价于在关键字全集内做一次二分查找；
5. 自动层次控制；

B树中每一个内部节点会包含一定数量的键值。通常，键值的数量被选定在d和2d之间。在实际中，键值占用了节点中大部分的空间。因数2将保证节点可以被拆分或组合。如果一个内部节点有2d个键值，那么添加一个键值给此节点的过程，将会拆分2d键值为2个d键值的节点，并把此键值添加给父节点。每一个拆分的节点需要最小数目的键值。相似地，如果一个内部节点和他的邻居两者都有d个键值，那么将通过它与邻居的合并来删除一个键值。删除此键值将导致此节点拥有d-1个键值;与邻居的合并则加上d个键值，再加上从邻居节点的父节点移来的一个键值。结果为完全填充的2d个键值。

下面是往B树中依次插入

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

B-Tree有许多变种，其中最常见的是B+Tree，MySQL就普遍使用B+Tree实现其索引结构。与B-Tree相比，B+Tree有以下不同点：

1. 非叶子结点的子树指针与关键字个数相同；
2. 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
3. 为所有叶子结点增加一个链指针；
4. 所有关键字都在叶子结点出现；
5. 内节点不存储data，只存储key如：（M=3）

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

1. 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2. 不可能在非叶子结点命中；
3. 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4. 更适合文件索引系统；

下面是往B+树中依次插入

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

假如每个盘块可以正好存放一个B树的结点（正好存放2个文件名）。那么一个BTNODE结点就代表一个盘块，而子树指针就是存放另外一个盘块的地址。

下面，咱们来模拟下查找文件29的过程：

1. 根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作 1次】
2. 此时内存中有两个文件名17、35和三个存储其他磁盘页面地址的数据。根据算法我们发现：17<29<35，因此我们找到指针p2。
3. 根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作 2次】
4. 此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现：26<29<30，因此我们找到指针p2。
5. 根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作 3次】
6. 此时内存中有两个文件名28，29。根据算法我们查找到文件名29，并定位了该文件内存的磁盘地址。分析上面的过程，发现需要3次磁盘IO操作和3次内存查找操作。关于内存中的文件名查找，由于是一个有序表结构，可以利用折半查找提高效率。至于IO操作是影响整个B树查找效率的决定因素。当然，如果我们使用平衡二叉树的磁盘存储结构来进行查找，磁盘4次，最多5次，而且文件越多，B树比平衡二叉树所用的磁盘IO操作次数将越少，效率也越高。

B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

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

索引查询是数据库中重要的记录查询方法，要不要进入索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际中的一些通用的原则：

1. 在经常用作过滤器的字段上建立索引；
2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；
3. 在不同值较少的字段上不必要建立索引，如性别字段；
4. 对于经常存取的列避免建立索引；
5. 用于联接的列（主健/外健）上建立索引；
6. 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；
7. 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。
8. 经常用在WHERE子句中的数据列；
9. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用；
10. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引；
11. 对于定义为text、image和bit的数据类型的列不要建立索引；
12. 对于经常存取的列避免建立索引；
13. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
14. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

一个单独的索引扫描只能用于这样的条件子句：使用被索引字段和索引操作符类中的操作符， 并且这些条件以AND连接。

假设在(a, b)上有一个索引， 那么类似WHERE a = 5 AND b = 6的条件可以使用索引，但是像WHERE a = 5 OR b = 6的条件就不能直接使用索引。

一个类似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 这样的查询可以分解成四个在x上的独立扫描，每个扫描使用一个条件， 最后将这些扫描的结果OR 在一起，生成最终结果。

另外一个例子是，如果我们在x 和y上有独立的索引，一个类似WHERE x = 5 AND y = 6 这样的查询可以分解为几个使用独立索引的子句，然后把这几个结果AND 在一起，生成最终结果。

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
2. 对于多列索引，不是使用的第一部分(第一个)，则不会使用索引
3. like查询是以%开头
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。

对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

这里记录两种方式，分别是

大家可以注意：

• handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
• handler_read_rnd_next:这个值越高，说明查询低效

分析这几个值，我们可以查看当前索引的使用情况：

• Handler_read_first：索引中第一条被读的次数。如果较高，它表示服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引（这个值越低越好）。
• Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
• Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
• Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
• Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
• Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

查询 schema_unused_indexes库。

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

新建一张表，

执行查询，

响应数据如下，

type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。

• table：显示这一行的数据是关于哪张表的
• type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
• possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
• key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：MYSQL认为必须检查的用来返回请求数据的行数
• Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：| All | index | range | ref | eq_ref | const,system | null |

• system 表只有一行：system表。这是const连接类型的特殊情况
• const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
• eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
• ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
• range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
• index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
• ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

• Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
• Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
• Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
• Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
• Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
• Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
• Using where 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com