第十四章 MySQL

一、MySQL

1.1 MySql 体系结构

    

MySQL 架构总共四层，在上图中以虚线作为划分。

1. 最上层的服务并不是 MySQL 独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。

2. 第二层的架构包括大多数的 MySQL 的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

3. 第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎 API 包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析 SQL（InnoDB 会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。

4. 第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上。

   

1.1.1 SQL 语句的执行流程

   

1. 建立连接

连接数默认 151 最大 10000。

  

2. 查询缓存

默认关闭 8.0 删除 比较鸡肋，因为缓存触发条件苛刻，eg：查询语句完全一样；数据库有数据更新缓存会清空。

  

3. 解析器

词法解析 将 sql 打散成一个一个的词。

语法解析 对 sql 进行语法检验；同时将词法解析成的词语 按照语法规则生成特定的数据结构 ----解析树。

  

4. 预处理器

检查生成的解析树，处理解析器无法解析的语义，比如表名、列名是否存在，检查名字和别名保证没有歧义 同时会生成新的解析树。

  

5. 查询优化器

一条 Sql 语句可以有很多种执行方式，但是返回的结果是一样的。查询优化器的目的就是基于解析树生成不同的解析计划，从中选择一个最优的执行计划；MYSQL

里面使用的是基于开销的优化器，那种执行计划开销最小就是用哪种。

比如：多表关联查询，基准表的选择；多个索引可以使用时候，选择哪个索引；

  

6. 执行计划

优化器优化解析树会得到另一个数据结构的数据-----查询执行计划使用 EXPLAIN 可以查看。

    

7. 执行引擎

使用执行计划操作存储引擎，通过存储引擎提供的 API 完成，得到结果最后返回给客户端；

   

8. 存储引擎

存储数据的形式。

MYISM 适用只读的场景 支持表锁、记录数据行数、插入和查询较快。

INNODB 支持表锁、行锁、外键、事务；支持读写并发，写不阻塞读(MVCC)；特殊的索引存放，可以减少 IO，提升查询效率。

MEMORY 适用临时表 数据存在内存中，读写快；数据库崩溃或者宕机，数据消失。

  

1.1.2 一条更新 SQL 的执行

1. 建立连接，事务开启，查询缓存（可跳过，默认不开启），然后通过解析器词法解析，语法分析，预处理等生成解析树，然后通过查询优化器获得一个开销最小的执行计划，然后通过执行引擎调用 API 接口操作存储引擎获取数据，返回查询结果给 server 的执行器；

2. server 的执行器修改数据页中的一行数据；

3. 记录修改日志到 undo log

4. 记录日志到 redo log

5. 调用存储引擎接口，记录数据页到 buffer pool 中

6. 事务提交；

修改数据的时候会先写入缓冲区的数据页中，因此缓冲区的数据页与磁盘中的数据出现了数据不一致的现象，此时的数据页被称为脏页；将脏页数据更新到磁盘的过程称为刷脏；

   

1.2 MySQL 存储引擎

1.2.1 存储引擎

MySQL 中的数据用各种不同的技术存储在文件（或者内存）中。

这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。

通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。

这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也称作表类型)。

   

MySQL 默认配置了许多不同的存储引擎，可以预先设置或者在 MySQL 服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

选择如何存储和检索你的数据的这种灵活性是 MySQL 为什么如此受欢迎的主要原因。其它数据库系统 (包括大多数商业选择)仅支持一种类型的数据存储 。

  

1.2.2 MySQL 支持的存储引擎

MySQL5.6 支持的存储引擎包括：

1. InnoDB

2. MyISAM

3. MEMORY

4. CSV

5. BLACKHOLE

6. FEDERATED

7. MRG_MYISAM

8. ARCHIVE

9. PERFORMANCE_SCHEMA。

  

其中 NDB 和 InnoDB 提供事务安全表，其他存储引擎都是非事务安全表。

  

1.2.3 各种存储引擎的特性

1. 并发性：某些应用程序比其他应用程序具有很多的颗粒级锁定要求（如行级锁定）。

2. 事务支持：并非所有的应用程序都需要事务，但对的确需要事务的应用程序来说，有着定义良好的需求，如 ACID 兼容等。

3. 引用完整性：通过 DDL 定义的外键，服务器需要强制保持关联数据库的引用完整性。

4. 物理存储：它包括各种各样的事项，从表和索引的总的页大小，到存储数据所需的格式，到物理磁盘。

5. 索引支持：不同的应用程序倾向于采用不同的索引策略，每种存储引擎通常有自己的编制索引方法，但某些索引方法（如 B-tree 索引）对几乎所有的存储引擎来说是共同的。

6. 内存高速缓冲：与其他应用程序相比，不同的应用程序对某些内存高速缓冲策略的响应更好，因此，尽管某些内存高速缓冲对所有存储引擎来说是共同的（如用于用户连接的高速缓冲，MySQL 的高速查询高速缓冲等），其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义。

7. 性能帮助：包括针对并行操作的多 I/O 线程，线程并发性，数据库检查点，成批插入处理等。

8. 其他目标特性：可能包括对地理空间操作的支持，对特定数据处理操作的安全限制等。

   

1.2.4 各种搜索引擎介绍

- InnoDB：MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。

InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。

- MyISAM：MyISAM 既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。

- Memory：在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory 类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失。

- BLACKHOLE：黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。

- CSV：它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据，以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引，你最好是在普通操作中将数据放在 InnoDB 表里，只有在导入或导出阶段使用一下 CSV 表。

- NDB：(又名 NDBCLUSTER)——这种集群数据引擎尤其适合于需要最高程度的正常运行时间和可用性的应用。注意：NDB 存储引擎在标准 MySql 5.6 版本里并不被支持。

- MySql 集群的版本有：基于 MySql 5.1 的 MySQL Cluster NDB 7.1；基于 MySql 5.5 的 MySQL Cluster NDB 7.2；基于 MySql 5.6 的 MySQL Cluster NDB 7.3。同样基于 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正处于研发阶段。

- Merge：允许 MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组，并把它们作为一个对象进行引用。适用于超大规模数据场景，如数据仓库。

- Federated：提供了从多个物理机上联接不同的 MySql 服务器来创建一个逻辑数据库的能力。适用于分布式或者数据市场的场景。

- Example：这种存储引擎用以保存阐明如何开始写新的存储引擎的 MySql 源码的例子。它主要针对于有兴趣的开发人员。这种存储引擎就是一个啥事也不做的“存根”。你可以使用这种引擎创建表，但是你无法向其保存任何数据，也无法从它们检索任何索引。

    

1.2.5 存储引擎相关 sql 语句

# 查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";
# 查询当前数据库支持的存储引擎
mysql> show engines \G;

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 
mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;
# 也可以使用alter table语句，修改一个已经存在的表的存储引擎。
mysql> alter table ai engine = innodb;

# my.ini文件
[mysqld]
default-storage-engine=INNODB

  

1.3 MySQL 内存结构

1.3.1 Innodb 内存架构

   

主要分以下几个要点

- Buffer Pool（buffer 池）

- Change Buffer（更改 buffer）

- Adaptive Hash Index（自适应 hash 索引）

- Log Buffer（日志 buffer）

   

1.3.2 Buffer Pool

Buffer Pool 是 mysql 运行时使用的一块内存区域，用来存储/修改/访问 Table 和 Index 数据的内存区域。mysql 用它存储了被频繁访问的数据。

理论上来说，它能使用的内存空间越大，mysql 性能越好；所以在专门的 mysql 服务器上，一般分配物理内存的 80%给 Buffer Pool。

为了提升大容量数据的访问性能，Buffer Pool 内部分为可以容纳多行的页（Page）。

为了方便缓存数据的管理，Buffer Pool 又实现了由页组成的链表（linked list），数据的过期策略采用 LRU 算法实现。

  

> 了解如何利用缓冲池将经常访问的数据保存在内存中是 MySQL 调优的一个重要方面。

新链表与老链表（以下称 new 链表和 old 链表）。

在单链表的基础上，Buffer Pool 又划分了 new 子链表和 old 子链表。

- new 子链表处于单链表的头部，占 5/8；old 处于尾部，占 3/8。

- new 是经常被访问部分，old 反之。

- new 和 old 分开管理。

   

- 图中的 midpoint 是 new 和 old 的交接点

- 当从磁盘中读取的数据被插入链表时，第一次是插入 old 子链表的头部，old 子链表中的数据被访问时会被移动到 new 子链表的头部（预读操作除外）

- new 和 old 子链表中的页面会随着其他页面的更新而老化，未使用或少使用的页逐渐到达 old 子链表的尾部，然后并被驱逐(evicted)。

  

为什么要用双链表？

> 单链表情况下的缓存池污染

>

> 1. 默认的机制是，page 只要被读取就会被移动到链表头部。这在某些情况下会造成缓冲池污染！比如 mysqldump 操作或者不带 WHERE 的 SELECT 查询会一次性往 bp 页链表中存入大量的数据，并导致等量的数据老化失效。而这些操作是临时性的，读取的大量数据基本很长时间都不会被再次读取，这就造成了 bp 池污染，严重降低了 mysql 性能！

> 2. 同样的原理，预读操作也会造成 bp 池污染！

>

> 双链表方案

>

> 1. 区分 new 和 old 链表，使用 old 链表存储那些刚从磁盘加载的数据

> 2. 不管是什么操作，数据从磁盘加载后，都是先存储 old 链表中，若这些数据在淘汰前再次被读取，说明它们的确是热数据，需要移到 new 链表的头部；否则就一直处于 old 链表，会以比 new 链表更快的速度被淘汰。

   

1.3.3 Change Buffer

Change Buffer（下文简称 CB）是一种由多个二级索引页和一颗 B+树（存在于共享表空间，ibdata1 文件）构成的内存空间，用于缓存针对不存在于 buffer pool 中的二级索引页的 DML 操作（INSERT, UPDATE, DELETE），稍后这些变更根据各种机制合并到 buffer pool 中。

  

下图是 CB 与 buffer pool 的交互图

   

Change Buffer 内部实现

CB 是由一个 B+树构成的，它负责对所有表的二级索引更改进行记录。树的非叶节点存放的是 search key，其构造如下图：

  

search key 一共占用 9 个字节，其中 space 表示待插入记录所在表的表空间 id，在 InnoDB 存储引擎中，每个表有一个唯一的 space id，可以通过 space id 查询得知是哪张表。space 占用 4 字节。marker 占用 1 字节，它是用来兼容老版本的 Insert Buffer。offset 表示页所在的偏移量，占用 4 字节。当一个二级索引记录要 insert 到页( space，offset）时，如果这个页不在缓冲池中，那么 InnoDB 引擎首先根据上述规则构造一个 search key，接下来查询 Change Buffer 这棵 B+树，然后再将这条记录插入到 Change Buffer B+树的叶子节点中。

  

对于插人到 Change Buffer B+树叶子节点的记录，并不是直接将待插入的记录插入，而是需要根据如下的规则进行构造：

  

前几个字段和 searchkey 一样就不说了，metadata 占 4 字节，记录了这条变更记录的元数据，比如包含了插入这棵树的顺序值用于 replay 恢复数据，其他的不做 DB 开发不需要掌握。后面的 secondary index record 部分就是描述本次操作(DML)的内容了。

  

1.3.4 自适应 Hash 索引（Adaptive Hash Index）

简称 AHI，具体来说是给经常被访问的索引页（热点页）建立 hash 索引，可以描述为索引的索引。

它加速了对热点页的查找过程，常规是通过 B+tree 查找，可能需要 2-3 次 I/O，而通过 hash 索引可以直接找到随意的索引页，不再需要树查找。

  

它有几个特点如下：

- 限制场景使用，比如仅包含 = 和 IN 操作符的等值查询。

- hash 索引的 key 值是 where 条件中的索引字段，如果是组合索引字段，可以只包含部分字段。

- 在部分场景下，对自适应哈希索引的访问有时会导致严重的锁竞争，如高并发 join 查询。

- MySQL 自动调整，无法干预。

  

5.7 版本及以上中，AHI 使用了分段锁来减少高并发场景下锁的竞争，提高性能 。 官 文 中 描 述 为 分 区 (partitioned) ， 其 实 一 个 意 思 。 它 由 参 数 `innodb_adaptive_hash_index_parts`控制，默认 8，最高 512。

  

查看相关配置 show variables like '%hash_index%'

  

关闭 set global innodb_adaptive_hash_index='off'

  

1.3.5 Log buffer

是一块用来作为 redo log 在内存缓冲区的内存区域，增加 log buffer size 可以提升事务并发性能，减少磁盘 I/O（redo log 会在 log buffer 不够用时刷盘）。另外，log buffer 也会定期刷盘。

  

相关可调变量：

- innodb_log_buffer_size，默认 16MB。

- innodb_flush_log_at_trx_commit，控制如何将日志缓冲区的内容写入并刷到磁盘，具体细节涉及到 redo log 部分，暂不细讲。

- innodb_flush_log_at_timeout，控制日志刷新频率。

  

另外，还有几个变量是调整 redo log 的物理文件大小。

- innodb_log_file_size 控制单个 redo log 文件大小，默认 48MB，最大 512GB，文件名 ib_logfile0 和 ib_logfile1，这两个文件是循环写入的；值越大，能存的 redo log 越多，就减少了 redo log 写入到数据页的 I/O 次数，同时 mysql 启动时的恢复时间越长，可参考的时间是 1G 的 redo log 文件需要 5 分钟的恢复时间。

- innodb_log_files_in_group 控制 redo log 文件数量，默认 2

- innodb_log_group_home_dir 控制 redo log 的位置，默认 data 目录

  

1.4 MySQL 磁盘结构

InnoDB 磁盘主要包含【Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log 和 Undo Logs】五部分组成。

  

1.4.1 表空间（Tablespaces）

innodb 存储引擎在存储设计上模仿了 Oracle 的存储结构，其数据是按照表空间进行管理的。表空间用于存储表结构和数据。表空间又分为系统表空间、独立表空间、 通用表空间、临时表空间、Undo 表空间等多种类型。

  

1. 表空间组成

- 物理结构组成

在系统表空间由于所有的表公用一个.ibdatat1 数据文件，所以针对每个表只有一个.frm 表结构文件。

在独立表空间中，每个表分别都有一个.frm 表结构文件，一个.ibd 数据文件。innodb 存储引擎物理组织形式可以理解为其在磁盘的存储形式，表现为各种文件，其分类大概为

| 文件          | 功能           | 描述                                               |
| :------------ | -------------- | -------------------------------------------------- |
| ibdatat1      | 共享表空间文件 | 系统/共享表空间，存储各种缓冲数据                  |
| .frm          | 表定义文件     | 记录表的定义，列名以及列的数据类型                 |
| .ibd          | 表数据存储文件 | 独立表空间，存储数据表的数据，按行存储             |
| ib_logfile0/1 | redo日志文件   | 重做日志文件，一共两个循环使用，一个写完即写另一个 |

  

表空间是 innodb 存储引擎逻辑结构的最高层，所有的数据都存储在表空间中，默认 innodb 有一个共享表空间，所有的数据都存储在共享表空间中，可以通参数 innodb_per_table 设置每张表单独存放在一个表空间中。

- 独立表空间内存储的只是数据、索引、插入缓冲页。

- 回滚日志、插入缓冲索引页、事务信息、二次写缓冲等其他数据还是存放在共享表空间。

  

1.4.2 表空间的五种类型

- 系统表空间（The System Tablespace）

- 包含 InnoDB 数据字典，Doublewrite Buffer，Change Buffer，Undo Logs 的存储区域。

- 系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。

- 系统表空间是一个共享的表空间因为它是被多个表共享的。

  

> innodb_data_file_path 用来指定 innodb tablespace 文件，如果我们不在 My.cnf 文件中指定 innodb_data_home_dir 和 innodb_data_file_path 那么默认会在 datadir 目录下创建 ibdata1 作为 innodb tablespace。

  

#默认值:
innodb_data_file_path = ibdata1:12M:autoextend
# ibdata1 : 文件名为 ibdata1
# 12M : 大小为 12M
# autoextend : 自动扩展

     

- 独立表空间（File-Per-Table Tablespaces）

默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。 开启独立表空间参数为：innodb_file_per_table

  

  innodb_file_per_table = ON：独立表空间：tablename.ibd
  innodb_file_per_table = OFF：系统表空间：ibdataX

  

- 【innodb_file_per_table = ON】
新建表被创建于【表空间】中,每一个表建立ibd的扩展文件,文件名为：表名.ibd，该文件默认被创建于数据库目录中,表空间的表文件支持动态和压缩行格式。
  

- 【innodb_file_per_table = OFF】
innodb将被创建于【系统表空间】中，即ibdataX中。X代表从1开始的一个数字

  

- 【查看表的存储表空间的存储方式值】
show variables like 'innodb_file_per_table';

  

- 【修改存储表空间的存储方式值为OFF】
set global innodb_file_per_table=off;

  

- 通用表空间（General Tablespaces）
通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于 mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。

  

#创建表空间tablespaces1
CREATE TABLESPACE tablespaces1 ADD DATAFILE tablespaces1.ibd Engine=InnoDB; 
#将表添加到test1表空间
CREATE TABLE test1 (c1 INT PRIMARY KEY) TABLESPACE tablespaces1; 

  

- 撤销表空间（Undo Tablespaces）
撤销表空间由一个或多个包含Undo日志文件组成。
> 在MySQL 5.7版本之前Undo占用的是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。

  

- 【innodb_undo_tablespaces】
innodb_undo_tablespaces = 0 ：默认值，表示使用系统表空间ibdata1
innodb_undo_tablespaces = 1：大于0表示使用undo表空间undo_001、 undo_002等

  

- 临时表空间（Temporary Tablespaces）
mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

  
临时表空间分为两种：
- 【session temporary tablespaces】
存储的是用户创建的临时表和磁盘内部的临时表。
- 【global temporary tablespace】
储存用户临时表的回滚段（rollback segments）。

  

1.4.3 数据字典（InnoDB Data Dictionary）

InnoDB 数据字典由内部系统表组成。这些表包含用于查找表、索引和表字段等对象的元数据。

元数据物理上位于 InnoDB 系统表空间中。数据字典元数据在一定程度上与 InnoDB 表元数据文件（.frm 文件）中存储的信息重叠。

  

1.4.4 双写缓冲区（Doublewrite Buffer）

位于系统表空间，是一个存储区域。

 

> 在 BufferPage 的 page 页刷新到磁盘真正的位置前，会先将数据存在 Doublewrite 缓冲区。如果在 page 页写入过程中出现操作系统、存储子系统或 mysqld 进程崩溃，InnoDB 可以在崩溃恢复期间从 Doublewrite 缓冲区中找到 page 页备份。在大多数情况下，默认情况下启用双写缓冲区。

>

  

- innodb_doublewrite = 0 ：禁用 Doublewrite 缓冲区

- innodb_flush_method = O_DIRECT ：数据文件写入操作会通知操作系统不要缓存数据，也不要用预读。

innodb_flush_method 控制 innodb 数据文件及 redo log 的打开、 刷写模式。

  

1.4.5 重做日志（Redo Log）

- 重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间修正不完整事务写入的数据。

- MySQL 以循环方式写入重做日志文件，记录 InnoDB 中所有对 Buffer Pool 修改的日志。

  

> 当出现实例故障，导致数据未能更新到数据文件，则数据库重启时须 redo，重新把数据更新到数据文件。读写事务在执行的过程中，都会不断的产生 redo log。默认情况下，重做日志在磁盘上由两个名为 ib_logfile0 和 ib_logfile1的文件物理表示。

  

1.4.6 撤销日志（Undo Logs）

撤消日志是在事务开始之前保存的被修改数据的备份，用于回滚事务。

撤消日志属于逻辑日志，根据每行记录进行记录。

撤消日志存在于系统表空间、撤消表空间和临时表空间中。