MySQL学习笔记（二）-存储引擎

InnoDB和MyISAM的区别

InnoDB	MyISAM
支持事务	不支持事务
支持外键	不支持外键
支持行锁	不支持行锁，支持表锁
写操作效率高	读操作效率高
支持全文索引	支持全文索引
表占用空间较小	表占用空间较大
聚集索引	非聚集索引

Innodb:通过辅助索引找到主键,在通过主键索引来找到记录。
myisam通过索引找到物理地址,再通过物理地址找到对应的记录。

应用场景：

1. MyISAM 管理非事务表，它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的
   SELECT查询，那么MyISAM是更好的选择。
2. InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的
   INSERT或UPDATE操作，则应该使用InnoDB,这样可以提高多用户并发操作的性能

MyISAM索引实现

索引由B+树构成，执行查询操作的时候会先搜索B+树，如果找到对应叶子结点会，根据叶子节点的值（地址），拿出整行数据。

InnoDB索引实现（5.5后默认引擎）

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。（InnoDB如果没有主键，会用第一个非空且唯一的列，会建立隐藏列-6字节。）

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

innodb buffer pool

1. innodb buffer pool一次从磁盘读一个或几个page页，大小为16K
2. io_write_thread什么时候进行与commit无关，当IO压力大时少写入，IO压力小就多写入
3. innodb buffer pool以LRU（lastest recent used）作为其存储结构：若不做优化，select * 冷端表会把热端顶掉，再次查询热端表时就会出现大量磁盘IO（LRU污染）。
   
   热块竞争解决：
   设置 set global innodb buffer pool_size = XXX(一般占整个数据库的70%)
   innodb buffer pool_instance(这个参数会根据COU数量把IBP分为几块)
   innodb对LRU优化：在LRU中设置midpiont（默认第一次插入LRU列表5/8处，第二次插入头部）

undo里的值是修改前的值
undo里的数据在commit后就没用了
undo的作用：

• 没有提交和回滚时，保证事务的一致性，做一致性读取
• 当回滚时，支持事务的回滚

日志先行
commit做了一件事，将log buffer日志永久保留在磁盘中
数据库宕机时，进行redo操作
没有commit时，直接rollback回去

log buffer原则：当达到二分之一满，1M，commit时任意一个条件时，就会把数据刷到磁盘中。

关于redo的commit：
Innodb_flush_log_at_trx_commit参数
0 ：每秒将日志缓冲区写入log file，并同时flush到磁盘。跟事务提交无关。在机器crash并重启后，会丢失一秒的事务日志数据（并不一定是1s，也许会有延迟，跟操作系统调度有关）。
1：每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。（crash不会丢失事务日志）
2：每次事务提交将日志缓冲区写入log file，每秒flush一次到磁盘。（crash有可能丢失数据）

MRR（multi range read）：而MRR的优化在于，并不是每次通过辅助索引就回表去取记录，而是将其rowid给缓存起来，然后对rowid进行排序后，再去访问记录，这样就能将随机I/O转化为顺序I/O，从而大幅地提升性能。