MySQL 学习笔记

Mysql 学习笔记

本书是在学习《MySQL 45 讲》的时候加上网上一些资料总结的笔记。

基础架构

首先看 MySql 的基础架构示意图：

连接器：第一步，连接数据库，就是由连接器来做的，连接器负责跟客户端建立连接、获取权限、维持和管理连接。

1. 如果用户名或密码不对，就会收到一个 “Access denied for user” 的错误；
2. 如果认证通过，之后权限判断逻辑都依赖于此时读到的权限，说明连接成功之后再修改权限，也不会影响已经存在连接的权限。

查询缓存：以 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 的优点：

1. InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。
   从上图可以看到，data 都是在叶子节点上的，这样的好处是每个页存储空间是有限的，如果 data 和 key 存储在一块，data 太大的时候，每个页存储的节点会变少，那么就会导致多占用磁盘块；
   每个节点不存 data，可以存更多的 key，这样有两个好处：一是降低树的高度，二是将数据范围变为多个区间，区间越多，数据检索越快。
2. 叶子结点呈双向循环链表结构，顺序查询和分页查询速度会很快；

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）。

这条更新语句做了如下的操作（按照图中的数字顺序）：

1. Page 1 在内存中，直接更新内存；
2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下 “我要往 Page 2 插入一行” 这个信息；
3. 将上述两个动作记入redo log中（图中3和4）。

在后续读 page2 的时候，就会把 page2 的数据 读入内存，然后应用 change buffer 到 page2 的操作日志，再返回结果。

上面也可以看到，change buffer 的变化也被记录到 redo log 中了，所以当遇到机器断电等意外情况也不会导致数据丢失。