1. InnoDB架构

1.1 内存

  1. 缓冲池：磁盘读取的页放在缓冲池中，下一次读取相同页时若发现在池中可以直接从池中读取，否则再从磁盘上读取。修改时也是先修改池中的页面，然后以一定的频率刷新到磁盘。
  2. 缓存的数据页类型包含：索引页，数据页，undo页，insert buffer，AHI，lock info，data dictionary等等。允许多个缓冲池实例来减少资源竞争。
  3. 缓冲池一般使用LRU算法管理页面。一点优化是，新读到的页不直接放到LRU列表的首部而是放到midpoint。因为若直接放到首部，某些扫描类的SQL操作需要访问很多页，但这些不是活跃的热点数据，可能导致热点数据从LRU中移除。
  4. LRU列表管理缓冲中页的可用性，Free管理空闲页，Flush管理将页刷回磁盘。

1.2 Checkpoint技术

  1. 作用：缩短数据库恢复时间(仅需对Checkpoint的日志进行恢复)；缓冲池不够用时，将脏页刷回磁盘(强制执行Checkpoint)；redo log不可用时，刷新脏页(强制执行Checkpoint)。
  2. Sharp Checkpoint：数据库关闭时将所有脏页刷新到磁盘
  3. Fuzzy Checkpoint：运行时仅刷新部分脏页，提高可用性。
    1. Master Thread Checkpoint：异步以每秒或十秒的速度刷新一定比例的脏页
    2. FLUSH_LRU_LIST Checkpoint：保证LRU有约100个空闲页，否则刷新部分脏页
    3. Async/Sync Flush Checkpoint：redo log不可用时刷新部分脏页
    4. Dirty Page too much Checkpoint：脏页太多，刷新部分脏页，保证缓冲池中有足够空闲页

1.3 Master Thread

  1. 主循环(loop)：每秒一次以及每秒10次的操作
  2. 后台循环(background loop)
  3. 暂停循环(suspend_loop)：挂起，等待事件的发生
  4. 刷新脏页为一个单独的线程：Page Cleaner Thread

1.4 InnoDB关键特性

  1. Insert Buffer:
    1. 对于非聚集索引的插入或更新操作，由于数据页的存放时按主键的顺序，这时就需要随机的访问非聚集索引页，由于随机读取的存在导致插入操作的性能下降。因此引入了Insert Buffef。
    2. Insert Buffer是指对于每一次的非聚集索引的插入或者更新操作，不是每一次直接插入到索引页中，而是先判断插入到非聚集索引页是否在缓冲池中，若在，直接插入，若不在，先放在一个Insert Buffer对象中，然后以一定的频率进行merge。
    3. 可能出现的问题：程序进行了大量的插入操作，涉及到了非聚集索引，这时数据库宕机，这时会有很多的Insert Buffer没有合并到实际的索引中去，使得恢复时需要很长的时间。
    4. Change Buffer包含Insert Buffer, Delete Buffer, Update Buffer。
  2. Double Write Buffer
    1. InnoDB 16KB的页在刷到磁盘上时，可能只写了一部分，就发生了宕机（称为partial page write），这种情况是无法通过redo恢复对，因为页已经发生了损坏，redo对损坏的页进行重做是没有意义的，因为redo不是记录整个页的所有数据，而是记录这个页的哪部分数据被修改。
    2. Double Write Buffer由两部分组成，一部分是内存中的double write buffer（2MB），另一个部分是磁盘上共享表空间中连续的128个页，即2个区(extent)，也为2MB。在对脏页刷新时，并不直接写磁盘，而是先memcpy到内存中的dwb，之后dwb分两次顺序写到共享表空间的磁盘上，立即调用fsync，同步磁盘。然后再将磁盘上buffer中的页写到各个表空间文件中，此时的写入是离散的。
    3. 如果操作系统在将页写到磁盘的过程中发生了崩溃，恢复过程中，引擎可以从共享表空间中的dwb中找到该页的一个副本，将其复制到表空间文件中再应用redo log。
  3. Adaptive Hash Index
    1. 哈希索引一般仅需一次查找就可以定位数据，而B+树一般需要好几次。InnoDB会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。
    2. AHI是通过缓冲池的B+树构造而来，因而建立速度很快(不是从数据)，而且不需要对整张表建立哈希索引。引擎会自动对根据访问的频率和模式自动地为某些热点页建立哈希索引。
    3. 要求是对某个页的连续访问模式必须是一样的。查询条件一样。
  4. Asynchronous IO (AIO)
    1. 同步IO是说每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。而如果用户发出的是一条索引扫描的查询，那么可能需要扫描多个页，需要进行多次的IO操作，在每扫描一个并等待其完成后再进行下一次扫描，这是没有必要的，用户可以在一次IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等到所有IO操作的完成，这就是AIO。
    2. AIO可以进行IO merge，即将多个IO合并为一个IO。例如某三个IO的请求是连续的页，每个16Kb，那么可以只进行一次IO，一次读取48KB。
  5. Flush Neighbor Page
    1. 刷新一个脏页时，引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新，这样可以通过AIO将多个IO写入合并为一个IO操作，在传统机械磁盘上有优势。
    2. 问题：是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变为脏页？固态硬盘有着较高的IOPS，可能不再需要这个特性？

2. 文件

2.1 参数文件

  1. 可以通过show variables like 'innodb_buffer'; 查看数据库中的所有参数。
  2. 动态参数可以实例运行中修改，静态参数不可以。global和session表示修改是基于当前会话还是整个实例周期。
  3. MySQL实例本身不会修改参数文件，因此下一次启动时参数还是为修改前的值，要想永久修改，需要修改参数文件。

2.2 日志文件

  1. 错误日志(error log): 记录MySQL启动、运行、关闭，记录了所有的错误信息，以及一些警告和正确的信息，可以通过show variables like 'log_error'; 来定位该文件。
  2. 慢查询日志(slow query log): 在MySQL启动设置一个阈值，将运行时间大于该阈值的SQL语句会记录到慢查询日志文件中，可以用来查看是否有SQL语句需要优化。也可以根据IO次数记录。现在慢查询日志可以通过表slow_log进行查询。详细内容看书P67。
  3. 查询日志(log): 记录所有对MySQL数据库请求对信息，无论是否正确执行。可以将查询日志放在general_log表中进行查询。类似slow_log。
  4. 二进制日志(binlog): 记录对数据库执行更改的所有操作，不包括select，show这类操作，另外若操作本身没有导致数据库变化（执行删除，但是没有数据被删除），可能也会写入二进制日志。
    • 恢复：某些数据恢复需要二进制日志，如一个数据库全备文件恢复后，用户可以通过二进制日志进行PIT的恢复
    • 复制：通过复制和执行二进制日志使另一台数据库(slave)与该台(master)进行实时同步。
    • 审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库注入的攻击。 参数log-bin[=name]开启binlog。详情看书P73。

2.3 socket文件

  • 查看show variables like 'socket';

2.4 pid文件

  • 记录进程的pid：show variables like 'pid_file';

2.5 表结构定义文件

  • 记录表以及视图的表结构定义，后缀名为frm。

2.6 InnoDB存储引擎文件

  1. 表空间文件：数据按表空间存储。默认有一个10MB的ibdata1文件，可以通过innodb_data_file_path设置，如innodb_data_file_path = /db/ibdata1:2000M; /dr2/db/ibdata2:2000M:autoextend,autoextend表示用完了该文件可以继续增长。也可以设置innodb_file_per_table，这样每个表会产生一个独立的表空间，命名为：表名.ibd。单独的表空间仅存储该表的数据、索引和插入缓冲BITMAN等信息，其余信息如undo信息、插入缓冲索引页、系统事务信息、double write buffer等还是存放在默认表空间中。
  2. Redo log文件：默认情况下数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。写日志时先写日志1，再写日志2，然后又回到日志1，如此循环。可以通过show variables like "inndob%log'; 查看配置信息。redo log文件不能设置太大，太大恢复时可能需要很长的时间，也不能太小，太小看你导致一个事物的日志需要多次切换日志文件，另外，太小会频繁的发生async checkpoint，导致性能的抖动。
  3. binlog和redo log的区别：
    • binlog会记录所有MySQL有关的日志记录，InnoDB的redo log只记录有关该引擎的事务日志。
    • binlog记录的是关于一个事务的具体操作内容，是逻辑日志。redo log记录的是关于每个页的更改的物理情况。
    • binlog仅在事务提交前进行提交，只写磁盘一次，不论事务多大。而在事务进行过程中，会不断有redo entry写到redo log中。
  4. Redo entry包含四个部分：
    • redo_log_type: 1字节，表示redo log的类型
    • space：表空间ID
    • page_no：页的偏移量
    • redo_log_body：数据部分，恢复时需要调用相应函数进行解析
    • Redo log从buffer写到磁盘时按照扇区大小512字节写，故而必定成功，不需要double write。
  5. 参数innodb_flush_log_at_trx_commit控制redo log从buffer写到磁盘的条件，取值为0，1，2。
    • 0：提交事务时，不将redo log从buffer写到磁盘中，而是等待主线程每秒的刷新。
    • 1：提交事务时，将log从buffer写到磁盘，并调用fsync，刷新操作系统的缓存。
    • 2：提交事务时，将log从buffer写到磁盘，但不会调用fsync，因此日志可能在操作系统的缓存中而没有写到磁盘文件中。宕机时如果操作系统及服务器没有宕机，那么恢复数据不会丢失，否则可能会丢失数据。

3. 表

3.1 索引组织表

  1. 表按照主键的顺序存储。若没有指定主键，判断表中是否有非空的唯一索引，有的话即为主键，否则自动创建一个6字节大小的指针作为主键。

3.2 逻辑存储结构

  1. 所有数据存放在一个表空间中，表空间由段(segment)，区(extent)，页(page)组成。
  2. segmeng包含数据段，索引段，回滚段。InnoDB表是索引组织的，所以数据段为B+树的叶子结点，索引段为非叶子结点。
  3. extent总是为1M，默认页大小为16KB，故而一个extent默认包含64个页。此外，segment刚开始时，会先用32个页大小的碎片页来存放数据，然后才是64个连续页的申请。这样目的是对于一些小表或者undo段，可以在开始时申请较少的空间，节省磁盘容量的开销。
  4. page是InnoDB磁盘管理最小单位。常见类型有：数据页(B-tree Node)，undo页(undo log page)，系统页(system page)，事务数据页(transaction system page)，插入缓冲位图页(Insert Buffer Bitmap)，插入缓冲空闲列表页(Insert Buffer Free List)，未压缩的二进制大对象页(Uncompressed BLOB Page)，压缩的二进制大对象页(commpressed BLOB Page)
  5. 行存储：Compact格式，查看P104以及P117。若发生行溢出，数据会被保存在BLOB页中。

3.3 物理数据页结构

自行查看P120页

3.4 约束，视图自行查看

  1. 视图是虚拟的表，不包含数据，真实数据还是在原来的表中。

3.5 分区

  1. 逻辑上，只有一个表或一个索引，物理上可能包含多个分区。水平分区按行，垂直分区按列(不支持)，局部分区包含索引和数据，全局分区仅数据分区，索引在一个对象中。若表有主键或唯一索引，分区列必须是他们的一部分。
  2. RANGE：常用于日期列分区。根据年份月份等来存储数据。方便管理以及加快查询。
  3. LIST：数据时离散的。
  4. HASH：将数据均匀分布到预先定义的各个分区中。
  5. KEY：类似HASH，但使用MySQL提供的函数进行分区。
  6. OLAP场景分区有可能提升查询性能，例如分析某一年的数据。但OLTP的数据分区要慎重，因为有可能带来更多的IO，比如查询某个值一个分区可能只需要3次(3层B+树)，若有10个分区，每个为2层，也需要20次IO，影响性能。

4. 索引和算法

4.1 B+树

  1. B+树索引只能找到被查找数据行所在的页，然后数据库将页读入内存，在内存中进行查找，最后得到数据。
  2. 平衡二叉树的性能是比较高的，但不是最高的，只是接近最高性能。最好的性能需要建立一颗最优二叉树，但最优二叉树的建立和维护需要大量的操作。故而一般平衡二叉树即可。
  3. B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。B+树的高度一般都在2～4层，2～4次的IO意味着查询时间只需0.02～0.04s。
  4. B+树索引按照每张表的主键构造一颗B+树，叶子结点存放的即为整张表的行记录数据，即叶子结点为数据页，故而索引组织表中数据也是索引的一部分，数据页通过双向链表来连接。聚集索引的存储并不是物理上连续的，而是逻辑上连续的，这其中有亮点：一是页通过双向链表连接，页按照主键的顺序排序，二是每个页中的记录也是通过双向链表维护的，物理存储上可以不按照主键存储。聚集索引对于排序查找和范围查找很快，实际上不用排序。
  5. 辅助索引的叶子结点不包含行记录的全部数据，叶子结点除了包含键值外，每个叶子结点的索引行还包含了一个bookmark，其为相应行数据的聚集索引键。辅助索引查找时会先得到指向主键索引的主键，然后再通过主键索引找到相应的行数据所在的页，中间多了一个回表的过程。（堆表和索引组织表的优劣分析参见P97）
  6. 索引创建：COPY方式先创建一张临时表，然后将原表的数据导入到临时表，删除原表，重命名临时表。对于大表这可能很慢。后面支持Online DDL操作，原理是将DML操作的日志写到一个缓存中，待索引创建再将重做应用到表上。
  7. Cardinality是通过采样的方式得到的，随机选取8个叶子结点统计。
  8. Index Condition Pushdown是指索引查找时进行过滤，而非索引查找结束后再过滤，这样可以更早的减少数据量。

4.2 哈希

  1. InnoDB采用bucket chain的方式，chain指针指向相同哈希函数值的页，页转换为自然数通过space_id<<20+space_id+offset 的方式。
  2. AHI是引擎自己控制的，无法外部干预。

4.3 全文检索(Full Text Search)

  1. 使用倒排索引(inverted index)实现。在辅助表(auxiliary table)中存储单词与单词自身在一个或多个文档所在位置之间的映射。通常利用关联数组实现。
  2. Auxiliary table是持久化的表，存放于磁盘上。InnoDB还设有FTS index cache，是红黑树结构，提高全文检索的性能。
  3. 文档中分词的插入操作是在事务提交时完成，但是对于删除，不会删除AT表中的记录，而是删除FTS IC中的记录，对于AT表中被删除的记录，引擎会记录其FTS DOC ID，保存在Delete auxiliary table中。若要手工彻底删除索引中被删除的记录，可执行OPTIMIZE TABLE。

5. 锁

5.1 lock和latch

  1. 锁是数据库系统区别于文件系统的一个关键特性，主要是用于管理对共享资源的并发访问，提供数据的完整性和一致性。lock和latch有着截然不同的含义，latch一般称为闩锁(轻量级的锁)，其要求锁定的时间非常短，InnoDB中，latch分为mutex和rwlock，目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。lock的对象是事务，用来锁定的是数据库中的对象，如表，页，行，并且一般lock的对象在事务commit或rollback后进行释放(不同事务隔离级别释放的时间可能不同)，此外lock是有死锁机制的。

5.2 InnoDB中的lock

  1. InnoDB实现了两种行级锁：
    • 共享锁S lock：允许事务读一行数据
    • 排他锁X lock：允许事务更新或删除一行数据
    • X锁和任何的锁不兼容，S锁仅与S锁兼容。
  2. InnoDB实现了意向锁(Intention Lock)，意向锁将锁定的对象分为多个层次，若看成一棵树，那么对最下层的对象上锁，首先需要对粗粒度对对象上锁，如对某一行上X锁，分别需要对数据库，表，页上意向锁IX，最后对记录上X锁，任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。
    • 意向共享锁IS lock：事务想要获得一张表中某几行的共享锁
    • 意向排他锁IX lock：事务想要获得一张表中某几行的排他锁
    • InnoDB支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫描以外的任何请求
  3. 一致性非锁定读是指如果读取的行正在执行delete或update操作，这时读取不会去等待行上锁的释放，而是去读取行的一个快照。事务隔离级别READ COMMITED和REPEATED READ(默认级别)下，InnoBD使用一致性非锁定读。
  4. 自增长与锁
  5. 外键和锁

5.3 锁算法

  1. InnoDB包含3种行锁算法：
    • Record lock：单个行记录上的锁
    • Gap lock：间隙锁，锁定一个范围，但不包含记录本身
    • Next-Key lock：Gap lock+Record lock，锁定一个范围并且锁定记录本身
  2. InnoDB默认采用Next-Key的算法，但是当查询的索引含有唯一属性时，InnoDB会将Next-Key降级为Record Lock。
  3. Phantom Problem（幻想问题）是指在同一事务下，连续执行两次同样的SQL可能产生不同的结果，第二次SQL可能返回之前不存在的行。InnoDB采用Next-Key Locking的算法来避免该问题。

5.4 锁问题

  1. 脏读：脏数据不同于脏页，脏页指的是缓冲池中已经被修改的页但是还没刷新到磁盘中，而脏数据是指事务对缓冲池中行记录的修改，并且还没提交。对于脏页的读取是正常的，不影响数据的一致性，并且因为脏页的刷新是异步的，不影响数据的可用性，并且可以提高性能。而脏数据是指未提交的数据，如果读到脏数据，说明一个事务可以读到另一个事务未提交的数据，这违反了数据库的隔离性。脏读在READ UNCOMMITTED隔离级别下会产生。
  2. 不可重复读：指在一个事务内多次读取同一数据，但是在这期间，另一个事务也访问该数据并进行了DML操作，使得在第一个事务内多次读到的数据可能是不一样的。和脏读的区别是，这里读到的是提交的数据，但是违反了数据库事务一致性的要求。READ COMMITED级别下会有这个问题，但是该问题是可以接收的，因为读到的是已提交的数据，因此很多厂商默认的级别均为该级别，该问题在InnoDB中即上面提到的幻想问题，InnoDB采用Next-Key方法来避免该问题。因此InnoDB的默认级别是READ REPEATABLE。
  3. 丢失更新问题：参考P274

5.5 死锁

  1. 死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。最简单解决死锁问题的方式是回滚，但这会导致性能下降，甚至事务不能进行，另一种方法是超时，即当等待时间超过阈值时回滚，根据FIFO的顺序选择回滚对象，但若超时的事务所占权重较大，占用较多undo log，就不太合适。
  2. 数据库普遍采用wait-for-graph的方式进行死锁检测。通过两个链表锁的信息链表和事务等待链表来构造出一张图，若存在回路则代表存在死锁。InnoDB通常回滚undo量最小的事务。

6. 事务

6.1 事务概述

  1. 事务也是数据库区别文件系统的重要特效之一。ACID四个特性。
    • 原子性Atomicity：事务中的操作要么成功要么失败
    • 一致性Consistency：事务将数据库从一种状态转变为下一种一致性状态，转账前两个账户总金额是一万，转之后和仍然是一万
    • 隔离性Isolation：事务之间不影响
    • 持久性Durability：事务一旦提交，结果是永久性的。保证的是高可靠性而不是高可用性。
    • 高可靠和高可用：可靠性是指服务连续无故障运行的时间，可用性定义为在足够长的时间里，服务可用的时间，举两个极端的例子：一个系统可靠性很高，可以无故障运行10年，但是一旦崩溃恢复需要一年的时间，那么可用性只有90%，另外一个服务可靠性很差，每10s就会宕机一次，但是恢复仅需1ms，那么可用性为99.9%。

6.2 事务类型：

扁平事务，带有保存点的扁平事务，链事务，嵌套事务，分布式事务

6.3 事务实现

  1. 隔离性通过锁实现，redo log保证事务的原子性和持久性，undo log保证事务的一致性。undo不算是redo的逆过程，两者都可视为一种恢复操作，redo恢复提交事务的修改的页操作，undo回滚行记录到某个特定版本，两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作，undo是逻辑日志，根据每行记录进行记录。redo是顺序写的，undo是需要进行随机读写的。
  2. 日志的格式，看书P297
  3. LSN：日志序列号，表示事务写入redo log的字节的总量。不仅redo log中记录LSN，页中也会记录LSN，表示该页最后刷新时LSN的大小。checkpoint表示已经刷新到磁盘上LSN。恢复时仅需恢复checkpoint开始的日志部分。
  4. undo：记录回滚。undo存放在数据库中一个特殊段undo segment中，其位于共享表空间中。undo是逻辑日志，因此只是将数据库逻辑的恢复到原来的样子，所有的修改都被逻辑的取消了，但是数据结构和页本身在回滚之后可能大不相同，这是因为在并发系统中，可能有多个事务，一个事务页中某几条记录，另一个事务修改同一个页中另外几条记录，因此不能将一个页回滚到事务开始时的样子，这样会影响其他事务的工作。
  5. undo的另一个作用是MVCC，用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。最后一个重要的点是，undo也会产生redo log，因为undo也需要持久性的保护。
  6. 事务提交时，将undo log放入列表中，以供之后的purge操作，判断undo所在的页是否可以重用，若可以分配给下个事务使用。事务提交后不能马上删除undo log以及所在的页，因为可能还有其他的事务需要通过undo来得到行记录之前的版本，故事务提交时放入一个链表中，是否可以最终删除由Purge线程确定。undo页是可重用的，因此可能存放不同事务的undo log，因此Purge操作涉及磁盘的离散随机读操作，是一个比较缓慢的过程。
  7. Purge：delete和update操作可能并不能直接删除原有数据，因为InnoDB支持MVCC，所以记录不能在提交时立即处理，这时其他的事务可能正在引用这行，故InnoDB需要保存记录之前的版本。是否可以删除由purge来进行判断。若该行事务已不被任何其他事务引用，则可以真正delete。
  8. Group commit可以在一次fsync的过程中刷新多个事务日志，查看P319。

6.4 事务语句

  1. Start transaction或者set autocommit=0
  2. QPS：question per second
  3. TPS：transaction per second

6.5 分布式事务

  1. XA事务由一个或多个RM(资源管理器)，一个TM(事务管理器)以及一个AP(应用程序)组成。
    • RM：提供访问事务资源的方法，通常一个数据库就是一个RM
    • PM：协调参与全局事务中的各个事务，需要和参与全局事务的RM进行通信。
    • AP：定义事务的边界，指定全局事务的操作。
    • MySQL数据库的分布式事务中，RM就是MySQL数据库，PM是连接MySQL的客户端。
  2. 两阶段提交：第一阶段所有参与全局事务的节点开始准备(prepare)，告诉TM他们准备好提交了，第二阶段TM告诉RM执行ROLLBACCK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。
  3. 上述一般为外部XA事务，MySQL还有内部XA事务，常见的是binlog与InnoDB的redo log之间。

6.6 事务的注意点

看书

6.7 备份与恢复

  • 热备Hot Backup是指数据库运行中直接备份，也称Online Backup
  • 冷备Cold Backup是指数据库停止下备份，简单仅需复制相关文件即可
  • 温备Warm Backup是指数据库运行中备份，但是会对数据库有影响，如加一个全局读锁来保证备份数据一致性
  • 逻辑备份是指备份出文件内容一般是可读的，一般由一条条SQL语句组成。
  • 裸文件备份是指复制数据库的物理文件。
  • 完全备份是指数据库进行一个完整的备份。
  • 增量备份是指在上次完全备份的基础上，对于更改的数据进行备份。
  • 日志备份是指MySQL的binlog备份，通过对一个完全备份进行binlog的replay来完成数据库的point-in-time的恢复工作。

results matching ""

    No results matching ""