MyException - 我的异常网
当前位置:我的异常网» 数据库 » 针对 MySQL 大规模数据库的性能跟伸缩性的优化

针对 MySQL 大规模数据库的性能跟伸缩性的优化

www.MyException.Cn  网友分享于:2013-08-14  浏览:0次
针对 MySQL 大规模数据库的性能和伸缩性的优化

在需要支持移动/平板电脑应用及普通桌面浏览器访问的时代,网站的普及率和有效性很大程度上取决于其可用性和性能。一个访问缓慢的网站会使得访问者或潜在的客户流失,并导致商业的失败。一个访问速度相当快的网站将会决定访客是否会使用网站提供的产品或服务。

拥有大规模数据库的网站始终需要适当的关注、配置、优化、调整和维护,以确保网站的快速加载。这篇文章将讨论如何优化有海量数据的 MySQL 数据库。

选择 InnoDB 作为存储引擎

大型产品的数据库对于可靠性和并发性的要求较高,InnoDB 作为默认的 MySQL 存储引擎,相对于 MyISAM 来说是个更佳的选择。

优化数据库结构

  • 组织数据库的 schema、表和字段以降低 I/O 的开销,将相关项保存在一起,并提前规划,以便随着数据量的增长,性能可以保持较高的水平。
  • 设计数据表应尽量使其占用的空间最小化,表的主键应尽可能短。
  • 对于 InnoDB 表,主键所在的列在每个辅助索引条目中都是可复制的,因此如果有很多辅助索引,那么一个短的主键可以节省大量空间。
  • 仅创建你需要改进查询性能的索引。索引有助于检索,但是会增加插入和更新操作的执行时间。

InnoDB 的 Change Buffering 特性

InnoDB 提供了 change buffering 的配置,可减少维护辅助索引所需的磁盘 I/O。大规模的数据库可能会遇到大量的表操作和大量的 I/O,以保证辅助索引保持最新。当相关页面不在缓冲池里面时,InnoDB 的 change buffer 将会更改缓存到辅助索引条目,从而避免因不能立即从磁盘读取页面而导致耗时的 I/O 操作。当页面被加载到缓冲池时,缓冲的更改将被合并,更新的页面之后会刷新到磁盘。这样做可提高性能,适用于 MySQL 5.5 及更高版本。

InnoDB 页面压缩

InnoDB 支持对表进行页面级的压缩。当写入数据页的时候,会有特定的压缩算法对其进行压缩。压缩后的数据会写入磁盘,其打孔机制会释放页面末尾的空块。如果压缩失败,数据会按原样写入。表和索引都会被压缩,因为索引通常是数据库总大小中占比很大的一部分,压缩可以显著节约内存,I/O 或处理时间,这样就达到了提高性能和伸缩性的目的。它还可以减少内存和磁盘之间传输的数据量。MySQL 5.1 及更高版本支持该功能。

注意,页面压缩并不能支持共享表空间中的表。共享表空间包括系统表空间、临时表空间和常规表空间。

使用批量数据导入

在主键上使用已排序的数据源进行批量数据的导入可加快数据插入的过程。否则,可能需要在其他行之间插入行以维护排序,这会导致磁盘 I/O 变高,进而影响性能,增加页的拆分。关闭自动提交的模式也是有好处的,因为它会为每个插入执行日志刷新到磁盘。在批量插入期间临时转移唯一键和外键检查也可显著降低磁盘 I/O。对于新建的表,最好的做法是在批量导入后创建外键/唯一键约束。

SQL 语句优化

为了提升查询的速度,可以为 WHERE 字句中使用的列添加索引。此外,不要将主键索引用于太多或太长的列,因为这些列值在辅助索引进行复制的时候会增加读取所需要的 I/O 资源并占用缓存。

如果索引包含了不必要的数据,通过 I/O 读取这些数据并进行缓存就会减弱服务器的性能和伸缩性。也不要为不必要的列使用唯一键索引,因为它会禁用 change buffering。应该使用常规索引代替。

减少和隔离需要耗费大量时间的函数调用。

尽可能的减少查询中的全表扫描次数。

调整缓存区域的大小和属性,比如 InnoDB 缓冲池,MySQL 查询缓存等,这样会通过从内存而非从硬盘获取数据而让重复的查询变得更快。

优化存储结构

对于大型的表,或者包含大量重复文本或数值数据的表,应该考虑使用 COMPRESSED(压缩的) 行格式。这样只需要较少的 I/O 就可以把数据取到缓冲池,或执行全表扫描。

一旦你的数据达到稳定的大小,或者增长的表增加了几十或几百兆字节,就应该考虑使用 OPTIMIZE TABLE 语句重新组织表并压缩浪费的空间。对重新组织后的表进行全表扫描所需要的 I/O 会更少。

优化 InnoDB 磁盘 I/O

增加 InnoDB 缓冲池大小可以让查询从缓冲池访问而不是通过磁盘 I/O 访问。通过调整系统变量 innodb_flush_method 来调整清除缓冲的指标使其达到最佳水平。

使用其它存储设备配置 RAID。

MySQL 的内存分配

在为 MySQL 分配足够的内存之前,请考虑不同领域对 MySQL 的内存需求。

要考虑的关键领域是:并发连接 —— 对于大量并发连接,排序和临时表将需要大量内存。在撰写本文时,对于处理 3000+ 并发连接的数据库,16GB 到 32GB的 RAM 是足够的。

内存碎片可以消耗大约 10% 或更多的内存。像 innodb_buffer_pool_size、key_buffer_size、query_cache_size 等缓存和缓冲区要消耗大约 80% 的已分配内存。

日常维护

定期检查慢的查询日志并优化查询机制以有效使用缓存来减少磁盘 I/O。优化它们,以扫描最少的行数,而不是进行全表扫描。

其他可以帮助 DBA 检查和分析性能的日志包括:错误日志、常规查询日志、二进制日志、DDL日志(元数据日志)。

定期刷新缓存和缓冲区以降低碎片化。使用 OPTIMIZE TABLE 语句重新组织表并压缩任何可能被浪费的空间。

文章评论

 程序员的样子
程序员的样子
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
我是如何打败拖延症的
我是如何打败拖延症的
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
我的丈夫是个程序员
我的丈夫是个程序员
程序员应该关注的一些事儿
程序员应该关注的一些事儿
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
老程序员的下场
老程序员的下场
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
程序员都该阅读的书
程序员都该阅读的书
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
中美印日四国程序员比较
中美印日四国程序员比较
每天工作4小时的程序员
每天工作4小时的程序员
编程语言是女人
编程语言是女人
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
10个调试和排错的小建议
10个调试和排错的小建议
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
一个程序员的时间管理
一个程序员的时间管理
旅行,写作,编程
旅行,写作,编程
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
那些争议最大的编程观点
那些争议最大的编程观点
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
为什么程序员都是夜猫子
为什么程序员都是夜猫子
漫画:程序员的工作
漫画:程序员的工作
代码女神横空出世
代码女神横空出世
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
Java程序员必看电影
Java程序员必看电影
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
程序员必看的十大电影
程序员必看的十大电影
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有