实战,让学习更简单     全国咨询热线:400-009-1906

北京 切换校区

全国24小时免费热线

400-009-1906

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

时间:2018-11-26   来源:尚学堂   阅读:71
首页> 针对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 语句重新组织表并压缩任何可能被浪费的空间。

相关资讯

  • 北京校区
  • 山西校区
  • 郑州校区
  • 武汉校区
  • 四川校区
  • 长沙校区
  • 深圳校区
  • 上海校区
  • 广州校区
  • 保定招生办

北京海淀区校区(总部):北京市海淀区西三旗街道建材城西路中腾建华商务大厦东侧二层尚学堂
北京京南校区:北京亦庄经济开发区科创十四街6号院1号楼 赛蒂国际工业园
咨询电话:400-009-1906 / 010-56233821
面授课程: JavaEE培训大数据就业班培训大数据云计算周末班培训零基础大数据连读班培训大数据云计算高手班培训人工智能周末班培训人工智能+Python全栈培训H5+PHP全栈工程师培训

山西学区地址:山西省晋中市榆次区大学城大学生活广场万科商业A1座702
咨询电话:0354-3052381 / 18903441162

郑州学区地址:金水区东风路经三路北100米注协大厦10楼
咨询电话:0371-55177956 

武汉学区地址:湖北省武汉市江夏区江夏大道26号 宏信悦谷创业园4楼
咨询电话:027-87989193

四川学区地址:成都市高新区锦晖西一街99号布鲁明顿大厦2栋1003室
咨询电话:028-65176856 / 13880900114

长沙学区地址:长沙市岳麓区麓源路湖南电子商务基地5楼(公交车站:六沟垅)
咨询电话:0731-83072091

深圳校区地址:深圳市宝安区航城街道航城大道航城创新创业园A4栋210(固戍地铁站C出口)
咨询电话:0755-23061965 / 18898413781

上海尚学堂松江校区地址:上海市松江区荣乐东路2369弄45号绿地伯顿大厦2层
咨询电话:021-67690939

广州校区地址:广州市天河区元岗横路31号慧通产业广场B区B1栋6楼尚学堂(地铁3号线或6号线到“天河客运站”D出口,右拐直走约800米)
咨询电话:020-2989 6995

保定招生办公室

地址:河北省保定市竞秀区朝阳南大街777号鸿悦国际1101室

电话:15132423123

Copyright 2006-2018 北京尚学堂科技有限公司  京ICP备13018289号-19  京公网安备11010802015183  
媒体联系:18610174079 闫老师  

有位老师想和您聊一聊