MYSQL学习日记--基础篇（8讲）
第一讲：基础架构：一条SQL查询语句是如何执行的？
1、MySQL 的基本架构示意图
Mysql的逻辑架构图
大体来说Mysql分为两部分：Server层和数据引擎。
Server层包括连接器、查询缓存、分析器、优化器、执行器。涵盖大多数核心功能，以及所有内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、试图等。
存储引擎层负责数据的提取和存储，支持InnoDB、MyISAM、Memory 等多个存储引擎，Mysql5.5.5后InnoDB成为默认引擎。如果在建表是想选择其他引擎可以engine=memory来指定使用内存引擎创建表。
不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分。
2、连接器
第一步必须要连接到Mysql上，这时用到的是连接器。
连接器负责跟客户端建立连接、获取权限、维持和管理连接。
客户端长时间不使用，连接器会自动断开，这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。如果断开后，再次发送请求，会提示Lost connection to MySQL server during query。这时需要重连。
数据库里面长连接是，长连接成功后，客户端持续的请求使用同一个连接。短连接是客户端几次查询后就断开，下次查询再重新建立连接。推荐使用长连接，建立连接过程是复杂的，但有些时候内存长得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
长连接占用内存过多解决方案
（1）、定期断开长连接，重连。
（2）、Mysql5.7版本后，每次执行完比较大的操作，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过 程不需要不需要重连和重做权限验证，但是会将连接恢复到刚刚创建完时的状态。
3、查询缓存
第二步，建立好了连接，接下来执行sql，查询缓存。
Mysql拿到sql首先会检查是不是之前执行过的sql，所以会先查询缓存，之前执行过的sql及结果可能会以key-value形式存在于缓存中。如果能直接找到key，就直接返给客户端。
如果缓存没命中，则执行后续过程，执行完成后，执行结果会被存入缓存。
使用缓存弊大于利，缓存失效非常频繁，只要有一个表更新，这个表上的查询缓存会全部失效，可能之前存的缓存还没有用到，就全失效了，这对压力大的数据库来说，查询缓存命中率特别低。静态表，比如配置表，长时间不更新推荐使用缓存。
注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。
4、分析器
如果没有命中缓存，接下来就真正执行sql，首先要知道sql要干什么，就要对sql进行解析。
首先进行 词法分析 分析出字符串是什么意思，比如select->查询语句，把T识别为表名T，ID识别为列ID
然后进行 语法分析 要遵从语法规则比如select->elect会报错You have an error in your SQL syntax
5、优化器
经过了分析器，知道了sql要干什么，接下来进行优化器处理。
优化器是当你sql中有多个索引时，决定使用哪个索引，或者在一个语句中有多个join表关联，决定各个表的连接顺序。
例如 select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
这里有两种执行顺序，先取c=10或者先取d=20，逻辑上一样，但是效率有所不同，优化器就是起选择作用。
6、执行器
知道要做什么了，接下来就是执行器。
执行前会判断下有没有表T的执行权限，如没有会返回错误。如果是查询缓存，则是返回的时候验证权限，(查询也会在优化器之前调用 precheck 验证权限)。
为什么对权限的检查不在优化器之前做？
有些时候，SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器，得在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的。
mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
如果有权限就打开表执行，会根据表的引擎定义，去使用引擎提供的接口。
比如上面sql，id字段没有索引，则：
1）、调用Innodb引擎接口取第一行判断id是不是10，是加入到结果集，不是跳过。
2）、调用引擎接口取下一行，重复1），直到这个表最后一行。
3）、执行器把上述过程所有满足条件的行组成结果集返回给客户端。
至此一条sql执行完成。
对于有索引的表，逻辑差不多，第一次调的是“满足条件的第一条”，第二次调的是“满足条件的下一条”这些接口引擎中有定义。
数据库慢日志中可以查到rows_examined字段，代表一共扫描了多少行，这是执行器每次调用引擎累加的。
有些情况下，执行器调用一次，引擎会扫描多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。这个后面会学习。
第二讲：日志系统：一条SQL更新语句是如何执行的？
1、更新语句执行
mysql> create table T(ID int primary key, c int);
mysql> update T set c=c+1 where ID=2;
查询语句的流程更新语句会再走一遍，首先连接数据库，然后在一个表上有更新，则这条语句就会把表 T 上所有缓存结果都清空，然后分析器分析这是一条更新语句，优化器决定使用ID这个索引，然后执行器找到这一行更新。
与之前不同的是更新流程涉及到两个重要日志模块：redo log（重做日志）和 binlog（归档日志）。
2、重要的日志模块：redo log
关键点就是先写日志，再写磁盘。
当有一条记录需要更新的时候，InnoDB引擎会先把记录写到 redo log (粉板)里面，并更新内存，这个时候就算更新完成了，同时，InnoDB会在适当的时候将这个记录更新到磁盘中，而这个操作往往是在系统比较空闲的时候做，类似打烊以后掌柜把粉板更新到账本上。
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示：
redo log
write pos是当前写位置，一边写一边后移，check point是当前要擦除记录位置，这两个指针都是循环的。擦除的记录需要更新到记录文件。
两指针中间位置是当前“粉板”还空着的空间，如果write pos追上check point，说明“粉板”满了，需要停下来擦除一下记录，
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
3、重要的日志模块：binlog
mysql主要有两块，Server和存储引擎，Server主要做的是mysql功能层面的事，存储引擎主要做的是存储相关，那么redo log相当于是存储引擎（InnoDB）特有的日志。而Server层也有自己特有的日志 binlog ，称为归档日志。
1）、为什么会有两份日志？
简单来说是当时Mysql没有InnoDB引擎，Mysql自带引擎MyISAM不支持 crash-safe 能力，binlog 日志只能用于归档。
2）、这两种日志有以下三点不同
a、redo log是InnoDB特有的，binlog是Server层实现的，所有公用的。
b、redo log是物理日志，记录了“某页改了什么”，binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1”。
c、redo log是循环写的，空间固定会用完，binlog是可以"追加写"，不会覆盖以前的日志。
3）、执行器和InnoDB执行一条更新语句流程
深色部分执行器执行，浅色部分引擎执行。
update语句执行流程
总结起来分五步，1、执行引擎取id=2的这行，引擎直接搜索到这行看是否在内存页中，是返回，否磁盘读入内存；2、引擎c+1，写入新行；3、引擎将新行更新到内存，更新写入redo log，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务；4、执行器生成这个操作的binlog，并把 binlog 写入磁盘；5、引擎提交事务，redo log 处于 commit 状态。
最后三步，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。
两阶段提交是为了让两份日志之间的逻辑一致
简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
4）、怎样让数据库恢复到半个月内任意一秒的状态？
首先取最近一次的全量备份，然后取binlog注意执行到要恢复的时间点
四、总结
主要学习两个日志，物理日志redo log 和 逻辑日志binlog。
redo log 保证了crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。
sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数也建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。
日志系统的"两阶段提交"，两阶段提交是跨系统维持数据逻辑性一致时常用的一个方案。
三、事务隔离：为什么你改了我还看不见？
1、前言
事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
Mysql支持多引擎，但并非所有引擎都支持事务，比如MyISAM不支持事务，也是被InnoDB替换的重要原因之一。
2、隔离性于隔离级别
1）、事务特征，ACID，原子性、一致性、隔离性、持久性。
2）、多事务同时执行时可能出现，脏读、不可重复读、幻读。
脏读：读了一个事务没有提交的数据
不可重复读：事务A执行中还未最终提交，另一个事务B修改了数据并且提交，事务A两次读取数据不一致
幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据
3）、事务的隔离级别，隔离的越严，性能就越低
读未提交：事务还没被提交时，其他事务就能看到。
读已提交：事务提交后，才能够被其他事务看到
可重复读：一个事务在执行过程中，总是跟这个事务开启时看到的数据是一致的，在此级别下为提交的变更其他事务也看不见
串行化：对同一行数据，读会加读锁，写会加写锁，当出现读写冲突时，后执行的事务必须等待前一个事务执行完才能继续执行
总结起来：
读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
串行化：我的事务尚未提交，别人就别想改数据。
这4种隔离级别，并行性能依次降低，安全性依次提高。
4）、Oracle默认的隔离级别是“读已提交”，transaction-isolation设置当前事务级别，show variables查看当前隔离级别
使用“可重复读”隔离级别好处：事务启动时的视图可以认为是静态的，不受其他事务更新的影响。
3、事务隔离的实现
每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。
1）、回滚日志什么时候删除？
系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。
2）、什么时候不需要了？
当系统里么有比这个回滚日志更早的read-view的时候。
3）、为什么尽量不要使用长事务。
长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。
四、事务的启动方式
1）、显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2）、set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
建议：set autocommit=1, 通过显式语句的方式来启动事务，可以解决有些客户端启动时set autocommit=0，这导致接下来的每次查询都在事务中，如果是长链接，就导致了长事务。
但如果考虑多次交互问题建议在set autocommit=1的情况下使用 commit work and chain 语法，commit是提交事务， commit work and chain是提交事务并自动启动下一个事务，这样省去了begain的开销。
查询长事务方法：下面这个语句，用于查找持续时间超过 60s 的事务。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
第四讲、深入浅出索引（上）
一句话简单来说，索引就跟书的目录一样，就是为了提高数据的查询效率。
一、索引的常见模型
1、常见的索引模型：哈希表、有序数组、搜索树。
2、哈希表：键 - 值（key-value）存储数据的结构。
3、哈希的思路：把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。解决哈希冲突的办法是拉出一个链表。
4、哈希表适用场景：只有等值查询。
5、有序数组：按顺序存储，查询用二分法就可以快速查询，时间复杂度是：O(log(N))，有序数组在等值查询和范围查询场景中的性能就都非常优秀，有序数组查询效率高，更新效率低。
6、有序数组适用场景：只适用于静态存储引擎。（一些不会修改的数据）
7、二叉搜索树：左节点 < 根节点 < 右节点。查询和更新时间复杂度都是O(log(N))。
8、数据库存储大多不用二叉树，原因是索引不仅存在内存中，还要写到磁盘，树过高的话，查询效率慢，所以适用n叉树。
二、InnoDB中的索引模型
1、InnoDB中的索引模型：B+树。每一个索引在 InnoDB 里面对应一棵 B+ 树。
2、索引类型：主键索引和非主键索引。
InnoDB 的索引组织结构
主键索引叶子结点存的是整行数据，也称为聚簇索引。
非主键索引叶子结点存的是主键的值，也称为二级索引。
3、主键索引与非主键索引区别：主键索引只需要搜索ID这棵B+树就能拿到数据。非主键索引是先搜索索引拿到主键值，再到 主键索引树搜索一次，这个过程称为回表。
4、基于非主键索引的查询需要多扫描一棵索引树，所以尽量使用主键索引查询。
三、索引维护
1、B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。
2、一个数据页满了，按照B+算法，新增加一个数据页，然后挪动部分过去，这个过程叫页分裂。会导致性能下降，空间利用率降低大概50%。当相邻的两个数据页利用率很低时会合并，叫做分裂过程的逆过程。
3、从性能和存储空间方面考量，自增主键往往是更合理的选择。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
