mysql存储引擎优化.docx

mysql存储引擎优化.docx

《mysql存储引擎优化.docx》由会员分享，可在线阅读，更多相关《mysql存储引擎优化.docx（20页珍藏版）》请在冰豆网上搜索。

mysql存储引擎优化

存储引擎优化

MyISAM存储引擎优化

我们知道，MyISAM存储引擎是MySQL最为古老的存储引擎之一，也是最为流行的存储引擎之一。

对于以读请求为主的非事务系统来说，MyISAM存储引擎由于其优异的性能表现及便利的维护管理方式无疑是大家最优先考虑的对象。

我们将通过分析MyISAM存储引擎的相关特性，来寻找提高MyISAM存储引擎性能的优化策略。

索引缓存优化

MyISAM存储引擎的缓存策略是其和很多其他数据库乃至MySQL数据库的很多其他存储引擎不太一样的最大特性。

因为他仅仅缓存索引数据，并不会缓存实际的表数据信息到内存中，而是将这一工作交给了OS级别的文件系统缓存。

所以，在数据库优化中非常重要的优化环节之一“缓存优化”的工作在使用MyISAM存储引擎的数据库的情况下，就完全集中在对索引缓存的优化上面了。

在分析优化索引缓存策略之前，我们先大概了解一下MyISAM存储引擎的索引实现机制以及索引文件的存放格式。

MyISAM存储引擎的索引和数据是分开存放于“.MYI”文件中，每个“.MYI”文件由文件头和实际的索引数据。

“.MYI”的文件头中主要存放四部分信息，分别称为：

state（主要是整个索引文件的基本信息），base（各个索引的相关信息，主要是索引的限制信息），keydef（每个索引的定义信息）和recinfo（每个索引记录的相关信息）。

在文件头后面紧接着的就是实际的索引数据信息了。

索引数据以Block（Page）为最小单位，每个block中只会存在同一个索引的数据，这主要是基于提高索引的连续读性能的目的。

在MySQL中，索引文件中索引数据的block被称为IndexBlock，每个IndexBlock的大小并不一定相等。

在“.MYI”中，IndexBlock的组织形式实际上只是一种逻辑上的，并不是物理意义上的。

在物理上，实际上是以FileBlock的形式来存放在磁盘上面的。

在KeyCache中缓存的索引信息是以“CacheBlock”的形式组织存放的，“CacheBlock”是相同大小的，和“.MYI”文件物理存储的Block（FileBlock）一样。

在一条Query通过索引检索表数据的时候，首先会检查索引缓存（key_buffer_cache）中是否已经有需要的索引信息，如果没有，则会读取“.MYI”文件，将相应的索引数据读入KeyCache中的内存空间中，同样也是以Block形式存放，被称为CacheBlock。

不过，数据的读入并不是以IndexBlock的形式来读入，而是以FileBlock的形式来读入的。

以FileBlock形式读入到KeyCache之后的CacheBlock实际上是于FileBlock完全一样的。

如下图所示：

当我们从“.MYI”文件中读入FileBlock到KeyCache中CacheBlock时候，如果整个KeyCache中已经没有空闲的CacheBlock可以使用的话，将会通过MySQL实现的LRU相关算法将某些CacheBlock清除出去，让新进来的FileBlock有地方呆。

我们先来分析一下与MyISAM索引缓存相关的几个系统参数和状态参数：

◆key_buffer_size，索引缓存大小；

这个参数用来设置整个MySQL中的常规KeyCache大小。

一般来说，如果我们的MySQL是运行在32位平台纸上，此值建议不要超过2GB大小。

如果是运行在64位平台纸上则不用考虑此限制，但也最好不要超过4GB。

◆key_buffer_block_size，索引缓存中的CacheBlockSize；

在前面我们已经介绍了，在KeyCache中的所有数据都是以CacheBlock的形式存在，而

key_buffer_block_size就是设置每个CacheBlock的大小，实际上也同时限定了我们将

“.MYI”文件中的IndexBlock被读入时候的FileBlock的大小。

◆key_cache_division_limit，LRU链表中的HotArea和WarmArea分界值；

实际上，在MySQL的KeyCache中所使用的LRU算法并不像传统的算法一样仅仅只是通过访问频率以及最后访问时间来通过一个唯一的链表实现，而是将其分成了两部分。

一部分用来存放使用比较频繁的HotCackeLock（HotChain），被成为HotArea，另外一部分则用来存放使用不是太频繁的WarmCacheBlock（WarmChain），被成为WarmArea。

这样做的目的主要是为了保护使用比较频繁的CacheBlock更不容易被换出。

而key_cache_division_limit参数则是告诉MySQL该如何划分整个CacheChain划分为HotChain和WarmChain两部分，参数值为WarmChain占整个Chain的百分比值。

设置范围1～100，系统默认为100，也就是只有WarmChain。

◆key_cache_age_threshold，控制CacheBlock从HotArea降到WarmArea的限制；

key_cache_age_threshold参数控制HotArea中的CacheBlock何时该被降级到WarmArea中。

系统默认值为300，最小可以设置为100。

值越小，被降级的可能性越大。

通过以上参数的合理设置，我们基本上可以完成MyISAM整体优化的70％的工作。

但是如何的合理设置这些参数却不是一个很容易的事情。

尤其是key_cache_division_limit和key_cache_age_threshold这两个参数的合理使用。

对于key_buffer_size的设置我们一般需要通过三个指标来计算，第一个是系统索引的总大小，第二个是系统可用物理内存，第三个是根据系统当前的KeyCache命中率。

对于一个完全从零开始的全新系统的话，可能出了第二点可以拿到很清楚的数据之外，其他的两个数据都比较难获取，第三点是完全没有。

当然，我们可以通过MySQL官方手册中给出的一个计算公式粗略的估算一下我们系统将来的索引大小，不过前提是要知道我们会创建哪些索引，然后通过各索引估算出索引键的长度，以及表中存放数据的条数，公式如下：

Key_Size=key_number*（key_length+4）/0.67

Max_key_buffer_size

Threads_Usage=max_connections*（sort_buffer_size+join_buffer_size+

read_buffer_size+read_rnd_buffer_size+thread_stack）

当然，考虑到活跃数据的问题，我们并不需要将key_buffer_size设置到可以将所有的索引都放下的大小，这时候我们就需要KeyCache的命中率数据来帮忙了。

下面我们再来看一下系统中记录的与KeyCache相关的性能状态参数变量。

◆Key_blocks_not_flushed，已经更改但还未刷新到磁盘的DirtyCacheBlock；

◆Key_blocks_unused，目前未被使用的CacheBlock数目；

◆Key_blocks_used，已经使用了的CacheBlock数目；

◆Key_read_requests，CacheBlock被请求读取的总次数；

◆Key_reads，在CacheBlock中找不到需要读取的Key信息后到“.MYI”文件中读取的次数；

◆Key_write_requests，CacheBlock被请求修改的总次数；

◆Key_writes，在CacheBlock中找不到需要修改的Key信息后到“.MYI”文件中读入再修改的次数；

由于上面各个状态参数在MySQL官方文档中都有较为详细的描述，所以上面仅做基本的说明。

当我们的系统上线之后，我们就可以通过上面这些状态参数的状态值得到系统当前的KeyCache使用的详细情况和性能状态

Key_buffer_UsageRatio=（1-Key_blocks_used/（Key_blocks_used+Key_blocks_unused））*100%

Key_Buffer_Read_HitRatio=（1-Key_reads/Key_read_requests）*100%

Key_Buffer_Write_HitRatio=（1-Key_writes/Key_Write_requests）*100%

通过上面的这三个比率数据，就可以很清楚的知道我们的KeyCache设置是否合理，尤其是Key_Buffer_Read_HitRatio参数和Key_buffer_UsageRatio这两个比率。

一般来说

Key_buffer_UsageRatio应该在99%以上甚至100%，如果该值过低，则说明我们的key_buffer_size设置过大，MySQL根本使用不完。

Key_Buffer_Read_HitRatio也应该尽可能的高。

如果该值较低，则很有可能是我们的key_buffer_size设置过小，需要适当增加key_buffer_size值，也有可能是key_cache_age_threshold和key_cache_division_limit的设置不当，造成KeyCachecache失效太快。

一般来说，在实际应用场景中，很少有人调整key_cache_age_threshold和key_cache_division_limit这两个参数的值，大都是使用系统的默认值。

多KeyCache的使用

从MySQL4.1.1版本开始，MyISAM开始支持多个KeyCache并存的的功能。

也就是说我们可以根据不同的需要设置多个KeyCache了，如将使用非常频繁而且基本不会被更新的表放入一个KeyCache中以防止在公共KeyCache中被清除出去，而那些使用并不是很频繁而且可能会经常被更新的Key放入另外一个KeyCache中。

这样就可以避免出现某些场景下大批量的Key被读入KeyCache的时候，因为KeyCache空间问题使本来命中率很高的Key也不得不被清除出去。

MySQL官方建议在比较繁忙的系统上一般可以设置三个KeyCache：

一个HotCache使用20%的大小用来存放使用非常频繁且更新很少的表的索引；

一个ColdCache使用20%的大小用来存放更新很频繁的表的索引；

一个WarmCache使用剩下的60%空间，作为整个系统默认的KeyCache；

多个KeyCache的具体使用方法在MySQL官方手册中有比较详细的介绍，这里就不再累述了，有兴趣可以自行查阅研究。

KeyCache的Mutex问题

MySQL索引缓存是所有线程共享的全局缓存，当多线程同时并发读取某一个CacheBlock的时候并不会有任何问题，每个线程都可以同时读取该CacheBlock。

但是当某个CacheBlock正在被一个线程更新或者读入的时候，则该线程就会通过mutex锁定该CacheBlock以达到不允许其他线程再同时更新或者读取。

所以在高并发的环境下，如果Key