mysql各個(gè)內(nèi)存參數(shù)的介紹，分線程獨(dú)享和全局共享兩大類

mysql的內(nèi)存參數(shù)分別有兩大類，一類是線程獨(dú)享的內(nèi)存，一類是全局共享的內(nèi)存 線程獨(dú)享內(nèi)存：join_buffer_size、sort_buffer_size、read_buffer_size順序讀取數(shù)據(jù)緩沖區(qū)、read_rnd_buffer_size隨機(jī)讀取數(shù)據(jù)緩沖區(qū)、bulk_insert_buffer_size批量插入暫存使用內(nèi)存、tmp_table_size內(nèi)部臨時(shí)表使用內(nèi)存、max_heap_table_size內(nèi)存表使用內(nèi)存

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用于普通索引掃描、范圍索引掃描和不使用索引因而執(zhí)行全表掃描的聯(lián)接的緩沖區(qū)的最小大小。當(dāng)使用批處理密鑰訪問(wèn)時(shí)，join_buffer_size的值定義了向存儲(chǔ)引擎發(fā)出的每個(gè)請(qǐng)求中的批處理密鑰的大小

sort_buffer_size:Each session that must perform a sort allocates a buffer of this size每個(gè)必須執(zhí)行排序的會(huì)話都會(huì)分配一個(gè)這種大小的緩沖區(qū)

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對(duì)MyISAM表進(jìn)行順序掃描的每個(gè)線程為其掃描的每個(gè)表分配一個(gè)這種大小(以字節(jié)為單位)的緩沖區(qū)

tmp_table_size:The maximum size of internal in-memory temporary tables. 內(nèi)存中內(nèi)部臨時(shí)表的最大大小。mysql臨時(shí)表分為兩種，一種是使用create temporary table創(chuàng)建的，稱為為外部臨時(shí)表，一種是因union、order by、group by、distinct等語(yǔ)句產(chǎn)生的，稱為內(nèi)部臨時(shí)表

max_heap_table_size:This variable sets the maximum size to which user-created MEMORY tables are permitted to grow此變量設(shè)置允許用戶創(chuàng)建的內(nèi)存表增長(zhǎng)的最大大小

全局共享內(nèi)存：key_buffer_size(MyISAM索引緩存)、query_cache_size為緩存查詢結(jié)果而分配的內(nèi)存量、thread_cache_size服務(wù)器應(yīng)該緩存多少個(gè)線程以供重用、table_open_cache所有線程打開的表的數(shù)量、binlog_cache_size二進(jìn)制日志緩沖區(qū)、innodb_log_buffer_size事務(wù)日志緩沖區(qū)、innodb_buffer_pool_size數(shù)據(jù)和索引緩沖區(qū)

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為緩存查詢結(jié)果而分配的內(nèi)存量

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里面為了提高客戶端請(qǐng)求創(chuàng)建連接過(guò)程的性能,提供了一個(gè)連接池也就是 Thread_Cache池,將空閑的連接線程放在連接池中,而不是立即銷毀.這樣的好處就是,當(dāng)又有一個(gè)新的請(qǐng)求的時(shí)候,mysql不會(huì)立即去創(chuàng)建連接 線程,而是先去Thread_Cache中去查找空閑的連接線程,如果存在則直接使用,不存在才創(chuàng)建新的連接線程.

table_open_cache：The number of open tables for all threads. 為所有線程打開的表的數(shù)量。

binlog_cache_size：The size of the cache to hold changes to the binary log during a transaction.在事務(wù)處理期間，緩存保存對(duì)二進(jìn)制日志的更改的大小。

innodb_log_buffer_size：The size in bytes of the buffer that InnoDB uses to write to the log files on disk.InnoDB用來(lái)寫入磁盤上的日志文件的緩沖區(qū)的字節(jié)大小。

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).緩沖池的字節(jié)大小，InnoDB緩存表和索引數(shù)據(jù)的內(nèi)存區(qū)域。默認(rèn)值是134217728字節(jié)(128MB)