MySQL基础知识

MySQL基础架构

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

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

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

一条SELECT语句是如何执行的

连接器

​ 第一步，你需要先连接到这个数据库上。

1
2
3
4
5
6
7
8
9
10

mysql -h主机名 -u用户名 -p密码 -P端口号

-h表示服务进程所在的计算机域名或ip地址，不填写默认为本机localhost
-u表示用户名，不填写在类Unix系统是登入操作系统的用户名、Windows系统是环境变量里的USER
-p表示密码
-P表示端口号，不填写默认为3306

各个参数之间没有顺序要求，需要注意的是密码最好不要在命令中输入

执行mysql -h主机名 -u用户名 -p之后在输入密码更安全

​ MySQL采用TCP作为服务器与客户端之间的网络通信协议。在完成 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

• 如果用户名或密码不对，你就会收到一个”Access denied for user”的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，这就意味着，一个用户成功建立连接后，即使你对这个用户的权限做了修改，也不会影响已经存在连接的权限。

​ 连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。客户端如果太长时间没动静，连接器就会自动将它断开，这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。同时MySQL服务支持的最大连接数由 max_connections 参数控制。

​ 我们也可以手动断开连接，通过kill connection +连接id。

查询缓存

​ MySQL 拿到一个SELECT语句后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的SELECT语句能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端，不需要执行后面的复杂操作。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

​ 但是大多数情况下建议不要使用查询缓存：

• 查询缓存的失效非常频繁，只要有对一个表的更新（表的数据或者结构），这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。
• 查询缓存的触发非常苛刻，SELECT语句必须完全匹配才能触发，任何一个字符（如注释、大小写等）不同都无法触发，此外如果SELECT语句中包含某些系统函数（如NOW）、用户自定义变量和函数、系统表（如information_schema），则该语句无法使用查询缓存。

​ 所以，MySQL 8.0 版本直接将查询缓存删掉了。

分析器

​ 对SELECT语句进行分析，判断语法是否正确。包含词法解析、语法分析、语义分析等阶段。

优化器

​ 在分析器执行之后，就从SELECT语句中获取到了需要的信息，如要查询的表和字段、搜索条件等。但光有这些是不够的，因为我们写的语句执行起来效率可能并不是很高，优化器会对我们的语句做一些优化，如表达式简化、子查询转为连接查询等。最终会生成一个执行计划，这个执行计划表面了应该使用哪些索引进行查询，以及表之间的连接顺序等。我们可以使用EXPLAIN语句来擦看某个语句的执行计划。

执行器

​ 接下来就开始真正执行SELECT语句了，MySQL的不同存储引擎为Server层提供了统一的调用接口，通过调用这些接口获取数据。

索引

​ 索引就像书的目录一样，通过索引可以快速定位到想要获取到的数据的位置，提高数据查询的效率。在 MySQL 中，索引是在存储引擎层实现的，不同存储引擎的索引的工作方式并不一样，即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。接下来将基于InnoDB存储引擎探讨索引。

​ 无论是主键索引还是非主键索引，InnoDB存储引擎均使用的是B+树进行存储。B+树是一种多叉树，真实数据存储在叶子节点上，上层均为索引数据，并且每个节点的数据根据索引的定义按照顺序存放。主键索引的叶子节点存的是整行数据，非主键索引的叶子节点存放的是主键的值。

​ 非主键索引也称为二级索引，在基于非主键索引进行查找时，若不满足索引覆盖，则还需根据非主键索引存储的主键值进行回表操作。

索引覆盖

​ 在基于非主键索引进行查找时，若需要查找的字段均出现在该非主键索引上，则无需进行回表操作从主键索引中获取其他字段的数据，这称之为索引覆盖。

最左前缀匹配法则

​ 前面有提到索引中每个节点的数据根据索引的定义按照顺序存放，如按照name、age建立的联合索引，由于索引是按照该顺序进行组织的，当不在where中使用name，却使用age进行查找时，该索引就无法使用，这就是最左前缀匹配法则。

​ 若有一个按照name、age、sex建立的联合索引，根据name、sex进行查找时，就不满足最左前缀匹配法则，会导致索引部分失效，只能够根据name在索引中进行查找。在新版本的MySQL中，引入了索引下推进行优化，能够根据sex先进行数据的筛选，以减少不必要的回表操作（不满足索引覆盖时）。

事务

​ 简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败（ACID属性）。在 MySQL 中，事务支持是在引擎层实现的，MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务，通过show engines查看MySQL的存储引擎，可以看到，InnoDB 存储引擎是支持事务的，接下来的讨论都将基于 InnoDB 存储引擎。

​ 事务的四个属性如下：

• 原子性（Atomicity）：一个事务中的所有操作，要么全部成功，要么全部失败，不会结束在某个中间状态，事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。

• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

持久性是通过 redo log （重做日志）来保证的，原子性是通过 undo log（回滚日志） 来保证的，隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的，一致性则是通过持久性+原子性+隔离性来保证。

隔离性

​ 当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read），读到其他事务未提交的数据；不可重复读（non-repeatable read），事务前后读取的数据不一致；幻读（phantom read），事务前后读取的记录数量不一致的问题，为了解决这些问题，就有了“隔离级别”的概念，隔离级别越高，效率越低。

​ SQL 标准的事务隔离级别包括以下几种：

• 读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。可能会发生脏读、不可重复读、幻读。
• 读提交（read committed）：一个事务提交之后，它做的变更才会被其他事务看到。可能会发生不可重复读、幻读。
• 可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交的变更对其他事务也是不可见的。可能会发生幻读。
• 串行化（serializable）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

​ 实现上，数据库里面会创建一个读视图（Read View），访问的时候以读视图的逻辑结果为准。在“读未提交”隔离级别下直接返回记录上的最新值即可，无需使用读视图。在“读提交”隔离级别下，这个读视图是在每个 SQL 语句开始执行的时候创建的。在“可重复读”隔离级别下，这个读视图是在事务启动时创建的，整个事务存在期间都复用这个读视图。在“串行化”隔离级别下直接用加锁的方式。

读视图是如何工作的

​ 首先，在读视图中有四个字段：

• creator_trx_id：创建该读视图的事务的id
• m_ids：创建该视图时未提交的事务的id集合
• min_trx_id：创建该视图时未提交的事务中最小的事务id
• max_trx_id：下一个被创建的读视图的id（预分配的id）

​ 同时，数据在存储时，会有一些隐藏字段，如trx_id，记录该条数据是由哪个事务修改的；roll_pointer回滚指针，指向回滚日志undo log中该条数据的历史版本（多版本并发控制MVCC，同一条数据在系统中可以存在多个版本）。

​ 在事务对数据进行访问时，会按照指定的规则进行访问：

• 当数据的trx_id = 读视图的creator_trx_id

  此时该条数据是由当前事务产生的，可见。

• 当数据的trx_id < 读视图的min_trx_id

  此时产生该条数据的事务已经提交，可见。

• 当数据的trx_id ≥ 读视图的max_trx_id

  此时该条数据是在当前读视图创建之后被修改的，不可见。

• 当数据的trx_id 在读视图的min_trx_id到max_trx_id之间

  需要再通过读视图的m_ids进行判断，当数据的trx_id不在m_ids时，产生该条数据的事务已经提交，可见，否则不可见。

​ 会通过数据的roll_pointer在undo log中访问该条数据的历史版本，通过指定的规则找到当前读视图可见的该条数据的版本。通过该规则，事务只要使用相同的读视图即可实现可重复读。

可重复读隔离级别下的幻读

​ 更新数据都是先读后写的，为了防止其他事务丢失修改，这个读，只能读当前的值，称为“当前读”（current read），区别于之前的快照读。

​ 在可重复读隔离级别下，当某条数据的某个字段val值为1时，有A、B两个事务开启后，A事务将字段val修改为val+1并提交事务，此时B事务也进行该操作，虽然B事务根据快照读读取到的值为1，但修改后val的值为3。

日志

​ MySQL的日志种类包含 redo log （重做日志）、 undo log（回滚日志）、binlog（归档日志）。其中binlog是Server层实现的，其余两种日志是由InnoDB存储引擎层实现的。

redo log

​ redo log重做日志，是物理日志，记录数据的物理变化，如页xxx偏移量yyy写入数据zzz，在数据库发生意外时用来进行故障恢复。在MySQL对数据进行修改时，修改的是内存中的数据页的数据，为了优化性能，并不会及时的将数据页立即保存到磁盘，而是使用WAL（Write-Ahead Logging）技术，先写日志，再写磁盘，有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失。采取循环写的形式。

binlog

​ binlog归档日志，是逻辑日志，记录数据的逻辑变化。用于归档。采取追加写的形式。

两阶段提交

​ 在MySQL的 InnoDB 存储引擎中，开启 binlog 的情况下，MySQL 会同时维护 binlog 日志与 InnoDB 的redo log，为了保证这两个日志的一致性，MySQL 使用了XA 事务。

​ 内部流程如下，其中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

1. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
2. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
3. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

undo log

​ 回滚日志，记录的是逻辑操作日志，比如对某一行数据进行了INSERT语句操作，那么undo log就记录一条与之相反的DELETE操作。主要用于事务的回滚（undo log 记录的是每个修改操作的逆操作）和一致性非锁定读（MVCC）。

锁

全局锁

​ 全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份，适用于不支持事务的存储引擎，支持事务的存储引擎可以使用mysqldump配合–single-transaction开启一个事务进行备份（可重复读隔离级别）。

表级锁

​ 表级锁分为两类：表锁、元数据锁（MDL）。

表锁

​ 使用lock tables … read/write加锁，如lock tables t1 read, t2 write，unlock tables释放锁。如果在某个Session A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，Session A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作，不允许写 t1 ，同时也不能访问其他表。

元数据锁

​ 元数据锁不需要显示的使用，在访问一个表的时候会被自动加上。MDL 的作用是保证读写的正确性，防止执行期间另一个线程对这个表结构做改变。需要注意的是MDL写锁的优先级更高，当Session A获取了MDL读锁后并未提交事务，此时Session A还占有MDL读锁，之后 Session B 获取写锁会被 blocked，由于MDL写锁的优先级更高，后续再来其他Session需要对该表进行任何的增删改查语句都需要等Session B获取写锁并释放后才能进行。

行级锁

​ MySQL 的行锁是在引擎层由各个引擎自己实现的。在InnoDB存储引擎中支持行锁。在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

​ 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁处理

​ 在MySQL中，出现死锁一般有以下两种策略：

• 直接进入等待，在innodb_lock_wait_timeout 后超时。
• 将参数innodb_deadlock_detect设置为 on，开启死锁检测，MySQL会回滚代价最小的事务。

部分内容转载自：丁奇《MySQL 实战 45 讲》