百度360必应搜狗淘宝本站头条
当前位置:网站首页 > 优雅编程 > 正文

MySQL 核心三剑客 —— 索引、锁、事务

sinye56 2024-10-14 16:11 4 浏览 0 评论

一、常见存储引擎

1.1 InnoDB

InnoDB 是 MySQL 5.5 之后默认的存储引擎,它具有高可靠、高性能的特点,主要具备以下优势:

  • DML 操作完全遵循 ACID 模型,支持事务,支持崩溃恢复,能够极大地保护用户的数据安全;
  • 支持多版本并发控制,它会保存数据的旧版本信息,从而可以支持并发和事务的回滚;
  • 支持行级锁,支持类似 Oracle 的一致性读的特性,从而可以承受高并发地访问;
  • InnoDB 组织数据时默认按照主键进行聚簇,从而可以提高主键查找的效率。对于频繁访问的数据,InnoDB 还会为其建立哈希索引,从而提高等值查询的效率,这也称为自适应哈希索引;
  • InnoDB 基于磁盘进行存储,所有存储记录按 的方式进行管理。为弥补 CPU 速度与磁盘速度之间的鸿沟,InnoDB 引用缓存池 (Buffer Pool) 来提高数据的整体性能。查询时,会将目标页读入缓存中;修改时,会先修改缓冲池中的页,然后再遵循 CheckPoint 机制将页刷回磁盘。所有缓存页通过最近最少使用原则 ( LRU ) 来进行定期清理。
  • InnoDB 支持两次写 (DoubleWrite) ,从而可以保证数据的安全,提高系统的可靠性。

一个 InnoDB 引擎完整的内存结构和磁盘结构如下图所示:


1.2 MyISAM

MyISAM 是 MySQL 5.5 之前默认的存储引擎。创建 MyISAM 表时会创建两个同名的文件:

  • 扩展名为 .MYD(MYData):用于存储表数据;
  • 扩展名为 .MYI (MYIndex): 用于存储表的索引信息。

在 MySQL 8.0 之后,只会创建上述两个同名文件,因为 8.0 后表结构的定义存储在 MySQL 数据字典中,但在 MySQL 8.0 之前,还会存在一个扩展名为 .frm 的文件,用于存储表结构信息。MyISAM 与 InnoDB 主要的区别其只支持表级锁,不支持行级锁,不支持事务,不支持自动崩溃恢复,但可以使用内置的 mysqlcheck 和 myisamchk 工具来进行检查和修复。

1.3 MEMORY

MEMORY 存储引擎(又称为 HEAP 存储引擎)通常用于将表中的数据存储在内存中,它具有以下特征:

  • MEMORY 表的表定义信息存储在 MySQL 数据字典中,而实际的数据则存储在内存空间中,并以块为单位进行划分;因此当服务器重启后,表本身并不会被删除,只是表中的所有数据都会丢失。
  • MEMORY 存储引擎支持 HASH 索引和 BTREE 索引,默认采用 HASH 索引。
  • MEMORY 表使用固定长度的行存储格式,即便是 VARCHAR 类型也会使用固定长度进行存储。
  • MEMORY 支持 AUTO_INCREMENT 列,但不支持 BLOB 列或 TEXT 列。
  • MEMORY 表和 MySQL 内部临时表的区别在于:两者默认都采用内存进行存储,但 MEMORY 表不受存储转换的影响,而内部临时表则会在达到阈值时自动转换为磁盘存储。

基于以上特性,MEMORY 表主要适合于存储临时数据 ,如会话状态、实时位置等信息。

1.4 CSV

CSV 存储引擎使用逗号分隔值的格式将数据存储在文本文件中。创建 CSV 表时会同时创建两个同名的文件:

  • 一个扩展名为 csv ,负责存储表的数据,其文件格式为纯文本,可以通过电子表格应用程序 (如 Microsoft Excel ) 进行修改,对应的修改操作也会直接反应在数据库表中。
  • 另一个扩展名为 CSM,负责存储表的状态和表中存在的行数。

1.5 ARCHIVE

ARCHIVE 存储引擎默认采用 zlib 无损数据压缩算法进行数据压缩,能够利用极小的空间存储大量的数据。创建ARCHIVE 表时,存储引擎会创建与表同名的 ARZ 文件,用于存储数据。它还具有以下特点:

  • ARCHIVE 引擎支持 INSERT,REPLACE 和 SELECT,但不支持 DELETE 或 UPDATE。
  • ARCHIVE 引擎支持 AUTO_INCREMENT 属性,并支持在其对应的列上建立索引,如果尝试在不具有 AUTO_INCREMENT 属性的列上建立索引,则会抛出异常。
  • ARCHIVE 引擎不支持分区操作。

1.6 MEGRE

MERGE 存储引擎,也称为 MRG_MyISAM 引擎,是一组相同 MyISAM 表的集合。 ”相同” 表示所有表必须具有相同的列数据类型和索引信息。可以通过 UNION = (list-of-tables) 选项来创建 MERGE 表,如下:

mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;

mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a))

ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

复制代码

创建表时可以通过 INSERT_METHOD 选项来控制 MERGE 表的插入:使用 FIRST 或 LAST 分别表示在第一个或最后一个基础表中进行插入;如果未指定 INSERT_METHOD 或者设置值为 NO ,则表示不允许在 MERGE 表上执行插入操作。MERGE 表支持 SELECT,DELETE,UPDATE 和 DELETE 语句,示例如下:

mysql> SELECT * FROM total;

+---+---------+

| a | message |

+---+---------+

| 1 | Testing |

| 2 | table |

| 3 | t1 |

| 1 | Testing |

| 2 | table |

| 3 | t2 |

+---+---------+

复制代码

二、索引

2.1 B+ tree 数据结构

如果没有特殊说明,通常大多数数据库采用的索引都是 B+ tree 索引,它是基于 B+ tree 这种数据结构构建的。为什么采用 B+ tree 而不是平衡二叉树 (AVL) 或红黑树等数据结构?这里假设索引为 1-16 的自增数据,各类数据结构的表现如下:

平衡二叉树数据结构


红黑树数据结构


Btree 数据结构


B+ Tree 数据结构


以上图片均通过数据结构可视化网站 Data Structure Visualizations 自动生成,感兴趣的小伙伴也可自行尝试。

从上面的图示中我们可以看出 B+ Tree 树具有以下优点:

2.2 B+ tree 索引

对于 InnoDB ,因为主键索引是聚集索引,所以其叶子节点存储的就是实际的数据。而非主键索引存储的则是主键的值 :

对于 MyISAM,因为主键索引是非聚集索引,所以其叶子节点存储的只是指向数据位置的指针:

综上所述,B+ tree 结构普遍适用于范围查找,优化排序和分组等操作。B+ tree 是基于字典序进行构建的,因此其适用于以下查询:

2.3 哈希索引

使用哈希索引时,存储引擎会对索引列的值进行哈希运算,并将计算出的哈希值和指向该行数据的指针存储在索引中,因此它更适用于等值比较查询,而不是范围查询,同样也不能用于优化排序和分组等操作。在建立哈希索引时,需要选取选择性比较高的列,即列上的数据不容易重复 (如身份证号),这样可以尽量避免哈希冲突。因为哈希索引并不需要存储索引列的数据,所以其结构比较紧凑,对应的查询速度也比较快。

InnoDB 引擎有一个名为 “自适应哈希索引 (adaptive hash index)” 的功能,当某些索引值被频繁使用时,它会在内存中基于 B+ tree 索引再创建一个哈希索引,从而让 B-Tree 索引具备哈希索引快速查找的优点。

2.4 索引的优点

2.5 使用策略

三、锁

3.1 共享锁与排它锁

InnoDB 存储引擎支持以下两种标准的行级锁:

排它锁和共享锁的兼容情况如下:

X X X 不兼容 不兼容 S 不兼容 兼容

3.2 意向共享锁与意向排它锁

为了说明意向锁的作用,这里先引入一个案例:假设事务 A 利用 S 锁锁住了表中的某一行,让其只能读不能写。之后事务 B 尝试申请整个表的写锁,如果事务 B 申请成功,那么理论上它就应该能修改表中的任意一行,这与事务 A 持有的行锁是冲突的。想要解决这个问题,数据库必须知道表中某一行已经被锁定,从而在事务 B 尝试申请整个表的写锁时阻塞它。想要知道表中某一行被锁定,可以对表的每一行进行遍历,这种方式可行但是性能比较差,所以 InnoDB 引入了意向锁。

按照意向锁的规则,当上面的事务 A 给表中的某一行加 S 锁时,会同时给表加上 IS 锁,之后事务 B 尝试获取表的 X 锁时,由于 X 锁与 IS 锁并不兼容,所以事务 B 会被阻塞。

X IX S IS X 不兼容 不兼容 不兼容 不兼容 IX 不兼容 兼容 不兼容 兼容 S 不兼容 不兼容 兼容 兼容 IS 不兼容 兼容 兼容 兼容

3.3 一致性读

1. 一致性非锁定读

一致非锁定读 (consistent nonlocking read) 是指在 InnoDB 存储引擎下,如果将要读取的行正在执行 DELETE 或 UPDATE 操作,此时不必去等待行上锁的释放,而是去读取 undo 日志上该行的快照数据,具体如下:

基于多版本并发控制和一致性非锁定读,可以避免获取锁的等待,从而提高并发访问下的性能。

2. 一致性锁定度

一致性锁定读则允许用户按照自己的需求在进行 SELECT 操作时手动加锁,通常有以下两种方式:

3.4 锁的算法

InnoDB 存储引擎支持以下三种锁的算法:

Record Lock:行锁,用于锁定单个行记录。示例如下:

-- 利用行锁可以防止其他事务更新或删除该行

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

复制代码

Gap Lock:间隙锁,锁定一个范围,但不包括记录本身,主要用于解决幻读问题,示例如下:

-- 利用间隙锁可以阻止其他事务将值15插入列 t.c1

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

复制代码

Next-Key Lock:等价于 行锁+间隙锁,既锁定范围,也锁定记录本身。可以用于解决幻读中的 ”当前读“ 的问题。

四、事务

4.1 ACID 定义

InnoDB 存储引擎完全支持 ACID 模型:

1. 原子性(Atomicity)

事务是不可分割的最小工作单元,事务的所有操作要么全部提交成功,要么全部失败回滚,不存在部分成功的情况。

2. 一致性(Consistency)

数据库在事务执行前后都保持一致性状态,数据库的完整性没有被破坏。

3. 隔离性(Isolation)

允许多个并发事务同时对数据进行操作,但一个事务所做的修改在最终提交以前,对其它事务是不可见的。

4. 持久性(Durability)

一旦事务提交,则其所做的修改将会永远保存到数据库中。即使宕机等故障,也不会丢失。

4.2 事务的实现

数据库隔离性由上一部分介绍的锁来实现,而原子性、一致性、持久性都由 undo log 和 redo log 来实现。

4.3 并发问题

在并发环境下,数据的更改通常会产生下面四种问题:

1.丢失更新

一个事务的更新操作被另外一个事务的更新操作锁覆盖,从而导致数据不一致:

2. 脏读

在不同的事务下,一个事务读取到其他事务未提交的数据:

3. 不可重复读

在同一个事务的两次读取之间,由于其他事务对数据进行了修改,导致对同一条数据两次读到的结果不一致:

4.幻读

在同一个事务的两次读取之间,由于其他事务对数据进行了修改,导致第二次读取到第一次不存在数据,或第一次原本存在的数据,第二次却读取不到,就好像之前的读取是 “幻觉” 一样:


4.4 隔离级别

想要解决以上问题,可以通过设置隔离级别来实现:InnoDB 支持以下四个等级的隔离级别,默认隔离级别为可重复读:

在每个级别下,并发问题是否可能出现的情况如下:

隔离级别 脏读 不可重复读 幻读 读未提交(READ UNCOMMITTED) 可能出现 可能 可能 读已提交(READ COMMITTED) 不可能出现 可能 可能 可重复读(REPEATABLE READ) 不可能 不可能 可能 串行化(SERIALIZABLE) 不可能 不可能 不可能

就数据库层面而言,当前任何隔离级别下都不会发生丢失更新的问题,以 InnoDB 存储引擎为例,如果你想要更改表中某行数据,该行数据上必然会加上 X 锁,而对应的表上则会加上 IX 锁,其他任何事务必须等待获取该锁才能进行修改操作。

相关推荐

程序员:JDK的安装与配置(完整版)_jdk的安装方法

对于Java程序员来说,jdk是必不陌生的一个词。但怎么安装配置jdk,对新手来说确实头疼的一件事情。我这里以jdk10为例,详细的说明讲解了jdk的安装和配置,如果有不明白的小伙伴可以评论区留言哦下...

Linux中安装jdk并配置环境变量_linux jdk安装教程及环境变量配置

一、通过连接工具登录到Linux(我这里使用的Centos7.6版本)服务器连接工具有很多我就不一一介绍了今天使用比较常用的XShell工具登录成功如下:二、上传jdk安装包到Linux服务器jdk...

麒麟系统安装JAVA JDK教程_麒麟系统配置jdk

检查检查系统是否自带java在麒麟系统桌面空白处,右键“在终端打开”,打开shell对话框输入:java–version查看是否自带java及版本如图所示,系统自带OpenJDK,要先卸载自带JDK...

学习笔记-Linux JDK - 安装&配置

前提条件#检查是否存在JDKrpm-qa|grepjava#删除现存JDKyum-yremovejava*安装OracleJDK不分系统#进入安装文件目...

Linux新手入门系列:Linux下jdk安装配置

本系列文章是把作者刚接触和学习Linux时候的实操记录分享出来,内容主要包括Linux入门的一些理论概念知识、Web程序、mysql数据库的简单安装部署,希望能够帮到一些初学者,少走一些弯路。注意:L...

测试员必备:Linux下安装JDK 1.8你必须知道的那些事

1.简介在Oracle收购Sun后,Java的一系列产品就被整合到Oracle官网中,打开官网乍眼一看也不知道去哪里下载,还得一个一个的摸索尝试,而且网上大多数都是一些Oracle收购Sun前,或者就...

Linux 下安装JDK17_linux 安装jdk1.8 yum

一、安装环境操作系统:JDK版本:17二、安装步骤第一步:下载安装包下载Linux环境下的jdk1.8,请去官网(https://www.oracle.com/java/technologies/do...

在Ubuntu系统中安装JDK 17并配置环境变量教程

在Ubuntu系统上安装JDK17并配置环境变量是Java开发环境搭建的重要步骤。JDK17是Oracle提供的长期支持版本,广泛用于开发Java应用程序。以下是详细的步骤,帮助你在Ubuntu系...

如何在 Linux 上安装 Java_linux安装java的步骤

在桌面上拥抱Java应用程序,然后在所有桌面上运行它们。--SethKenlon(作者)无论你运行的是哪种操作系统,通常都有几种安装应用程序的方法。有时你可能会在应用程序商店中找到一个应用程序...

Windows和Linux环境下的JDK安装教程

JavaDevelopmentKit(简称JDK),是Java开发的核心工具包,提供了Java应用程序的编译、运行和开发所需的各类工具和类库。它包括了JRE(JavaRuntimeEnviro...

linux安装jdk_linux安装jdk软连接

JDK是啥就不用多介绍了哈,外行的人也不会进来看我的博文。依然记得读大学那会,第一次实验课就是在机房安装jdk,编写HelloWorld程序。时光飞逝啊,一下过了十多年了,挣了不少钱,买了跑车,娶了富...

linux安装jdk,全局配置,不同用户不同jdk

jdk1.8安装包链接:https://pan.baidu.com/s/14qBrh6ZpLK04QS8ogCepwg提取码:09zs上传文件解压tar-zxvfjdk-8u152-linux-...

运维大神教你在linux下安装jdk8_linux安装jdk1.7

1.到官网下载适合自己机器的版本。楼主下载的是jdk-8u66-linux-i586.tar.gzhttp://www.oracle.com/technetwork/java/javase/downl...

window和linux安装JDK1.8_linux 安装jdk1.8.tar

Windows安装JDK1.8的步骤:步骤1:下载JDK打开浏览器,找到JDK下载页面https://d.injdk.cn/download/oraclejdk/8在页面中找到并点击“下载...

最全的linux下安装JavaJDK的教程(图文详解)不会安装你来打我?

默认已经有了linux服务器,且有root账号首先检查一下是否已经安装过java的jdk任意位置输入命令:whichjava像我这个已经安装过了,就会提示在哪个位置,你的肯定是找不到。一般我们在...

取消回复欢迎 发表评论: