【MySQL】MySQL高级

一、概述

1.1 逻辑架构

1.1.1 MySQL体系结构框图
MySQL逻辑架构图
1.1.2 体系结构组成
  • 连接层
    • 该层引入连接池技术
    • 管理连接,权限验证
  • 服务层:
    • SQL接口(SQL Interface):接受用户的SQL命令,并返回查询结果
    • 解析器(Parser):类似编译过程中的词法,语法解析器,对SQL语句进行分析得到这个语句"想做什么",并验证其是否合法
    • 优化器(Optimizer):查询优化
    • 缓存器:查询结果的缓存,若命中缓存则无需去磁盘中查找
    • 所有跨存储引擎的功能都在这里实现
  • 引擎层:
    • 向上层提供通用的读写API,屏蔽底层不同存储引擎之间的差异
    • 真正负责数据的存储与提取,类似Web项目中DAO层
  • 存储层:
    • 底层存储设备的硬件
1.1.3 MySQL工作流程

虚线框内为MySQL服务器端

img
  • 连接

    • 输入如下指令进入连接过程
    mysql -u root -p
    
    • ​ 连接使用TCP/IP三次握手连接
  • 查询缓存

    • 查询是否执行过该sql语句,如果执行过其结果有可能会在缓存中,进入缓存寻找
    • 找到直接返回,没找到进入下一步
  • 分析器

    • 词法分析
    • 语法分析
  • 优化器:

    • 索引使用
    • 表的连接顺序等
  • 执行器:

    • 调用引擎接口取出数据或修改数据

1.2 MySQL存储引擎

什么是存储引擎

MySQL中的数据用各种不同的技术存储在文件(或者内存)中;

每一种技术都使用不同的存储机制索引方式锁定水平并且最终提供广泛的不同的功能和能力;

以上每一种技术以及与其配套的相关功能组件统称为存储引擎(也称之为表类型);

有哪些存储引擎

MySQL中存储引擎可以如下查询:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

查询当前使用的存储引擎:

mysql> show variables like '%storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+

最常见的是MyISAMInnoDB这两种存储引擎,默认是InnoDB

MyISAM和InnoDB

两种存储引擎的比较:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3udhsrn4-1642237646034)(C:\Users\19643\AppData\Roaming\Typora\typora-user-images\image-20211222114954498.png)]

二、索引基础

2.1 索引概述

2.1.1 什么是索引

索引是一种数据结构,这种数据结构将本来无序的数据变得有序;

使用索引的方式就是对某个字段(列)建立索引。

如果按如下方式建表:

CREATE TABLE t_book(id INT NOT NULL, bookName NOT NULL VARCHAR(20))

从bookName字段来看,数据存储形式大致是这样

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pj62JkOc-1642237646035)(C:\Users\19643\AppData\Roaming\Typora\typora-user-images\image-20211227203712827.png)]

数据之间没有顺序,想要查询“Go”需要从头遍历

如果对bookName字段建立索引(B+树索引)

CREATE INDEX index_name ON t_book(bookName);

数据存储形式大致是这样(假设是按照字典序排序)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MRwSIllG-1642237646036)(C:\Users\19643\AppData\Roaming\Typora\typora-user-images\image-20211227204016685.png)]

这是省略id字段,真正的索引并不是这样(索引的结构取决于存储引擎规定的索引方式),这里仅作为示意

2.1.2 为什么需要索引

不建立索引的情况下,如下条件查询

SELECT * FROM t_book WHERE bookName LIKE 'Java';

会按照顺序查询,查询所有数据直到满足条件或者所有数据查询完成,时间复杂度O(n),n为数据行数;

由于MySQL中数据存储在磁盘中,即使数据在磁盘连续存储(涉及到的磁盘块最少的情况),也可能会占用很多磁盘块,这种级别的查询时间复杂度可能会频繁的产生I/O,耗费大量时间;

而建立索引之后,数据是"有序"的,在有序的序列中查询特定值,可以用二分查找将时间复杂度降到O(logn)。

看看真正的索引是什么样的

InnoDB存储引擎支持的索引有B+树索引全文索引哈希索引

下面以B+树索引为例

2.1.3 B-树建立索引

img

每个结点中蓝色框内位索引列的值,即建立该B-树依据的表中的列值;

每个结点会存储真正的数据;

每个结点除了存储数据外还有存储指针的区域指向其它磁盘块,该磁盘块的所有数据的索引字段的大小一定介于指针左右两侧索引字段值之间

下图用索引字段的范围表示某个磁盘块的表数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fzCMVjRC-1642237646037)(C:\Users\19643\AppData\Roaming\Typora\typora-user-images\image-20211222165534228.png)]

B-树特点:
  • 所有结点均存储数据区间

  • 索引元素不会重复存储

  • 任意结点中的数据按索引字段大小依次排列

2.1.4 B+树建立索引

建立在B树基础之上

下图中,以id为索引建表,蓝色框内代表索引值,绿色代表该id对应的所有行数据

img

B+树特点:
  • 非叶结点不包含数据,仅包含用于判断大小的索引字段值和相应大小的区间
  • 索引结点冗余
  • 叶子结点组成双向链表,包含所有数据,增加区间访问性
  • 叶子结点组成双向链表,对于根据索引排序的查询语句执行速度非常快
B-树B+树
非叶节点有数据无数据
叶节点数据不完全的数据(行)包含全部数据
叶节点结构双向环链表

*这里注意一点 B+树所谓的叶节点包含所有数据,指的不一定是叶节点会真的开辟空间存储数据,而是指的是通过叶结点可以找到所有数据,可以是真的存储,也可以是将上图的data替换为磁盘地址,这就是聚簇索引非聚簇索引MyISAMInnoDB就分别使用了这两种方式

B+树示例查询过程

现在要查id > 50的所有行

  1. 访问根结点,找到对应的索引页,在该页中找到50,根据其右侧的指针找到下一层结点
  2. 进入下一层结点发现该页是数据页(叶结点),且最小值就是50,数据页中数据按序排列其右侧的值一定更大
  3. 直接遍历链表右侧,返回所有50右侧的值,查询结束
2.1.5 索引为什么可以优化查询
无索引 -> 索引(时间复杂度优化)

数据量很大时,如果不使用索引,每次查询最坏情况下都要遍历所有数据,对所有涉及到的磁盘块进行I/O是一种非常耗费时间的行为;

使用索引之后,显然查询时间复杂度变为O(logn),但不仅如此,数据库存储的数据是存储在磁盘中,在根据索引字段查询时,就类似在二叉搜索树中查询一样,每次往下一层,并根据与预期值大小决定往左还是往右,B/B+树中是根据结点值大小决定前往哪个区间,建立索引之后,时间复杂度降低可以理解为查询次数减少,也就是I/O次数减少,自然极大提高效率;

一般情况下可以理解为,查询过程中每往下一层都需要进行一次I/O,I/O次数由树的高度决定;

一般一棵 B-树的高度在 3 层左右,3 层就可满足百万级别的数据量。

B-树索引 -> B+树索引(高度优化)

建立索引后,树的高度直接决定查询效率,B+树非叶子结点中不包含数据,意味着可以包含更多的区间,树的层数就会降低;

所有叶子结点以环形双向链表组织,叶子结点包含全部数据信息,利于区间查找顺序查询以及根据索引的排序查找

  • 对于区间查找

    • 比如查询索引值在[x1, x2]的数据,只需要找到x1所在磁盘块c1,即可,然后从x1开始遍历直到遇到x2停止,输出其间所有数据即可
    • 而B-树执行同样的查找,在最底层发现了x1, 在上一层发现了x2, 由于没有往上层的指针,无法直接遍历得其间所有数据,仍需要进行额外的查询
  • 对于顺序查询

    • B-树进行顺序查询需要用树的中序遍历,频繁的在上下层之间进行切换,B+只需从叶结点链表头部开始往后遍历即可
  • 对于排序查找:

    • select * from table_t order by id
      

      id已经建立索引,所有取出的数据都必然已经按照id排序,所以order by id并不会造成额外的排序时间开销

2.1.6 索引的劣势
  • 空间开销交大:空间换时间的做法

  • 更新表速度降低:更新表的同时需要对索引进行更新,维护B+树本身需要一定时间开销

2.1.7 索引分类
  • 单值索引:一个索引只包含单个字段

  • 唯一索引:索引的值不能出现重复

  • 复合索引:相对于单值索引来说,一个索引可以用多个字段(也称联合索引)

2.2 索引常见概念

2.2.1 聚簇索引和非聚簇索引

  • 聚簇索引(Innodb):聚簇索引指的是在叶子结点会存放所有数据

    img

    • ​ 聚簇索引对于主键的排序查找和范围查找很快
  • 非聚簇索引(MyISAM):叶节点仅存放数据所在地址

    img

2.2.2 联合索引

联合索引是针对一张表而言,对多个字段一起建立索引,排序规则按照建立索引时从左到右的顺位

非常重要的概念,联合索引的使用对索引优化有极大帮助

示例

建表

CREATE TABLE table_test(a INT NOT NULL, b INT NOT NULL)

建立索引

CREATE INDEX index_a_c ON table_test(a, b);

假设数据为

ac
11
12
21
24
31
32

建立联合索引后会形成如下的B+树

在这里插入图片描述

该联合索引会如果忽略第二个字段,则所有叶节点依旧按照a排序,在a相等的情况下按照b排序;

2.2.3 回表/索引覆盖/索引下推/最左匹配

  • 回表

    事实上一个表中除了主键会建立索引(通常称之为主索引)外,任何一个字段都可以成为索引字段,这样的索引称为辅助索引(二级索引),主索引可以采用聚簇或者非聚簇的方式,如果采用聚簇的方式,辅助索引为了节约空间,不会在叶节点存放数据,其会存放对应记录的主索引的值,如下图所示:

    img

这就表示如果执行如下的查询:

select * from table_1 where secondary_index = value

secondary_index是辅助索引,就会先查询辅助索引树,到叶结点获取到对应行记录的主索引值,然后以主索引值为查询条件再回到主索引树中再一次查询到叶节点获取到数据,这样一个从辅助索引树回到主索引树的过程就是 — 回表

  • 索引覆盖

    select 主索引 from table_1 where 辅助索引 = value
    

    上面也说了,辅助索引的叶节点存放了主索引的信息,如果本次查询仅需要通过辅助索引获取主索引的值,那就不需要回表,这就叫索引覆盖

  • 索引下推

2.4 索引结点底层结构

2.4.1 InnoDB逻辑存储结构

  • 所有数据逻辑上被存放在一个空间中(物理上不一定连续),称为表空间

  • 表空间由段(segement)、区(extent)、页(age)组成

    img

  • 段:大致分为数据段、索引段、回滚段等,数据段就是B+树叶子结点构成,索引段就是B+树非叶子结点

  • 页:每一个叶子结点就是一个页(又称:块),数据页详细介绍可以看这里 Mysql----InnoDB数据页结构 - 梦想成为DALAO - 博客园 (cnblogs.com)

  • 行:每一页有很多行,每一行就是数据库一条记录

  • 页结点中的头部包含了该页结点是不是叶子结点,以及上一个和下一个页的地址

2.4.2 InnoDB行记录格式

  • MySQL 5.0之后采用Compact行格式

  • img

  • 记录头各个bit位信息如下:

  • img

2.4.3 总结

通过索引结点底层可以看出在主键索引树中

  • 所有非叶结点存放的仅仅是键值以及该键值对应的数据所在数据页的偏移量

  • B+树中所谓的左右"孩子指针"实际上只有"右孩子指针",该指针实际上也只是一个数据页的偏移量,该数据页最小的数据就是本索引值

  • 详见《MySQL技术内幕》P193

三、索引优化

3.1 索引分析方法 — explain

explain都不会用,你还好意思说精通MySQL查询优化? - 51CTO.COM “explain各列解释”

3.2 索引失效

3.2.1 不满足最左前缀

3.2.2 索引列上做函数等操作

3.2.3 use file sorting

3.2.4 其它

  • 不符合最左前缀原则
  • 在索引列上做操作
  • 使用索引中范围条件右边的列
  • 使用不等于
  • is null or is not null
  • like以通配符开头
    • like (’%abd…’)

四、MySQL的MVCC实现原理

4.1 基本概念

什么是MVCC

MVCC,全称 Multi-Version Concurrency Control ,即多版本并发控制

对于写操作一定是排他锁,不存在版本的概念,所以其实MVCC针对的是读操作,解决的是快照读的同时发生的读写冲突问题;

一般MySQL中读操作可以分为快照读当前读,当前读读取的一定是最新版本的记录,所以当前读也不存在MVCC的问题

所以MVCC应该理解为:MySQL在快照读的情况下,保证多版本并发控制的方法

快照读

又称一致性非锁定读(consistent nonlocking read),在读数据时若发现有事务 正在写数据则直接获取之前的快照数据(即对本事务可见的最新版本行数据)。

显然,这种情况下读取数据的事务不需要等待写数据的事务释放锁,提高了并发性能;

快照读提高并发性能带来的弊端是获取的数据并不一定是最新数据

4.2 MVCC实现原理

MVCC的实现依赖于三个组件,分别是行记录中的隐式字段,undo日志,Read View

隐式字段

MySQL数据库表中数据以行的形式存在,每行数据除了显式定义的列之外,还会有一些隐藏的列,这些列中和MVCC相关的是以下几个

  • DB_TRX_ID

    记录最后一个对该记录修改的事务的id(每个事务都有一个独一无二的id)

  • DB_ROLL_PTR

    回滚指针,指向上一个版本的数据;

    事务在执行过程中发生异常,行数据需要回滚回上一个版本就是依靠这个指针

  • DB_ROW_ID

    隐藏主键,如果没有指定主键或者唯一值的列,则使用隐藏主键位索引组织数据

    隐藏列的示意图

    img


undo log

undo log 主要分为两种:

  • insert undo log
    代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要,并且在事务提交后可以被立即丢弃

  • update undo log
    事务在进行 update 或 delete 时产生的 undo log ,不仅在事务回滚时需要,在快照读时也需要;

    在快速读或事务回滚不涉及该日志时,对应的日志才会被统一清除

undo log示意图

事务1将name = ”Jerry“修改为 name = "Tom"会按照如下顺序进行

  1. copy原纪录(name = “Jerry”)到undo log中
  2. 对原纪录进行sql语句中的修改
  3. 修改原纪录隐藏列DB_ROW_ID的值,使其指向之前copy的记录
  4. 修改DB_TRX_ID的值为事务1的id(假设为1)

img

undo log的存在使得MySQL中同一行记录(指主键的值相同)可以保存很多条,比如如果事务1修改完后,事务2又进行修改,将age修改为30

会产生如下情况:

img

其中每一条行记录可以看作是该记录的一个版本,MVCC中 V即是version,指的就是这些不同版本的行数据;

而MVCC做的就是判断一个事务在快照读的时候到底应该选择哪个版本的行数据去读取


可重复读隔离级别下,总是去读取事务开始时的快照版本
读已提交隔离级别下,总是去读取最新的快照版本
Read View

Read View 是某一个事务进行快照读操作的时候产生,其中主要存储的信息如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GAdBM3Hg-1642237646040)(C:\Users\19643\AppData\Roaming\Typora\typora-user-images\image-20220114152858362.png)]

read view是MVCC实现的原理 ,通过将某行数据的DB_TRX_ID(最后一次修改这行记录的事务的id)与read view中这三个主要信息进行比较,根据一定的规则判断事务是否可以读取该行数据,从而达到了某些版本对事务不可见,进行版本控制。


下面结合隔离等级为可重复读(repeatable-read)和读已提交(read-committed)的实现原理来解释下read view的作用

  1. 在隔离级别为可重复读的情况下

    若事务流水线如下所示:

    事务 1事务 2事务 3事务 4
    插入数据并提交事务开始事务开始事务开始
    快照读
    进行中修改并提交
    快照读
    • 事务1插入数据后:

在这里插入图片描述

​ DB_TRX_ID为最后一次修改该条数据的事务id,由于是刚插入的数据,所以此列值为执行插入语句的事务id = 1

  • 事务2进行快照读前生成read view:

在这里插入图片描述

此时全部事务都还在活跃中,trx_list(未提交事务列表)中包含所有事务id;

trx_list中最小的id为1(即up_limit_id),下一个未分配的id号为5

  • 事务2读取数据

在这里插入图片描述

  • 事务4修改(将Tom修改为Jerry)之后

在这里插入图片描述

  • 事务2再次读取该行记录

    在可重复读隔离级别下,此时read view仍旧使用之前快照读的的read view

在这里插入图片描述

行记录的DB_TRX_ID在trx_list中,意味着在事务2第一次读取数据之后,该数据被当时正活跃的事务4修改了,这次修改在可重复读隔离级别下应当对事务2是不可见的,所以判断事务2不能读取这个版本的记录

之后会通过DB_ROLL_PTR找到undo log中上一个版本的数据,发现这个版本的行数据中,DB_TRX_ID为1,1不在trx_list中,且小于trx_list中最小的值,说明这个版本的数据是在事务2发起第一次快照读之前就已经修改完成,所以可以读取。

可以看到,通过这样一个过程,使得事务2前后两次读取的数据,即便是期间有其它事务对该行数据进行了修改,也会读取到一摸一样的数据(或者说同一个版本),从而实现了事务的重复读取数据前后一致,即可重复读

实际上,在生成read view之后,具体判断是否可以读取该版本的数据是根据如下的规则:

在这里插入图片描述

  1. 在隔离级别为不可重复读(读已提交)的情况下

    不可重复读指的是不保证一个事务前后两次快照读的结果一致,其它事务的已提交的结果对快照读的事务是可见的

    其实不可重复读的实现原理和可重复读几乎一样,区别在于不可重复读每次快照读的时候都会生成新的read view从而导致trx_list更新

    上述例子中事务2第二次进行快照读的时候,如果是不可重复读,则会生成新的read view如下:

在这里插入图片描述

此时事务4已经不在活跃事务列表中,那么此时事务2按照上面的判断规则是可以读取到事务4修改之后的数据

不可重复读隔离级别关注的是,此时此刻数据的状态只要是已提交就可以读取

综上,实现可重复读(repeatable-read)和读已提交(read-committed)的隔离级别仅仅需要改变read view的生成时机即可。

这里为什么没有说读未提交(read uncommitted)和串行(serializable)这两个隔离级别?

因为MVCC只针对可重复读和不可重复读(读已提交)两个隔离级别,解决的只是读和写的冲突,很好理解:

对于串行来说事务之间采用悲观锁的思想严格互不干扰,一个事务执行过程中对涉及到的行加锁,其它事务无法对改行进行读写操作,不存在版本问题,所有的读和写都是针对最新版本

对于读未提交来说,所有事务对数据的修改对其它所有事务可见,也不存在版本控制问题,只需要解决并发过程中的写冲突即可

五、MySQL中的锁

5.1 基本概念

当前读

快照读相对应,又称一致性锁定读,操作的数据为最新版本的数据,包括增删改查

一般如下使用:

select ... for update	# 写锁
select ... lock in share mode # 读锁

MySQL的锁都是和当前读相关,下文的读操作也都是当前读,当前读都不涉及MVCC机制

行锁

单个行记录上的锁,同样区分为写锁(x锁),和读锁(S锁)

间隙锁

锁定一个范围,通常是按照索引列的值来锁定,用左开右开区间表示锁的范围,(a, b)表示索引列值>a的和 <b的行全部锁定;

间隙锁上锁时即便数据中没有对应的数据也会上锁,当没有数据时候的上锁理解为其它事务不可以插入数据,比如:

案例数据:

id(主键)c(普通索引)d(无索引)
555
101010
151515
202020
252525

执行事务:

时刻事务A事务B
1begin; select * from t where id = 11 for update(当前读)-
2-begin; select * from t where id = 6 for update; 成功
3-insert into t value (7, 7, 7) (blocked)
4commit;

事务A进行当前读操作,条件为id = 11,此时结合原始数据,会对区间(10, 15)上锁 — 间隙锁

事务B随后进行当前读,条件为id = 6,该次读取数据成功,因为上锁区间不包含id = 6,同时本次读取会对区间 (5, 10]上锁;

事务B进行插入操作,插入条件id = 7(这条id对应的记录本来不存在),7属于(10, 15)区间已经上锁,所以无法插入,事务B被阻塞

Next-Key Lock

就是间隙锁+行锁,用左开右闭区间表示,实际上Next-Key Lock是为了解决幻读问题产生的锁,过程示例同间隙锁,只是上锁区间改为左开右闭

幻读

事务A前后两次执行相同的sql语句,查询某个范围内的数据,所得到的结果不一致的现象;

与**不可重复读同样是因为相同的sql语句执行结果不同,不同的是幻读是因为其他事务进行insertdelete操作导致结果不一致,而不可重复读**是因为其他事务进行update操作而产生的

比如:

id(主键)c(普通索引)d(无索引)
555
10105
15155
202020
252525

执行事务:

时刻事务A事务B
1begin; select * from t where d = 5 for update;begin;
2-insert into t value (16, 16, 5) (没有间隙锁的情况下会执行成功);commit;
3select * from t where d = 5 for update;
4commit;

事务A第1次查询结果:

id(主键)c(普通索引)d(无索引)
555
10105
15155

事务A第2次查询结果:

事务B插入的数据也在查询结果中

id(主键)c(普通索引)d(无索引)
555
10105
15155
26165

这种情况下不涉及到行数据版本问题,所以**不可重复读隔离级别下的MVCC对解决幻读问题没有作用,而通过Next-Key Lock可以在隔离级别仅仅为不可重复读情况下解决幻读**问题;

即**Next-Key Lock**是一种保证数据读取一致性的情况下同时解决幻读问题的技术。

加入**Next-Key Lock**机制之后,事务B在做插入操作的时候,会被阻塞无法执行,因为此时区间会被上锁

实际上,本次示例在mysql5.5的版本中,即便是**不可重复读**隔离级别下,事务B的插入语句也会被阻塞无法执行,本例仅做说明

关于间隙锁详细的加锁规则可以参考这位大佬blog:

MYSQL(04)-间隙锁详解 - 简书 (jianshu.com)

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值