基础知识

  • mysql默认的引擎是InnoDB(MySQL5.5.5版本起)

三大范式

  • 属性具有原子性，不可再分解；
  • 不存在部分依赖；
  • 不存在传递依赖；

MySQL InnoDB 概览

InnoDB的数据存储在表空间，表空间是由InnoDB管理的一个黑盒子,由一系列的数据文件组成。InnoDB采用MVCC来支持高并发

MySQL执行mysql逻辑及架构图.png

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

  • 连接器
    • 先连接上数据库，这个时候接待的就是连接器，连接器负责根客户端建立连接，获取权限，维持和管理连接
  • 查询缓存
    • MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
    • 可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。(MySQL8.0没有查询缓存)
  • 分析器
    • 分析语法是否正确
  • 优化器
    • 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：
    • 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。
  • 执行器
    • 如检查权限等，执行SQL

IO成本

IO成本就是寻址时间和上线文切换所需要的时间，最主要是用户态和内核态的上下文切换。我们知道用户态是无法直接访问磁盘等硬件上的数据的，只能通过操作系统去调内核态的接口，用内核态的线程去访问。 这里的上下文切换指的是同进程的线程上下文切换，所谓上下文就是线程运行需要的环境信息。首先，用户态线程需要一些中间计算结果保存CPU寄存器，保存CPU指令的地址到程序计数器（执行顺序保证），还要保存栈的信息等一些线程私有的信息。 然后切换到内核态的线程执行，就需要把线程的私有信息从寄存器，程序计数器里读出来，然后执行读磁盘上的数据。读完后返回，又要把线程的信息写进寄存器和程序计数器。 切换到用户态后，用户态线程又要读之前保存的线程执行的环境信息出来，恢复执行。这个过程主要是消耗时间资源。

MySQL更新

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做
update语句执行流程.png

两种日志不同

  • redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  • redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
  • 只要刷入磁盘的数据，都会从 redo log 中被抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。

这就是为什么说 redo log 具有崩溃恢复的能力，而 bin log 不具备。

redolog使用场景

  • 作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。

binlog使用场景

  • binlog的主要使用场景有两个，分别是主从复制和数据恢复。
    • 主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
    • 数据恢复：通过使用mysqlbinlog工具来恢复数据。


      binlog刷盘时机

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

  • 0：不去强制要求，由系统自行判断何时写入磁盘；
  • 1：每次commit的时候都要将binlog写入磁盘；
  • N：每N个事务，才会将binlog写入磁盘。

看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。 同 两阶段提交原理是一样的问法

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

undo log

回滚日志

作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容：逻辑格式的日志，在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于 redo log 的。

other

qps:每秒处理的查询数
tps:每秒处理的事务数

分析SQL

  1. set @@profiling=1;
  2. select ....
  3. show profiles;

查看当前会话所产生的所有 profiles的耗时