关系型数据库,是指采用了关系模型来组织数据的数据库,其以行和列的形式存储数据,以便于用户理解,关系型数据库这一系列的行和列被称为表,一组表组成了数据库。用户通过查询来检索数据库中的数据,而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型,而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。
一个关系型数据库是由两部分组成,一部分是RDBMS
,另一部分是存储系统
。
RDBMS的全拼是Relational Database Management System,从字面上可以理解为关系数据库管理系统。可以简单理解为为是关系型数据库中的管理系统,也可以认为是一个程序实例,用来跟用户进行交互和数据的操作,将物理存储映射成逻辑结构。
存储系统即文件系统,即将数据以文件的形式持久化存储进硬盘(SSD,机械硬盘)中。
设计一个关系型数据库,其实主要是设计RDBMS。可以将RDBMS拆分成以下几个子模块。
1.存储管理模块
:对数据的格式和文件分割进行管理,把物理数据通过逻辑结构标示出来。在这一模块中,还需要对存储效能进行优化,因为磁盘的IO速度很低,不能在磁盘上做数据的处理,需要将数据读取到内存当中进行处理,为了提高效率就需要尽量减少磁盘的IO次数。在进行磁盘IO的时候,读取一行数据和多行数据所消耗的时间是差不多的,所以行就失去了意义。在关系型数据库中,一般将页和块作为逻辑存储单位,块和页中存放多行数据,每次IO加载多个块到内存当中来提高效率。
2.缓存模块
:为了更好更快地对数据文件进行操作,需要引入缓存机制,将取出的数据块放入缓存中,下次程序再次使用就可以直接从内存中返回而不是对磁盘进行IO。缓存管理有许多种算法, 有一种思想是一旦某行数据被访问了,那么它附近的数据也极有可能在最近的访问中被访问到。
3.SQL解析模块
:在外界对数据库进行操作时,是需要使用SQL语句的,SQL解析模块负责将SQL语句解析编译成机器能识别的语言,并对数据进行操作。如果想要提升SQL效率,可以将SQL语句编译之后存到缓存当中,方便下次使用的时候直接解析即可。
4.日志管理模块
:对数据库的操作需要记录下来,方便做数据库的主从同步和数据恢复。
5.权限划分模块
:需要给用户数据管理的私密空间,按照权限划分,给不同用户不同的操作级别。
6.容灾机制模块
:数据库挂了需要恢复,怎么恢复,恢复到什么程度,需要设计。
7.索引模块
:为了提升查询数据的速度,需要引入索引模块。
8.锁管理模块
:为了让数据库支持并发,需要引入锁管理模块。
1.4.1 为什么要使用索引? 如果说没有索引的话,我们从数据库中查询数据需要对数据库进行全表扫描来查询所需的数据,将整张表的数据全部或者分批次加载到内存当中,找到我们需要的数据并返回。这种方式非常的慢,数据量小还可以,数据量大之后就不能用了,所以我们需要避免全表扫描的情况,引入索引机制。 索引的灵感来自于字典,在字典的检索目录中,我们将文字的关键信息组织起来,比如偏旁和部首,查询的时候根据偏旁或者部首找到对应的页码,就快速找到了我们想要的数据。数据库也一样,将关键信息放在索引中,快速找到数据所在的内存地址来获取数据。 1.4.2 什么样的信息适合作为索引? 能把该记录限定到一定范围内的字段,就适合用来作为索引,比如主键、唯一建和其他普通键都可以。索引的设置也需要进行相应的考虑,不是所有的字段作为索引都很高效。 1.4.3 索引的数据结构选择 有了关键字还不足以成为一个索引系统,关键是利用什么样的逻辑结构将关键字组织起来,让我们的检索变得高效。这就需要选择一种合适的数据结构了。具体的数据结构论述将在章节2中进行阐述。
二分搜索树是一种常用的树状数据结构,又称为BST。在树形态比较平衡的情况下,BST的搜索效率可以达到O(logN)级别,搜索的时间和树的层数成正比。所以我们可以利用BST来为数据库建立索引模块。 具体的实现就是,可以将索引的关键字信息挂到BST上,根据大小关系在BST中进行搜索,BST的每个节点存储着关键字对应数据的物理内存地址,搜索到所需关键字之后,根据指针去内存中拿到整个数据。也可以将数据和索引一起挂在BST的节点上,直接返回即可。 BST虽然简单,查询效率也高,但是有一个致命的问题是BST有可能会退化成一条链表,所有的节点都挂在根结点的右子树或者全部挂在左子树上,此时查询效率就会退化成O(N)级别,和全表扫描一样了。所以我们需要更稳定的索引结构,此时AVL树和红黑树就跳进了我们的脑海。
AVL树是一种平衡二叉搜索树,通过旋转的操作来保持BST的左右子树高度差不超过1,不会出现极端的链表化的情况。红黑树也是对BST的另外一种变种数据结构,在节点上增加了颜色限制,同时也是靠旋转来调整结构。 虽然这两种数据结构都避免了BST的链表化,保证了稳定性,但是还存在着一个巨大的问题,那就是AVL和红黑树的旋转是对整棵树的操作,需要将树整体加载到内存当中,部分加载的情况是无法旋转的,所以如果进行大规模数据存储的时候,内存是装不下整颗树的,此时就会造成频繁的磁盘IO。所以这就是为什么数据库最后没有选择这两种所数据结构的原因。
B-Tree有两种写法:B Tree和B-Tree,所以你平时看到的B-树和B树其实是一个东西。B树即平衡多路查找树,其结构如下图:
在B树中,叶子节点和非叶子节点都存储着关键字信息和数据,查询过程是靠大小比较来进行。比如我们要在树中查数字0029,首先进入根结点发现0029大于根结点的0017,就进入右边的指针指向的节点,发现右子节点的范围是0028-0048,而0029正好在其中,就进入范围中间的指针所指向的节点,找到0029并返回。 一棵 m 阶的 B 树,需要满足下列条件:
在AVL或者红黑树中,插入或者删除后不满足条件需要对树进行旋转。而B树维护自己的平衡状态是依靠分裂以及合并的操作,这种操作并不需要将整棵树一次性加载进内存当中,B树的查找过程是一个顺指针查找节点和节点中查找关键字的交叉过程。 即使B树的查找很方便和稳定,但是有一个缺陷是如果我们需要遍历数据,就需要从根结点开始往下跨层进行遍历,需要不断地在内外存进行数据交互,磁盘的IO次数也会增加,这是比较影响性能的,所以就引出了B树的变种——B+树。
B+树是B树的一个升级版,相对于B树来说B+树更充分的利用了节点的空间,让查询速度更加稳定,其速度完全接近于二分法查找。
B+树叶子节点保存了父节点的所有关键字记录的指针,所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样。
B+树的百度百科示意图如下:
在B树的基础上B+树将所有的数据都放在了叶子节点中,并使用链表进行连接。有以下几个提升:
和HashMap快速获取数据的思想一样,我们可以利用Hash来创建数据库的索引模块,可以将索引关键字进行Hash运算,放进不同的桶中,在桶的后面挂上该索引的数据。
此时,我们需要查询某个索引关键字,直接找到所在的桶取出即可,其思想和HashMap类似,但是这种索引有很大的局限性:
SQL数据库主要指关系型数据库,比如MySQL,SQL Server等。NoSQL数据库泛指非关系型数据库,比如MongoDB,Redis等。 SQL数据存在特定结构的表中,而NoSQL则更加灵活和可扩展,存储方式可以省是JSON文档、哈希表或者其他方式。这就导致了SQL经常需要进行遍历操作,而NoSQL可以将索引和结果数据聚合在一起。比如在MySQL中,我们有两个表,一个存储学生信息,一个表存储班级,我们想要获取1班中有几个学生,就需要先在班级表中查到1班的id,然后去学生表中遍历班级id为1班的数量。而MongoDB中,可以在一个集合中,将1班的所有同学聚合在班级表中,一次查询直接返回所有学生数即可。 根据之前介绍的B数和B+树的区别,不难想到,NoSQL数据库由于索引和数据聚合在一起,而且一般都是单一查询,所以使用B树作为存储结构更为合适,因为B树就是索引和数据聚合在一起的结构。
密集索引如下图所示:
不仅存储了索引值,还包含了该索引其他列的内容
。稀疏索引如下图所示:
仅保存了索引键位信息以及物理地址
。MySQL中的索引结构和存储引擎有关
稀疏索引
。InnoDB引擎有且仅有一个密集索引
。最左匹配原则都是针对联合索引来说的,所以首先要了解一下联合索引。如下图所示,假设有一个表,有三列:col1, col2, col3,然后创建(col3, col2)联合索引。其表结构和索引结构如下图所示:
最左匹配原则
:MySQL会从最左索引开始,一直向右匹配索引,知道遇到范围查询(>, <, between, like)就停止匹配。
(a, b, c, d)
,那么必须要有a索引,才可能匹配该联合索引,如 a = 3 and b = 4 and c = 5 and d = 6
是完全匹配上该联合索引的,如果没有a索引,如b = 4 and c = 5 and d = 6
是无法使用该联合索引的。这一点从联合索引的介绍中可以发现,建立联合索引去查询时,第一位索引是绝对有序的,如果不提供第一索引位信息,是无法进行后续查询的。(a, b, c, d)
,如果使用 a = 3 and b = 4 and c > 5 and d = 6
,其中c索引位使用了范围查询,那么索引匹配只到c为止,d是是用不上索引的,此时索引位为(a, b, c)
。其原理也很容易理解,由于d索引是在c索引确定后在其基础上进行排序的,如果c索引是一个范围,那么d索引无法利用索引排序进行查询。in
和=
是可以乱序的,比如b = 3 and a = 4 and c = 5 and d = 6
可以匹配联合索引(a, b, c, d)
,这是因为MySQL的查询优化器会自动优化成索引可以识别的形式,也就是a = 3 and b = 4 and c = 5 and d = 6
。这个问题是一种经验问题,具体场景需要具体分析,这里可以抽象出大致的优化思路:
set global slow_query_log = on;
语句打开慢日志查询。
1.2 使用set global long_query_time = ?;
语句设定定义为慢查询SQL的时间,其中?
为自定义数字,单位为秒。
1.3 使用show variables like '%quer%';
和show status like '%slow_queries%';
来查询慢查询的关键信息
explain
关键字对其进行分析
2.1 explain
的SQL语句并不会真正的运行,只是分析
2.2 explain的结果中,比较关键的两个字段,一个是type
,一个是extra
。
数据库中锁的分类按照不同的划分方式,可以分为以下几种:
表级锁
、行级锁
和页级锁
。 共享锁
、排它锁
。 自动锁
、显示锁
。 DML锁
、DDL锁
。乐观锁
、悲观锁
。 表级锁
,不支持行级锁。 行级锁
,也支持表级锁。 数据库的四大特性为ACID,分别如下:
原子性
(Atomic):指数据库事务作为一个整体被执行,要么全部执行,要么全部不执行。一致性
(Consistency):保证数据库状态从一个一致状态转变为另一个一致状态。隔离性
(Isolation):多个事务并发执行时,一个事务的执行不应该影响其他事物的执行。持久性
(Durability):一个事务一但提交,对数据库的修改应当永久保存。MySQL数据库事务的四大隔离级别如下:
读未提交
(Read Uncommitted):该隔离级别下,如果一个事务已经开始写数据,则不允许其他事务同时进行写操作,但允许其他事务读取正在写操作的数据。 读未提交
隔离级别下,可能会出现脏读
的问题。脏读是指事务A读取了事务B已修改未提交的数据,而事务B随后回滚,就导致事务A读取到了错误的数据,成为脏读。读已提交
(Read Committed):该隔离级别下,读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其它事务访问正在操作行数据。 读已提交
隔离级别下,不会出现脏读问题,但是会出现不可重复读
问题。不可重复读是由于读取数据的事务允许其他事务继续访问该行数据,一个事务A在自己没有更新数据库数据的情况下,同一种查询操作执行两次或者多次获取到的结果不一致,因为别的事务B、C…在A查询的同时一直在对数据进行更新,并提交了事务。可重复读
(Repeatable Read):该隔离级别下,读取数据的事务将会禁止写事务,但允许读事务,写事务则禁止其它任何事务。 可重复读
隔离级别下,不会出现脏读和不可重复读问题,但是会出现幻读
问题。幻读有很多种解读说法,按照MySQL官方文档中的法,幻读是指读会读到上一次没有返回的数据,看起来像是幻影一般。也就是说事务A读取到了一个结果集,然后事务B向表中新增了一条数据,事务A再次读取结果集就发现数量增加了一条,这就是幻读。序列化
(Serializable):该隔离级别下,提供严格的事务隔离,将禁止并发执行,要求事务序列化执行,必须一个接一个执行。 序列化
隔离级别下,不会出现脏读、不可重复读和幻读问题,因为严格串行执行,虽然可靠性高,但是会降低并发操作的性能。可重复读
。上一节介绍中,在RR(可重复读)隔离级别下,会出现幻读的情况,MySQL也考虑到了这种情况并且给出了解决办法来尽量避免幻读现象的产生,那么到底是怎么解决的呢?首先是通过MVCC
机制,利用快照读来一定程度上避免幻读。
MVCC
:多版本控制(Multiversion Concurrency Control),是一种提高并发的技术。MVCC解决幻读的方法就是利用undo log日志保存数据的多个版本,然后利用事务的id来创建快照,同一个事务中,事务只能看到其它事务在创建快照之前已经提交的修改(不管查询几次)和该事物本身提交的修改。当前读
:指读出的数据是数据库中的最新数据,加了锁的增删改查都是当前读,如update
,delete
,insert
和显式加锁的select
(select ... lock in share mode,select ... for update
)。 update
,delete
,insert
也是当前读呢?明明是写操作啊。以update为例,在其更新前,实际上会有一个当前读的操作,将数据的最新版本读取出来并进行修改,修改后再更新回数据库中。
快照读
:指读出的数据是创建快照前的最新数据,普通的查询语句为快照读:select
。快照读
的原理 DB_TRX_ID
:记录最近一次对本行数据进行修改(写操作)的事务ID。DB_ROLL_PTR
:一个回滚指针,指向上一次当前数据行的修改undo log信息。DB_ROW_ID
:随着新的数据行插入而单调递增的一个ID信息。(当InnoDB引擎没有指定主键并且没有唯一非空键的时候,就会调用这个ID信息作为自增聚簇索引键,这个字段和快照读的关系不大。)DB_TRX_ID
与Read View中的一些变量进行比较,然后只展现满足条件的数据。不能完全保证不出现幻读
,比如A事务在开启快照读之后,B事务进行了写操作插入了一个新行,然后A事务进行了update操作,此时A事务的update操作覆盖到了B事务刚插入的新行,那么A事务再次快照读就会读到这个新行,产生了幻读。MySQL在RR级别下彻底解决幻读的方法其实还是加锁,RR级别下的当前读,利用了next-key锁,即行锁+gap锁来避免了幻读,加next-key锁需要显示进行调用,即使用select ... lock in share mode,select ... for update
语法进行上锁。
在上图所示的表中,我们通过select from tb1 where id = 9 for update
这个语句进行当前读的时候,由于id是非唯一键,为了防止幻读就会对如图所示的行间隙加gap锁。在当前事务未结束之前,其他事务不允许向间隙中添加数据。
此时当我们where id = 3 and id = 4完全命中时,只会加行锁,因为是唯一索引,其他事务无法添加id = 3或者id = 4的新纪录,所以不会出现幻读情况,就没有必要使用gap锁。
数据库表的每一列都是不可分割的原子数据项
。比如下图的表结构就不满足第一范式,因为列中的数据还可以再分隔:
修改成下表就满足了第一范式:
数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关
(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。如下图表结构所示,由于一个订单号中可能包含几种产品号,所以主键为订单号和产品号联合组成,但是发现表中的订单金额和订单时间仅和订单号相关,与产品号无关,这就违反了第二范式。
此时需要对表进行拆分,拆分成下面两张表结构就满足了第二范式:
拆分成下面两张表结构,就满足了第三范式:
参考:深入理解索引和AVL树、B-树、B+树的关系 平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了