浅谈MySQL 数据库性能优化

标签: mysql 数据库 性能优化 | 发表时间:2013-12-13 02:00 | 作者:顺其自然EVO
出处:http://www.blogjava.net/qileilove/
  MySQL 数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理 工作。本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存, 日志缓存,索引缓存,innodb缓存,插入缓存,以及连接参数等方式来对MySQL数据库进行优化。
   缓存参数
  这里先引用一句话,从内存中读取一个数据的时间消耗是微秒级别,而从普通硬盘上读取一个数据是在毫秒级别,二者相差3个数量级。可见,想对MySQL数据库进行优化,合理调配缓存参数显得更为直接
   表缓存
  相关参数: table_open_cache
  指定表缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值,如果发现open_tables等于table_cache,并且opened_tables在不断增长,那么就需要增加table_open_cache的值了。注意,不能盲目地把这个参数设置得很大,如果设置太大,会引起文件描述符不足,造成性能不稳定或者数据库连接失败。建议为512
   查询缓存
  相关参数: query_cache_size / query_cache_type
  QC(注:查询缓存简称)  主要用来缓存 MySQL 中的 结果集,也就是一条 SQL语句执行的结果集,所以仅仅只能针对select 语句。如果启用了QC 功能,MySQL在接到select 请求后,如果该语句满足QC的要求,MySQL 会直接根据HASH算法将接收到的select 语句以字符串方式进行hash,然后到QC中直接查找,如果已经在缓存中,该select 请求就会直接将数据返回,从而省略了后面所有的步骤(如 SQL语法解析,优化器优化以及存储引擎请求数据等),极大的提高性能。
  当然,QC也有一个致命的缺陷,就是当表中数据有变化时,所有引用到该表的 QC 缓存全部失效。所以,当数据变化非常频繁的情况下,使用QC 反而得不偿失。
  QC 的使用需要多个参数配合,其中最为关键的是 query_cache_size 和 query_cache_type ,前者设置缓存记录集的内存大小,后者设置在何场景下使用QC 。
  在以往的经验来看,中等规模的网站,query_cache_size 设置 256MB 足够了。当然,还可以通过计算QC的命中率来进行调整。
  Qcache_hits / (Qcache_hits + Qcache_inserts)  * 100%
  query_cache_type有三种选择:0(OFF,不使用QC),1(ON,默认使用QC ),2(DEMAND,默认不使用QC)。
  为什么加上“默认”?MySQL还支持动态使用缓存的SQL语法,如下:
  # 强制使用缓存
  SELECT SQL_CACHE id FROM table
  # 强制不使用缓存
  SELECT SQL_NO_CACHE id FROM table
   日志缓存
  相关参数:binlog_cache_size
  用于在打开了二进制日志(binlog)记录功能的环境中,是 MySQL 用来提高 binlog 的记录效率而设计的一个在短时间内缓存binlog 数据的内存缓存。
  如果数据库中没有大事务,写入不是特别频繁,2MB~4MB是一个合适的选择。但是如果数据库大事务较多,写入比较频繁,可适当加大。使用的时候,还可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够,是否有大量的binlog_cache由于内存大小不够而使用临时文件来缓存了。


索引缓存
  相关参数:key_buffer_size
  这个是对MyISAM表性能影响最大的一个参数,用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果有足够的内存,这个缓存区域大小可以设为所有的 MyISAM表的索引大小的总和,即 data 目录下所有*.MYI文件大小的总和。
  注意,由于 MyISAM 引擎只会缓存索引块到内存中,而不会缓存表数据库块。所以,查询SQL语句一定要尽可能让过滤条件都在索引中,以便使用到索引缓存来提高查询效率。
  计算索引缓存未命中的概率:
  Key_reads / Key_read_requests * 100%
   插入缓存
  相关参数:bulk_insert_buffer_size
  用于使用 MyISAM引擎,用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候,会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件,默认8M,建议不要超过32M
  insert … select …
  insert … values (…),(…),(…),…
  load data infile… into… /* 非空表 */
  InnoDB缓存
  相关参数:innodb_buffer_pool_size / innodb_additional_mem_pool_size
  innodb_buffer_pool_size参数是影响InnoDB存储引擎性能的最为关键的一个参数,设置用于缓存 InnoDB 索引及数据块的内存区域大小,类似于 MyISAM 存储引擎的 key_buffer_size 参数,当然,可能更像是 Oracle 的 db_cache_size。简单来说,当操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会涉及到这个内存区域。
  innodb_buffer_pool_size 参数设置了 InnoDB 存储引擎需求最大的一块内存区域的大小,直接关系到 InnoDB存储引擎的性能,所以如果有足够的内存,尽可能加大该参数的值,将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中。
  当然,可以通过计算缓存命中率,并根据命中率来调整这个参数的大小:
  (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%
  innodb_additional_mem_pool_size 参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。随着数据库对象越来越多,需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。这个参数的大小是相对稳定的,没有必要预留非常大的值。如果InnoDB引擎用光了这个池内的内存,InnoDB引擎就开始从操作系统申请内存,并往MySQL错误日志写警告信息。默认值是1MB,当发现错误日志中已经有相关的警告信息时,就应该适当的增加该参数的大小。
  innodb_log_buffer_size 参数是 InnoDB 存储引擎的事务日志所使用的缓冲区。类似于 Binlog Buffer,InnoDB 在写事务日志的时候,为了提高性能,也是先将信息写入 Innofb Log Buffer 中,当满足 innodb_flush_log_trx_commit 参数所设置的相应条件(或者日志缓冲区写满)之后,才会将日志写到文件(或者同步到磁盘)中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。
  innodb_flush_log_trx_commit 参数对 InnoDB引擎日志的写入性能有非常关键的影响。该参数可以设置为0,1,2,如下:
  0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;
  1:在每次事务提交的时候将log buffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;
  2:事务提交会触发log buffer 到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。
  此外,MySQL文档中还提到,这几种设置中的每秒同步一次的机制,可能并不会完全确保非常准确的每秒就一定会发生同步,还取决于进程调度的问题。实际上,InnoDB 能否真正满足此参数所设置值代表的意义正常 Recovery 还是受到了不同 OS 下文件系统以及磁盘本身的限制,可能有些时候在并没有真正完成磁盘同步的情况下也会告诉 mysqld 已经完成了磁盘同步。
  innodb_max_dirty_pages_pct 参数用来控制在 InnoDB 缓冲池(Buffer Pool) 中可以不用写入数据文件中的脏页(Dirty Page) 的比例(已经被修但还没有从内存中写入到数据文件的脏数据)。这个比例值越大,从内存到磁盘的写入操作就会相对减少,所以能够一定程度下减少写入操作的磁盘IO。但是,如果这个比例值过大,当数据库崩溃(Crash)之后重启的时间可能就会很长,因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。同时,过大的比例值同时可能也会造成在达到比例设定上限后的 flush 操作“过猛”而导致性能波动很大。如果这个参数设置过大,将会导致MySQL启动时间过长,关闭时间也过长。
   连接参数
  MySQL数据库操作是建立在MySQL数据库连接的基础上,所以提高MySQLl处理连接的能力,也是提高MySQL的性能的一个重要体现。
   连接数量
  相关参数:max_connections / back_log
  max_connections参数设置MySQL的最大连接数,也就是允许同时连接的客户数量。如果服务器的并发连接请求比较大,建议调高此值,以增加并行连接数量。但连接数越大,MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,服务器消耗的内存越多,可能会影响服务器性能,所以要根据服务器的配置适当调整该值,不能盲目提高设值。默认数值是100。
  计算MySQL繁忙时处理连接的情况,建议值50% ~ 80%
  max_used_connections / max_connections * 100%
  back_log参数设置MySQL能暂存的连接数量。当MySQL在一个很短时间内收到非常多的连接请求时起作用。如果MySQL的连接数达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。试图设定back_log高于你的操作系统的限制将是无效的。默认数值是50。
   连接超时
  相关参数:wait_timeout / interactive_timeout
  服务器关闭连接之前等待活动的秒数。MySQL所支持的最大连接数是有限的,因为每个连接的建立都会消耗内存,因此我们希望MySQL 处理完相应的操作后,应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接,他们不仅会白白消耗内存,而且如果连接一直在累加而不断开,最终肯定会达到MySQL Server的连接上限数,这会报'too many connections'的错误。对于wait_timeout的值设定,应该根据系统的运行情况来判断。在系统运行一段时间后,可以通过show processlist命令查看当前系统的连接状态,如果发现有大量的sleep状态的连接进程,则说明该参数设置的过大,可以进行适当的调整小些。建议120 ~ 300


连接检查
  相关参数:skip-name-resolve
  skip-name-resolve参数用于禁止DNS的反向解析。MySQL默认开启了DNS的反向解析,当有新的连接到来时,MySQL会解析连接主机的DNS,这就影响了连接速度。使用该参数也有一个代价,就是每次连接都要使用ip地址,就不能再使用localhost,改成127.0.0.1
   配置建议值
  根据以往数据库处理经验取值,仅供参考,不一定适用于所有场景,建议在生产环境中进一步分析调整
MySQL配置 建议值说明
table_open_cache 如果设置太大,会造成系统不稳定或者数据库连接失败,建议512
query_cache_type 如果全部使用innodb引擎,建议为0,如果使用MyISAM引擎,建议为1,如果写入过于频繁,建议为2
query_cache_size 根据实际命中率进行调整,不需要太大,建议256MB
binlog_cache_size 一般环境2MB~4MB是一个合适的选择,事务较大且写入频繁的数据库环境可以适当调大,但不要超过32MB
key_buffer_size 如果不使用MyISAM引擎,可以不调整。如果使用MyISAM,在内存允许的情况下,尽可能加大,参考值 512MB
bulk_insert_buffer_size 如果经常性的需要使用批量插入的特殊语句(上面有说明)来插入数据,可以适当调大至32MB
innodb_buffer_pool_size 如果不使用InnoDB引擎,可以不用调整;如果使用InnoDB,在内存允许的情况下,可以设置50% ~ 80%内存
innodb_log_buffer_size 默认是1MB,数据库操作频繁的系统可适当增大至4MB ~ 16MB
innodb_max_dirty_pages_pct 这个值越大,数据库启动时间和关闭时间越长,可以适当调大至90
max_connections 根据实际情况取值,设置过大反而影响性能。默认值是100,建议256 - 512
back_log 默认数值是50,建议 128 - 256
wait_timeout 同时修改interactive_timeout,默认28800(8小时),建议120 ~ 300

  可能用到的MySQL命令:
  # 查看当前MySQL运行状态值
  mysql> show global status like 'Thread_%';
  # 查看当前MySQL配置信息
  mysql> show global variables like '%binlog%';


顺其自然EVO 2013-12-13 10:00 发表评论

相关 [mysql 数据库 性能优化] 推荐:

浅谈MySQL 数据库性能优化

- - BlogJava-qileilove
数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理. 本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存,. 日志缓存,索引缓存,innodb缓存,插入缓存,以及连接参数等方式来对MySQL数据库进行优化.   这里先引用一句话,从内存中读取一个数据的时间消耗是微秒级别,而从普通硬盘上读取一个数据是在毫秒级别,二者相差3个数量级.

MySQL 数据库性能优化之表结构

- tangfl - Sky.Jian 朝阳的天空
接着上一篇 MySQL 数据库性能优化之缓存参数优化 ,这是 MySQL数据库性能优化专题 系列的第二篇文章:MySQL 数据库性能优化之表结构. 很多人都将 数据库设计范式 作为数据库表结构设计“圣经”,认为只要按照这个范式需求设计,就能让设计出来的表结构足够优化,既能保证性能优异同时还能满足扩展性要求.

MySQL 数据库性能优化之缓存参数优化

- flychen50 - Sky.Jian 朝阳的天空
在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助. 这是 MySQL数据库性能优化专题 系列的第一篇文章:MySQL 数据库性能优化之缓存参数优化.

MySQL数据库性能优化之存储引擎选择

- - Sky.Jian 朝阳的天空
MySQL 数据库性能优化之SQL优化,这是  MySQL数据库性能优化专题 系列的第五篇文章:. MySQL数据库性能优化之存储引擎选择. 离上一篇文章已经有很长时间没有更新这个MySQL数据库性能优化专题了,时间太紧加上人之惰性,今天这里将之前就规划好的关于存储引擎选择方面的内容更新出来,希望对大家有所帮助吧.

MySQL数据库性能优化之硬件瓶颈分析

- - Sky.Jian 朝阳的天空
接着上一篇 MySQL数据库性能优化之存储引擎选择,这是 MySQL数据库性能优化专题 系列的第六篇文章: MySQL数据库性能优化之硬件优化. 在过往与很多人的交流过程中发现,在谈到基于硬件来进行数据库性能瓶颈分析的时候,常被大家误解为简单的使用更为强劲的主机或者存储来替换现有的设备. 个人觉得这其中可能存在一个非常大的误区.

MySQL 数据库性能优化之SQL优化

- - OurMySQL
注:这篇文章是以 MySQL 为背景,很多内容同时适用于其他关系型数据库,需要有一些索引知识为基础. IO永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是 IO 操作所占用的,减少 IO 次数是. SQL 优化中需要第一优先考虑,当然,也是收效最明显的优化手段.

MySQL数据库性能优化之表结构优化

- - haohtml's blog
由于MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了. 反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能.

mysql数据库性能优化的关键参数及mysql服务器优化

- - CSDN博客数据库推荐文章
MySQL数据库性能优化的关键参数. 关键参数一: back_log. 要求 MySQL 能有的连接数量. 当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程. back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中.

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

Mysql性能优化

- - 数据库 - ITeye博客
MySQL性能优化.   性能优化是通过某些有效的方法来提高MySQL的运行速度,减少占用的磁盘空间. 性能优化包含很多方面,例如优化查询速度,优化更新速度和优化MySQL服务器等.   数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能. 语法:SHOW STATUE LIKE ‘value’;其中value参数是常用的几个统计参数.