目录
一.MySQL的逻辑架构
- 最上层的客户端所包含的服务并不是MySQL独有的，大多数基于网络的客户端/服务器工具或服务器都有类似的服务，包括连接处理、身份验证、确保安全性等。
- 第二层是比较有意思的部分。大多数MySQL的核心功能都在这一层，包括查询解析、分析、优化、以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能也都在这一层实现：存储过程、触发器、视图等。
- 第三层是存储引擎层。存储引擎负责MySQL中数据的存储和提取。和GNU/Linux下的各种文件系统一样，每种存储引擎都有其优势和劣势。服务器通过存储引擎API进行通信。这些API屏蔽了不同存储引擎之间的差异，使得它们对上面的查询层基本上是透明的。存储引擎层还包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL(InnoDB是一个例外，它会解析外键定义，因为MySQL服务端没有实现该功能)，不同存储引擎之间也不会相互通信，而只是简单地响应服务器的请求。
连接管理与安全性
默认情况下，每个客户端连接都会在服务器进程中拥有一个线程，该连接的查询只会在这个单独的线程中执行，该线程驻留在一个内核或者CPU上。服务器维护了一个缓存区，用于存放已就绪的线程，因此不需要为每个新的连接创建或者销毁线程。
优化与执行
MySQL解析查询以创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊关键字向优化器传递提示，从而影响优化器的决策过程。也可以请求服务器解释优化过程的各个方面，使用户可以知道服务器是如何进行优化决策的，并提供一个参考点，便于用户重构查询和schema、修改相关配置，使应用尽可能高效地运行。
优化器并不关心表使用的是什么存储引擎，但存储引擎对于查询优化是有影响的。优化器会向存储引擎询问它的一些功能、某个具体操作的成本，以及表数据的统计信息。例如，一些存储引擎支持对某些查询有帮助的特定索引类型。
在旧版本中，MySQL可以使用内部查询缓存（query cache）来查看是否可以直接提供结果。但是，随着并发性的增加，查询缓存成为一个让人诟病的瓶颈。原因如下：
- 查询缓存只能缓存精确相等的查询语句，而不能缓存包含变量或参数的查询语句。
- 查询缓存需要占用一定的内存空间，当缓存的查询语句过多时，会导致内存占用过高，从而影响数据库的性能。
- 查询缓存只能在单个服务器上使用，不能在分布式数据库中使用。
从MySQL 5.7.20版本开始，查询缓存已经被官方标注为被弃用的特性，并在8.0版本中被完全移除。现在一个流行的设计模式是在memcached或Redis中缓存数据。
二.并发控制
无论何时，只要有多个查询需要同时修改数据，就会产生并发控制问题。 MySQL提供两个级别的并发控制：服务器级别与存储引擎级别。这里只简要地介绍MySQL如何控制并发读写。
我们用一个传统的电子表格文件作为示例，来说明MySQL如何处理同一组数据上的并发工作。电子表格由行和列组成，很像数据库中的表。假设文件在只有你可以访问的笔记本电脑上，没有潜在的冲突，因为只有你可以对该文件进行更改。现在，想象一下你需要与一位同事合作制作电子表格，文件存放在一个你们都可以访问的共享服务器上。当你们需要同时对该文件进行更改时，会发生什么情况？如果还有一整个团队的人积极地尝试编辑、添加和删除这个电子表格中的单元格内容，那会怎么样呢？我们可以说他们应该轮流修改，但这样效率是极低的。我们需要一种允许并发访问大容量电子表格的方法。
读写锁
如果把上述电子表格看作数据库中的表，很容易发现也会有同样的问题。从很多方面来说，电子表格就是一张简单的数据库表。修改数据库表中的记录，和删除或者修改电子表格文件中的单元格内容十分类似。
并发控制这一经典问题的解决方案相当简单。处理并发读/写访问的系统通常实现一个由两种锁类型组成的锁系统。这两种锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。
先不考虑具体的锁定机制，锁的概念可以如下描述：资源上的读锁是共享的，或者说是相互不阻塞的。多个客户端可以同时读取同一个资源而互不干扰。写锁则是排他的，也就是说，一个写锁既会阻塞读锁也会阻塞其他的写锁，这是出于安全策略的考虑，只有这样才能确保在特定的时间点只有一个客户端能执行写入，并防止其他客户端读取正在写入的资源。
在实际的数据库系统中，每时每刻都在发生锁定：当某个客户端在修改某一部分数据时，MySQL会通过锁定防止其他客户端读取同一数据。如果数据库服务器以可接受的方式执行，锁的管理速度足够快，那么不会引起客户端的感知。
锁的粒度
一种提高共享资源并发性的方式就是 让锁定对象更有选择性。 尽量只锁定包含需要修改的部分数据，而不是所有的资源。更理想的方式是，只对需要修改的数据片段进行精确的锁定。任何时候，让锁定的数据量最小化，理论上就能保证在给定资源上同时进行更改操作，只要被修改的数据彼此不冲突即可。
加锁也需要消耗资源。锁的各种操作，包括获取锁、检查锁是否空闲、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是存取数据，那么系统的性能可能会受影响。
锁定策略是锁开销和数据安全性之间的平衡，这种平衡会影响性能。锁是数据库实现一致性保证的方法。
而MySQL则提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。
全局锁
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语 句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整 性。
加全局锁
flush tables with read lock ;数据备份
mysqldump -uroot –p密码 要备份的数据库名 > xxx.sqlmysqldump非SQL语言，需要在操作系统窗口执行
释放锁
unlock tables ;表级锁
表锁
表锁（table lock）是MySQL中最基本也是开销最小的锁策略。 表级锁是针对整张表的锁，可以是共享锁或排它锁，它会影响整张表的并发读写操作。
- 当客户端A给某个表加了读锁，那么客户端A和其他客户端可以读取该表数据，但都不能写。读锁不会阻塞其他客户端的读。
- 当客户端A给某个表加了写锁，那么客户端A既能读取该表数据，也能进行写操作。其他客户端既不能读也不能写。写锁既会阻塞其他客户端的读，又会阻塞 其他客户端的写。
表锁有一些变体，可以在特定情况下提高性能。例如，READ LOCAL表锁支持某些类型的并发写操作。写锁队列和读锁队列是分开的，但写锁队列的优先级绝对高于读队列。
加锁
lock tables 表名... read/write释放锁
unlock tables / 客户端断开连接元数据锁
元数据锁 (meta data lock， MDL)是一种表级锁，它用于保护MySQL中的元数据， 如数据库、表、视图等对象的结构和状态信息。当对这些元数据对象进行修改或查询时，MySQL会自动添加元数据锁来保证数据的一致性和正确性。
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。也就是说，某一张表涉及到未提交的事务 时，是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变 更操作的时候，加MDL写锁(排他)。
查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from
performance_schema.metadata_locks ;意向锁
在给一张表加表锁之前会检查该表中是否存在相互冲突的行锁，所以在InnoDB中引入了意向锁，如果表中存在行锁那么一定存在对应的意向锁，通过检查该意向锁与表锁是否排斥来判断该行锁是否与表锁冲突。这使得表锁不用检查每行数据是否加锁， 使用意向锁来减少表锁的检查。
有了意向锁之后 :
客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。
而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。
意向共享锁(IS):
由语句select ... lock in share mode添加 。 与表锁共享锁 (read)兼容，与表锁排他锁(write)互斥。
意向排他锁(IX):
由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。
查看意向锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;行级锁
使用行级锁（row lock）可以最大程度地支持并发处理（也带来了最大的锁开销，因为需要跟踪谁拥有这些行级锁、已经锁定了多长时间、行级锁的类型，以及何时该清理不再需要的行级锁）。 行级锁是针对表中行的锁，可以是共享锁或排它锁，它只会影响到当前行的并发读写操作。
行级锁是在存储引擎而不是服务器中实现的。服务器通常不清楚存储引擎中锁的实现方式。每种存储引擎都以自己的方式来实现锁。
行锁（Record Lock）
锁定单个行记录的锁，防止其他事务对此行进行update和delete。在REPEATABLE READ、REPEATABLE READ隔离级别下都支持
间隙锁（Gap Lock）
锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事 务在这个间隙进行insert，产生幻读。在REPEATABLE READ隔离级别下都支持。
临键锁（Next-Key Lock）
行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。 在REPEATABLE READ隔离级别下支持。
三.事务
事务就是一组SQL语句，作为一个工作单元以原子方式进行处理。如果数据库引擎能够成功地对数据库应用整组语句，那么就执行该组语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么整组语句都不执行。 也就是说，作为事务的一组语句，要么全部执行成功，要么全部执行失败。
假设一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么需要至少三个步骤：
1. 确保支票账户的余额高于200美元。
2. 从支票账户的余额中减去200美元。
3. 在储蓄账户的余额中增加200美元。
以上三步操作必须打包在一个事务中，以保证一旦其中任何一步失败，都能够回滚所有的操作。
可以用START TRANSACTION语句启动事务，然后要么使用COMMIT提交事务将修改的数据持久保留，要么使用ROLLBACK撤销所有的修改。本例中的事务的SQL语句可能如下：
在这一系列操作中，有更多的失败可能性。连接可能会断开、会超时，甚至数据库服务器在操作执行过程中会崩溃。这就是为什么存在高度复杂且缓慢的两阶段提交系统的典型原因：为了应对各种失败场景。
ACID
ACID是支持事务的四个关键属性。
ACID代表原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。一个确保数据安全的事务处理系统，必须满足这些密切相关的标准。
原子性（atomicity）
一个事务必须被视为一个不可分割的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
一致性（consistency）
数据库总是从一个一致性状态转换到下一个一致性状态。在前面的例子中，一致性确保了，即使在执行第3、4条语句之间时系统崩溃，支票账户中也不会损失200美元。如果事务最终没有提交，该事务所做的任何修改都不会被保存到数据库中。
隔离性（isolation）
常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的，这就是隔离性带来的结果。在前面的例子中，当执行完第3条语句、第4条语句还未开始时，此时有另外一个账户汇总程序开始运行，其看到的支票账户的余额并没有被减去200美元。后面我们讨论隔离级别（isolation level）的时候，会发现为什么我们要说“通常来说”是不可见的。
持久性（durability）
一旦提交，事务所做的修改就会被永久保存到数据库中。此时即使系统崩溃，数据也不会丢失。持久性是一个有点模糊的概念，实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且不可能有100%的持久性保障。如果数据库本身就能做到真正的持久性，那么备份又怎么能增加持久性呢？
ACID事务和InnoDB引擎提供的保证是MySQL中最强大、最成熟的特性之一。虽然它们在吞吐量方面做了一定的权衡，但如果应用得当，就可以避免在应用层实现大量复杂逻辑。
并发事务问题
- 脏读（dirty read）：一个事务读取另一个事务还未提交的数据
- 不可重复读：在同一个事务中，多次读取同一数据，但每次读取的结果都不一致。这是因为在事务执行期间，其他事务修改了该数据，导致每次读取的结果不同。（对于同一条记录）
- 幻读：在同一个事务内，多次执行同一查询，但每次查询返回的结果集都不一致。这是因为在事务执行期间，其他事务插入或删除了满足查询条件的数据，导致每次查询的结果集不同。（查到不同条数的记录）
“不可重复读”和“幻读”都是读的过程中数据前后不一致，只是前者侧重于update，后者侧重于insert和delete。个人认为，严格来讲“幻读”可以被称为“不可重复读”的一种特殊情况，但是从数据库管理的角度来看二者是有区别的。
- 解决“不可重复读”只要加行级锁就可以了。
- 而解决“幻读”则需要加表级锁，因为对于insert，由于插入行不存在，无法加锁。正常情况下可重复的隔离级别下是存在幻读的，如果要解决幻读需要加表锁，但MySQL的InnoDB引擎在 可重复读 的隔离级别下使用 间隙锁 解决了幻读。
隔离级别
事务隔离级别则用于控制并发事务之间的可见性和影响范围，保证并发执行的事务之间相互隔离，避免数据异常和并发问题的出现。
ANSI SQL标准定义了4种隔离级别。
这个通用标准的目标是定义在事务内外可见和不可见的更改的规则。较低的隔离级别通常允许更高的并发性，并且开销也更低。每种存储引擎实现的隔离级别都不尽相同。
对于隔离性，简单来说就是多个事务之间是彼此隔离的，互不影响。但想要做到完全的互不影响是很难的，因为数据的强一致性，很多时候需要牺牲性能去达成。比如如果我们能接受事务的串行执行，那一定是互不影响的。然而现实是，MySQL作为一个数据库，必然是要支持一定程度的并行执行的，也就是多个事务同时去执行。
READ UNCOMMITTED（未提交读）
在READ UNCOMMITTED级别，在事务中可以查看其他事务中还没有提交的修改。这个隔离级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他级别好太多，却缺乏其他级别的很多好处，除非有非常必要的理由，在实际应用中一般很少使用。
READ COMMITTED（提交读）
大多数数据库系统的默认隔离级别是READ COMMITTED（但MySQL不是）。
READ COMMITTED满足前面提到的隔离性的简单定义：一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所做的任何修改对其他事务都是不可见的。
这个级别仍然允许不可重复读（nonrepeatable read），这意味着同一事务中两次执行相同语句，可能会看到不同的数据结果。
REPEATABLE READ（可重复读）
REPEATABLE READ解决了READ COMMITTED级别的不可重复读问题，保证了在同一个事务中多次读取相同行数据的结果是一样的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（phantom read）的问题。
InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。
SERIALIZABLE（可串行化）
SERIALIZABLE是最高的隔离级别。该级别通过强制事务按序执行，使不同事务之间不可能产生冲突，从而解决了前面说的幻读问题。简单来说，在这个隔离级别下，一个事务执行时会对所涉及的所有数据加锁，其他事务无法修改这些数据，直到该事务提交或回滚后才会释放锁。所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，除非需要严格确保数据安全且可以接受并发性能下降的结果。
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;设置事务隔离级别（当点会话/全局）
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }死锁
死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时，也可能会发生死锁。
例如，设想运行下面两个针对主键为（stock_id，date）的StockPrice表的事务：
每个事务都开始执行第一个查询，在处理过程中会更新一行数据，同时在主键索引和其他唯一索引中将该行锁定。然后，每个事务将在第二个查询中尝试更新第二行数据，却发现该行已经被锁定。这两个事务将永远等待对方完成，除非有其他因素介入解除死锁。
为了解决这个问题，数据库系统实现了各种死锁检测和锁超时机制。更复杂的系统，比如InnoDB存储引擎，检测到循环依赖后会立即返回一个错误信息。这可能是一件好事——否则，死锁将表现为非常缓慢的查询。还有一种方式，当超过锁等待超时的时间限制后直接终止查询，这样做通常来说不太好。InnoDB目前处理死锁的方式是将持有最少行级排他锁的事务回滚（这是一种最容易回滚的近似算法）。
锁的行为和顺序是和存储引擎相关的。同样的一系列查询语句，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。
一旦发生死锁，如果不回滚其中一个事务（部分或全部），就无法打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新从头开始执行被回滚的事务即可，除非又遇到另一个死锁。
事务日志
事务日志有助于提高事务的效率。存储引擎只需要更改内存中的数据副本，而不用每次修改磁盘中的表，这会非常快。然后再把更改的记录写入事务日志中，事务日志会被持久化保存在硬盘上。因为事务日志采用的是追加写操作，是在硬盘中一小块区域内的顺序I/O，而不是需要写多个地方的随机I/O，所以写入事务日志是一种相对较快的操作。最后会有一个后台进程在某个时间去更新硬盘中的表。因此，大多数使用这种技术（write-ahead logging，预写式日志）的存储引擎修改数据最终需要写入磁盘两次。
如果修改操作已经写入事务日志，那么即使系统在数据本身写入硬盘之前发生崩溃，存储引擎仍可在重新启动时恢复更改。具体的恢复方法则因存储引擎而异。
Binlog（二进制日志）
Binlog是MySQL的一种二进制日志，它记录了所有对MySQL数据库的写操作(增、删、改)。它可以用来进行数据库的备份和恢复，并且也可以用来进行主从同步。主库会将所有的写操作记录到Binlog中，并将其发送给从库，从库则可以根据Binlog来进行数据的同步。
Redo Log（重做日志）
Redo Log是MySQL中的一种重做日志，它记录了所有对InnoDB存储引擎进行的物理操作。当数据库崩溃或者出现宕机等异常情况时，Redo Log可以用来恢复数据库的数据。每次数据更新时，InnoDB存储引擎都会将Redo Log记录到磁盘上。
Undo Log（撤销日志）
Undo Log是MySQL中的一种撤销日志，它记录了事务执行过程中对数据的修改操作。当事务回滚时，MySQL会根据Undo Log中的信息来撤销已经执行的修改操作。与Redo Log不同，Undo Log是在内存中维护的，而不是记录到磁盘上。
总结：Redo Log和Undo Log都是在InnoDB存储引擎层面上实现的，而Binlog是在MySQL服务层面上实现的。在数据库的事务执行过程中，当对数据进行修改时，会先记录Undo Log，然后再进行数据的修改，之后再记录Redo Log。这样做的目的是为了保证数据的可恢复性。
在数据备份和恢复方面，Binlog是最常用的，因为它可以用于数据的恢复和主从同步。Redo Log也可以用于数据的恢复，但是它主要用于在崩溃或宕机等异常情况下恢复数据。而Undo Log主要用于事务的回滚。
MySQL中的事务
理解AUTOCOMMIT
默认情况下，单个INSERT、UPDATE或DELETE语句会被隐式包装在一个事务中并在执行成功后立即提交，这称为自动提交（AUTOCOMMIT）模式。通过禁用此模式，可以在事务中执行一系列语句，并在结束时执行COMMIT提交事务或ROLLBACK回滚事务。
在当前连接中，可以使用SET命令设置AUTOCOMMIT变量来启用或禁用自动提交模式。启用可以设置为1或者ON，禁用可以设置为0或者OFF。如果设置了AUTOCOMMIT=0，则当前连接总是会处于某个事务中，直到发出COMMIT或者ROLLBACK，然后MySQL会立即启动一个新的事务。此外，当启用AUTOCOMMIT时，也可以使用关键字BEGIN或者START TRANSACTION来开始一个多语句的事务。修改AUTOCOMMIT的值对非事务型的表不会有任何影响，这些表没有COMMIT或者ROLLBACK的概念。
还有一些命令，当在活动的事务中发出时，会导致MySQL在事务的所有语句执行完毕前提交当前事务。这些通常是进行重大更改的DDL命令，如ALTER TABLE，但LOCK TABLES和其他一些语句也具有同样的效果。
MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个服务器的隔离级别，也可以只改变当前会话的隔离级别：
建议最好在服务器级别设置最常用的隔离，并且只在显式情况下修改。MySQL可以识别所有4个ANSI标准的隔离级别，InnoDB也支持这些隔离级别。
在事务中混合使用存储引擎
MySQL不在服务器层管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，混合使用多种存储引擎是不可靠的。
假设在事务中混合使用事务表和非事务表（例如，InnoDB和MyISAM表），如果一切顺利，事务将正常工作。如果需要回滚，则无法撤销对非事务表的更改。这会使数据库处于不一致的状态，可能难以恢复，并使整个事务问题变得毫无意义。所以，为每张表选择合适的存储引擎，并不惜一切代价避免在应用中混合使用存储引擎是非常重要的。
824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
