大型数据集的MySQL优化

原文：MySQL Tuning for Large Datasets
作者： Rob Gravelle
译者：仲培艺，关注数据库领域，纠错、寻求报道或者投稿请致邮：zhongpy@csdn.net。

诸多知名大公司都在使用MySQL，其中包括Google、Yahoo、NASA和Walmart。此外，其中部分公司的表囊括数十亿行，却又性能极佳。虽然很难保持MySQL数据库高速运行，但面对数据堆积，可以通过一些性能调整，来使其继续工作。本文则将围绕这一问题展开讨论。

导论

设计数据库之前，有必要先了解一下表的使用方法。例如，对于需要频繁更新的数据，最好将其存入一个独立表中，而通过这样的分表，更新操作将更加快捷。同时，表的连接操作也会消耗时间，所以若要深入分析复杂数据，则最好选用大表。惯有认知下，归一化可通过清除冗余来减少数据。然而，归一化也有其负面作用：它会极大地增加索引查找量。考虑到其需要随机访问读取标准硬盘，故而有时也会选用反归一化。

虽然新加载的数据库能够很好地有序运行，但随着数据库进一步扩展，这种有序操作将难以保持，从而导致更多的随机I/O和性能问题。

尽管“反归一化”可能颠覆了一些传统认知，但随着“元数据”理念兴起，为求性能和扩展性的双重提升，包括Google、eBay和Amazon在内的众多主要参与者，都对其数据库进行了“反归一化”调整。更有甚者，传统思维上的这一转变，还在众多数据库设计人员中掀起了这样的言论：归一化是弱者的选择。

用InnoDB取代MyISAM

InnoDB具有change buffering特性（5.5版本之前，又名insert buffer），它能减少磁盘I/O（要求保留二级索引），而名称变更则大大提升了其性能。它可通过多种途径提升插入性能，且受到默认支持。

在表尾插入方面，MyISAM速度更快，但在其和磁盘间的数据加载过程中，为了保护key buffer，MyISAM用到了表锁和一个single lock，从而导致争用。同时，它也不具备上面提到的change buffering特性。

相关阅读

• MySQL Numeric Overflow Gotcha
• An Overview of the MySQL Performance Schema
• MySQL Date Gotchas
• Importing Into MySQL from Other Databases

压缩InnoDB表

InnoDB的另一大优势就是它支持表压缩（有助于提高其原始性能和扩展性），它还具有双重效用：减少磁盘和内存间的数据传送；增加磁盘和内存中的压缩存储。此外，因为索引数据本身就是压缩状态，所以对于包含二级索引的表来说，这些优势又将进一步扩大。不止如此，通过SSD（闪存盘）存储设备，压缩的益处会达到最大化，原因是其容量小于传统HDD（旋转硬盘驱动）设备。

按主键顺序批量导入数据

进行批量插入时，按照主键顺序插入行，速度会更快。但InnoDB按该顺序对行进行机械化排布时，如果需要在其他行中间再插入行，就会导致页面分割（极大损害内存外表的性能）。

硬件优化

很久之后才能开始变更MySQL的设置，但如果在次优硬件上操作，则不会造成什么影响。

内存

写入时采用16到32GB的RAM应当是效果最佳的。

处理能力

MySQL（5.5版本）全面采用多线程处理，因此在操作系统支持的情况下，可实现多处理器操作。尽管出于扩展性的需求，很多DBAs能支持更多处理器，但在这一点上，两个双核CPU已能满足需求。

操作系统

只要能支持64位进程，选用什么样的O/S并不重要。大多数DBAs都选择64位的LVM（逻辑卷管理器）来提高备份效率。

存储

存储的标准协议，是将其连接至数个spindle和RAID（独立磁盘冗余阵列）。新版2.5 SAS（串行连接SCSI接口）硬盘驱动器虽然很小，通常却比传统大型驱动器运行得更快。

如上所述，在某些情况下，可以使用SSD（特别当需要执行多项写入时）。假设驱动器转速为7200 RPM，则IPOS（每秒输入输出操作）不可能超过100，而SSD的速度则至少是它的五倍。若基于较为优质的SSD，则IOPS可达到20000甚至更高。由此看来，如果面对巨量内存，且只想清除其中20%的数据，可利用MySQL将其存入内存。

方法和技巧

为保持数据库高效运行，方法和技巧如下：

从大容量的insert/read表开始，分析所有表的索引：移除不必要的索引；特别关注唯一索引（禁用change buffering）。除非必要，否则不使用唯一索引，代之以普通索引。

每隔一至两周，查看一次慢查询日志，从中筛选出三项速度最慢的查询，并加以优化。

若加载一个备份从服务器，卸载大容量读取查询的备份，因为这也会导致信息冗余。

总结

论及数据库优化，所有方法归根结底都是泛型建议。因此，进一步评估之前，并不能保证这些方法就适用于某些特定的操作或模式。此外，还有许多本文未曾涉及的方法，可以用来优化MySQL服务器。例如，MySQL包含许多服务器变量，它们都可以进一步优化，且在不久的将来，这些发展就会实现。

更多Rob Gravelle的文章

2016年8月12日-13日，由CSDN重磅打造的互联网应用架构实战峰会、运维技术与实战峰会将在成都举行，目前18位讲师和议题已全部确认。两场峰会大牛讲师来自阿里、腾讯、百度、京东、小米、乐视、聚美优品、YY、华为、360等知名互联网公司，共同探讨高可用/高并发/高性能系统架构设计、电商架构、分布式架构、运维工具研发与实践、运维自动化系统的构建、大数据与运维、云上的运维案例分析、虚拟化技术、应用性能检测与管理、游戏行业的运维实践等，将和与会嘉宾共同探讨「构建更安全、更高性能、更稳定的架构和运维体系」等领域的话题与技术。【目前限时6折，点击这里抢票】

7月15日24点前仍处于最低六折优惠票价阶段，单场峰会（含餐）门票只需499元，5人以上团购或者购买两场峰会通票更有特惠，限时折扣，预购从速。（票务详情链接）。