MySQL数据库高级教程[图灵学院周瑜老师] Summary for videos

一、索引

B树：排好序且一个节点可以有多个元素。
B+树：一个节点有多个元素，叶子结点之间存在指针。所有非叶子节点的元素都在叶子结点上冗余了一份。
insert into t1 values(1,2,3,4,'a');，假设1所在的字段为主键，那么MySQL在插入数据时排序。
Innodb一页默认的大小为16kb，是存储数据的最小基本单位，对于一条指令，例如select * from t1 where a=1，默认每次从磁盘取出一页放入内存进行查询比较，达到减少磁盘IO的效果。

在往用户数据区域插入数据时，会在每次插入操作时按照索引的大小插入适当的位置。为了提高查找的性能，创建了页目录，页目录对应着用户数据区域的分组，查找时，会在页目录使用二分查找来找到数据对应的组。
当一页的数据满了之后，会新开辟一页，如果索引不是自增的插入的话，那么插入每一页的数据都会跟前面的页相比较与调整位置，来插入到排序好的位置。

随着页的数量越来越多，也会变成一个长链表，所以为了提高效率，会新开辟一页，在其中存一个排序的节点，每个节点按照排序指向每一页，这样可以使用二分查找的方法找到对应的页中的数据。

所以索引存储的数据结构为数据结构。

B+树的四个特点：
- 排序。
- 一个节点存在多个元素leaf pages。
- 叶节点之间存在指针。
- 非叶子结点上的元素都冗余了一份在叶子结点。
叶子节点是数据页，存储的是所有数据；非叶子节点是索引页，目的是提高效率。如果从B+树的叶子节点一个一个查找，则为全表扫描；如果从B+树的根节点往下查找，则为走索引。
例子：
- select * from t1 where a=5：走索引。
- select * from t1 where a>5：走索引，先找a=5，那么目标叶子结点之后的所有叶子节点都为目标值。
- select * from t1 where a<5：走索引。
- select * from t1 where c=5：全表扫表，因为索引对应的是a字段的值。
联合索引：create index idx_t1_bcd on t1(b,c,d)，产生一个B+树，按照b、c、d三个字段进行排序，排序规则为先比较b字段，再比较c字段，最后d字段。

上述图片的索引不是最终的索引，因为叶子结点存储了所有的数据，如果存在很多的索引页，就会造成内存浪费，因此可以只存储b、c、d三个相关字段的值，并用另一个值存储主键索引a字段，可以根据a字段找到主键索引中数据页的位置，此操作为回表。如下图，只冗余了必要的信息。

联合索引要遵循最左匹配原则。在MySQL8值之前，所有索引都是升序索引，在MySQL8之后，支持降序索引。
例子：
- select * from t1 where b=1 and c=1 and d=1：走索引。
- select * from t1 where c=1 and d=1：全表扫描。
- select * from t1 where b=1：走索引。
- select * from t1 where b>1：全表扫描，因为对于每一个目标叶子节点都要进行回表，成本大于全表扫描。
- select * from t1 where b>6：走索引，回表次数少，成本少。
- select b,c,d from t1 where b>1：走索引，因为不需要回表。
- select b,c,d,a from t1 where b>1：走索引，因为a也在索引页中，不需要回表。
- select b from t1：(所有走法：在主键进行全表扫描、在索引B+树的叶子结点扫描)走索引扫描，因为索引B+树的叶子节点不完整，一页存储的更多，因此走索引扫描。
- select * from t1 order by b,c,d：(所有走法：走索引不用排序但是要回表、全表扫描要排序)，全表扫描+排序，因为此时(数据小)排序的时间很小。
- select b from t1 order by b desc,c,d：走索引，再额外进行排序。
建立索引：create index idx_t1_e on t1(e)，e字段为字符，举例如下：
- select 'a'=0，值为1，把字符转换为数字，因此所有非数字字符串转换为数字都为0，数字字符转换为对应的数字，如'1'转换为数字为1，'1a'转换为数字为1。
- select * from t1 where e = 1：不能走索引，因为要把字符转换为数字，但是字符是一个字段，如果走索引代表把所有的字段值转换为数字。
- select * from t1 where e = '1'：能走索引。
- select * from t1 where a = 1：能走索引。
- select * from t1 where a = '1'：能走索引，把字符'1'转换为数字。
- select * from t1 where a = '1a'：能走索引，把字符'1a'转换为数字1。
- select * from t1 where a+1 = 1：不能走索引，因为如果走索引要把字段的所有值都加1。
- 因此只要对字段进行操作(隐式的类型转化、加减操作等)都不能走索引。

二、内存结构与磁盘结构

Buffer Pool：当第一次取数据时，把磁盘中的数据放到Buffer Pool中，而下次查找相同的数据时，直接从Buffer Pool中取数据。
free链表：Buffer Pool内存中存储的数据在某时刻可能是散乱的，也就是不连续的。free链表除基节点之外的所有节点都指向Buffer Pool的空闲位置。

flush链表：除基节点之外的所有节点都指向Buffer Pool的脏页(被更新过的)。
简化版lru链表：用一个页时，都在lru链表进行头插，代表最近用到的数据。也就是链表中越靠前，越为热点数据。当Buffer Pool满了时，淘汰lru链表尾部的节点。缺点：换血(在执行例如全表扫描时，会把lru链表的所有节点全部淘汰掉，会把热点数据也淘汰了，影响性能)。
lru链表：把lru链表分为两个区域，一个占5/8(前面的，热数据区域)，一个占3/8(冷数据区域)。淘汰是优先淘汰冷数据区域，调用数据时优先加在冷数据区域的头结点位置，当冷数据中的数据又被访问时，且和上次此数据访问的时间差大于1s，就把此数据移动到热数据区域的头节点位置。
redo log和bin log：
- 对于一个update：先修改Buffer Pool里面的数据页(脏页)->update语句生成redo log->redo log持久化(事务提交的时候才持久化，先放到log buffer中)、bin log持久化->修改成功。如果MySQL挂掉了，再读取，会把磁盘中的数据和redo log中的记录进行整合放在Buffer Pool中。
- redo log有两个文件记录，分别为ib_logfile0、ib_logfile1，当两个文件都满了时，会触发一次检查点，把一部分数据持久化到磁盘(优化方法：调大文件、调多文件个数。但是MySQL重启会很慢)。
- bin log和redo log的区别：bin log是MySQL中的概念，redo log是innodb的概念。bin log记录的是SQL语句，redo log记录的是对哪一页的哪个位置进行修改。
- undo log：反向操作日志，例如源数据为7，被改成6，那么此日志中记录update x=7，如果将来回滚的话，会用undo log中的数据更新Buffer Pool中被更改的数据。事务隔离级别也是通过undo log来实现的。
log buffer：因为当执行事务时，不确定是否回滚或提交，也就不确定是否要持久化，所以对于这部分数据用log buffer存储。
innodb_flush_log_at_trx_commit：
- 为0时：表示事务提交时，不立即对redo log进行持久化，这个任务交给后台线程去做。
- 为1时：表示事务提交时，立即把redo log进行持久化。
- 为2时：表示事务提交时，立即把redo log写到操作系统的缓冲区，并不会直接将redo log进行持久化，这种情况下，如果MySQL挂了但操作系统没挂，那么事务的持久性仍可以保证。
数据页默认为16kb，操作系统中的页默认为4kb。Double_write_buffer如下，但是如果磁盘支持把16kb一次性写入，就可以不用开启Double_write_buffer。

Change buffer(默认为Buffer Pool的25%)：MySQL语句更新时，除了更新数据页，还要更新索引页。Change buffer会存储对索引页的update语句，如果需要从磁盘中取出索引页，其存储的为老数据，因此需要把此索引页和Change buffer中的update语句整合放入Buffer Pool中。