MySQL内存优化

优化前应该确定的:

1. 允许MySQL能使用的最大内存;

2. 允许MySQL为每个连接使用多少内存;

 

以下均以64位系统下的MySQL5.5为讨论，参考MySQL官方文档(本地文档)个别设置应该由使用的表引擎是MyISAM还是InnoDB而定，以下论述以采用单独引擎，如果系统采用混合引擎请按照下文叙述适当调整。其中的蓝色内容为MySQL系统变量(Variable)，红色内容为MySQL系统状态(Status)。

 

一、MyISAM  Key Buffer

默认情况下，MySQL只有一个Key Buffer，其大小由key_buffer_size指定，默认为8M(最大不超过4G-1...无符号int，以前5.0的时候有限制到2G)

MyISAM引擎和InnoDB的一个区别，他只缓存了索引，数据是交给操作系统去缓存的，所以对MyISAM引擎的全局方面的内存优化就放在对Key Buffer的优化上。

最优的情况当然是Key Buffer能缓存下所有的表的索引，也要估计好一定时间范围内的增长，这个估计是建立在预先的测试上的，在此不论。

对表当前索引的大小的估计统计方法 可以在数据库执行:

mysql> show table status;

其中的Index_length列就是对应表的索引占用的字节数，叠加即可，不过需要注意Engine列所指定的引擎应该为MyISAM

这个数据的出处是在information_schema库的tables表，也可以在那边查到对应的数据。

 

由于单个Key Buffer有个最大4G(那1个字节差距我们下面就掠过不提了)的限制，我们可以创建扩展Key Buffer，配置多Key Buffer方法是在my.cnf中增加一句（要多加几个就加几句咯），其中开头的部分是扩展的Key Buffer的名字，可以根据自己需要定义，后面跟上这个key buffer的大小，5.0的时候有听说单个Key Buffer只能使用到2G的bug(64位机上仍然如此)：

key_buffer_ext1.key_buffer_size=1G

这样设置后，默认的key buffer仍然是有效的，并且默认情况下MySQL仍然只会使用默认的Key Buffer。所以我们还需要在MySQL执行如下一句，告诉MySQL把指定表的索引加载到指定的扩展Key Buffer中：

mysql> cache index table_1, table_2 in key_buffer_ext1;

一般情况mysql会随着使用不断的缓存Key，也可以用下面一句令MySQL直接加载整个指定表的索引，这样不用每次用到再缓存提升效率：

mysql> load index into cache table_1, table_2;

一般我们是会把这两句添加到配置文件init_file变量指定的文件中，让MySQL在启动的时候执行：

init_file=load_key.sql

 

一般情况下，Key Buffer应该在我们允许MySQL使用的内存的25%～50%，当然如果应用中的索引大小并不会徒增的情况下，考虑当前索引的总大小增长即可。

 

另外要注意的是，MyISAM引擎的数据本身是交给操作系统去缓存的，在分配时，应考虑这个在给MySQL分配内存外，操作系统本身也应该有更 多的空闲内存。另外虽然key_buffer_size只是对MyISAM引擎有影响，但即使业务表都不是MyISAM的，因为MySQL的系统表很多还 是MyISAM的，所以Key Buffer多少还是需要的。

 

对于Key Buffer的监控主要是使用mysqladmin extended-status命令以一定单位间隔获取key_reads和key_read_requests的增量。他们分别是从磁盘加载索引的次数和命中缓存的次数。注意这两个只是一个从服务器启动开始的计数器，一定要使用增量的比较才有意义。

 

对MyISAM来说，有个有意思的查询特性，被称为Covering Index，对于复合主键，如果我们使用第一字段搜索，查询索引的第二字段(注意顺序)，也就是查询使用和返回的字段其实都包括在同个索引中，这时服务器 根本就不会去管数据文件，直接利用索引数据返回结果，这时如果所有的索引都包含在Key Buffer中了的话，就单纯是内存操作了。这也教育我们没事不要随便来个select *...

 

二、InnoDB Buffer Pool

前面提到，InnoDB和MyISAM不同，它的Buffer Pool除了索引外，也要保存数据以及一些临时结构等，所以通常情况下采用InnoDB的表引擎，MySQL数据库进程都会占用更多的内存，相反的，他不 必给操作系统留太多内存来缓存数据。(按照以前的经验，我们将表从MyISAM转InnoDB后，实际运营系统MySQL进程内存占用至少增加了一倍)。 另外InnoDB也会使用缓冲池来实现延迟磁盘写入以合并零碎的IO操作提高性能更重要的是避免数据碎片(所以innodb表不用Optimize table也不会坏表)。如上所述，InnoDB的性能可以说是严重依赖Buffer，所以在MySQL的官方文档上建议，把80%的MySQL占用内存 全部分给InnoDB Buffer Pool(当然说的是全部业务表都是InnoDB的情况)。对应的参数是innodb_buffer_pool_size

 

与MyISAM不同，InnoDB没有有load index之类的命令让服务器一次性加载所有数据和索引到内存，他只能逐渐加载，逐渐增大内存占用。当然考虑在正式环境前预热服务器，可以对所有业务表进行全表扫描和全索引扫描。

 

另外有个变量innodb_max_dirty_pages_pct可以调整InnoDB保留在Buffer Pool中被修改的页面数，不过通常的建议是采用默认值。

另外使用InnoDB时有个需要注意的地方，如前文提到的，InnoDB会延迟写入，这个带来的问题是，在MySQL服务器正常关闭的时候，它 会花更多的时间来把Buffer Pool中的修改页面写入到数据文件中。当然可以强制关闭进程，但是这样操作包括MySQL异常crash或者遇到机器重启等问题后启动MySQL时，就 需要花相当的时间从ib_logfile中恢复数据(虽然是自动过程，之前我遇到的情况这个时间可以以十分钟为单位来算了，而且这个时间内MySQL是不 提供服务的)。有个做法是，当有可以提前确定的服务器关闭或重启前，通过set命令改小innodb_max_dirty_pages_pct的值，可以减少关闭前需要从Buffer Pool中刷到数据文件中的页面数，以加快关闭过程，当然这一段时间内的服务器性能可能会有不良影响。

 

可以通过命令 show status like 'innodb_buffer_pool%'查询到InnoDB Buffer Pool的相关状态。

 

三、Query Cache

设置查询缓存大小的参数是query_cache_size，默认值为0(没看错，没分配)，这个值没有什么特别明确的要求，实际上是要考虑实际使用数据库的业务的情况的。其他相关的设置有query_cache_limit，当一个查询的返回结果集超过这个大小，他是不会缓存的，他的默认值是1M，一般是够用了，应用程序通常也不应该频繁查询结果集过大的数据。

 

对查询缓存的监控可以看show status like 'qcache%'语 句返回的服务器状态。根据返回结果中的hits(命中次数)，insert(查询后结果添加到缓存中的次数)，queries_in_cache(直接从 缓存中返回结果的次数)，我们可以估计出查询缓存的命中率。但是和前面提到的两种Buffer不同，查询缓存命中率的高低是需要结合具体业务来考虑的。如 果表的数据经常变动，比如游戏行业里面用户的游戏状态数据，那么这些查询通常都无法从缓存中来，即使缓存了，下一次因为数据的变化，仍然需要重新查询。所 以对查询缓存的利用主要是集中在查询频繁，但是数据基本上不会变化的语句上。

 

要注意的一个问题是，InnoDB提供了事务支持，这带来额外的问题是如果事务获取了一个表的写锁，他会连这个表在查询缓存里面的缓存结果一起锁掉。

另外上文提到的在数据变动频繁的时候，即使结果被缓存了，很快这个缓存数据也会失效，这没意义的一来一去仍然是要消耗服务器的资源的，如何避免 这类问题呢。一个方法就是全局关闭查询缓存。另外一个办法就是在查询语句上使用sql_no_cache显示的声明不缓存结果，例如：

mysql> select sql_no_cache count(*) from table_1;

，当然这个会造成代码在移植到其他的数据库系统中的二次开发成本。

 

最后要说一点mysql在这个问题上的不足，mysql对查询缓存的匹配是采用的未被任何处理的原始sql来命中的，应用程序发过来的是什么就 是什么。哪怕字符大小写，参数顺序，是否省略数据库或者表名，甚至空白字符的位置只要有一点不同都会造成无法命中查询缓存...所以开发中对sql语句的 书写一定要有严格的规范。

 

补充：

在《高性能MySQL》书中对查询缓存优化还有以下的观点：

1、使用小表而不是大表，使更新带来的缓存失效机制工作在一个更好的粒度上，但带来的好处有限，不应成为更改业务应用架构的理由；

2、批更新而不是逐个执行，这样只会产生一次缓存失效的操作；

3、查询缓存不应太大，缓存失效或者清理缓存(flush query cache)是会造成本身服务挂起，查询缓存设置为256M已经太大了。

4、反过来，如果只有少部分查询能从查询缓存中获益，可以将query_cache_type设置为DEMAND(默认是ON，即尽可能缓存)，并在这些语句中使用sql_cache来声明需要缓存查询。

 

四、Thread Cache

线程池的概念和功能就不用多介绍了，用thread_cache_size变量可以指定线程池的容量下限，这个容量建议是略大于等于系统当前消耗线程数，另一方面线程池容量大基本上不会带来什么坏处，默认情况下一个线程只会额外消耗256K的内存。但带来的好处确实明显的，特别是对于短连接业务。

 

MySQL上，通常一个连接会消耗线程池中的一个线程，在系统状态中，threads_connected表示了现在使用的线程数，对他进行定时监控即可。另外一说threads开头的几个状态：threads_running是指的是当前活动线程数(长连接后不执行语句其占用的线程会sleep但不会返回到线程池中直到断开连接)，这个正常情况通常应该是小于当前服务器的CPU核数的。threads_created表示的是实际向操作系统申请创建线程的计数器，如果线程池中的线程够用的情况下，这个值使用mysqladmin获取增量时应该长期不变，如果增长剧烈应该考虑检查并调高线程池容量。

 

线程池的分配除了受MySQL自身的限制外，也要受到操作系统的限制(Linux下ulimit -u)

 

五、Table Cache

表缓存指的是MySQL长期保持表的索引或数据文件句柄的，他保证在IO时不用重复打开表的相关文件。他是由table_open_cache定义的。

 

由上文很容易知道，表缓存最好的设置就是略大于等于当前表的数目，这个进系统数一下便知(或者查一下tables视图)。对于系统状态而言如果opened_tables不断增加，那么说明设置的表缓存是不够用的。

 

一般情况下，InnoDB是统一的数据文件，所以表缓存优化对InnoDB基本上是没有什么效果的，当然通过设置innodb_file_pre_table让InnoDB为每个表单独创建数据文件，这一般只会在系统中有成千上万的大型表的情况下才会设置。

 

和线程池一样的，文件句柄数也是要受操作系统限制的(ulimit -n)，另一方面MyISAM的参数open_file_limit和InnoDB参数innodb_open_files也产生同样的限制，如果表很多（特别是数据库错误日志提示不能打开更多文件时）应调大这两个数值。

 

调大表缓存带来的负面影响是在于MyISAM表关闭是需要更多的时间释放文件句柄，解锁文件。这也是我们通常在运行时下直接复制MyISAM表数据文件做备份时需要先flush table在数据恢复后需要对表使用myisamchk进行修复的原因。

 

到此MySQL内存优化内容完毕。