读书笔记：《 MySQL 实战 45 讲》，MySQL 基本原理介绍，极客时间笔记

MySQL 基础架构，主要包括 Server 层和储存引擎层

  • Server 端
    • 连接器：管理连接、权限验证
    • 分析器：词法分析、语法分析
    • 优化器：执行计划生成、索引选择
    • 执行器：操作引擎、返回结果
  • 存储引擎层负责数据的存储和提取。场景的存储引擎有 InnoDB( 5.5 之后为默认)、MyISAM、Memory

逻辑架构图：

日志系统 redo log 和 binLog

1
2
3
mysql> create table T(ID int primary key, c int);

mysql> update T set c=c+1 where ID=2;

InnodDB redo log

如果每次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。为了解决这个问题，MySQL的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

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

  • WAL技术：Write-Ahead Logging，先写日志，再写磁盘
  • 当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里，并更新内存。其它合适时间再写入磁盘

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

MySQL binlog（归档日志）

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

redo log 和 binlog 不同点

  • redo log 是 InnoDB 引擎特有的，binlog 是 MySQL的 Server 层实现的，所有引擎都能用
  • redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑
  • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的，写到一定大小会切换到下一个文件，不会覆盖以前的日志

我们来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

  • 执行器先找引擎取 ID = 2 这一行。 ID 是主键，引擎直接用树搜索找到这一行。如果ID = 2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
  • 执行器拿到引擎给的行数据，把这个值加上 1 ，比如原来是 N ， 现在就是 N+1 ，得到新的一行数据，再调用引擎接口写入这行新数据
  • 引擎将这行数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状体啊。然后告知执行期执行完成了，随时可以提交事务
  • 执行器生成这个操作的 binlog，并把 binlog 写入磁盘
  • 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成 提交（commit）状态，更新完成

redolog两阶段提交

redo log 和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

事务隔离

事务：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

隔离级别：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

事务隔离的实现：在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

长事务：系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

事务启动方式

  • 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback
  • set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接

建议总是使用set autocommit=1, 通过显式语句的方式来启动事务。避免长连接导致长事务。

查询长事务

1
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60; -- 查找持续时间超过60s的事务

深入浅出索引

索引常见模型

  • 哈希表 ( 查询效率不高 )
  • 有序数组 （ 增删效率低，适合静态存储 ）
  • 搜索树

InnoDB 的索引模型

索引类型

  • 主键索引

    主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）

  • 非主键索引

    非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）

1
2
3
4
5
mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;
  • 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树 ；
  • 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

覆盖索引

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

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

最左前缀原则

只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

索引下推 （index condition pushdown)

1
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。在我们的这个例子中，只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次