MySql技术内 幕：InnoDB存储引擎 读书笔记

书名	《MySql技术内幕：InnoDB存储引擎》
作者	姜承尧
书摘
第一章：MySQL体系结构和存储引擎 定义数据库和实例：  定义数据库和实例 数据库：文件的集合，frm、MYD、MYI、ibd等 实例：是有后台线程以及一个共享内存区组成。操作数据库文件。单进程 配置文件的读取：/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysqetc/my,cnf --> ~/.my,cnf MySQL体系结构： 连接池组件： 管理服务和工具组件： SQL接口组件： 查询分析器组件： 优化器组件： 缓冲组件： 插件式存储引擎：基于表的 物理文件： MySQL存储引擎： InnoDB： 支持事务，面向在线事务处理(OLTP)的应用. 特点：行锁设计、支持外键、支持非锁定读(默认读取操作不会产生锁) 数据存放在逻辑表空间，每个表放到独立的ibd文件 使用多版本控制MVCC，开获得高并发性，实现了4种隔离级别，默认REPEATABLE，同时，使用一种被称为next-key locking的错略避免幻读，提供了插入缓冲、二次写、自适应哈希索引、预读等高性能功能。 采用聚集方式，每张表的存储，按主键顺序存放，没有主键会生成一个6字节的ROWID MyISAM： 不支持事务、表锁。支持全文所有、面向OLAP数据库 缓冲池只缓冲了索引文件， 数据文件：MYD：索引文件：MYI NDB： 集群存储引擎，数据全部放内存，查找快，通过添加节点增加数据库性能 缺点：join连接操作是在mysql数据库层完成的，存在巨大网络开销。 Memory： 数据存放内存，不安全，默认使用哈希索引 速度快，只支持表锁，并发性能差，不支持TEXT、BLOB类型，不存在变长字段，varchar，回浪费空间，常用作数据临时表。 mysql 使用Memory存储时，如果中间结果存在TEXT，等不符合的数据，会将其转换到MyISAM存储引擎表放到磁盘。 Archive： 只支持，insert、select，5.1支持索引，存储引擎使用zlib算法将数据行进行压缩后存储，1:10 适合归档，支持行锁，不支持事务 Federated： 不存放数据，指向一台远程MySQL数据库服务器上的表 Maria： 支持缓存和索引文件，行锁，提供MVCC，事务和非事务安全的选项，为MyISAM进化版。 其他： 连接MySQL： TCP/IP：mysql会先校验查一张权限视图，然后判断是否允许连接Mysql实例。 mysql -u db2-dev -S /tmp/mysql.sock
第二章：InnoDB存储引擎 概述：支持：行锁，MVCC、外键、一致性非锁定读等 版本 InnoDB体系架构： 后台线程： Master Thread 缓冲池中数据异步刷新到磁盘、保证数据的一致性、脏页的刷新、合并插入缓冲、UNDO页的回收 IO Thread：AIO的IO回调线程 write、read、insert buffer、log IO Thread，四个 innodb_read_io_threads 和 innodb_write_io_threads配置 show engine innodb status show variables like '%read%'; Purge Thread： 回收已经使用并分配的undo页。 innodb_purge_threads Page Cleaner Thread： 脏页刷新，减轻Master负担，以及对用户查询查询的阻塞。 innodb_page_cleaners 内存： 缓冲池： 读取页操作时，将页fix到缓冲池中，允许多个缓冲池。 修改：先修改缓冲池的数据，再以一定频率刷新到磁盘。机制：Checkpoint； 配置： innodb_buffer_pool_size innodb_buffer_pool_instances 数量 数据：索引页、数据页、undo、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。 LRU List、Free List 和 Flush List 缓冲区管理：LRU 最近最少使用算法， InnoDB策略：新读的页插入到列表中间5/8处。 参数：innodb_old_blocks_pct, = 37% midpoint为分界线，前面是new数据，后面是old数据 参数：innodb_old_blocks_time，mid的数据多久之内再次读取，会加入到new里面。 可以算取热点数据的百分比，以及热点数据的读取时间，配置参数。合理95%，低于需检查是否全表扫描 LRU列表中的页被修改后，就是脏页。需要checkpoint机制刷新到磁盘 脏页存在于LRU和Flush列表，LRU列表管理缓冲池中页的可用性，Flush列表用来将页刷新到磁盘。 重做日志缓冲：是循环使用的 缓冲池到磁盘的时机： 默认每一秒将缓冲池的重做日志信息刷新到磁盘。 每个事物提交 缓冲池剩余空间小于1/2 innodb_log_buffer_size: 64M Checkpoint技术： 原因：为了解决内存数据丢失，没有刷新到磁盘 解决方式：Write Ahead Log策略：当事务提交时，先写重做日志，在修改页， 解决的问题：确保持久性 缩短数据库恢复时间 缓冲池不够用时，刷新脏页 重做日志不可用时，刷新脏页 版本号：页、重做日志、checkpoint都有 innodb对于checkpoint的规则很复杂 Master Thread：异步，1-10s一次 FLUSH_LRU_LIST：保证LRU列表中又100个空闲页可用现在，这个操作是，Page Cleaner线程在执行。innodb_lru_scan_depth : 1024 页 Async/Sync Flush Checkpoint：重做日志不可用时，重做日志快满的时候75%，mysql 5.6之前会阻塞触发的线程，之后的版本在Page Cleaner线程中执行。 innodb_max_dirty_pages_pct : 75%脏页数据占据比，强制Checkpoint Master Thread 工作方式 InnoDB 1.0x版本之前的Master Thread 最高优先级线程，多个loop组成，主循环，后台循环，刷新循环，暂停循环等。 每秒操作 日志刷新到磁盘没提交事物也算（总是） 合并插入缓冲（可能） 1s内IO次数小于5次，表明压力小可以进行合并插入缓冲 至多刷新100个脏页到磁盘（可能） 缓冲池中的脏页比，默认90% 如果当前没有用户活动，切换到background loop（可能） 每10秒操作 刷新100脏页到磁盘（可能） 10s内IO操作小于200次，IO能力充足 合并至多5个插入缓冲（总是） 将日志缓冲刷新到磁盘（总是） 删除无用的Undo页（总是） 提交事务后的undo页 刷新100个或者10%脏页到磁盘（总是） 超过70%的脏页，刷新100不然刷新10% background loop：空闲或关闭时 删除无用Undo页（总是） 合并20个插入缓冲（总是） 跳回到主循环（总是） 不断刷新100个页直到符合条件（可能跳转到flush loop中完成） InnoDB 1.2x版本之前的Master Thread，的改进 由于硬编码，100个脏页20个插入缓冲等，太慢 磁盘IO吞吐量：innodb_io_capacity：200 刷新数量为：innodb_io_capacity 合并插入缓冲：innodb_io_capacity * 5% 脏页的占比90%太高，太低磁盘压力大 innodb_max_dirty_pages_pct：综合比较定于到了75% 自适应刷新：innodb_adaptive_flushing，每秒刷新脏页的数量，之前是100.系统通过生成重做日志的数量来决定。 full purge操作最多20个undo页。 innodb_purge_batch_size：300 可修改 可通过各个循环进行的比例判断服务器是否压力大 InnoDB 1.2x版本 刷新脏页的操作分离到单独的Page Cleanner Thread。 InnoDB 关键特性 插入缓冲：物理页,性能提升 解决非顺序性的插入带来的索引消耗。 使用条件：辅助索引，且不是唯一索引 操作：对非聚集索引，先判断非聚集索引页是否在缓冲池中，在直接插入，不在先插入inset buffer。数量大时，会进行合并插入缓冲，大大提高了非聚集索引的插入性能。 写密集会占用过多缓冲池内存，默认最大可占到1/2。 配置：ibuf_pool_size_per_max_size: 3 插入缓冲可占用最大缓存比，1/3 升级：change buffer：可对DML操作进行缓冲，INSET、DEL、UP，各自对应一个buffer。 update操作；先标记删除，然后真正删除 innodb_change_buffer_max_size: 25 等于1/4，最大有效值为50。 实现：insert buffer内部是B+树(全局一个树)，每个叶子结点是一个search key 9字节 Space：4表id、marker：1兼容老版本、offset：4偏移量。 插入时，先构造一个key 两次写: 可靠性 问题：redo损坏，数据库宕机，恢复数据需要一个数据副本。 doublewrete：分为两部分 内存中的doublewrete buffer：2M 物理磁盘共享空间中连续的128个页。2个区 操作：对脏页刷新的时候，先复制到内存buffer，然后每次1M的写入磁盘共享空间。然后再同步到磁盘。 自适应哈希索引.  InnoDB会监控表上各索引页的查询，如果发现建立哈希索引可以带来速度提升，就建立。通过缓冲池的B+树页构造而来的。建立速度很快，不需要对整张表结构建立索引，InnoDB会自动给某频率给热点页建立索引。 要求：这个页的连续访问模式必须是一样的。 提升：读写速度2倍。辅助索引的连续操作5倍以上完全自动化。 异步IO:AIO 多个io请求异步，可以合并io请求（连续页的访问） 参数：innodb_use_native_aio ，是否启用本地aio，负责的话会采用代码模拟aio速度会提升75% 使用：脏页的刷新，磁盘的写入都是aio 刷新邻接页 当刷新一个脏页时，InnoDB会检测改页所在区的所有页，如果是脏页，就一起刷新。可以对多个io进行合并 问题：将不怎么脏的临近页刷新后，很快就脏了，固态硬盘的iops比较高，有必要吗？ 参数：innodb_flush_neighbors：1 启动、关闭、恢复、 关闭配置参数：innodb_fast_shutdown：1    0，1，2 0:必须完成所有的full purge和merge insert buffer。并且将所有脏页刷新。可能需要几小时，在进行InnoDB升级时可以设置为0 1：只需要完成缓冲池中的脏页就可以了。 2: 只需要将日志都卸乳日志文件，下次开启会进行恢复操作。 恢复参数：innodb_force_recovery:0  时机：数据页corrupt，宕机，等 0: 进行所有的恢复操作。 1: 会略煎茶道的corrupt页 2: 阻止MasterThread线程的运行，主线程的full puege会造成宕机 3：不进行事物会滚：对表进行改变，人为操作更快 4:   不进行插入缓冲的合并操作 5:  不查看撤销日志（undo）InnoDB存储引擎会将未提交的事物视为已提交。 6:  不进行前滚的操作
第二章：文件 参数文件： 通过：show variables like ‘%xxx%’； 参数类型： 动态参数：运行时可改@@global。静态参数不可改@@session 日志文件： 错误日志：启动运行关闭过程的日志 日志目录：show variables like “%log_error% 二进制文件：binary log  记录所以对数据库的更改操作。 作用： 恢复：point-in-time的恢复