MYSQL基础知识——逻辑结构、并发控制、事务、多版本控制和存储引擎

最新推荐文章于 2025-12-05 18:26:01 发布

原创最新推荐文章于 2025-12-05 18:26:01 发布 · 428 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

技术专栏收录该内容

5 篇文章

订阅专栏

本文深入解析MySQL的逻辑结构、并发控制、事务管理、多版本控制和存储引擎等关键概念，对比InnoDB与MyISAM特性，助您掌握数据库底层运作。

本篇文章会简单对MYSQL的逻辑结构、并发控制、事务、多版本控制和存储引擎等知识点进行梳理。

1 MYSQL逻辑结构

图1-1 MYSQL逻辑结构图
MYSQL逻辑结构如图所示，最上层服务主要包括连接管理、授权认证等；第二层服务是整个MYSQL的核心层，查询解析、优化、缓存以及所有内置函数的功能都在该层实现；第三层包含了存储引擎，由存储引擎负责数据存储和提取，由于各个存储引擎各有特点，为了屏蔽不同存储引擎之间的差异，服务器会通过API和存储引擎进行通信，存储引擎之间不会相互通信，而是简单响应上层服务器的请求。
每个客户端连接到服务器时，服务器会对其进行安全认证，认证成功后客户端会被分配一个线程，该线程只能轮流在CPU中运行，服务器会继续验证客户端都拥有的权限。值得一提的是，如果每创建一个连接就创建一个新的线程，那么资源就会被多余消耗，为了改善这一问题，服务端对线程进行了缓存。

1.1 优化

对于客户端的查询请求，MYSQL首先会进行解析并创建解析树，然后对解析树进行各种优化：选择表读取的顺序、选择合适的索引等等，用户可使用 explain 关键字请求优化器解释优化过程，便于用户重构SQL语句，使应用更高效的运行。
对于SELECT语句，在解析查询之前会先查询缓存，若查询能命中缓存直接返回缓存中的结果集，若无法命中，再执行查询解析、优化、执行的整个过程。

2 MYSQL的并发控制

只要多个客户端在同一时刻修改数据就会产生并发控制的问题。本节将会讨论MYSQL在服务器层和存储引擎层的并发控制。

        多个客户端在同一时刻读取数据时是安全的，因为不会修改数据，但一旦有客户端对数据进行了修改，就可能会读到不一致的数据，解决此问题的方法思想是：通过引入由共享锁和排它锁组成的锁系统，或者叫做读锁和写锁。读锁是共享的，多个客户端同一时刻的读取操作不会被干扰；写锁是排他的，一个写锁会阻塞其他的读操作和写操作，只有这样，才能保证在给定时间内只有一个用户能完成写操作。
        在实际数据库系统中，每时每刻都在发生锁定的情况，目的就是防止其他客户端读取到不一致的数据，为了提高加锁的效率，让锁定对象更有选择性，可以控制锁粒度，理想情况是只会修改的数据片进行锁定，保证系统的并发程度。此外，无论是获得锁还是检查锁、释放锁都会增加系统的开销，如果系统花费太多时间在锁的各种操作上，而不是操作数据，系统的性能都会降低不少。
        MYSQL中的存储引擎都可以实现自己的锁策略和锁粒度，表锁和行锁是重要的两种锁策略。表锁是MYSQL中最基本的锁策略，即会整张表进行锁定，其开销最小，当一个用户对表进行写操作（插入、更新、删除）时，需要获得整张表的写锁，并阻塞其他客户端对该表的所有操作，而读锁和读锁不会被阻塞，是可以并发执行的；行锁的并发程度比表锁高，但同时带来了更大的锁开销，行锁只在存储引擎层实现，在MYSQL服务器层中只实现了表锁而没有实现行锁。

3 事务

事务是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能成功对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。一个良好的事务处理系统必须满足ACID（atomicity consistency isolation durability）的标准。实现了ACID的数据库在安全性上表现更佳，但同时会增加系统开销，具体来说，实现了ACID的数据库，相比没有实现ACID的数据库，需要更大的内存和磁盘空间，对CPU的处理能力要求更高。

原子性（atomicity）
整个事务不可能只执行一部分，要么全部提交成功要么全部失败。

一致性（consistency）
数据库总是从一个一致性的状态转换到另一个一致性的状态。比如在转账过程中，不会因为转账事务没有提交而事务中的修改操作丢失，也就是说转账事务失败，账户的钱不会减少，和事务失败前是一致的。

隔离性（isolation）
一个事务所做的修改在提交之前，对其他事务不可见。

持久性（durability）
一旦事务提交，所做的修改就会永久保存到数据库中，即使系统崩溃，恢复后数据也不会丢失。在后面文章中会对ACID实现的原理进行剖析，这里不再详述。

3.1 隔离级别

MYSQL拥有四种隔离级别，每一种级别都规定了一个事务的修改，哪些在事务内和事务间是可见的，哪些不可见。低隔离级别的并发量更好，开销更低，但安全性上得到的保障也会更低，下面将介绍四种隔离级别：

3.1.1 READ UNCOMITTED (未提交读)

在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED 不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。

3.1.2 READ COMMITTED (提交读)

大多数数据库系统的默认隔离级别都是READ COMMITTED(但MySQL不是)。READ COMMITTED满足前面提到的隔离性的简单定义: -一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读。read)，因为两次执行同样的查询，可能会得到不一样的结果。

3.1.3 REPEATABLE READ (可重复读)

REPEATABLE READ 解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)解决了幻读的问题。可重复读是MySQL的默认事务隔离级别。

3.1.4 SERIALIZABLE (可串行化)

SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的-致性而且可以接受没有并发的情况下，才考虑采用该级别。
为了对四个隔离级别更深的理解，下面将结合案例进行讲解：
建表语句：CREATE CREATE TABLE T_S_TEST( id int not null PRIMARY KEY,name VARCHAR(10)not null )
插入三条测试数据：

INSERT INTO T_S_TEST(ID,NAME)VALUES(1,'张三'),(2,'李四'),(3,'王五')

测试未提交读：SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

事务A：

START TRANSACTION;
UPDATE T_S_TEST SET NAME ='赵四' WHERE id = 1;

rollback;

事务B：

START TRANSACTION;
SELECT * FROM T_S_TEST;// 只要事务A修改了，事务B就能读到更新的数据

实验描述与分析

事务A修改成功后即使不提交，事务B也能读取到修改的数据，而这个数据最后会被回滚。也就是说数据库中最终保存的数据是“张三”，但事务B却读到了“赵四”的脏数据。

测试已提交读：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

事务A：

START TRANSACTION;
UPDATE T_S_TEST SET NAME ='赵四' WHERE id = 1;

commit;

事务B：

START TRANSACTION;
SELECT * FROM T_S_TEST;// 事务A没有提交，事务B读到的还是老数据


SELECT * FROM T_S_TEST;// 事务A提交了，事务B能读到更新的数据，但是在一个事务里面就会出现读取不一致的情况

实验描述与分析

事务A修改成功后如果不提交，事务B就不能读取到修改的数据。因此不会产生脏数据，但是一旦事务A提交，事务B就能看到提交的数据，对于事务B来说，两次SELECT看到的数据就会不一致，第一个 SELECT 读取的数据中ID=1对应的name是“张三”，第二个 SELECT 读取的数据中ID=1对应的name是“赵四”，也就是不可重复读的现象。

测试可重复读：SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

事务A：

START TRANSACTION;
UPDATE T_S_TEST SET NAME ='赵四' WHERE id = 1;
commit;//测试可重复读

START TRANSACTION;
INSERT INTO T_S_TEST(id,name)VALUES(4,'孙六')
commit;//说明幻读现象

事务B：

START TRANSACTION;
SELECT * FROM T_S_TEST;// 事务A没有提交，事务B读到的还是老数据


SELECT * FROM T_S_TEST;// 事务A提交了，但是依然只能读取到老数据

UPDATE T_S_TEST SET NAME = CONCAT(NAME,"_") WHERE id >2

SELECT * FROM T_S_TEST;// 会多出一条"ID=4,NAME=孙六_"的记录，就好像是幻觉一样

实验描述与分析

事务A修改成功后如果不提交，事务B就不能读取到修改的数据。因此不会产生脏数据，即使事务A提交，事务B也只能看到老数据，两次SELECT 得到的数据是一致的，是可重复读的，但是一旦事务A更新了某条数据并提交，对于事务B来说是不可见的，因此事务B自己做更新操作的时候就会默默地把这条不可见记录也更新，再去查询时就会发现凭空多出一行数据，这就是幻读现象，用一句话通俗的解释幻读现象产生的原因：插入了事务不可见的数据行。

测试可串行化：SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

事务A：

START TRANSACTION;
INSERT INTO T_S_TEST(id,name)VALUES(4,'孙六')
commit;//测试幻读现象是否还存在

事务B：

START TRANSACTION;
UPDATE T_S_TEST SET NAME = CONCAT(NAME,"_") WHERE id >2;// 事务A没有执行完毕，事务B就会被阻塞住，就相当于事务串行执行。
SELECT * FROM T_S_TEST;//

实验描述与分析

SERIALIZABLE 会在读取的每一行数据上都加锁，因此不会出现幻读，但是没有并发，实际运用中不会使用该隔离级别。

4 多版本控制(MVCC)

       为了从原理上理解隔离级别，可以借助多版本控制MVCC去理解。我们可以将MVCC看做是一种行级锁，相同点是都实现了非阻塞的读操作，写操作也只是锁定必要的行，不同点是，在很多情况下，MVCC可以避免加锁操作，因此性能上损耗更小。
       MVCC是利用数据快照来实现的，具体来说，不管事务需要执行多长时间，同一个事务看到的数据是一致的，若事务开始执行的时间不同，即使是同一张表，同一时刻下不同事务看到的数据可能是不一样的，也就是说这种数据快照是某个时间点的快照。
       不同的存储引擎有着不同的MVCC实现机制，以InnoDB为例来探讨MVCC运行的原理。InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间( 或删除时间)。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ 隔离级别下，MVCC具体是如何操作的。

SELECT：
InnoDB会根据以下两个条件检查每行记录:
	(a)InnoDB只查找版本早于当前事务版本的数据行(也就是，行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的
	(b)行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除
只有符合上述两个条件的记录，才能返回作为查询结果。

INSERT：
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE：
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE：
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作.
MVCC只在REPEATABLE READ 和READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED 总是读取最新的数据行,而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

5 MYSQL中的存储引擎

5.1 InnoDB引擎

       InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ (可重复读) ,并且通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
       InnoDB表是基于聚簇索引建立的。InnoDB的索引结构和MySQL的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。不过它的二级索引(secondary index, 非主键索引)中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。
       InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index)，以及能够加速插入操作的插入缓冲区(insertbuffer)等。
       作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份，Oracle 提供的MySQL Enterprise Backup、Percona 提供的开源的XtraBackup都可以做到这一点。MySQL的其他存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

5.2 MyISAM引擎

        MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。MyISAM有一些服务器级别的性能扩展限制，比如对索引键缓冲区(key cache)的Mutex锁，MariaDB 基于段(segment) 的索引键缓冲区机制来避免该问题。但MyISAM最典型的性能问题还是表锁的问题，如果你发现所有的查询都长期处于“Locked" 状态,那么毫无疑问表锁就是罪魁祸首。
MyISAM引擎不支持事务和行锁，而且在崩溃后无法安全恢复。下面将对MyISAM引擎的特点进行归纳：
       （a）MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录(这被称为并发插入，CONCURRENT INSERT)。
         (b）对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，但这里说的修复和事务恢复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable 检查表的错误，如果有错误可以通过执行REPAIR TABLE mytable 进行修复。另外，如果MySQL服务器已经关闭，也可以通过myisamchk命令行工具进行检查和修复操作。

5.3 InnoDB引擎和MyISAM引擎简单对比

InnoDB引擎：支持ACID的事务，支持事务的四种隔离级别；支持行级锁及外键约束：不存储总行数。
MyISAM引擎：不支持事务，只支持表锁，存储表的总行数。

从索引的角度看，InnoDB的主键索引采用聚集索引（数据域存储了数据本身），二级索引的数据域存储了主键的值，因此通过二级索引需要扫描两次；MYISAM引擎均采用非聚集索引，数据域存储指向数据文件的指针。
从数据存储的角度看，一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；一个InnoDB表的索引文件和数据文件存储在一起。

5.4 选择合适的存储引擎

       大部分情况下，InnoDB 都是正确的选择，所以Oracle在MySQL 5.5版本时就将InnoDB作为默认的存储引擎。对于如何选择存储引擎,可以简单地归纳为一句话:“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎”。例如，如果要用到全文索引，建议优先考虑InnoDB加上Sphinx的组合，而不是使用支持全文索引的MyISAM。当然，如果不需要用到InnoDB的特性，同时其他引擎的特性能够更好地满足需求，也可以考虑一下其他存储引擎。举个例子，如果不在乎可扩展能力和并发能力，也不在乎崩溃后的数据丢失问题，却对InnoDB的空间占用过多比较敏感，这种场合下选择MyISAM就比较合适。
        在选择合适的存储引擎前，需要考虑如下几个因素：是否需要事务支持；是否有备份需求（是否每次备份都可以关闭服务器，若不能关闭需要在线热备份，就只能选择InnoDB）；是否需要快速崩溃恢复（InnoDB恢复速度更快）；是否有特定需求（MYSQL中只有MyISAM支持地理空间搜索）。
        从应用的角度分析，日志型应用、只读表或者大部分情况只读的表在不介意崩溃恢复的情况下可以使用MyISAM引擎（MyISAM引擎将数据写到内存中，然后等待操作系统定期将数据刷到磁盘上），像订单等典型需要事务支持的应用，就需要使用InnoDB 引擎。