《MySQL技术内幕：InnoDB存储引擎》读书笔记.

一、MySQL 体系架构和存储引擎

1、MySQL 被设计成一个单进程多线程架构的数据库，MySQL 数据库实例在系统上的表现就是一个进程。

2、MySQL 的体系架构，需要特别注意的是，存储引擎是基于表的，而不是数据库。

3、InnoDB 存储引擎是面向在线事务处理（OLTP）应用的首选，其特点是：支持事务、支持外键、聚簇索引、行锁设计、基于 MVCC 来获得高并发性，使用一种被称为 next-keylocking 的策略来避免幻读，还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用的功能。InnoDB 存储引擎在 1.2 版本中新增了全文索引等内容。

MyISAM 存储引擎不支持事务、表锁设计、支持全文索引，主要是面向一些在线分析处理（OLAP）数据库应用。MyISAM 存储引擎表由 MYD 和 MYI 组成，MYD 用来存放数据文件，MYI 用来存放索引文件。MyISAM 存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓存数据文件，这点和大多数的数据库都非常不同。

Memory 存储引擎不支持事务、表锁设计、支持哈希索引、并发性能较差，并且不支持 TEXT 和 BLOB 列类型。Memory 存储引擎将表中的数据存放在内存中，如果数据库重启或者崩溃，表中的数据都将消失，它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。Memory 存储引擎默认使用哈希索引，而不是我们熟悉的 B+ 树索引。

二、文件

1、查看 MySQL 的参数配置

SHOW VARIABLES

2、错误日志（error log）文件对 MySQL 的启动、运行、关闭过程中进行了记录。

SHOW VARIABLES LIKE 'log_error'

慢查询日志（slow log）文件记录了可能存在问题的 SQL 语句，可根据慢查询日志进行 SQL 语句层面的优化。

# 记录没有使用索引的 SQL 语句
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
# 允许记录到 slow log 且未使用索引的 SQL 语句次数
SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes'
# 大于该时间的 SQL 语句都会被记录下来
SHOW VARIABLES LIKE 'long_query_time';
# 指定慢查询输出的格式（FILE/TABLE）
SHOW VARIABLES LIKE 'log_output';

二进制日志（binary log）记录了对 MySQL 数据库执行更改的所有操作，但是不包括 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改。二进制日志在数据库恢复、主从复制、审计等场景中得到了广泛的应用。

要查看二进制日志文件的内容，必须通过 MySQL 提供的工具 mysqlbinlog。

# 单个二进制文件的最大值
SHOW VARIABLES LIKE 'max_binlog_size';
# 二进制日志缓冲，基于会话，所有未提交的二进制日志都会被缓冲
SHOW VARIABLES LIKE 'binlog_cache_size';
# 二进制日志文件记录缓冲与记录临时文件的次数
SHOW GLOBAL STATUS LIKE 'binlog_cache%';
# 表示每写缓冲多少次就同步到磁盘，该参数的有效值为0 、1、N；
# 0：默认值，事务提交后，将 binlog 日志写入操作系统缓存，不立即刷新到磁盘；
# 1：事务提交后，将 binlog 日志写入操作系统缓存并立即刷新到磁盘，即同步写磁盘；
# N：每写 N 次操作系统缓存就执行一次刷新操作；
SHOW VARIABLES LIKE 'sync_binlog'
# slave 角色配置，从 master 取得 bin_log 日志写入到自己的二进制日志文件中
SHOW VARIABLES LIKE 'log_slave_updates%'
# 动态参数，记录二进制日志文件的格式，STATEMENT/ROW/MIXED，通常设置为 ROW
SHOW VARIABLES LIKE 'binlog_format'

３、因为 MySQL 插件式存储引擎体系结构的关系，MySQL 数据的存储是根据表进行的，每个表都会有与之对应的文件，但不论表采用何种存储引擎，MySQL 都有一个以 frm 为后缀名的文件，这个文件记录了该表的表结构定义。

4、InnoDB 采用了将存储的数据按表空间（tablespace）进行存放的设计，innodb_data_file_path 参数用来设置默认的表空间，所有基于 InnoDB 存储引擎的表数据都会记录到默认的表空间中，若设置了参数 innoDB_file_per_table，每个基于 InnoDB 存储的表都将产生一个独立的表空间，命名规则为：表名.ibd，独立的表空间仅存储该表的数据、索引和插入缓冲 BITMAP 等信息，其余信息（如回滚（undo）信息、插入缓冲索引页、系统事务信息、二次写缓冲（doublewrite buffer）等）还是存放在默认的表空间中。

SHOW VARIABLES LIKE 'innodb_data_file_path';
SHOW VARIABLES LIKE 'innodb_file_per_table';

5、InnoDB 重做（REDO）日志文件对于 InnoDB 存储引擎至关重要，它们记录了 InnoDB 存储引擎的事务日志，记录了关于每个页（Page）的更改的物理情况。重做日志文件跟 binlog 日志文件不同，binlog 日志记录了所有与 MySQL 数据库有关的日志记录，包括 InnoDB、MyISAM、Heap 等其他存储引擎的日志。

6、redo log 称为重做日志，恢复提交事务修改的页操作，用来保证事务的原子性和持久性；undo log 称为回滚日志，帮助回滚行记录到某个特定版本及 MVCC 的功能，用来保证事务的一致性。

三、表

1、表是关于特定实体得数据集合，这也是关系型数据库模型的核心。

2、在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table），可以参考 聚簇索引。因此，InnoDB 存储引擎表总是 B+ 树索引组织的。

3、从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。

常见的段有数据段、索引段、回滚段等。数据段即为 B+ 树的叶子节点，索引段即为 B+ 树的非索引节点；

区是由连续页组成的空间，在任何情况下每个区的大小都为 1MB。

页是 InnoDB 磁盘管理的最小单位，默认每个页的大小是 16K，可以通过参数 innodb_page_size 设置。然后每个页两行数据，所以每行最大 8K 数据。

4、InnoDB 存储引擎和大多数数据库一样，记录是以行的形式存储的，InnoDB 存储引擎提供了 Antelope（Compact、Redundant）、Barracuda（Dynamic、Compressed、FIXED）等格式 来存放行记录数据，可以通过命令 SHOW TABLE STATUS LIKE 'table_name' 来查看当前表使用的行格式。

SHOW GLOBAL VARIABLES LIKE '%FILE_FORMAT%';
SHOW TABLE STATUS LIKE 'table_name';
ALTER TABLE `table_name` ROW_FORMAT = FIXED;

Antelope 存储格式会把每个字段的前 864 个字节存储在 PAGE 里，所以你的字段超过一定数量的话，单行大小就会超过 8K。

Barracuda 存储格式对字段的处理方式是在 PAGE 里头存储一个 20byte 大小的指针，其它全存在溢出区，所以轻易超不了 8K。

Compressed 行记录格式的另一个功能就是，存储在其中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能进行非常有效的存储。

若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的 row_format 是 fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。

若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，即该表的 row_format 是 dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。

5、关系型数据库系统和文件系统的一个不同点是，文件系统一般需要在程序端进行控制以保证存储数据的完整性，而关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，比如主键约束、唯一键约束、外键约束等等（唯一索引是可以允许有 NULL 值的）。

6、在某些设置下，MySQL 数据库允许非法的或不正确的数据插入或更新，如向 EUNM 约束中插入一个非法值，又或者可以在数据库内部将其转化为一个合法的值，如向 NOT NULL 的字段插入一个 NULL 值，MYSQL 数据库会将其更改为 0 再进行插入，因此数据库本身没有对数据的正确性进行约束，只会得到一个 WARNINGS 提示，通过设置参数 sql_mode 的值为 STRICT_TRANS_TABLES 对于输入值的合法性进行约束。

SHOW GLOBAL VARIABLES LIKE 'sql_mode'

7、视图的主要用途之一是被用作一个抽象装置。特别是对于一些应用程序，程序本身不需要关心基表的结构，只需要按照视图定义来取数据或更新数据，因此，视图同时在一定程度上起到一个安全层的作用。

# 查看基表
SELECT * FROM information_schema.TABLES WHERE table_type ='BASE TABLE' AND table_schema = database();
# 查看视图
SELECT * FROM information_schema.VIEWS WHERE table_schema = database();

8、视图是基于基表的一个虚拟表，基于视图的更新操作，其本质都是通过视图的定义来更新基本表。

四、备份和恢复

1、可以根据备份的方法不同将备份分为：

• Hot Backup（热备）：数据库运行中直接备份，对正在运行的数据库操作没有任何的影响；
• Cold Backup（冷备）：数据库停止的情况下复制，一般只需要复制相关的数据库物理文件（.frm、.ibd 等）即可；
• Warm Backup（温备）：数据库运行中进行，会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性；

2、可以根据备份后文件的内容不同将备份分为：

• 逻辑备份：一般是文本文件，内容可读，内容一般由一条条 SQL 语句构成，可以使用 mysqldump 工具完成；
• 裸文件备份：复制数据库的物理文件，既可以是在数据库运行中的复制（如 ibbackup、xtrabackup 这类工具），也可以是在数据库停止运行时直接的数据文件复制；

3、MySQL 数据库本身提供的工具并不支持真正的增量备份，借助 xtrabackup 工具可以完成 InnoDB 存储引擎的增量备份；

4、复制（replication）是 MySQL 数据库提供的一种高可用性能的解决方案，一般用来建立大型的应用。总体来说，replication 的工作原理分为以下三个步骤：

1）主服务器（master）把数据更改记录到二进制日志（binlog）中；

2）从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中；

3）从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性；（从服务器有 2 个线程，一个是 I/O 线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是 SQL 线程，负责执行中继日志）

# 查看主服务器中二进制日志的状态
SHOW MASTER STATUS;
# 查看从服务器中二进制日志的状态（主从服务器上 binlog 日志的偏移量，就可以得知 I/O 线程的延迟）
SHOW SLAVE STATUS;

五、其它

1、OLAP 的应用适用 CPU 密集型的数据库；OLTP 的应用适用于 IO 密集型的数据库；

# 检查当前数据库的运行状态，显示有哪些线程在运行
SHOW FULL PROCESSLIST;

2、内存的大小是最能直接反映数据库的性能，因此，应该在开发应用前预估“活跃”数据库的大小是多少，并以此确定数据库服务器内存的大小。可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓存池的命中率，通常 InnoDB 存储引擎的缓存池的命中率不应该小于 99%；

SHOW GLOBAL STATUS LIKE 'innodb%read%';

3、RAID（Redundant Array of Independent Disks，独立磁盘冗余数组）的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘；

4、sysbench 是一个模块化的、跨平台的多线程基准测试工具，主要用于测试各种不同系统参数下的数据库负载情况；

5、TPC（Transaction Processing Performance Conuncil，事务处理性能协会）是一个用来评价大型数据库系统软硬件性能的非盈利组织。TPC-C 是 TPC 协会制定的，用来测试典型的复杂 OLTP（在线事务处理）系统的性能。tpcc-mysql 是开源的 TPC-C 测试工具，完全遵守 TPC-C 的标准，专用于 MySQL 基准测试；