Mysql 学习笔记
本书是在学习《MySQL 45 讲》的时候加上网上一些资料总结的笔记。
基础架构
首先看 MySql 的基础架构示意图:
连接器:第一步,连接数据库,就是由连接器来做的,连接器负责跟客户端建立连接、获取权限、维持和管理连接。
- 如果用户名或密码不对,就会收到一个 “Access denied for user” 的错误;
- 如果认证通过,之后权限判断逻辑都依赖于此时读到的权限,说明连接成功之后再修改权限,也不会影响已经存在连接的权限。
查询缓存:以 key-value 的形式存储,key 就是 sql 语句,但是尽量不要使用查询缓存,因为只要有更新,这个表上的查询缓存就会清空,对经常更新的表来说,缓存命中率很低。
分析器:分析器的作用就是解析语句,判断语法是否错误、指定列是否存在等。
优化器:优化器是在表里面有多个索引的时候,决定使用哪个索引。
执行器:MySQL通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句。
开始执行之前,会先判断有没有对表的查询条件:如果没有会返回没有权限,如果有就会根据表的引擎定义,去使用这个引擎提供的接口。
日志系统
WAL技术,WAL的全称是Write-Ahead Logging,它的关键点就是先写日志,再写磁盘。
这里例如我们要执行 update T set c = c + 1 where id = 2,那么执行过程应该是:
1. 执行器找到 id = 2 这一行,如果在内存中有,直接返回给执行器;否则需要先从磁盘读入内存,然后再返回;
2. 执行器拿到行数据,把指定字段的值加上 1 ,得到新的数据,调用引擎接口把新数据写入内存,这时候,该数据被称为“脏页”;
3. 然后将这个更新记录到 redo log 中,此时 redo log 处于 prepare 状态。然后告知执行器执行完成,随时可以提交事务;
2. 执行器生成这个操作的 binlog ,如果 sync_binlog = 1(sync_binlog 是 binlog 持久化到磁盘的策略),并将binlog 写入磁盘;
3. 执行器调用引擎的提交事务接口,redo log 状态改为 commit ,更新完成,等从 redo log 同步到磁盘后,“脏页” 变为 “干净页” 。
如下图,浅色是 InnoDb 内部执行的,深色是执行器执行的
Redo log(重做日志)
上面可以看到,Redo log 和 binlog,那么下面介绍这两个 log 的作用。
首先,Redo log 是 InnoDb 引擎特有的日志,主要用于崩溃恢复,也可用于数据恢复;binlog 是 Server 层的日志,主要用于数据恢复。
先举个例子:Redo log 就像是临时的小账本,记录账目变动情况,在空闲时间或临时小账本满了之后再往全量账本上同步,这样的效率比每次都找出全量账本再修改速度要快得多。
MySQL 的数据最终是要持久化到磁盘的,但是磁盘读写分随机读写与顺序读写。顺序读写的速度是比随机读写快的。
MySQL 的数据页读写是随机读写,如果每次都直接数据落地到磁盘,那么每次都是随机读写,所以 MySQL 引入了 Redo log 来作为临时账本,而且 Redo log 是顺序写的方式。
有了 Redo log ,InnoDb 就可以记录某个页做了什么变动,在空闲时间把数据落地到磁盘,这样保证即使数据库发生异常,之前的数据也不会丢失,这个能力被称为 crash-safe。
Redo log 实现图
1. Redo log 分为两部分:一是 redo log buffer ,该部分在内存中,是易失的,二是 redo log file,这才是持久化到磁盘上的,
当数据到这里时,就保证了持久化。
2. 为了确保每次日志都能写入到事务日志文件中,在每次将日志缓存(log buffer)中的日志写入日志文件的过程中会先经过操作系统内核
空间的os buffer,然后会调用一次操作系统的fsync操作刷入到 log file 中
MySQL的 log buffer 写入到 log file 有如下三种磁盘写入策略,可以使用 innodb_flush_log_at_trx_commit 来控制。
binlog(归档日志)
主要用于主从复制和数据恢复,主从复制的场景中在Master 端会开启binlog ,然后将 binlog 发送到各个Slave 端,Slave 端重放binlog 从而达到Slave 端的数据和Master端的数据保持一致
Redo log 和 binlog(归档日志)的区别
Redo log | binlog | |
---|---|---|
日志类型 | 物理日志,记录的是在某个数据页做了什么操作 | 逻辑日志,主要记录了这个语句的原始逻辑,比如 “给 id = 2 的这一行 c 字段 + 1” |
适用场景 | 崩溃恢复(crash-safe) | 适用于主从复制和数据恢复 |
实现方式 | InnoDB 引擎层实现的 | Server 层实现的,所有引擎都可以使用 binlog 日志 |
文件大小 | 大小是固定的,可以通过修改配置参数 innodb_log_files_in_group 和 innodb_log_file_size 配置日志文件数量和每个日志文件大小 | Server 层实现的,所有引擎都可以使用 binlog 日志 |
记录方式 | redo log 采用循环写的方式记录,当写到结尾时,会回到开头循环写日志 | 通过追加的方式记录,当文件尺寸大于给配置值后,后续的日志会记录到新的文件上 |
undo log
1、多版本并发控制( MVCC )
上面说到,同一条记录在系统中可以存在多个版本。MVCC 就是一种并发控制的方法,通过多个版本实现对数据库的并发访问,主要是
为了在 MySQL InnoDb 的 RC、RR 两种隔离级别中提高数据库并发读写性能。
好处是:读不阻塞写,写也不阻塞读,利用 undo log 实现读已提交和可重复读,但是事务 A 先更新 name = ‘张三’,事务 B 更新
name = '李四' 这种事务 A 更新丢失的问题不能解决。
2、当前读与快照读
· 当前读:
像 select lock in share mode (共享锁)、select for update、update、 insert、delete (排他锁)这些操作都是一种当前
读,为什么叫当前读?就是它读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。
· 快照读:
单纯的 select 就是快照读,前提是隔离级别不是串行化,串行化读写都会加锁,就退变成了当前读,快照读的出现是为了更高的并发
读性能。
如下图所示,就是一个记录被多个事务连续更新后的状态:
undo log 是逻辑日志,可以理解为:
· 当delete一条记录时,Undo Log中会记录一条对应的insert语句
· 当update一条记录时,它记录一条相反的update记录
实际上,InnoDb 每次进行增删改操作都会生成回滚日志,会记录与操作相反的逻辑与 row trx_id(创建这条记录的事务 ID),上图中的虚线就是 undo log,而上面的 V1、V2、V3 并不是真实存在的,每次获取都需要通过当前版本与 undo log 计算出来的,所谓的快照读,读的就是这种 “快照” 。
事务ID 是在事务开始的时候向 InnoDb 申请的,严格按照顺序递增,需要注意的是,事务真正启动的时机是开启事务之后第一条进行快照读语句的时候,如果想要直接启动一个事务,可以使用:start transaction with consistent snapshot 这个命令。
当然回滚日志不会一直存在,在系统里没有比这个回滚日志更早的 read-view 的时候,会把之前的回滚日志删除。
怎么实现事务回滚
上面说到,每个事务都有自己的 “快照”,那么事务回滚就只需要根据 undo log 里的属于自己的逻辑日志操作一遍即可。
假设一个值从1被按顺序改成了2、3、4,在回滚日志里面就会有类似下面的记录。
当前值是4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的 read-view。如图中看到的,在视图 A、B、C 里面,这一个记录的
值分别是1、2、4,同一条记录在系统中可以根据 undo log 计算出多个版本,这就是数据库的多版本并发控制(MVCC)。对于 read-view A
要得到1,就必须将当前值依次执行图中所有的回滚操作得到。
Read View 它的结构如下:
怎么实现 RC、RR 隔离级别的读已提交和可重复读
1、事务的四种隔离级别
· 读未提交:一个事务可以读取另一个未提交事务的数据;
· 读已提交(RC):两个不同的事务,事务 A 还没结束,事务 B 修改后并 commit ,再次查询可以读到 B 修改后的数据;
· 可重复度(RR):两个不同的事务,事务 A 还没结束,事务 B 修改后并 commit ,再次查询,只能查到和事务开始的时候表里的数
据,但有问题就是在**当前读**下会出现幻读,即新事务 insert 并且 commit,事务 A 再查询可以查询到多了一条记录;
· 串行化:读写都会加锁。
3、怎么实现读已提交 ?
首先,InnoDb 的 RC、RR 两种隔离级别中,select 进行的都是快照读,而快照读首先是通过 ReadView 来判断读 undo log 中哪个版本中的快照数据的,那么判断方式如下:
RR 隔离级别可重复读,和 RC 隔离级别读已提交都是通过 MVCC 来解决的,只是生成 ReadView 的时机不一样。
- 读已提交是通过:每次 select 都生成一个 ReadView,里面的 m_ids 也就是活跃的事务 ID 集合不一样,所以在另外一个事务 commit 之后,再次 select 会读到已经提交的数据
- 可重复读是通过:仅在第一次 select 时生成一个 ReadView,所以在这个事务中,只会查到同样的数据。
RR 隔离级别下的快照读没有幻读,因为每次都根据 ReadView 访问快照,当前读解决幻读是通过间隙锁,什么是间隙锁,以后再说-----------------------------------------------------------------------------------------------------------------------------------------
索引
1、索引的好处
1. 大大减少了服务器要扫描的数据量
2. 帮助服务器避免排序临时表
3. 将随机IO变成顺序IO
2、索引的常见模型
hash 表:等值查询快,范围查询需要一个个对比;
有序数组:等值查询与范围查询很快,但是往中间插入数据,后面的所有数据必须挪动,插入成本高;
二叉树:查询和插入都是 O(log(N)) ,但是索引是要在磁盘上的,数据量多的时候,树高变深,最差可能一次树高需要进行一次磁盘随机 IO,
这时候耗时较长;
N 叉树 : InnoDb 使用的就是这样的结构作为索引结构。
InnoDb 使用的是 B+ Tree,是 N 叉树的一种,结构如下图
3、B+ Tree 的优点:
- InnoDB的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在InnoDB中,每个数据页的大小默认是16KB。
从上图可以看到,data 都是在叶子节点上的,这样的好处是每个页存储空间是有限的,如果 data 和 key 存储在一块,data 太大的时候,每个页存储的节点会变少,那么就会导致多占用磁盘块;
每个节点不存 data,可以存更多的 key,这样有两个好处:一是降低树的高度,二是将数据范围变为多个区间,区间越多,数据检索越快。 - 叶子结点呈双向循环链表结构,顺序查询和分页查询速度会很快;
4、MySQL 里的 B+Tree
1、MyISAM 的 B+Tree
MyISAM 的叶子节点存放的是一个地址,地址指向的才是数据存放的位置。查找过程是根据主键找到地址,再根据地址查询对应数据。
存储引擎是 MyISAM, 在 data 目录下会看到3类文件:.frm、.myi、.myd
(1).frm–表定义,是描述表结构的文件。
(2).MYD–数据信息文件,是表的数据文件。
(3).MYI–索引信息文件,是表数据文件中任何索引的数据树
2、InnoDb 的 B+Tree
-
主键索引的叶子节点存放的是整行数据,主键索引也被称为聚簇索引。如果没有主键,那么会生成一个长度为6字节的全局自增 row_id来作为主键,对于用户是不可见的,Oracle的是可见的;
-
如果创建索引的键是其他字段,那么在叶子节点中存储的是该记录的主键,然后再通过主键索引找到对应的记录,叫做回表,非主键索引也被称为二级索引。
5、怎么避免回表?
1、索引覆盖
假设 id 是主键,num 有索引,执行 select id from T where num between 1 and 5 ,我们只需要 id
的值,id 在 num 索引的叶子节点上,无需再去查主键索引的树,这样的操作被称为索引覆盖。
再例如有一个高频请求:根据身份证号查询 name ,那么我们可以建立一个(身份证号,name)的索引,这样
也可以避免回表。当然这样可能会造成冗余索引,这时候就需要权衡了。
2、 最左匹配 组合索引:name,age
//使用索引
select * from table where name = ? and age =? ;
//使用索引,mysql有自己的优化器,会自动调整顺序
select * from table where age = ? and name =?;
//不会使用
select * from table where age = ?;
//最左匹配,使用索引
select * from table where name = ?;
// 使用索引,因为索引是顺序排序的,找到第一个满足条件的往后遍历到不满足条件
select * from table where name like '王%';
3、索引下推
在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
以联合索引(name, age)为例,检索出表中“名字第一个字是张,而且年龄是10岁的所有男孩”
select * from tuser where name like '张%' and age=10 and ismale=1;
在MySQL 5.6之前,只能从第一个满足 name like 张% 的条件开始一个个回表。到主键索引上找出数据行,再对比字段值。
而MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
第一张图是<无索引下推执行流程>,第二张是<索引下推执行流程>,可以看到索引下推之后回表次数减少到了两次。
6、索引维护
假设现在的索引结构如下:
如果要插入700,那么直接在后面追加就可以了,但是如果要插入400,就需要把后续的数据往后挪,更糟糕的情况是:R5 所在的数据页已经满了,这时候就会申请新的数据页,然后挪动部分数据过去,这个过程称为页分裂,消耗性能。
而且本来一个页上的数据分到两个页上,还会浪费数据页利用率。当两个页利用率很低的时候,还会进行页合并。
重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。
主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小,所以自增主键是个不错的选择。
不论是删除主键还是创建主键,都会将整个表重建,所以需要重建主键索引不如使用:alter table TableName engine=InnoDB。
锁
锁都是不影响正常 select 的。
server 层锁
以下锁都是在 server 层进行加锁的。
1、全局锁
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都select出来存成文本。
- 如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆;
- 如果你在从库上备份,那么备份期间从库不能执行主库同步过来的binlog,会导致主从延迟。
// 加锁 FTWRL,与客户端断开连接之后,MySQL会自动释放锁
Flush tables with read lock;
// 解锁
unlock tables
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。
但是优缺点就是只适用于所有的表使用事务引擎的库。
2、表级锁
MySQL 里有两种表级锁:表锁和元数据锁(meta data lock,MDL)。
1、表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2的操作。连写t1都不允许,自然也不能访问其他表。
unlock tables 只有在 lock tables 已经获取到表锁时,会隐式提交任何活动事务,不管是 FRWRL 命令之后使用 unlock tables 不会提交事务。
2、另一类表级的锁是MDL(metadata lock)
执行 show processlist,看看有哪些session,这些session在做什么事情。当你看到 waiting for table metadata lock 时,那就是遇到MDL元数据锁了。
MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁,来保护表的元数据信息,用于解决或者保证DDL操作与DML操作之间的一致性。
元数据锁是server层的锁,表级锁,每执行一条DML、DDL语句时都会申请MDL锁,DML操作需要MDL读锁,DDL
操作需要MDL写锁(MDL加锁过程是系统自动控制,无法直接干预,读读共享,读写互斥,写写互斥),申请MDL锁
的操作会形成一个队列,队列中写锁获取优先级高于读锁。一旦出现写锁等待,不但当前操作会被阻塞,同时还会阻
续塞该表的所有操作。事务一旦申请到MDL锁后,直到事务执行完才会将锁释放。(这里有种特殊情况如果事务中
包含DDL操作,mysql会在DDL操作语句执行前,隐式提交commit,以保证该DDL语句操作作为一个单独的事务存在,
同时也保证元数据排他锁的释放)。
InnoDb 引擎层锁
1、行锁
MySQL的行锁是在引擎层由各个引擎自己实现的。只有 InnoDb 支持行锁。
如果 执行 DML 语句(insert 、 delete 、 update),并且条件是索引的时候,就会获取行锁,但是行锁获取是在执行之前获取的,执行之后并不会直接释放,是到事务结束之后才释放。
所以如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
change buffer
1、change buffer 的应用
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响
数据一致性的前提下,InooDB 会将这些更新操作缓存在 change buffer 中,这样就不再需要从磁盘中读入这个数
据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的
操作。通过这种方式就能保证这个数据逻辑的正确性。
change buffer 在内存中有拷贝,也会被写入到磁盘上。
把 change buffer 应用到旧的数据页,得到新的数据页的过程,称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期merge。在数据库正常关闭(shutdown)的过程中,也会执行merge 操作。
merge的执行流程是这样的:
1. 从磁盘读入数据页到内存(老版本的数据页);
2. 从 change buffer 里找出这个数据页的 change buffer 记录(可能有多个),依次应用,得到新版数据页;
3. 写 redo log。这个redo log 包含了数据的变更和 change buffer 的变更。
2、change buffer 的好处
可以减少读磁盘,语句返回响应的速度明显提升,而且数据读入内存需要占用 buffer pool,所以还能避免内部占用。
3、什么条件下可以使用 change buffer
首先只会在 insert 的时候会用 change buffer 。
- 唯一索引不会使用 change buffer。因为唯一索引每次都需要把数据页读到内存中判断是否存在,才能进行插入,那么就没必要使用 change buffer 了。
- 普通索引应该插入到的数据页不在内存中,使用 change buffer。
4、change buffer 和 redo log
redo log 主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘
的 IO 消耗。
比如我们要执行一个插入操作:
mysql> insert into t(id,k) values(id1,k1),(id2,k2);
我们假设当前k索引树的状态,查找到位置后,k1所在的数据页在内存(InnoDB buffer pool)中,k2所在的数据页不在内存中。如下图所示是带 change buffer 的更新状态图。
分析这条更新语句,你会发现它涉及了四个部分:内存、redo log(ib_log_fileX)、 数据表空间(t.ibd)、系统表空间(ibdata1)。
这条更新语句做了如下的操作(按照图中的数字顺序):
- Page 1 在内存中,直接更新内存;
- Page 2 没有在内存中,就在内存的 change buffer 区域,记录下 “我要往 Page 2 插入一行” 这个信息;
- 将上述两个动作记入redo log中(图中3和4)。
在后续读 page2 的时候,就会把 page2 的数据 读入内存,然后应用 change buffer 到 page2 的操作日志,再返回结果。
上面也可以看到,change buffer 的变化也被记录到 redo log 中了,所以当遇到机器断电等意外情况也不会导致数据丢失。