![](http://img.blog.itpub.net/blog/2023/05/29/cf69de2747828714.png?x-oss-process=style/bb)

# SGA

1. innodb_buffer_bool
   innodb 高速缓冲 data 和索引，简称 IBP，这个是 Innodb 引擎中影响性能最大的参数。用来缓存Innodb表的数据、索引、插入缓冲、数据字典等信息。建议将 IBP 设置的大一些，单实例下，建议设置为可用 RAM 的 50%~80%。
   1. innodb 不依赖 OS，而是自己缓存了所有数据，包括索引数据、行数据等等，这个和 myisam 有差别。
   2. IBP 有一块 buffer 用于插入缓冲，在插入时，先写入内存之后再合并后顺序写入磁盘；在合并到磁盘的时候会引发较大的 IO 操作，对实际操作造成影响。（看上去的表现是抖动，TPS变低）
   3. show global status like ‘innodb_buffer_pool_%’ 查看 IBP 状态，单位是 page(16kb)，其中，Innodb_buffer_pool_wait_free 如果较大，需要加大 IBP 设置
   4. InnoDB会定时（约每10秒）将脏页刷新到磁盘，默认每次刷新 10 页；要是脏页超过了指定数量（innodb_max_dirty_pages_pct），InnoDB 则会每秒刷 100 页脏页
   5. innodb_buffer_pool_instances 可以设置 pool 的数量
   6. show engine innodb status\G    可以查看 innodb 引擎状态
   7. 包括如下几个部分：
      - undo page
      - insert buffer page 
        不是缓冲池的一部分，Insert Buffer是物理页的一个组成部分，它带来InnoDB性能的提高。根据B+算法(下文会提到)的特点，插入数据的时候会主键索引是顺序的，不会造成数据库的随机读取，而对于非聚集索引(即辅助索引)，叶子节点的插入不再是顺序的了，这时需要离散地访问非聚集索引，插入性能在这里变低了。InnoDB引入插入缓冲，判断非聚集索引页是否在缓冲池中，如果在则直接插入;不在，则先放在 插入缓冲区中。然后根据上述master thread中介绍的，会有一定的频率将插入缓冲合并。此外，辅助索引不能是 Unique的，因为插入到插入缓冲时，并不去查找索引页的情况，否则仍然会造成随机读，失去插入缓冲的意义了。插入缓冲可能会占缓冲池中内存，默认也能会占到1/2，所以可以将这个值调小点，到1/3。通过IBUF_POOL_SIZE_PER_MAX_SIZE来设置，2表示1/2，3表示1/3。
      - adaptive hash index 
        InnoDB存储引擎提出一种自适应哈希索引，存储引擎会监控对表上索引的查找，如果观察到建立建立哈希索引会带来速度的提升，则建立哈希索引，所以称之为自适应的。自适应哈希索引只能用来搜索等值的查询，如select * from table where index_col='***'， 此外自适应哈希是由InnoDB存储引擎控制的，我们只能通过innodb_adaptive_hash_index来禁用或启用，默认开启。 
      - index page
      - data dictionary
      - lock info
2. innodb_log_buffer
   事务在内存中的缓冲，即 red log buffer 的大小
   1. innodb redo 日志缓冲，提高 redo 写入效率。如果表操作中包含大量并发事务（或大规模事务），并且在事务提交前要求记录日志文件，请尽量调高此项值，以提高日志效率。
   2. show global status 查看 Innodb_log_waits 是否大于0，是的话，就需要提高 innodb_log_buffer_size，否则维持原样。
   3. show global stauts 查看30~60秒钟 Innodb_os_log_written 的间隔差异值，即可计算出 innodb_log_buffer_size 设置多大合适。
3. query cache
   高速查询缓存，在生产环境中建议关闭。
   1. 查询高速缓冲，缓存结果，减少硬解析（建议关闭，如果真需要查询缓存可以借助redis等缓存）
4. key_buffer_size
   1. myisam引擎中表的索引 的缓存大小，默认值=16M；单个key_buffer_size最大只有4G（32-bit系统下最大4G，64-bit下可以超过）
   2. 若主要使用myisam存储引擎，则设置最高不超过物理内存的20%~50%，
   3. 即便全是innodb表,没用MyISAM，也有必要设置key_buffer_size用于缓存临时表的索引，推荐设置32MB
   4. 关于临时表，如果内存tmp_table_size（Created_tmp_tables）不够的话，内部的临时磁盘表是MyISAM表（Created_tmp_disk_tables）。show global status like 'Create%'; show variables like 'tmp%';
5. innodb_additional_mem_pool_size
   用来缓存数据字典信息和其它内部数据结构的内存池的大小。MySQL5.7.4中该参数取消。
   指定 InnoDB 用来存储数据字典和其他内部数据结构的内存池大小。缺省值是8M（8388608）。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。
6. table_definition_cache：
   1. 表定义文件描述缓存，提高表打开效率。是frm文件在内存中的映射。MySQL需要打开frm文件，并将其内容初始化为Table Share 对象。这里存放与存储引擎无关的，独立的表定义相关信息。
7. table_open_cache：
   1. 表空间文件描述缓冲，提高表打开效率。
   2. 增加table_open_cache，会增加文件描述符（ulimit -a查看系统的文件描述符），当把table_open_cache设置的过大时，如果系统处理不了这么多文件描述符，那么就会出现客户端失效、连接不上。
   3. table_open_cache，也就是平时说的table cache。存放当前已经打开的表句柄，与表创建时指定的存储引擎相关。请注意和table_define_cache参数的区别。

# PGA

1. sort_buffer_size
   是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。
2. join_buffer_size
   应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作。当 Join Buffer 太小，MySQL 不会将该 Buffer 存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join 的表进行 Join 操作，然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer 中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。
3. read_buffer_size
   是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，        并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
4. read_rnd_buffer_size
   是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
5. max_heap_table_size 参数：
   定义了MEMORY、HEAP表的最大容量，如果内存不够，则不允许写入数据
6. tmp_table_size 参数：
   规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下。
   优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的，否则临时表超过内存临时表的限制，会自动转化为基于磁盘的Myisam表。
7. binlog_cache_size：
   在事务过程中容纳二进制日志SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存，注意，是每个Client 都可以分配设置大小的binlog cache 空间。如果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能。当然，我们可以通过MySQL 的以下两个状态变量来判断当前的binlog_cache_size 的状况：Binlog_cache_use 和Binlog_cache_disk_use。“max_binlog_cache_size”：和"binlog_cache_size"相对应，但是所代表的是binlog 能够使用的最大cache 内存大小。当我们执行多语句事务的时候，max_binlog_cache_size 如果不够大的话，系统可能会报出“ Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的错误。
   其中需要注意的是：table_cache表示的是所有线程打开的表的数目，和内存无关。
8. thread_stack ：
   主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等，我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存。 
9. thread_cache_size：
   如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。
10. net_buffer_length：
      客户发出的SQL语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。
11. bulk_insert_buffer_size：
    如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对myisam表使用。

ITPub博客

MySQL：内存结构

最新文章