[大数据]数据库优化复习（第二章）

第二章：

存储引擎

MySQL存储引擎实际上是个抽象类，文件访问层的一个抽象接口来定制种文件访问机制，这种访问机制就称为存储引擎，MySQL区别于其他数据库的最重要的特点，就是其插件式的存储引擎接口模块，可插拔存储引擎。

存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。

MySQL官方最主流的存储引擎包括以下几种：

1. MyISAM存储引擎
2. InnoDB存储引擎
3. Memory存储引擎
4. NDB存储引擎
5. Archive存储引擎

MyISAM存储引擎（面向OLAP类数据库应用）

1. 存储限制：256TB
2. 不支持事务，不支持MVCC（多版本并发控制）
3. 锁粒度：表级
4. 支持索引类型：B树索引，全文索引
5. 复制支持，外键不支持，查询缓存支持
6. 访问速度快，有较好的索引优化和数据压缩技术

MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件， MYI用来存放索引文件。

MySQL 5.0之前， MyISAM默认支持的表大小为4GB，MySQL 5.0之后，支持256TB的单表数据。

MyISAM缓冲池只缓存索引文件，而不缓冲数据文件。

InnoDB存储引擎（面向OLTP类数据库应用）

1. 支持事务，支持MVCC（多版本并发控制）
2. 锁粒度：行级
3. 支持索引类型：B树索引，自适应哈希索引、全文索引
4. 复制支持，外键支持，查询缓存支持

MyISAM和InnoDB的区别：

MyISAM 保存有表的总行数，InnoDB 没有保存表的总行数。

MyISAM查询速度比InnoDB快很多。

NDB存储引擎

NDB存储引擎是一个集群存储引擎。

NDB的特点是数据全部放在内存中，因此主键查找的速度极快，是高可用、高性能的集群系统。

但是它的表的连接操作是在MySQL数据库层完成的，不是在存储引擎层完成的。这意味着复杂的连接操作需要巨大的网络开销，因此复杂查询速度会很慢，NDB存储引擎不支持事务。

Memory存储引擎

Memory存储引擎将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。

适合用于存储临时数据的临时表。

虽然Memory存储引擎速度非常快，但在使用上还是有一定的限制。比如只支持表锁，并发性能较差，不支持事务等。Memory存储引擎默认使用哈希索引，而不是B+树索引。

Archive存储引擎

Archive存储引擎只支持INSERT和SELECT操作。使用zlib算法将数据行进行压缩后存储，压缩比一般可达1∶10。

Archive存储引擎非常适合存储归档数据，如日志信息。

Archive存储引擎是事务不安全的，其设计目标主要是提供高速的插入和压缩功能。

引擎修改

转换表的存储引擎，会失去和原存储引擎相关的所有特性。例如，如果将一张InnoDB表转换为MyISAM，然后再转回InnoDB，原InnoDB表上的所有外键将消失。

Innodb存储引擎体系结构

1. 缓冲池
2. change buffer
3. 自适应哈希索引
4. redo log buffer
5. double write

MySQL是通过WAL方式，来保证数据库事务的一致性和持久性。具体而言就是:

1. 修改记录前，一定要先写日志；
2. 事务提交过程中，一定要保证日志先落盘，才能算事务提交完成。

InnoDB的缓冲池

InnoDB引擎使用缓冲池技术来提高数据库的整体性能（速度）。

InnoDB存储引擎有各种缓冲池, 这些缓冲块组成了个大的InnoDB储引擎内存池，主要负责的工作是

1. 维护所有进程／线程需要访问的多个内部数据结构；
2. 缓存磁盘上的数据，方便快速读取，同时在对磁盘文件修改之前进行缓存；
3. 重做日志缓存等。

Innodb引擎中磁盘和内存之间数据交互的基本单位是数据页，默认大小是16KB。

Innodb引擎为每一个缓存页都创建了一个对应的块结构，块结构中存有该页面的表空间编号、页号等信息。

数据页通过LRU最近最少使用算法来进行换进换出操作。

Buffer Pool的相关参数

1. innodb_buffer_pool_size : Buffer Pool的总大小
2. innodb_buffer_pool_instances : Buffer Pool中instance的数量，为了减轻高并发下锁争抢的压力，Buffer Pool分为多个instances。
3. innodb_buffer_pool_chunk_size : chunk的大小，默认为128M，Mysql5.7版本后，Innodb引擎引入了chunk结构。Buffer Pool（扩大时）向操作系统申请一块连续的内存空间是chunk为单位向操作系统申请空间，chunk中存放缓存页的控制块和缓存页，还有管理这些缓存页的链表信息等。
4. innodb_buffer_pool_dump_at_shutdown=ON表示在关闭MySQL时会把内存中的热数据保存在磁盘里ib_buffer_pool文件中。
5. innodb_buffer_pool_load_at_startup=ON表示在启动时会自动加载热数据到Buffer_Pool缓冲池里。这样，始终保待热数据在内存中。

InnoDB的change buffer

二级索引通常是非唯一的，插入顺序很随机，更新删除也都不是在邻近的位置，change buffer避免了很多随机I/O的产生，将多次操作尽量变为少量的I/O操作。

change buffer的相关参数：

1. innodb_change_buffering：缓存所对应的操作。
2. innodb_change_buffer_max_size：用于配置 change buffer在Buffer Pool中所占的最大百分比。

InnoDB的自适应哈希索引

lnnoDB存储引擎会监控对表上二级索引的查找。如果发现某二级索引被频繁访问，二级索引就成为热数据；如果建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应的，即自适应哈希索引。MySQL自动管理，人为无法干预。

自适应哈希索引通过缓冲池的B+Tree构造而来

自适应哈希索引会占用 lnnoDB Buffer Pool。

通过"set global innodb_adaptive_hash_index off/on" 命令来关闭或打开该功能。

InnoDB的redo log buffer

redo log buffer存放将要写入redo log文件的数据。其大小是通过设置。

参数：

1. innodb_flush_log_at_trx_commit，控制redo log flush的频率 (0，1，2，安全性由低到高，性能由高到低)
2. innodb_log_buffer_size，设置redo log buffer的大小。

InnoDB的double write

double write技术的引入是为了提高数据写入的可靠性。相对于随机写操作来说，顺序写入的代价较小。缺点是在新型的SSD存储中重复写入对SSD寿命有较大影响。

参数：

1. innodb_doublewrite=0：关掉double write功能

事务提交

事务提交过程

存储引擎实现事务的通用方式是基于 redo log 和 undo log。redo log 记录事务修改后的数据, undo log 记录事务前的原始数据。

事务执行时实际发生过程

1. 先记录 undo/redo log，确保日志刷到磁盘上持久存储。
2. 更新数据记录，缓存操作并异步刷盘。
3. 提交事务，在 redo log 中写入 commit 记录。

binlog 不在事务存储引擎范围内，所以在提交事务前需要将事务日志持久化到 binlog（更新后，提交前）。

事务提交完后

1、事务提交完后会purge undo段，purge的主要职能是，真正删除物理记录。（在执行delete或update操作时，实际旧记录没有真正删除（类似于is_deleted），只是在记录上打了一个标记，而是在事务提交后，purge线程真正删除）

2、释放锁资源

3、刷redo日志。通过redo日志落盘操作，保证数据库的完整性和一致性；

4、清理保存点列表，每个语句实际都会有一个保存点，用来回滚的。

InnoDB后台线程

InnoDB后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最新数据。

InnoDB主线程

master thread：主要工作是将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲等。

master thread的线程优先级别最高，其内部由几个循环组成。master thread会根据数据运行的状态几个循环之间进行切换。

1. 后台循环
   1. 删除无用Undo页（总是）
   2. 合并一定数量插入缓冲（总是）
   3. 若有用户活动，跳回主循环，否则，跳入刷新循环（总是）
2. 刷新循环
   1. 将一定数量的脏页刷回磁盘（总是）
   2. 跳入暂停循环（总是）
3. 暂停循环
   1. 将Master Thread挂起
   2. 若有事件发生，跳入主循环（总是）

InnoDB后台I/O线程

InnoDB存储引擎中大量使用AIO 异步I/O来处理I/O请求，可以极大地提高数据库的性能。

read thread负责将数据从磁盘加载到Buffer Pool的Page页。

write thread负责将Buffer Pool的dirty page刷新到磁盘。

log thread负责将Log Buffer内容刷新到磁盘。

insert buffer thread负责将Change Buffer内容刷新到磁盘。

参数：可以在配置文件 my.cnf 中设置

1. innodb_read_io_threads
2. Innodb_write_io_threads

InnoDB脏页刷新线程

MySQL5.6前，脏页清理工作由master线程处理。5.6之后是由page cleaner thread实现缓冲池刷脏页的工作。

参数：

1. innodb_page_cleaners：设置脏页刷新线程数。（5.7.4版本后引入了多个page cleaner线程）
2. Innodb_buffer_pool_wait_free：标志脏页有没有成为系统的性能瓶颈。如果innodb_buffer_pool_size够大，就可以让Innodb_buffer_pool_wait_free的值很小，甚至为0。

InnoDB purge线程

purge thread负责回收已经使用并分配的undo页（记录原始数据）。

例外：

1. insert undo log是不需要purge的，因为insert操作只对本事务可见，所以提交事务后就直接删除了。
2. update undo log是delete update操作产生的，后续MVCC可能会用到，所以不能在提交时候删除。它会放入undo log的链表，等待purge最后删除。

当删除和更新数据行时，对数据页中要删除的数据行做标记"deleted"，事务提交速度快；后台线程purge线程对数据页中有 “deleted” 标签的数据行进行真正的删除。

参数：

1. innodb_purge_threads：可以调整并发的purge线程数。