調整內存，提高MySQL的執行效率

目录

​​​## 虚拟内存

## 数据缓存

## 臨時表

## 数据预热

## 减少IO

---

​​​## 虚拟内存

通過設置tmp_table_size && max_heap_table_size管理虛擬內存。其中max_heap_table_size全部放入内容，超出部分写入硬盘。tmp_table_size通常設置如下size

tmp_table_size=200M

# Maximum size for internal (in-memory) temporary tables. If a table grows larger than this value, it is automatically converted to disk based table This limitation is for a single table. There can be many of them.

## 数据缓存

1. open table && table_cache

show variables like 'table_cache';

or

show global status like 'open%tables%';

Open_tables / Opened_tables   * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

2.​​​​​​​ 查询innodb_buffer_pool_size

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

Innodb_buffer_pool_pages_free 为 0，buffer pool 已经被用光，需要增大innodb_buffer_pool_size

说明：innodb_buffer_pool_size参考物理内存50%-80%。

3. ​​​​​​​设置innodb_buffer_pool_size

innodb_buffer_pool_size=373M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

# row data. The bigger you set this the less disk I/O is needed to

# access data in tables. On a dedicated database server you may set this

# parameter up to 80% of the machine physical memory size. Do not set it

# too large, though, because competition of the physical memory may

# cause paging in the operating system.  Note that on 32bit systems you

# might be limited to 2-3.5G of user level memory per process, so do not

# set it too high.

独立的数据库服务器，物理内存的80%。设置过大，会导致system的swap空间被占用，操作系统变慢，从而减低sql查询的效率。

4.​​​​​​​ query cache/查询缓存

show global status like 'qcache%';

Qcache_free_blocks——缓存中相邻内存块的个数，数目大碎片多。通过FLUSH QUERY CACHE整理获得一个空闲块。

Qcache_free_memory——缓存中的空闲内存。

Qcache_hits——每次查询在缓存中命中时就增大。

Qcache_inserts——每次插入一个查询时就增大，命中次数除以插入次数就是不中比率。

5.緩存狀態

query_cache_type=1
query_cache_size=128M

# Set the query cache type. 0 for OFF, 1 for ON and 2 for DEMAND.

## 臨時表

創建臨時表：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

Created_tmp_tables——在内存中创建临时表

Created_tmp_disk_tables——在磁盘上创建临时表

show global status like 'created_tmp%';

​​​​​​​## 数据预热

理论上某条数据被读取时，才会缓存在 innodb_buffer_pool。所以，数据库刚刚启动，需要进行数据预热，将磁盘上的所有数据缓存到内存中。数据预热可以提高读取速度。

​​​​​​​## 减少IO

方法一、innodb_log_file_size

服务器宕机，需要更长时间来恢复。说明：

innodb_log_file_size设置为（0.25|1）*innodb_buffer_pool_size

方法二、innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 1 则每次修改写入磁盘

innodb_flush_log_at_trx_commit = 0/2 每秒写入磁盘​​​​​​​