《MySQL 实战 45 讲》 学习笔记 day1

后端要努力
132 阅读9分钟

大家好，我是砸锅。一个摸鱼八年的后端开发。熟悉 Go、Lua。今天和大家一起学习 MySQL😊

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 27 天，点击查看活动详情

基础

MySQL 基础架构：

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，以及所有内置函数，跨存储引擎功能：存储过程、触发器、视图

存储引擎层负责数据的存储和提取，支持 InnoDB、MyISAM、Memory 等多个存储引擎

连接器

连接器负责和客户端建立连接、获取权限、维持和管理连接

mysql -h$ip -P$port -u$user -p

一旦成功建立连接，后续用户权限更改了之后只有在新建的连接才会生效。如果连接之后没有后续的动作，可以通过 show processlist 查看到，Command 那一列显式 “Sleep” 就表示这个是空闲连接

客户端长时间没动静就会自动断开，默认是 8 小时，通过参数 wait_timeout 控制，断开后发送请求会收到 Lost connection to MySQL server during query

长连接是指连接成功之后，客户端如果持续有请求则一直使用同一个连接，短连接则每次执行完很少几次查询就断开连接。尽量使用长连接

长连接缺点是占用内存，因为 MySQL 执行过程中临时使用的内存都是在连接对象里的，只有在连接断开才会释放。因此可以采取定期断开长连接或者是在执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源

查询缓存

MySQL 8.0 去除查询缓存的功能了，因为这个功能作用不大，只是缓存执行过的查询结果。一旦对这个表更新操作，这个表全部查询缓存都会清空

分析器

分析器会对 SQL 语句做解析，先会词法分析，识别出里面的字符串是什么。然后再做语法分析，判断这个 SQL 语句是否满足语法。但是表不存在或者字段不存在，并不是在分析器里做的

优化器

优化器在表里面有多个索引的时候，决定使用哪个索引

执行器

执行器开始执行语句，先判断是否有执行查询的权限，如果有权限就会根据表的引擎定义，去使用这个引擎提供的接口

日志系统

redo log

WAL 技术：Write-Ahead-Logging，先写日志，再写磁盘。当一个记录需要更新时，InnoDB 引擎会先把记录写到 redo log 里面且更新内存，然后在后面适当的时候将这个操作记录更新到磁盘里面

redo log 是 InnoDB 引擎特有的， redo log 是物理日志，记录在某个数据页做了什么修改。而且是固定大小的，在空间里循环写。write pos 是当前记录的位置，checkpoint 是当前要擦除的位置，擦除记录前要把记录更新到数据文件。即使数据库异常重启，之前的记录都不会丢失，这个称为crash-safe

https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/c8a92b72ee4648e8a3472a8d9d9c7532~tplv-k3u1fbpfcp-zoom-1.image

innodb_log_at_trx_commit 设置为 1 时，表示每次事务的 redo log 都直接持久化到磁盘，保证 MySQL 异常重启之后数据不丢失

binlog

Server 层特有的日志：binlog（归档日志），所有引擎都可以用。而且 binlog 是逻辑日志，记录这个语句的原始逻辑，而且 binlog 是追加写入，写到一定大小就会切换下一个，不会覆盖以前的日志

一个 update 语句的执行流程图，浅色框是 InnoDB 内部执行，深色框是执行器，redo log 的写入有两个步骤：prepare 和 commit ，这就是两阶段提交，两阶段提交就是让 redo log 和 binlog 这两个事务提交 状态保持逻辑的一致

sync_binlog 这个参数设置为 1 时，表示每次事务的 binlog 都持久化到磁盘，保证 MySQL 异常重启之后 binlog 不丢失

事务

在 MySQL 里，事务是在引擎层实现的。MySQL 的 MyISAM 引擎不支持事务

ACID（Atomicity、Consistency、Isolation、Durability）原子性、一致性、隔离性、持久性

隔离性和隔离级别

SQL 标准的隔离级别分别是：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）、串行化（serializable）

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到了

读提交是指，一个事务提交之后，它做的变更才会被别的事务看到

可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据是一致的

串行化，对于同一行记录会加读写锁，出现读写冲突时，后面的事务需要等待前一个事务执行完成才可以执行

事务隔离的实现

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

例如将一个值从 1 改成 2、3、4，回滚日志如下：

当系统里没有比这个回滚日志更早的 read-view 时，回滚日志就会被删除

事务的启动方式

  1. 显式启动事务。begin 或者 start transaction，提交语句是 commit，回滚语句是 rollback
  2. set autocommit = 0，这个命令会把线程的自动提交关掉，然后当语句开始执行时就会开启事务。只有手动执行 commit 或者 rollback 语句才会结束这个事务，可能会导致长事务的出现，所以推荐还是 set autocommit = 1
  3. 在 autocommit = 1 的情况下，用 begin 显式开启的事务，如果想 commit 之后自动启动下一个事务，可以执行 commit work and chain 来节省再次执行 begin 语句的开销

索引

索引的常见模型：

  1. 哈希表，一种键值存储数据的结构，通过将值放入数组中，然后用哈希函数将 key 换算成一个确定的位置，然后将 value 放入数组的这个位置。多个 key 值经过哈希函数运算，可能会重现同一个值的情况，也就是哈希冲突。解决方式是链表法或者开放寻址法，缺点是对区间搜索支持不好，所以哈希表这种结构适合等值查询的场景
  2. 有序数组，也就是将数据按照一定顺序保存的结构。优点是等值查询和范围查询的性能都非常好，缺点是插入数据时成本太高，所以有序数组索引适合静态存储引擎
  3. 搜索树的搜索效率是最高的，但是因为它不仅存在内存里，还要写入磁盘中。树的高度越高性能越差，为了查询尽量少的读磁盘，所以要尽可能少读数据块，也就是减少树的高度。所以 N 叉树出现了，N 越大，树的高度越小，读写性能高

InnoDB 的索引模型

InnoDB 采用 B+ 树索引模型，所有数据都是存储在 B+ 树中，每个索引在 InnoDB 里面对应一棵 B+ 树，索引类型分为主键索引（聚簇索引 clustered index）和非主键索引（二级索引 secondary index）。主键索引的叶子节点存储的是整行数据，非主键索引的叶子节点内容是主键的值

如果先搜索二级索引，然后得到主键之后再去聚簇索引搜索一次，这个过程称为回表。因此尽量使用主键查询

索引维护

B+ 树为了维护索引有效性，在插入新值的时候需要做必要的维护。例如插入的数据页已经满了，就需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。或者相邻的两个页由于删除了数据，利用率很低导致数据页需要做合并，这个过程可以认为是分裂过程的逆过程

自增主键的选择，如果采用自增列定义的主键，插入新纪录时可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 来作为下一条记录的 ID 值。如果使用有业务逻辑的字段做主键，则往往不容易保证有序插入，写数据的成本相对较高

除了考虑性能外，从存储空间来看也是自增主键更为合理，因为主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也越小

用业务字段当主键的场景：

  1. 只有一个索引
  2. 该索引必须是唯一索引，由于没有其他索引，也不需要考虑其他索引的叶子节点大小问题

覆盖索引

如果查询语句里的要查的值可以从二级索引树里找到，不需要回表，索引已经覆盖查询需求，称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提高查询性能，所以使用覆盖索引是一个常用的性能优化手段

最左前缀原则

只要满足联合索引的最左 N 个字段，或者是字符串索引里最左 M 个字符，都可以利用索引检索

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

此文章为3月Day1学习笔记，内容来源于极客时间《MySQL 实战 45 讲》

avatar
文章
阅读
粉丝