mysql写系统_十五、MySQL系统优化 － 系统的撸一遍MySQL

MySQL体系架构

MySQL中的线程

insert buffer thread：主要负责插入缓冲区的合并操作

read thread：负责数据库读取操作

write thread：负责数据库写操作

log thread：将日志刷新到日志文件中

purge thread：执行purge操作

lock thread：锁控制和死锁检测

通过以下命令查看进程状态：

show engine innodb status\G

内存优化

MyISAM

索引块

MyISAM中主要优化索引块缓存，通过合理的优化，让索引读写更高效。

数据快没有提供缓存机制，采用操作系统的IO缓存。

通过设置 key_buffe_size 决定索引块大小，一般设置为可用内存的1/4。

通过以下命令评估索引块使用状况：

show status like 'key%';

MySQL为了防止索引块竞争，可以手动创建索引块：

//创建新的索引快

set global cache_name.key_buffer_size=1024*512;

//让索引使用新的索引快

cache index idx_name,idx_name2 in cache_name;

索引块淘汰

MySQL的索引块采用LRU算法进行淘汰，但是为了防止短期的热数据将长期热数据淘汰掉，MySQL采用了中点插入策略。

将LRU分为两部分：hot和warm，索引块缓存后首先进入warm尾部，如果一定时间达到命中次数后，转移到hot的尾部。hot头部热度超出时间则转移到warm尾部。触发淘汰机制的时候会首先淘汰warm头部的数据。用于保证真正的热数据不会被淘汰掉。

可以通过设置hot和warm链表的大小来进行优化。

主要参数：key_cache_division_limit、key_cache_age_threshold

InnoDB

缓存池

InnoDB采用一块内存区作为IO缓存池，用于索引和数据快的缓存。

缓存池分为：free list(空闲缓存快)、flush list(待sync缓存块)、LRU list等部分。

通过调整innodb_buffer_poll_size来设置缓存池大小。

//查看缓存池状态

show status like 'innodb_buffer%';

可以通过 Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests 来查看索引命中率。

通过调整innodb_buffer_poll_instances(缓存池数量)来减少线程对于缓存池的竞争。MySQL会根据缓存池的大小和数量平分缓存池。

doublewrite

InnoDB数据也大小为16KB操作系统的数据页一般为4KB，为了防止数据刷新硬盘的时候没有完整的写入，InnoDB采用doublewrite方式。每次刷新数据的时候会先写入到系统表空间的一块连续硬盘空间中，然后fsync直接刷新到硬盘作为备份，之后才会将数据写入硬盘。

可以通过调整 innodb_doublewrite来开启关闭doublewrite机制。

连接数

修改 max_connections 调整最大连接数，通过查看connection_errors_mac_connections 的增长来决定曾正最大连接数。

修改 back_log 参数控制MySQL监听端口时积压请求栈的大小，一般为连接数的五分之一。