Innodb之全局共享内存

参考链接：

https://blog.csdn.net/miyatang/article/details/54881547

https://blog.csdn.net/wyzxg/article/details/7700394?utm_source=copy 

https://blog.csdn.net/qq_27529917/article/details/78454947

http://www.cnblogs.com/zyzloner/p/6727676.html

http://www.ywnds.com/?p=9886

mysql内存：分为全局共享内存和线程独享内存，类似Oracle里的SGA和PGA.

1.全局共享内存：

InnoDB Buffer Pool：存储InnoDB数据和索引。        ---innodb_buffer_pool_size
InnoDB Additional Memory Pool：InnoDB 字典信息缓存  --innodb_additional_mem_pool_size  
redo log buffer：redo日志缓冲区。               ---innodb_log_buffer_size
Query Cache：存储查询缓存的。                  ---query_cache_size
Thread Cache：缓存连接线程的。                 ----thread_cache_size
Table Open Cache：缓存表文件句柄信息的。          ----table_open_cache   
Key Buffer：缓存MyISAM存储引擎索引键的。          ----key_buffer_size

2.global buffer(SGA, 全局内存分配总和)

global buffer(SGA, 全局内存分配总和) =

innodb_buffer_pool_size -- InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等

+innodb_additional_mem_pool_size -- InnoDB数据字典额外内存，缓存所有表数据字典

+innodb_log_buffer_size -- InnoDB REDO日志缓冲，提高REDO日志写入效率

+key_buffer_size -- MyISAM表索引高速缓冲，提高MyISAM表索引读写效率

+query_cache_size -- 查询高速缓存，缓存查询结果，提高反复查询返回效率

+table_cahce -- 表空间文件描述符缓存，提高数据表打开效率

+table_definition_cache

3.LSN

什么是LSN： LSN（Log Sequence Number 日志序列号），数据库内部记录数据库时间的值，为单调递增的long long 类型。

LSN的作用：

每个数据页有LSN，重做日志有LSN，checkpoint有LSN。 在每个Page头部和尾部都有对应的这个页第一次修改LSN号和最新一次修改LSN号。 LSN 主要用于发生 crash 时对数据进行 recovery ， LSN 是一个一直递增的整型数字， mysql中是按照写入redo的事务日志的字节数来增长得。 5.6.3之后占用 8 字节， 在每个数据页头部也会有对应的 LSN 号，该 LSN 记录当前页最后一次修改的 LSN 号，用于在 recovery 时对比重做日志 LSN 号决定是否对该页进行恢复数据。前面说的 checkpoint 也是有 LSN 号记录的， LSN 号串联起一个事务开始到恢复的过程。

查看LSN: mysql> show engine innodb status\G;(其值由上减小)

Log sequence number 2513682     --当前系统最大的LSN号

Log flushed up to   2513682      --当前已经写入redo日志文件的LSN

Pages flushed up to 2513682    ---已经将更改写入脏页的lsn号

Last checkpoint at  2513673   --系统最后一次刷新buffer pool脏中页数据到磁盘的checkpoint

以上4个LSN是递减的，即： LSN1>=LSN2>=LSN3>=LSN4.

 

4.checkpoint与脏页落盘时机

检查点（checkpoint,简写ckpt）:就是落脏页的点,本质是一个特殊的lsn编号。

检查点的作用:1. 缩短数据库恢复时间  2. 缓冲池不够用的时候，刷新脏页到磁盘  3. 重做日志不够用的时候，刷新脏页.

解释：当数据库发生宕机的时候，数据库不需要恢复所有的页面，因为检查点之前的页面都已经刷新回磁盘了。故数据库只需要对检查点以后的日志进行恢复，这就大大减少了恢复时间。缓冲池不够用时，根据LRU算法，溢出最近最少使用的页，如果页为脏页，强制执行checkpoint，将页刷新回磁盘。重做日志不可用，是指，重做日志的这部分不可以被覆盖，为什么？因为：这部分对应的数据还未刷新到磁盘上。重做日志的设计是循环使用的。数据库恢复时，如果不需要，即可被覆盖；如果需要，必须强制执行checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

检查点的类型: 一种是sharp检查点，一种是fuzzy检查点。（fuzzy落盘部分脏页，而sharp检查点落盘所有脏页。）

fuzzy checkpoint在数据库运行的时候，进行页面的落盘操作，不过这种模式下，不是全部落盘，而是落盘一部分数据。

Fuzzy落盘的条件：

1. master thread checkpoint： master每一秒或者十秒落盘

2. sync check point： redo 不可用的时候，这时候刷新到磁盘是从脏页链表中刷新的。

3. Flush_lru_list check point : 刷新flush list的时候

sharp checkpoint在数据库关闭的时候将所有数据页落脏。

sharp checkpoint落盘条件

1.关闭数据库的时候设置 innodb_fast_shutdown=1，在关闭数据库的时候，会刷新所有脏页到数据库内。

脏页落盘的操作是异步的，因此不会阻塞其他事务执行，而redo落盘的操作是同步的。

 

5.WAL

WAL(Write-Ahead Logging):日志先行

日志先行,任何对Innodb表的变动, redo log都要记录对数据的修改，redo日志就是记录要修改后的数据。redo 日志是保证事务一致性非常重要的手段，同时也可以使在buffer pool修改的数据不需要在事务提交时立刻写到磁盘上减少数据的IO从而提高整个系统的性能。这样的技术推迟了buffer pool页面的刷新，从而提升了数据库的吞吐，有效的降低了访问时延。带来的问题是额外的写redo log操作的开销。而为了保证数据的一致性，都要求WAL（Write Ahead Logging）。而redo 日志也不是直接写入文件，而是先写入redo log buffer，而是批量写入日志。当需要将日志刷新到磁盘时（如事务提交）,将许多日志一起写入磁盘。

 

6.实例启动时虚存很高，而物理内存很低的原因

Buffer Pool的每个内存块通过mmap的方式分配内存，在MySQL 5.7版本中开始默认以128M（可配置）的chunk单位分配内存块，InnoDB存储引擎中数据的访问是按照页（默认为16KB）的方式从数据库文件读取到Buffer Pool中的，然后在内存中用同样大小的内存空间来做一个映射。为了提高数据访问效率，数据库系统预先就分配了很多这样的空间，用来与文件中的数据进行交换，但实际并没有被使用。用mmap分配的内存都是虚存，在top命令中占用VIRT这一列，而不是RES这一列，只有相应的内存被真正使用到了，才会被统计到RES中，提高内存使用率。

 

7.buffer pool中控制块与内存碎片的产生

Buffer Pool的内存刚分配时，会被划分成若干对控制块和缓存页，InnoDB为每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址，一些锁信息以及LSN信息，当然还有一些别的控制信息，这些信息不需要记录到磁盘，而是根据读入数据块在内存中的状态动态生成的。每个缓存页对应的控制信息占用的内存大小是相同的，我们就把每个页对应的控制信息占用的一块内存称为一个控制块吧，控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块都被存放到 Buffer Pool 的前边，缓存页都被存放到 Buffer Pool 后边，在分配足够多的控制块和缓存页后，可能剩余的那点儿空间不够一对控制块和缓存页的大小，自然就用不到了，这个用不到的那点儿内存空间就被称为碎片了。当然，如果把Buffer Pool的大小设置的刚刚好的话，也可能不会产生碎片。

1.1内存缓冲池：Buffer Pool

1.1.1为什么有内存缓冲池？

innodb作为一个使用磁盘作为持久化介质的数据库，因为传统磁盘的IO能力非常有限，在数据处理的过程中，不可能所有的数据，都从磁盘中获取，所以一般情况，作为关系型数据库设计，都采用了内存缓冲池(Buffer Pool)作为一种这种的手段，把最近常用的数据缓存到内存中，然后在特定的时间对内存和硬盘数据进行同步，来保证内存和硬盘数据的一致性。那么在这种情况下，其实磁盘和内存的交互瓶颈，就成为了数据库主要的瓶颈。缓冲池的设计目的为了协调CPU速度与磁盘速度的鸿沟。

 

数据库瓶颈说明：

关系型数据库性能最大的瓶颈在硬盘上.大多数关系型数据库的优化方向是在优化IO上.

优化IO主要从三个方面着手:1.减少随机扫描,2减少硬盘IO请求次数.3.数据的总体的流量.

Buffer Pool中缓存的数据页分类：

buffer pool是一片连续的内存，缓冲池中缓存的数据页类型有:索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等。

 

数据页分类：

1.free page :此page未被使用，此种类型page位于free list中

2.clean page:此page被使用，对应数据文件中的一个页面，但是页面没有被修改，此类型page位于lru list中

3.dirty page:此page被使用，对应数据文件中的一个页面，但是页面被修改过，没有刷新到磁盘，此种类型page位于lru list和flush list中.

注明：free 类型的 page，一定位于 buf pool 的 free 链表中。clean，dirty 两种类型的 page，一定位于 buf pool的 LRU 链表中；dirty  page 还位于 buf  pool 的 flush 链表中。

我的总结：

free page数=free list节点数。dirty page数=flush list节点数。  

dirty page数+clean page数=lru list节点数。

总page数=free page数+clean page数+dirty page数=free list节点数+lru list的节点数。

 

1.1.2 三链表介绍（都是逻辑链表）

   1.Free List

为什么有FREE list?

Buffer Pool的内存刚分配时，会被划分成若干对控制块和缓存页。但是此时并没有真实的磁盘页被缓存到Buffer Pool中（因为还没有用到），之后随着程序的运行，会不断的有磁盘上的页被缓存到Buffer Pool中，那么问题来了，从磁盘上读取一个页到Buffer Pool中的时候该放到哪个缓存页的位置呢？或者说怎么区分Buffer Pool中哪些缓存页是空闲的，哪些已经被使用了呢？Free list就是用来记录这些的。刚刚完成初始化的Buffer Pool中所有的缓存页都是空闲的，所以每一个缓存页都会被加入到Free链表中。

free list记录的信息：
Free链表的节点中都记录了某个缓存页控制块的地址，而每个缓存页控制块都记录着对应的缓存页地址，所以相当于每个Free链表节点都对应一个空闲的缓存页。
原理：
Free list其上的节点都是未被使用的节点，都是可以立即使用的,如果需要从数据库中分配新的数据页，直接从上获取即可。在使用的过程中，每次成功load页面到内存后，都会判断free page是否够用，如果不够用的话，否则需要从FLU List或者LRU List淘汰一定的节点加入到free list中，这就可以满足其他进程在申请页面，使系统可用。数据库刚启动的时候，LRU 列表为空，当需要从磁盘中加载一个页到Buffer Pool中时，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free链表节点从链表中移除，表示该缓存页已经被使用了。而lru链表进行了清理动作，最后也需要把清理后的block加入到free才算完成，维持页数守恒。

(可以将free list想象成n多个空的容器，可以直接被使用，以备不时之需，但如果被使用，就给他贴上标签，标识它装的是什么东西，然后将它踢出链表，当使用完毕，又重新加入free list)。

说明：Innodb_buffer_pool_wait_free：如果>0，说明出现性能负载，buffer pool free list中没有可用块

疑问：系统是怎么判断free list是否够用的，free list的长度是怎么定的？

   2.LRU List(最近最少使用算法least recently used)

内存远比硬盘小,不可能将所有数据缓存到内存,故当buffer pool缓存满后,再有请求时,就必须丢弃一部分不经常用的数据(冷数据)。LRU-list按数据页的使用热度来记录数据页位置（故其数据页都被使用过），其两端分别是热数据组成的热端和冷数据组成的冷端。该算法采用“中点插入法”：当插入一个新block时，移除表尾最近最少使用的block，在中点插入新block。这个中点将链表分为两部分：靠近表头的一部分，为young区，这里的block是最近使用的节点，靠近表尾的一部分，为old区，默认前5/8为young list，存储经常被使用的热点page，后3/8为old list（young:old=5:3）。当读取的数据不在缓冲池里的时候，读取到的block需要插入到链表中，插入点为中点（中点不是中间点，而是young与old的区分点），但是插入的新节点为old区的节点，如果此时old区满了得话，移除表尾的block，即新读入的page默认被加在old-list头。如果数据块被用户请求获取，立即提升数据块到young区的头部（如果是innodb的read_head操作，延迟或不提升数据块到NEW区的最前面）。当长期不使用的数据块被挤到了Old list的尾巴，就会被系统回收。当读取old区的block时，该节点将变成“young”节点，而且此节点移动到young区的表头。从lru移除链表，要么是非脏页（也就是所谓的“clean的LRU链表节点”）按照策略进行“evict”，要是脏页但是已经在flush链表中完成了刷新动作，可以移除。LRU List还包含没有被解压的压缩页（这些压缩页刚从磁盘读取出来，还没来的及被解压，innodb存储引擎支持压缩页功能，非16k的页通过unzip_LRU列表管理）。

总结：在数据库操作中，被访问的节点将移除到young的表头，这样一来，在young区中的未被访问的节点将逐渐往表尾移动，当移动过中点，将变为old区的节点。而old区的节点若被访问到将变为young节点移动到表头，而old区中的未被访问的节点依旧往表尾移动，当表满时，表尾那个block将会被淘汰掉。

注明：1.innodb_old_blocks_pct： 确定modpoint 位置，默认37 ，(3/8=37%) 可以通过这个调整young 与old 比.

    2.innodb_old_blocks_time: 当有大的查询时，可能会将热点数据页从LRU 列表中移除，为了避免这个问题可以通过参数 innodb_old_blocks_time 的修改来实现，该参数表示页读取到 mid 位置后需要等待多久才会被加入到 LRU 列表的热端。

   3.innodb_max_dirty_pages_pct： 这个参数据控制脏页的比例（默认75%），即 当缓冲池中脏页的数量占据总页数的75%时，也会强制进行Checkpoint，刷新一部分的脏页到磁盘。 建议这个参数可以设制到 75%-90% 都行。如果是大量写入，而 且写入的数据不是太活跃，可以考虑把这个值设的低一点。  如果写入或是更新的数据也就是热数据就可以考虑把这个值设为：95%。

问题：lru list的长度是怎么定的？

   3.FLUSH List(简写为FLU list)

FLU链表是负责写入数据块的链表.即哪些块该不该落盘和落盘的顺序.链表中的所有节点都是脏页，也就是说这些数据页都被修改过，但是还没来得及被刷新到磁盘上。在FLU List上的页面一定在LRU List上，但是反之则不成立。一个数据页可能会在不同的时刻被修改多次，在数据页上记录了最老(也就是第一次)的一次修改的lsn，即oldest_modification。不同数据页有不同的oldest_modification，FLU List中的节点按照oldest_modification排序，链表尾是最小的，也就是最早被修改的数据页，当需要从FLU List中淘汰页面时候，从链表尾部开始淘汰。加入FLU List，需要使用flush_list_mutex保护，所以能保证FLU List中节点的顺序。数据库通过checkpoint机制将脏页刷新会磁盘，flush list中的页即为脏页列表。
脏块被刷入FLUSH链表，然后会在一段时间内进行落盘，当LRU List中的页第一次被修改了，就将该页的指针(page number)放入了Flush List(只要修改过，就放入，不管修改几次)。Flush List 中包含脏页(数据经过修改，但是未刷入磁盘的页)。Flush list 中存放的不是一个页，而是页的指针(page number)。在 Buffer Pool 的每个instance上都维持了一个flush list，flush list 上的 page 按照修改这些 page 的LSN号进行排序。因此定期做redo。checkpoint点时，选择的 LSN 总是所有 bp instance 的 flush list 上最老的那个page（拥有最小的LSN）。由于采用WAL的策略，每次事务提交时需要持久化 redo log 才能保证事务不丢。而延迟刷脏页则起到了合并多次修改的效果，避免频繁写数据文件造成的性能问题。
参数：innodb_io_capacity 和innodb_io_capacity_max 每次刷脏的page数。

 

        4.lru和FLU链表的比较说明：

1.除了flush链表本身的flush操作可以把dirty page从flush链表删除外，lru链表的flush操作也会让dirty page从flush链表删除。
2.LRU list flush，由用户线程触发(MySQL 5.6.2之前)；而Flush list flush由MySQL数据库InnoDB存储引擎后台srv_master线程处理。(在MySQL 5.6.2之后，都被迁移到page cleaner线程中)。LRU list flush，由于可能是用户线程发起，已经持有其他的page latch（页占有），因此在LRU list flush中，不允许等待持有新的page latch，导致latch死锁；而Flush list flush由后台线程发起，未持有任何其他page latch，因此可以在flush时等待page latch。
3.LRU list flush，其写出的dirty page，需要移动到LRU链表的尾部(MySQL 5.6.2之前版本)；或者是直接从LRU链表中删除，移动到free list(MySQL 5.6.2之后版本)。Flush list flush，不需要移动page在LRU链表中的位置。
4.LRU list flush，其目的是为了写出LRU 链表尾部的dirty page，释放足够的free pages，当buf pool满的时候，用户可以立即获得空闲页面，而不需要长时间等待；Flush list flush，其目的是推进Checkpoint LSN，使得InnoDB系统崩溃之后能够快速的恢复。

说明：Free链表跟LRU链表的关系是相互流通的，页在这两个链表间来回置换。而FLUSH链表记录了脏页数据，也是通过指针指向了LRU链表。

疑问：Free list 、lru list和flush list 增加与删除节点的机制？  

       5.查看buffer pool使用运行状况

mysql> show engine innodb status\G;    ---不是当前状态，而是过去某段时间范围内的状态。
.....

Buffer pool size   40955

Free buffers       40631

Database pages     324

Old database pages 0

Modified db pages  0

Pending reads      0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 287, created 37, written 55

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 324, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Buffer pool size：表示整个缓冲池中页的数量。（Buffer pool size*16K/1024=buffer pool 大小）

Free buffers表示free列表中页的数量。

Database pages表示LRU列表中页的数量，即正在被使用页的数量 。

Modified db pages显示了脏页的数量。

buffer pool hit rate，缓冲区中读到的页 / 总共发出的读页数，表示缓冲命中率，一般不低于95%，如果偏低，要看看是不是有全表扫描造成LRU列表污染。

注明：缓存命中率:

Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads)

Pending reads 0 发出了请求但没完成的io读个数

Pending writes: LRU 0, flush list 0, single page 0 发出了请求但没完成的io读个数在各个列表上的体现

Pages read 287, created 37, written 55， 从磁盘上读取出来的页数， 在内存中建立了页面但是没从磁盘上读取出来的页面数以及写入了的页面数。

0.00 reads/s, 0.00 creates/s, 0.00 writes/s 在刚过去的时间间隔里， 平均每秒的读取数和新建数

上述中Buffer pool size =  40955正好为Free buffers与Database pages之和。若不等，可能是因为有自适应哈希索引、lock信息、insert buffer等页。

脏页比率 = 需要被flush的页面数/(使用中的页面数+空闲页面数+1) 

查看lru链表每个页的具体信息：

mysql> select table_name, space,page_number,page_type from information_schema.innodb_buffer_page_lru where space=1;

查询脏页的数量：

mysql> select table_name, space,page_number,page_type from information_schema.innodb_buffer_page_lru where oldest_modification>0;

查看buffer pool 运行状况：

mysql> show status like  'Innodb_buffer_pool_%';

Innodb_buffer_pool_pages_data 分配出去，正在被使用页的数量，即lru链表中的page数（clean+dirty）

Innodb_buffer_pool_pages_dirty 脏页但没被flush除去的页面数,即flush list中的page数

Innodb_buffer_pool_pages_flushed 已经flush的页面数

Innodb_buffer_pool_pages_free 当前空闲页面数，即free list中page数。

Innodb_buffer_pool_pages_latched 当前被锁住的页面数

Innodb_buffer_pool_pages_misc 用于管理功能的页面数， 如adaptive hash等，其值为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data

Innodb_buffer_pool_pages_total 缓冲区总共的页面数

Innodb_buffer_pool_read_ahead_rnd 随机预读的次数

Innodb_buffer_pool_read_ahead_seq 线性预读的次数

Innodb_buffer_pool_read_requests 从内存中逻辑读取的请求数

Innodb_buffer_pool_reads 从磁盘上一页一页的读取的页数

Innodb_buffer_pool_wait_free 缓冲池等待空闲页的次数，当需要空闲块而系统中没有时，就会等待空闲页面，此值应该很小。如果大于0，则表示InnoDb缓冲池太小。

Innodb_buffer_pool_write_requests 缓冲池总共发出的写请求次数

Innodb_data_fsyncs  总共完成的fsync次数

Innodb_data_pending_fsyncs   innodb当前等待的fsync次数

Innodb_data_pending_reads   innodb当前等待的读的次数

Innodb_data_pending_writes   innodb当前等待的写的次数

Innodb_data_read   总共读入的字节数

Innodb_data_reads  innodb完成的读的次数

Innodb_data_writes   innodb完成的写的次数

Innodb_data_written  总共写出的字节数

缓冲池命中率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)

平均每次读取的字节数 = Innodb_data_read/Innodb_data_reads

 

1.1.3  innodb_buffer_pool相关参数

default_storage_engine(默认存储引擎)      innodb_data_file_path(ibdata1的文件大小及自增)
innodb_buffer_pool_size(缓存池大小)        innodb_buffer_pool_instances(缓存池个数)  innodb_additional_mem_pool_size(额外内存池的大小)  innodb_file_per_table(是否开启独立表空间)  innodb_buffer_pool_chunk_size(在线修改)

   1.innodb_buffer_pool_size大小设置

innodb_buffer_pool_size：如果系统总内存比较小,就设置50% ,如果内存大，最大可设置75%左右，其并不是mysql占用总内存。
举例： OS内存与buffer_pool ：8GB -->4GB   16G -->12GB    32G --->20GB    64G --->50GB
缓冲池的大小，直接影响了数据库的性能，通过增加缓冲池的大小，可以提升数据库的性能。

show global status like 'Innodb_buffer_pool_pages_data';--lru链表中页的数量（clean+dirty）

show global status like 'Innodb_buffer_pool_pages_total'; --buffer pool中总页数

show global status like 'Innodb_page_size'; --页大小，默认16KB。

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%

当结果 < 95% 则减少 innodb_buffer_pool_size，

建议设置大小为： Innodb_buffer_pool_pages_data* Innodb_page_size * 1.05 / (1024*1024*1024)

什么时候调整innodb_buffer_pool_size合适？
调大：命令： show status like  'Innodb_buffer_pool_%';
算法：

InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100  

当其值低于99%则可适当调大innodb_buffer_pool_size。

调小：命令：show engine innodb status\G; 

Free buffers :表示有多少空闲buffer。如果 此值长时间都较高，则可以考虑减小InnoDB缓冲池大小。

注明：

1. innodb_buffer_pool_size大小是分配给所有的buffer pools。每个buffer pool 实例至少1GB。

2.   innodb_buffer_pool_size = N * (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances)  其中N为正整数。

3.设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。

   2.innodb_buffer_pool_instances大小设置

参数innodb_buffer_pool_instances设置buffer_pool实例的个数， 默认为 8。
说明：
1.每个buffer_pool_instance无竞争关系：
每个buffer_pool_instance都有自己的锁，信号量，物理块(Buffer chunks)以及逻辑链表(下面的各种List)，即各个instance之间没有竞争关系，可以并发读取与写入。所有instance的物理块(Buffer chunks)在数据库启动的时候被分配，直到数据库关闭内存才予以释放。

2.当innodb_buffer_pool_size小于1GB时候，innodb_buffer_pool_instances被重置为1，主要是防止有太多小的instance从而导致性能问题。

3.每个Buffer Pool Instance有一个page hash链表，通过它，使用space_id和page_no就能快速找到已经被读入内存的数据页，而不用线性遍历LRU List去查找。注意这个hash表不是InnoDB的自适应哈希，自适应哈希是为了减少Btree的扫描，而page hash是为了避免扫描LRU List。

4.每个buffer_pool实例都有对应的buffer_pool，多个buffer pool实例的目的，是减少内存的对于一个链表的栓锁征用，对性能无明显的提升。

5.innodb_buffer_pool_instances大小设置：  

建议:如果内存2GB，配1个innodb_buffer_pool_instances 即可；4GB-8GB内存配2个，16-32GB内存配4个，64GB及以上配8个左右,一般不超过8个.

  

       3.在线修改buffer_pool。

 在mysql5.6里要修改innodb_buffer_pool大小只能停库后修改配置文件再启动.但在5.7里可以在线修改buffer_pool.
参数:innodb_buffer_pool_chunk_size 默认 128MB，read-only。

mysql> show variables like '%buffer_pool_chunk_size%';

 Variable_name            Value     

innodb_buffer_pool_chunk_size  134217728   ---134217728/1024/1024=128

报告在线调整缓冲池大小操作的状态：

mysql> show status like '%Innodb_buffer_pool_resize_status%'; 

注明：在线修改大小,就是我们不管数据库的情况下,调整buffer pool size大小,但是调整的大小,必须是innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances的整数倍，若不是，则自动调整为其倍数值，在错误日志里有显示。

    

      4.innodb_file_per_table开启独立表空间

开启独立表空间，建议开启,开启后作用:

1. 每个表都有自已独立的表空间。

2. 每个表的数据和索引都会存在自已的表空间中。

3. 可以实现单表在不同的数据库中移动。

4. 空间可以回收

启用innodb_file_per_table后，每张表的表空间只存放自己的：数据，索引和插入缓冲BITMAP页。其它信息仍放在默认表空间。其它信息如：回滚(undo)信息、插入缓冲索引页、系统的事物信息、二次写缓冲(Double write buffer)等

 

  5.数据预热

数据预热的作用：当数据库重启,则刚开启阶段需要将数据从硬盘加载到buffer pool中,走物理IO,导致数据库性能较差,有了数据预热,则在数据库重启过程中会将热数据写到硬盘的另一块地方,数据库开启后则会加载到buffer pool中。其中ib_buffer_pool为存放数据预热信息的文件.

[root@lbg mysql3306]# ll ib_buffer_pool

-rw-r----- 1 mysql mysql 615 Oct 15 23:33 ib_buffer_pool

 

自动数据预热：

innodb_buffer_pool_dump_pct=40   --LRU上最热的page的百分⽐.(默认40%)

innodb_buffer_pool_dump_at_shutdown = 1 --在关闭时把热数据dump到本地磁盘。（5.7.7以后默认是on）

innodb_buffer_pool_load_at_startup = 1  ---在启动时把热数据加载到内存。（默认关闭，最好开启）

手工开启数据预热：

innodb_buffer_pool_dump_now = 1   ----采用手工方式把热数据dump到本地磁盘。（默认关闭）

innodb_buffer_pool_load_now = 1     ---采用手工方式把热数据加载到内存。（默认关闭）

问题：手动数据预热的方法？

 

  6.其他参数

 

innodb_lock_wait_timeout 事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；

innodb_autoextend_increment：   系统表空间文件每次扩展的大小

   

    7.  FNP-刷新临阶页(Flush Neighbor Page )

刷新临阶页的作用：当刷新一个脏页时，innodb会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新，达到合并IO，随机转顺序的优化。

参数1：innodb_flush_neighbors={0|1|2} (>=MySQL 5.6)

0:表示关闭该功能

1:表示刷新一个区内的脏页

2:表示刷新几个连续的脏页

参数2:show variables like '%innodb_flush_method%';  --直接设置其值为O_DIRECT即可.

作用:设置innodb在落盘脏页的时候,绕过操作系统缓存,直接落盘,提高效率.

mysql> show variables like '%flush_method%';

+---------------------+----------+

| Variable_name       | Value    |

+---------------------+----------+

| innodb_flush_method | O_DIRECT |

+---------------------+----------+

建议：

如果是机械硬盘的话,建议使用2.,建议打开该特性,用来提高落盘效率

如果固态硬盘,关闭掉该特性0 (SSD建议关闭此功能)

 

     8 insert buffer(插入缓冲)

插入缓冲 insert buffer 又称change buffer。位于buffer pool中，从MySQL5.5版本开始，Insert buffer更名为change buffer,除了缓冲对二级索引的insert操作，还包括update/delete/后台purge操作。

插入缓冲的作用:

因为当修改表数据时,不仅要修改原表数据,还要修改辅助索引的数据,额外开销太大。插入缓冲是针对辅助索引的优化手段.对于辅助索引的插入或者更新操作，不是每一次直接插入到索引页中，而是先判断辅助索引页是否在内存中，如果没有的话，那么要插入到一个insert buffer页中，然后在达到一个时间点后，进行insert buffer和辅助索引的叶子节点的合并，这样可以将多个插入操作合并到一个插入操作中，节约大量的IO请求。

数据库使用插入缓冲的条件:

1. 索引是辅助索引

2. 索引不是唯一索引.因为在合并插入缓冲的过程中，数据库并不去判断唯一性.

3.索引必须在硬盘上,不在内存中.(若在内存上,直接合并即可,没必要使用insert-buffer)

使用insert buffer过程:

1. 在没有Insert/ChangeBuffer时，每次插入一条记录，就要读取一次页(读取内存，或者从磁盘读到内存)，然后将记录插入到页中;

2. 在有Insert/ChangeBuffer时，当插入一条记录时，先判断记录对应要插入的二级索引(secondaryindex)页是否在BufferPool中:

3. 如果该二级索引(secondaryindex)页已 在Buffer Pool中，则直接插入;

4. 如果没有在内存中，先将其Cache起来，放到Insert/ChangeBuffer中，等到该二级索引(secondaryindex)页被读到时(或主线程每1s或10s时)，将Insert/ChangeBuffer中该页对应的记录合并(Merge)进去，从而减少I/O操作;

insert buffer的优点:

1. 减少磁盘的离散读取

2. 将多次插入合并成一次操作（配合异步IO）

insert buffer的合并时机：

1. 通过master thread 定期进行合并(每1s或10s)

2. 用户在读取辅助索引的时候进行合并

Insert Buffer参数设置：

innodb_change_buffering = all

All ----默认,表示所有   none (禁用)   inserts --只优化insert.   Deletes--只优化delete.

changes =(insert & delete-marking) --优化insert+delete-marking    purge--只优化purge操作

 

  9 double-write(二次写)

double-write的作用:解决部分写的问题.

double write的实现机制:

Double write分为两部分:一部分是内存中的double write buffer ，大小为2MB（16k一个页，一共128个页），不属于buffer pool。第二部分是磁盘共享表空间(即ibdata1)连续的128个页，即2个区（extend），大小同样为2M，在对脏页进行落盘的时候，并不是直接进行落盘，而是先复制到double write buffer，然后再分别写入到共享表空间(ibdata1)(这是顺序IO),然后脏页再写入自己的表空间( .ibd文件)。

页面的刷新会遇到部分写的问题，也就是说对于只写了其中一个页面，只写了一部分的内容，innodb的页面大小是16KB，但是写入过程中只写了4KB（操作系统仅仅保证512字节写入的完整性），这个是时候因为页面不是完整的，因此不能通过redo来进行恢复。redo恢复的前提条件是页是完整的。在数据库崩溃后，传统的数据库会使用redo log进行恢复，恢复的原理是通过redo对数据重新进行物理操作，但是如果这个数据页本身发生了损坏，那么redo对其进行重做也是没有作用的，innodb的double-write，在写入的时候，创造了一个关于页的副本，这样即使在发生写失效后，也可以通过副本页，对还原重做。故double-write是针对最近需落盘的脏页(2M)先在内存生成其镜像块,然后该镜像块落盘到ibdata1文件中的double-write中(2M).最后脏页才落盘. 

说明：若Double-write落盘时，发生部分写，那么源数据块依旧是完好的，没有发生改变，故依旧可根据源数据块和redo进行数据恢复。若脏页落盘发生部分写，那么可以根据double-write和redo进行恢复，若没有double-write,则源数据块损坏没法恢复，则就是double-write的意义所在。

注：Double-write增加了IO次数,mysql性能可能会降低10%.

开启关闭double_write参数:      innodb_doublewrite

问题：double_write落盘机制？可以在redo落盘前落盘吗？

 

      10 AHI-自适应hash索引(adaptive_hash_index)

 adaptive_hash_index的作用：

自适应hash索引: hash索引对那些经常访问的索引页创建，可视为建立在B+tree索引上的“索引”。

 维护索引叶页面中所有记录的索引键值(或键值前缀)到索引叶页面位置的Hash映射关系，能够根据索引键值(前缀)快速定位到叶页面满足条件记录的Offset，减少了B+树Search Path的代价，将B+树从Root页面至Leaf页面的路径定位，优化为Hash Index的快速查询。hash索引只在buffer pool中,不落盘。数据库自动发现热数据并对热数据创建hash索引（不能人为干预）。

开启关闭自适应hash索引参数:     innodb_adaptive_hash_index

AHI的要求：

1.以该模式访问了100次该数据页。（访问模式一样指的是查询的条件一样）

2.页通过该模式访问了N次，其中N=页中记录*1/16;

如果一张表几乎整个被至于主内存中，那么有了哈希索引就可以直接查找任意元素并将其索引值转换为一系列的指针从而提升查询速度。InnoDB有一个机制可以监控索引的搜索。如果InnoDB注意到查询可以通过建立哈希索引得到优化，那么他就会自动做这件事。在一些负载的情况下，hash index的查找效益明显高于监控索引查询和维护hash index结构的花费。 但有时候，确保ahi访问的读写锁却会在负载很重的情况下变成竞争来源。比如很多并发关联。like操作符，和%匹配符不能再ahi中获益，在这种情况下可以关闭ahi，hash索引总是在基于已存在的b-tree索引的结构上创建，hash index可以是部分的覆盖经常访问的页。可以使用 SHOW ENGINE INNODB STATUS 命令去监控，如果看到了很多线程在等待 RW-latch竞争，那么可以考虑关闭该功能。从MySQL 5.7开始，自适应哈希索引搜索系统是分区的。每个索引都会绑定到一个特殊的分区上，并且每个分区都由各自独立的锁存器来保护。分区受到innodb_adaptive_hash_index_parts配置项的控制。在MySQL5.7之前，自适应哈希索引搜索系统是通过一个单独的锁存器来保护，在高负载的情况下它会变成竞争点。innodb_adaptive_hash_index_parts选项默认值为8，最大值为512。

说明：AHI启动后，读写速度提高了2倍，辅助索引的连接操作性能可以提高5倍。

 

2.1.1  InnoDB Log Buffer（日志缓冲区）和undo介绍

   1.为什么设计redo系统?

1.每次修改数据块落盘,哪怕修改一条数据,都要落盘,IO压力太大,而且数据块落盘是随机IO,效率太低.如果commit落盘的是脏块,脏块的落盘对硬盘的IO产生了非常大的性能影响.所以设置redo,只记录数据页的修改偏移情况,减少IO的数据量.(redo---记录的是偏移量,故原值必须存在,才可恢复数据正确性.)

2.数据块落盘是随机落盘,redo落盘是顺序IO,redo数据块和硬盘块都是512字节,相契合,效率更高.

    2.commit的实际意义.

数据库commit成功是保证了redo的落盘,不保证数据块的落盘.redo的落盘就已保证了数据事务的安全性.

        3.redo的日志类型(物理逻辑日志)

redo属于物理逻辑日志,物理性体现在redo记录的是数据页的改变.逻辑性体现在redo记录的是数据页修改的偏移量. Redo只记录DML操作导致的页面变化,不记录DDL操作.

        4.redo落盘时间(同步IO)

1.InnoDB Log Buffer满1/2.
2. 1s和10s自动落盘redo.  
3.commit时,必须落盘redo.
说明：redo落盘是同步IO，且是顺序IO，redo数据块大小是512字节而非16KB。

       5.redo日志文件写入方式

redo log文件以ib_logfile[number]命名，日志目录可以通过参数innodb_log_group_home_dir控制。redo log 以顺序的方式写入文件文件，写满时则回溯到第一个文件，进行覆盖写。Redo log文件是循环写入的，在覆盖写之前，总是要保证对应的脏页已经刷到了磁盘。在非常大的负载下，Redo log可能产生的速度非常快，导致频繁的刷脏操作，进而导致性能下降，通常在未做checkpoint的日志超过文件总大小的76%之后，InnoDB 认为这可能是个不安全的点，会强制的preflush脏页，导致大量用户线程stall住。如果可预期会有这样的场景，我们建议调大redo log文件的大小。可以做一次干净的shutdown，然后修改Redo log配置，重启实例。

1.当当前未刷脏的最老lsn和当前lsn的距离超过max_modified_age_async（71%）时，且开启了选项innodb_adaptive_flushing时，page cleaner线程会去尝试做更多的dirty page flush工作，避免脏页堆积。

2.当当前未刷脏的最老lsn和当前Lsn的距离超过max_modified_age_sync(76%)时，用户线程需要去做同步刷脏，这是一个性能下降的临界点，会极大的影响整体吞吐量和响应时间。

3.当上次checkpoint的lsn和当前lsn超过max_checkpoint_age(81%)，用户线程需要同步地做一次checkpoint，需要等待checkpoint写入完成。

4.当上次checkpoint的lsn和当前lsn的距离超过max_checkpoint_age_async（78%）但小于max_checkpoint_age（81%）时，用户线程做一次异步checkpoint（后台异步线程执行CHECKPOINT信息写入操作），无需等待checkpoint完成。

[root@lbg mysql3306]# ll ib_logfile*

-rw-r----- 1 mysql mysql 268435456 Oct 16 10:35 ib_logfile0

-rw-r----- 1 mysql mysql 268435456 Oct 12 16:18 ib_logfile1

注明： InnoDB 日志组的特性已经被废弃（redo日志写多份），归档日志(InnoDB archive log)特性也在5.7被彻底移除了。

   6.redo文件和redo块结构

 redo文件结构：

每个日志文件的前2048字节是存放的文件头信息。头结构定义在”storage/innobase/include/log0log.h” 中。

1.LOG_GROUP_ID 这个log文件所属的日志组，占用4个字节，当前都是0

2. LOG_FILE_START_LSN 这个log文件记录的开始日志的lsn，占用8个字节

3. LOG_FILE_WAS_CRATED_BY_HOT_BACKUP 备份程序所占用的字节数，共占用32字节

4. LOG_CHECKPOINT_1/LOG_CHECKPOINT_2 两个记录InnoDB checkpoint信息的字段，分别从文件头的第二个和第四个block开始记录，只使用日志文件组的第一个日志文件。 从地址2KB偏移量开始，其后就是顺序写入的各个日志块（log block）。

REDO数据块结构：

在InnoDB存储引擎中，重做日志都是以512字节进行存储的，这意味着重做日志缓存、重做日志文件块都是以块block的方式进行保存的，称为重做日志块(redo log block)每块的大小512字节。每个日志块包含一个日志头段（12字节）、一个尾段（4字节），以及一组日志记录（512 – 12 – 4 = 496字节） 。

   7.redo相关参数

innodb_log_buffer_size     --redo buffer大小。默认8M。
innodb_log_files_in_group            ---redo log文件个数，默认2个。
innodb_log_file_size                    ---每个文件的大小。默认值:5242880 (5M)。
innodb_log_files_in_group * innodb_log_file_size    ---总的redo log大小。
innodb_flush_log_at_trx_commit        ---落盘机制。

   8.redo文件大小设置

参数：innodb_log_file_size   配置redo文件大小。
内存<1G: 48M     内存<=4G: 128M       内存<=8G: 512M        内存<=16G: 1024M

        9.redo buffer大小设置

 参数：innodb_log_buffer_size   配置redo buffer大小。

日志缓冲的大小，重做日志缓冲，一般情况下8MB足够使用，如果不够放心，可以使用16MB。每满1/2就强制落盘，故其无需设置太大。

   10.redo落盘参数（innodb_flush_log_at_trx_commit）

innodb_flush_log_at_trx_commit:这个参数有三个值可以设置0或1或2.

0 :redo buffer每秒写入redo log file一次（数据库），并且log-file的磁盘flush刷新同步进行（系统),这种情况下，log buffer仅仅在master thread的每秒循环中执行。（每秒落盘。）

1 每次事务提交都会进行log buffer的写入log file（数据库），并且flush到磁盘中。（每次commit落盘。）

2 每次事务提交都会进行log buffer的写入到log file（数据库），但是flush操作是每秒进行一次。（每次commit写到操作系统缓存,但每秒落盘一次。）

说明：

设置为0的时候，速度最快，但是不安全。mysqld进程崩溃后,导致上一秒的数据全部丢失。

设置为1的时候，会造成一个事务的丢失。

设置为2的时候，速度较快，数据库崩溃会造成某个事务丢失，但是不会丢失一秒的数据，只有当服务器宕机或者断电才会造成1s的数据丢失。

注：这个参数配合sync_binlog（使binlog在每N次binlog写入后与硬盘 同步），共同组成了innodb的日志刷盘策略和数据安全性。相当重要当两个参数都为1的时候，速度最慢，但是数据最安全。(双1策略)

注：一般的脏页落盘都是先从mysqld的内存写到操作系统缓存，再从操作系统缓存写入磁盘。

  undo介绍：

    1.undo日志作用：

 Undo作用:1.保证rollback(事务的回滚). 2.mvcc(多版本并发控制)
Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作.大多数对数据的变更操作包括INSERT/DELETE/UPDATE，其中INSERT操作在事务提交前只对当前事务可见，因此产生的Undo日志可以在事务提交后直接删除（谁会对刚插入的数据有可见性需求呢！！），而对于UPDATE/DELETE则需要维护多版本信息，在InnoDB里，UPDATE和DELETE操作产生的Undo日志被归成一类，即update_undo。为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式，每个回滚段又有多个undo log slot。

 2.undo日志类型及记录时机

Undo是逻辑日志.undo页存放在buffer pool中，记录undo日志的时机：

1. DML操作导致的数据记录变化，均需要将记录的前镜像写入Undo日志（逻辑日志）

2.Undo页面的修改，同样需要记录Redo日志;

3. Rollback--反向使用事务的所有Undo日志，回滚整个事务所做的修改;

不同的update语句，写的日志量有较大的差异（三种update情况）

 1. in place update 日志量最小，操作最简单

 2. 不修改主键列，日志量其次

 3. 修改主键列，日志量最大，操作最复杂

  3.undo文件（最少2个。）

undo日志最早放在系统表空间ibdata1里.但ibdata1只能扩大不能缩小.在mysql5.6及以上版本是放在独立表空间里. 

UNDO的undo段的设置：

1. rseg0预留在系统表空间ibdata中;

2. rseg 1~rseg 32这32个回滚段存放于临时表的系统表空间中;

3. rseg33~ 则根据配置存放到独立undo表空间中（如果没有打开独立Undo表空间，则存放于ibdata中）

[root@lbg mysql3306]# ll undo*

-rw-r----- 1 mysql mysql 10485760 Oct 15 21:53 undo001

-rw-r----- 1 mysql mysql 10485760 Oct 15 21:53 undo002

-rw-r----- 1 mysql mysql 10485760 Oct 15 21:09 undo003

 4.在线回收undo表空间功能.

Mysql5.6中undo表空间独立了,5.7中增加了在线回收undo表空间功能.

innodb_undo_directory = /home/mysql3306/mysql3306/   ----配置undo目录

innodb_undo_logs = 128 #must >=35.default 128

innodb_undo_tablespaces = 3       #must >=2    ---undo文件个数(必须大于2)

innodb_undo_log_truncate = 1       -----开启在线回收功能.(5.7默认关闭,8.0默认开启)

innodb_max_undo_log_size = 1000M      ----undo单个文件最大值,即回收阀值.

innodb_purge_rseg_truncate_frequency = 128

    5.inset/update/delete的redo和undo过程

1.insert的redo和undo过程.

 第一步:写undo的redo.    第二步:写undo.  第三步:写入数据页(insert in memory page)

 第四步:写聚簇索引和辅助索引变化的redo.   第五步: 刷新脏页.

2.delete的redo过程.(没有修改主键列)

第一步:写undo的redo.    第二步:写undo.  第三步:修改数据页((insert in memory page))

第四步:写redo.(write del mark redo,在聚簇索引和辅助索引打上删除标记的redo)   第五步: 刷新脏页.(oracle里delete只是打上删除标记,表空间不会释放.)

3.update(不修改主键列和修改主键列,前后项长度变不变)

第一步:写undo的redo.    第二步:写undo.  第三步:修改数据页((insert in memory page))

第四步:写redo.(write insert redo,包括聚簇索引和辅助索引)   第五步: 刷新脏页.

若不修改主键列,若空间够用,可以直接在原位置修改. 若空间不够用,则打上删除标记,重新插入.若修改了主键列,则会打上删除标记,然后重新插入数据.

 

3.1 innodb_additional_mem_pool_size(额外内存池大小)

innodb_additional_mem_pool_size:设置innodb额外内存池（ InnoDB Additional Memory Pool）的大小.如果数据字典，以及内部数据结构缓存，如果表数量特别多的话，那么需要的内存就越大，默认8MB，一般设置8MB-32MB足够使用，一般设置16MB足够。如果我们通过 innodb_additional_mem_pool_size 参数所设置的内存大小不够，InnoDB 会自动申请更多的内存，并在 MySQL 的 Error Log 中记录警告信息。
注明：在mysql5.7上取消了InnoDB Additional Memory Pool。

 

4.1 MyISAM索引缓存 Key Buffer（key_buffer_size）

MyISAM 索引缓存将MyISAM表的索引信息（.MYI文件）缓存在内存中，以提高其访问性能。这个缓存可以说是影响MyISAM存储引擎性能的最重要因素之一了，通过 key_buffere_size 设置可以使用的最大内存空间。

注意：即使运行一个全部采用innodb的模式，仍需要定义一个索引码缓冲区，因为MYSQL元信息与MyISAM定义相同。

参数：key_buffer_size         ---Global 动态，默认 8M

推荐配置：默认 8M

# 如果为 MyISAM 表的话，设置 key_buffer 为内存的 5 - 50 % ，

# 但是对于 InnoDB 来说，必须保证 key_buffer+ InnoDB 缓冲池小于内存的 80%。

如何确认key_buffer_size不够用？

查看show full processlist的State列中，值Repairing the keycache是一个明显的指标，它指出当前索引码缓冲区大小不足以执行当前运行的SQL语句。这将导致额外的磁盘I/O开销。

 

4.2  查询缓存 Query Cache （query_cache_size）：

当打开MySQL的Query Cache之后，MySQL接收到每一个SELECT类型的Query之后都会首先通过固定的Hash算法得到该Query的Hash值，然后到Query Cache中查找是否有对应的Query Cache。如果有，则直接将Cache的结果集返回给客户端。如果没有，再进行后续操作，得到对应的结果集之后将该结果集缓存到Query Cache中，再返回给客户端。当任何一个表的数据发生任何变化之后，与该表相关的所有Query Cache全部会失效，所以Query Cache对变更比较频繁的表并不是非常适用，但对那些变更较少的表是非常合适的，可以极大程度的提高查询效率，如那些静态资源表，配置表等等。
参数说明：

为了尽可能高效的利用Query Cache，MySQL针对Query Cache设计了多个query_cache_type值和两个QueryHint：SQL_CACHE和SQL_NO_CACHE。当query_cache_type设置为0（或者 OFF）的时候不使用Query Cache，当设置为1（或者 ON）的时候，当且仅当Query中使用了SQL_NO_CACHE 的时候MySQL会忽略Query Cache，当query_cache_type设置为2（或者DEMAND）的时候，当且仅当Query中使用了SQL_CACHE提示之后，MySQL才会针对该Query使用Query Cache。可以通过query_cache_size来设置可以使用的最大内存空间。

参数 query_cache_size: Global Dynamic,Default 0

推荐配置：16M

如何确定系统query cache的情况？

show global status like 'qcache%';

或者

select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME like 'Qcache%';

公式：（Qcache_hits/Qcache_hits+Com_select+1)*100来确定查询缓存的有效性

mysql> show variables like 'Query_cache_size';

mysql> show global status like 'Com_select';

mysql> show global status like 'Qcache%';

 

4.3连接线程缓存 Thread Cache（thread_cache_size）

连接线程是MySQL为了提高创建连接线程的效率，将部分空闲的连接线程保持在一个缓存区以备新进连接请求的时候使用，这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。
参数：thread_cache_size，Global,Dynamic,Default 0
建议配置：

1G  —> 8；  2G  —> 16；

3G  —> 32；  >3G  —> 64 

注意，这里设置的是可以缓存的连接线程的数目，而不是内存空间的大小。

如何确定系统Thread Cache的情况？

mysql> show global status like 'Threads_created';

mysql>  show global status like 'connections';

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。

Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。最好将thread_cache_size设置成与threads_connected一样。

Threads_created :代表从最近一次服务启动，已创建线程的数量。

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

连接线程缓存的命中率：

(Connections - Threads_created) / Connections * 100% 计算出连接线程缓存的命中率。

 

4.4 表缓存 Table Cache（table_open_cache）

作用：表缓存区主要用来缓存表文件的文件句柄信息，当我们的客户端程序提交Query给MySQL的时候，MySQL需要对Query所涉及到的每一个表都取得一个表文件句柄信息，如果没有Table Cache，那么MySQL就不得不频繁的进行打开关闭文件操作，无疑会对系统性能产生一定的影响，Table Cache 正是为了解决这一问题而产生的。在有了Table Cache之后，MySQL每次需要获取某个表文件的句柄信息的时候，首先会到Table Cache中查找是否存在空闲状态的表文件句柄。如果有，则取出直接使用，没有的话就只能进行打开文件操作获得文件句柄信息。在使用完之后，MySQL会将该文件句柄信息再放回Table Cache 池中，以供其他线程使用。
在MySQL5.1.3之前的版本通过table_cache参数设置，但从MySQL5.1.3开始改为table_open_cache来设置其大小。

参数：table_open_cache     ---Global,Dynamic,Default 400

 注意，这里设置的是可以缓存的表文件句柄信息的数目，而不是内存空间的大小。

推荐配置：根据内存配置4G--->2048 大于最大Opened_tables。

在mysql默认安装情况下，table_cache的值在2G内存以下的机器中的值默认时256到 512，如果机器有4G内存,则默认这个值是2048。（可能不对）

 查看open_tables和opened_teables值：

mysql> show global status like 'open%_tables';     

如何确定系统table_open_cache的情况？

mysql> show variables like 'table_open_cache';

对于大多数情况，比较适合的值：

Open_tables / Opened_tables >= 0.85

Open_tables / table_cache <= 0.95

 

4.5表定义信息缓存 Table definition Cache （table_definition_cache）

表定义信息缓存是从 MySQL5.1.3 版本才开始引入的一个新的缓存区，用来存放表定义信息。当MySQL 中使用了较多的表的时候，此缓存无疑会提高对表定义信息的访问效率。MySQL提供了table_definition_cache 参数给我们设置可以缓存的表的数量。注意，这里设置的是可以缓存的表定义信息的数目，而不是内存空间的大小。
参数：table_definition_cache       -----Global, Dynamic, Default 400

 推荐配置：根据内存配置4G 2048 和Table Cache一样即可