优化前应该确定的:
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_readskey_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内存优化内容完毕。