Mysql45讲 ——1~5

wuxiren
420 阅读10分钟

第一节  sql执行基本过程

1.1  mysql逻辑架构图


大体来说，mysql分为服务层（跨存储引擎的功能都在这）和存储引擎层。

  • server层包含连接器、查询缓存、分析器、优化器、执行器基本涵盖绝大部分功能，还包括内置函数。
  • 存储引擎负责数据的存储和提取，其架构模式是插件式，可插拔的。支持InnoDB（5.5.5版本后为默认引擎）、MyISAM、Memory等多个存储引擎
  1. 建立连接：第一步就是建立对数据库的连接。连接器负责对客户端建立连接、获取权限、维持和管理连接。在经典TCP连接后连接器开始认证身份，这时需要输入用户名和密码。但是只要连接不断，即使使用超级账号修改该连接的权限也无法感知，show processlist 查看空闲连接。由wait_timeout参数控制断开时间间隔。建立连接的过程比较复杂，砖家建议尽量使用长连接（1.定期断开长连接 2.5.7版本之后可以每次执行一个比较大的操作之后，执行mysql_reset_connection对连接进行资源初始化）
  2. 查询缓存：拿到sql后，他会拿着语句去缓存中查询一波，命中则直接返回结果，效率较高，但是砖家建议大多数情况下不要用缓存，因为缓存失效很频繁，只要对一个表更新，表上的所有查询缓存都会失效。除非系统配置表才适合使用缓存，业务表其实都不适合。8.0版本之后mysql直接删除掉缓存查询功能
  3. 分析器：先做词法分析 ——也就是对sql进行分析识别，select a from db.test ，知道这个语句是个查询语句，接下来就是语法分析，判断这个sql是否合法（就像语言学习的词法与语法概念）
  4. 优化器：优化器就是帮使用者的语句进行优化选择，尽量选一个他觉得最优的执行方式（虽然极少时候会选错）。优化器执行后，sql的执行方案就确定。
  5. 执行器：mysql通过分析器知道你要做什么，照着优化器参谋的作战方案执行。开始执行的时候执行器会判断你对表是否有操作权限，如果没有则返回没有权限错误。如果有则调用引擎提供的接口执行


问题：如果表T中没有字段k，而你提交执行select * from T where k=1，报的错会在那个阶段报出来？？？

当然是分析器阶段


2.1 SQL语句更新过程

2.1.1  redo log（InnoDB特有）

更新古板场景：如果每一次的更新操作都需要写磁盘，接着磁盘也要找到对应记录，然后在更新，整个过程IO成本过大。


替换思路：

  • 先写redo日志、在写磁盘。在磁盘不忙的时候进行数据更新
  • redo日志空间有限。从头开始写，写到末尾又回到开头循环写，write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件 开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录 更新到数据文件
  • write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如 果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下 来先擦掉一些记录，把 checkpoint 推进一下
  • 有了redolog，数据库就有了数据恢复能力


binlog日志（服务器层）

  • redo日志是物理日志，binlog是逻辑日志，记录的是这个语句的原始逻辑
  • redolog是循环写，空间固定会用完。binlog是可以追加写入的，binlog文件写到一定大大小之后会切换到下一个，并不会覆盖以前的日志

redolog的写入方式：两阶段提交

如果不用两阶段提交方式，在写redolog后再写binlog时被打断会出现. 

  • 1.先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候， MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍 然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此， 之后备份日志的时候，存起来的 binlog 里面就没有这条语句。 然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢 失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不 同。
  •  2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写， 崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来， 恢复出来的这一行 c 的值就是 1，与原库的值不同。

其他使用场景：当数据库需要扩容时，常见做法也是用全量备份加上应用的binlog来实现的。也就是说两阶段提交是为了让两个日志的写入状态保持逻辑上一致


3  事务

在mysql中，事务在引擎层级实现

3.1  事务的四大特性：ACID

  1. 原子性：事务不可分割，整体状态一致
  2. 一致性：事务发生前后数据的完整性保持一致
  3. 隔离性：多个用户同时使用事务互不影响
  4. 持久性：事务提交后，对数据的修改就是永久的

3.2  事务隔离级别

  1. 未提交读：会导致脏读
  2. 提交读（Oracle和sql server默认隔离级别）：会导致可重复读也成快照读，会导致不可重复读
  3. 可重复读（Mysql默认隔离级别）：在这个级别下，普通的查询同样是使用的“快照读”，但是，和“读提交”不同的是，当事务启动时，就不允许进行“修改操作（Update）”了，而“不可重复读”恰恰是因为两次读取之间进行了数据的修改，因此，“可重复读”能够有效的避免“不可重复读”，但却避免不了“幻读”，因为幻读是由于“插入或者删除操作（Insert or Delete）”而产生的。
  4. 串行化

3.2  事务隔离的实现方式（可重复读）


当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如 图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统 中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要 得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

回滚日志在当前系统没有更早的日志时删除，所以不建议使用长事务，长事务会锁住回滚日志，导致无法删除，越来越多。

3.3  事务的启动方式

  1. 显示声明启动，begin 或 start transaction。配套的提交语句是 commit，回滚语 句是 rollback。
  2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一 个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。有些客户端启动时默认执行 set autocommit=0命令，这就导致 接下来的查询都在事务中，如果是长连接，就导致了意外的长事务，所以建议使用 set autocommit=1, 通过显式语句的方式来启动事务。


4  索引（上）

索引的意义类比一本新华字典的目录

4.1  索引的常见模型

  1. 哈希表：是一种以键值对存储数据的结构，我们只要输入待查找的key，能以O(1)的速度获取到记录，当出现hash冲突的时候，使用拉链法生成一个相同key的链表，当存在大量hash冲突的时候，这时候查询的效率并不高。且不利于区间查询
  2. 有序数组：有序数组在等值查询和区间范围查询性能优秀O(logn)，但很明显不适合修改较多的场景，他会花费很大的性能去维护他的顺序，成本极高
  3. 二叉树：完全二叉树很明显会有较高的查询效率，但是插入效率随着树的高度增加，进行磁盘IO的次数也大大增加，一旦涉及磁盘其性能直线下降。所以二叉树在磁盘的限制下转化出N叉树，其矮胖的特征保证了磁盘读取次数少，本身的读取速度也不会慢。
  4. B树不管是叶子节点还是非叶子节点都会保存数据，这样导致非叶子节点能保存的指针数量变少，指针少的情况下保存大量数据只能增加树的高度

4.2  InnoDB索引模型

innodb的每一个索引都对应着一颗B+树，根据叶子节点的内容，分为主键索引（也叫聚簇索引）和非主键索引，主键索引的叶子节点存的是记录全部信息，而非主键索引存的是主键，如需获取记录其他字段，需要进行一次回表操作。

4.3  索引维护

  • 当插入数据或者删除数据时，就可能会触发页分裂及页合并，每个页的默认大小为16k，他会维持一个大概的利用率范围。
  • 显然尽量使得一张表有一个主键会使得空间的利用率会比较均匀且不会频繁触发页分裂和合并
  • 主键字段的选取也不要内容过大，因为二级索引的页子节点都是主键，这样会导致其他二级索引占用空间大大增加


5  索引（下）

5.1  覆盖索引

覆盖索引可以在执行查询的时候直接返回查询的列，无需回表

5.2  最左前缀原则

  1. B+树可以利用索引的最左匹配原则，来定位记录
  2. 在建立联合索引时，索引的复用能力是评估标准，如a,b索引，想使用b只能重新为b创建二级索引

5.3  索引下推

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名 字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的

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

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段 值。 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过 程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

可在索引内部进行判断，5.6版本之前要回表四次，而5.6以后能在索引内部进行判断，只需回表两次


avatar
文章
阅读
粉丝