MYSQL学习笔记

最新推荐文章于 2024-09-17 12:26:00 发布

爱学习的刘先生

最新推荐文章于 2024-09-17 12:26:00 发布

阅读量102

点赞数

文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/qq_52167794/article/details/132479250

版权

MYSQL

MYSQL基础

MYSQL定义

MySQL 是一种开源的关系型数据库管理系统（RDBMS），广泛用于各种应用程序的数据存储和管理。MySQL 以其高性能、可靠性和易用性而闻名，它提供了许多功能，使其成为许多 Web 应用、企业应用和嵌入式应用的首选数据库选择。

MYSQL三大范式

第一范式

数据库表中的每个列都包含原子性的值：即每个单元格只能包含一个值，而不是一个集合、数组或其他嵌套结构

第二范式

非主键列完全依赖于表的全部主键：表中的每个非主键列都应该和主键具有完全函数依赖关系。

第三范式

消除非主键列之间的传递依赖关系：这意味着表中的每个非主键列应该与主键直接相关，而不是通过其他非主键列间接相关。

四大存储引擎

InnoDB

当前MYSQL默认使用的存储引擎，支持事务处理，支持高并发，支持表级锁、行级锁。

默认支持的索引类型：B+树、自适应Hash索引

MyISAM

不支持事务处理与行级锁，适用于读写密集的操作

Memory

将数据存储在内存中，数据查询效率高

使用的索引类型：hash

MYSQL索引

当前MYSQL默认的存储引擎InnoDB使用的索引数据结构为B+树。

为什么使用B+树？

B+树是一颗多叉有序树，他的非叶子节点只存储key值，叶子结点存储key值与value值。叶子节点之间使用类似双向链表的结构，便于范围与顺序查询。

B+树存储特点：局部性原理

时间局部性

之前被访问过的数据有可能很快被再次访问
空间局部性

数据和程序都有聚集成群的特点，具备某一特点的数据，经常聚集存储

B+树与其他数据结构的区别

B+树与Hash

如果使用Hash作为索引的数据结构的话，需要设计一个特别优秀的Hash算法，避免多次数的hash碰撞，并且Hash浪费空间，而空间十分宝贵。Hash的查找速率也许很快，但是Hash不支持范围查找。

B+树与BST、AVL、红黑树

不论是BST、AVL、还是红黑树，这些数都有一个普遍的缺点，那就是只能有两个分叉，在数据量变得十分庞大的时候，二叉树的层级就会变得很高，这也导致io次数变多，自然查询效率就不如B+树

补充：红黑树的特征

节点颜色： 每个节点要么是红色，要么是黑色。
根节点颜色： 根节点是黑色的。
叶子节点（NIL 节点）颜色： 叶子节点（也称为 NIL 节点或外部节点）都是黑色的。注意，这里的叶子节点不是指传统意义上的叶子节点，而是代表树的末端。
红节点特性： 红色节点的两个子节点都是黑色的（没有连续的红色节点）。
黑高度平衡： 从任意节点到其每个叶子节点的所有路径都包含相同数目的黑色节点，这个数目称为“黑高度”。

B+树与B树

B树也是多叉有序树，但是B+树相对于B树的优点是，只在叶子节点存储数据，并且叶子节点之间是双向链表。

B+树索引的类型

聚集索引

InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。

聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。

辅助 / 二级索引

使用表的其他非主键列来构造B+树，对于二级索引，叶子节点并不存储所有的数据，只存储了key-value值，此外还有一个bookmark标签，标签中存储了对应的主键值。通过这个书签值也可以去找到完整的行数据。

但是，如果使用二级索引的话，需要查询非组成该B+树的其他属性值的话，就需要依靠二级索引bookmark存储的主键值去聚集索引中查询全部的数据，这操作就导致了回表。

联合 / 复合索引

前面我们对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个，但实践工作中构建索引的完全可以是多个字段。所以，将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引。

对于联合索引来说，会优先根据创建索引时的属性顺序对数据进行排序。

覆盖索引

对于二级索引来说，既然只使用一列不能够满足查询条件，需要通过回表操作去查询完整的数据，那么是不是就可以使用多个列组合起来当作一个B+树，当然在这颗B+树上的叶子节点依旧有存储主键值。当我们需要查询的数据在联合索引中都存在时，就不需要使用回表操作了。

使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。所以记住，覆盖索引并不是索引类型的一种。

自适应Hash索引

自适应hash索引的使用场景是当同一组数据多次反复的查询，这时候每一次都通过B+树来进行查询成本就有点高了。而hash索引的优点就是，查询效率高，基本上只需要一次就能够定位到在哪里。这个hash索引并不用我们自己去创建，是InnoDB内部会有一个机制，这个机制会把访问次数频繁的数据认定为热点数据，之后会内部生成一个hash索引。之后再次访问就会直接通过hash索引来查找数据。

索引失效场景

查询条件符合最左前缀原则
对查询的字段不使用函数操作
避免使用OR和NOT操作
避免在索引上使用类型转换

索引的缺点

索引需要占用空间
使用索引之后，对数据的修改插入效率有一定影响
错误地创建过多的索引可能会导致性能下降，因为查询优化器可能会错误地选择不适当的索引。

MYSQL事务

事务基础知识

什么是事务？

事务是指一组数据库操作，这组操作要么全部执行，要么全部回滚到初始状态。

MySQL中使用BEGIN、COMMIT和ROLLBACK语句来控制事务的开始、提交和回滚。

事物的保存点

在执行事务的时候，我们可以在任意一个时刻埋下一个保存点，之后如果发现某条语句有点问题，可以通过保存点回退到指定的某个版本。

事物的隐式提交

即使是开启了手动提交，在执行DDL语句时，仍会自动帮我们提交事务。

MYSQL事务四大特性

A：原子性，每个事务都是看作一组原子操作，要么全部执行成功，要么全部执行不成功。

C：一致性，在事务执行完毕之后，数据库由一个一致状态到达另一个一致状态。

I：隔离性，每个事务之间是隔离的，互相不知道对方的存在

D：持久性，事务一旦提交，对数据库的更改将永久保存。

MYSQL如何实现事务的

MYSQL如何实现事务主要就是MYSQL中的InnoDB引擎如何保证事物的ACID。

对于原子性，在MYSQL的InnoDB中维护了一张UNDO_LOG表，对于事务中的数据库操作都会有一条对应的记录写入表中，如果事务执行失败了，MYSQL就会通过UNDO_LOG表中的数据将数据库数据恢复。
对于一致性，在事务执行成功之后，数据库会由一个状态转移到另一个一致的状态
对于隔离性，MYSQL中提供了四种隔离级别，读未提交，不能解决任何问题、读已提交，能够解决脏读问题、可重复读，能够解决脏读，不可重复读问题、串行化能够解决脏读、不可重复读、幻读。
对于持久性，MYSQL的InnoDB中维护了一张REDO_LOG表，存储数据库数据变动信息，即使是数据库宕机也能够通过REDO_LOG表中的数据恢复过来。

MVCC

全称Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。
同一行数据平时发生读写请求时，会上锁阻塞住。但MVCC用更好的方式去处理读—写请求，做到在发生读—写请求冲突时不用加锁。
这个读是指的快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。
那它到底是怎么做到读—写不用加锁的，快照读和当前读是指什么？我们后面都会学到。

MVCC的原理

MVCC的版本控制原理是通过MYSQL中InnoDB维护的UNDO_LOG日志以及READVIEW实现的。其中READVIEW中维护了四个核心属性：m_ids、min_trx_id、max_trx_id、creator_trx_id

对于使用InnoDB引擎的，维护了两个隐藏的列，一个是记录当前事务ID、一个是指向当前记录的指针，后续的这两个列将会对InnoDB实现实现版本控制起到很大的作用

在这里插入图片描述

MVCC能否避免脏读

MVCC能够避免脏读问题出现，具体实现原理：

在InnoDB使用READ COMMITTED隔离级别时，在每一次的select操作时都会创建一个独立的READVIEW

在这里插入图片描述

当在事务80提交之前查询READVIEW时，会发现当前有两个活跃的事务，分辨是80、100，所以m_ids的范围为[80,100]，min_trx_id = 80，max_trx_id = 101。此时版本链中的数据为陈思宇最新，在查询到陈思宇时，会对比他的Trx_id，发现80在[80,100]之间，证明此时事务还未提交，不能直接返回，故通过roll_point去查找下一个，刘滔也是类似，在查询到刘思陈时发现Trx_id为60，不在[80,100]范围内，故得出该条记录是已经提交了的，所以最后返回的数据就是刘思陈。

总结就是：所以有了这种机制，就不会发生脏读问题！因为会去判断活跃版本，必须是不在活跃版本的才能用，不可能读到没有 commit的记录。

MVCC能否避免不可重复读

注意：当事务80提交之后，再次进行查询的话，读已提交会重新再次生成一张READVIEW，此时的m_ids就是[120]，min_trx_id = 120，max_trx_id = 121。

当InnoDB采用可重复读隔离级别时，就能够解决不可重复读问题，具体实现如下：

在采用可重复读隔离级别时，InnoDB只会在第一次查询语句生成一张READVIEW，在之后的查询中直接复用该READVIEW，这样就保证了一定会拿到最原始的版本。

MVCC能否避免幻读

网上说的是不能解决，我的观点是解决了，但没有完全解决，在某些情况下还是会出现幻读。

MVCC小结

从上边的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了，从而基本上可以避免幻读现象（就是第一次读如果ReadView是空的情况中的某些情况则避免不了）。

另外，所谓的MVCC只是在我们进行普通的SEELCT查询时才生效，截止到目前我们所见的所有SELECT语句都算是普通的查询，至于什么是个不普通的查询，后面马上就会讲到（锁定读）。

MYSQL锁

为什么要使用锁？MVCC不是已经解决了脏读，不可重复读的问题吗？

MVCC只是在进行普通的select查询才有效。
MVCC 并不能完全消除死锁问题。死锁是一种情况，其中多个线程互相等待对方释放锁，从而导致所有线程都无法继续执行。在某些情况下，由于复杂的交互关系，死锁仍然可能发生。锁机制中的一些技术，如超时和死锁检测，可以用来解决这个问题。
MVCC 虽然可以提供更好的并发性能，但有时候在特定的场景下，简单的锁机制可能更高效。MVCC 需要维护多个数据版本和相关的时间戳，这可能会引入一些开销。

目前常用的解决事务并发方式有两种，一种是读操作使用MVCC，写操作使用锁，另一种是读写都使用锁。一般情况下肯定是第一种方式效率更高，但是还是有部分特殊情况要求必须使用第二种方式。

方案一：读操作MVCC，写操作进行加锁

事务利用MVCC进行的读取操作称之为一致性读，或者一致性无锁读，也称之为快照读，但是往往读取的是历史版本数据。所有普通的SELECT语句（plain SELECT）在READ COMMITTED、REPEATABLE READ隔离级别下都算是一致性读。

一致性读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。

很明显，采用MVCC方式的话，读-写操作彼此并不冲突，性能更高，采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些情况下，要求必须采用加锁的方式执行。

方案二：读、写操作都采用加锁的方式

适用场景：

业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本，

比方在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候也就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行。

脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录，如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。

不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。

幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录，我们把新插入的那些记录称之为幻影记录。采用加锁的方式解决幻读问题就有不太容易了，因为当前事务在第一次读取记录时那些幻影记录并不存在，所以读取的时候加锁就有点麻烦—— 因为并不知道给谁加锁。InnoDB中是如何解决的，我们后面会讲到。

锁定读

也称当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。

哪些是当前读呢？select lock in share mode (共享锁)、select for update (排他锁)、update (排他锁)、insert (排他锁/独占锁)、delete (排他锁)、串行化事务隔离级别都是当前读。

当前读这种实现方式，也可以称之为LBCC（基于锁的并发控制，Lock-Based Concurrency Control），怎么做到？

共享锁和独占锁

在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，MySQL中的锁有好几类：

共享锁英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。

假如事务E1首先获取了一条记录的S锁之后，事务E2接着也要访问这条记录：

如果事务E2想要再获取一个记录的S锁，那么事务E2也会获得该锁，也就意味着事务E1和E2在该记录上同时持有S锁。

独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。

如果事务E2想要再获取一个记录的X锁，那么此操作会被阻塞，直到事务E1提交之后将S锁释放掉。

如果事务E1首先获取了一条记录的X锁之后，那么不管事务E2接着想获取该记录的S锁还是X锁都会被阻塞，直到事务E1提交。

所以我们说S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的

写操作的锁

平常所用到的写操作无非是DELETE、UPDATE、INSERT这三种：

DELETE：

对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，然后再执行delete mark操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。

INSERT：

一般情况下，新插入一条记录的操作并不加锁，InnoDB通过一种称之为隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。当然，在一些特殊情况下INSERT操作也是会获取锁的，具体情况我们后边再说。

UPDATE：

在对一条记录做UPDATE操作时分为三种情况：

1、如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。

2、如果未修改该记录的储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。

3、如果修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了。

锁的粒度

表锁与行锁的比较

锁定粒度：表锁 > 行锁

加锁效率：表锁 > 行锁

冲突概率：表锁 > 行锁

并发性能：表锁 < 行锁

不同情况加锁

以一个不同的teacher表做示例

在teacher表中某行数据如果有S锁，那么可以对其他行、整张表加S锁，可以对表中某行数据加X锁，不可以对整张表加X锁
在teacher表中某行数据如果有X锁，那么可以对其他行加S锁，可以对表中某行数据加X锁，不可以对整张表加S、X锁
teacher表如果整张表有S锁，那么可以对其他行加S锁，不可以对表中某行数据加X锁，不可以对整张表加X锁
teacher表如果整张表有X锁，那么全部加锁阻塞

意向锁

如果我们想对大楼整体上S锁，首先需要确保大楼中的没有正在维修的楼层，如果有正在维修的楼层，需要等到维修结束才可以对大楼整体上S锁。

如果我们想对大楼整体上X锁，首先需要确保大楼中的没有办公的楼层以及正在维修的楼层，如果有办公的楼层或者正在维修的楼层，需要等到全部办公的同学都办公离开，以及维修工维修完楼层离开后才可以对大楼整体上X锁。

我们在对大楼整体上锁（表锁）时，怎么知道大楼中有没有楼层已经被上锁（行锁）了呢？依次检查每一楼层门口有没有上锁？那这效率也太慢了吧！于是InnoDB提出了一种意向锁（英文名：Intention Locks）：

意向共享锁 ，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

意向独占锁 ，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

视角回到大楼和楼层上来：

如果有客户到楼层中办公，那么他先在整栋大楼门口放一把IS锁（表级锁），然后再到楼层门口放一把S锁（行锁）。

如果有维修工到楼层中维修，那么它先在整栋大楼门口放一把IX锁（表级锁），然后再到楼层门口放一把X锁（行锁）。

之后：

如果有投资人要参观大楼，也就是想在大楼门口前放S锁（表锁）时，首先要看一下大楼门口有没有IX锁，如果有，意味着有楼层在维修，需要等到维修结束把IX锁撤掉后才可以在整栋大楼上加S锁。

如果有谈条件要占用大楼，也就是想在大楼门口前放X锁（表锁）时，首先要看一下大楼门口有没有IS锁或IX锁，如果有，意味着有楼层在办公或者维修，需要等到客户们办完公以及维修结束把IS锁和IX锁撤掉后才可以在整栋大楼上加X锁。

注意：客户在大楼门口加IS锁时，是不关心大楼门口是否有IX锁的，维修工在大楼门口加IX锁时，是不关心大楼门口是否有IS锁或者其他IX锁的。IS和IX锁只是为了判断当前时间大楼里有没有被占用的楼层用的，也就是在对大楼加S锁或者X锁时才会用到。

总结一下：IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。

InnoDB中的表锁

原生表级锁

目前只有InnoDB支持行锁，其他存储引擎例如MyISAM、Memory都只支持表锁

对于InnnoDB原生的S、X锁，日常是不会使用的，原因：

**力度太大：**表级S锁和X锁会锁定整个表，而不是单独的行。这意味着当一个事务持有这些锁时，其他事务将无法并发地访问表中的任何行，即使它们要访问的行之间并没有冲突。
并发受限： 表级锁会限制并发访问。当一个事务持有S锁时，其他事务可以同时持有S锁，但是如果有事务想要持有X锁（例如写入数据），则其他事务必须等待。这可能导致并发性下降。
不适合高并发写入： 当多个事务想要同时修改同一张表时，表级X锁会阻塞其他事务的写入操作，从而导致性能问题和延迟。

元数据锁：在某个事物进行CURD时，另一个事务执行DDL语句会被阻塞，这就由元数据锁实现的

InnoDB中表级意向锁

IS意向共享锁、IX意向排他锁

当我们在对使用InnoDB存储引擎的表的某些记录加S锁之前，那就需要先在表级别加一个IS锁，当我们在对使用InnoDB存储引擎的表的某些记录加X锁之前，那就需要先在表级别加一个IX锁。

IS锁和IX锁的使命只是为了后续在加表级别的S锁和X锁时判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。我们并不能手动添加意向锁，只能由InnoDB存储引擎自行添加。

AUTO-INC锁

在InnoDB中，如果给某一列属性值添加AUTO_INCREMENT自动递增属性，那么在插入数据时，InnoDB就会自动帮我们分配好递增的值，实现该种现象的方式有两种

使用AUTO-INC锁

在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
使用轻量级锁

在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的AUTO_INCREMENT列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。

如果我们的插入语句在执行前就可以确定具体要插入多少条记录，比方说我们上边举的关于表t的例子中，在语句执行前就可以确定要插入2条记录，那么一般采用轻量级锁的方式对AUTO_INCREMENT修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。
InnoDB提供了一个称之为innodb_autoinc_lock_mode的系统变量来控制到底使用上述两种方式中的哪种来为AUTO_INCREMENT修饰的列进行赋值，当innodb_autoinc_lock_mode值为0时，一律采用AUTO-INC锁；当innodb_autoinc_lock_mode值为1时，一律采用轻量级锁；当innodb_autoinc_lock_mode值为2时，两种方式混着来（也就是在插入记录数量确定时采用轻量级锁，不确定时使用AUTO-INC锁）。

不过当innodb_autoinc_lock_mode值为2时，可能会造成不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。