菜鸟工程师-MySQL基础原理

PeterBear1111
140 阅读19分钟

事先声明，本文只用做本人学习使用，勿用于商业用途，可能会引用各个博客的内容，在文中都会一一声明。

博主是刚毕业工作的菜鸟工程师，工作节奏很快，但是一些开发者所需的知识和经验还停留在浅层，目前在学习一名后端开发工程师的常用的相关工具和中间件。MySQL是最重要最基础的，毕竟因为MySQL使用不当而导致数据出现问题，会造成极其严重的事故。

MySQL的学习，整体是follow极客时间的《MySQL实战45讲》，大家可以去订阅，建议还是去看原版，有很多例子讲解，支持正版，作者非常良心（但是部分内容是DBA需要学习的，对于专注于业务开发的我，目前只总结了部分开发需要的了解的，深度也不足）。

MySQL基础原理

从表的创建开始

# 建表语句
create table test.test_in_for_index(
    id bigint auto_increment,
    c1 int default 0 not null,
    c2 int default 0 not null,
    primary key (id)
)ENGINE INNODB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 设置隔离级别
set global transaction isolation level REPEATABLE READ;

# 查看隔离级别
show variables like '%isolation%';

NOTE：

  • 如果是比较大的表，自增id一定要设定为bigint
  • Primary key = unique + not null
  • unique字段允许为空，且可以插入多个空
  • 存储引擎现在默认都为INNODB，建表时可以手动指定
  • 字符集一般是utf8mb4，可以支持更多字符
  • collate，这个很重要，以_ci(表示大小写不敏感)，以_cs(表示大小写敏感)，以_bin(表示用编码值进行比较)。
  • mysql出厂设置的隔离级别都是Repeatable Read，可以通过SQL指定和查看，且只对后续新建立的链接有效

SQL语句的执行流程

Server

  • 连接器：客户端连接MySQL的组件，包括权限控制、链接释放等。
  • 查询缓存：会根据查询语句作为key去查询是否有该语句的返回结果缓存，查询缓存不建议使用，因为每次更新都会清空缓存，缓存命中很低，8.0后Mysql取消查询缓存。
  • 分析器先会做“词法分析”，识别出里面的字符串分别是什么，代表什么。然后需要做“语法分析”，判断你输入的这个 SQL 语句是否满足 MySQL 语法，平常的语法报错都是由语法分析这一步报错的。
  • 同样一个语句，有不同的条件语句，优化器会判断先执行哪个条件性能更好。比如索引怎么走，该如何进行更高效的join。
  • 执行器：真正执行SQL语句的组件，调用存储引擎提供的接口进行增删改查，存储引擎只负责查数据，该查什么数据，由执行器来决定。

（图源：《MySQL实战45讲》）

链接包括长链接和短链接，一般来说都会使用长链接，不用频繁的进行连接。但是如果客户端陆陆续续都有请求，那么长链接不会被释放，这时候，这个长链接在这几次请求中占用的资源是不会被释放的，有两种方案：（1）断开链接重连；（2）使用 mysql_reset_connection 来重新初始化连接资源。

存储引擎

常见的存储引擎有innodb和myisam，现在mysql都默认使用innodb。二者在事务支持、主键、索引结构上有较大区别，可以参照资料看看。（待补充）

Buffer pool

Buffer pool是存储引擎的一大特性。 （待补充）

查询流程

create table test.test_in_for_index(
    id bigint auto_increment,
    age int default 0 not null,
    name int default 0 not null,
    primary key (id)
)

select id,age,name from T where age=18;

以Innodb为例（B+树索引），查询表T中ID=10的数据。

  1. age有索引
  • (1)根据辅助索引，查找到主键，age=18的数据可能有很多（范围查找），那么可以获得多个主键值；
  • (2)根据主键从主键索引中获取数据行（叶子节点），从（1）中查找到了多个主键，则每个都要走一遍主键索引（回表）。

（1）所以索引尽量要选择distinct的列作为索引（2）考虑建立更有效的联合索引

  1. age无索引
  • 调用 InnoDB 引擎接口取这个表的第一行，判断 age 值是不是 18，如果不是则跳过，如果是则将这行存在结果集中；
  • 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行（扫描行）；
  • 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

日志系统

Redolog（重做日志）

MySQL Redo Log 重做日志

MySQL崩溃恢复功臣—Redo Log

WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。Redolog就是WAL技术的一种实现。Redolog是一个循环写的文件，其实是一个文件组，文件数量和每个文件的大小可以通过变量innodb_log_files_in_group和innodb_log_file_size来设置，这两个变量都是只读变量，只能通过在配置文件中修改并重启的方式生效。默认为4个文件，每个文件1GB，redo log文件的总大小为4GB。

（图源：《MySQL实战45讲》）

Redolog需要在SQL更新语句执行完之后进行更新，但不是直接更新到磁盘，而是有个Redobuffer进行缓存，更新到磁盘的方式有三种：

  • 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次操作。
  • 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。
  • 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk 的操作。

磁盘上的Redolog迟早要满的，因此Redolog有一套擦除机制，擦除就意味着数据更新到数据库。Redolog存储的是每一行更新的结果，在更新数据库的时候，mysql会对要更新的数据进行聚合，提高吞吐量。擦除的时机有：

  • 系统空闲时。
  • redo log文件没有空闲空间时，即write pos追上check point的时候。
  • MySQL Server正常关闭时。
  • 系统内存空间不足时。

在使用redolog的时候，mysql只会更新内存中的数据页，后续的查询会直接从数据页中捞数据，这种数据页称为脏页（与磁盘数据不同步），而没有更新过数据的数据页就是干净页。所以在redolog满了或者内存满了的时候，就需要将脏页flush到磁盘中，刷新磁盘的相关内容可以参考资料:12 | 为什么我的MySQL会“抖”一下？-极客时间。

Binlog（归档日志）

mysql-3 Redo Undo Binlog

为什么需要有binlog？redolog主要用于异常重启，binlog主要用于备份（主从）和还原。

Binlog其实也不只能存储SQL语句，也能存储对数据的具体修改信息，总共有三种类型：

  • ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 slave端对相同的数据进行修改。 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
  • STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到 master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的 SQL再次执行。简称SQL语句复制。 优点：日志量小，减少磁盘IO，提升存储和恢复速度。缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。
  • MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

同时，binlog的刷盘时机和机制也与redolog有所区别：

  • 根据记录模式和操作触发event事件生成log event（事件触发执行机制）
  • 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区 Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
  • 事务在提交阶段会将产生的log event写入到外部binlog文件中。不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。
  • 非事务的缓存在SQL语句执行完成写入到到binlog文件中。

两阶段提交

双阶段提交的意义在于，让两个日志备份之间的数据一致。两阶段提交的具体流程为：

  1. 写入新数据，新数据更新到内存
  2. 写入Redolog的缓存，这时redolog处于prepare状态，这时候redolog已经有了对应的数据变更记录
  3. 写入binlog的缓存
  4. redolog提交，处于commit状态

如果在2崩了，那么更新数据都不存在，数据一致；如果在3崩了，redolog是prepare状态，MySQL恢复的时候，会查看binlog是否存在该redolog的commit id，不存在，则丢弃数据，存在，则将这条数据更新提交；如果在4崩了，与3的处理类似。

Redolog和Binlog的区别

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

ChangeBuffer

顾名思义，changebuffer就是在更新的时候，先将要更新的内容记录在缓存之中。

了解changebuffer之前，需要先知道mysql是如何进行数据查询和更新的。

  • 查询：根据索引，多次IO定位到数据所在页，读取到内存中，在该页上进行查询
  • 更新：如果要更新的页在内存中，则直接更新内存，但不直接刷盘，有后台进程去刷盘，并且记录redolog；如果更新的页不在内存中，则在changebuffer记录要更新哪个页的哪个信息，等到下次读取该页数据到内存中进行merge，或者也由后台进程进行刷盘。

所以，其实在更新场景较多的情况下，changebuffer很有用，因为能减少实时的IO。

但是如果是在更新完就查询的情况下，changebuffer马上会进行merge，某些情况下还会导致性能下降。

唯一索引会导致changebuffer失效，因为唯一索引需要先读数据，确保唯一性约束。

ChangeBuffer和RedoLog的区别

在mysql innodb中， change buffer机制不是一直会被应用到，仅当待操作的数据页当前不在内存中，需要先读磁盘加载数据页时，change buffer才生效。

redo log是为了保证crash-safe，有无用到change buffer机制，对于redo log的区别在于—— 用到了change buffer机制时，在redo log中记录的本次变更，是记录new change buffer item in page XXX相关的信息，而不是直接的记录物理页的变更。

组提交

在提交log的时候，会带着更新其余的log。

（待补充）

事务

08 | 事务到底是隔离的还是不隔离的？-极客时间

事务等级和事务的具体应用是比较熟悉的，但是事务隔离是如何实现的呢？

视图

事务A只能看到事务A开始的数据，这一份数据就是一个视图（read-view），但是需要注意的是，不是说一个视图就是一份数据库的拷贝。

  • 读未提交：直接返回记录上的最新值，没有视图概念；-- 存在脏读
  • 在读已提交：每个SQL新建视图（当前读）；--存在不可重复读
  • 可重复读：每次事务都会新建视图（快照读）；--存在幻读，并且事务提交顺序不同，会导致binlog和实际数据不一致
  • 而串行化：直接用加锁的方式来避免并行访问。

（1）视图还有一种概念，就是一张虚拟表，通过create view 创建。 （2）快照读的幻读MVCC解决了，当前读的幻读用间隙锁解决了。 innodb的默认事务隔离级别是RR（MVCC实现）。 该技术不仅可以保证Innodb的可重复读，而且可以防止幻读。

MVCC

MVCC 只在 REPEATABLE READ 和 READ COMMITIED 两个隔离级别下工作。 InnoDB 中的 MVCC 其实是通过在每行记录后面保存两个隐藏的列来实现的。一列是事务 ID：trx_id；另一列是回滚指针：roll_pt。视图实际上是MVCC的产物，基于trx_id实现。

简单解释一下上图，上图中的V其实就是一个记录的多个版本，U就是一个Undolog。即一个记录更新后，旧的记录仍然存在，更新的数据增加了一行和原本相同的数据，但是更新的列、版本号、回滚指针不同。表现在数据中，就是每一行记录，都有一个trx_id，用于标记该记录是由哪个事务生成的。并且通过roll_pt（回滚指针，undolog）指向这条数据的上一个版本。

每个事务通常只能看到该事务的trx_id之前的数据（不准确，实际上有一个算法进行控制）。但是由于长事务的存在，可能trx_id大的事务先结束了事务，并且更新了某行记录的DB_TRX_ID，那么trx_id较小的长事务可能会将该行数据的DB_TRX_ID更新为当前长事务的较小的trx_id。

当前读

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。因此，一个事务中的select只能看到版本号小于等于自身版本号的记录。

但是，在RR级别下，在update的时候，是进行当前读操作的，如果不进行当前读，是会丢失更改的数据的。并且后续的数据都基于trx_id这个快照点去读取的。因此，如果其他事务1早提交了，那么其更改对于当前事务2的update语句是可见的，事务2更新了数据，也更新了版本号。在update前的select语句是看不见其他事务的更改的，但是在update后，此时的记录的版本号是该事务本身，因此select语句是可以看见新数据的，但是是覆盖后的数据，这算是一个后门？

同时，还有一种情况也会是当前读，就是在select语句加了锁的情况，这时必须进行当前读，不然也会造成数据不一致的问题。

有疑问可以看这个RR和RC的例子：小胖问我:MySQL 事务与 MVCC 原理? - 掘金

长事务

一个值在数据库中会存在多个版本的数据，如果需要获取上一个版本的数据，则需要用到对应的回滚日志，每次修改都会增加一个回滚日志。回滚日志是会进行删除的，但是只会在不存在比该回滚日志更早的read-view时才会进行删除，所以，如果有一个事务的跨度特别长，就会导致后续事务的回滚日志无法删除，占用巨大的空间，因此要尽量避免长事务。

数据库索引

索引模型

  • 哈希：精确搜索很快，但是无法进行范围查找
  • 有序数组：搜索很快，但是每次更新比较费劲
  • 搜索树：搜索和更新的时间复杂度都是logN

搜索树的索引类别

  • 聚簇索引：数据存放在叶子节点，方便进行范围查找，并且树会更宽更矮，减少IO
    • 主键索引：索引key为主键，叶子节点存放的是数据行
    • 辅助索引：索引key为其余字段，叶子结点存放的是主键，需要回表
  • 非聚簇索引：数据存可存放在非叶结点，导致非叶节点的分叉较少，且无法范围查找

B+树索引

具体的索引流程

  1. B+树的每一个叶子大概是4KB，也就是磁盘数据读取的一个基本页块（block）大小。从根节点开始，走B+树的分叉，每次读取一个页到内存，判断该数据所在的下一个页块。
  2. 如果1是辅助索引，则查找到主键数据，走主键索引回表进行IO获取数据。
  3. 如果1是主键索引，直接查找到数据，返回。
  4. 如果2是范围索引，在辅助索引的叶子节点范围查找，多次回表返回数据。
  5. 如果3是范围索引，则在主键索引的叶子结点范围查找，返回。

索引更新

  • 页分裂
  • 页合并

节点分叉数量的调整

  • key大小
  • page大小

联合索引

覆盖索引

如果要查询的字段，在联合索引中都存在了，则成为索引覆盖。因为不需要进行回表取数，可以快速返回查询结果。

前缀索引

联合索引不是在所有条件下都生效的，查询条件需要满足最左前缀原则。

  • like aa%后模糊查询索引有效，like %aa模糊查询索引无效
  • 索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
  • mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。

索引下推

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

辅助索引如果有相同的数据， 会按照主键再次排序。

数据库锁

从作用范围来看，数据库的锁可以分为全局锁、表锁、行锁、间隙锁以及next-key lock，next-key lock有点特殊，后续进行分析。全局锁就不做过多的说明了。

从功能来看，主要分为写锁（排他锁）和读锁（共享锁）。只要是获取排他锁，就不能有其他任何任何锁。

表锁

表锁分为两种，一种就是直接锁住整个表的读写操作，比如lock table t1 read,t2 write;；另一种是MDL（metadata lock），顾名思义，元数据锁，就是一个表的描述性数据。

对表进行DDL操作或者写操作时，需要获取MDL写锁，而查询数据时，是需要获取MDL读锁的，试想一下，如果在更新行时或者更新表结构时，出现了并发更新，那这个操作肯定是会出问题的。

  • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MDL 锁是系统默认会加的，某些时候会出现一个现象，就是获取MDL写锁A时，被未完成的查询的MDL读锁B阻塞，这时候，后续的所有读锁都会被A阻塞，导致整个表不可读。

行锁

MYISAM只支持表锁，但是表锁太大，影响并发。而Innodb是支持行锁的，大大提高了表的并发读写能力，在 InnoDB 事务中，行锁是在执行到update或者insert语句的时候才加上的，而不是事务开始就加上了。但行锁并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。因此，尽量把影响面较大的行锁的语句放在最后去执行。

间隙锁

间隙锁是一种非常奇特的想法，是为了解决幻读而开发出来的，顾名思义就是锁住一段数据。怎么锁呢？锁对象是索引而不是整个数据行，用到了哪个索引就锁哪个索引，此外，在不同类型索引上，锁有各种不同的优化。

行锁只锁住了一行数据，它可以解决不可重复读的问题，比如多个获取读锁的线程可以重复读而不会有脏数据，写锁会进行阻塞。但是如果是插入数据呢，那行锁无能为力了呀，都没有新插入的这个行，怎么去锁呢？因此，间隙锁就是用于处理这个新插入的数据的幻读问题。

Next-key lock（NKL）

Next-key lock=间隙锁+行锁，是RR隔离下的Innodb默认的加锁方式，规则如下：

  • 查询中涉及到的列，如果有索引，就加在这索引上；没有索引就加主键索引上
  • 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁；等值查询，向右/向左（根据order by的升序降序确定想做向右） 遍历时且最后一个值不满足等值条件的时候，NKL退化为间隙锁
  • NKL 是前开后闭区间
  • 查找过程中访问到的对象才会加锁
  • NKL 互相之间不冲突，不同线程可以对同一个区间进行加锁
  • update会给主键索引加锁
avatar
文章
阅读
粉丝