Mysql如何使用内存

　　每个session使用的内存：

　　Each thread that is used to manage client connections uses some thread-specific space. The following list indicates these and which variables control their size:

　　A stack (default 192KB, variable thread_stack)

　　A connection buffer (variable net_buffer_length)

　　A result buffer (variable net_buffer_length)

　　The connection buffer and result buffer both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.

　　每个session都有自己独占的内存空间，存储特定的信息，内存分配采用malloc(),free()，内存管理采用FIFO，应该也是增强版的先进先出，没什么区别。

　　key_buffer_size：

　　索引缓冲区的大小，只用来缓存Myisam的索引，Myisam表的数据是不会放到这个缓存里面。

　　我们可以通过Key_read_requests和Key_read的比例来判断设置是否合理，key_read/Key_read_requests比例要小越好，至少是在1:1000以上。

　　对表进行顺序扫描的请求将分配一个缓存区(变量read_buffer_size)。

　　当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读 缓存区(变量read_rnd_buffer_size)以避免硬盘搜索。

　　Myisam表文件是每个session都会打开，多个session就是打开多次，而索引只需要打开一次。

　　For each MyISAM table that is opened, the index file is opened once; the data file is opened once for each concurrently running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 × N are allocated (where N is the maximum row length, not counting BLOB columns). A BLOB column requires five to eight bytes plus the length of the BLOB data. The MyISAM storage engine maintains one extra row buffer for internal use.

　　tmp_table_size

　　If an internal heap table exceeds the size of tmp_table_size, MySQL handles this automatically by changing the in-memory heap table to a disk-based MyISAM table as necessary.--这个说的内部表和内存表(memory)是不一样的，内存表是不会转化的,文档中说得太模糊，基本上我已经晕了。

　　看看这位老兄的描述，清楚多了。

　　The lower value from tmp_table_size and use max_heap_table_size was used to define when temporary table was converted from memory to disk.

　　Also tmp_table_size was used to limit the size of the temporary table - no matter whether it’s in memory or on disk.

　　And max_heap_table_size was also used to limit the size of explicitly created HEAP tables.

　　This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory

原文转自：http://blogread.cn/it/article/65