MySQL常用存储引擎优化

前言：

MySQL 提供的非常丰富的存储引擎种类供大家选择，有多种选择固然是好事，但是需要我们理解掌握的知识也会增加很多。每一种存储引擎都有各自的特长，也都存在一定的短处。如何将各种存储引擎在自己的应用环境中结合使用，扬长避短，也是一门不太简单的学问。本章选择最为常用的两种存储引擎进行针对性的优化建议，希望能够对读者朋友有一定的帮助。

MyI SAM存储引擎优化

我们知道，MyISAM 存储引擎是 MySQL 最为古老的存储引擎之一，也是最为流行的存储引擎之一。对于以读请求为主的非事务系统来说，MyISAM 存储引擎由于其优异的性能表现及便利的维护管理方式无疑是大家最优先考虑的对象。这一节我们将通过分析MyISAM存储引擎的相关特性，来寻找提高MyISAM存储引擎性能的优化策略。

索引缓存优化

MyISAM 存储引擎的缓存策略是其和很多其他数据库乃至 MySQL 数据库的很多其他存储引擎不太一样的最大特性。因为他仅仅缓存索引数据，并不会缓存实际的表数据信息到内存中，而是将这一工作交给了 OS 级别的文件系统缓存。

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

在分析优化索引缓存策略之前，我们先大概了解一下 MyISAM 存储引擎的索引实现机制以及索引文件的存放格式。MyISAM 存储引擎的索引和数据是分开存放于“.MYI”文件中，每个“.MYI”文件由文件头和实际的索引数据。“.MYI”的文件头中主要存放四部分信息，分别称为：state（主要是整个索引文件的基本信息），base（各个索引的相关信息，主要是索引的限制信息），keydef（每个索引的定义信息）和recinfo（每个索引记录的相关信息）。在文件头后面紧接着的就是实际的索引数据信息了。索引数据以Block（Page）为最小单位，每个 block 中只会存在同一个索引的数据，这主要是基于提高索引的连续读性能的目的。在 MySQL 中，索引文件中索引数据的 block 被称为 Index Block，每个 Index Block 的大小并不一定相等。

在“.MYI”中，Index Block 的组织形式实际上只是一种逻辑上的，并不是物理意义上的。在物理上，实际上是以 File Block 的形式来存放在磁盘上面的。在 Key Cache 中缓存的索引信息是以“Cache Block”的形式组织存放的，“Cache Block”是相同大小的，和“.MYI”文件物理存储的 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 完全一样的。如下图所示：

经没有空闲的 Cache Block 可以使用的话，将会通过 MySQL 实现的 LRU 相关算法将某些 Cache Block清除出去，让新进来的 File Block 有地方呆。我们先来分析一下与 MyISAM 索引缓存相关的几个系统参数和状态参数：

◆ key_buffer_size，索引缓存大小；

这个参数用来设置整个 MySQL 中的常规 Key Cache 大小。一般来说，如果我们的 MySQL 是运行在 32 位平台纸上，此值建议不要超过 2GB 大小。如果是运行在 64 位平台纸上则不用考虑此限制，但也最好不要超过 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 中所使用的 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 两部分，参数值为 WarmChain 占整个 Chain 的百分比值。设置范围 1～100，系统默认为 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_cache_division_limit 和 key_cache_age_threshold这两个参数的合理使用。

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

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 设置到可以将所有的索引都放下的大小，这时候我们就需要 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”文件中读取的次数；

◆ Key_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_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 设置过大，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 的使用从 MySQL4.1.1 版本开始，MyISAM 开始支持多个 Key Cache 并存的的功能。也就是说我们可以根据不同的需要设置多个 Key Cache 了，如将使用非常频繁而且基本不会被更新的表放入一个 Key Cache 中以防止在公共Key Cache中被清除出去，而那些使用并不是很频繁而且可能会经常被更新的Key 放入另外一个 Key Cache 中。这样就可以避免出现某些场景下大批量的 Key 被读入 Key Cache 的时候，因为 KeyCache 空间问题使本来命中率很高的 Key 也不得不被清除出去。

MySQL 官方建议在比较繁忙的系统上一般可以设置三个 Key Cache：一个 Hot Cache 使用 20%的大小用来存放使用非常频繁且更新很少的表的索引；一个 Cold Cache 使用 20%的大小用来存放更新很频繁的表的索引；一个 Warm Cache 使用剩下的 60%空间，作为整个系统默认的 Key Cache；多个Key Cache的具体使用方法在MySQL 官方手册中有比较详细的介绍，这里就不再累述了，有兴趣的读者朋友可以自行查阅研究。