MySQL5.7优化之优化总览

2017-06-19, 1622 Words, 4 Minutes

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服务器忽略注释关键字。

(完)


Next:
Prev: