Mysql-核心知识

最新推荐文章于 2021-12-19 21:40:34 发布

weixin_43934698

最新推荐文章于 2021-12-19 21:40:34 发布

阅读量140

点赞数

本文链接：https://blog.csdn.net/weixin_43934698/article/details/118793453

版权

1 常用引擎

1.1 引擎对比

myisam：不支持事物、表级锁、不支持外键、非聚集索引（B+树）、可以没有主键
innodb：支持事物（ACID）、支持行锁、支持外键、聚集索引（B+树）、必须有主键

1.2 B树与B+树

【B-树】

B-树是一种平衡M叉搜索树

键值分布在整颗树中，搜索有可能在非叶子结点结束
所有叶子节点都在同一层
根节点至少有两个子节点，（除了第一次插入的时候，此时只有一个节点，根节点同时是叶子节点）
每个节点最多拥有m个子树
每个节点最多可以有m-1个key，最小？
n个元素的节点包含n+1个子树

【B+树】

B+Tree 是在 B-Tree 基础上的一种优化

非叶子节点存储key，叶子节点存储key和数据
叶子节点两两指针相互链接（符合磁盘的预读特性），顺序查询性能更高
n个元素的节点包含n个元素（左闭右开）

1.3 二叉树与哈希表

哈希表：虽然能够再 O(1) 查找到目标数据，不过不支持模糊查找，且会出现哈希表冲突问题。
二叉树：每个节点仅存放一个键，树的高度会更高，导致磁盘的寻址加载次树增多，影响性能。

2 事物

是访问和更新数据库的程序执行单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。

2.1 四个特性ACID

原子性：整个事务是不可分割的最小单位，事务中任何一个语句执行失败，所有已经执行成功的语句也要回滚恢复到执行事务前到状态。要么全部成功、要么全部失败。
一致性：事务将数据库从一种状态转变为下一种一致的状态。在事务的前后，数据库的完整性约束没有被破坏。
- 强一致性：当更新操作完成之后，任何多个后续进程或者线程的访问都会返回最新的更新过的值。
- 弱一致性：系统在数据写入成功之后，不承诺立即可以读到最新写入的值，也不会具体的承诺多久之后可以读到。
- 最终一致性：弱一致性的特定形式。系统保证在没有后续更新的前提下，系统最终返回上一次更新操作的值。
隔离性：MySQL数据库中可以同时启动很多的事务，但是，事务和事务之间他们是相互分离的，也就是互不影响的。
- 隔离级别：后文有介绍；
持久性：事务一旦提交，那么就是永久性的，不会因为宕机等故障导致数据丢失（外力影响不保证，比如磁盘损害）。

2.2 隔离级别

先介绍一下隔离级别需要解决的几个问题：

脏读：指读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。
不可重复读：指读到了其他事物已提交的数据，同一事务内，不同的时刻读到的同一批数据可能是不一样的。
幻读：幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行并提交。

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

读未提交（READ UNCOMMITTED）
读提交（READ COMMITTED）
可重复读（REPEATABLE READ）：mysql默认级别
串行化（SERIALIZABLE）

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

3 事物的实现

本节会介绍redo log与undo log，他们共同保证了事物的原子性、一致性、持久性。

3.1 持久性实现

MySQL的innoDB存储引擎，使用Redo log保证了事务的持久性。当事务提交时，必须先将事务的所有日志写入日志文件进行持久化，就是我们常说的WAL（write ahead log）机制。这样才能保证断电或宕机等情况发生后，已提交的事务不会丢失。

WAL（write ahead log）机制：这个技术是保障持久性的关键技术，在HBase中也扮演重要角色

【名词介绍】

redo log分为两部分

redo log buffer：日志缓存，放在内存中可能会丢失；
redo log file：日志记录，放在磁盘中的持久化数据；

两个阶段提交：redo log file会分两个阶段进行数据提交

prepare阶段：代表日志数据全部准备好；
commit阶段：代码日志已经完成提交；

【实现方式】

举一个事务的例子：

步骤1:begin;
步骤2:insert into t1 …r
步骤3:insert into t2 …
步骤4:commit;

这个事务的写入过程实际拆解如下：

为什么要采用两阶段提交呢？

实际上，两阶段提交是分布式系统常用的机制，也是为了保证事务的持久性。redo log 和bingo 有一个共同的XID数据字段，崩溃恢复的时候会按顺序扫描redo log。

碰到有 prepare的事物，拿XID找不到binlog对应的事务，事务就直接操作回滚。
碰到既有prepare事物，又找得到对于binlog事物的，就直接提交保证数据不丢失。

redo log buffer什么时候写入redo log file中？

写入redo log buffer的日志已经占据总容量的一半了，也就是超过了8MB（可配置），此时就会把他们刷入到磁盘文件里去。
一个事务提交的时候，必须把他的那些redo log所在的redo log block都刷入到磁盘文件里去。
后台线程定时刷新，有一个后台线程每隔1秒就会把redo log buffer里的redo log block刷到磁盘文件里去。
MySQL关闭的时候，都会刷入到磁盘里去。

innodb持久化配置？

对于redo log的持久化，可以如下图所示。

1）先写入redo log buffer，在蓝色区域。

2）写入redo log file，但是还没有fsync，这时候是处于黄色的位置，处于系统缓存。

3）调用fsync，真正写入磁盘。

innodb支持配置事物提交时redo log处理哪个阶段，这里就不详细展开了。参考文档：https://www.toutiao.com/a6815552421872271884/

redo log和binlog的区别？

3.2 原子性实现

在对数据库进行修改时，innoDB引擎除了会产生redo log，还会产生undo log（在sql前记录）。Undo log保证了事务的原子性，当事物中失败了一条sql需要使用undo log日志进行事物回滚操作，将数据回滚到修改之前的样子。

undo log是逻辑日志,事务提交时,记录操作的反操作(insert->delete,update->update,delete->insert),可以用于回滚。

	redo log	undo log	binlog
日志类型	物理日志	逻辑日志	逻辑日志

undo log还要另外一个重要作用，就是用于mvcc（多版本控制），也就是实现事务隔离性的基础，当用户读取一行记录时，如果这个记录已接被其他事务占用，那么当前事务就可以通过undo读取之前的行版本信息，用来实现非锁定读取，就是“快照读”。

3.3 一致性的实现

就像一开始在定义的时候介绍的，事务的ACID性质不是完全正交的，尤其是一致性，我们可以认为原子性、持久性和隔离性都是为了实现事务的一致性。

4 多版本并发控制

MVCC全称Multi-Version Concurrency Control，是一种并发控制的方法。一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC最大的好处是：读不加锁，读写不冲突，极大的提高了系统的并发性能。其实，MVCC就一句话总结：同一份数据临时保存多个版本的一种方式，进而实现并发控制。

4.1 当前读与快照读

当前读：

场景：select lock in share mode(共享锁), select for update ; update, insert,delete(排他锁)这些操作都是一种当前读

加锁：读取最新版本并对读取的记录进行加锁，读取时还要保证其他并发事务不能修改当前记录，
快照读：

场景：不加锁的操作(select)就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；

不加锁：快照读的实现是基于多版本并发控制，既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本；

4.2 MVCC作用

解决读-写冲突的无锁并发控制（用到undo log获取到指定版本），这里的读是快照读

在并发读-写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

5 锁

5.1 共享锁与排他锁

mysql锁机制分为表级锁和行级锁，这里介绍的是行级锁中的共享锁与排他锁。

共享锁：又称为读锁（S锁），就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁：又称为写锁（X锁），如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

5.2 意向锁

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）
意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

【注意事项】

意向锁之间互相兼容
影响表级排他 / 共享锁：这里的排他 / 共享锁指的都是表锁！！！

【场景举例】

事物A需要获取表级排他锁，发现事物存在意向共享锁（或意向排他锁），那么事物A需要等待B事物释放锁后才能获取到。设想一下如果表中数据由100W条，就避免的循环表中数据判断是否存在对应行锁了。

5.3 排他锁三种实现

之前我们介绍了排他锁，其实innodb下的记录锁，间隙锁，next-key锁统统属于排他锁。

记录锁（行锁）：记录锁其实很好理解，对表中的记录加锁，简称行锁。
间隙锁：锁定一个范围但不包含记录本身。
next-key锁：索引记录上的记录锁和在记录之前的间隙锁的组合。

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：

防止间隙内有新数据被插入。
防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）。

5.4 插入意向锁

插入意向锁不是意向锁，是一种特殊的间隙锁。

innodb中有插入意向锁，专门针对insert，如果插入前该间隙已经由gap锁，那么Insert会申请插入意向锁。那么这个插入意向锁的作用是什么？

避免出现幻读：间隙锁是不互斥的，间隙锁的存在是为了避免间隙中出现新增数据，那么就需要与插入意向锁配合使用。插入意向锁所在间隙如果存在间隙锁，就进行锁等待，等待间隙锁释放后唤醒。
保持并发插入：插入意向锁之间不互斥，也就是同一个间隙可以同时插入多条不同数据。

5.5 自增锁

参考文档：https://keithlan.github.io/2017/03/03/auto_increment_lock/

自增锁是一种特殊的表级别锁（table-level lock），语句结束即释放（非事物级别），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便保持列的自增。

与此同时，InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为。

【举例】

假设有数据表：t(id AUTO_INCREMENT, name);

事务A先新增语句执行中：insert into t(name) values(xxx);
事务B后执行：insert into t(name) values(ooo)；阻塞等待A语句结束

【新增类型】

simple insert：插入的记录行数是确定的：比如：insert into values、replace 但是不包括： INSERT ... ON DUPLICATE KEY UPDATE。
bulk inserts：插入的记录行数不能马上确定的，比如： INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA
mixed-mode inserts：这些都是simple-insert，但是部分auto increment值给定或者不给定，以下都是Mixed-mode inserts

1. INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

2. INSERT ... ON DUPLICATE KEY UPDATE

【innodb_autoinc_lock_mode】

0：性能最低，所有的insert语句在开始时都会获得一个表锁autoinc_lock.该锁会一直持有到insert语句执行结束才会被释放。保证同一条语句插入记录自增ID是连续的。
1：默认锁模式，性能相比0有优化，在这种模式下，insert语句在开始时会获得一个表锁autoinc_lock, simple insert在获取到需要增加的ID的量后，autoinc_lock就会被释放。但对于bulk insert，自增锁会被一直持有直到语句执行结束才会被释放。保证同一条语句插入记录自增ID是连续的。
2：性能最好，但不能保证同一条语句插入记录自增ID是连续的。由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的。

5.6 一条简单语句分析

https://www.toutiao.com/a6798269146309067271/

【实践操作】

建表如下
设置Innodb存储引擎
设置为可RR隔离级别
新增三条数据
三种场景执行sql：delete from suoyin where test = 7；

场景一：test字段无索引

est列上无索引，只能进行全表扫描，那么该如何加锁，参考下图：

如图，可以看出这是一个很恐怖的事情，全表每条记录要加X锁，每个Gap加上Gap锁。其他事物将均不能执行，不能更新，删除，插入，这样，全表锁死。当然，MySQL进行了优化，就是semi-consistent read。semi-consistent read开启的情况下，对于不满足条件的记录，MySQL会提前放锁，同时Gap锁也会释放。

场景二：test字段唯一索引

参考下图：首先会在唯一索引上加对应记录锁，然后会找到对应主键索引加对应记录锁。

场景三：test字段普通索引

将普通索引匹配记录加锁，并且前后加间隙锁。同时对应主键加记录锁。

5.7 如何避免死锁

加锁顺序一致：以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序为1，2；事务B更新数据的顺序为2，1。这样更可能会造成死锁。
事物大小控制：大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
加锁范围控制：在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

6 SQL执行顺序

通过一条例子进行分析

书写顺序	执行顺序	执行动作
SELECT	7	查询出需要的数据
DISTINCT <select_list>	8	对数据进行去重
FROM 表 1	1	获取表1为需要处理的表,这也是sql执行的第一步
【连接类型】 JOIN 表2	3	表1、过滤条件、表2获得笛卡尔积
ON 连接条件	2	通过连接的条件，对表1进行过滤
WHERE 筛选条件	4	通过条件筛选数据
GROUP BY 分组列表	5	对筛选出的数据进行分组
HAVING 分组后的筛选条件	6	对group by数据进行聚合
ORDER BY 排序的字段	9	对数据进行排序
LIMIT 起始的条目索引，条目数;	10	获取指定数据

7 MYSQL索引

参考文档：https://www.toutiao.com/a6904879222414737924/

索引：在关系数据库中，索引是一种对数据库表中一列或多列的值进行排序的一种存储结构。

7.1 常用数据结构

【为什么使用索引】

提高查询效率：索引能极大的减少存储引擎需要扫描的数据量。
保证唯一性：唯一索引可保证数据库表中每行数据的唯一性。
加速表之间的连接。
分组排序效率提高：分组、排序等操作时，避免使用临时表，提高执行效率。

【如何建立索引】

尽量使用区分度高的列
尽量使用查询频率高的列
尽量使用占字节少的列
尽量扩展索引，而不是新增索引（新增索引消耗内存大）

【合理运用索引】

查询条件不使用函数，函数不走索引，比如max
不使用左模糊查询
使用limit时保证偏移量不太大
符合索引合理使用 1最左匹配原则。2范围查询后不会再命中索引
不能包含隐式类型转换

【有序数组】

以有序数组存储，需要查询特定值时，使用二分法就可以快速得到，时间复杂度是 O(logn)。

优点：实现简单，占用存储空间小，效率高。

缺点：其只适合静态数据，如遇到有数据新增或删除，则就会需要数据移动，这将非常消耗资源。

【哈希表】

哈希表是一种以键-值（K-V）存储数据的结构，我们只需要输入键 K，就可以找到对应的值 V。

优点：假设无哈希冲突，那么时间复杂度时O(1)

缺点：无法进行模糊查询与范围查询，同时哈希冲突时可能导致效率降低

【二叉搜索树】

是指一颗空树或者具有以下性质的二叉树：

若任意节点的左子树不空，则左子树上所有节点的值均小于它的根节点的值；
若任意节点的右子树不空，则右子树上所有节点的值均大于或等于它的根节点的值；
任意节点的左、右子树也分别为二叉查找树；

相对于有序数组和 Hash，二叉搜索树在查找和插入两端的表现都非常不错。后续基于此不断的优化，发展出 N 叉树等。

优点：查询与查询删除都不错，查找时间复杂度O(logn)。

缺点：二叉树深度较大，磁盘io次数多。

【B+树】

B+树索引是关系型数据库中最常见的一种索引，也将是本节的主角。B+树的 3 个优点：

层级更低，IO 次数更少
每次都需要查询到叶子节点，查询性能稳定
叶子节点形成有序链表，范围查询方便

查询：从根节点开始，找到要检索数据位于哪个子节点上（如不存在查询为空），再不断对子节点进行同样的处理，直到到达叶子结点。对叶子节点中的数组，使用二分查找算法，就可找到元素或者确认不存在。

插入：插入的操作全部都在叶子结点上进行，且不能破坏关键字自小而大的顺序，规则如下：

若被插入关键字所在的结点，其含有关键字数目不大于阶数 M，则直接插入；
若被插入关键字所在的结点，插入后含有关键字数目大于阶数 M，则需要将该结点分裂为两个结点，一个结点包含 ⌊M+1/2⌋ ，另一个结点包含 ⌈M+1/2⌉ 。如果上移操作导致其双亲结点中关键字个数大于 M，则应继续分裂其双亲结点。
若插入元素比当前最大元素还大，破坏了B+树中从根结点到当前结点的所有索引值，此时需要及时修正后，再做其他操作。