innodb笔记

innodb
1.purge thread：事务提交后所使用的undo log页不需要，所以用purge thread来回收已经分配使用的undo log页。在innodb 1.1版本之前，由master thread完成，从1.1版本开始放在单独的线程进行
通过innodb_purge_threads=1启用，1.2版本可以设置最大值为4个线程 page clean thread是将之前版本中刷新脏页的线程放到单独线程来完成，减轻master thread及查询阻塞
2.缓冲池中的数据页类型：索引页、数据页、插入缓冲、自适应哈希索引、innodb存储锁信息、数据字典
3.多实例缓冲池可以减少数据库内部的资源竞争、增加数据库并发处理能力(innodb_buffer_pool_instances)
4.如果预计需要大表全表扫描，我们可以降低innodb_old_blocks_pct的值，以防止将热数据刷新(默认为37)
5.当数据库刚启动时，LRU列是空的，这时页都在Free列中，当需要从缓冲池中分页时，首先从Free列表中查找是否还有可用的空闲页，若有则将从Free中删除，放到LRU列中，否则
根据LRU算法淘汰末尾页，当页从old到new时称为page made yong
6.重做日志(redo log):重做日志缓冲:innodb_log_buffer_size(8M)，innodb_log_files_size重做日志文件大小，innodb_log_file_group(=2)重做日志组，三种情况会刷新重做日志
到缓冲到外部磁盘重做日志文件中:a)master thread每一秒将重做日志缓冲刷新到重做日志文件中 b)每个事物提交时 c)当重做日志缓冲池剩余空间大小小于1/2时
7.checkpoint:每当事务提交时，先写重做日志，再修改页，当数据库宕机时而导致数据丢失时，完全可以通过重做日志来恢复，数据库不需要重做所有的日志，因为checkpoint之前
的页已经刷新到磁盘了，故只需对checkpoint之后的重做日志进行恢复
8.当缓存池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行checkpoint，将脏页也就是页的新版本刷回磁盘
9.对innodb而言是通过LSN来标记版本的，LSN是8字节的数字，单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。通过show engine innodb status\G;
10.两张checkpoint:Sharp Checkpoint;Fuzzy Checkpoint 前者发生在数据库关闭时将所有的脏页刷新回磁盘，innodb_fast_shutdown=1；后者只在数据库运行时只刷新一部分脏页
a)Master Thread Checkpoint b)FLUSH_LRU_LIST Checkpoint c)Async/Sync Flush Checkpoint d)Dirty Page too much Checkpoint 对于b种情况，是因为innodb需要保证LRU
中至少有100个空闲页可以使用，在1.1之前，需要检查LRU列表中是否有足够的可用空间操作发生在用户查询中，会阻塞用户查询操作，如果没有100个可用的页，那么innodb会
根据LRU算法淘汰末尾页，如果这些页有脏页则需要进行Checkpoint,1.2开始放在单独的线程中，可以通过innodb_lru_scan_depth控制LRU表中可用页的数量，默认1024
11.Async/Sync FLUSH Checkpoint指重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，此时脏页是从脏页列表中选取的，若将已经写入到重做日志的LSN记为redo_lsn
,将已经刷新回磁盘最新页的LSN记为checkpoint_lsn,则定义:checkpoint_age=redo_lsn-checkpoint_lsn async_water_mark=75%*total_redo_log_file_size sync_water_mark=90%*total_redo_log_file_size
当checkpoint_age<async_water_mark,不需要刷新任何页到磁盘，当async_water_mark<checkpoint_age<sync_water_mark，除非Async FLUSH，从Flush列表中刷新足够的脏页
回磁盘，使得满足checkpoint_age<async_water_mark,当checkpoint_age>sync_water_mark,这种情况很少发生，除非设置的重做日志文件太小，此时触发Async Flush操作，从
Flush列表中刷新足够的脏页回磁盘，使得刷新满足checkpoint_age<async_water_mark,在1.2版本之前，Async会阻塞发现问题的线程，Sync操作会阻塞所有用户的查询操作，1.2开始放到单独线程进行
12.Dirty Page too muc,当脏页数量太多，会导致innodb强制执行checkpoint，通过innodb_max_dirty_pages_pct设置，默认为75%
Master Thread工作方式
13.master thread具有最高优先级别，内部由多个循环组成:主循环(loop)、后台循环(backgroup loop)、刷新循环(flush loop)、暂停循环(suspend loop)
14.每秒一次的操作包括:日志缓冲刷新到磁盘，即使这个事务还没提交(总是) 合并插入缓冲(可能) 至多刷新100个innodb的缓冲池中的脏页到磁盘(可能) 如果当前没有用户活动，则切换到background loop(可能)
15.即使某个事务还没提交，innodb存储引擎仍然每秒会将重做日志缓冲中的内容刷新到重做日志文件。解释了为什么再大的事务提交的时间也很短
16.合并插入缓冲也不是每秒都会发生的，innodb会判断当前一秒内发生的IO次数是否小于5，如果小于5，innodb认为当前IO压力很小，可以合并插入缓冲操作
17.每10秒操作:刷新100个脏页到磁盘(可能的情况下) 合并至多5个插入缓冲(总是) 将日志缓冲刷新到磁盘(总是) 删除无用的undo页(总是) 刷新100个或10个脏页到磁盘(总是)
在以上过程中，innodb会判断过去10秒内磁盘IO操作是否小于200次，如果是，则刷新脏页到磁盘，接着innodb会合并插入缓冲。然后再进行一次将日志缓冲刷新到磁盘，之后会
进一步执行full purge操作，删除无用的undo页，每次最多尝试回收20个undo页
18.backgroup loop:若当前没有用户或者数据库关闭时，就会切换到这个循环。执行:删除无用的undo页(总是) 合并20个插入缓冲(总是) 跳回到主循环(总是) 不断刷新100个页直到符合条件(可能跳转到flush loop完成)
19.若flush loop也没事做了，会切换到suspend_loop,将master thread挂起，等待事件的发生。如果用户enable了innodb存储引擎却没有innodb表，master thread总是处于挂起状态
20.在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值的5% 在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity
21.innodb_adaptive_flushing(自适应地刷新)：原来的规则是脏页所占的比例小于innodb_max_dirty_pages_pct时，不刷新脏页；大于innodb_max_dirty_pages_pct时，刷新100个脏页
引入innodb_adaptive_flushing参数，innodb会通过buf_flush_get_desired_flush_rate函数判断需要刷新脏页最合适的数量，其是通过判断重做日志的速度来决定的，当小于innodb_max_dirty_page_pct时，也会刷新部分脏页
22.之前每次进行full purge操作是，最多回收20个undo页，此后引入了参数innodb_purge_batch_size，来控制每次full purge回收的undo页数量，默认为20，可动态调整
innodb1.2
插入缓冲 两次写 自适应哈希索引 异步IO 刷新邻接页
23.插入缓冲：insert buffer(物理页的一个组成部分) 主键索引插入一般是顺序插入，非聚集索引是离散访问，由于是随机读取的存在而导致了插入操作性能下降
24.某些情况下辅助索引依然插入依然是顺序的，比如按时间字段
25.对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，若在则直接插入，若不在则先放入insert buffer中。需要
满足两个条件：索引是辅助索引 索引不是唯一的
26.change buffer从1.0版本开始，innodb可以对DML操作----insert、delete、update都进行缓冲，分别是:insert buffer、delete buffer、purge buffer(只适应于非聚集索引)
可以他通过innodb_change_buffering来开启各种buffer:insert、purge、changes、all、none; chenges表示启用inserts和deletes
27.从1.2开始通过参数innodb_change_buffer_max_size来控制change buffer最大使用内存数量 默认为25，最大值为50
28.全局只有一颗B+树，负责对所有表的辅助索引进行insert buffer，而这棵树存放在共享表空间中，默认就是在ibdata1中。因此，试图通过独立表空间ibd文件恢复表中数据时，
往往会导致check table失败，这是因为表的辅助索引中的数据可能还在insert buffer中，也就是共享表空间中，所以通过ibd文件恢复后还需进行repair table重建表上所有的辅助索引
29.merge insert buffer的操作可能发生在以下几种情况下:辅助索引页被读取到缓冲池时 insert buffer、bitmap页追踪到该辅助索引页已无可用空间时、master thread
对于第一种情况当辅助索引被读取到缓冲池中，例如执行政策的select查询操作，这是需要检查insert buffer bitmap页，然后确认该辅助索引页是否有记录存放在insert buffer
树种，若有则将insert buffer B+树中该记录插入到该辅助索引页中，所以多次对该页操作通过一次合并到了原有的辅助索引中
30.insert buffer bitmap页用来追踪每一个辅助索引页的可用空间，并至少有1/32页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于1/32页，则会强制进行一个合并操作
即强制读取辅助索引页，将insert buffer B+树中该页的记录及待插入的记录插入到辅助索引页中
两次写
insert bufer带来了性能上的提升，doublewrite带给innodb的是数据页的可靠性
31.当数据库宕机时，innodb可能正在写入某个页，可能只写了4K，这种情况称为部分写失效，可以通过重做日志恢复，但重做日志记录的是对页的物理操作，如偏移量800，写
‘aaa'记录。如果这个页本身已经发生损坏再对其重做是没有意义的，在应用重做日志之前，用户需要一个副本，当写入失效时，先通过副本还原该页，再进行重做
32.doublewrite由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，2个区(extent)，大小为2MB。在对缓冲池
的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制地写入共享表空间磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题，在这个过程中
因为doublewrite页是连续的，因此这个过程是顺序写的，开销不是很大，完成doublewrite页的写入后，再将doublewrite buffer汇总的页写入各个表空间文件中，此时是离散的，通过show global status like 'innodb_dblwr%'\G;
33.可以通过skip_innodb_doublewrite启用来禁止使用doublewrite功能，可能会发送部分写失效的问题。(ZFS文件系统自身提供了部分写失效的防范机制，所以可以启用此参数)
自适应哈希索引(AHI)
哈希是一种非常快的查找方法，innodb会监控对表上各索引的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引。AHI是通过缓冲池的B+树页构造的，因此建立速度很快
且不需要对整张表构建哈希索引。innodb会自动根据访问的频率和模式来自动为某些热点页建立哈希索引
34.哈希索引只能通过等值条件来搜索，不能搜索范围查询。可以通过innodb_adaptive_hash_index来启用或禁止(条件：对这个页的连续访问模式必须是一样的，比如对a,b：select *
from table where a=xxx 和select * from table where a=xxx and b=xxx,如果交替执行这两个sql，是不会建立哈希索引的)
异步IO
为了提高磁盘操作新能，当前的数据库系统都采用异步IO的方式来处理磁盘操作，Sync IO即每进行一次IO操作需要等待此次操作结束才能继续接下来的操作
35.AIO的另一个优势是可以进行IO Merge操作，也就是将多个IO合并为1个IO，这样可以提高IOPS性能
36.在1.1之前，AIO通过Innodb的代码来模拟实现，从1.1开始提供了内核级别AIO的支持，称为Native AIO，在编译mysql时，需要libaio库会吃
37.通过innodb_use_native_aio来控制是否启用Native AIO，linux默认为ON，脏页的刷新全部由AIO完成
刷新邻近页:
原理：当刷新一个脏页时，innodb会检测该页所在区(extent)的所有页，如果是脏页那么进行一起刷新。好处是通过AIO可以将多个IO写入操作合并为一个IO操作，但需要考虑
可能将不怎么脏的页刷新，而该页之后又会很快变成脏页 固态硬盘有着较高的IOPS，是否需要这个特性 在1.2引入了innodb_flush_neighbors用来控制这个参数
启动、关闭与恢复
在关闭时，参数innodb_fast_shutdown影响着表的存储引擎为innodb的行为，该参数可取值为0、1、2，默认为1。0表示在数据库关闭时，innodb需要完成所有的full purge和merge
insert buffer，并且将所有脏页刷新到磁盘，如果在升级innodb，必须设置为0，然后再关闭数据库。 1表示不需要完成full purge和merge insert buffer操作，在缓存池中的脏页还是会刷新到磁盘
2表示不完成full purge和mergr insert buffer操作，也不将缓冲池的数据脏页写回磁盘，而是将日志都写入日志文件，这样不会丢失任何事物，下次启动时进行恢复

文件
参数文件、日志文件(错误日志、二进制日志、慢查询日志、查询日志等)、socket文件、pid文件、mysql表结构文件、存储引擎文件
1.log_slow_queries:慢查询日志 long_query_time慢查询日志时间节点，从5.1开始支持微妙
2.log_queries_not_using_indexes:如果sql没用到索引，mysql会将这条sql语句记录到慢查询日志
3.5.6.5开始新增了参数:log_throttle_queries_not_using_index表示每分钟允许记录到slow log的且为使用索引的sql语句次数，默认为0
4.mysqldumpslow:希望得到执行时间最长的10条语句:mysqldumpslow -s -al -n 10 slow.log 从5.1开始将其放在mysql.slow_log表中，可以将log_output设置为TABLE将其记录到表中(可以将其改为MyISAM表)
5.可以通过long_query_io将超过指定逻辑IO次数的SQL语句记录到slow log中，默认为100，为了兼容原版mysql，还添加了slow_query_type,可选值为:0不将SQL语句记录到slow log
1表示根据运行时间将SQL语句记录到slow log 2表示根据逻辑IO次数将SQL语句记录到slow log 3表示根据运行时间及逻辑IO次数将SQL语句记录到slow log
6.general_log:查询日志，也可以记录到表中
7.二进制日志：记录了mysql数据执行更改的所有操作，但是不包括select和show。show binlog events in 'mysqld.000002'\G;
8.影响二进制日志记录的信息和行为：max_binlog_size binlog_cache_sie sync_binlog binlog-do-db binlog-ignore-db log-slave-update binlog_format
max_binlog_size指定了单个二进制日志文件的最大值，超过这个值，则产生新的二进制文件，后缀名+1，并记录到.index文件，默认为1G；当使用事务的表存储时，所有未提交的
二进制日志会被记录到缓存中去，等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件中，该缓存大小由binlog_cache_sie决定，默认32K，binlog_cache_size是基于会话的
当一个线程开始一个事务时，mysql会自动废品大小为binlog_cache_size的缓存。可以通过show global status like 'binlog_cache%';查看，binlog_cache_disk_use记录了使用
临时文件写二进制日志的次数，binlog_cache_use记录了使用缓冲写二进制日志的次数
9.默认情况下二进制日志不是每次写的时候都同步到磁盘，因此数据库宕机时可能会有一部分数据没有写入二进制日志文件中，会给恢复带来问题，参数sync_binlog表示每写缓冲多少
次就同步到磁盘，1表示采用同步的方式来写入二进制日志，不使用缓冲来写二进制日志，设为1，因此会立刻写入磁盘，如果已经写入了磁盘但是还未commit，此时发生了宕机
，那么下次mysql启动时由于没有commit操作发生，事务会被回滚，但是二进制已经记录了该事务信息，不能回滚，此时通过innodb_support_xa设为1来解决，它确保了二进制日志和
innodb存储引擎数据文件的同步
10.binlog_format:可以为statement、row、mixed,当为mixed时：默认记录格式为statement，除了以下几种情况记录为row:存储引擎为NDB,对表进行DML；使用了UUID()、USER()、
CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定性函数；使用了INSERT DELAY语句；使用了临时表
表结构定义文件
11.每张表都有一.frm结尾的文件，此文件记录了该表的表结构定义，视图也一样
表空间文件
12.innodb采用将存储的数据按表空间(tablespace)进行存放设计。默认配置下会有一个初始大小为10MB，名为ibdata1的文件，可以通过innodb_data_file_path设置
[mysqld] innodb_data_file_path = /data/ibdata1:2000M;/data1/ibdata2:2000M:autoextend 用两个文件组成表空间。如果位于不同磁盘，磁盘负载可能被平均，可以提高新能
13.可以通过innodb_file_per_table开启来产生每个独立的表空间，表名.ibd，如果是共享表空间，则所有数据默认存放在ibdata里面
重做日志
14.默认情况下，数据目录下会有两个名为ib_logfile0和lb_logfile1的文件，称为重做日志(redo log),记录了innodb的事务日志
15.每个innodb存储引擎至少有1个重做日志文件组，每个文件组至少有两个重做日志文件，为了提高可靠性可以设置多个镜像日志组，innodb先写重做日志文件1，当达到文件最后是会切换到2，当2满时又到1
innodb_log_file_size:指定每个重做日志文件大小，1.2之前不能大于4GB，1.2之后扩大为512GB，innodb_log_files_in_group指定了日志文件组中重做日志文件数量，默认为2
参数innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，innodb_log_group_home_dir指定了日志文件组所在路径
16.重做日志不能设置过大，太大可能需要很长的恢复时间，大小可能导致一个事务多次切换重做日志文件，而且会导致发送async checkpoint
17.二进制日志和重做日志都记录事务，但前者记录所有存储引擎的日志，后者只记录有关本身存储引擎的事务日志，前者记录的是逻辑日志，后者是记录的每个页的更改的物理情况，前者写入在事务提交前，后者一直有日志写入
18.重做日志组成：redo_log_type 1字节表示重做日志类型 space表示表空间ID，采用压缩方式可能小于4字节 page_no表示页偏移量，采用亚索 redo_log_body表示每个重做日志的数据部分，恢复是需要调用相应函数进行解析
19.写入重做日志文件的操作不是直接写，而是先写入重做日志缓冲(redo log buffer),然后按照一定的条件顺序写入日志文件，写入磁盘时是按512字节即一个扇区大小写入，因为扇区是写入最小单位所以可以保证写入是必定能成功的，不需要doublewrite
20.写入磁盘条件：主线程每秒会将冲着这日志写入磁盘文件，不论事务是否已提交，另一个触发就是innodb_flush_log_at_trx_commit,表示当提交操作时，处理重做日志方式。有效值为
0、1、2；0表示当事务提交时，并不将事务的重做日志写到磁盘，而是等主线程每秒的刷新；1和2不同在于：1表示在执行commit时将重做日志缓冲同步写到磁盘，即伴有fsync的调用
2表示将重做日志异步写到磁盘，即写到文件系统的缓存中。不能完全保证执行commit时会肯定写入重做日志文件，只是有这个动作发生；所以为了保证事务ACID中的持久性，必须
设为1，也就是每当有事务提交时，就必须保证事务都已经写入重做日志文件
表
1.如果在创建表时没有指定主键，innodb会自动创建主键：首先会判断是否有非空的唯一索引，有则该列为主键，如果没有，则创建一个6字节大小的指针
2.可以通过select _rowid 来判断主键，但是不使用于组合主键索引
表空间：Row(行)--》Page(页)--》Extent(区)--》Segment(段)--Tablespace(表空间)
3.启用了innodb_file_per_table后，每张表的共享表空间里面有:数据、索引、插入缓冲Bitmap页，其他的回滚(undo)信息、插入缓冲索引页、系统事务信息、二次写缓冲等还是在原来
的共享表空间内，说明即使开启了Innodb_file_per_table共享表空间还是会变大
4.在关闭自动提交，update一张表的时候，事务还未提交，执行update后会产生大量的undo，观察ibdata增长了，说明undo还是在共享表空间中，当使用rollback时，ibdata也不会缩减
只是会标记这些空间为可用空间，供下次undo使用
段
表空间由各个段组成，常见的段有：数据段、索引段、回滚段等
区
去有连续页组成的空间，在任何情况下每个区大小都为1MB。为保证区的连续性，innodb一次从磁盘申请4~5个区，默认页的大小为16KB，即一个区中一共有64个连续的页
页
1.页是innodb磁盘管理的最小单位。默认为16KB，从1.2开始，可以通过innodb_page_size调整大小，不可以再次修改
2.页的类型：数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页
行

1.NULL值实际存储不占任何空间，只会占有标志位
2.innodb varchar实际只能最大支持65532
约束
innodb提供了以下几种约束：primary key、unique key、foreign key、default、not null
2.如果用户想通过约束对于数据库非法插入或更新，那么需要开启sql严格模式:set sql_mode='STRICT_TRANS_TABLES';
触发器
作用是在执行insert 、delete、update命令之前或之后自动调用SQL命令或存储过程，create trigger trigger_name before|after insert|update|delete on tb_name for each row trigger_stmt
最多可以为一个表建立6个触发器，分别为insert|delete|update|before|after各定义一个

delimiter $$
mysql> create trigger tgr_usercash_update before update on usercash for each row
-> begin
-> if new.cash-old.cash > 0 then
-> insert into usercash_err_log
-> select old.cash,old.cash,new.cash,user(),now();
-> set new.cash = old.cash;
-> end if;
-> end;
-> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;