Mysql 极客

SQL查询语句的执行过程

连接
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。一般使用长链接，但是全部使用长连接后，你可能会发现，有些时候MySQL占用内存涨得特别快，这是因为 MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候 才释放。
长连接占用内存多

1. 定期断开长连接
2. 通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存
建议你不要使用查询缓存，查询缓存的失效非常频繁，命中率低
在一个表上有更新的时候，跟这个表有关的查询缓存会失效

分析器
词法分析，语法分析

优化器
使用哪个索引
表关联（join）的时候，决定各个表的连接顺序

执行器
判断权限
第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口

SQL更新语句是如何执行的（redo log，binlog）

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）
MySQL 里经常说到的 WAL 技术(Write-Ahead Logging),它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

redo log

在InnoDB存储引擎中，所有的操作都是以页为单位的。而在我们的客户端在进行数据的操作时，主要都会经过buffer pool这个缓冲池来完成，也就是说，真正访问页面之前，都需要把磁盘上的页缓存到buffer pool之后才可以访问。我们都了解事务有ACID四个特性，其中的C——持久性，说的是，已经提交的事务，在事务提交以后即使系统发生了崩溃，这个事务对数据库的更改也不可以丢失，但是试想，如果把数据直接读到buffer pool中，事务在提交后发生了故障，数据并没有及时同步到磁盘，内存中的数据丢失，这个就已经不满足于持久性了。这个时候可能会想到有以下这个解决方案：

在事务提交之前，把该事务涉及到修改的页面全部刷到磁盘中去
但是这个做法有一些问题：

将数据刷到磁盘中的基本单位是页，如果只是修改了某一行数据，也会将整个页刷盘，这个实在是很浪费
随机IO速度低。一个事务修改的数据可能并不在一个页里面，这些页面可能本身在物理上就不相邻，这种情况下，就会产生了大量的随机IO，需要经过一个不停的寻址过程，随机IO的效率比顺序IO低很多
那么这块在mysql中，对于一条修改的数据，就记录了这个数据哪些地方修改了来完成的。比如

update table set a = 1 where id = 1;

就会记录一条日志：

把第10表空间的第90号页面的偏移量为1024处的值更新为1

提交事务后，把这条操作日志刷到磁盘中，

---

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就 像打烊以后掌柜做的事。

binlog

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为binlog（归档日志）。
binlog 日志只能用于归档

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

两阶段提交

图中，redo log的两阶段提交，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两
个状态保持逻辑上的一致。
崩溃恢复时的判断规则判断逻辑：

1. 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；
2. 如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整： a. 如果是，则提交事务； b. 否则，回滚事务。

如果不两阶段提交，基于binlog恢复的数据可能与原库的值不同

数据库恢复

怎样让数据库恢复到半个月内任意一秒的状态 ：binlog
首先定期全量备份，然后基于最近全量备份的数据，从备份的时间点开始，将binlog依次取出来重新执行
redo log具有crash-safe 的能力，redo log commit的时候写入磁盘

redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
如果碰到既有 prepare、又有 commit 的redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog找对应 的事务。

事务隔离

四种隔离级别，在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准（当前读）。
实现
在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC），不同时刻启动的事务会有不同的 read-view，当系统里没有比这个回滚日志更早的 read-view 的时候会被删除

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

语句更新会生成 undo log（回滚日志）,图 中的三个虚线箭头，就是 undo log

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read），除了 update 语句外，select 语句如果加锁，也是当前读。

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待

索引（*）

0.数据结构

hash（范围查询不好，不使用）
有序数组（插入删除很慢，不使用）
二叉树（树的深度太高，找到一个数据要到叶子节点，索引不止存在内存中，还要写到磁盘上，磁盘随机i/o高）
n叉树（采用，N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了）
B+树（B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。）

innoDB采用B+树

1.索引类型

1. 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引
2. 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。）
3. 覆盖索引，不用回表（回到主键索引树搜索的过程，我们称为回表）

2.唯一索引和普通索引

唯一索引的更新就不能使用 change buffer,普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引

change buffer
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在
内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change
buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的
时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方
式就能保证这个数据逻辑的正确性。

change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页（读）会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作

change buffer 用的是 buffer pool 里的内存

change buffer对普通索引的插入能加速，使用场景，写多读少

对于写少读多的场景，由于要读数据再merge，还是要访问磁盘，此时效果不好

redo log和change buffer不一样

后续读与redo log无关，但是会与change buffer merge后返回

3.最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。