Loading

MySQL 45讲笔记(1)

一条SQL查询语句是如何执行的

mysql> select * from T where ID=10；

image

大体来说，MySQL可以分为Server层和存储引擎层两部分

  • Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

  • 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎。

  • 不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分

连接器

  • 第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在show processlist命令中看到它。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是8小时。数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

查询缓存

  • 连接建立完成后，你就可以执行select语句了。执行逻辑就会来到第二步：查询缓存。
  • MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。
  • 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

大多数情况下不要使用查询缓存，因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。分析器先会做“词法分析”,词法分析完后就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒

优化器

  • 经过了分析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。
  • 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

比如你执行下面这样的语句，这个语句是执行两个表的join：
mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器

  • MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
  • 开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误
  • 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

mysql> select * from T where ID=10;
比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：
调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

  • 至此，这个整个语句就执行完成了。一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

一条SQL更新语句是如何执行的

  • 与查询流程不一样的是，更新流程还涉及两个重要的日志模块 redo log（重做日志）和 bin log（归档日志）
  • 每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。为了解决这个问题，MySQL的设计WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘
  • 当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。
  • 同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。但是InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB,总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。
  • 在进行redo log写入时，有两个重要参数的write pos(当前记录的位置),checkpoint是当前要擦除的位置

image

  • 一边写一边后移，写到第3号文件末尾后就回到0号文件开头,checkpoint也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos和checkpoint之间还空着的部分，可以用来记录新的操作。

  • 如果write pos追上checkpoin，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

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

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

  • 最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是redo log来实现crash-safe能力。

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

执行器和InnoDB引擎在执行这个简单的update语句时的内部流程:

  • 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

  • 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

  • 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。

  • 执行器生成这个操作的binlog，并把binlog写入磁盘。

  • 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

  • redo log的写入拆成了两个步骤：prepare和commit，这就是"两阶段提交"。

image

1、prepare阶段 2、写binlog 3、commit

当在2之前崩溃时，重启恢复：后发现没有commit，回滚。备份恢复：没有binlog。

当在3之前崩溃，重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

总结

  • Redo log不是记录数据页“更新之后的状态”，而是记录这个页 “做了什么改动”。

  • Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

事务隔离

  • 事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。MySQL默认的MyISAM引擎就不支持事务，这也是MyISAM被InnoDB取代的重要原因之一。

  • 事务的特性：ACID即原子性、一致性、隔离性、持久性。多个事务同时执行的时候，就可能出现脏读，不可重复读，幻读，为了解决这些问题，就有了“隔离级别”的概念。但是隔离得越严实，效率就会越低

  • SQL标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

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

  2. 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

  3. 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。未提交的更改对其他事务是不可见的

  4. 串行化是指对应一个记录会加读写锁，出现冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

  • 在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
  • 在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。
  • “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念。
  • “串行化”隔离级别下直接用加锁的方式来避免并行访问

查看数据库的事务隔离级别 show variables like '%isolation%';

事务隔离的实现

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

image

  • 回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的read-view的时候。

  • 为什么尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。

  • 事务启动方式：一、显式启动事务语句，begin或者start transaction,提交commit，回滚rollback；二、set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。

  • 建议使用方法一，如果考虑多一次交互问题，可以使用commit work and chain语法。在autocommit=1的情况下用begin显式启动事务，如果执行commit则提交事务。如果执行commit work and chain则提交事务并自动启动下一个事务

深入浅出索引（1）

索引的常见模型

  • 索引的出现是为了提高查询效率，常见的三种索引模型分别是哈希表、有序数组和搜索树

  • 哈希表：一种以key-value 存储数据的结构，哈希的思路是把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。哈希冲突的处理办法是使用链表。哈希表适用只有等值查询的场景

  • 有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))。查询效率高，更新效率低（涉及到移位）。在等值查询和范围查询场景中的性能就都非常优秀。有序数组索引只适用于静态存储引擎。

  • 二叉搜索树：每个节点的左儿子小于父节点，右儿子大于父节点。查询时间复杂度O(log(N))，更新时间复杂度O(log(N))。数据库存储大多不适用二叉树，因为树高过高，会适用N叉树

InnoDB 的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。每一个索引在InnoDB里面对应一棵B+树。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。这个表的建表语句是：

create table T( id int primary key, k int not null, name varchar(16), index (k))engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下。

image

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

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

基于主键索引和普通索引的查询有什么区别？

  • 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
  • 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

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

深入浅出索引（2）

  • 覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据
  • 最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
  • 联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。
  • 索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度
posted @ 2023-08-31 10:14  Xianhao  阅读(12)  评论(0)  编辑  收藏  举报