MySQL
一、关系型数据库的特点:
1. 传统关系型数据库中的数据以列和行的方式进行存储
1.1.Innodb存储引擎
Innodb存储引擎支持两种常见的索引数据结构:B+树索引、Hash索引,其中B+树索引是目前关系型数据库系统中最常见、最有效的索引。
1.1.1 B+树索引
B+树索引分为聚簇索引与非聚簇索引。聚簇索引就是按照每张表的主键构造一个B+树,B+树的叶子节点中记录着表中一行记录的所有值。非聚簇索引的叶节点不包含行记录的所有值,只包含索引值和主键的值。
B+树特点:
- 1.B+树是一颗平衡树,每个叶子节点到根节点的路径长度相同,
查找效率较高
。 - 2.B+树的所有关键字都在叶子节点上,因此
(支持)范围查询
时只需要遍历一遍叶子节点即可。 - 3.B+树的叶子节点都按照关键字大小顺序存放,因此可以快速的支持按照关键字大小进行
(支持)排序
。 - 4.B+树的非叶子节点不存储实际数据,因此可以
存储更多的索引数据
。 - 5.B+树的非叶子节点使用指针连接子节点,因此可以快速的
支持范围查询
和倒叙查询
。 - 6.B+树的叶子节点之间通过双向链表连接,方便进行
范围查询
。
B+树优点 & 缺点:
- 在节点分裂和合并时,
IO操作减少
。B+树到叶子节点的大小就是固定的,而且节点的大小一般都会设置为一页的大小,这就使得节点分裂和合并时,IO操作很少,只需要读取和写入一次。 有利于磁盘预读
。由于B+树的节点大小是固定的,因此可以很好的利用磁盘预读的特性,一次性读取多个节点到内存中,可以减少IO操作到次数,提高查询效率。有利于缓存
。B+树到非叶子节点只存储指向子节点的指针,而不存数据,这一可以使得缓存能够容纳更多的索引数据,从而提高缓存的命中率,加快查询速度。- B+树
索引的维护成本比较高
,插入删除数据时需要调整索引结构,这个过程可能会涉及到页分裂和页合并等操作。
为什么不用B树:
B树(B- Tree)和B+树(B+ Tree)是两种常用的树结构,B是Balanced首字母,平衡的意思,MySQL的InnoDB使用的是B+树,MongoDB使用的就是B树存储。B树属于多叉树,又名平衡多路查找树,B树多设计目的是为了减少磁盘访问次数,提高查询性能。
m阶的B树,满足如下条件:
- 每个节点最多只有m个子节点。
- 每个非叶子节点(除了根)具有至少[m/2]子节点。
- 如果根不是叶节点,则根至少有两个子节点。
- 具有K个子节点的非叶节点包含k-1个键。
- 所有叶子都出现在同一水平,没有任何信息(高度一致)。
特点:
- B树是一种多路搜索树,每个节点可以包含多个子节点。相当于二叉搜索树,B树可以存储更多的关键字和子节点,从而降低了树的高度,减少了磁盘访问次数。
- B树通过在插入和删除操作时进行节点的分裂和合并,保持树的平衡状态。这样可以确保树的高度适中保持在可接受的范围内,保证了较快的查询性能。
- B树的节点中的关键字按照升序排序,使得范围查询操作更加高效。通过遍历树中的叶子姐弟哪,可以顺序获取连续的数据。
- B树的关键字和数据项可以存储在叶子节点和非叶子节点。并且每个关键字出现且只出现在一个节点中。
- B树的搜索可能在非叶子节点上结束,他的搜索性能相当于在关键字全集中做二分查找。
区别:
- 1.数据存储位置:在B树中,数据项存储在叶子节点和非叶子节点上,而B+树中,数据项只存储在叶子节点上。非叶子节点只包含键值信息。
- 2.叶子节点指针:B树的叶子节点之间没有指针连接,每个叶子节点独立存储数据项。而B+树的叶子节点通过指针连接成一个连表,可以方便的进行范围查询。
为什么不用红黑树:
红黑树是一种自平衡的二叉查找树。红黑树适用于关联数组、优先队列等。
优点:
保证最坏情况下的性能
:红黑树通过维持树的大致平衡,而不是完美平衡。确保了在插入删除和查找操作中最坏情况下的时间复杂度均为O(log n)。这比普通的二叉搜索树(在最坏情况下可能退化为连表,时间复杂度为O(n)好得多)。自平衡
:每次插入或删除操作后,红黑树通过旋转和重新着色的方法自动维持平衡,无需额外的操作或维护。数据结构简洁
:节点只需要额外存储一个颜色位,因此相比于其他平衡树(如AVL树)来说,内存的额外开销较小。
1.1.2 Hash索引
哈希索引是将索引列的值通过哈希函数计算后得到一个桶的编号,然后将桶内的记录保存在一个链表或者树结构中。
- 哈希索引适用于等值查询,但不适合范围查询和排序操作。
- B+树索引在节点中存储多个键值对,因此可以充分利用磁盘块的空间提高空间利用率,而哈希索引由于需要存储哈希值和指针,因此空间利用率低。
2. 关系型数据库采用结构化查询语言(SQL)对数据进行查询
3. 关系型数据库强调ACID(原子性-Atomicity,一致性-Consistency,隔离性-Isolation,持久性-Durability)
- 原子性:事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。
- 一致性:事务应确保数据库的状态从一个一致状态转变为另一个一致的状态。
- 隔离性:多个事务兵法执行时,一个事务的执行不应影响其他事务。
- 持久性:一个事务一旦提交,他对数据库的修改应该永久保存在数据库。
3.1. 数据库事务:访问并可能操作各种数据项的一个数据库序列,这些操作要么全部执行要么都不执行,由事务开始于事务结束之间执行的全部数据库操作组成。
- 脏读:读到了其他事务还未提交的数据
- 幻读:事务在做范围查询的过程中,有另外一个事务对范围内新增了记录(INSERT),导致范围查询的结果条数不一致。
- 不可重复读:对某数据进行读取的过程中国,有其他事务对数据进行了修改(UPDATE、DELETE),导致第二次读取1的结果不同
3.2 数据库事务的隔离级别:SQL-92定义了4种隔离级别来解决脏读、幻读、不可重复读。
- 串行化(Serializable):最高的隔离级别,解决了幻读的问题。
- 可重复读(Repeatable read):解决了不可重复读的问题,存在幻读的问题。
- 读已提交(Read committed):在一个事务修改数据的过程中,如果事未提交,其他事务不能读该数据,避免了脏读。会存在不可重复读的问题。
- 未提交读(Read uncommitted):最低的隔离级别,一个事务可以读到另一个未提交事务的数据。会存在脏读、幻读、不可重复读的问题。
# MySQL5.X事务隔离级别:REPEATABLE-READ
select @@tx_isolation;
# MySQL8.0事务隔离级别:REPEATABLE-READ
select @@transaction_isolation;
# 查看系统当前隔离级别
select @@global.tx_isolation;
# 开始事务
set autocommit=off;
# 或者
start transaction;
3.3 MySQL 默认隔离级别使用:REPEATABLE-READ
- Oracle 默认使用 Read committed。
- MySQL 默认使用 Repeatable read。
3.3.1 原因:兼容历史上statement模式的bin log,导致主从同步数据不一致的问题
- 在主从复制的MySQL集群下,数据的同步是通过bin log进行的,且早期只有statement模式(记录SQL语句原文),如果使用Read committed或Read uncommitted会有问题。
- 问题:在RC的前提下,事务1->新增,事物2->删除 ,如果两个事务写入bin log,事务1先提交,事务2后提交,bin log先记录事务1的SQL语句,后记录事务2的SQL语句,导致主节点,有新增的数据,从节点在则没有新增的数据。
- 解决:Repeatable read 更新,插入数据时会加锁(GAP锁+临键锁),使得事务1会被卡住,待事务2提交回滚后再执行,同时使用mixed或者row格式的bin log日志
3.3.2 bin log 模式
- statement:记录SQL语句的原文。
- row:记录每个数据更改的具体行的细节,不会导致主从不一致,担是需要记录更多的内容,数据恢复时,时间会更长,磁盘IO和网络IO等资源占用也比较高。
- mixed:将row和statement结合,MySQL会根据SQL情况,自动切换一个合适的格式进行记录。
3.4 MySQL InnoDB 的 REPEATABLE-READ 隔离级别有没有解决幻读
- 答案:通过 间隙锁 + MVCC解决了大部分幻读的问题,彻底解决幻读还是需要使用串行化的隔离级别。
- MVCC 机制:MVCC 为每个数据行维护多个版本,每个版本都有一个可见性标志。当事务开始时,它会获得一个读视图,这个视图决定了事务能看到哪些数据版本。事务在其生命周期内始终看到同一份数据版本,即使有其他事务插入了新的行
3.4.1 MVCC(Multiversion Concurrency Control)
- MVCC 翻译为“多版本并发控制”,和数据库锁一样,是一种并发控制的解决方案,主要用于解决 读-写 并发的情况。
- MVCC 是一种读取数据的技术,它允许多个事务同时读取同一份数据的不同版本。在 REPEATABLE-READ 隔离级别下,事务在其生命周期内看到的数据版本是一致的,也就是说,事务在开始时看到的数据版本在整个事务过程中都不会改变。
3.4.1.1 数据库中对数据的主要操作分别是读和写,在并发的场景下则会出现三种情况:
- “读-读并发”不会出现问题。
- “写-写并发”则通过加锁解决数据的一致性。
- “读-写并发”则通过MVCC的机制解决。
3.4.1.2 当前读和快照读
- MySQL中只有 RC 和 RR 这两种事务才会使用当前读和快照读。
- 快照读:读取的是快照数据,即普通SELECT语句在不加锁的情况下就是快照读。
- 当前读:读取的是最新数据,即加锁的SELECT,或对数据进行行增删改都会进行当前读。
- 在RR中,快照会在事务中第一次SELECT语句执行时生成,只有在本事务中对数据进行更改才会更新快照。
- 在RC中,每次读取都会重新生成一个快照,总是读取行的最新版本。
3.4.1.3 UndoLog
- undo log是MySQL用于回退的日志,在事务没提交之前,MySQL会先记录更新前的数据到undo log日志文件中,当事务回滚时或者数据库崩溃时,可以利用undo log来进行回退。
- undo log“更新前的数据”就是快照读,即UndoLog是MVCC实现的重要手段。
- 如果一条记录在同一时刻可能会有多个事务在执行,那么,undo log会有一条记录的多个快照,那么在这一时刻发生select要进行快照读的时候,要读取哪个快照呢?这样就需要用到另外几个信息了。
3.4.1.4 行记录的隐式字段
在数据库中的每行记录中,除了保存我们自定义的一些字段以外,还有一些重要的隐式字段:
- db_row_id:隐藏主键,如果我们没有给表创建主键,那么会以这个字段来创建聚簇索引。
- db_trx_id:对这条记录做了最新一次修改的事务ID。
- db_roll_ptr:回滚指针,指向这条记录的上一个版本,其实她指向的就是UndoLog中的上一个版本的快照的地址。
- 以上字段,只有在聚簇索引的行记录中才会有,而在普通的二级索引记录中是没有的。
3.4.1.5 Read View
Read View主要用来解决可见性的问题,即他会来告诉我们本次事务应该看到哪个快照,不应该看到哪个快照。
在Mysql中,不同的事务隔离级别,比如我们常见的RR和RC,RR要求在一个事务中,多次读取的结果是保持一致的,而RC则要求每次都要读取到最新的值。
- 在可重复读(Repeatabel Read)级别下,快照读(ReadView)在事务开始时创建一次,并在整个事务期间保持不变。
- 在读已提交(Read Committed)级别下,快照读(ReadView)会在每次查询的时候重新创建,以反映数据库中最新提交更改。
在Read View中有4个重要的属性: - trx_ids: 表示在生成ReadView时当前系统中活跃的读写事务的事务Id列表。
- low_limit_id: 应该分配给下一个事务的Id的值。
- up_limit_id: 未提交的事务中最小的事务Id。
- creator_trx_id: 创建这个Read View 的事务Id。
trx_ids 中包含了low_limit_id和up_limit_id的信息,其实trx_ids = [up_limit_id, low_limit_id]
(但是需要注意,他并不一定连续,只是会包含up_limit_id,并且小于low_limit_id,左闭右开)。
也就是说,在读已提交(Read Committed)级别下,每次读取数据时都会生成一个ReadView,并在其中记录上trx_ids(包含[up_limit_id, low_limit_id])和,creator_trx_id。
eg:如何判断一个事务应该看到哪些快照,不应该看到哪些快照?
假如一个ReadView的内容为:
trx_ids = [5,6,8)
low_limit_id = 8
up_limit_id = 5
creator_trx_id = 7
假设当前事务要读取某一个记录行,该记录行的 db_trx_id 为 trx_id,那么就有以下几种情况:
trx_id < up_limit_id
即小于5的事务,说明这些事务在生成ReadView之前就已经提交了,那么该事务的结果就是可见的。
trx_id > low_limit_id
即大于8的事务,说明该事务在生成ReadView后才生成,所以该事务的结果就是不可见的。
up_limit_id < trx_id < low_limit_id
即大于等于5小于8,则会再拿事务Id和ReadView中的trx_ids进行逐一比较。
a. 如果,事务Id在trx_ids列表中,如6,那么表示在当前事务开启时,这个事务还是活跃的,则这个记录对于当前事务来说不可见。
b. 如果,事务Id不在trx_ids列表中,如7,那么表示在当前事务开启之前,其他事务对数据进行修改并提交了,则这个记录对当前事务是可见的。
c. 如果,trx_id = creator_trx_id ,那么表示这个记录对当前事务是可见(都在当前的同一事务中)。
总结:一个事务,能看到的是在他开始之前就已经提交的事务的结果,而未提交的结果都是不可见的。
所以,当读取一条记录的时候,经过上面的判断,发现记录对当前事务可见,那么就直接返回就行了。那么如果不可见怎么办?就需要用到undo log了,即当数据的事务Id不符合Read View规则时,就需要从 undo log 里面获取数据的历史快照,然后数据快照的事务Id再来和Read View 进行可见性比较,如果找到一条快照则返回,找不到则返回空。
3.4.1.6 二级索引在索引覆盖时如何使用MVCC
储备知识:
- 1.行记录的隐式字段
- 2.在MVCC中通过隐藏字段中的
db_roll_ptr
(回滚指针)来构建版本链,db_trx_id
(对这条记录做了最新一次修改的事务ID)也是一个重要的用来判断快照可见性的字段。 - 3.在Innodb的聚簇索引(主键索引)中,叶子节点上保存的是整行的记录。非聚簇索引(二级索引)中,叶子节点上只保存了主键的信息。
- 4.在一条查询语句中,如果能用到索引覆盖,则会直接使用二级索引进行检索,不会回表。
问题:
- 如果某个查询语句的查询字段都包含在二级索引中,即会走覆盖索引,不需要回表去读取聚簇索引的页记录。但是,版本链的头节点在聚簇索引中,不在二级索引中,通过二级索引的记录无法直接找到版本链。在这种情况下如何使用MVCC?
MySQL官网描述:https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-transaction-management.html(如果发行二级索引页有一个 page_max_trx_id(表示修改该页的最大事务Id) 太新,或者如果二级索引中的记录被删除标记,InnoDB可能需要使用聚簇索引来查找记录),也就是说,索引覆盖并不是用到了联合索引就一定会去走,在括号中的情况下还是会去回表,通过覆盖索引进行查询。
总结:
在二级索引中,用一个额外的名为page_max_trx_id的变量来记录表示修改过该页的最大事务id。如果当前查询的到的readVidew的up_limit_id > page_max_trx_id
,说明在创建readVidew时,最后一次更新二级索引的事务已经提交了,意味着二级索引里的提交对于当前查询
都是可见的。这时,如果这个二级索引的记录没有被删除,那么就可以直接走索引覆盖查询。否则,就意味着数据可能被修改了,不能直接查询,需要回表,通过聚簇索引进行查询。同时使用聚簇索引时,叶子节点行记录中设计包含了版本链就可以用到MVCC了。
3.4.2 解决的幻读问题:
- RR中,通过MVCC机制,解决了快照读的幻读问题,RR中的快照读只有第一次会进行数据查询,后面都是直接读取快照,所以不会发生幻读。
- RR中,通过间隙锁解决了部分当前读的幻读问题,通过增加间隙锁将记录之间的间隙锁住,避免新的数据插入。
3.4.3 未解决的幻读问题:
如果两个事务,事务1先进行select操作(快照读)
select -> A,B
,然后事务2插入了一条记录并提交事务2add -> C
,再在事务1中进行update新插入的这条记录(当前读操作)是可以更新成功的update -> C
,这就是发生了幻读。如果两个事务,事务1先进行select操作(快照读)
select -> A,B
,然后事务2插入了一条记录并提交事务2add -> C
,在事务1中进行了select for update(当前读)之后select for update -> A,B,C
,再进行select(快照读)也会发生幻读select -> A,B,C
。如果两个事务,事务1先进行select操作(快照读)
SELECT * FROM users WHERE age = 25; -> A,B
,然后事务2插入一条记录并提交事物2(age:唯一索引)
,再在事务1中进行select操作select -> A,B,C
,因为在唯一性索引上插入的新行不会触发间隙锁。
4. 关系型数据库十分强调数据的一致性,并为此降低读写性能付出了巨大的代价
4.1 InnoDB的锁机制
根据不同的锁粒度:
- 行级锁:根据锁粒度区分:记录锁(Record Lock 锁索引记录)、间隙锁(Gap Lock 锁索引记录直接的间隙)、间隙记录锁(Next-Key Lock 同时锁索引记录和间隙,范围左开右闭)、
- 页级锁(InnoDB没有):
- 表级锁:对表加锁,包含:意向锁、AUTO-INC锁、字典锁、表级排他&共享锁、
- 全局锁:影响整个MySQL实例的锁。
FLUSH TABLES WITH READ LOCK
这个命令会锁定整个数据库实例所有的表(全局读锁定),主要用于全局备份等操作,执行命令后库实例中的所有表都被锁定为只读。
4.1.1 共享锁和排他锁
Innodb中的锁在锁的级别上一般分为两种,一种是共享锁(S锁),一种是排他锁(X锁)
- 共享锁又称读锁,是读取操作创建的锁。其他用户可以并发读取数据,但任何事物都不能对数据进行修改(获取数据上的排他锁),直到已释放所有共享锁
- 排他锁又称写锁,如果事务T对数据A加上排他锁后,则其他事务不能再对A加任何类型的锁。获得排他锁的事务既能读取数据,又能修改数据。
- 表级排他&共享锁:
LOCK TABLES table READ
:添加表级共享锁,LOCK TABLES table WRITE
:添加表级排他锁。
4.1.2 意向锁(Intention Locks)
当多个事务想要访问一个共享资源时,如果每个事务都直接请求获取锁,那么就可能会导致互相阻塞,甚至导致死锁。
为了解决这个问题,MySQL引入了意向锁机制。所以意向锁上数据库管理系统中用于实现锁协议的一种锁机制,旨在处理不同锁力度(如行锁和表锁)之间的并发性问题。(相同锁粒度(如多个行级锁)之间的并发性问题通过行级互斥锁解决。)
- 意向排他锁(IX锁),表示事务打算在资源上设置排他锁。这表示事务计划修改,并不希望在读取时有其他事务同时设置共享排他锁。
- 意向共享锁(IS锁),表示事务打算在资源上设置共享锁。这通常用于表示事务计划读取资源,并不希望在读取事有其他事务设置排他锁。
- 插入意向锁是一种由插入操作在行插入之前设置的间隙锁。这种锁表明了插入的意图,以这样一种方式,如果多个事务插入到同一索引间隙中但不在间隙内到相同位置插入,则他们不需要互相等待。
注意: - 意向锁是一个表级锁(作用范围是针对整个表,而不是针对表中的具体行或页),并且他会在触发意向锁的事务提交或者回滚后释放。
- 意向锁本身不锁定资源,而是未来通知其他事务,以防止它们在资源上设置不兼容的锁。
- 意向锁并不是直接由用户请求的,而是由MySQL管理的。
4.1.3 记录锁(Record Lock)
记录锁是加在索引记录上的锁,是一个典型的行级锁,如果没有索引InnoDB回创建一个隐藏的聚簇索引(隐藏主键row_id),并使用这个索引进行记录锁定。
4.1.4 间隙锁(Gap Lock)
间隙锁是锁索引记录之间的间隙。
4.1.5 间隙记录锁(Next-Key Lock)
是Record Lock和Gap Lock的组合,同时锁索引记录和间隙,他的范围是左开右闭。
注意 MySQL加锁原则为,两个“原则”、两个“优化”和一个“bug”:
- 原则1:加锁的基本单位是next-key lock。是一个前开后闭区间。
- 原则2:查找过程中访问到的对象才会加锁。
- 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
- 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
- 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
4.1.6 字典锁(MetaData Locks)
对表结构改变的动作,如ALTER、DROP,他们加锁的过程添加的MDL锁,即字典锁,也叫元数据锁。主要目的是保护数据字典中的元数据不被并发更改。MDL 锁是由MySQL自动管理的,不需要应用程序显式地获取或释放。
4.1.6.1 DML锁的类型:
- MDL_EXCLUSIVE (EX):排他字典锁,用于写入操作,阻止所有其他类型的 MDL 锁。
- MDL_SHARED (SH):共享字典锁,用于读取操作,允许其他共享锁,但阻止独占锁。
- MDL_SHARED_UPGRADABLE (SX):可升级共享锁,用于读取操作,允许其他共享锁,但阻止独占锁。这种锁可以升级为独占锁。
- MDL_INTENTION_EXCLUSIVE (IX):意向独占锁,表示事务希望在将来获取独占锁。
5. 关系型数据库的数据通常存在硬盘中
5.1. MySQL的数据存储也可以基于内存,即MySQL的内存表技术。它允许将数据和索引存储在内存中,从而提高检索速度和修改数据的效率。创建内存表于创建普通表一样,使用CREATE TABLE
语句,但是需要将存储引擎设置为:ENGINE=MEMORY
# 查看mysql当前使用什么存储引擎
show engines;
# 查看mysql当前默认存储引擎
show variables like '%storage_engine%';
# 查看表存储引擎
show create table v_task;
5.2. 数据库存储引擎:数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可以控制访问权限并快速处理事务。
二、注意事项
1.多表join
1.1. join的效率比较低,MySQL使用了嵌套循环(Nested-Loop Join)的方式来实现关联查询,简单来说就是通过两层循环,第一张表做外循环,第二张表做内循环,外循环每一条记录和内循环中的记录作比较符合就输出,eg:2张表join复杂度为O(n^2),3张表join复杂度为O(n^3)
1.1.1 嵌套循环算法(Nested-Loop Join)
- simple nested loop,他的做法简单粗暴,就是全量扫描连接两张表进行数据的两两对比,所以他的复杂度可以认为是N*M
- index nested loop,当Inner Loop的表用到字段有索引的话,可以用到索引进行查询数据,因为索引是B+树的,复杂度可以近似认为是N*logM
- block nested loop,其实是引入了一个Buffer,会提前把外循环的一部分结果提前放到JOIN BUFFER中,然后内循环的每一行都和整个buffer的数据作比较。虽然比较次数还是N*M,但是因为join buffer是基于内存的,所以效率高很多。
1.1.2 不用join如何关联查询
- 在内存中自己做关联,先从数据库吧数据查出来后,代码中再进行二次查询再进行关联。
- 数据冗余,把一些重要的数据在表中做冗余,就可以避免关联查询
- 宽表,就是基于一定的join关系,吧数据库中多张表的数据打平做成一张大的宽表,可以同步到ES或者直接在数据库汇总查询。