MySQL数据库性能优化大总结

影响数据库服务器性能的因素

• 超高的QPS（每秒钟处理的查询量）和TPS导致SQL处理效率下降。
• 大量的并发导致的数据库连接数被占满和超高的CPU占用率导致资源耗尽服务器宕机。
• 磁盘IO性能瓶颈导致数据传输效率下降，计划任务导致磁盘IO下降。
• 网卡IO性能瓶颈，要减少从服务器数量，缓存要分级，避免使用 select * 这样的查询。

大表导致的问题：

• 不同数据库引擎对于大表的概念是不一样的。
• InnoDB存储引擎没有明确的大表概念。
• 实际使用中发现当一个数据表中的数据超过千万行的时候会造成影响。
• 但是对于日志存储引擎来说可能达到10亿条数据也不会出现问题，但是更改表结构会很耗时。
• 大表往往会造成慢查询的产生，因为很难在一定时间内过滤出需要的数据。
• 大表也会对DDL操作造成影响，比如建立索引会很长时间，在Mysql5.5版本之前建立索引会锁表，在5.5及其以后会造成主从延迟。
• 修改表结构也会造成正常的数据操作，因为要锁表，所有会导致短时间内所有SQL操作都被阻塞，连接数会猛增导致Web服务器5xx错误。

大表解决方案：

• 分库分表。
• 跨分区的数据库查询的解决。
• 另一种解决方案就是历史数据归档。
  • 归档时间点的选择。
  • 如何进行归档操作。

大事务导致的问题：

• 原子性：一个事务是一个不可分割的最小单元，执行时要么全部成功，要么全部失败。

  • 一个银行账户向另一个账户转账操作，只能两个操作同时成功才算成功，否则回滚。

• 一致性：事务将数据库从一种一致性状态转换到另一种一致性状态，在事务开始之前和结束之后数据库中的数据完整性没有被破坏。

  • 两个账户转账完成后不应该影响两个账户的总金额。

• 隔离性：要求一个事务对数据库的修改，在未完成提交之前对于其他事务是不可见的。

  • 当在第一个账户中取款以后还没向第二个账户存款时，另一个查询事务统计第一个存款余额时应该没有任何改动。
  • SQL标准中定义的四种隔离级别：（隔离性从低到高，并发性从高到低）
    • 未提交读
    • 已提交读（Mysql不是默认，SQLServer和Oracle是默认）
    • 可重复读（InnoDB默认）
    • 可串行化

• 持久性：一旦事务提交，数据将永久保持到数据库，即使系统崩溃也不会丢失。

• 大事务：运行时间比较长，操作数据比较多。

• 锁定的数据太多导致大量的阻塞和锁超时。

• 回滚所需要的时间比较长。

• 执行时间长，容易造成主从延迟。

大事务解决方案：

• 避免一次性处理较多的数据
• 移除不必要的select操作

影响性能的几个因素

• 服务器的硬件
• 服务器的操作系统
• 数据库系统的存储引擎
• 数据库系统的配置参数
• 数据库表结构设计和SQL语句优化

1. CPU资源和可用内存资源

• CPU的频率高还是CPU的数量大的选择
  • 看应用是否是CPU密集型的，那么就需要更好的CPU，并且当前Mysql也不支持多CPU并行处理
  • 提高并发量就需要多个CPU
  • 为了支持多CPU，尽可能选择新版本Mysql
  • 尽量选择x64位CPU，尽量不要将64位Mysql安装到32位操作系统上
  • 缓存不仅仅会对读取有好处，对写入也有好处，多次缓存数据后一次写入，减少IO操作
  • 尽量选择容量较大的内存，内存频率要尽量高

2. 磁盘的配置和选择

• 使用传统机械硬盘

  • 传统机械硬盘读取数据的过程
    1. 移动磁头到磁表面上的正确位置
    2. 等待磁盘旋转，使得所需的数据在磁头之下
    3. 等待磁盘旋转过去，所需数据被读出
  • 如何选择机械硬盘：
    1. 存储容量
    2. 传输速度
    3. 访问时间
    4. 主轴转速
    5. 物理尺寸

• 使用RAID卡+传统机械硬盘

  • 磁盘冗余队列技术将多个容量较小的磁盘组合成为一个容量较大的磁盘，并且提供数据冗余技术

  • 主要的RAID级别：

    1. RAID0 是最早出现的模式，成为数据条带，是最简单的一种方式，只需要2块以上的硬盘即可，成本较低，可以提高整个磁盘的性能的吞吐量。RAID没有提供荣誉或者错误修复能力，但是实现成本较高。容量是所有磁盘的总和，并且性能是之前的N倍。
    2. RAID1 是磁盘镜像，原理就是把一个数据镜像到另一个磁盘上，最大限度的保证系统的可靠性和可修复性。
    3. RAID5 是分布式奇偶校验磁盘阵列，主要就是把数据分散到多个磁盘上，如果任何一个磁盘损坏都可以快速重建，但是如果2块失效就全部无法恢复。最好使用在以读取为主要任务的服务器上，比如从数据库服务器。
    4. RAID10 分片的磁盘镜像，对磁盘先进行RAID1，然后对两组RAID1进行RAID0，对读写都有较好的性能，重建速度快于RAID5。

  • 如何选择RAID级别：

    特点	是否冗余	盘数	读取	写入
    D0	便宜快速危险	否	N	快
    D1	高速读简单安全	有	2	快
    D5	安全成本折中	有	N+1	快
    D10	昂贵高速安全	有	2N	快

  • 使用SSD和PCIe卡

    • 相对于机械磁盘来说，SSD具备更好的随机读写的性能

    • 更好的支持并发

    • 更容易损坏，每次进行写操作之前都要进行擦除操作，大批量的写操作会导致使用寿命问题，而且当磁盘容量所剩无几的时候性能也会下降。

    • SSD（固态硬盘）：

      • 直接使用SATA接口，当然也会限制SSD的速度。
      • 直接使用SATA接口，同样支持RAID技术。
      • 选择RAID卡的时候要支持SSD硬盘

    • 固态存储PCIe技术：

      • 无法使用SATA接口，需要独特的驱动和配置
      • 性能高于普通SSD的RAID阵列，但是价格要贵，而且占用CPU和内存资源

    • 固态存储的使用场景：

      • 适用于存在大量的随机IO的场景
      • 适用于解决单线程负载的IO瓶颈

• 使用网络存储NAS或SAN

  • SAN（Storage Area Network）和NAS（Network-Attached Storage）是两种外部文件存储设备加载到服务器上的方法

  • SAN的访问通过光纤接口连接到服务器，服务器可以当做硬盘使用

  • SAN可以承载大量的顺序读写操作，但是在随机读写方面性能不高

  • NAS使用网络来连接，通过基于文件的协议，比如NFS或SMB

  • 通常NAS具备更高的网络延迟

  • 网络存储的使用场景：

    • 数据库的访问需要承载大量的随机IO，因此SAN和NAS都不太适合
    • 但是适用于数据库备份

3. 网络的配置和选择

• 网络的带宽
  • 采用高性能和高带宽的网络接口设备和交换机，最好是万兆级别的
• 网络的质量
  • 对个网卡进行绑定，增强带宽和可用性
  • 尽可能对网络进行隔离

4. 操作系统对性能的影响

• 由于Mysql的Schema数据库实际上是一个目录，在Windows系统上对大小写不敏感，在类Unix上对大小写敏感，可能造成错误。
• 通过配置Mysql参数来强制使用小写
• 旧版本的FreeBSD对Mysql的支持不够好
<