事务
事务是逻辑上的一组操作,要么都执行,要么都不执行。
事务的四个特性(ACID):
- 原子性:事务不允许分割,要么全部完成,要么完全不执行。
- 一致性:逻辑上的正确性,即这组操作的结果是符合期望的。具体来说,就是不发生 丢失修改、不能重复读、脏读 这三种错误。
- 隔离性:并发访问数据库时,一个用户的事务不被其他事务干扰。
- 持久性:事务提交之后,对数据库中数据的改变是持久的,即使数据库发生故障也不影响。
并发事务可能会导致以下问题:
- 丢失修改:事务 A 和 B 先后更改数据 x(x = x0),在 A 实际更改 x 之前,B 已经读取了 x 原来的值 x0,然后 A 将 x 更改为 x1,B 的更改是基于 x0,更改后为 x2,这时 x2 将 x1 覆盖了,相当于 事务 A 针对 x 的更改丢失了。
- 脏读:事务 A 读取了 B 更改过的 x,但是 B 因为出错发生了 回滚,这时 A 读取的实际是 无效数据。
- 不可重复读:事务 A 前后两次读取 x,但是在这中间事务 B 更改了 x,导致 A 两次读取的结果不同。
- 幻读:在事务 A 读取符合某个条件的所有记录时,事务 B 增加 了一条符合该条件的记录,这就导致 A 在执行过程中 前后读取的记录可能不一致。
前三种都是 并发事务 在 修改同一份数据 的时候导致的问题,可以通过 对同一个资源加锁 的方式来解决。幻读是由不同事物并发执行时,新增数据 导致的问题,只能通过 事务串行化 来解决。而串行化和并发是矛盾的,所以要在 性能 和 一致性强度 上取得一个平衡,这就涉及到不同的隔离等级。
隔离等级 从低到高有:
-
读未提交:即允许读取未提交的数据。MySQL 事务隔离其实是依靠锁来实现的,加锁自然会带来性能的损失。而读未提交隔离级别是 不加锁 的,可以读到其他事务未提交的数据,但没有办法保证你读到的数据最终一定是提交后的数据,如果中间发生回滚,那就会出现 脏读 问题。
-
读已提交:允许读取已提交的数据,反过来说,就是 如果事务还没有提交,其他事务就不能读取该数据。数据的 读取不加锁,但是数据的 写操作加锁。可以避免脏读,但是不能避免不可重复读、幻读(读的时候其他事务可能会对正在读的数据做更新)
-
可重复读(默认):读取时加锁,直到事务结束才释放。可以避免不可重复读、脏读 问题(两次读取之间不允许别的事务访问相关数据),不能解决幻读问题,因为其他事务仍然可以增加新数据。
-
可串行化:增加 表锁,所有事务 逐个执行,可以避免所有问题。
锁
乐观锁 vs 悲观锁
悲观锁:
- 总是假设最坏的情况 —— 每次拿数据时都认为别人会修改,所以 每次在拿数据时都会上锁。即 共享资源只能同时给一个线程用,释放锁之后其他线程才能用。
- 适用于写操作多的场景。
- 数据库中的 行锁、表锁 都是悲观锁,在操作之前先上锁。Java 中的
synchronized
和ReentrantLock
等独占锁就是悲观锁思想的实现。
乐观锁:
- 总是假设最好的情况 —— 每次拿数据时都认为别人不会修改,所以不会上锁,但是 在更新时会判断一下在此期间有没有其他线程去更新这个数据,可以使用版本号机制和 CAS 算法实现。
- 版本号机制:一般是在数据表中加入一个版本号字段
version
,表示数据被修改的次数。当线程 A 要更新数据时,在读取数据的同时也会读取 version;在提交更新时,只有刚才读取的 version 值和当前数据库中的 version 值相等时才更新,否则重来一遍。 - CAS 算法:即 compare and swap,旨在不使用锁的情况下实现多线程之间的变量同步。详细介绍见后文。
- 版本号机制:一般是在数据表中加入一个版本号字段
- 乐观锁 适用于读操作多的应用,可以提高吞吐量。
java.util.concurrent.atomic
包中的原子变量类就是使用了乐观锁的一种实现方式(CAS)实现的。
CAS 算法:
-
算法过程:涉及三个操作数——需要读写的内存中的值 V、进行比较的值 A、拟写入的新值 B。过程如下:
-
线程 1 和 2 在同一时间并发更新主存的值。
-
线程 1 和 2 都先将主存中的值 V 备份到自己的本地内存中,备份值为 A (预期值)。
-
线程 1 要将 V 更新为 1,线程 2 要将 V 更新为 2。假设线程 1 先获得 CPU。
-
线程 1 更新时先将自己的备份值 A 和主存中的值 V 比较,此时
A=V=0
,即 主存中的值符合预期,则进行更新操作。 -
线程 2 更新时将自己的备份值 A 和 V 比较,此时主存中的值已经被线程 1 更新过了,
A=0, V=1
,主存中的值不满足预期,所以线程 2 不能进行更新操作,进入 自旋 —— 不断重新读取主存中的值并加以备份,此时A=V=1
,满足预期,进行更新操作。
-
-
-
可能出现的问题:
- ABA 问题:
- 如果一个变量 V 初次读取是值为 A,在准备赋值的时候检查到它仍然是 A,那么就能说明它的值没有被其他线程修改过吗?显然不能,其他线程可能将它修改成了 B,然后又改回了 A。但是 CAS 会误认为它没有被修改过。
- 解决方案可以是:在变量值之前追加版本号,更新变量时将版本号加一,这样 A → B → A 就变成了 1A → 2B → 3A。
- JDK 的
AtomicStampedReference
类提供了compareAndSet
方法,首先检查当前引用是否等于预期引用,然后检查当前标志是否等于预期标志,都相等才会更新引用的值和标志的值。
- 循环时间长:如果主存的值不等于预期值,那么会进入 自旋,如果多次自旋,会造成较大的开销。
- 只能保证一个共享变量的原子操作:当涉及多个共享变量时 CAS 无效。JDK 1.5 之后提供了
AtomicReference
类,可以保证 引用对象之间的原子性,可以把多个共享变量放在一个对象里进行 CAS 操作。
- ABA 问题:
行锁 vs 表锁
- MyISAM 支持表锁;InnoDB 支持行锁和表锁,默认为行锁。
- 表锁:对当前访问的整张表加锁,
- 消耗资源少,实现简单,加锁快,不会出现死锁。
- 锁定粒度最大,触发锁冲突的概率高,并发度最低。
- 行锁:只对当前访问的行加锁,
- 减少冲突,并发度高。
- 加锁开销大,加锁慢,会出现死锁。
共享锁 vs 排他锁
- 共享锁:又称读锁,事务在读取记录的时候获取共享锁,允许多个事务同时获取。但对一条记录加共享锁后,不能再加排他锁(共享锁仅和共享锁兼容)。
- 排他锁:又称写锁/独占锁,事务在修改记录的时候获取排他锁,不允许多个事务同时获取。如果一个记录已经被加了排他锁,那其他事务不能再对这条事务加任何类型的锁(排他锁与任何锁都不兼容)。
索引
索引分类
- 按存储结构分类:B 树索引、哈希索引(不能排序,不支持范围查询,不支持多字段查询)、全文索引。
- 从应用层次上划分:主键索引、唯一索引、复合索引、普通索引(普通列上的索引)。
- 根据表记录和索引的排列顺序是否一致来划分
- 聚集索引:顺序一致,B+ 树叶结点存储的数据就是表记录(主键索引)
- 非聚集索引:顺序不一致,B+ 树叶结点存储的数据是主键(也叫二级索引,辅助索引)
InnoDB 的主键索引是聚集索引,唯一索引、普通索引、前缀索引属于非聚集索引。
B+ 树相对 B 树的优势
- B+ 树磁盘读写代价低。B+ 树内部结点不存储数据,占用空间更小,所以同一块磁盘就能容纳更多结点,IO 次数更低。
- 查询效率更稳定。所有查询都在叶子结点命中。
- B+ 树只需要遍历叶结点就能实现整棵树的遍历。
- 所有叶结点形成有序链表,便于范围查询。
聚集索引 vs 非聚集索引
聚集索引:表记录在磁盘上存储的顺序和索引的排列顺序一致。为了保证记录的物理顺序和索引顺序的一致,在记录插入的时候,会对数据页重新排序,所以插入操作比较慢。在叶子结点中存储的数据是表记录。
非聚集索引:表记录的物理顺序和索引顺序不一致。在叶子结点中存储的是索引列和主键,所以使用非聚集索引查询数据时,找到对应叶子结点后,需要通过得到的主键再次查询(这个过程就是回表,但如果发生覆盖索引,就会直接返回对应值)。
覆盖索引,即 需要查询的字段正好是索引的字段,那么直接根据该索引,就可以查到数据了,而无需回表查询。
InnoDB 的主键索引和辅助索引
InnoDB 的主键索引是聚集索引,索引 + 数据 = 表数据文件(.ibd
文件),叶结点保存着 主键值 和对应的 表记录。而在辅助索引中,叶结点只保存着 索引列 和对应的 主键,需要通过这个主键进行二次查询,得到完整记录。
通过主键索引到整条记录,通过辅助索引找到主键。
MyISAM 的主键索引和辅助索引
MyISAM 的主键索引和辅助索引都是 非聚集索引,索引和数据文件是分离的(数据是 .MYD
文件,索引是 .MYI
文件),索引保存的是到数据文件的指针。主索引和辅助索引在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。
联合索引中的最左前缀匹配原则
如果查询条件中存在与 创建联合索引时的最左侧字段 相匹配的字段,就会使用该字段过滤一批数据,直至联合索引中全部字段匹配完成,或者在执行过程中遇到范围查询,如 >
、<
、between
和 以 % 开头的 like 查询 等条件,才会停止匹配。
所以,我们在使用联合索引时,可以将区分度高的字段放在最左边,可以过滤更多数据。
不会应用索引的情况有哪些?
where
使用了like
,但like
后的 模式串以%
开始 不会使用索引。- 如果使用了
or
运算符,第一个条件的字段有索引,但 第二个条件的字段没有索引,那么有索引也不会用。 - 如果
where
条件的字段参与了计算或者使用了函数,不使用索引。 - 数据类型出现隐式转换时,比如给
'123'
类型的字段传入123
,那么不会使用索引。 - 在索引列上使用
is not null
操作,不会使用索引。 - 在索引列上 使用不等号,不会使用索引。
使用索引的注意事项
- 选择合适的字段建立索引
- NOT NULL 字段,允许为 NULL 的字段,数据库较难优化。
- 被 频繁查询 的字段
- 需要 频繁排序 的字段
- 建立联合索引而不是单列索引:索引需要占用磁盘空间,索引修改也需要较多时间。如果是联合索引,多个字段在一个索引上,那么将会节约很大磁盘空间,且修改数据的操作效率也会提升。
- 避免冗余索引:能够命中索引 (a, b) 就肯定能命中索引 (a) ,那么索引 (a) 就是冗余索引。应该尽量扩展已有的索引而不是创建新索引。
优化
MySQL 优化的步骤是什么?
-
通过
show status
命令了解各种类型 SQL 执行的次数,了解到 当前数据库是以更新为主还是查询为主。 -
通过慢查询日志 定位执行效率较低的 SQL 语句。
-
通过
explain sql
命令分析 SQL 语句的执行情况,重点关注分析结果中的type
字段。据此优化查询方式,使type
尽可能接近system
级别。type=const
:表中 只有一条记录匹配,比如使用 主键 或者 唯一索引 进行查询时。type=system
表示表中仅有一条数据,性能最好。type=all
:全表查询select * from xxx
,性能最差。
常用优化策略:
- 充分利用已经存在的索引(避免索引失效的情况)
- 在
select
语句中指定确切的字段名 - 在
insert
语句中指定确切的字段名 - 建议使用预编译语句进行 DB 的操作
- 避免数据类型的隐式转换(这会导致索引失效)
- 不在 where 子句中使用函数
- 合并多个相同的操作,让数据库批量处理,效率更高
- 使用 in 代替 or,因为后者很少会用到索引
大表优化
单表记录数过大时,CRUD 效率会明显降低,常见的优化措施如下:
-
限定查询范围:禁止不带过滤条件的查询语句。
-
读写分离:主库负责写,从库负责读。
-
垂直分区:即数据表 列的拆分,将列较多的表拆分成多个列较少的表。
- 简化表结构,易于维护,且在查询时减少读取的 IO 次数。
- 主键会出现冗余;使事务变得复杂。
-
水平分区:保持 数据表结构不变,通过某种策略将 存储数据分片,每一片的数据分散到不同的表甚至库中,达到分布式的目的。能够支持很大的数据量,但 分片事务难以解决。
尽量不要对数据分片,因为拆分会带来逻辑、部署、运维的各种复杂度。
日志
总的来说:
- redo 日志用于 数据恢复
- binlog 日志用于不同节点之间的 数据同步
- undo 日志用于操作 回滚
redo 日志
redo log 赋予了 InnoDB 崩溃恢复 能力,保证了数据的持久性和完整性。
MySQL 中数据以 页 为单位进行存储,查询一条记录时会从硬盘中加载一整页数据,放入 Buffer Pool 中。后续查询都是先从 Buffer Pool 中查找,没有命中才去磁盘加载,减少了磁盘 IO 开销。更新表数据时也是如此,先看 Buffer Pool 中有没有要更新的数据,有的话就直接在 Buffer Pool 中更新,然后会把「在某个数据页上做了什么修改」记录到 redo log buffer 中,随后伺机 刷盘 到 redo log 文件中。
那么什么时候刷盘呢?innodb_flush_log_at_trx_commit
参数控制刷盘时机,支持三种策略:
- 设置为 0,表示每次提交事务时不进行刷盘。
- 设置为 1(默认),表示 每次提交事务时都进行刷盘。如果事务提交成功,那么就会立即将日志写入磁盘;如果事务执行期间 MySQL 挂了或机器宕机,那么这部分日志就无了,但是因为事务没有提交,日志无了也没有任何影响。
- 设置为 2,表示每次提交事务都只把 redo log buffer 写入 page cache(文件系统缓存)。即如果事务提交成功,会立即将日志写入文件系统缓存(后台还是每秒刷盘一次);所以如果只是 MySQL 挂了,没有影响,如果宕机,会丢失一秒内的修改。
另外,InnoDB 存储引擎有一个后台线程,每隔 1 秒,就会把 redo log buffer 写到 page cache,然后调用 fsync
进行刷盘。
此外,当 redo log buffer 占用空间即将达到 innodb_log_buffer_size
的一半时,后台线程会主动刷盘。
binlog 日志
binlog 用来实现不同 MySQL 之间的 数据同步,保证 数据一致性。
会记录所有 更新操作的逻辑,例如「对 id=2 这一行的 A 字段加 1」。
刷盘机制:事务执行过程中,会把日志写到 binlog cache,提交时将 binlog cache 写入 page cache(write),后面伺机将 page cache 写入磁盘(fsync)。write 和 fsync 的时机,由参数 sync_binlog
控制:
- 0(默认),每次提交事务都只 write,由系统判断何时 fsync。宕机时 page cache 里面的 binlog 会丢失。
- 1,每次提交事务都会 write 和 fsync。
- n(>1),每次提交事务都只 write,累积 n 个事务后才 fsync。
两阶段提交
在执行更新操作的过程中,会记录这两种日志,它们都属于 持久化 的保证,但是侧重点不同:redo log 让 InnoDB 存储引擎有了崩溃恢复能力,而 binlog 保证了 MySQL 集群架构的数据一致性。
redo log 会在执行过程中不断写入,而 binlog 会在事务提交时才写入,写入时机的不同就导致这两份日志之间可能存在不一致的情况 —— 比如写完 redo log 之后,binlog 写入时发生异常,就会导致不一致。为了解决这个问题,InnoDB 存储引擎使用两阶段提交方案 —— 将 redo log 的写入拆分为两个步骤:prepare 和 commit。
本小节的图片来源:https://javaguide.cn/database/mysql/mysql-logs.html
使用两阶段提交后,在写入 binlog 时发生异常,或者在 redo log 的 commit 阶段发生异常,都不会影响一致性。具体见下图:
- 如果在写入 biglog 阶段发生异常,MySQL 根据 redo log 恢复数据时,发现 redo log 还是 prepare 阶段,并且没有对应的 binlog 日志,就会回滚该事务。
- 如果在 redo log 的 commit 阶段发生异常,虽然 redo log 处于 prepare 阶段,但是存在对应的 binlog 日志,所以会提交该事务。
undo log
所有修改操作都会记录到 undo log 中,如果执行过程中出现异常,MySQL 会根据 undo 日志进行回滚,这就保证了事务的 原子性。
引擎
MyISAM vs InnoDB
- MySQL 5.5 版本之前的默认引擎是 MyISAM,5.5 之后是 InnoDB。
- 是否支持 事务和行级锁?
- MyISAM 不支持事务和行级锁,崩溃后无法恢复,但支持全文索引。
- InnoDB 支持事务和行级锁,有崩溃后修复能力。
- 是否支持 外键?
- 只有 InnoDB 支持。
- 是否支持 MVCC(多版本事务控制)?
- 只有 InnoDB 支持,应对高并发事务,MVCC 机制比加锁更高效。
- 索引的实现方式:
- 都使用 B+ 树作为索引的数据结构,但是实现方式不同。
- InnoDB(聚集索引):数据文件本身就是按 B+ Tree 组织的一个索引结构。主键索引中叶节点保存了完整的数据记录;辅助索引中叶结点保存主键的值。
- MyISAM(非聚集索引):索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引相互独立,叶结点都保存着到数据文件的指针。
- 在很多我们已知场景中,InnoDB 的速度都可以让 MyISAM 望尘莫及。