MySQL实战笔记01

这部分笔记是学习专栏的总结，这篇笔记的包括如下内容：

• 一条sql查询语句是如何执行的
• 一条sql更新语句是如何查询的
• 事务的隔离

 

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

select * from T where ID = 10

上面这条查询语句是如何执行的，我们先把MySQL拆解一下看看里面都有哪些零件。这样我们碰到MySQL的一些异常或者问题时，就能够直戳本质，更为迅速的定位并解决问题。下面是MySQL基本架构的示意图，会做一些特殊说明。   

    

MySQL大致可以分为Service层和存储引擎层。service层包括连接器、查询缓存、分析器、优化器、执行器（图中没有画出来），这一层涵盖的大多数的核心服务功能，以及所有的内置函数，所有的跨存储引擎操作都是在这一层完成的，比如存储过程、触发器、视图等等。

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

现在我们来看一下开头的那条SQL是如何执行的：

连接器

第一步你会连接到数据库上，这里需要的就是连接器，连接器负责跟客户端建立连接、获取权限、维护和管理连接，连接命令如下

mysql -h$ip -P$port -u$user -p

虽然密码可以直接写在-p命令后面，但是这样容易造成密码泄露。连接命令中mysql是客户端工具，用来跟服务器建立连接。在完成经典的TCP握手后，连接器就开始认证你的身份，用的是用户名和密码。

• 用户名密码错误的话就会收到“Access denied for user”的错误，让客户端程序结束执行。
• 如果通过验证，连接器就会到权限表中查询出你所拥有权限，之后这个连接里面的权限判断都会依赖于此时读到的权限。

这就意味着，一个账户建立连接后，即使你用管理员账号修改这个用户的权限，也不会影响已经存在的连接，只有新建连接后，才会使用新的权限设置。如果一个客户端太长时间没有动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认是8小时。

在数据库里面，长连接指的是连接成功之后，如果客户端有持续的请求，则一直使用同一个连接，短连接是指每次执行完很少的几个查询就断开连接，下次查询再重新建立一个。

查询缓存

建立连接完成之后，就可以执行select语句了。来到第二步：查询缓存。

MySQL拿到一个查询请求后，会先查看缓存，以前是不是执行过这条语句。之前执行的语句及其结果会以key-value的形式保存到内存中，key是查询语句value是查询结果，如果可以在缓存中找得到key，这个value直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成之后结果会被放入缓存中。

但是大多数情况下会建议不使用查询缓存，因为查询缓存往往弊大于利。查询缓存失效非常频繁，只要对表有更新，这个表上的查询缓存都会被清空，对于更新压力大的数据库，查询缓存的命中率非常的低。除非你的业务就是一张静态表，很长时间才会做更新，这种表上的查询才适合用查询缓存。

你可以将参数query_cache_type设置成DEMAND，这样默认的SQL语句都不会查询缓存，如果你需要查询缓存，可以使用SQL_CACHE显示的去指定。

select SQL_CACHE * from T where ID = 10;

分析器

如果没有命中缓存，就要开始真正的执行语句了，首先MySQL需要知道你要做什么，因此要对SQL进行解析。

分析器会先做 “词法分析” ，做完这些识别之后要做 “语法分析” 。如果你的语句不对，就会收到 “You have an error in your SQL syntax ”，一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接着 “use near ”的内容。

优化器

经过分析器，MySQL就知道你要做什么了，在开始之前还需要经过优化器的处理。优化器是在表里有多个索引时，决定用哪一个索引；或者在多表关联时，决定各表的连接顺序。优化器阶段完成之后，就正式进入执行器阶段。

执行器

在开始执行的时候会判断你对这个表T有没有执行权限，如果没有会返回没有权限的错误。

如果有权限就打开表继续执行，使用这个引擎提供的接口。

如果T表的ID字段没有索引执行顺序如下：

1、调用InnoDB引擎接口取这个表的第一行，判断ID的值是不是10，如果不是则跳过，如果是将这行存到结果集中。

2、调用引擎接口的“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3、将遍历结果中所有满足条件的行组成记录集作为结果集返回给客户端。

对于有索引的表，逻辑差不多，第一次调用的是“满足条件的第一行”，循环条件是“满足条件的下一行”这个接口，接口都是数据库定义好的。

你会在数据库的慢查询日志中看到一个rows_examined字段，记录这个语句在执行过程中扫描了多少行。

 

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

你可能听过DBA同事说MySQL可以恢复到半个月内的任意一秒的状态，你是不是好奇这是怎么做到的？

这还是要从一个表的一条更新语句说起，下面是一个创建表的语句，这个表有一个主键ID和一个整型的字段c

create table T(ID int primary key , c int);

如果要将ID = 2这一行值加一，SQL可以这么写

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

来看一下这个语句是怎么执行的，首先可以确定地说查询语句的那一套流程，更新语句同样走一遍。

执行之前先连接数据库，这是连接器的工作。在表上有更新的时候，更这个表有关的缓存结构都会被清空，这也是我们不建议使用查询缓存的原因。接下来分析器会通过语法和词法分析解析知道这是一个更新语句。优化器决定要使用ID这个索引。然后执行器负责具体执行。与查询不同，更新流程还涉及两个重要的日志模块redo log （重做日志）和 binlog（归档日志）。

redo log

在MySQL中，如果每一次更新都要写进磁盘，然后磁盘也要找到对应的记录再进行更新，整个过程IO成本、查找成本都很高。MySQL通过WAL技术技术来提高更新效率，WAL全称是Write-Ahead Logging，他的关键是先写日志，再写磁盘。具体来说，当有一条记录需要更新时InnoDB引擎会把记录先写到redo log中，并更新内存，这个时候更新就算完成了。同时InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘中，而这个更新往往是在系统比较空闲的时候做。InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这个日志就可以记录4GB的操作。从头开始写，写到末尾又回到开头循环写。有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个功能称为crash-saft。

binlog

从MySQL整体来看包括两块，一个是service层，它主要是做MySQL 功能层面的事情，还有一块是引擎层，负责存储相关的事宜，redo log是InnoDB引擎特有的日志，而service也有自己的日志叫做binlog。

这两种日志有以下三点不同

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

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

3、redo log 是循环写的，空间固定会用完，binlog是追加写的，不会覆盖之前的日志。

接下来看一下执行器和InnoDB引擎在内部是如何执行update语句的

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

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

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

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

5、执行器调用引擎的提交事务接口，引擎把刚刚的redo log改成提交状态。

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

两阶段提交

为什么要有两阶段提交，简单来说是为了两份日志之间的逻辑一致，回到开头的问题，怎样让数据库恢复到半个月内的任意一秒的状态？

通过binlog，如果DBA承诺半个月内可恢复，那么备份系统中一定包含最近半个月的所有binlog，同时系统会定期做整库备份。可以一天一备，或者一周一备份。

如果需要恢复到指定的一秒状态，比如某天下午两点，你可以这么做：

• 找到离这个时间最近的一次全量备份，从这个备份恢复到临时库
• 从备份的时间点开始，将binlog依次取出来，重放到两点的那个时刻

 

三、事务隔离

在MySQL中事务的支持是在引擎层，但不是所有的引擎都支持事务，比如MySQL原生的MyISAM就不支持事务。提到事务，肯定会想到ACID，即原子性、一致性、隔离性、持久性。这部分主要讲解I，也就是隔离性。

当数据库上有多个事务的时候，可能会出现脏读、不可重复度、幻读等问题。为了解决这些问题，就有了事务隔离级别，SQL标准的隔离级别包括：读未提交（read uncommited）、读提交（read commited）、可重复读（repeatable read）、串行化（serializable）。

• 读未提交：一个事务该还没有提交时，他做的变更就能被其他事务看到。
• 读已提交：一个事务提交后，他做的变更才会被其他事务看到。
• 可重复读：一个事务执行过程所看到的数据，总是和这个事务启动时看到的数据是一致的。在可重复读级别下，未提交的变更对于其他事务也是不可见的。
• 串行化：对于同一行记录，“写”会加“写锁”，读会加“读锁”。当出现读写冲突时，后访问的事务必须等前一个事务执行完，才能继续执行。

事务A	事务B
启动事务 查询得到值1	启动事务
	查询得到值1
	将1改成2
查询的到值v1
	提交事务B
查询的到值v2
提交事务A
查询的到值v3

 

 

 

 

 

 

 

 

（看一下在不同隔离级别下，v1、v2、v3返回值是什么）

 

• 读未提交：v1的值为2，此时事务B还没有提交，但是结果已经被A查询到了。v2、v3的值也是2。
• 读已提交：v1是1，v2是2，v3是2。
• 可重复读：v1、v2是1，v3是2，因为在可重复读下，事务在执行期间看到的数据必须前后一致。
• 串行化：v1、v2是1，v3是2，因为事务B在执行将1改为2时，会被锁住，直到事务A提交后才能继续执行。

事实上，数据库里面会创建视图，访问的时候以视图逻辑为准。在可重复读隔离级别，这个视图是在事务启动时创建的，整个事务期间都在使用这个视图，读提交是在SQL语句执行的时候创建的，读未提交隔离界别在直接返回记录上的最新值，没有视图概念，串行化直接使用加锁的方式避免并行访问。

Oralce数据库默认的事务隔离界别是读提交，MySQL是可重复读。对于一些从Oracle迁移到MySQLde应用程序，要将MySQL的事务隔离级别改为读提交。配置方式是将transaction-isolation的值设置为READ-COMMITTED。

事务隔离的实现

这里以可重复读为例：

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

假设一个值从1被按顺序改成了2、3、4，在回滚日志里就会有记录。当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view，在视图ABC中，这个是记录的值是1、2、4。对于同一个记录在系统中可以存在多个版本，这就是数据库多版本控制MVCC。回滚日志并不会一直保存，系统会判断，当没有事务在需要这些回滚日志的时候就会删除这些日志。

基于以上的说明，来讨论一下为什么建议你不要使用长事务。

长事务意味着系统中会存在很老的事务视图，由于这些事务随时可能会访问数据库里面的任何数据，所以这个事务提交前，他可能用到的所有回滚记录都必须保留，这会占用大量的存储空间。

事务的启动方式

MySQL事务的启动方式有如下几种：

1、显示启动事务的语句，begin或者start transaction，配套的提交语句是commit，回滚语句是rolback。

2、set autocommit = 0 这个命令会将这个线程的自动提交关掉。这意味着你如果执行一个select语句，这个事务就已经开启了，并且不会自动提交。这个事务会一直1存在到你主动执行commit语句或者rollback语句。建议总是使用set autocommit = 1，通过显示语句的方式启动事务。