MySQL innoDB 内存结构

分为SGA系统全局区和PGA程序缓存区
show variables like "%buffer%";

SGA系统全局区：
1、innodb_buffer_pool，缓存innodb表数据、索引、插入缓冲、数据字典等
2、innodb_log_buffer，事务在内存中的缓冲，即redo log buffer的大小
3、query cache，高速查询缓存，建议关闭
4、key_buffer_size，只用于myisam，缓存myisam的索引，不缓存数据
5、innodb_additional_mem_pool_size，保存数据字典信息和其他内部数据结构的内存池大小，5.7.4中被移除

PGA程序缓存区：
1、sort_buffer_size，SQL语句在内存中的临时排序
2、join_buffer_size，表连接，用于BKA
3、read_buffer_size，表顺序扫描的缓存，只应用于myisam
4、read_rnd_buffer_size，随机读缓冲区大小，用于做mrr

特殊：
tmp_table_size，SQL语句在排序或者分组时没有用到索引，就会使用临时表
max_heap_table_size，管理heap、memory存储引擎表

生产中建议把tmp_buffer_size和max_heap_table_size设置成一样，若不一致，会按照两者中小的值限制。且不能太小，太小会报错：converted heap to myisam
show variables like "%heap%";
show variables like "%tmp_table_size%";

针对tmp还有两个重要的参数：default_tmp_storage_engine临时表默认存储引擎和internal_tmp_disk_storage_engine磁盘上临时表管理(决定create temporary table)

　一条SQL进入MySQL服务器，会依次经过连接池模块（进行鉴权，生成线程），查询缓存模块（是否被缓存过），SQL接口模块（简单的语法校验），查询解析模块，优化器模块（生成语法树），然后再进入innodb存储引擎。进入innodb后，首先会判断该SQL涉及到的页是否存在于缓存中，如果不存在则从磁盘读取相应索引及数据页加载至缓存。如果是select语句，读取数据(使用一致性非锁定读)，并将查询结果返回至服务器层。如果是DML语句，读取到相关页，先试图给这个SQL涉及到的记录加锁。加锁成功后，先写undo 页，逻辑地记录这些记录修改前的状态。然后再修改相关记录，这些操作会同步物理地记录至redo log buffer。如果涉及及非唯一辅助索引的更新，还需要使用insert buffer。事务提交时，会启用内部分布式事务，先将SQL语句记录到binlog中，再根据系统设置刷新redo log buffer至redo log，保证binlog与redo log的一致性。提交后，事务会释放对这些记录所加的锁，并将这些修改的记录所在的页放入innodb的flush list中，等待被page cleaner thread刷新到磁盘。这个事务产生的undo page如果没有被其它事务引用(insert的undo page不会被其它事务引用)，就会被放入history list中，等待被purge线程回收。

　　需要注意的是：
　　a.脏页的刷新采用的是checkpoint机制
　　b.DML语句不同undo页的格式也会不同。insert类型的undo log只记录了主键及对应的主键值，而update、delete则记录了主键及所有变更的字段值
　　c.一条设计不好的SQL，可能会导致大量的离散读、加载很多冗余的数据页至缓存中

以下为innodb内部各部分的协调管理简图：