高性能MySQL —— 1、MySQL架构和历史-CSDN博客

本文链接：https://blog.csdn.net/ForNever_/article/details/115206117

MySQL学习笔记 1、MySQL架构和历史

1、MySQL逻辑架构
- 1.1 连接管理与安全性
- 1.2 优化与执行
2、并发控制
3、事务
4、多版本控制并发
5、MySQL的储存引擎

1、MySQL逻辑架构

MySQL最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理与其他系统任务和数据的存储/提取相分离。这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。
MySQL逻辑架构
第一层客户端，按我的理解是如Mybatis、Hibernate之类数据库连接工具。
第二层包含大多数MySQL核心服务，如查询解析、分析、优化、缓存，以及所有的内置函数，跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层存储引擎，负责数据的储存和提取，服务器通过API与存储引擎通信。API包含几十个底层函数，如“开始一个事务”、“根据主键提取一行数据”等。存储引擎不会解析SQL（除InnoDB解析外键定义），不会互相通信。锁的实现也是存储引擎实现的。

1.1 连接管理与安全性

每个新建的连接都会在MySQL服务器中有且只有一个线程，MySQL服务器会缓存线程（MySQL 5.5之后提供了支持线程池插件）。
故一个MySQL服务器线程可能先后对应过多个连接，但一个连接只会对应一个线程。

1.2 优化与执行

MySQL会解析查询，创建内部数据结构（解析数），然后解析优化，如：重写查询、决定表读取顺序，选择索引等。
用户可以通过特殊关键字提示优化器（hint功能），影响它的决策过程。
也可以请求优化器解释优化过程（explain），了解语句的优化决策，便于重构查询、重构schema（数据库的组织和结构）、修改配置等。

优化器不关心表的存储引擎，但会请求存储引擎提供容量和某个具体操作的开销信息，以及表数据的统计信息等。如某些存储引擎特定优化了某种所以的查询。

对于select语句，解析查询前，MySQL会先检查查询缓存（Query Cache），如果对应的查询语句与结果，直接返回查询结果集，不进行查询解析、优化和执行。

2、并发控制

2.1 读写锁

在处理并发读或者并发写时，可以通过实现共享锁和排他锁（也叫读锁和写锁）共同组成的一个锁。
读锁是共享的，多个读锁互不阻塞，但会阻塞排他锁。即一条数据可以同时添加多个读锁。
写锁是排他的，会阻塞其他的读锁和写锁，即一条数据拥有了一个排他锁，就不能再拥有其他锁。仅拥有锁的线程能够修改数据。

一条数据或表不能同时拥有读锁和写锁。

2.1.1 InnoDB与MyISAM读锁的区别

MySQL InnoDB中，普通的select语句并没有添加锁，所以不论何时都能查询到数据；update,delete,insert 都会自动给涉及到的数据加上排他锁。同时，InnoDB拥有读锁的线程可以修改锁住的数据，但若数据上有多把锁，那么这些锁会互相阻塞，都不能修改数据。

MySQL MyISAM中，select会添加表读锁，增删改会添加表写锁。持有读锁的线程只能读取锁住的表，不能修改表中数据以及访问其他表，但不影响其他线程对此表添加读锁以及数据访问。

2.1.2 InnoDB与MyISAM手动添加锁

InnoDB 添加读锁
select [columns] from [table] [where [wheres]] lock in share mode;
InnoDB 添加写锁
select [columns] from [table] [where [wheres]] for update [nowait];
不添加nowait，则若当前须加锁数据有锁，则等待解锁后第一时间加锁；
添加nowait当前数据有锁则返回错误。

MyISAM添加锁
lock table [tablename] [read/write];
unlock table [tablename] 解锁
read即读锁，write为写锁

MySQL锁
LOCK TABLES；
UNLOCK TABLES；
MySQL实现，与存储引擎无关

2.2 锁粒度

为了提高并发性，当然是尽量只锁定需要修改的部分数据，更理想的是对会修改的数据片进行精确的锁定。
但锁的各种操作，如获得锁、检查锁、释放锁等都会增加系统开销。
所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然会影响到性能
每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

2.2.1 表锁

表锁是MySQL最基本的锁策略，并且是开销最小的策略，它会锁定整张表。
写锁比读锁有更高的优先性，因此一个写锁请求可能会被插入到读锁队列前面，读锁不能插入到写锁前面。

2.2.2 行级锁

行级锁能最大程度的支持并发处理，同时也带来了最大的锁开销，它会锁定数据行。InnoDB和XtraDB实现了行级锁。
行级锁只在存储引擎层实现，服务器层完全不了解存储引擎中的锁实现。

3、事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果有任何一条语句无法执行，那么所有语句都不会执行。

一个运行良好的事务处理系统，必须具备ACID特征，即原子性、一致性、隔离性、持久性

原子性
一个事务必须被视为一个不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚。
一致性
数据库总是从一个一致性状态转换到另一个一致性状态。事务未提交时，事务中的修改不会保存到数据库中。
隔离性
一个事务的修改在最终提交前，对其他事务不可见。
持久性
一旦事务提交，则其所作的修改就会永久保存在数据库中。实际上持久性也分很多不同级别，有些持久性策略能提供非常强的安全保障，有些则未必。而且不可能做到100%的持久性保证的策略。

事务虽然提供了更加具有安全性的数据操作，但也需要数据库系统做更多的额外工作。实现了ACID的数据库通常需要更强的CPU处理能力、更大的内存和更多的磁盘空间。
用户可以根据业务是否需要事务，选择合适的存储引擎。对于不需要事务的查询类表，可以选择非事务型或事务粒度更大的存储引擎（如MyISAM，事务粒度为表级锁），获取更高的性能。
即使存储引擎不支持事务，也可以通过LOCK TABLES语句进行锁表。

3.1 事务的隔离级别

SQL标准中定义了四种隔离级别，较低级别的隔离通常可以执行更高的并发，系统开销也更低。

3.1.1 数据读取常见问题

在了解事务的隔离级别前，需要先了解几个名词：
不可重复读：当前事务能看到其他事务已提交的所有修改，导致连续两次读取数据可能出现数据不一致的情况。

可重复读：当前事务可以看到其他事务新增、删除的行数据，但不能看到事务执行中，其他事务提交的数据内容修改。
对于不可见事务执行中其他事务的修改，按我的理解，即当前事务读取数据时，对于原有数据的读取类似于读取事务开启时的数据快照。具体原理之后会有讲解，如有误届时回来修改。

幻读：当前事务执行中，对于某个范围内的数据总条目数为N，如果其他事务进行数据行的新增或删除并提交，虽然此时实际数据库中该范围内数据条目数为M（M != N），但当前事务再次查询该范围内的数据总条目数仍未为N条原有数据。
我的猜想可能“可重复读”使用的原始快照导致的。

脏读：当前事务可以读取到其他事务未提交的数据修改。

第一类丢失更新：当前事务执行失败，数据回滚时将其他事务已经提交的数据修改进行了回滚覆盖。标准定义的所有隔离级别都不允许第一类丢失更新发生。

第二类丢失更新：当前事务执行中，其他事务提交了对于某数据的修改，之后当前事务同样修改了该数据并提交，导致其他事务对于该数据的修改最终仿佛没有发生。

3.1.2 隔离级别

之后，我们再来讨论四种隔离级别：
事务隔离级别

3.1.2.1 Read Uncommitted（未提交读）

在RU级别中，事务中的修改即使没有提交，对其他事务也都是可见的，可能会导致脏读。从性能上来说，并没有比其他级别好太多，会导致的问题也很多，除非真有必要，在实际应用中一般很少使用。会发生第二丢失更新。

3.1.2.2 Read Committed（提交读）

在RC级别，事务能够随时看见其他事务已提交的修改，事务执行中的修改是不可见得，可能导致不可重复读。大多数数据库默认隔离级别，非MySQL默认。会发生第二丢失更新。

3.1.2.3 Repeatable Read（可重复读）

MySQL默认事务隔离级别，保证了在同一个事务中，对于数据的多次读取结果都是一致的，但可能引发幻读。不会发生第二类丢失更新。
对于不会引发第二类丢失更新这点，我经过查阅网上资料与自己试验，发现并不能够完全防止第二类丢失更新，但配合乐观锁能够实现防止第二类丢失更新，对于这方面的具体情况可能要查阅SQL标准才能有所定论，这里先写下我自己的尝试。

/**初始化表结构*/
CREATE TABLE `tab` (
  `id` int NOT NULL COMMENT '键',
  `val` int NOT NULL COMMENT '值',
  PRIMARY KEY (`id`)
)
insert into tab values (1, 1);

第二类丢失更新发生情况：

/**SQL语句行后数字为SQL语句执行顺序*/
/**查询窗口1*/
START TRANSACTION;// 1
select id, val from tab where id = 1;// 3、6
update tab set val = 2 where id = 1;// 5
commit;// 8
select id, val from tab where id = 1;// 9、13

/**查询窗口2*/
START TRANSACTION;// 2
select id, val from tab where id = 1;// 4、7、10、12
update tab set val = 3 where id = 1;// 11
commit;// 14
select id, val from tab where id = 1;// 15

我们可以看到
①当两个窗口的事务开启后，id为1的条目val都是1；
②当窗口1修改了val后，窗口1可以发现未提交的数据修改，但窗口2仍为原数据；
③当窗口1提交了val修改后，数据库一致性发生变化，但窗口2查询仍为开启事务时val；
④当窗口2修改了val，由于事务未提交，事务外部查询数据没有发生变化，仍为窗口1修改后的值；窗口2中val变为了窗口2修改的val值；
⑤窗口2事务提交后，查询数据库当前id为1的val值，发现数值变为窗口2修改的val值。
此时，第二类丢失更新发生。

但是，当我们将窗口2的update语句进行修改为

update tab set val = 3 where id = 1 and val = 1;

虽然在窗口2中查询id为1的val是1，但执行update返回的受影响行数却为0，且数据没有发生更新。

由此，为了防止第二类丢失更新的发生，如果非要采用RR级别，我们可以对这种事务频繁、事务执行长，数据可靠性要求高的表添加乐观锁，来保证不会丢失每一次对数据行的修改，如使用旧值条件或添加版本字段。
或者使用悲观锁，即读锁、写锁来提前锁住数据。

3.1.2.4 Serializable（可串行化、序列化）

是最高的隔离级别，强制事务串行执行，即一个事务处理完成后才能处理下一个事物，也就是事务不再是并发的了。serializable会在读取的每一行数据上都加锁，所以可能导致大量的锁超时和锁争用的问题。只有在非常需要确保数据的一致性且能接受没有并发的情况下，才考虑使用。

3.2 死锁

死锁是指两个或者多个事务在同一资源上的互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，如InnoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。
还有一种方式，当查询时间达到锁等待超时时间则放弃锁请求。
InnoDB目前是将持有最少行级排他锁的事务进行回滚。
锁的行为和顺序适合存储引擎相关的。同样的SQL在不同的存储引擎发生的情况是不一定的。所以死锁的产生有些是真正的数据冲突，有些则是存储引擎的实现方式导致的。

3.3 事务日志

事务日志可以帮助提高事务的效率。未使用事务日志，存储引擎在修改内存中表的数据后，每次都需要将数据的修改持久到硬盘上；使用事务日志后，存储引擎修改内存中表的数据后，只需要把修改行为记录到持久在硬盘上的事务日志中，对于数据的修改就能够慢慢的刷回硬盘，就算系统崩溃，存储引擎也能在重启后自动回复这部分修改的数据。我们通常称之为预写式日志，修改数据需要写两次磁盘。
事务日志的记录是采用追加的方式，因此写日志仅需要在硬盘上一小块区域顺序I/O，而不像随机I/O需要在硬盘多次移动磁头，数据修改频繁的时候能够更快的响应请求且保证了数据的持久化。

3.4 MySQL中的事务

MySQL提供了两种事务性存储引擎：InnoDB和NDB Cluster

3.4.1 自动提交

MySQL默认采用自动提交模式。如果不是显式开启一个事务，则每个查询都被当做一个事务执行提交操作。
可以通过设置AUTOCOMMIT开启或关闭
SHOW VARIABLES LIKE ‘AUTOCOMMIT’;
SET AUTOCOMMIT = 1;
1或ON为开启，0或OFF为关闭。当自动提交关闭，所有查询都是在一个事务中，直到显式执行COMMIT或ROLLBACK，结束当前事务，同时又开始一个新的事务。不会影响如MyISAM、MEMORY之类非事务表。
另外有些命令在执行前会强制执行COMMIT，如导致大量数据改变的操作ALTER TABLE等，还有LOCK TABLES等。

set session transaction isolation level READ COMMITTED；
手动设置隔离级别，在下一个事务开始时生效。

3.4.2 在事务中混合使用存储引擎

事务是由下层存储引擎实现的，所以在一个事务中，使用多种存储引擎是不可靠的。
如果事务回滚，事务型表数据会正常回滚，而非事务型表则不会，导致数据不一致。

3.4.3 隐式和显式锁定

隐式锁定：InnoDB会根据隔离级别在需要的时候自动加锁。InnoDB采用的是两阶段锁定协议。事务执行过程中，随时可以执行锁定，只有在COMMIT或者rollback时才会释放，并且所有的锁是在同一时刻被释放。

显示锁定：特定语句锁定
如InnoDB显示锁定，但并不符合SQL规范
select … lock in share mode;
select … for update;

MySQL服务器层实现的表锁定LOCK TABLES和UNLOCK TABLES。

建议除非事务中禁用了AUTOCOMMIT，否则任何时候都不要显示执行LOCK TABLES。

4、多版本控制并发

MySQL大多数事务型存储引擎实现的都不是简单的行级锁。一般都同时实现了多版本并发控制（MVCC）。
可以认为MVVC是行级锁的一个变种。很多情况下避免了加锁操作，因此开销更低。
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。
InnoDB的MVCC，是通过每行记录后保存两个隐藏列实现的。一个保存了行的创建版本号，一个保存行的过期版本号。
select
①InnoDB只查找版本早于当前版本号的数据行。
②行的删除版本要么未定义，要么大于当前事务版本号。
insert
InnoDB为新插入的每一行保存当前系统版本号作为新版本号
delete
InnoDB为删除的每一行保存当前系统版本号作为删除标识
update
InnoDB为插入一行新记录，保存当前系统版本号为行版本号，同时保存当前系统版本号作为原来行删除标识。

MVCC只在Repeatable Read和Read Committed两个隔离级别下工作。

5、MySQL的储存引擎

文件系统中，MySQL将每个数据库保存为数据目录下的一个子目录。创建表时，会在子目录下创建一个同名.frm文件保存表定义。

5.1 InnoDB存储引擎

InnoDB是MySQL默认存储引擎，也是最重要、使用最广泛的存储引擎。被设计用来处理大量的短期事务。
InnoDB的数据存储在表空间，表空间是由InnoDB管理的一个黑盒子，由一系列数据文件组成。可以将每个表的数据和索引存放在单独的文件中。
InnoDB采用MVCC支持高并发，实现了四个标准的隔离级别。默认级别是Repeatable Read，通过间隙锁策略防止幻读的出现。
InnoDB表是基于聚簇索引建立的。与其他存储引擎索引结构很很大不同，对主键查询有很高的性能。但由此若表上索引较多的话，主键应当尽量的小。
InnoDB内部做了很多优化，如可预测性预读，能自动在内存创建hash索引以加速读操作的自适应哈希索引，以及能加速插入操作的插入缓冲区等。
建议阅读“InnoDB事务模型和锁”一节。
InnoDB通过一些机制和工具支持真正的热备份。不支持热备份的存储引擎，要获取一致性视图需要停止对所有表的写入。

5.2 MyISAM存储引擎

MySQL 5.1及之前，MyISAM是默认存储引擎。MyISAM提供了大量如全文索引、压缩、空间函数等，但不支持事务和行级锁且崩溃后无法安全恢复。对于只读的数据，或表比较小、可以忍受修复操作，依然可以使用MyISAM。

MyISAM将表储存在数据文件和索引文件只用，分别为.MYD和.MYI文件。可以包含动态和静态行。可以存储的行记录数，受限于硬盘空间或操作系统最大文件尺寸。
可以通过修改MAX_ROWS和AVG_ROW_LENGTH修改最大表容量，二者相乘即最大大小，但会导致重建表和索引，耗时久。

MyISAM读取时对读到的所有表加共享锁，写入的加排他锁。但在表有读取操作的同时，也能往表中插入新的数据，即并发插入。
MySQL可以手工或自动检查和修复MyISAM表，可能会导致数据丢失而且慢。check table [table]检查表错误，repair table [table]进行修复。MySQL服务关闭可通过myisamchk命令行工具进行检查和修复。
MyISAM即使是BLOB和TEXT等长字段，也能基于前500字符创建索引。支持全文索引，是一种基于分词创建的索引，能支持复杂的查询。
如果指定了delay_key_write，会先将修改写入键缓冲区，清理缓冲区或关闭表的时候才会写入硬盘。

如果表创建并导入数据后，不会进行修改操作，或许适合使用MyISAM压缩表。压缩后表不能进行修改，修改需解压缩再修改然后重压缩。能极大减少磁盘占用空间，减少硬盘I/O提升查询性能。压缩后支持索引，但索引也是只读。

5.3 MySQL内建的其他存储引擎

5.3.1 Archive

只支持insert和select，MySQL 5.1前不支持索引。
会缓存所有的写并用zlib对插入行压缩，I/O更少。但select会全表扫描。适合日志和数据采集类应用。
支持行级锁和专用缓冲区，能高并发插入。一个查询返回前，会阻止其他select执行。但不是事务型引擎，是一个对高速插入和压缩做了优化的简单引擎。

5.3.2 Blackhole

没有存储机制，不保存插入行。但服务器会记录表日志，可用于复制数据到备库，或只是记录日志。

5.3.3 CSV

可将CSV文件作为表处理，不支持索引。作为数据交换机制。

5.3.4 Federated

默认禁用，是访问其他MySQL服务器的代理，能远程访问其他MySQL服务器客户端。

select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题，但是按主键或索引列查询，则很快，如以下查询就非常慢；
如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常。
类似 where name like “str%” limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为federated引擎会将所有满足条件的记录读取到本，再进行 limit 处理。

5.3.5 Memory

基于内存，速度极快，虽然表结构能保存，但数据不会。

用于查找或映射表；
用于缓存周期性聚合数据的结果；
用于保存分析中产生的中间数据。
支持Hash索引，查询快。表级锁，并发写入慢。不支持BLOB和TEXT，行长度固定，varchar以char存储，导致内存浪费。
查询过程中需要使用临时表保存中间结果，内部使用的就是memory表，数据过大会转为MyISAM表。
非临时表，临时表是一种声明与存储引擎无关。