MySQL 索引,锁 再次总结

这些问题你都知道吗?

在这里插入图片描述

关于1、可参考:【mysql 5.7】like ‘XX%‘一定会使用索引吗 & 离散性对like的影响(索引选择性)

什么是索引?

在这里插入图片描述
RDBMS:(Relational Database Management System 关系数据库管理系统)

InnoDB AHI?

自适应哈希索引 — The InnoDB Storage Engine
AHI:Adaptive Hash Index 自适应哈希索引

Hash 可以查找。但是量大的时候,进行范围查找就不太友好了。

MySQL为什么用B+Tree?

(1)、二叉搜索树
可能会发送单向链表的情况。
在这里插入图片描述

(2)、平衡二叉搜索树
在这里插入图片描述

每次都要旋转,左旋,右旋,。。。来保持平衡。
每次查都需要从下往下进行查找(浪费IO),不方便范围搜索。
磁盘 -> 内存 -> 应用(IO开销,每次IO都是16KB)

(3)、B-Tree(B减树、多路搜索树、多叉平衡查找树)
不止可以纵向查找,还可以横向查找。

1
2
3
4
5
6
7
5, 20 的数据区划分

1、 (-∞, 5)
2、 5
3、 (5, 20)
4、 20
5、 (20, +∞)

在这里插入图片描述
(4)、B+Tree(B加树)

1
2
3
4
5
1,28,66 的数据区划分

1、 [1, 28)
2、 [28, 66)
3、 [66, +∞)

P1、P2、P3 都是一个 Page(页) => 16KB
在这里插入图片描述

(5)、联合索引
在这里插入图片描述

InnoDB 页大小默认 16KB

每次取数据都要取 16KB。
在这里插入图片描述

Myisam索引结构

在 .myi(my index) 找到地址,根据地址去 .myd(my data) 文件中找数据。
在这里插入图片描述

myisam 的主键索引(ID索引)与 非主键索引(NAME索引):功能与数据结构都是一致的。
都是从自己的 .myi 中找到地址,根据地址去 .myd 文件中查找数据。
在这里插入图片描述

InnoDB索引结构

叶子节点存储的不再是地址值,而是真正的数据。

.ibd(InnoDB data),把主键索引和数据二合一了。
在这里插入图片描述

非主键索引与主键索引不同。非主键索引的叶子节点存放的并非数据,而是主键索引的值。要获取数据的时要,会再根据主键索引的值来遍历主键索引树,查找到数据。也就是一个”回表“的过程。
在这里插入图片描述

聚集索引

可以参考:15.6.2.1 Clustered and Secondary Indexes
在这里插入图片描述
在这里插入图片描述

索引字段的离散型

在这里插入图片描述
答:”姓名“。

为什么?
离散性越好的列作为索引。离散性:count distinct col : count col。这什么意思?例如年龄就是2:8。一个列,重复的越少,越好作为索引。

例如:下面以离散性最差的作为索引,看有什么影响。查找 1 的时候,会把所有为 1 的都查询出来。
在这里插入图片描述

创建单索引、联合索引

在这里插入图片描述
联合索引,就是 name 相同,再按照 phoneNum 进行查找,phoneNum相同的话再按照 age 进行查找。
若查找条件没有 name 的话,就不会使用此索引。这被称为”最左前缀原则“

思考1:怎样创建索引

问:对于下面两个经常要查询的语句,建立索引。给出三种方案,哪个最优?
A、建立 (name)
B、建立 (name,phoneNum)
C、建立 (name) 和 (name,phoneNum)
在这里插入图片描述
答:”B、建立 (name,phoneNum)“。

因为第二个 SQL用到 phoneNum,所以 A 不太特别支持。因为建立了 B 就包含了 A,没必要再建立 A 了,会造成浪费。

思考2:索引一样吗

问:下面两个索引一样吗?
在这里插入图片描述
答:不一样。因为最左前缀原则,比较顺序是不一样的。所以,它们两个是完全不一样的索引树。

思考3:是否用到索引

下面四个 SQL 有没有用到联合索引,用到几列,未用到几列,为什么?
在这里插入图片描述

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# SQL1:用到两列,name,phoneNum。
例如:
name --- 几号楼
phoneNum --- 层数
age --- 几号房间

此时,我要找 7 号楼,3层以上的2号房间。
你只能定位到 7号楼,3层以上的楼层,然后依次去遍历每一层的2号房间。
所以:范围查询要放在最后。


# SQL2:用到一列,name(最左前缀原则)

# SQL3:未用到索引(最左前缀原则)

# SQL4:用到两列,name,phoneNum
MySQL优化器会自动优化,相当于执行 name=xx and phoneNum=xx
优化器也会耗资源,所以:离散性好的条件写在左边。

MySQL逻辑架构图

存储引擎就是 myisam,InnoDB,memory。
在这里插入图片描述

思考4:哪个执行效率高

在这里插入图片描述

答案:”4“

1
2
3
4
5
6
7
8
9
10
11
12
13
# 1:遍历两个索引:唯一索引,主键索引
根据唯一索引,找到叶子节点,叶子节点中只保存的是唯一索引的值和主键的值(userNum,id)。
但要查询的是 * ,所以拿着主键去主键索引的叶子节点中找数据。

# 2:遍历两个索引:唯一索引,主键索引
同上,但要查询的是 name ,所以拿着主键去主键索引的叶子节点中找数据 name 列。

# 3:遍历两个索引:联合索引,主键索引
根据联合索引,找到叶子节点,叶子节点中只保存的是联合索引的值和主键的值(name,phoneNum,id)。
但要查询的是 userNum ,所以拿着主键去主键索引的叶子节点中找数据 userNum 列。

# 4:遍历一个索引:联合索引
根据联合索引找到叶子节点,联合索引叶子节点有字段 name,phoneNum,也有主键 id,所以不需要再去遍历主键索引。

覆盖索引

覆盖索引无需回表。(可参考上面的 思考4 的 SQL 4 就是覆盖索引)
在这里插入图片描述

最左前缀原则

在这里插入图片描述
答:会用到索引。
为什么呢?

因为内部会把进行 ASCII 码转换。a=97,b=98,c=99。bba=989897,bbc=989899。

假设有索引 panfeng1,panfeng2,panfeng3,… ,panfeng999。
我使用 like ‘panfeng%’ 就不会加速我们的查询,因为此时的离散性,选择性是极差的。

所以,对应上面的问题1 是需要根据业务进行考量。如果离散性好的话会根据最左前缀原则用到索引。
在这里插入图片描述

三星索引

在这里插入图片描述
对于 1: 没什么好说的。

对于2:默认就是有序的,根据主键排序(order by id)。但如果你在 SQL 写了(order by name),则会进行二次排序,效率降低。

对于3:在上面提到了,避免回表。

and or union

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
主键索引:id
唯一索引:name

select * from user where id=? and name=?
会用到几个索引?1 2? ===> 答:2
MySQL 5.0 会用到 2个索引, index_merge。两个索引过滤的更快。


select * from user where id=? or name=?
MySQL 5.0 之后用到 2个。


如果只有主键索引 id,而不存在唯一索引 name的话。
对于 SQL select * from user where id=? or name=? ,会导致整个索引失效(这个是执行器、优化器做的)
所以,会有一条 SQL 优化说:建议把 or 改为 union 。
union 也就相当于写了两个SQL 使用 union 拼接,不会像 or 一样name没有索引从而导致 id主键索引失效。
使用 union最起码 id是有效的,是可以用到索引的。

索引下推

1
2
3
4
5
6
7
8
9
10
11
12
13
14
icp:index condition pushdown

联合索引(name,age)
select * from user where name like 'tao%' and age>18

1、陶------回表------18(根据最左前缀原则,先查姓陶的,再进行回表,再过滤 18岁以上的)
2、陶------18------回表(根据最左前缀原则,先查姓陶的,再过滤 18岁以上的,再进行回表)

上面两种方式哪个效率高呢?
答案:2。为什么?

因为条件过滤的越多越好,回表的数据越少越好。
在 where 后面条件执行完之后,才会进行回表操作。
遍历联合索引(name,age)比 主键索引(id)更简单一些。

隐藏主键

我们都会听说 MySQL 一定要自己创建主键。
如果没有创建主键索引的话,MySQL 默认会为我们创建一条主键索引 rownum。

1
2
3
4
5
6
7
1、int 4byte。默认主键索引 6byte。。。资源浪费

2、update ------ 创建主键 行锁。默认主键 升级成表锁。

3、如果勾选自增。
int max = 2^32-1 行记录达到最大值后,insert into 插入时报错。
隐藏主键,达到最大值再进行插入会默认从 1 开始,并且覆盖 1 的记录。

事务四大特性(ACID)

兄弟,你要想深入学习,可以参考:MySQL 事务的 ACID 原理

1
2
3
4
原子性 Atomicity
一致性 Consistency
隔离性 Isolation
持久性 Durability

在这里插入图片描述

脏、不可重复、幻

事务并发的问题

  • 脏读:读取了其他并发事务未提交的数据。
    在这里插入图片描述
  • 不可重复读:读取了其他并发事务提交的数据。针对 update 和 delete 。
    在这里插入图片描述
  • 幻读:读取了其他并发事务提交的数据。针对 insert 。
    在这里插入图片描述
    15.7.4 Phantom Rows — 官方文档
    幻读:MySQL 官方称为“虚拟的行”。声明一下,幻读在可重复读的模式下被 MySQL 5.5后解决了,使用 MVCC 快照读,不会出现两次读取不一样的情况。
    在这里插入图片描述

事务隔离级别

在这里插入图片描述

RC:READ COMMITTED 读已提交(Oracle 默认事务隔离级别)
RR:REPEATABLE READ 可重复读(MySQL 默认事务隔离级别)

READ UNCOMMITTED:读未提交 ===> 一般没人使用,因为它接不了脏读、不可重复读、幻读。

SERIALIZABLE:串行化 ===> 可以想象成,你发送给 MySQL 的 SQL 语句,它内部在 for 循环执行,而不是并行执行的。所以会拉低效率,一般都不采用这种方式。你也可以想象成:有很多个坑位,但是呢,你只允许来的人使用 1号坑位,人很多也要等前面人解决完事,你再进行解决,不能同时进行。(哇,这句话好大的味道。)

在这里插入图片描述

RR、RC结果

在这里插入图片描述
RC:V1=18,V2=20(别人已经提交,会触发我事务内的改变),V3=20
RR:V1=18,V2=18(即使别人已经提交,因为我是可重复读,所以不会触发我事务内的改变),V3=20

LBCC MVCC

1
2
3
LBCC:Lock-Based Concurrent Control 基于锁并发控制

MVCC:Multi-Version Concurrent Control 多版本并发控制

并发事务的隔离就是用这两个(LBCC + MVCC)来实现的,现在知道了吧,下面来具体阐述。

InnoDB Locking

15.7.1 InnoDB Locking 官方文档
在这里插入图片描述
注意:这些锁是互相有交集的。什么意思?比如:我可以是男的,也可以是 Java开发。

锁:又分模式(思想),和算法(实现)。下面来分开进行讲解。

  • 先上一张图,让你明白锁模式,和锁算法。
    在这里插入图片描述

  • 再上一张图,让你明白这些锁的分支、含义。先了解一下,下面来阐述为什么有这些东西。
    在这里插入图片描述

锁的模式

表锁、行锁

1
2
3
4
5
6
表锁与行锁的区别:

锁的粒度:表锁 > 行锁
加锁效率:表锁 > 行锁
冲突概率:表锁 > 行锁
并发性能:表锁 < 行锁

这些不用说,简单也可以理解,那怎么触发呢?
在这里插入图片描述
在这里插入图片描述

锁的排查指令

在这里插入图片描述

共享锁

在这里插入图片描述
你可以想象成进出动物园的过程:每个人都可以进动物园(开启共享锁),但是要把动物放出来(修改数据),就需要所有人都出动物园(关闭共享锁)。

排它锁

在这里插入图片描述

意向锁

了解即可。不是我们手动控制的,而是 MySQL 内部自己的处理逻辑。

你表中任何一行加了 共享锁 或 排它锁,意向锁这盏灯都会亮。
表中任何一行都没加 共享锁 或 排它锁,意向锁这盏灯才会灭。

为什么要加入意向锁?
要进行表锁的时候,判断意向锁这盏灯是否亮起即可,不用每一行都进行遍历。
在这里插入图片描述

锁的算法

在这里插入图片描述

锁的区间

临键 = 记录 + 间隙
在这里插入图片描述

记录锁

在这里插入图片描述
例如:下面 4 会一直阻塞,等待事务1 进行提交或回滚。此时暂停,执行 5 则可以成功。
在这里插入图片描述
在这里插入图片描述

间隙锁

在这里插入图片描述
答:id>15 for update 会锁 (11,+∞) 这个范围。

数据库中只有 1,5,9,11 四条记录。
事务1、2都开启事务。
事务1 执行 id>15 for update 之后,但未提交,也没回滚。
此时,事务2执行 insert into 插入 12,13,14,15,16,17,18,19,20,。。。 都会阻塞。

对于下面,开启事务1、事务2:
(1)、执行了1、2、3,再执行4的时候会阻塞,暂停4,再执行5的时候不会阻塞。
(3)、执行了1、2、6,再执行 7 或 8 都会阻塞。因为间隙锁是以真实的数据记录进行划分区间的,这里 id>15 命中了(11,+∞) 这个范围,而13、20都属于 这个范围,所以效果是一样的,都会被阻塞。
在这里插入图片描述
在这里插入图片描述

临键锁

在这里插入图片描述

如下:
(1)、执行1、2、3之后,再执行 4 或 5 都会失败。
(2)、执行1、2、3之后,再执行 6 也会失败。

为什么呢?
因为 id>5 and id<10 for update 命中的是 (5,9] (9,11] 的并集,也就是(5,11] 。所以,这个范围包含了 6,8,11。
在这里插入图片描述
在这里插入图片描述

插入意向锁

插入意向锁在上面已经演示过了,也可以参考官方文档:插入意向锁
在这里插入图片描述

自增锁

在这里插入图片描述
开启两个事务:
对于一个空表,事务1插入两条数据,事务2插入两条数据。
理想是事务1插入主键(1、2),事务2插入主键(3、4)。
但可能存在跳票的问题:事务1插入主键(1、4),事务2插入主键(2、3)。

改为 0 的话,会出现锁表的情况,会拉低高并发的性能。
在这里插入图片描述

====================

参考官方文档:
AUTO-INC Locks
15.6.1.6 InnoDB 中的 AUTO_INCREMENT 处理

innodb_autoinc_lock_mode 变量 有三种可能的设置 。设置为 0、1 或 2,分别表示 “传统”、“连续”或 “交错”锁定模式。
从 MySQL 8.0 开始,交错锁模式 ( innodb_autoinc_lock_mode=2) 是默认设置。
在 MySQL 8.0 之前,连续锁定模式是默认的 ( innodb_autoinc_lock_mode=1)。

当前读、快照读

在MySQL默认隔离级别下,假设原本 age=18 ,执行结果 Q1 = ? Q2 = ?
在这里插入图片描述
答:Q1=20,Q2=18。为什么?这是快照读,当前读的区别。

  • 当前读
    像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读,为什么叫当前读?就是它读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁

  • 快照读
    像不加锁的select操作就是快照读,即不加锁的非阻塞读;快照读的前提是隔离级别不是串行级别,串行级别下的快照读会退化成当前读;之所以出现快照读的情况,是基于提高并发性能的考虑,快照读的实现是基于多版本并发控制,即MVCC,可以认为MVCC是行锁的一个变种,但它在很多情况下,避免了加锁操作,降低了开销;既然是基于多版本,即快照读可能读到的并不一定是数据的最新版本,而有可能是之前的历史版本。

说白了MVCC就是为了实现读-写冲突不加锁,而这个读指的就是快照读, 而非当前读,当前读实际上是一种加锁的操作,是悲观锁的实现。

在这里插入图片描述

开启事务建立快照

start transaction 等同于 begin

start transaction 和 start transaction with consistent snapshot

事务开始时间点,分为两种情况:

1)start transaction 时,是第一条语句的执行时间点,就是事务开始的时间点,第一条select语句建立一致性读的snapshot;

2)start transaction with consistent snapshot 时,则是立即建立本事务的一致性读snapshot,当然也开始事务了;

幻读解决方案

通过上面所说的快照读,当前读来解决的。
在这里插入图片描述