文档章节

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

爱mili
 爱mili
发布于 2016/01/04 10:29
字数 1816
阅读 28
收藏 1

       数据库属于 IO密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化:

  query_cache_size/query_cache_type (global)

  Query cache 作用于整个 MySQL Instance,主要用来缓存 MySQL 中的 ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache 功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache,或者已经显式申明需要使用Query Cache),MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash,然后到Query Cache 中直接查找是否已经缓存。也就是说,如果已经在缓存中,该select请求就会直接将数据返回,从而省略了后面所有的步骤(如 SQL语句的解析,优化器优化以及向存储引擎请求数据等),极大的提高性能。

  当然,Query Cache 也有一个致命的缺陷,那就是当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache 中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache 可能会得不偿失。

  Query Cache的使用需要多个参数配合,其中最为关键的是 query_cache_size 和 query_cache_type ,前者设置用于缓存 ResultSet 的内存大小,后者设置在何场景下使用 Query Cache。在以往的经验来看,如果不是用来缓存基本不变的数据的MySQL数据库,query_cache_size 一般 256MB 是一个比较合适的大小。当然,这可以通过计算Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))来进行调整。query_cache_type可以设置为0(OFF),1(ON)或者2(DEMOND),分别表示完全不使用query cache,除显式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache,只有显示要求才使用query cache(使用sql_cache)。

  binlog_cache_size (global)

  Binlog Cache 用于在打开了二进制日志(binlog)记录功能的环境,是 MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。

  一般来说,如果我们的数据库中没有什么大事务,写入也不是特别频繁,2MB~4MB是一个合适的选择。但是如果我们的数据库大事务较多,写入量比较大,可与适当调高binlog_cache_size。同时,我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够,是否有大量的binlog_cache由于内存大小不够而使用临时文件(binlog_cache_disk_use)来缓存了。

  key_buffer_size (global)

  Key Buffer 可能是大家最为熟悉的一个 MySQL 缓存参数了,尤其是在 MySQL 没有更换默认存储引擎的时候,很多朋友可能会发现,默认的 MySQL 配置文件中设置最大的一个内存参数就是这个参数了。key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存,这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引,以尽可能提高性能。

  此外,当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意,由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中,而不会缓存表数据库块。所以,我们的 SQL 一定要尽可能让过滤条件都在索引中,以便让缓存帮助我们提高查询效率。

  bulk_insert_buffer_size (thread)

  和key_buffer_size一样,这个参数同样也仅作用于使用 MyISAM存储引擎,用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候,会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件:

  insert … select …


  insert … values (…) ,(…),(…)…


  load data infile… into… (非空表)

  innodb_buffer_pool_size(global)

  当我们使用InnoDB存储引擎的时候,innodb_buffer_pool_size 参数可能是影响我们性能的最为关键的一个参数了,他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小,类似于 MyISAM 存储引擎的 key_buffer_size 参数,当然,可能更像是 Oracle 的 db_cache_size。简单来说,当我们操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会在这个内存区域中走一遭。

  和key_buffer_size 对于 MyISAM 引擎一样,innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小,直接关系到 InnoDB存储引擎的性能,所以如果我们有足够的内存,尽可将该参数设置到足够打,将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中,直至全部。

  我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率,并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。

  innodb_additional_mem_pool_size(global)

  这个参数我们平时调整的可能不是太多,很多人都使用了默认值,可能很多人都不是太熟悉这个参数的作用。innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,所以当我们一个MySQL Instance中的数据库对象非常多的时候,是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

  这个参数大小是否足够还是比较容易知道的,因为当过小的时候,MySQL 会记录 Warning 信息到数据库的 error log 中,这时候你就知道该调整这个参数大小了。

             想必各位看了这篇文章之后一定会有所收货,若想了解更多相关知识请继续锁定希赛系统学院

本文转载自:http://www.educity.cn/os/

爱mili
粉丝 3
博文 33
码字总数 22919
作品 0
崇明
私信 提问
抛开复杂的架构设计,MySQL优化思想基本都在这了

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷...

赵栩彬
04/08
0
0
掌握MySQL数据库这些优化技巧,事半功倍!

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的,它是随着用户量的增加,基础架构才逐渐完善。这篇文章主要谈谈MySQL数据库在发展周期中所面临的问题及优化方案,暂且抛...

李振良OK
2018/06/26
0
0
mysql性能优化(一)

mysql性能优化、慢查询分析、优化索引和配置 一.每项的基本思路步骤 1.性能瓶颈定位:show命令、慢查询日志、explain分析查询、profiling分析查询、 2.索引及查询优化 3.配置优化 二.mysql是...

攀岩人生
2017/06/16
0
0
浅谈MySQL数据库优化

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的,它是随着用户量的增加,基础架构才逐渐完善。这篇博文主要谈MySQL数据库发展周期中所面临的问题及优化方案,暂且抛开前...

wangergui
2017/03/15
0
0
MySQL性能管理及架构设计:SQL查询优化、分库分表

一、SQL查询优化(重要) 1.1 获取有性能问题SQL的三种方式 通过用户反馈获取存在性能问题的SQL; 通过慢查日志获取存在性能问题的SQL; 实时获取存在性能问题的SQL; 1.1.2 慢查日志分析工具...

whs0668
2018/03/27
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Cesium中级4 - 空间数据可视化(二)

Cesium中文网:http://cesiumcn.org/ | 国内快速访问:http://cesium.coinidea.com/ Viewer中的Entity功能 让我们看看Viewer为操作entities提供出来的功能函数。 选中和描述 点击Viewer中的e...

Cesium中文网
26分钟前
1
0
Linux-CentOS7 安装VMware Workstation

Linux-CentOS7 安装VMware Workstation 太极之道关注0人评论1605人阅读2018-10-17 14:36:22 Linux-CentOS7 安装VMware Workstation 12 1、下载VMware 衔接地址 http://www.vmware.com/produc......

linjin200
28分钟前
1
0
centos7.5 部署flask+nginx+uwsgi+python3

centos7.5 部署flask+nginx+uwsgi+python3 ## uwsgi [uwsgi] master = true max-requests = 6000 processes = 6 threads = 6 chmod-socket = 664 thunder-lock = true buffer-size = 32768 #......

丁典
32分钟前
3
0
BigData NoSQL —— ApsaraDB HBase数据存储与分析平台概览

一、引言 时间到了2019年,数据库也发展到了一个新的拐点,有三个明显的趋势: 越来越多的数据库会做云原生(CloudNative),会不断利用新的硬件及云本身的优势打造CloudNative数据库,国内以阿...

阿里云官方博客
37分钟前
2
0
好代码是什么样的?

简评: 好代码是什么样的?相信不同的人会有不同的看法,正如「一千个读者,就有一千个哈姆雷特」,关键在于你有没有自己的标准。 这篇文章是我个人很喜欢的一家公司 - Atomic Spin 的 blog...

极光推送
37分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部