mysql存储引擎优化.docx

1、mysql存储引擎优化存储引擎优化MyI SAM存储引擎优化我们知道，MyISAM 存储引擎是MySQL 最为古老的存储引擎之一，也是最为流行的存储引擎之一。对于以读请求为主的非事务系统来说，MyISAM 存储引擎由于其优异的性能表现及便利的维护管理方式无疑是大家最优先考虑的对象。我们将通过分析MyISAM 存储引擎的相关特性，来寻找提高MyISAM 存储引擎性能的优化策略。索引缓存优化MyISAM 存储引擎的缓存策略是其和很多其他数据库乃至MySQL 数据库的很多其他存储引擎不太一样的最大特性。因为他仅仅缓存索引数据，并不会缓存实际的表数据信息到内存中，而是将这一工作交给了OS 级别的文件系

2、统缓存。所以，在数据库优化中非常重要的优化环节之一“缓存优化”的工作在使用MyISAM 存储引擎的数据库的情况下，就完全集中在对索引缓存的优化上面了。在分析优化索引缓存策略之前，我们先大概了解一下MyISAM 存储引擎的索引实现机制以及索引文件的存放格式。MyISAM 存储引擎的索引和数据是分开存放于“.MYI”文件中，每个“.MYI”文件由文件头和实际的索引数据。“.MYI”的文件头中主要存放四部分信息，分别称为：state（主要是整个索引文件的基本信息），base（各个索引的相关信息，主要是索引的限制信息）， keydef（每个索引的定义信息）和recinfo（每个索引记录的相关信息）。在

3、文件头后面紧接着的就是实际的索引数据信息了。索引数据以Block（Page）为最小单位，每个block 中只会存在同一个索引的数据，这主要是基于提高索引的连续读性能的目的。在MySQL 中，索引文件中索引数据的block 被称为Index Block，每个Index Block 的大小并不一定相等。在“.MYI”中，Index Block 的组织形式实际上只是一种逻辑上的，并不是物理意义上的。在物理上，实际上是以File Block 的形式来存放在磁盘上面的。在Key Cache 中缓存的索引信息是以“Cache Block”的形式组织存放的，“Cache Block”是相同大小的，和“.MY

4、I”文件物理存储的Block（ File Block ） 一样。在一条Query 通过索引检索表数据的时候， 首先会检查索引缓存（key_buffer_cache）中是否已经有需要的索引信息，如果没有，则会读取“.MYI”文件，将相应的索引数据读入Key Cache 中的内存空间中，同样也是以Block 形式存放，被称为Cache Block。不过，数据的读入并不是以Index Block 的形式来读入，而是以File Block 的形式来读入的。以File Block 形式读入到Key Cache 之后的Cache Block 实际上是于File Block 完全一样的。如下图所示：当我们从

5、“.MYI”文件中读入File Block 到Key Cache 中Cache Block 时候，如果整个Key Cache中已经没有空闲的Cache Block 可以使用的话，将会通过MySQL 实现的LRU 相关算法将某些Cache Block清除出去，让新进来的File Block 有地方呆。我们先来分析一下与MyISAM 索引缓存相关的几个系统参数和状态参数： key_buffer_size，索引缓存大小；这个参数用来设置整个MySQL 中的常规Key Cache 大小。一般来说，如果我们的MySQL 是运行在32 位平台纸上，此值建议不要超过2GB 大小。如果是运行在64 位平台纸上

6、则不用考虑此限制，但也最好不要超过4GB。 key_buffer_block_size，索引缓存中的Cache Block Size；在前面我们已经介绍了，在Key Cache 中的所有数据都是以Cache Block 的形式存在，而key_buffer_block_size 就是设置每个Cache Block 的大小，实际上也同时限定了我们将“.MYI”文件中的Index Block 被读入时候的File Block 的大小。 key_cache_division_limit，LRU 链表中的Hot Area 和Warm Area 分界值；实际上，在MySQL 的Key Cache 中所使用

7、的LRU 算法并不像传统的算法一样仅仅只是通过访问频率以及最后访问时间来通过一个唯一的链表实现，而是将其分成了两部分。一部分用来存放使用比较频繁的Hot Cacke Lock（Hot Chain），被成为Hot Area，另外一部分则用来存放使用不是太频繁的Warm Cache Block（Warm Chain），被成为Warm Area。这样做的目的主要是为了保护使用比较频繁的Cache Block 更不容易被换出。而key_cache_division_limit 参数则是告诉MySQL该如何划分整个Cache Chain划分为Hot Chain和Warm Chain 两部分，参数值为Wa

8、rmChain 占整个Chain 的百分比值。设置范围1100，系统默认为100，也就是只有Warm Chain。 key_cache_age_threshold，控制Cache Block 从Hot Area 降到Warm Area 的限制；key_cache_age_threshold参数控制Hot Area 中的Cache Block 何时该被降级到Warm Area 中。系统默认值为300，最小可以设置为100。值越小，被降级的可能性越大。通过以上参数的合理设置，我们基本上可以完成MyISAM 整体优化的70的工作。但是如何的合理设置这些参数却不是一个很容易的事情。尤其是key_cac

9、he_division_limit 和key_cache_age_threshold这两个参数的合理使用。对于key_buffer_size 的设置我们一般需要通过三个指标来计算，第一个是系统索引的总大小，第二个是系统可用物理内存，第三个是根据系统当前的Key Cache 命中率。对于一个完全从零开始的全新系统的话，可能出了第二点可以拿到很清楚的数据之外，其他的两个数据都比较难获取，第三点是完全没有。当然，我们可以通过MySQL 官方手册中给出的一个计算公式粗略的估算一下我们系统将来的索引大小，不过前提是要知道我们会创建哪些索引，然后通过各索引估算出索引键的长度，以及表中存放数据的条数，公式如

10、下：Key_Size = key_number * (key_length+4)/0.67Max_key_buffer_size Max_RAM - QCache_Usage - Threads_Usage - System_UsageThreads_Usage = max_connections * (sort_buffer_size + join_buffer_size +read_buffer_size + read_rnd_buffer_size + thread_stack)当然，考虑到活跃数据的问题，我们并不需要将key_buffer_size 设置到可以将所有的索引都放下的大小，

11、这时候我们就需要Key Cache 的命中率数据来帮忙了。下面我们再来看一下系统中记录的与KeyCache 相关的性能状态参数变量。 Key_blocks_not_flushed，已经更改但还未刷新到磁盘的Dirty Cache Block； Key_blocks_unused，目前未被使用的Cache Block 数目； Key_blocks_used，已经使用了的Cache Block 数目； Key_read_requests，Cache Block 被请求读取的总次数； Key_reads，在Cache Block 中找不到需要读取的Key 信息后到“.MYI”文件中读取的次数； Ke

12、y_write_requests，Cache Block 被请求修改的总次数； Key_writes，在Cache Block 中找不到需要修改的Key 信息后到“.MYI”文件中读入再修改的次数；由于上面各个状态参数在MySQL 官方文档中都有较为详细的描述，所以上面仅做基本的说明。当我们的系统上线之后，我们就可以通过上面这些状态参数的状态值得到系统当前的Key Cache 使用的详细情况和性能状态Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused) *100%Key_Buffer

13、_Read_HitRatio = (1 - Key_reads/Key_read_requests) * 100%Key_Buffer_Write_HitRatio = (1 - Key_writes/Key_Write_requests) * 100%通过上面的这三个比率数据，就可以很清楚的知道我们的Key Cache 设置是否合理，尤其是Key_Buffer_Read_HitRatio 参数和Key_buffer_UsageRatio 这两个比率。一般来说Key_buffer_UsageRatio 应该在99%以上甚至100%，如果该值过低，则说明我们的key_buffer_size 设置

14、过大，MySQL 根本使用不完。Key_Buffer_Read_HitRatio 也应该尽可能的高。如果该值较低，则很有可能是我们的key_buffer_size 设置过小， 需要适当增加key_buffer_size 值， 也有可能是key_cache_age_threshold和key_cache_division_limit的设置不当，造成Key Cache cache失效太快。一般来说，在实际应用场景中，很少有人调整key_cache_age_threshold 和key_cache_division_limit这两个参数的值，大都是使用系统的默认值。多Key Cache 的使用从My

15、SQL4.1.1 版本开始，MyISAM 开始支持多个Key Cache 并存的的功能。也就是说我们可以根据不同的需要设置多个Key Cache 了，如将使用非常频繁而且基本不会被更新的表放入一个Key Cache 中以防止在公共Key Cache 中被清除出去，而那些使用并不是很频繁而且可能会经常被更新的Key 放入另外一个Key Cache 中。这样就可以避免出现某些场景下大批量的Key 被读入Key Cache 的时候，因为KeyCache 空间问题使本来命中率很高的Key 也不得不被清除出去。MySQL 官方建议在比较繁忙的系统上一般可以设置三个Key Cache：一个Hot Cach

16、e 使用20%的大小用来存放使用非常频繁且更新很少的表的索引；一个Cold Cache 使用20%的大小用来存放更新很频繁的表的索引；一个Warm Cache 使用剩下的60%空间，作为整个系统默认的Key Cache；多个Key Cache 的具体使用方法在MySQL 官方手册中有比较详细的介绍，这里就不再累述了，有兴趣可以自行查阅研究。Key Cache 的Mutex 问题MySQL 索引缓存是所有线程共享的全局缓存，当多线程同时并发读取某一个Cache Block 的时候并不会有任何问题，每个线程都可以同时读取该Cache Block。但是当某个Cache Block 正在被一个线程更新或者读入的时候，则该线程就会通过mutex 锁定该Cache Block 以达到不允许其他线程再同时更新或者读取。所以在高并发的环境下，如果Key