MySQL基础篇 上

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

先看下面的MySQL基本架构示意图，你可以清楚的看到SQL语句在各个功能模块的中的执行过程。

大体来说，MySQL分为Server层和存储引擎层两部分，不同的存储引擎共用一个Server层。

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

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。从5.5开始InnoDB为默认存储引擎。

下面来看每个组件的作用。

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。经常使用的mysql -h -P -u -p连接命令就是由连接器负责。在完成经典的TCP握手后，连接器就要开始认证身份，验证账号和密码。

• 如果用户名和密码不对，会收到Access denied for user错误。
• 如果验证通过，连接器会到权限表里面查出你拥有的权限。之后连接里面的权限判断逻辑，都依赖于此时读到的权限。意思是在读取完权限之后的权限修改，重新连接才会生效。

连接完成后，如果没有后续动作，这个链接处于空闲状态，通过show processlist命令可以看到，Command列显示为Sleep的表示这是空闲连接，如下所示：

客户端如果太长时间没动静，连接器将自动将连接断开，该时间由参数wait_timeout控制，默认为8小时，通过show variables like wait_timeout命令查看该参数的值。

当连接被断开时，客户端再次发送请求会收到一个错误提醒：Lost connection to MySQL server during query。这时候需要重新连接才能继续执行请求。

数据库里面的长连接指的是客户端不断有请求，一直使用的是同一个连接，短连接指的是每次执行很少的请求就断开连接。

因为建立连接的过程通常是比哈欠较复杂的，尽量减少建立连接的次数，所以建议使用长连接。

但全部使用长连接，会导致MySQL内存占用涨的特别快，因为MySQL在执行过程中临时使用的内存时管理在连接对象里面的。这些资源在连接断开的时候才释放。 长连接累积下来的，可能会导致内存占用太大，被系统强行杀掉(OOM)，从现象看就是MySQL异常重启了。

考虑以下解决方案

1. 定期断开长连接，使用一段时间或者判断执行过一个内存占用比较大的操作后，断开连接，之后查询在进行重连。
2. 在MySQL 5.7 或更更新版本，判断执行过一个内存占用比较大的操作后，执行mysql_reset_connection来重新初始化连接资源，这个过程不需要重连和重新做权限验证，它会将连接恢复到刚刚创建完时的状态。

查询缓存

连接建立后，执行select一句，来到第二步：查询缓存。

MySQL拿到一个查询请求后，会先到查询缓存看看，查看是否执行过这条语句，之前执行过的语句以key-value对形式缓存在内存中，key为执行语句，value是查询结果，如果与缓存中的key匹配，则直接将value返回给客户端。

如果缓存未命中，继续执行后面的阶段，执行完成后，执行结果会放入缓存中。

查询缓存失效非常频繁，如果对一个表进行更新，这个表上的所有查询缓存都会失效，所以查询缓存只适用于更新不频繁的静态表，MySQL也提供了"按需使用的"方式，你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

select SQL_CACHE * from T where ID=10；

MySQL 8.0版本也直接移除了查询缓存功能。

分析器

查询缓存未命中的情况下进入分析器，分析器先对sql语句做词法分析，MySQL需要识别出里面的字符串分别是什么，代表什么，从select关键字识别出这是一条查询语句，将对应字符串识别成表名，列名。

词法分析没问题紧接着进入语法分析。根据语法规则判断传入的SQL语句是否满足MySQL语法，不满足就会收到You have an error in your SQL syntax的错误提醒。一般语法错误提示第一个出现错误的位置，所以你要关注的是紧接use near的内容。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引，在一个语句有多个关联(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。

执行逻辑结果是一样的，执行效率不一样，优化器的作用就是决定使用哪个方案。

执行器

MySQL通过分析器知道该做什么，通过优化器知道了该怎么做，紧接着进入执行器。

开始执行的时候，会先判断当前用户对该表是否有查询的权限，如果没有，则返回没有权限的错误，如下所示。查询也会在调用优化器之前调用precheck验证权限。考虑到查询缓存的情况，在查询缓存返回的结果的时候验证权限。

select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就根据表的引擎定义，去使用这个引擎提供的接口。

拿上面举例子来说，当上面ID字段没有索引时，那么执行器的执行流程是这样的：

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

至此，这个语句就执行完成。

对于有索引的表，执行的逻辑也差不多，第一次调用的是“取满足条件的第一行“接口，之后循环取”满足条件的下一行“这个接口，这些接口都是引擎中已经定义好的。

在数据库的慢查询日志可以看到一个rows_examined字段，表示这个语句执行过程中扫描了多少行。这个值是执行器每次调用引擎获取数据行的时候累加的。

但在有些场景下，执行器调用一次，引擎内部扫描了多行，所以引擎扫描行数跟rows_examined并不是完全相同。

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

更新语句大致上与查询语句的执行流程差不多，首先连接数据库，这是连接器的工作，接着清除该表上的查询缓存，因为是更新表的缘故。接下来，分析器通过词法和语法分析知道这是一条更新语句。优化器决定使用索引等执行方案。然后执行器负责具体执行。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log(重做日志)和binlog(归档日志)。

重要的日志模块：redo log

如果数据库的每次更新操作都需要写进磁盘，磁盘需要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高，为了解决这个问题，MySQL的设计者使用WAL技术来提升更新效率。WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

IO成本解释引用如下：

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

先提下随机IO和顺序IO的概念：

顺序IO是指读写操作的访问地址连续。在顺序IO访问中，HDD所需的磁道搜索时间显着减少，因为读/写磁头可以以最小的移动访问下一个块。数据备份和日志记录等业务是顺序IO业务。
随机IO是指读写操作时间连续，但访问地址不连续，随机分布在磁盘的地址空间中。产生随机IO的业务有OLTP服务，SQL，即时消息服务等。

每次更新操作都直接写入磁盘的话，会产生大量的随机IO，效率较低。所以需要先写入日志，具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面(这个过程是顺序IO，效率更高)，并更新内存，这个时候就算完成了。InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，这个更新往往是系统比较空闲的时候做。

InnoDB的redo log是固定大小，比如可以配置为一组4个文件，每个文件大小1GB，总共可以记录4GB的操作。从头开始写，写到末尾时，redo log记录满了时，会将一部分记录写到磁盘中，然后redo又重头开始写，如下所示：

write pos是当前记录位置，一边写一边后移，check point表示当前要擦除的位置，当write pos到达check point位置表示日志写满了，会将一部分数据写入磁盘中，同时check point后移到已写入磁盘那部分数据的位置。

有了redo log，InnoDB就可以保证数据库发生异常重启，之前提交的记录也不会丢失，这个能力称为crash-safe。

重要的日志模块： binlog

redo log是InnoDB引擎持有的日志，而Server层持有的日志，称为binlog。原因是因为最开始的MySQL没有InnoDB引擎，InnoDB引擎是另一个公司以插件引入MySQL，binlog日志没有crash-safe能力，所以InnoDB使用了另外一套日志系统，也就是redo log。

两种日志的不同点：

1. redo log是InnoDB引擎持有的，binlog是MySQL的Server层实现的，所有引擎都可以使用。
2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”，binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID =2 这一行的c字段加1”。
3. redo log是循环写的，空间固定那么大，binlog当写满一个文件，是会切换到另一个文件进行写入的，并不会覆盖以前的日志。

下面是执行器和InnoDB引擎在执行下面这个update语句时的内部流程。

update T set c=c+1 where ID=2;

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

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

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

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

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

这里引入一个Buffer Pool的概念，读入内存和第2步的写入这行新数据都是写入InnoDB的Buffer Pool。

Buffer Pool实例，大小等于innodb_buffer_pool_size/innodb_buffer_pool_instances，每个Buffer Pool Instance都有自己的锁，信号量，物理块(Buffer chunks)以及逻辑链表(List)。即各个instance之间没有竞争关系，可以并发读取与写入。所有instance的物理块(Buffer chunks)在数据库启动的时候被分配，直到数据库关闭内存才予以释放。每个Buffer Pool Instance有一个page hash链表，通过它，使用space_id和page_no就能快速找到已经被读入内存的数据页，而不用线性遍历LRU List去查找。注意这个hash表不是InnoDB的自适应哈希，自适应哈希是为了减少Btree的扫描，而page hash是为了避免扫描LRU List。

当innodb_buffer_pool_size小于1GB时候，innodb_buffer_pool_instances被重置为1，主要是防止有太多小的instance从而导致性能问题。

数据页

InnoDB中，数据管理的最小单位为页，默认是16KB，页中除了存储用户数据，还可以存储控制信息的数据。InnoDB IO子系统的读写最小单位也是页。

数据页是mysql中磁盘和内存交换的基本单位，通过改变 innodb_page_size 选项对默认大小进行修改。一次最少从磁盘读取16KB内容到内存中，一次最少把内存中16KB内容刷新到磁盘中，当然了单页读取代价也是蛮高的，一般都会进行预读。

以下是上述update语句执行的流程图：

最后三步将redo log拆分成了两个步骤，prepare和commit，这就是"两阶段提交"。

为什么需要两阶段提交

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

1. 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
2. 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

如果不是两阶段提交，也许可能在写完redo log的时候，还没来得及写binlog，机器突然挂了，导致binlog与redo log记录的数据更新记录不一致。又或者先写完binlog，再写redo log的时候挂了，两者记录的数据更新记录又不一致。

所以两阶段提交是为了保证binlog与redo log的记录的数据更新记录是一致的。

除误删表需要恢复临时库的情况，还有当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

小结

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

知识扩充

innodb_flush_log_at_trx_commit 参数说明

innodb_flush_log_at_trx_commit={0|1|2} # 指定何时将事务日志刷到磁盘，默认为1。 0表示每秒将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。 1表示每事务提交都将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。 2表示每事务提交都将"log buffer"同步到"os buffer"但每秒才从"os buffer"刷到磁盘日志文件中。

物理和逻辑的概念

• 物理：InnoDB存储引擎提供接口给执行器调用（操作数据），数据库的数据是存在磁盘上的（或者说硬盘上），那么redo log记录存储引擎修改硬盘上的数据的操作就叫做物理操作；
• 逻辑：binlog归档日志，有两种模式 1.statement记录sql; 2 row格式记录两条数据，数据修改前的样子，数据修改后的样子。记录的是一种逻辑上的变化 。

逻辑日志可以给别的数据库，别的引擎使用，已经大家都讲得通这个“逻辑”；

物理日志就只有“我”自己能用，别人没有共享我的“物理格式”。

事务隔离，为什么你改了我还看不见？

事务就是要保证一组数据库操作，要么都成功，要么都失败。在MySQL中，事务支持在引擎层实现的。但并不是所有引擎都支持事务，比如MyISAM引擎就不支持事务。

事务有4个特性：原子性、一致性、隔离性、持久性，又称为ACID。其中隔离性在MySQL有各种隔离级别的区分，为了解决数据库上多个事务同时执行的时候，出现的脏读，不可重复读，幻读的问题。

隔离性与隔离级别

SQL标准的事务隔离级别包括：读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(serializable)。下面是关于各种隔离级别的解释：

1. 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
2. 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
3. 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
4. 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

针对上面的隔离级别举例说明。假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

create table T(c int) engine=InnoDB;
insert into T(c) values(1);

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。事务B获取写锁会一直阻塞到A提交完成，读写操作阻塞。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

Oracle数据库的默认事务隔离级别是"读提交"，MySQL数据库的默认事务隔离级别是"可重复读"，对于Oracle往MySQL迁移的应用，为了保证数据库事务隔离级别一致，需要将MySQL的事务隔离级别设置为"读提交"。

配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值。

mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

需要可重复读的场景：

假设你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

事务隔离的实现

这里对”可重复读“进行说明，在MySQL中，每条记录在更新的时候都会同时记录一条回滚操作。记录上最新的值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

read-view表示是每个事务启动时创建的，因为是“可重复读”。在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。即使有另一个事务正在将4改成5，这个事务跟read-view A、B、C对应的事务也不会冲突。因为有锁的控制。

回滚日志在不需要的时候才会被删除。当没有事务用到这些回滚日志的时候，回滚日志会被删除，也就是当事务提交了，系统里没有比这个回滚日志更早的read-view。

长事务

长事务指的是系统里面存在很老的事务视图。这些事务在未提交之前回滚记录会大量占用存储空间。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，所以需要尽量避免使用长事务。

事务的启动方式

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

   执行一条select语句，innodb_trx表中就多了一条RUNNING的事务，执行commit 该条记录又被删除了。

有些客户端连接框架默认连接成功后先执行一个set autocommit=0的命令，导致了接下来查询都在事务中，如果是长连接，就导致了意外的长事务。

所以一般都先使用set autocommit = 1设置为自动开启提交事务。

对一个需要频繁使用事务的业务，可以使用commit work and chain 语法，提交事务并开启下一个事务（少执行一个begin语句）

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

你可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

如何避免长事务对业务的影响？

从应用开发端来看

1. 确认是否使用了set autocommit=0，这个可以通过MySQL的general_log日志查看，发现有这个情况关闭它。
2. 去掉不必要的只读事务。
3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）

从数据库端来看

1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
2. Percona 的 pt-kill 这个工具不错，推荐使用；
3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

innodb_undo_tablespaces是控制undo是否开启独立的表空间的参数 为0表示：undo使用系统表空间，即ibdata1 不为0表示：使用独立的表空间，一般名称为 undo001 undo002，存放地址的配置项为：innodb_undo_directory 一般innodb_undo_tablespaces 默认配置为0，innodb_undo_directory默认配置为当前数据目录