MySQL5.7优化之优化总览

本文主要讲解如何优化MySQL性能，以及提供样例。优化包括几个层面的配置，微调和性能测试。依赖于你工作的角色（开发者、DBA、两者都是），你可能在单独的SQL语句、整个应用、单独的数据库服务器、多个网络数据库服务器几个层面优化。有时候你可能积极地制定优化任务，而其他时候你可能是在代码或配置发生问题后做故障排查。优化CPU和内存的使用也能提高伸缩性，使数据库能够在不用关机的情况下处理更多负载。

优化总览

数据库优化依赖于数据库层面的几个因素，例如表、查询、配置。软件的初始化会在硬件级别消耗CPU和I/O操作，你必须最小消耗的和尽可能高效的使用。在你处理数据库性能时，你开始为软件学习高级规则和引导，并且使用wall-clock time测试性能。当你成为专家，你学习更多的是内部发生了什么，并且开始测试CPU周期和I/O操作。

典型的用户旨在用他们现有的软件和硬件得到数据库最好的性能。高级用户寻找机会优化MySQL本身，或者开发他们自己的存储引擎和硬件应用去扩展MySQL生态系统。

数据库层面优化

使数据库应用快的最重要影响因素是基础设计：

  • 表结构是否合适？特别是，这些列用正确的数据类型吗？每个表都有适用于工作的列类型吗？例如：频繁更新的应用通常有很多表，这些表有很少的列；分析大数据的应用通常有很少的表，但是它们有很多列。

  • 是否有正确的索引来提高查询效率？

  • 是否为每一张表使用了合适的存储引擎？并且利用好了你使用到的每个存储引擎的优点和特性？特别是，选择一个像InnoDB事务型存储引擎或像MyISAM这样的非事务型存储引擎对于性能与可扩展性是非常重要的。 注：在MySQL5.5之后，InnoDB默认为新表的存储引擎。在实践中，InnoDB先进的性能特性意味着它经常胜过简单的MyISAM表，尤其是在繁忙的数据库情况下。

  • 每个表是否有适当的行格式？这个选择依赖被使用的表使用的存储引擎。特别是，被压缩的表使用少的磁盘空间，所以需要少的磁盘I/O去读和写数据。压缩适用于所有种类的InnoDB表和只读的MyISAM表。

  • 应用是否有合适的锁策略？例如：在可能的情况下使用共享访问，以便于数据操作能同时运行；适当的时候请求独占访问，以便于关键操作获取优先权。再一次说，存储引擎的选择是重要的。InnoDB存储引擎在不干涉你的情况下处理大多数锁问题，使数据库更好的应对并发，并且减少你的测试和调整代码次数。

  • 用于缓存的所有内存大小是否正确？更确切的说，要有足够大的内存来保证频繁的数据访问，但是太大会导致物理内存负担太重并且导致分页（casue paging，不懂）。主要的内存区域配置是InnoDB缓冲池，MyISAM的key缓存以及查询缓存。

硬件级别优化

在数据库越来越繁忙的时候，任何数据库应用最终会遇到硬件限制。DBA必须评估是否可以去调整应用或者重新配置服务来避免瓶颈，或者是否需要更多的硬件资源。系统瓶颈通常源于这些原因：

  • 磁盘寻道。磁盘需要一段时间来寻找数据。对于现代的磁盘，做这件亊的平均时间通常低于10毫秒，所以理论上每秒可以做100次寻道。对于新磁盘，时间的提高是很慢的，单表优化是非常困难的。优化寻道时间的优化方式是将数据分布在多个磁盘上。

  • 磁盘读和写。当磁盘在正确的位置时，我们需要去读和写数据。对于现代磁盘，磁盘驱动提供至少10-20MB/S吞吐量。这要比优化寻道更容易，因为你能从多个磁盘并行读取。

  • CPU周期。当数据在主内存的时候，我们必须处理它来得到结果。相对与内存来说的大表是最普遍的限制因素。对于小表来说，速度通常没有问题。

  • 内存带宽。当CPU需要比CPU缓存更多的数据时，主存带宽就成了瓶颈。这对于大多数系统来说不是问题，但是要注意。

平衡可移植性和性能

在一个可移植的MySQL项目中使用面向性能的SQL扩展，你可以在/*! */注释符号里包含MySQL特定的关键字语句。其他的SQL服务器忽略注释关键字。

（完）