mysql 优化心得

lb5645284

  mysql的优花其实是个艰难的工作，要搞的东西太多了，之前在http://www.cnblogs.com/jackyrong/archive/2008/05/04/1182331.html
中摘了一些原则，最近对一个100多万条数据的表做优花时，有如下心得：

1） 取必须要用的数据
    这里对于select 语句中只选有用的字段，这样的原则就肯定人人都知道的了。但关键的是，要从全局考虑问题，比如我的这个应用
是每个新闻网页的跟帖，一条新闻有很多很多人跟帖，平均有40-50条，那么每天这么多新闻，很多的跟帖记录，但对于审帖
者，由于是每天上班工作的，每天他们会把当天的帖子审核掉，因此，在做记录列表时，只需要选用出当前时间以内1-2天的记录就可以了，
这里已经是大大优化
2 ） 建好索引
     这里的学问太多，也太多文章讲，这里不说
3 ） MYSQL的优化
      第三个工作才是MYSQL的优化，其实就是改my.ini，有如下几点，归纳下.(我的是4G内存的双核机器）
   A 设置key_buffer_size
   key_buffer_size指定索引缓冲区的大小，这个值越高，索引可以使用的内存越多，一般为可用内存的25-30%，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。

 B max_connections数量
     MYSQL建议使用table_cache=max_connection*N来设置tablecache,N为标准连接中表的数量
 C table_cache
  
table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
D 提高order by groud by的速度，通过设置sort_buufer变量进行控制，还可以增加read_rnd_buffer_size变量值，也可以增加read_buffer_size值，提高select的查询速度
 E query_cache_size
从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238％的效率。
通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_lowmem_prunes增长迅速，意味着很多缓存因为内存不够而被释放，而不是因为相关表被更新。尝试加大query_cache_size，尽量使Qcache_lowmem_prunes零增长。
如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。