MySQL 基础架构

• 客户端：JDBC、Navicat等
• 连接器：管理连接，权限验证。
• 查询缓存：如果查询的语句在缓存中有，直接返回结果，如果没有，查询出结果后将查询语句和结果以kv形式存入缓存。

• 优点：查询命中缓存，速度快，效率高，适用于系统配置表等不经常更新的表。
• 缺点：每次更新一个表都会清空这个表上的所有缓存。
• MySQL8.0版本之后已经彻底删除这个功能。

• 分析器：语法分析，词法分析。(例如select、from等关键词用法错误等)
• 优化器：执行计划生成、索引选择等。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
• 执行器： 验证权限，调用存储引擎接口查询数据。

Buffer Pool（数据缓存池）

这个帖子写的很好

执行sql语句时，如果发现数据不在内存中，需要将数据从磁盘加载到内存，填充缓存页，维护了hash表数据结构，用“表空间号+数据页号”作为key，缓存页的地址作为value，这样就知道数据页在不在缓存中。

数据更新操作

• 如果需要更新的数据在内存中，直接更新内存，不进行io操作，语句结束。
• 如果需要更新的数据不在内存中，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。
• 在其他线程查询该数据的时候，将数据从磁盘读到内存中，并且和channge buffer进行merge操作来得到最新的数据（后台也有线程定时merge）。

刷脏页

如何判断脏页

• LSN：（log sequence number）日志序列号。
• 系统会维护一个LSN，每次在记录日志时该值都会增加，每个数据页的头部都会记录这个值，checkpoint也会对应一个LSN，这个LSN是内存中最早的脏页的LSN。
• 如何判断一个页是否是脏页？只要判断该页的LSN如果大于checkpoint的LSN，那么该页就是脏页。

刷脏页时机

• redo log写满了，要flush脏页。
• 内存（buffer pool）不够用了，要先将脏页写到磁盘。
• 系统空闲的时候，MySQL会主动刷脏页。
• MySQL正常关闭的时候，MySQL会把内存的脏页都flush到磁盘上。

刷脏页

• 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”，把内存里的数据写入磁盘的过程就是刷脏页。
• 当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：

• 如果要淘汰的是一个干净页，就直接释放出来复用；
• 但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

• 脏页会被后台线程自动flush，也会由于数据页淘汰而触发flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

delete

delete操作会将所操作的数据在ID索引树上该位置标记为可复用，磁盘文件大小不会变小，如果后续插入符合顺序的数据，那么就会将数据放到该位置。

count

• count(主键id)：遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
• count(1)来说：InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
• count(字段)：

• 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
• 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加

• count(*)：并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加
• 结论：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以我建议你，尽量使用count(*)

order by

• 初始化sort_buffer
• 查询数据放入sort_buffer，在内存中进行快速排序，如果查询数据过大，需要借助磁盘临时文件辅助排序。

日志

redo log (重做日志)

• redolog是属于InnoDB存储引擎的,是物理日志，记录的是“在某个数据页上做了什么修改”,用来保证事务的持久性。
• WAL技术：Write-Ahead Logging，先写日志，再写磁盘。
• redo log包括两部分：

• 一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；
• 二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的

• redo log文件以ib_logfile[number]命名，有固定的大小，文件循环写入，write pos是当前记录的位置，checkpoint是当前要擦除的位置。
• write pos和checkpoint之间的空间可以用来记录新的操作，如果擦除追上记录，则需要停下来擦除。
• 在innodb中，数据刷盘的规则只有一个：checkpoint。checkpoint触发后，会将buffer中脏数据页和脏日志页都刷到磁盘。
• 有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe
• redo log的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作

undo log (回滚日志)

• Undo Log(回滚日志)用来实现事务的原子性(回滚)和隔离性(MVCC)
• Undo Log和Redo Log正好相反，记录的是数据被修改前的信息，并且只记录逻辑变化，基于Undo Log进行的回滚只是对数据库进行一个相反的操作，而不是直接恢复物理页

binlog（归档日志）

• binlog是属于MySQL Server层,是逻辑日志，记录的是“在某个数据页上做了什么修改”,用来保证事务的持久性。
• binlog有三种记录模式，statement,row,mixed。

• statement：binlog中的是sql语句原文，有风险，可能导致数据不一致，如果sql语句中带有uuid now 等函数的时候
• row：row格式的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度
• mixed：mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式，mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险 row格式现在用的较多，因为可以快速恢复数据，比如删除10万行数据，发现删除错了，row格式的binlog中记录着这10万条语句，可以将delete换成insert 等恢复数据

• binlog是追加写入。

图片转载出处 www.jianshu.com/p/4bcfffb27…

两阶段提交是为了保证数据库的状态和用日志恢复出来的库的状态一致。

隔离性与隔离级别

读未提交

一个事务还没提交时，它做的变更就能被别的事务看到

读已提交

一个事务提交之后，它做的变更才会被其他事务看到

可重复读

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的

一致性读视图

InnoDB在实现MVCC时用到的一致性试图，事务在启动的时候把整个数据库“拍了个快照”,每个事务都用自己的trx_id，数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到。

• 版本未提交，不可见；
• 版本已提交，但是是在视图创建后提交的，不可见；
• 版本已提交，而且是在视图创建前提交的，可见 例如列age在AB两个事务启动的时候都是20，B事务中将age+1,事务A中将age+1，因为一致性视图，事务A查询的age应该是他本身事务开启的时候的20+1=21，但事实是查询出来的是21+1=22。
• 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read），而当前读，总是读取已经提交完成的最新版本。 幻读

• 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
• 幻读只发生在insert插入的时候，update和delete时候看到的数据不一致叫做脏读或者不可重复读。

串行化

顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

索引

常见索引模型

1. 哈希表

• 哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。
• 哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。
• 不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。
• 优点：等值查询，速度快。
• 缺点：范围查询，速度慢，效率低，因为需要全表扫描。

2. 有序数组

• 优点：因为是有序数组，用二分法可以快速查询，时间复杂度是O(log(N))。
• 缺点：只适用于静态数据，如果插入数据，需要挪动后面所有记录，成本太高。

3. 二叉搜索树

• 优点：查找速度快。
• 缺点：容易退化成斜树，增加IO操作。

4. B-树

B树中的每个节点根据实际情况可以包含大量的键值，数据和指针

5. B+树

• B+树是B树的一个升级版。
• 叶子节点冗余了所有的非叶子节点的key，
• 每个叶子/4785节点增加了一个指向相龄叶子节点的指针

索引分类

1. 聚簇索引

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

2. 非聚簇索引

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

索引的使用

这篇文章写的不错

回表

• select * from T where ID=500；只需要搜索ID这颗B+树。
• select * from T where k=5；在k这颗B+树上，搜索k=5所对应的ID值，用ID值回到ID索引树上查询这一行的数据，这个过程叫做回表。

自增主键的讨论

自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂，一个数据页写满了，再插入的时候，直接申请新的数据页即可。 如果是字符串做主键，在插入的时候可能会插入到已有的2个主键之间，在数据页写满的情况下，插入到现有的数据页中，将原本属于这个数据页的键，移动到新申请的数据页上，导致页分裂。

索引覆盖

• select * from T where k between 3 and 5;需要先查询k索引树，之后回表去主键树查询。
• select ID from T where k between 3 and 5；因为索引树上叶子节点存储的就是ID的值，所以不需要再回表。

索引下推

• 前置条件：联合索引（name, age）
• select * from tuser where name ='虎哥' and age=30;
• MySQL5.6之前，需要在索引树查询name=虎哥的数据，之后去ID树回表查询数据筛选age=30的数据。
• MySQL5.6之后，在索引树查询name=虎哥的同时还可以筛选age=30的数据，之后筛选过的数据的ID去ID树回表查询。

普通索引和唯一索引

普通索引和唯一索引的选择

• 在查询的场景下，性能差距忽略不计。
• 在更新的场景下，如果需要更新的数据不在内存中：

• 普通索引将数据记录在channgerbuffer中，语句结束.
• 唯一索引会将数据从磁盘中读取到内存来判断是否存在唯一性冲突。

• 优先使用普通索引和change buffer

为字符串选择索引

• 直接创建完整索引，这样可能比较占用空间；
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引（系统无法判断是否截断完整信息，需要去ID树回表查询）；
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
• 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

破坏索引

• 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
• 隐式类型转换
• 隐式字符编码转换

锁

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份。

表级锁

表锁

表锁是最常用的处理并发的方式

元数据锁

MDL（metadata lock),当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁

行锁

• 行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新,
• 在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议

间隙锁

在可重复度的隔离级别下才生效，用来解决幻读，select * from t where d=5 for update，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。

• 防止间隙内有新数据被插入
• 防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5） 间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。 默认情况下，InnoDB工作在Repeatable Read隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，当对数据进行条件，范围检索时，对其范围内也许并存在的值进行加锁！

自增id用完怎么办

• 无符号整型(unsigned int)是4个字节，上限是2的32次幂-1
• 无主键 row_id长度是2的48次幂-1
• 自增id到达上限后，再插入数据时报主键冲突错误
• 没有指定主键，mysql分配的row_id达到上限，会从头分配，会覆盖原来的第一行。 从这个角度看，我们还是应该在InnoDB表中主动创建自增主键。因为，表自增id到达上限后，再插入数据时报主键冲突错误，是更能被接受的。毕竟覆盖数据，就意味着数据丢失，影响的是数据可靠性；报主键冲突，是插入失败，影响的是可用性。而一般情况下，可靠性优先于可用性

MRR

mrr

select * from user where age between 10 and 20;

• 没有开启mrr，比如在查询的时候，普通索引树上的age是按照顺序排序，每个叶子节点的键值对应主键树上的ID，从索引树按照age顺序查找ID值去主键树回表查询，但是索引树上的主键值不一定保持顺序，索引回表的过程中，可能会查id=1，id=10000，id=300，这样会导致随机读

• 磁盘要不停的旋转，磁头要不停的移动，耗费io时间
• 比如id=1和id=300在一个缓存池中，查找id=1之后，查找id=10000，之后过了很久再查找id=300的时候，可能因为申请的缓存池过多，导致id=1的缓存池已经被淘汰了，这样为了查询id=300就必须把这一页再重新加载到缓存池，又多了一次io操作。

• 开启mrr ，mysql > set optimizer_switch='mrr=on'; 顺序读

• 按照聚簇索引键值排好序，再顺序的读取聚簇索引
• 磁盘和磁头不再需要来回做机械运动；
• 可以充分利用磁盘预读,因为顺序读，可以先读完一个缓存池，之后这个缓存池淘汰了也没关系。

MySQL是怎么保证数据不丢

只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复

MySQL主从复制

• 在备库B上执行start slave命令，这时候备库会启动两个线程，io_thread和sql_thread。其中io_thread负责与主库建立连接
• 主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B.
• 备库B拿到binlog后，写到本地文件，称为中转日志（relay log）
• sql_thread读取中转日志，解析出日志里的命令，并执行
• 主从延迟

• 应用层与数据库之间添加缓存，优先走缓存，减少从库压力。
• 需要实时读取的业务数据优先走主库。
• 细化事务：将大事务拆为小事务，不必要的地方移除事务
• 提升 SQL 执行速度：优化索引
• 减少批量操作：批量 DML 的耗时较多，减少不必要的批量 DML

循环复制

发生在主备架构-双Master架构中，A库B库互为主备，这样会产生循环复制。

• 规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；
• 一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；
• 每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志