知识回顾

知识回顾

基础架构

服务端分为两大层：

• server 层
• 存储引擎层

server层

层提供 连接器，分析器，优化器，执行器。

• 连接器 客户端和连接器层进行里连接，最好不要使用-p 后面带密码，可能会泄漏密码。一般是长连接，如果8小时(默认值)没有动作会断开。长连接使用需要注意查询的时候是否会有特别大的查询，如果有需要定时断开连接，这样服务端内存才会释放。
• 查询缓存 可以使用SQL_CACHE 关键字指定使用缓存。但是主要对一个表有更新，这个表上的所有缓存都会失效，所以并没有什么卵用。除非表更新特别少。
• 分析器 语法分析。
• 优化器 一个sql 可以有多种执行方案，以及索引的选择，执行器就是评估哪一种更加合适。
• 执行器 校验权限，以及把sql翻译成调用存储引层的接口调用来实现。

存储引擎层

不同的存储引擎。 InnoDB,MyISAM,Memory 提供结构给server层进行调用。

更新语句执行

查询的话和就是执行基础架构总结的server层以及存储引擎层就可以了。但是更新操作还有额外的一些动作。主要涉及到 *redo log 重做日志 以及 binlog 归档日志。
分别：

• redo log 是InnoDB 特有，binlog 是Server层所有，所有的引擎都可用。
• redo log 是物理日志，-> 在某个数据页做了什么修改。binlog 逻辑日志 -> 给ID 2 中字段c + 1.
• redo log 循环写，文件大小固定。binlog 追加写，自动切换到下一个文件，不会覆盖。

redo log

类比了孔乙已中老板记账的过程 老板脑袋记账 -> 看板记账 -> 账本。redo log 就相当于是看板记账。也就是 WAL 技术 （write-Ahead logging） logging 先行技术。先写日志，后写磁盘，当系统空闲的时候写入磁盘。
redo log是个环形队列。其中还有write pos checkpoint 的概念，问题不大。因为有了redo log 就有了crash-safe 功能。

binlog

Server 层的日志，由于只有InnoDB 有binlog 日志。为了保障所有的存储引擎都有crash-safe 能力，就需要使用binlog了。

更新操作

一个更新操作主要涉及的是：

1. 写redo log, 状态为prepare
2. 写binlog
3. 提交事务，更新redo log 为commit 状态。

这是使用了两阶段提交，如果不使用都会导致回复时候的不一致性。关于以上3个动作主要的疑问点在于：
在 2阶段前后如何处理？
这里分为重启恢复和备份恢复。需要保障的是这两者的一致性。

• 2 之前挂了
• • 重启恢复时 没有commit。-> 回滚。
• • 备份恢复时 没有binlog。和重启恢复一致。
• 3 之前崩溃
• • 重启恢复 虽然没有commit 但是prepare 和binlog 一致，重启后commit。
• • 备份恢复 有binlog，和重启恢复一致。

设置每次刷磁盘

• innodb_flush_log_at_trx_commit 1 设置每次事务redo log 刷磁盘。
• sync_binlog 1 设置每次事务都刷 binlog 磁盘。

事务隔离

事务隔离等级分为4等。

• 读未提交 (read uncommited) 事务还没有提交，做的改变其他事务可以看到。
• 读提交 (read commited) 一个事务提交后事务可以被看到
• 可重复读 (repeatable read) 事务执行过程中读到的数据和启动时候看到的一致
• 串行化 (serializable) 一个事务执行完后，另一个事务才能执行。

级别越高，效率越低。文中有描述的例子。InnoDB 是使用MVCC 版本控制来实现事务隔离的。Oracle 默认的隔离级别是 读提交。

mysql 中更新删除操作除了redo log之外，还有undo log，也就是回滚日志。通过回滚日志可以找到之前的状态值。文中也有描述。
在启动事务的时候不同时刻会有不同的read-view，通过回滚日志就可以看到启动时刻对应的值。从而实现了MVCC 多版本并发控制。

回滚日志是落磁盘的。那么什么时候删除呢？
当系统没有比这个回滚日志更早的view 的时候我们就可以删除。

• 为什么尽量不适用长事务？
  当我们使用长事务的时候会创建view 。这时候会一直保留在磁盘没法删除，导致磁盘负载变高。

索引

索引的目的就是为了更快的查询到数据。类比书籍的目录。

索引模型

实现索引的方式有很多种。常见的有 哈希表 有序数组 搜索树

哈希表

使用 key-value 的形式进行存储。把值放在数组里，使用哈希函数把key换算成固定的位置，把value 放入。
当多个key 映射成同一个位置的时候就使用链表法来存储。
哈希表 不适合使用range [a,b] 的形式。这种形式下只能够遍历这个哈希表。
哈希表只能使用与等值查询的场景。

有序数组

有序数组是经过排序的数组。从查询的加多来看 等值，范围查询都非常的高效。但是插入操作就非常蛋疼。
所以有序查询适合于 静态存储引擎

树模型

二叉树保持一个父节点有两个子节点，左节点小于父节点。右节点大于父节点。查询的复杂度是O(log(N))。为了维持平衡二叉树时间复杂度也是O(log(N))。
树有二叉树也有N叉树。二叉树的搜索效率最高，但是索引不止在内存，还会在磁盘中，考虑到磁盘的代价更高。所以使用的是N叉树。
N取决于数据块的大小。

例子

InnoDB 一个整型字段索引。N 相当于1200. 树高为4 存1200 的3次方为17亿。 得出一个10亿行数据的数据，使用索引只需要访问磁盘3次。根节点一定是在内存中的。

1200 从哪里来呢？介绍
节点存储都是按照页为单位：mysql 页大小一般 16k。bigint 8字节(64bit)。 指针在mysql为6字节。一共数量为 16384/14=1170≈1200

InnoDB 索引

使用 B+ 树。
B树 节点上即是数据。B+树，只有叶子节点才是数据。

• InnoDB 索引分为聚簇索引和普通索引。聚簇索引的叶子节点就是行数据。而普通索引的叶子节点是主键节点的值。 也就是说如果是普通索引是先查找到主键之后，通过主键索引查找到行数据。比主键查找多了一倍的磁盘IO。

where k between 3 and 5

搜索过程
通过k 索引 找到3对应的ID = 300。300-> 行数据。 遍历叶子节点直到大于500.取行数据。这个过程称之为 回表

如果我们只是需要ID 不需要行数据。当我们指定 select id from tab where k = xx;这个时候索引已经满足了ID的查找，就不需要回表。称之为 覆盖索引。

最左匹配原则

存在索引 a,b 那么 a 索引一般就没有必要创建。

索引下推

mysql的优化，无需关注。

全局锁 表锁

全局锁

对整个数据库实例加锁。 FTWRL Flush tables with read lock. 处于只读状态。

• DDL Data Definition Language 数据库定义语言 CREATE
  ALTER
  DROP
  TRUNCATE
  COMMENT
  RENAME
• DML Data Manipulation Language 数据操纵语言 SELECT
  INSERT
  UPDATE
  DELETE
  MERGE
  CALL
  EXPLAIN PLAN
  LOCK TABLE
• DCL Data Control Language 数据库控制语言 授权，角色控制等 GRANT 授权
  REVOKE 取消授权
• TCL Transaction Control Language 事务控制语言 SAVEPOINT 设置保存点
  ROLLBACK 回滚
  SET TRANSACTION

表级锁

两种

• 表锁 lock tables … read/write
• 元数据锁 meta data lock 对表 增删改查 加 读锁 改变表结构 加写锁 可以理解 元数据是就表的基础结构信息

行锁

行锁在需要的时候加上，但是只有在commit的时候才释放，所以加锁的顺序也很重要。参考原文

死锁和死锁检测

策略

• 直接进入等待，直到超时。
• 发起死锁检测，发现死锁后，回滚死锁链的某一条事务。

第二种方式会导致每个事务都检测与此同时的事务是否有死锁。1000个并发检测会导致100w量级。消耗大量CPU。
思路:

1. 业务确保不会死锁，临时关掉检测。
2. 控制并发量。
3. 一行的逻辑改成多行，减少锁冲突。

事务隔离讨论

关键词 快照 MVCC 高低水位 视图 本章节图比较多 需要看文章