首页  思维导图  详情

数据库

2021-07-29 17:28:29   84  举报





AI智能生成

MySQL,Redis

MySQL

redis

数据库

作者其他创作

大纲/内容

Redis

简介

K-V型数据库，因为Redis把数据存在内存中，所以读写效率非常快

应用：缓存，分布式锁

C 语言写的

Redis支持事务，持久化，分布式方案等

https://try.redis.io/

为什么Redis

高性能

从缓存即内存中读响应快

即提高响应速度

用户第一次比较慢，从数据库即磁盘中读取，
然后将其数据信息缓存，下次就快了

高并发

多并发下
直接操作缓存承受的请求远远大于数据库

为了提升用户体验以及应对更多的用户

缓存

本地缓存

Map等

不支持持久化

随JVM生命周期结束而结束

多实例各自保存缓存，无一致性

轻量，速度快，存储容量较小

分布式缓存

Redis/Memcached

支持持久化

多实例共享同一份缓存数据，数据一致性

高可用，系统较复杂

线程模型

Redis内部使用一个
文件事件处理器

该处理器是单核的
所以Redis是单线程模型

包含

多个Socket

IO多路复用

使用 I/O 多路复用机制同时监听多个文件描述符的可读和可写状态

文件事件分配器

事件处理器

使用队列收集监听的socket

基于 Reactor 模式来设计了一套高效的事件处理模型

同时使用IO多路复用同时监听多个socket
然后根据不同的socket来选择对应的事件处理器

多个线程使用一个IO资源发送消息

多路网络连接复用一个io线程

多 > 一 > 多

https://www.zhihu.com/question/32163005

强大之处

支持更丰富的数据类型

string，list，set，zset,hash

hash存储对象

hset,hget

hset userInfoKey name "guide" description "dev" age "24"

list->lrange:高效分页

lpush,lpop,rpush,rpop

rpush myList value1 # 向 list 的头部（右边）添加元素

r为头部，l为尾部

双向链表

set自动排重

sadd,spop,smembers

sadd mySet value1 value2 # 添加元素进去

轻易实现求交集、并集、差集

zset：sorted set

zadd,zrem

zadd myZset 3.0 value1

多了一个权重参数score

刷直播礼物

实现

ziplist：
   元素数量少于128的时候
   每个元素的长度小于64字节

skiplist(跳表)
   不满足上述两个条件就会使用跳表

ziplist:压缩列表是为节约内存而开发的顺序性数据结构

跳表

为了解决有序链表的查找使用二分法

本质是解决查找问题

跳跃表的遍历总是从高层开始，
然后随着元素值范围的缩小，慢慢降低到低层。

每间隔之间使用一个指针指向
一层层的迭代
2: 1->5
1: 1->3->5
0: 1->2->3->4->5

如果碰到比待查找数据大的节点时
再到下一层链表中进行查找，

上面每一层链表的节点个数
是下面一层的节点个数的一半

插入数据存在问题。需要调整插入的节点后面的所有节点，
删除数据也有同样的问题

skiplist 为了避免这一问题插入删除问题
它不要求上下相邻两层链表之间的节点个数有严格的对应关系，
而是为每个节点随机出一个层数(level)

为什么不B+树

https://www.cnblogs.com/aspirant/p/11704530.html

https://blog.csdn.net/idwtwt/article/details/80233859

https://mp.weixin.qq.com/s/NOsXdrMrWwq4NTm180a6vw

string

set,get,expire,ttl,mset,mget

del,strlen

常用命令

FLUSHALL

输入添加命令即创建数据库

key

一般为==表名:列名:主键名:主键值

支持持久化

写入磁盘

单线程IO多路复用模型
为什么快

纯内存操作

C语言实现，优化过的数据结构

单线程非堵塞IO
减少多线程频繁上下文切换的问题

基于非阻塞的IO多路复用机制

为什么单线程

4.0 之后的版本加入了对多线程的支持。

提高网络 IO 读写性能

充分利用CPU，提高效率

单线程编程容易并且更容易维护；
Redis 的性能瓶颈不在CPU ，主要在内存和网络IO
多线程就会存在死锁、线程上下文切换等问题，甚至会影响性能。

伪并行

使用 I/O 多路复用并发处理来自客户端的多个连接
同时等待多个连接发送的请求。

过期时间
怎么删除

Redis支持为键设置expire time

为什么设置

短信验证码有效性

token时间有效性

内存是有限的

如何判断：过期字典

键指向Redis数据库中的某个key

值指向过期时间

定期删除+惰性删除

定期

每隔100ms随机抽取key过期的进行删除

堕性

系统去查该key，过期才会被删除

淘汰机制
热点数据

原生6种

已经设置过期时间里

最近最少使用

随机

最不经常使用的

不淘汰，内存满是报错

持久化

快照RDB

创建快照获取某个时间点上数据的副本，进行备份

默认持久化方式

redis.conf

save 300 10

SAVE命令会阻塞redis进程，直到RDB文件生成完毕

redis不能处理任何命令请求，

//300s之后，如果至少有10个key发生变化
则自动调用 BGSAVE 命令创建快照

只追加文件AOF

每执行一条修改数据的命令，
Redis就将【修改命令】数据写入磁盘的AOF文件中

可支持设置每秒

时效性高

appendonly yes

appendonly.aof

混合方式4•0

事务

单线程，保证ACID

MULTI, EXEC, WATCH

使用 MULTI命令后可以输入多个命令。Redis不会立即执行这些命令，
而是将它们放到队列，当调用了EXEC命令将执行所有命令。

Redis 是不支持 roll back 的，因而不满足原子性的（而且不满足持久性）。

WATCH命令监视整个事务中的key是否有被修改

CAS的机制

Redis将多个命令打包，然后一次性，按顺序执行命令

问题

缓存雪崩

缓存同一时间大面试失效
导致后面请求都在数据库，
然后数据库一时间不能承受大量请求而崩掉

解决

事前

保证Redis高可用
及时更换

事中

本地缓存+限流+限级等

事后

使用Redis恢复

缓存穿透

大量的请求key不在缓存中，
导致请求直接数据库

解决

检查参数

id,email

放SQL注入等

缓存无效 key：设置短过期时间

SET key value EX 10086

布隆过滤器

概率判断

布隆过滤器说某个元素存在，⼩概率会误判。
布隆过滤器说某个元素不在，那么这个元素⼀定不在。

存入数据的时候，会通过散列函数将它映射
为一个位数组中的K个点，同时把他们置为1。

查找时，同样hash，看是否都为1

热key

有几十万的请求去访问redis上的某个特定key，那么这样会造成流量过于集
中，达到物理网卡上限，从而导致redis的服务器宕机引发雪崩。

解决

提前把热key打散到不同的服务器，降低压力

加入二级缓存，提前加载热key数据到内存中，
如果redis宕机，走内存查询

缓存击穿

单个key并发访问过高，过期时导致所有请求直接打到db上

加锁更新

MySQL

关系型数据库

连接方式

默认端口3306

常用命令

通配符%

like 'xxx%'

show engines;

show variables like '%storage_engine%'; // 默认存储引擎

show table status like "table_name" ;// 表存储引擎

创建表

create table 'employer' (
'id' int(11) NOT NULL,
'name' varchar(255) default null,
'date' datetime default null,
primary key ('id')
) engine=InnoDB default charset=utf8;

select

limit 3 offset 1
=
limit 1,3

从第二位开始返回3条

不能有运算

插入

insert into employer(id,name,age,date,sex) values (1,'xxx',12,'1997-1-1',1);

更新

update employer set age=18 where sex=1;

删除

delete from employer where age=18;

UNION

多个 SELECT 语句合并集--会删除重复的数据。

SQL的执行过程

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括【连接器、查询缓存、分析器、优化器、执行器】，
同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
• 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
• SQL 等执行过程分为两类
1. 查询：【权限校验】---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎-返回
2. 更新：分析器----》权限校验----》执行器---》引擎---redo log prepare---》binlog---》redo log commit
【redo log两段式提交是为了保证数据一致性】

缓存机制

就是缓存sql文本及查询结果

只缓存Select语句

用KV形式保存在内存中

对查询语句进行Hash计算后，
把得到的hash值与Query查询的结果集对应存放在Query Cache中

不确定的函数不会cache(时间)

如果运行相同的SQL(相差一个字符也不算)，
服务器直接从缓存中取到结果，而不需要再去解析和执行SQL

对大小写敏感的

显然，这对于频繁更新的表，查询缓存是不适合的

缓存失效

表的结构或数据发生改变时，查询缓存中的数据不再有效。
如INSERT、UPDATE、 DELETE

show variables like '%query_cache%'; 是否开启

手动清理

FLUSH QUERY CACHE

缺点

查询语句的hash计算和hash查找带来的资源消耗

Query Cache的失效问题，命中率

相关系统变量设置不合理会造成大量的内存碎片

为什么去掉

命中率太低，影响性能

有了相关插件，做其他缓存机制

https://blog.csdn.net/weixin_34275734/article/details/89624537

持久化：日志

WAL（Write Ahead Logging）
提前写日志的技术

发生了数据修改操作先写日志记录下来，等不忙的时候再持久化到磁盘。
这里提到的日志就是redo log。

InnoDB

undolog、redo log 是InnoDB引擎记录的日制，用以来支持事务。

relog记录的是数据库事务操作中产生的变化，记录修改后的值，
undolog记录事务操作前的数据值。

redo log

redo log是物理格式日志，它记录的是对于每个页的修改。

redo log是循环写
只有那么大的空间

组成

一是内存中的重做日志缓冲（redo log buffer）
二是用来持久化的重做日志文件（redo log file）

参数调节写入file

适用于崩溃恢复

undolog

实现事务原子性

回滚：链表实现

insert undo log

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

update undo log

事务在进行update或者delete时产生的undo log

不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除

只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

实现多版本并发控制

图示

Binlog

Server层也有自己的日志
二进制日志用于归档。

追加写入，当一个binlog文件写到一定大小后会切换到下一个文件。

主从复制与数据同步

binlog记录了mysql执行的【增删改】操作

一条SQL语句执行很慢的原因

偶尔很慢的情况

数据库在刷新脏页（flush）

更新数据时
1. 首先在内存中更新
2. 更新之后，并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，
3. 等到空闲的时候，在通过 redo log 把最新的数据同步到磁盘中去。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为【脏页】。
内存数据写入到磁盘后，内存和磁盘上的数据页的内容一致了，称为“干净页”。

redolog写满了导致刷新脏页

执行时拿不到锁

表加锁

行加锁

一直都这么慢

没用到索引

字段没有索引

字段有索引，但却没有用索引

不符合最左原则

select * from t where c - 1 = 1000;索引无用
from t where c = 1000 + 1;索引有用

运算符、函数操作导致没有用上索引

数据库选错了索引

优化器预测导致扫描全表

大表优化

优先推荐内连接 inner join

使用union all，in代替or

or会使索引失效，再次进行全表扫描

查询限定范围-一个月

合理化设置索引

主库负责写，从库负责读

垂直分区，水平分区等

MYSQL规范与优化

数据库命令规范

小写字母并用下划线分割

禁止使用 MySQL 保留关键字（如果包含关键字查询，单引号括）

数据库基本设计规范

1. 所有表必须使用 Innodb 存储引擎

Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

2. 数据库和表的字符集统一使用 UTF8

减少乱码

防止索引失败

6.尽量做到冷热数据分离,减小表的宽度

MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节。

更有效的利用缓存，避免读入无用的冷数据；
经常一起使用的列放到一个表中（避免更多的关联操作）。

7. 禁止在表中建立预留字段

8. 禁止在数据库中存储图片,文件等大的二进制数据

通常存储于文件服务器，数据库只存储文件地址信息

索引设计规范

建议单张表索引不超过 5 个

增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

创建索引和维护索引需要耗费许多时间，【更新数据时维护索引】

占用物理存储空间

每个 Innodb 表必须有个主键

2. 禁止给表中的每一列都建立单独的索引
建议使用联合索引

常见索引列建议

WHERE 从句中的列

ORDER BY、GROUP BY、DISTINCT 中的字段

通常将 1、2 中的字段建立联合索引效果更好

避免建立冗余索引和重复索引

重复索引示例：primary key(id)、index(id)、unique index(id)•

冗余索引示例：index(a,b,c)、index(a,b)、index(a)

尽量扩展已有的索引而不是创建新索引【联合索引】

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

切记不要用字符串存储日期

字符串占用的空间更大！

比较效率低

无法使用API

https://www.cnblogs.com/huchong/p/10219318.html

索引

BTree索引

Hash需要把数据全部加载到内存中，数据量大很消耗内存，采用B+树，是基于按照节点分段加载，由此减少内存消耗。
业务有关，但数据库中经常查询多条数据，这时候由于B+数据的分段有序性，较快

B树是为磁盘或其他直接存取的辅助存储设备而设计的一种多路平衡搜索树。

B树的所有节点既存放键(key) 也存放数据(data)

B+

B+仅叶子结点存储数据
B+的磁盘读写代价更低

存储更多的键值

更矮更胖，减少IO

根据磁盘预读与局部性原理

MySQL单表能够存储的数据量计算

这里以InnoDB的主键索引为例

B+树的一个节点大小为一个数据页，默认为16KB

B+树非叶子节点存储的数据为关键字和指针，主键字段的数据类型为bigint（8字节），同时存在着指向下一个主键的指针（6字节）

一个节点中主键的个数为16 * 1024 / (8 + 6) ≈ 1170个

叶子节点中存储主键和实际数据，一般而言实际数据大小远远大于主键大小，主键大小可以忽略不计。
实际数据大小假定为1KB，那么叶子节点可以存储16KB / 1KB = 16条数据

通常B+树的高度为3层，整颗B+树能够存储的数据就是1170 * 1170 * 16 ≈ 2200 0000，大约等于2000万

也就是说MySQL单表能够存储的数据量大概在2000万左右

查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快

所有数据均存储在叶子节点，而且数据是按照顺序排列的。
B+的查询效率更加稳定【叶子节点有一个链表连接】

**范围查找，排序查找，分组查找以及去重查找变得简单**

索引文件也很大，一般存在磁盘中，根存内存，所以要减少IO

https://blog.csdn.net/u013967628/article/details/84305511

哈希索引

哈希表

查询性能快

适用于单条记录查询

缺点

哈希冲突

不支持顺序和范围查询--Hash索引不支持顺序和范围查询是它最大的缺点

为什么使用：类似目录，提高检索效率

无序的数据变成有序(相对)

B树

2. where子句中经常出现的字段
3. 索引应该建在小字段上，大的数据字段（bit，image，text）不适用

为什么能提高效率

MySQL的基本存储结构是页(记录都存在页里边)：

各个数据页可以组成一个双向链表

页大小默认16KB

每个数据页中的记录又可以组成一个单向链表

不使用索引

需要遍历双向链表，定位到记录所在的页：
由于不是根据主键查询，遍历所在页的单链表->定位相应的记录：

索引失效

like关键字第一个字符为 % 失效
联合索引中没有使用索引的第一列
or语句中有一个不是索引列

使用索引

无序的数据变成有序(相对)：B+树
查找效率加快，（二分查找，O(logn)）

分类

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

create unique index column on table;
|| unique(`column`)

3.添加INDEX(普通索引)

create index column on table;
|| key(`column`)

4.添加FULLTEXT(全文索引)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

联合索引问题

最左匹配原则

select a, b, c from test where a=? and b=? and c=?；查询效率最高，索引全覆盖。
select * from test where a=? and b=?；索引覆盖a和b。
select * from test where b=? and a=?；经过mysql的查询分析器的优化，索引覆盖a和b。
select * from test where a=?；索引覆盖a。
select * from test where b=? and c=?；没有a列，不走索引，索引失效。

where子句几个搜索条件顺序调换不影响查询结果，
因为Mysql中有查询优化器，会自动优化查询顺序

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

(a,b)->因为联合索引首先是按a排序的，b是无序的，所以单查b失效
在a值确定的情况下b是相对有序的，但当a为范围时b是无序的

区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）
使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。【age,sex】

KEY table(a,b,c));
B+数是按照从左到右的顺序来建立搜索树的，当(a=? and b=? and c=?)这样的数据来检索的时候，
b+树会优先比较a列来确定下一步的所搜方向，

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为“覆盖索引”。

即需要查询的字段正好是索引的字段【key】，直接根据该索引就可以查到数据，而无需回表查询。

存储引擎

show engines

InnoDB

事务性存储引擎

事务

逻辑操作

要么都执行，要么都不执行(原子性)

事务用来保证数据的完整性、正确性，操作的原子性以及并发访问时数据的隔离性

原子性Atomicity

最小执行单位

undo log日志保证，它记录了需要回滚的

一致性Consistency

数据保持一致（透明性）

代码层面来保证

隔离性Isolation

并发访问数据库时一个事务不受其他事务影响

隔离性由MVCC来保证

持久性Durability

对数据库的修改是持久的

内存+redo log来保证

回滚

提交

支持外键

崩溃修复能力

5.7

重做日志（redo log）独有，数据可以通过redo log进行恢复。

多版本并发控制MVCC

同一份数据有多个版本存储，但只有一个是最新的。

提高数据库并发性能，不加锁，非阻塞并发读

提供并发访问数据库时，用来避免写操作堵塞读操作的并发问题。

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，
写操作也不用阻塞读操作，提高了数据库并发读写能力

MVCC可以认为是行级锁的一个变种，避免加锁操作，开销更低。
写操作也只锁定必要的行。InnoDB的MVCC实现，是通过保存数据在某个时间点的快照来实现的

CAS+悲观锁实现

https://blog.csdn.net/SnailMann/article/details/94724197

当前读和快照读

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，
会对读取的记录进行加锁

悲观锁的实现

快照读

不加锁的非阻塞读

读到的是某一个版本的数据，既然是某一个版本的数据，
就并不一定是最新的数据，可能是历史数据

快照读的前提是隔离级别不是串行级别

基于提高并发性能的考虑

基于MVCC

MySQL-MVCC

由 3个隐式字段，undo日志，Read View

MVCC实现原理

MVCC主要通过表数据中的3个隐式字段、undo log、Read View来实现

表数据3个隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID

DB_TRX_ID

6字节，最近修改（修改/插入）事务id，记录创建这条记录/最后一次修改该记录的事务id

DB_ROLL_PTR

7字节，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

undolog

DB_ROW_ID

6字节，隐含的自增id（隐藏主键），如果表没有主键，InnoDB会自动以DB_ROW_ID生成一个聚簇索引

删除flag隐藏字段

实际上还有一个删除flag隐藏字段，既记录被删除并不代表真的删除，而是删除flag变了

记录图示

undo log

undo log主要分为两种insert undo log、update undo log

insert undo log

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

update undo log

事务在进行update或者delete时产生的undo log

不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除

只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

purge线程

从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的delete_bit，并不真正将过时的记录删除

为了节省磁盘空间，InnoDB有专门的purge线程来清理delete_bit为true的记录

为了不影响MVCC的正常工作，purge线程自己维护了一个read view（这个read view相当于系统中最老活跃事务的read view）

如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的

对MVCC有帮助的实质是update undo log，undo log实际上就是存在rollback segment中旧记录链

update undo log执行流程

比如persion表有一条记录，记录如下，name为Jerry，age为24岁，隐式主键是1，事务id和回滚指针，我们假设为NULL

现在来了一个事务1对该记录的name做出了修改，改为Tom

在事务1修改该行（记录）数据时，数据库会先对该行加排他锁

然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本

拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务id为当前事务1的id，
我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，即表示我的上一个版本就是它

事务提交后，释放锁

图示

又来了个事务2修改person表的同一个记录，将age修改为30岁

在事务2修改该行数据时，数据库也先为该行加锁

然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面

修改该行age为30岁，并且修改隐藏字段的事务id为当前事务2的id，那就是2 ，回滚指针指向刚刚拷贝到undo log的副本记录

事务提交，释放锁

图示

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表

undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

Read View（读视图）

事务在进行快照读操作的时候产生的读视图，在该事务执行快照读的那一刻，
会生成数据库系统当前对于事务的一个快照，记录并维护系统当前活跃事务的id

读视图并不是事务开始后就生成了，
而是在进行快照读的时候才会产生，当前读（加排它锁）不会产生读视图

读视图的作用就是通过记录的事务id，在数据版本链中寻找一个合适的版本让当前事务读

在不同的隔离级别下，读视图也不相同

在读已提交隔离级别下，每进行一次快照读，都会产生一个读视图，也就是说每次快照读，读视图都不相同，读视图随着执行快照读而发生变化

在可重复读隔离级别下，不管有多少个快照读，使用的都是第一个快照读进行时生成的读视图，读视图从始至终都不会发生变化

综上所述，在读已提交隔离级别下，即使是相同的SELECT语句，由于读视图可能不同（每次产生新的读视图），读取到的数据可能不同；在可重复读隔离级别下，相同的SELECT语句，读视图一定相同（沿用第一次快照读产生的读视图），读取到的数据一定相同。这也是可重复读的含义，相同的SELECT语句，查询的数据一定相同

读视图由查询时所有未提交事务id数组（数组里最小的id为min_id）和已提交的最大事务id（max_id）组成。查询的数据结果需要和读视图做对比从而得到快照结果

事务id和事务是否提交的关系图

版本链的比对规则

trx_id并不是指当前session的事务id，而是版本链中数据行对应生成该数据的事务id

creator_trx_id：表示生成该ReadView的事务的事务id

如果是只读事务，那么该事务的事务id就是0

如果被访问版本的trx_id与creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问

当前事务自己修改的数据，对自己一定是可见的

如果落在绿色部分（trx_id < min_id），表示这个版本是已提交的事务生成的，这个数据是可见的

如果落在红色部分（trx_id > max_id），表示这个版本是由将来启动的事务生成的，是肯定不可见的

如果落在黄色部分（min_id <= trx_id <= max_id）那就包含两种情况

若trx_id在数组中，表示这个版本是由还没有提交的事务生成的，对当前事务不可见

若trx_id不在数组中，表示这个版本是已经提交的事务生成的，对当前事务可见

如果某个版本的数据对当前事务不可见，根据回滚指针，需要顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，直到找到版本链中的最后一条数据。如果最后一个版本也不可见，那意味着该条记录对该事务完全不可见，查询结果不包含该记录

对于删除的情况可以认为是update的特殊情况。会将版本链上最新的数据复制一份，然后将trx_id修改成操作的trx_id，同时在该记录的头信息（record header）里的（deleted flag）标记位写上true，意味着记录已被删除，不返回数据

整体流程

前面理解了隐式字段、undo log、Read View、当前读和快照读的概念后，整体来看MVCC的执行流程

假定存在5个session，其中三个写事务，两个读事务（默认事务隔离级别是可重复读）

三个写事务和其中一个读事务（select1）

SQL语句执行顺序

第一次快照读

select 1第一次进行快照读生成读视图readview[100, 200], 300。当前活跃事务id为100和200，已提交的最大事务id为300。min_id为100，max_id为300

此时undo log版本链。第三列是生成改行数据的事务id

版本链的比对规则

第一条数据的row的trx_id为300

min_id <= trx_id <= max_id同时trx_id不在未提交事务id的数组[100, 200]中，因此该条数据对当前事务可见，停止查找直接返回数据

查询到的数据就是lilei300

第二次快照读

由于是可重复读，读视图沿用第一次快照读的读视图readview[100, 200], 300

此时undo log版本链

版本链的比对规则

第一条也是最新的一条数据，trx_id为100。100在事务活跃数组[100, 200]中，属于事务未提交的修改，对当前事务不可见

根据回滚指针，找到第二条数据，trx_id为100。100在事务活跃数组[100, 200]中，属于事务未提交的修改，对当前事务不可见

根据回滚指针，找到第三条数据，trx_id为100。min_id <= 100 <= max_id同时trx_id不在未提交事务id的数组[100, 200]中，因此该条数据对当前事务可见，停止查找直接返回数据

第三次快照读

读视图仍然沿用第一次快照读生成的读视图，由于读视图不变，根据版本比对规则，到最后也会找到lilei300这条数据

在可重复读隔离级别下，三次快照读，无论其他事务是否提交事务，读到的数据均相同，满足了可重复读的语义，根本的原因是在RR隔离级别下，读视图均沿用第一次快照读生成的读视图

三个写事务和其中一个读事务（select2）

SQL语句执行顺序

undo log日志数据版本链

进行快照读生成读视图readview[200], 300。此时活跃事务id为200，已提交的最大事务id为300。min_id为200，max_id为300

版本比对规则

第一条数据trx_id为200，在未提交事务id数组[200]中，对当前事务不可见

顺着回滚指针找到第二条数据，trx_id为200，对当前事务同样不可见

顺着回滚指针找到第三条数据，trx_id为100，小于min_id200，属于已提交的事务，对当前事务可见，停止查找，返回lilei2

每行数实际上隐藏了两列，创建时间版本号，过期(删除)时间版本号

MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本

锁

Record Lock

单个行记录上的锁

Gap Lock

间歇锁，锁定一个范围，不包含记录本身

可重复读级别

Next-Key Lock

Record+Gap Lock

前开后闭原则

http://xiaot123.com/gcqwq

共享锁和排他锁，也叫做读锁和写锁。

颗粒度来区分，可以分为表锁和行锁两种。

行锁又可以分为乐观锁和悲观锁

乐观锁则通过版本号或时间戳实现。

使用聚簇索引

数据和索引在一起存储的索引方式叫做聚簇索引

树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键-》就是主索引。

而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是数据地址

主键索引属于聚集索引

二级索引属于非聚集索引。

在根据主索引搜索时，直接找到key所在的节点即可取出数据；
在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。

因此，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，
这样会造成主索引频繁分裂

类似于字符串或UUID这种又长又难比较的数据，
插入需要排序等或查找的速度肯定比较慢。

叶子节点是有序的

逻辑存储

所有数据都被逻辑地存放在一个空间中，称之为表空间（ tablespace ）
表空间又由段（ segment ）、区（ extent ）、页 ( page ）组成

默认情况下 InnoDB存储引擎有一个共享表空间 ibdata1 ，即所有数据都放在这个表空间内。
如果我们启用了参数innodb_file_per_table ，则每张表内的数据可以单独放到一个表空间内。

MyISAM

5.5之前默认存储引擎

只有表级锁

查询具有原子性，速度快

不支持事务与行级锁，崩溃后数据无法安全恢复

密集型性能极好

使用非聚集索引

即索引文件与实际数据分离

B+Tree叶节点的data域存放的是数据记录的地址。

如果Key存在，则取出其 data 域的值【地址】，
然后以 data 域的值为地址读取相应的数据记录

锁机制

https://blog.csdn.net/qq_38238296/article/details/88362999

InnoDB锁详细解释

行锁

兼容性

共享锁（Share Lock，简称S锁）

普通的SELECT语句不会加共享锁

想要显式加共享锁，可以加SELECT ... LOCK IN SHARE MODE子句

为了确保自己查询的数据一定是最新的数据，不会被其他事务进行修改

被读取的行记录或者行范围其他SESSION可以读，也可以加共享锁，但是其他事务无法获取排它锁，也就是说S锁不冲突，S锁和X锁冲突

其他事务可以进行普通的SELECT、SELECT ... LOCK IN SHARE MODE，但是不能进行UPDATE、DELETE、INSERT操作

排它锁（Exclusive Lock，简称X锁）

普通的UPDATE、INSERT、DELETE语句都会加排它锁

想要对SELECT语句显式加排它锁，可以加SELECT ... FOR UPDATE子句，相当于UPDATE语句

保证读取到的数据一定是最新的，不允许其他事务进行修改

其他事务可以进行普通的SELECT，但是不能进行SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT操作

LOCK IN SHARE MODE和FOR UPDATE的相同和不同

相同

二者都可以读取到最新的数据，保证不让其他事务进行修改

可以让普通的SELECT读，UPDATE、DELETE和INSERT语句阻塞

不同

共享锁和共享锁不是互斥的，排它锁和排它锁、共享锁和排它锁是互斥的

两个事务同时进行LOCK IN SHARE MODE，且对锁住的数据执行UPDATE语句，会造成死锁

排它锁只有一个事务能进行FOR UPDATE，不会发生死锁

LOCK IN SHARE MODE适用于并发度低，且不会执行UPDATE锁住数据的场景，FOR UPDATE适用于并发度高，且执行UPDATE锁住数据的场景

锁模式

记录锁（Record Lock）

更新数据时根据索引进行更新。记录锁最简单的一种行锁形式，记录锁是加在索引上的

如果更新语句中WHERE过滤条件不走索引的话，那么它就会升级到表锁，最终造成效率低下，所以在写SQL语句时需要特别注意

间隙锁（Gap Lock）

当我们使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁

而对于键值在条件范围内但并不存在的记录，就叫做间隙，InnoDB在此时也会对间隙加锁，这种记录锁+间隙锁的机制叫Next-Key Lock

间隙锁是一个索引值的左开右开的区间

临键锁（Next-key Lock）

临键锁是记录锁与与间隙锁的结合，所以临键锁与间隙锁是一个同时存在的概念，并且临键锁是个左开右闭的区间

记录锁、间隙锁、临建锁之间的关系示意图

插入意向锁（Insert Intention Lock）

插入意图锁是一种间隙锁，在行执行INSERT之前的插入操作设置

如果多个事务INSERT到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突

锁模式下各种锁之间的兼容矩阵

第一行表示已有的锁，第一列表示要加的锁

插入意向锁不影响其他任何锁

间隙锁和Next-Key与插入意向锁冲突

间隙锁和除了插入意向锁之外的锁都不冲突

记录锁和记录锁冲突，记录锁和Next-key冲突，Next-key和Next-key冲突

表锁

读写意向锁

由于表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，必须要先遍历该表的所有记录，判断是否有排他锁。
这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）

当事务要在记录上加上行锁时，要首先在表上加上意向锁。
这样判断表中是否有记录正在加锁就很简单了，只要看下表上是否有意向锁就行了，从而就能提高效率

意向锁是InnoDB自动加的，不需要用户干预

自增锁

AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。
当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTOINC 表锁，
阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的

读意向锁、写意向锁、自增锁、共享锁、排它锁之间的兼容性

第一行是已存在的锁，第一列是想要获取的锁

总结

当不存在冲突时，InnoDB存储引擎并不会默认生成锁，而是当多个事务冲突后才会生成锁

表锁为意向锁，意向锁主要是为了简化表锁和行锁之间的逻辑，表锁是InooDB存储引擎自己加上的，一般不用关注

较为复杂的是行锁，行锁有两种模式，一种是S锁，一种是X锁。行锁的类型又可以细分成记录锁、间隙锁、临建锁等

如何描述一个行锁呢？现有锁的模式（共享、排他），然后有锁的类型。例如共享记录锁、排他记录锁

可以在information_schema.INNODB_LOCKS系统表中查看当前InnoDB存储引擎中存在的锁

lock_mode，表示锁模式，主要有S、X、IS、IX、GAP、AUTO_INC

lock_type，表示锁类型，主要有Record Lock、Next-key Lock、Insert Intention Lock

explain

EXPLAIN关键字查看MySQL对SELECT类型的SQL的执行计划。从而知道MySQL是如何处理查询SQL的

作用

查看表的读取顺序

查询类型

哪些索引被使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询