目录
一、事务隔离级别
1.概述
1.读未提交 - 读到其它事务未提交的数据(最新的版本)
错误现象:有脏读、不可重复读、幻读现象
2.读已提交(RC) - 读到其它事务已提交的数据(最新已提交的版本)
错误现象:有不可重复读、幻读现象
使用场景:希望看到最新的有效值
3.可重复读(RR) - 在事务范围内,多次读能够保证一致性(快照建立时最新已提交版本)
错误现象:有幻读现象,可以用加锁避免
使用场景:事务内要求更强的一致性,但看到的未必是最新的有效值
4.串行化 - 在事务范围内,仅有读读可以并发,读写或写写会阻塞其它事务,用这种办法保证更强的一致性
错误现象:无
2.错误现象
脏读现象
脏读:一个事务读取到了另外一个事务没有提交的数据
tx1 | tx2 |
set session transaction isolation level read uncommitted; | |
start transaction; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ | |
start transaction; | |
update account set balance = 2000 where accountNo=1; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 2000 | | 2 | 1000 | +-----------+---------+ |
-
tx2 未提交的情况下,tx1 仍然读取到了它的更改
不可重复读现象
不可重复读:一个事务读取到了另外一个事务修改的数据(修改)
tx1 | tx2 |
set session transaction isolation level read committed; | |
start transaction; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ | |
update account set balance = 2000 where accountNo=1; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 2000 | | 2 | 1000 | +-----------+---------+ |
-
tx1 在同一事务内,两次读取的结果不一致,当然,此时 tx2 的事务已提交
幻读现象
幻读(虚读):一个事务读取到了另外一个事务新增的数据(新增)
tx1 | tx2 |
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ | |
insert into account values(3, 1000); | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ | |
insert into account values(3, 5000); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' |
-
tx1 查询时并没有发现 3 号账户,执行插入时却发现主键冲突异常,就好像出现了幻觉一样
使用 for update 加锁避免幻读现象
tx1 | tx2 |
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ | |
select * from account where accountNo=3 for update; | |
insert into account values(3, 1000); /* 阻塞 */ | |
insert into account values(3, 5000); |
-
在 for update 这行语句执行时,虽然此时 3 号账户尚不存在,但 MySQL 在 repeatable read 隔离级别下会用间隙锁,锁住 2 号记录与正无穷大之间的间隙
-
此时 tx2 想插入 3 号记录就不行了,被间隙锁挡住了
使用串行读避免幻读现象
tx1 | tx2 |
set session transaction isolation level serializable; | |
start transaction; | |
select * from account; /* 此时已经加了读锁 */ +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ | |
insert into account values(3, 1000); /* 阻塞 */ | |
insert into account values(3, 5000); |
- 串行读隔离级别下,普通的 select 也会加共享读锁,其它事务的查询可以并发,但增删改就只能阻塞了
二、快照读与当前读
- 当前读,即读取最新提交的数据
- select … for update
- insert、update、delete,都会按最新提交的数据进行操作
- 快照读,读取某一个快照建立时(可以理解为某一时间点)的数据
- 快照读主要体现在 select 时,不同隔离级别下,select 的行为不同
- 在 Serializable 隔离级别下 - 普通 select 也变成当前读
- 在 RC 隔离级别下 - 每次 select 都会建立新的快照
- 在 RR 隔离级别下
- ①事务启动后,首次 select 会建立快照
- ②如果事务启动选择了 with consistent snapshot,事务启动时就建立快照
- ③基于旧数据的修改操作,会重新建立快照
RR 下,快照建立时机 – 第一次 select 时
tx1 | tx2 |
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; /* 此时建立快照 */ +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ | |
update account set balance = 2000 where accountNo=1; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ |
-
快照一旦建立,以后的查询都基于此快照,因此 tx1 中第二次 select 仍然得到 1 号账户余额为 1000
如果 tx2 的 update 先执行
tx1 | tx2 |
set session transaction isolation level repeatable read; | |
start transaction; | |
update account set balance = 2000 where accountNo=1; | |
select * from account; /* 此时建立快照 */ +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 2000 | | 2 | 1000 | +-----------+---------+ |
RR 下,快照建立时机 – 事务启动时
- 如果希望事务启动时就建立快照,可以添加 with consistent snapshot 选项
tx1 | tx2 |
set session transaction isolation level repeatable read; | |
start transaction with consistent snapshot;/* 此时建立快照 */ | |
update account set balance = 2000 where accountNo=1; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ |
RR 下,快照建立时机 – 修改数据时
tx1 | tx2 |
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; /* 此时建立快照 */ +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 1000 | | 2 | 1000 | +-----------+---------+ | |
update account set balance=balance+1000 where accountNo=1; | |
update account set balance=balance+1000 where accountNo=1; | |
select * from account; +-----------+---------+ | accountNo | balance | +-----------+---------+ | 1 | 3000 | | 2 | 1000 | +-----------+---------+ |
- tx1 内的修改必须重新建立快照,否则,就会发生丢失更新的问题
快照读与当前读 – 小结
- 当前读,即读取最新提交的数据,查询时需要加锁
- 快照读,读取某一个快照建立时的数据,无需加锁,读取的是历史数据(原理是回滚段)
三、MySQL 存储引擎
InnoDB
-
索引分为聚簇索引与二级索引
- 聚簇索引:主键值作为索引数据,叶子节点还包含了所有字段数据,索引和数据是存储在一起的
- 二级索引:除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据,叶子节点还包含了主键值
-
支持事务
- 通过 undo log 支持事务回滚、当前读(多版本查询)
- 通过 redo log 实现持久性
- 通过两阶段提交实现一致性
- 通过当前读、锁实现隔离性
-
支持行锁、间隙锁
-
支持外键
MyISAM
-
索引只有一种
- 被索引字段值作为索引数据,叶子节点还包含了该记录数据页地址,数据和索引是分开存储的
-
不支持事务,没有 undo log 和 redo log
-
仅支持表锁
-
不支持外键
-
会保存表的总行数
不同存储引擎索引区别
- InnoDB
- 聚簇索引:主键值作为索引数据,叶子节点还包含了所有字段数据
- 二级索引:被索引的字段值作为索引数据,叶子节点还包含了主键值
- MyISAM
- 被索引字段值作为索引数据,叶子节点还包含了该记录数据页地址
四、索引
1.为什么 MySQL 采用 B+ 树索引 (常见索引)
- 哈希索引
- 理想时间复杂度为 O(1)
- 适用场景:适用于等值查询的场景,内存数据的索引
- 典型实现:Redis,MySQL 的 memory 引擎
- 平衡二叉树索引
- 查询和更新的时间复杂度都是 O(log(n))
- 适用场景:内存数据的索引,但不适合磁盘数据的索引,可以认为树的高度决定了磁盘 I/O 的次数,百万数据树高约为 20
- BTree 索引
- BTree 其实就是 n 叉树,分叉多意味着节点中的孩子(key)多,树高自然就降低了
- 分叉数由页大小和行(包括 key 与 value)大小决定
- 假设页大小为 16k,每行 40 个字节,那么分叉数就为 16k / 40 ≈ 410
- 而分叉为 410,则百万数据树高约为3,仅 3 次 I/O 就能找到所需数据
- 局部性原理:每次 I/O 按页为单位读取数据,把多个 key 相邻的行放在同一页中(每页就是树上一个节点),能进一步减少 I/O
- B+ 树索引
- 在 BTree 的基础上做了改进,索引上只存储 key,这样能进一步增加分叉数,假设 key 占 13 个字节,那么一页数据分叉数可以到 1260,树高可以进一步下降为 2
2.BTree vs B+Tree
- BTree key 及 value 在每个节点上,无论叶子还是非叶子节点,而 B+Tree 普通节点只存 key,叶子节点才存储 key 和 value
- B+Tree 叶子节点用链表连接,可以方便范围查询及全表遍历
- 无论 BTree 还是 B+Tree,每个叶子节点到根节点距离都相同,B+Tree 必须到达叶子节点才能找到 value
- 注:这两张图都是仅画了 key,未画 value
3.B+Tree 新增key(以 5 阶为例)
4.B+Tree 查询key
5.B+Tree 删除key
6.命中索引要注意什么?
索引用于排序
/* 测试单列索引并不能在多列排序时加速 */
create index first_idx on big_person(first_name);
create index last_idx on big_person(last_name);
explain select * from big_person order by last_name, first_name limit 10;
/* 多列排序需要用组合索引 */
alter table big_person drop index first_idx;
alter table big_person drop index last_idx;
create index last_first_idx on big_person(last_name,first_name);
/* 多列排序需要遵循最左前缀原则, 第1个查询可以利用索引,第2,3查询不能利用索引 */
explain select * from big_person order by last_name, first_name limit 10;
explain select * from big_person order by first_name, last_name limit 10;
explain select * from big_person order by first_name limit 10;
/* 多列排序升降序需要一致,查询1可以利用索引,查询2不能利用索引*/
explain select * from big_person order by last_name desc, first_name desc limit 10;
explain select * from big_person order by last_name desc, first_name asc limit 10;
最左前缀原则
若建立组合索引 (a,b,c),则可以利用到索引的排序条件是:
order by a
order by a, b
order by a, b, c
索引用于 where 筛选
/* 模糊查询需要遵循字符串最左前缀原则,查询2可以利用索引,查询1,3不能利用索引 */
explain SELECT * FROM big_person WHERE first_name LIKE 'dav%' LIMIT 5;
explain SELECT * FROM big_person WHERE last_name LIKE 'dav%' LIMIT 5;
explain SELECT * FROM big_person WHERE last_name LIKE '%dav' LIMIT 5;
/* 组合索引需要遵循最左前缀原则,查询1,2可以利用索引,查询3,4不能利用索引 */
create index province_city_county_idx on big_person(province,city,county);
explain SELECT * FROM big_person WHERE province = '上海' AND city='宜兰县' AND county='中西区';
explain SELECT * FROM big_person WHERE county='中西区' AND city='宜兰县' AND province = '上海';
explain SELECT * FROM big_person WHERE city='宜兰县' AND county='中西区';
explain SELECT * FROM big_person WHERE county='中西区';
/* 函数及计算问题,一旦在字段上应用了计算或函数,都会造成索引失效。查询2可以利用索引,查询1不能利用索引 */
create index birthday_idx on big_person(birthday);
explain SELECT * FROM big_person WHERE ADDDATE(birthday,1)='2005-02-10';
explain SELECT * FROM big_person WHERE birthday=ADDDATE('2005-02-10',-1);
/* 隐式类型转换问题
* 查询1会发生隐式类型转换等价于在phone上应用了函数,造成索引失效
* 查询2字段与值类型相同不会类型转换,可以利用索引
*/
create index phone_idx on big_person(phone);
explain SELECT * FROM big_person WHERE phone = 13000013934;
explain SELECT * FROM big_person WHERE phone = '13000013934';
最左前缀原则(leftmost prefix)
若建立组合索引 (a,b,c),则可以利用到索引的查询条件是:
where a = ?
where a = ? and b = ? (注意与条件的先后次序无关,也可以是 where b = ? and a = ?,只要出现即可)
where a = ? and b = ? and c = ? (注意事项同上)
不能利用的例子:
where b = ?
where b = ? and c = ?
where c = ?
特殊情况:
where a = ? and c = ?(a = ? 会利用索引,但 c = ? 不能利用索引加速,会触发索引条件下推)
索引条件下推
/* 查询 1,2,3,4 都能利用索引,但 4 相当于部分利用了索引,会触发索引条件下推 */
explain SELECT * FROM big_person WHERE province = '上海';
explain SELECT * FROM big_person WHERE province = '上海' AND city='嘉兴市';
explain SELECT * FROM big_person WHERE province = '上海' AND city='嘉兴市' AND county='中西区';
explain SELECT * FROM big_person WHERE province = '上海' AND county='中西区';
索引条件下推
MySQL 执行条件判断的时机有两处:
服务层(上层,不包括索引实现)
引擎层(下层,包括了索引实现,可以利用)
上面查询 4 中有 province 条件能够利用索引,在引擎层执行,但 county 条件仍然要交给服务层处理
在 5.6 之前,服务层需要判断所有记录的 county 条件,性能非常低
5.6 以后,引擎层会先根据 province 条件过滤,满足条件的记录才在服务层处理 county 条件
我们现在用的是 5.6 以上版本,所以没有体会,可以用下面的语句关闭索引下推优化,再测试一下性能
SET optimizer_switch = 'index_condition_pushdown=off';
SELECT * FROM big_person WHERE province = '上海' AND county='中西区';
二级索引覆盖
explain SELECT * FROM big_person WHERE province = '上海' AND city='宜兰县' AND county= '中西区';
explain SELECT id,province,city,county FROM big_person WHERE province = '上海' AND city='宜兰县' AND county='中西区';
根据查询条件查询 1,2 都会先走二级索引,但是二级索引仅包含了 (province, city, county) 和 id 信息
-
查询 1 是 select *,因此还有一些字段二级索引中没有,需要回表(查询聚簇索引)来获取其它字段信息
-
查询 2 的 select 中明确指出了需要哪些字段,这些字段在二级索引都有,就避免了回表查询
其它注意事项
-
表连接需要在连接字段上建立索引
-
不要迷信网上说法,具体情况具体分析
例如:
create index first_idx on big_person(first_name);
/* 不会利用索引,因为优化器发现查询记录数太多,还不如直接全表扫描 */
explain SELECT * FROM big_person WHERE first_name > 'Jenni';
/* 会利用索引,因为优化器发现查询记录数不太多 */
explain SELECT * FROM big_person WHERE first_name > 'Willia';
/* 同一字段的不同值利用 or 连接,会利用索引 */
explain select * from big_person where id = 1 or id = 190839;
/* 不同字段利用 or 连接,会利用索引(底层分别用了两个索引) */
explain select * from big_person where first_name = 'David' or last_name = 'Thomas';
/* in 会利用索引 */
explain select * from big_person where first_name in ('Mark', 'Kevin','David');
/* not in 不会利用索引的情况 */
explain select * from big_person where first_name not in ('Mark', 'Kevin','David');
/* not in 会利用索引的情况 */
explain select id from big_person where first_name not in ('Mark', 'Kevin','David');
- 以上实验基于 5.7.27,其它如 !=、is null、is not null 是否使用索引都会跟版本、实际数据相关,以优化器结果为准
五、查询语句执行流程
执行 SQL 语句 select * from user where id = 1 时发生了什么
- 连接器:负责建立连接、检查权限、连接超时时间由 wait_timeout 控制,默认 8 小时
- 查询缓存:会将 SQL 和查询结果以键值对方式进行缓存,修改操作会以表单位导致缓存失效
- 分析器:词法、语法分析
- 优化器:决定用哪个索引,决定表的连接顺序等
- 执行器:根据存储引擎类型,调用存储引擎接口
- 存储引擎:数据的读写接口,索引、表都在此层实现
六、undo log 与 redo log
undo log 的作用有两个:
- 回滚数据,以行为单位,记录数据每次的变更,一行记录有多个版本并存
- 多版本并发控制,即快照读(也称为一致性读),让查询操作可以去访问历史版本
1. 每个事务会按照开始时间,分配一个单调递增的事务编号 trx id
2. 每次事务的改动都会以行为单位记入回滚日志,包括当时的事务编号,改动的值等
3. 查询操作,事务编号大于自己的数据是不可见的,事务编号小于等于自己的数据才是可见的
* 例如图中红色事务看不到 trx id=102 以及 trx id=101 的数据,只有 trx id=99 的数据对它可见
redo log
redo log 的作用主要是实现 ACID 中的持久性,保证提交的数据不丢失
- 它记录了事务提交的变更操作,服务器意外宕机重启时,利用 redo log 进行回放,重新执行已提交的变更操作
- 事务提交时,首先将变更写入 redo log,事务就视为成功。至于数据页(表、索引)上的变更,可以放在后面慢慢做
- 数据页上的变更宕机丢失也没事,因为 redo log 里已经记录了
- 数据页在磁盘上位置随机,写入速度慢,redo log 的写入是顺序的速度快
- 它由两部分组成,内存中的 redo log buffer,磁盘上的 redo log file
- redo log file 由一组文件组成,当写满了会循环覆盖较旧的日志,这意味着不能无限依赖 redo log,更早的数据恢复需要 binlog
- buffer 和 file 两部分组成意味着,写入了文件才真正安全,同步策略由下面的参数控制
- innodb_flush_log_at_trx_commit
- ①0 - 每隔 1s 将日志 write and flush 到磁盘
- ②1 - 每次事务提交将日志 write and flush(默认值)
- ③2 - 每次事务提交将日志 write,每隔 1s flush 到磁盘,意味着 write 意味着写入操作系统缓存,如果 MySQL 挂了,而操作系统没挂,那么数据不会丢失
七、你对 MySQL 的锁了解吗 (锁)
1. 全局锁
用作全量备份时,保证表与表之间的数据一致性
如果不加任何包含,数据备份时就可能产生不一致的情况,如下图所示
- flush tables with read lock;
使用全局读锁锁定所有数据库的所有表。这时会阻塞其它所有 DML 以及 DDL 操作,这样可以避免备份过程中的数据不一致。接下来可以执行备份,最后用 unlock tables 来解锁
但这属于比较重的操作,可以使用 --single-transaction 参数来完成不加锁的一致性备份(仅针对 InnoDB 引擎的表)
- mysqldump --single-transaction -uroot -p test > 1.sql