10招搞定MySQL大规模数据库的性能和伸缩性优化

拥有大规模数据库的网站始终需要适当的关注、配置、优化、调整和维护，以确保网站的快速加载。本文将讨论如何优化有海量数据的 MySQL 数据库。
数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我 们程序员需要去关注的事情。当我们去设计数据库表结构，对操作数据库时(尤其是查表时的SQL语句)，我们都需要注意数据操作的性能。这里，我们不会讲过 多的SQL语句的优化，而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。

1、选择 InnoDB 作为存储引擎

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

2、InnoDB 的 Change Buffering 特性

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

3、InnoDB 页面压缩

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

4、SQL 语句优化

为了提升查询的速度，可以为 WHERE 字句中使用的列添加索引。此外，不要将主键索引用于太多或太长的列，因为这些列值在辅助索引进行复制的时候会增加读取所需要的 I/O 资源并占用缓存。
如果索引包含了不必要的数据，通过 I/O 读取这些数据并进行缓存就会减弱服务器的性能和伸缩性。也不要为不必要的列使用唯一键索引，因为它会禁用 change buffering。应该使用常规索引代替。减少和隔离需要耗费大量时间的函数调用。尽可能的减少查询中的全表扫描次数。调整缓存区域的大小和属性，比如 InnoDB 缓冲池，MySQL 查询缓存等，这样会通过从内存而非从硬盘获取数据而让重复的查询变得更快。

5、优化数据库结构

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

6、使用批量数据导入

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

7、优化 InnoDB 磁盘 I/O

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

8、优化存储结构

对于大型的表，或者包含大量重复文本或数值数据的表，应该考虑使用 COMPRESSED(压缩的) 行格式。这样只需要较少的 I/O 就可以把数据取到缓冲池，或执行全表扫描。
一旦你的数据达到稳定的大小，或者增长的表增加了几十或几百兆字节，就应该考虑使用 OPTIMIZE TABLE 语句重新组织表并压缩浪费的空间。对重新组织后的表进行全表扫描所需要的 I/O 会更少。

9、MySQL 的内存分配

在为 MySQL 分配足够的内存之前，请考虑不同领域对 MySQL 的内存需求。
要考虑的关键领域是：并发连接 —— 对于大量并发连接，排序和临时表将需要大量内存。在撰写本文时，对于处理 3000+ 并发连接的数据库，16GB 到 32GB的 RAM 是足够的。
内存碎片可以消耗大约 10％ 或更多的内存。像 innodb_buffer_pool_size、key_buffer_size、query_cache_size 等缓存和缓冲区要消耗大约 80％ 的已分配内存。

10、日常维护

定期检查慢的查询日志并优化查询机制以有效使用缓存来减少磁盘 I/O。优化它们，以扫描最少的行数，而不是进行全表扫描。
其他可以帮助 DBA 检查和分析性能的日志包括：错误日志、常规查询日志、二进制日志、DDL日志（元数据日志）。
定期刷新缓存和缓冲区以降低碎片化。使用 OPTIMIZE TABLE 语句重新组织表并压缩任何可能被浪费的空间。
如果不能设计一个合理的数据库模型，不仅会增加客户端和服务器段程序的编程和维护的难度，而且将会影响系统实际运行的性能。所以，在一个系统开始实施之前，完备的数据库模型的设计是必须的。
在一个系统分析、设计阶段，因为数据量较小，负荷较低。我们往往只注意到功能的实现，而很难注意到性能的薄弱之处，等到系统投入实际运行一段时间后，才发现系统的性能在降低，这时再来考虑提高系统性能则要花费更多的人力物力，而整个系统也不可避免的形成了一个打补丁工程。
对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。
参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。
如果一个表只会有几列罢了(比如说字典表，配置表)，那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。
当然，你也需要留够足够的扩展空间，不然，你日后来干这个事，你会死的很难看，参看Slashdot的例子，一个简单的ALTER TABLE语句花了3个多小时，因为里面有一千六百万条数据。

转自微信公众号：Python运维圈