数据库影响因素之硬件、存储引擎和事务

影响数据库的因素

  1. Sql查询速度
  2. 服务器硬件
  3. 存储引擎
  4. 大表
  5. 大事务

服务器硬件

CPU

MYSQL不支持多核并发，会带来两方面的风险：

  • 大量的并发使数据库连接数被占满。max_connection 默认为100
  • CPU耗尽导致宕机
    QPS：每秒钟处理的查询量

更多核数还是更高频率视具体情况而定：高并发选更多核；CPU密集和复杂SQL选更高频率

磁盘IO

硬件升级
调整计划任务，避开高峰期，做好磁盘维护

RAID

RAID（Redundant Array of Independent Disks，独立磁盘冗余数组）的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。

RAID 0：将多个磁盘合并成一个大的磁盘，不会有冗余，并行I/O，速度最快。在所有的级别中，RAID 0的速度是最快的。理论上，多磁盘的效能就等于（单一磁盘效能）×（磁盘数），但实际上受限于总线I/O瓶颈及其他因素的影响，RAID效能会随边际递减。也就是说，假设一个磁盘的效能是50MB/s，两个磁盘的RAID 0效能约96MB/s，三个磁盘的RAID 0也许是130MB/s而不是150MB/s。

RAID 1：两组以上的N个磁盘相互作为镜像，在一些多线程操作系统中能有很好的读取速度，但写入速度略有降低。RAID 1的数据安全性在所有的RAID级别上来说是最好的。但是所有RAID中磁盘利用率最低的一个级别。

RAID 5：是一种存储性能、数据安全和存储成本兼顾的存储解决方案。它使用的是Disk Striping（硬盘分区）技术。RAID 5至少需要三个硬盘，RAID 5不对存储的数据进行备份，而是把数据和相对应的奇偶校验信息存储到组成RAID 5的各个磁盘上，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。当RAID 5的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。写入数据的速度相当慢，若使用Write Back可以让性能改善不少。

RAID 10和RAID 01：RAID 10是先镜像再分区数据，将所有硬盘分为两组，视为RAID 0的最低组合，然后将这两组各自视为RAID 1运作。RAID 10有着不错的读取速度，而且拥有比RAID 0更高的数据保护性。RAID 01则与RAID 10的程序相反，先分区再将数据镜射到两组硬盘。RAID 10巧妙地利用了RAID 0的速度及RAID 1的安全（保护）两种特性，它的缺点是需要较多的硬盘，因为至少必须拥有四个以上的偶数硬盘才能使用。

对于数据库应用来说，RAID 10是最好的选择，它同时兼顾了RAID 1和RAID 0的特性。但是，当一个磁盘失效时，性能可能会受到很大的影响，因为条带（strip）会成为瓶颈。

固态硬盘

SATA接口的SSD 也支持RAID
PCI-E卡，如Fusion IO。不支持SATA，RAID成本太高，且会占一定内存，优点是速度快。
固态硬盘适用于存在大量随机I/O的场景，也可用于解决单线程负载的I/O瓶颈——用于单线程的从服务器更好

网卡流量

采用高性能和高带宽的网络接口和交换机
对多个网卡绑定，增强可用性和带宽
减少从服务器数量
进行缓存升级
避免使用select * 查询
分离业务网络和服务器网络

插件式存储引擎

MySQL数据库的核心在于存储引擎。存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。

MySQL由以下几部分组成：
□ 连接池组件
□ 管理服务和工具组件
□ SQL接口组件
□ 查询分析器组件
□ 优化器组件
□ 缓冲（Cache）组件
□ 插件式存储引擎
□ 物理文件

设置引擎create table name () engine=myisam

InnoDB

InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。从MySQL数据库5.5.8版本开始，InnoDB存储引擎是默认的存储引擎。

独立表空间可以用optimize table命令压缩，而系统表空间处理起来则较为复杂，空间很难回收；独立表空间可以提高IO性能

  • 支持事务ACID特性，有redo log和undo log
  • 行级锁，支持更多的并发
  • 适合面向在线事务处理（OLTP）的应用
  • 后来也开始支持全文索引和空间函数

查看状态show engine innodb status

InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。同时，使用一种被称为next-key locking的策略来避免幻读（phantom）现象的产生。除此之外，InnoDB储存引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用的功能。

对于表中数据的存储，InnoDB存储引擎采用了聚集（clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

MyISAM

MySQL 5.5.8版本之前MyISAM存储引擎是默认的存储引擎
不支持事务、表锁设计，缓冲池只缓存（cache）索引文件，而不缓冲数据文件
支持全文索引，主要面向一些OLAP数据库应用。

MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

  • 表锁的设计使得读写操作是互斥的，读写混合的并发性不好
  • 没有事务日志，表修复check/repair table
  • 支持全文索引和前缀索引
  • 只读文件支持数据压缩，myisampack工具使用赫夫曼（Huffman）编码静态算法来压缩数据，因此使用myisampack工具压缩后的表是只读的
  • 在MySQL 5.0版本之前，MyISAM默认支持的表大小为4GB，如果需要支持大于4GB的MyISAM表时，则需要制定MAX_ROWS和AVG_ROW_LENGTH属性。从MySQL 5.0版本开始，MyISAM默认支持256TB的单表数据

适用场景

  1. 非事务型应用，如非财务管理应用
  2. 只读应用，共享锁并发
  3. 空间类应用，支持空间函数，如GPS应用

Memory

Memory存储引擎（之前称HEAP存储引擎）将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。但是表结构还存在，因为放在磁盘上

Memory存储引擎默认使用哈希索引，而不是我们熟悉的B+树索引。所以默认情况下等值查询快，不能进行范围索引。要根据业务需求选择索引

只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型。最重要的是，存储变长字段（varchar）时是按照定常字段（char）的方式进行的，因此会浪费内存

MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集（intermediate result）。如果中间结果集大于Memory存储引擎表的容量设置，又或者中间结果含有TEXT或BLOB列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘中。

Archive

Archive存储引擎只支持INSERT和SELECT操作，从MySQL 5.1开始支持索引。Archive存储引擎使用zlib算法将数据行（row）进行压缩后存储，压缩比一般可达1∶10。正如其名字所示，Archive存储引擎非常适合存储归档数据，如日志信息。Archive存储引擎使用行锁来实现高并发的插入操作，但是其本身并不是事务安全的存储引擎，其设计目标主要是提供高速的插入和压缩功能。

总结对比

考虑因素：事务，备份，错误恢复，存储引擎特征

大表

分库分表：主键的选择，分表后跨分区数据的查询和统计
历史数据归档，减少对前后端业务的影响：归档时间点的选择

大事务

事务：ACID

并发事务带来的问题

  • 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。
  • 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
  • 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
  • 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：不可重复读的重点是修改，幻读的重点在于新增或者删除。

事务隔离级别

SQL 标准定义了四个隔离级别：

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
  • READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
  • REPEATABLE-READ（可重读）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化): 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看
InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化)隔离级别。
InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

大事务解决

问题：锁定太多的数据，造成阻塞和锁超时；回滚时间长；执行时间长

  • 分批处理
  • 移出不必要的select 操作