MySQL 数据库性能优化之表结构优化

个人觉得这其中可能存在一个非常大的误区。我们在谈论基于硬件进行优化的时候，不能仅仅将数据库使用的硬件划分为主机和存储两部分，而是需要进一步对硬件进行更细的分解，至少也应该分解到如下范畴：

• 主机
  • CPU：仅仅只能决定运算速度，及时是运算速度都还取决于与内存之间的总线带宽以及内存本身的速度
  • 内存：大小决定了所能缓存的数据量，主要决定了热点数据的访问速度
  • 磁盘：
    • 大小：决定了你最终能存放多少数据量
    • 转速：决定了你每一次IO请求的延时时间，也就是决定了我们常说的IOPS和MBPS
    • 数目：磁盘数目决定了
    • 类型
      • 机械：SAS or SATA or FC ？
      • SSD：磁盘 or PCI卡？
  • Raid卡：
    • 缓存：缓存大小对数据写入速度有较大影响，使用策略也会直接影响IO效率
    • 电池：电池充放电策略会影响到瞬时IO的波动
  • 其他：如总线带宽等，决定了CPU与内存间数据传输效率，这一点很多时候关注较少，但也可能会出现瓶颈
• 存储
  • 内存：存储设备同样也有内存，用来存储前端主机访问的热点数据。存储的内存大小同样决定了热点数据的访问速度
  • 磁盘：和主机磁盘类似
  • 线路/环路带宽：环路带宽必须能够匹配磁盘带宽，至少不能少于磁盘所能输出的能力，否则就想被堵在高速收费站等待通行的车辆一样
• 网络
  • 延时：不同的网络设备其延时会有差异，对于 OLTP 设备来说，延时自然是越小越好
  • 吞吐量：对于数据库集群来说，各个节点之间的网络吞吐量可能直接决定集群的处理能力
  • iops：对于 OLTP 系统，数据传输更多是以小IO多并发方式，有时候光有大带宽并不一定能满足需求

硬件角度所能提供的处理能力，一定是上面所列的多个方面（这里仅仅只是主要部分，可能还有其他）共同决定的整体能力，任何一个方面出现瓶颈，都能导致整体性能上不去，也就是我们常说的木桶原理。

在以往的经验中，最容易出现性能瓶颈的地方主要会出现在以下几个方面：

• IO资源方面瓶颈
  出现 IO 资源方面瓶颈的时候，主要表现在服务器 iowait 很高，usr 占比较少，系统响应较慢，数据库中经常会存在大量执行状态的 session。
  遇到 IO 资源方面的瓶颈，我们可以使用的硬件层面优化方案主要就是： 
  • 增加内存加大可缓存的数据量：这个方案能否达到效果取决于系统热点数据的总量，毕竟内存的成本也是比较高的，而且单台设备所能管理的内存量也是有限的
  • 改善底层存储设备的 IO 能力：如本文前面所述，底层存储能力的改善同时取决于多个方面，既有单个磁盘本身的能力问题，也包括磁盘数目方面的策略，同时还受到存储自身以及存储和主机之间的带宽限制。所以在优化底层存储能力的同时需要同时考虑到这3方面的因素，做好总体分析和局部的平衡
• CPU资源方面瓶颈
  当 CPU 方面资源遇到瓶颈的时候，主要表现在服务器CPU利用率中 usr 所占比例很高，iowait却很小。这类问题大多出现在数据量并不是太大，同时又有足够内存来对数据进行缓存的应用场景。同时也是目前大多数中小网站所面临的数据库性能瓶颈。
  当遇到 CPU 方面的资源瓶颈的时候，可能由两个方面造成： 
  • 过多依赖数据库进行逻辑运算：对于这种状况，最好的优化方式是将运算尽可能从数据库端迁移到应用端，降低数据库主机的计算量。毕竟对有状态的系统设备（数据库）进行扩容的成本远高于无状态类系统设备（应用）。当然如果非要从数据库端的硬件来解决问题，那就只有通过增加设备CPU数目（如果支持），或者是使用CPU能力更为高端的主机来替换老主机
  • 数据库逻辑IO太大：对于这类状况，从硬件角度来说能做的就只有提升CPU处理能力。要么增加 CPU 数目（如果支持），要么换CPU更强劲的主机。但是在这之前，还是建议先尝试从应用角度优化看看是否能够尽量降低非必要请求或者是减少每次请求的数据量。同时从数据库角度针对 Schema结构以及索引进行相应的优化调整，尽可能让完成一次请求所需要检索的数据量更小，从而达到降低逻辑IO的目的
• 网络资源方面的瓶颈
  一般来说应用与数据库之间的网络交互所需的资源并不是非常大，所以这个环境遇到瓶颈的可能并不是非常大。但是在分布式的集群环境中，各个数据库节点之间的网络环境经常会称为系统的瓶颈。
  比较常见的场景如 MySQL Cluster 或者是 Oracle RAC 环境中，节点之间的数据交换网络环境的优劣可能直接影响到系统的整体处理能力，因为在节点间会存在大量的数据交换，都是依赖网络传输来完成。
  在这样的场景中，廉价一点的解决方案是通过 万兆交换机 来替换现在常用的 千兆交换机 来提升网络处理能力降低网络延时。不过这个方案主要提升的是吞吐量方面，对于延时方面的提升可能并不一定能满足某些要求非常高的场景。这时候就该考虑使用更为昂贵但也更高效的方案：用 Infiniband 替换普通交换机来极大的降低网络方面所带来的数据交换延时。