MyISAM表与innodb表的性能优化

本文章转载自 http://fengzheng369.blog.163.com/blog/static/752209792012512104121771/

#  MyISAM表与innodb表共同可优化的地方.

1. 打开查询缓存

  应用范围:  MyISAM与Innodb都可以使用,因为它在逻辑层中层级比引擎高.

  缓存更新机制:  当一个表有更新操作如update/insert时,这个表相关的所有缓存全部失效.

  
  

   
   query_cache_size = 268432 #单位为byte.
  
  
  
  

   
   query_cache_type = 1      #缓存所有.
  
  
  
  

   
   query_cache_limit = 10485 #不缓存大于这个值的结果. 单位为byte.
  
  

2.优化临时表.

方式:

(1),建立适当索引,避免用临时表. 在explain时可以看到using temporary时,就表明此查询要用到临时表.

(2),无法避免临时表时,要尽量让临时表建立在内存中,而不是在磁盘中,通过mysqlreport中的 Created Temp可以查看当前的临时表的使用情况.

如果配置文件中tmp_table_size=9M所定的值不够大,则会建立到磁盘中,这时可以适当提高此值.

   另,在show processlist时如果状态为copying to tmp table on disk,则表明内存不够用,正在把内容copy到磁盘中.

3.优化线程池

mysql是通过线程来处理并发连接的.

优化方法:

(1). 在应用层使用持久连接.

(2). 通过mysqlreport的threads部分可以看到线程的使用情况.可以适当提高thread_cache_size = 100这个值.

# innodb事务性表的性能优化

1.调节事务日志写入磁盘的时间

  innodb是通过预写日志方式来实现事务. 当有事务提交时,innodb先把它写到内存的事务日志缓冲区,随后当innodb把事务日志写入磁盘时,innodb才更新实际数据及索引,问题的关键点在于, 何时把事务日志写入磁盘.

innodb_flush_log_at_trx_commit  = 1  (默认值,非常耗时,比较慢,但最可靠,不会丢数据)

   机制: 当有事务提交时, 立即将事务日志写入磁盘,数据与索引也立即更新.

   优点: 最符合事务持久性原则.

   缺点: 比较耗时.

innodb_flush_log_at_trx_commit  = 0  (性能最佳,但数据最不安全)

   机制:  每隔1秒把事务日志刷新到内存的磁盘文件,并刷新到磁盘,这时更新数据及索引.

   缺点: 如果mysqld崩溃,1秒的数据将丢失并无法恢复.

innodb_flush_log_at_trx_commit  = 2  (性能比0差一些,但也比数据也比0安全一些,值得考虑)

   机制:  当有事务提交时,立即写入到内存区的磁盘文件,但每隔1秒才会把内存中的磁盘文件向磁盘刷新一次.

   缺点: 如果mysqld崩溃,不会丢失数据,只有系统崩溃才会丢失最近1秒的数据.

2.设置索引与数据的内存缓冲区大小

innodb的索引与数据的内存缓冲区大小调整,如果你大量使用了innodb表,则此值建议设置为物理内存的80%.并通过mysqlreport的Innodb buffer pool关注它的使用率.

    innodb_buffer_pool_size = 1024M

3.设置跳过系统缓冲.

另外 在mysql.ini中加入:

innodb_flush_method = O_DIRECT

   这样可以使innodb跳过文件系统绶冲区,提高性能.