mysql的内存参数分别有两大类,一类是线程独享的内存,一类是全局共享的内存
线程独享内存:join_buffer_size、sort_buffer_size、read_buffer_size顺序读取数据缓冲区、read_rnd_buffer_size随机读取数据缓冲区、bulk_insert_buffer_size批量插入暂存使用内存、tmp_table_size内部临时表使用内存、max_heap_table_size内存表使用内存
join_buffer_size:The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.When Batched Key Access is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine用于普通索引扫描、范围索引扫描和不使用索引因而执行全表扫描的联接的缓冲区的最小大小。当使用批处理密钥访问时,join_buffer_size的值定义了向存储引擎发出的每个请求中的批处理密钥的大小
sort_buffer_size:Each session that must perform a sort allocates a buffer of this size每个必须执行排序的会话都会分配一个这种大小的缓冲区
read_buffer_size:Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans对MyISAM表进行顺序扫描的每个线程为其扫描的每个表分配一个这种大小(以字节为单位)的缓冲区
tmp_table_size:The maximum size of internal in-memory temporary tables. 内存中内部临时表的大大小。mysql临时表分为两种,一种是使用create temporary table创建的,称为为外部临时表,一种是因union、order by、group by、distinct等语句产生的,称为内部临时表
max_heap_table_size:This variable sets the maximum size to which user-created MEMORY tables are permitted to grow此变量设置允许用户创建的内存表增长的大大小
全局共享内存:key_buffer_size(MyISAM索引缓存)、query_cache_size为缓存查询结果而分配的内存量、thread_cache_size服务器应该缓存多少个线程以供重用、table_open_cache所有线程打开的表的数量、binlog_cache_size二进制日志缓冲区、innodb_log_buffer_size事务日志缓冲区、innodb_buffer_pool_size数据和索引缓冲区
key_buffer_size:Index blocks for MyISAM tables are buffered and are shared by all threads.MyISAM表的索引块被缓冲并由所有线程共享。
query_cache_size:The amount of memory allocated for caching query results为缓存查询结果而分配的内存量
thread_cache_size:How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there.The default value= 8+(max_connections/100)
MySQL里面为了提高客户端请求创建连接过程的性能,提供了一个连接池也就是 Thread_Cache池,将空闲的连接线程放在连接池中,而不是立即销毁.这样的好处就是,当又有一个新的请求的时候,mysql不会立即去创建连接 线程,而是先去Thread_Cache中去查找空闲的连接线程,如果存在则直接使用,不存在才创建新的连接线程.
table_open_cache:The number of open tables for all threads. 为所有线程打开的表的数量。
binlog_cache_size:The size of the cache to hold changes to the binary log during a transaction.在事务处理期间,缓存保存对二进制日志的更改的大小。
innodb_log_buffer_size:The size in bytes of the buffer that InnoDB uses to write to the log files on disk.InnoDB用来写入磁盘上的日志文件的缓冲区的字节大小。
innodb_buffer_pool_size:The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 134217728 bytes (128MB).缓冲池的字节大小,InnoDB缓存表和索引数据的内存区域。默认值是134217728字节(128MB)