数据库入门之学习《MySQL实战45讲》第1讲-第5讲笔记

《MySQL实战45讲》第1讲

MySQL 的基本架构

MySQL 可以分为 Server 层和存储引擎层两部分。

• Server 层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL的大多数核心服务功能，以及所有的内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• 存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory 等多个存储引擎。

查询缓存

在大多数情况下不建议使用，因为弊大于利，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。注意，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说在MySQL 8.0 版本不会再有这个功能了。

《MySQL实战45讲》第2讲

redo log（重做日志）

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。那应该怎么做呢？

为解决该问题，MySQL采用了WAL技术。WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

具体的说，就是当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的，它从头开始写，写到末尾就又回到开头循环写。

write pos 是当前记录的位置，一边写一边后移。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

binlog（归档日志）

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

redo log与binlog的不同：

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

一条update语句的执行流程

我们可以看到，上面redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

两阶段提交

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。

《MySQL实战45讲》第3讲

隔离性

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

SQL 标准的事务隔离：

• 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交：一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
• 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

为什么要避免使用长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

事务隔离的实现

每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。

《MySQL实战45讲》第4讲

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”。

索引模型

• 哈希表：插入速度很快，因为只用往后面直接添加就行了。因为不是有序的，所以哈希索引做区间查询的速度是很慢的。适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL引擎。
• 有序数组：在等值查询和范围查询场景中的性能就都非常优秀。如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎（不会再修改数据）。
• N 叉树：由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在B+ 树中的。B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

索引类型

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引。
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引。

区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)

使用自增主键的优势

• 性能：每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
• 存储空间：主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

《MySQL实战45讲》第5讲

覆盖索引

select * from T where k between 3 and 5有回到主键索引树搜索的过程，我们称为回表。我们发现，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，不需要回表。我们称其为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

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

在建立联合索引的时候，如何安排索引内的字段顺序

因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下推

在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。