《高性能MySQL》（一）架构和历史

MySQL的存储引擎架构将查询处理及其他系统任务和数据的存储/提取相分离。从而可以在使用时根据性能、特性等需求来选择数据存储的方式。

一. MySQL逻辑架构

• 第一层：连接处理、授权认证、安全等等。
• 第二层：查询解析、分析、优化、缓存以及所有的内置函数，还有所有的跨存储引擎的实现：存储过程、触发器、视图。
• 第三层：存储引擎，服务器通过API与存储引擎进行通信，包括几十个底层函数，如“开始一个事务”、“根据主键提取一行记录”。存储引擎不会解析SQL（Innodb会解析外键，因为MySQL服务器本身没有实现此功能），存储引擎只会简单的响应上层服务器的请求。

1.1 连接与安全

每个客户端连接对应服务器进程中的一个线程，连接的所有查询都在此线程中进行，服务器会负责缓存线程而不必每次都创建新线程，MySQL 5.5后支持线程池来支持大量连接。

认证基于用户名、原始主机信息和密码。使用安全套接字SSL的方式连接，可以使用X5.09证书认证。连接成功后会继续验证用户是否有相应请求的权限。

1.2 优化与执行

MySQL会解析查询，并创建内部数据结构-解析树，然后对其进行各种优化，包括重写查询、决定表的读写顺序、选择合适的索引等。

• 可以通过特殊关键字 hint 提示优化器，影响其决策过程；
• 可以通过 explain 请求优化器解释优化过程的各个因素，从而了解到服务的优化决策，便于用户重构查询和schema、修改相关配置。

对于如 SELECT 语句，在解析查询前会检查查询缓存，若有相应查询就可以直接返回结果。

二. 并发控制

MySQL在两个层控制并发：服务器层和存储引擎层。

2.1 读写锁

并发读数据不会有问题，但当一个用户读取数据，另外一个用户删除数据时，可能读取会报错，也可能读到了不一致的数据。

通过实现共享锁（shared lock）和排它锁（exclusive lock），也叫读锁和写锁，这两种锁组成的锁系统来解决问题。写锁会阻塞其他写锁和读锁，

2.2 锁粒度

为了提高共享资源的并发性，减少锁定的对象，避免锁住所有资源。但锁的所有操作，如获取锁、检查是否锁已释放、释放锁等都会增加系统开销。锁策略就是在锁的开销和数据的安全性之间寻找平衡。

• 表锁：
  • 表锁是开销最小的锁，会锁定整张表，写操作时会阻塞其他读写操作，写锁未被持有时其他用户才能获取读锁，读锁间不相互阻塞。
  • 写锁的优先级比读锁要高，所以写请求可能会被插入到读请求前，服务器可能会忽略存储引擎的表锁实现为如 ALTER TABLE 之类的语句加表锁。
• 行锁：
  • 行锁可以提供更好的并发性，但也带来了最大的锁开销。行锁由存储引擎实现（InnoDB和XtraDB等）

2.3 锁类型

MySQL包括如下锁类型：

• 读写锁：同Java一样，写锁持有时其他事务不能获取任何锁，读锁持有时其他事务可以获取读锁，但不能获取写锁。

  • 互斥锁：简称X锁，又叫写锁。
  • 共享锁：简称S锁，又叫读锁。

• 意向锁：更容易的支持多粒度的封锁，在同时存在行级锁和表级锁的情况下，事务想要对表加锁，要首先分别检查是否有其他事务加表锁或行锁，这个过程需要对每一行都进行一次检测，这相当耗时。

  意向锁在 X/S 锁之上引入了 IX/IS，二者都是表锁，规定事务获取某行的X或S锁前要先获取IX或IS锁。这样当事务想对表加写锁时只需检测是否有其他事务对表加了 X/IX/S/IS 锁，只需做一次检测。（任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁）

  • 意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁；
  • 意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁；
  • 插入意向锁（Insert Intention Lock）：插入意向锁是间隙锁的一种，专门针对insert操作的。即多个事务在同一个索引、同一个范围区间内插入记录时，如果插入的位置不冲突，则不会阻塞彼此

• 自增锁（Auto-inc Locks）：自增锁是一种特殊的表级别锁，专门针对事务插入 AUTO-INCREMENT 类型的列。即一个事务正在往表中插入记录时，其他事务的插入必须等待，以便第1个事务插入的行得到的主键值是连续的。

• 记录锁（Record Locks）：

  • 记录锁是的单个行记录上的锁，会阻塞其他事务对其插入、更新、删除；
  • locks rec but not gap 。

• 间隙锁（Gap Lock）：间隙锁锁定记录的一个间隔，但不包含记录本身。

• 临键锁（Next-Key Lock）：

  • Next-Key Lock = Gap Lock + Record Lock 临建锁是记录锁与间隙锁的组合，即：既包含索引记录，又包含索引区间，主要是为了解决幻读。

总结：

• 表锁：意向锁，自增锁。
• 行锁：读写锁，记录锁，间隙锁，临键锁。

三. 事务

3.1 什么是事务？

事务是一组原子性的SQL查询，一个独立的工作单元。全部语句能够被数据库引擎成功应用，则执行该组查询；有任一语句无法执行，事务的所有语句都不会执行。要么全部执行成功，要么全部执行失败。

3.2 ACID

• 原子性（Atomicity）：事务作为一个不可分割的整体被执行，包含在其中的对数据库的操作要么全部被执行，要么全部不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。数据库发生故障导致事务中断，未完成的修改不能写入物理数据库。
• 隔离性（Isolation）：一个事务在最终提交前应该对其他事务是不可见的，在并发操作相同数据时，每个事务都有独立的数据空间；多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。系统发生奔溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。

事务所带来的安全性也需要额外的工作和开销，而MySQL的存储引擎架构可以支持用户根据业务是否需要事务选择合适的存储引擎。

3.3 隔离级别

1. 读未提交（READ UNCOMMITTED）：
   • 事务中的修改，即使没有提交，对其它事务也是可见的。
   • 会导致脏读（读到未提交的数据），性能也并未比其它级别好很多。
2. 读已提交（READ COMMITTED）：
   • 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
   • 也叫不可重复读，因为两次执行相同的查询可能会得到不同的结果。
   • 大部分数据库的默认隔离级别，MySQL因为历史遗留问题，默认隔离级别是可重复读。
3. 可重复读（REPEATABLE READ）：
   • 保证在同一个事务中多次读取同一数据的结果是一样的。
   • 不可重复读在同一事务中多次读取可能会读到不同的数据结果，可重复读则保证同一个事务中只会读到相同结果
4. 可串行化（SERIALIZABLE）：强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。

隔离级别	脏读	不可重复读	幻读	加锁读
READ UNCOMMITTED	Yes	Yes	Yes	No
READ COMMITTED	No	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
SERIALIZABLE	No	No	No	Yes

（1）为什么MySQL默认是可重复读？

MySql的历史版本中，主从复制基于binlog（记录数据库修改的文件），当时binlog只有 statement 这种模式（记录修改SQL语句），后来还有row（记录每行数据变更）和 mixed（前两种模式混合）总共三种模式。

statement模式在读已提交下进行主从复制有BUG，在master上执行的顺序为先删后插，而binlog的记录顺序为先插后删，所以slave同步binlog后导致执行顺序和master不一致。

解决方法：

1. 调整隔离级别为可重复读。
2. 更新模式为row（5.1版本后引入）。

（2）为什么选择读已提交？

首先排除读未提交和可串行化，前者会出现脏读，后者每次读操作都要加锁。

选读已提交/不可重复读的原因：

1. 可重复读下，存在间隙锁（封锁索引记录中的间隔）导致死锁概率增加：如 SELECT * FROM test WHERE id BETWEEN 5 AND 7 ，表中有id（1，5，7，10），所以存在5个间隙，上述sql会锁住(5,7]和(7,10]这两个区间，导致区间无法插入数据。而读已提交不存在间隙锁。
2. 可重复读下，条件未命中索引会锁表，而读已提交，只会锁行。
3. 读已提交，因为半一致性读的特性会增加update操作的并发性。一个事务执行完update操作，但尚未提交，另一事务获取锁时发现已占用，InnoDB开启半一致性读返回最新的已提交版本，MySql会重新发起一次读操作，此时可以读到此行的最新版本并加锁，而可重复读这时事务只能等待前一事务提交。
4. 不可重复读问题可以接受，已经提交了数据，即使不一样通常也不会造成问题。

3.4 并发一致性问题

在并发环境下，事务的隔离性很难保证，所以有几种并发一致性问题：

1. 修改丢失：即一个事务的更新背另一个事务的更新替换。
2. 读脏数据：指当前事务读到另外事务未提交的数据。
3. 不可重复读：指一个事务多次读取同一数据集合，在此期间另外事务也访问了此集合并做出修改，导致前个事务两次读取数据不一致。
4. 幻影读：本质也是不可重复读，指一个事务读取某个范围的数据，另一事务在此范围插入新数据，导致前个事务再次读取结果不一致。区别在要避免幻读需要锁整张表，而避免不可重复读只需锁住行即可。

通过锁机制来解决此问题，MySql提供了对应的封锁机制来实现，通过三级封锁协议来处理并发一致性问题。

3.5 封锁协议

• 三级封锁协议：
  • 一级：事务 T 要修改数据 A 时必须加写锁，直到 T 结束才释放锁。可以解决修改丢失问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
  • 二级：在一级的基础上，要求读取数据 A 时必须加读锁，读取完马上释放读锁。可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
  • 三级：在二级的基础上，要求读取数据 A 时必须加读锁，直到事务结束了才能释放读锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。
• 两段封锁协议：加锁和解锁分为两个阶段进行。

3.6 死锁

• 死锁指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
• InnoDB有死锁检测和死锁超时机制，处理死锁时会将持有最少行级排他锁的事务进行回滚。
• 死锁可能是因为真正的数据冲突，有时可能完全是由于存储引擎的实现方式导致的。
• 应用程序需要考虑如何处理死锁，大部分情况只需重新执行因死锁回滚的事务即可。

--查看数据库隔离级别
select @@tx_isolation;
select @@global.tx_isolation,@@tx_isolation;

--查看innodb状态，打印死锁日志，NaviCat复制出去看
show engine innodb status

--设置隔离级别
set transaction isolation level XXX;
--只改变当前会话的隔离级别
set session transaction isolation level READ COMMITED;

3.7 事务日志

事务日志用来提高事务的执行效率，存储引擎在修改表的数据时只需修改内存拷贝，再把修改行为记录在持久的硬盘上的事务日志中，而不是每次都把修改的数据持久到硬盘。

事务日志通过追加的方式，操作是磁盘小块区域上的顺序I/O，要比随机I/O快得多。所以每次修改数据需要写两次磁盘。

3.8 存储引擎与事务

MySQL有很多支持事务的存储引擎，如InnoDB、NDB Cluster、XtraDB和PBXT等。

（1）自动提交（AUTO COMMIT）

MySQL默认采用自动提交模式。每个查询都会被当做一个事务执行提交操作。

SHOW VARIABLES LIKE 'AUTOCOMMIT'

Variable_name	Value
autocommit	    ON
--1启用0禁用
SET AUTOCOMMIT = 1;
SET AUTOCOMMIT = 0;

数据定义语言（DDL）中涉及大量数据改变的操作（如Alter Table、Lock Tables）会强制执行COMMIT提交当前活动事务。

（2）在事务中混合使用存储引擎

事务由存储引擎实现，所以在一个事务中使用多种存储引擎不可靠，如果在事务中混合使用了多个包括事务型和非事务型存储引擎的表，正常提交不会有问题，但需要回滚时非事务型的表无法撤销变更。而且仅仅在回滚时提示一个警告，其余情况不会有提示。

（3）隐式和显式锁定

• 隐式锁定：InnoDB采用两阶段锁定协议，事务执行过程中可以随时锁定，但只有事务提交或回滚时才会释放，并且是同一时刻释放。InnoDB根据隔离级别在需要时自动加锁。

• 显式锁定：通过特点语句进行锁定。

  SELECT ... LOCK IN SHARE MODE
  SELECT ... FOR UPDATE

  LOCK TABLES 和 UNLOCK TABLES 由服务器层实现，与存储引擎无关，不能用来代替事务。

四. 多版本并发控制-MVCC

4.1 什么是多版本并发控制？

大部分事务型存储引擎实现的都不是简单的行级锁，一般都同时实现了多版本并发控制-MVCC，该机制可以在很多情况下避免加锁操作，所以可以降低开销，一般都会实现非阻塞的读操作和只锁定必要行的写操作。

MVCC实现机制各不相同：

• 乐观并发控制
• 悲观并发控制

MVCC只兼容于 REPEATABLE READ 和 READ COMMITED 这两个隔离级别，而 READ UNCOMMITED 总是读取最新的数据行而不是符合当前事务版本的数据行，SERIALIZABLE 则会对所有读取的行加锁。

4.2 如何实现多版本并发控制？

通过保存数据在某个时间点的快照来实现。

InnoDB通过在每行记录后面保存两个隐藏的列，一个保存了行的创建时间，一个保存行的过期时间（实际存储的非时间而是系统版本号）。每开启一个新事务，版本号都会自动递增，事务开始时刻的版本号作为事务的版本号与查询到的每行记录进行比较。

在REPEATABLE READ隔离级别下：

• SELECT：根据两个条件检查每行记录，符合才作为查询结果返回。
  • 只查找版本早于当前事务版本的数据行，确保事务读取的行是事务开始前存在或自己修改过的。
  • 行的删除版本要么未定义，要么大于当前事务版本号，确保事务读取到的行在事务开始前未被删除。
• INSERT：为新插入的每一行保存当前系统版本号作为行版本号。
• DELETE：为删除的每一行保存当前系统版本号作为行删除标识。
• UPDATE：插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

通过这两个版本号可以使大部分读操作都不需加锁（乐观），以少量的额外存储空间和检查维护工作换取好的性能。

五. 存储引擎

文件系统中，MySQL将每个数据库保存为数据目录下的一个子目录。创建表时在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。表的定义统一在服务层处理，数据和索引则在不同的存储引擎中保存方式不同。

正因为使用了文件系统的目录文件，所以MySQL大小写敏感和平台相关，Windows中不敏感，而类Unix中则敏感。

--查看表信息
SHOW TABLE STATUS LIKE '表名'												

返回字段：

字段	含义	例子
Name	表名	version
Engine	存储引擎类型	InnoDB
Version		10
Row_format	行格式： -Dynamic的行长度可变，一般会有如VARCHAR或BLOB这种可变长字段 -Fixed的行长度固定，只包含如CHAR何INTEGER这种固定长度字段 -Compressed只在压缩表存在 ….	Compact
Rows	表的行数，对于InnoDB是估计值，MyISAM则是精确值	25
Avg_row_length	平均每行包含的字节数	655
Data_length	表数据的大小，单位字节	16384
Max_data_length	表数据的最大容量	0
Index_length	索引大小	16384
Data_free	表示已分配但目前没有使用的空间。包括已删除的行以及后续可以被Insert利用的空间	0
Auto_increment	下一个自增的值
Create_time	表的创建时间	2020-12-02 13:04:02
Update_time	表数据的最后修改时间
Check_time	使用CHECK TABLE命令或myisamchk等工具最后一次检查表的时间
Collation	表的默认字符集和字符列排序规则	gbk_chinese_ci
Checksum	启用时保存整个表的实时校验和
Create_options	创建表时指定的其他选项
Comment	额外信息，InnoDB表空间的剩余空间信息，视图VIEW文本等

5.1 InnoDB

MySQL默认的事务型存储引擎，设计用来执行大量短期事务，短期事务很少回滚。

MySQL 5.1后，新的InnoDB plugin提供很多新特性，如利用排序创建索引、删除或增加索引时不需要复制全表数据、新的支持压缩的存储格式、新的大型列值如BLOB的存储方式、以及文件格式管理等。

InnoDB：

• 数据存储在表空间（tablespace）中，由一系列数据文件组成，InnoDB可以将每个表的数据和索引存放在单独的文件中。
• 采用MVCC来支持高并发，实现了四个标准的隔离级别，默认是可重复读（REPEATABLE READ），通过间隙锁策略防止幻读出现，间隙锁使InnoDB不仅锁行，还未锁定索引中的间隙以防止幻影行的插入。
• 表基于聚簇索引建立，主键查询有很好的性能，但其二级索引（非主键索引）必须包含主键列，若主键列很大会导致所有索引都很大（主键应尽量小一些）。
• 内部优化：从磁盘读取数据时采用了可预测性预读、自动在内存中创建hash索引以加速读操作的自适应哈希索引、能够加速插入操作的插入缓冲区等。
• InnoDB支持热备份，如 MySQL Enterprise Backup、XtraBackup都可以实现。

5.2 MyISAM

支持如全文索引、压缩、空间函数等，但不支持事务和行级锁，崩溃后无法安全恢复。对于只读数据或小表且可以接受修复操作的场景仍可以继续使用该引擎。

MyISAM：

• 存储：表存储在两个文件中：数据文件和索引文件，分别以 .MYD 和 .MYI 为扩展名。
• 加锁与并发：对整张表加锁，不能针对行；读取时对所有需要的表加共享锁，写入时则加排它锁，但允许表读取时向表中插入新纪录（允许并发插入）。
• 修复：可以手工或自动执行检查和修复操作，修复操作可能会导致数据丢失而且执行速度很慢。
  • CHECK TABLE mytable 检查表错误。
  • REPAIR TABLE mytable 修复错误。
  • myisamchk 命令行工具。
• 索引：即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引。支持全文索引，基于分词创建的索引，可以支持复杂查询。
• 延迟更新索引键：创建表时，若指定了 DELAY_KEY_WRITE 选项，在每次修改执行成功后不会马上把修改的索引数据写入磁盘，而是写入内存的键缓冲区，只有清理键缓冲区或关闭表时才写入磁盘；这种方式可以极大的提升写入性能，但在崩溃时会造成索引损坏，需要进行修复。
• 压缩表：使用 myisampack 将表压缩（打包pack），压缩状态不允许修改，可以极大的减少磁盘空间占用，压缩表支持索引，但索引也是只读的。
• 性能：数据以紧密格式存储，在某些场景下性能很好。主要问题是表锁，容易导致查询处于 Locked 状态。

5.3 MySQL内建其他存储引擎

• Archive引擎：

  • 只支持INSERT和SELECT操作。
  • 高速插入和压缩：插入缓存所有的写，利用zli对插入的行进行压缩，比MyISAM更少的磁盘I/O。
  • 每次查询都要执行全表扫描，适合日志和数据采集类应用。
  • 支持行级锁和专用缓冲区，可以实现高并发的插入。
  • 查询在返回前会阻止其他查询执行以保证一致性读。

• Blackhole引擎：不保存任何数据，服务器会记录Blackhole表的日志，可以用于复制数据到备库或记录日志。

• CSV引擎：将CSV文件当做MySQL表来处理，不支持索引。可以作为一种数据交换的机制。

• Federated引擎：访问其他MySQL服务器的一个代理，创建一个客户端连接，并将查询传输到远程服务器执行，然后提取或发送需要的数据。市场化设计，经常有问题，默认禁用。

• Memory引擎：所有数据都存在内存，不需要磁盘I/O，表结构重启后仍能保留，但数据会丢失，比MyISAM要快一个数量级。

  • 支持Hash索引，只支持表级锁，不支持BLOB或TEXT类型，且行长度固定，即使指定VARCHAR实际存储时也会转换成CHAR。
  • MySQL执行查询时如果需要临时表来保存中间结果，内部就是使用Memory表，如果结果太大超过内存表限制或含有BLOB和TEXT字段，会转换为MyISAM表。

  适用场景：

  • 查找或映射表。
  • 缓存周期性聚合数据的结果。
  • 保存数据分析中产生的中间数据。

• Merge引擎：由多个MyISAM表合并成的虚拟表，引入分区功能后已被废弃。

• NDB引擎：MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合被称为MySQL集群。

5.4 第三方存储引擎

• OLTP类引擎：
  • Percona的XtraDB存储引擎基于InnoDB改进，可以作为InnoDB引擎的替代品
  • PBXT，支持ACID事务和MVCC等。
  • TokuDB，使用一种叫分形树的索引数据结构，是一种大数据存储引擎，拥有很高的压缩比。
  • RethinkDB，采用只能追加的写时复制B树作为索引的数据结构。
• 面向列的引擎：MySQL默认面向行，每行数据一起存储，查询也以行为单位处理。大数据量时面向列的方式可以传输更少的数据，压缩效率也更好。
• 社区引擎：如Aria、Groonga、OQGraph、Q4M、SphinxSE、Spider、VPForMySQL等。

5.5 如何选择合适的存储引擎

大部分情况选择InnoDB，除非需要用到InnoDB不具备的特性，并且没有替代方案，否则请尽量选择InnoDB引擎。

尽量不要混合使用多种存储引擎，会带来一系列复杂和预想不到的问题。

5.6 转换表的存储引擎

• ALTER TABLE ：ALTER TABLE mytable ENGINE = InnoDB; 需要执行较长时间，MySQL会按行将数据从原表复制到一张新表，复制期间可能会消耗系统所有I/O能力，同时对原表加上读锁。注意，即使后续再恢复原引擎，也会丢失如外键等和引擎相关的特性。

• 导出与导入：使用 mysqldump 工具将数据导出到文件，修改文件中 CREATE TABLE 语句的存储引擎选项，注意修改表名（避免重复），工具默认会在 CREATE TABLE 语句前加上 DROP TABLE 可能会导致数据丢失。

• 创建与查询：综合前两种方法，先创建一个新的存储引擎的表，然后利用 INSERT...SELECT 语句来导入数据。

  CREATE TABLE innodb_table LIKE myisam_table;
  ALTER TABLE innodb_table ENGINE=InnoDB;
  INSERT INTO innodb_table SELECT * FROM myisam_table;

  数据量大时可以考虑分批处理，针对每一段数据执行事务提交操作，避免大事务产生过多的undo。

  START TRANSACTION;
  INSERT INTO innodb_table SELECT * FROM myisam_table
  WHERE id BETWEEN x AND y;
  COMMIT;

  如果有必要，可以在执行时对原表加锁，以确保新表和原表数据一致。

  Percona Toolkit提供了 pt-online-schema-change 工具方便简单安全的执行上述过程。

六. 时间线-Timeline

• 版本 3.23 2001
• 版本 4.0 2003
• 版本 4.1 2005
• 版本 5.0 2006
• 版本 5.1 2008
• 版本 5.5 2010 ：Oracle收购，InnoDB成为默认存储引擎。
• 版本 5.6
• ….

---

参考：

🔗 《高性能MySQL》