《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'

3、因为 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;

5、MySQL 数据库主从复制有异步复制、半同步复制和全同步复制的方式。

异步复制:异步复制模式下,主库在接受并处理客户端的写入请求时,直接返回执行结果,不关心从库同步是否成功,这样就会存在上面说的问题,主库崩溃以后,可能有部分操作没有同步到从库,出现数据丢失问题。

半同步复制:在半同步复制模式下,主库需要等待至少一个从库完成同步之后,才完成写操作。主库在执行完客户端提交的事务后,从库将日志写入自己本地的 relay log 之后,会返回一个响应结果给主库,主库确认从库已经同步完成,才会结束本次写操作。相对于异步复制,半同步复制提高了数据的安全性,避免了主库崩溃出现的数据丢失,但是同时也增加了主库写操作的耗时。

全同步复制:全同步复制指的是在多从库的情况下,当主库执行完一个事务,需要等待所有的从库都同步完成以后,才完成本次写操作。全同步复制需要等待所有从库执行完对应的事务,所以整体性能是最差的。

五、其它

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 基准测试;

posted @ 2021-02-25 20:25  JMCui  阅读(91)  评论(1编辑  收藏  举报