MySql海量数据存储和优化

MySQL 是最流行的关系型数据库软件之一，由于其体积小、速度快、开源免费、简单易用、维护成本低等，在集群架构中易于扩展、高可用，因此深受开发者和企业的欢迎。

1 MySQL体系架构
MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

• 网络连接层
  客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流的服务端编程技术，例如常见的 Java、C、Python、.NET等，它们通过各自API技术与MySQL建立连接。

• 服务层（MySQL Server）
  服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。

• 存储引擎层（Pluggable Storage Engines）
  存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

• 系统文件层（File System）
  该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

2 MySQL运行机制

3 MySQL存储引擎

存储引擎在MySQL的体系架构中位于第三层，负责MySQL中的数据的存储和提取，是与文件打交道的子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎。
使用show engines命令，就可以查看当前数据库支持的引擎信息。

在5.5版本之前默认采用MyISAM存储引擎，从5.5开始采用InnoDB存储引擎。

• InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全
• MyISAM：不支持事务和外键，访问速度快
• Memory：利用内存创建表，访问速度非常快，因为数据在内存，而且默认使用Hash索引，但是一旦关闭，数据就会丢失
• Archive：归档类型引擎，仅能支持insert和select语句
• Csv：以CSV文件进行数据存储，由于文件限制，所有列必须强制指定not null，另外CSV引擎也不支持索引和分区，适合做数据交换的中间表
• BlackHole: 黑洞，只进不出，进来消失，所有插入数据都不会保存
• Federated：可以访问远端MySQL数据库中的表。一个本地表，不保存数据，访问远程表内容。
• MRG_MyISAM：一组MyISAM表的组合，这些MyISAM表必须结构相同，Merge表本身没有数据，对Merge操作可以对一组MyISAM表进行操作

适用场景

MyISAM

• 不需要事务支持（不支持）
• 并发相对较低（锁定机制问题）
• 数据修改相对较少，以读为主
• 数据一致性要求不高

InnoDB

• 需要事务支持（具有较好的事务特性）
• 行级锁定对高并发有很好的适应能力
• 数据更新较为频繁的场景
• 数据一致性要求较高
• 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO

总结
两种引擎该如何选择？

• 是否需要事务？有，InnoDB
• 是否存在并发修改？有，InnoDB
• 是否追求快速查询，且数据修改少？是，MyISAM
• 在绝大多数情况下，推荐使用InnoDB

4 MySQL集群架构

在集群架构设计时，主要遵从下面三个维度：

• 可用性
• 扩展性
• 一致性

4.1 可用性设计

• 站点高可用，冗余站点
• 服务高可用，冗余服务
• 数据高可用，冗余数据
  保证高可用的方法是冗余。但是数据冗余带来的问题是数据一致性问题。

实现高可用的方案有以下几种架构模式：

• 主从模式
  简单灵活，能满足多种需求。比较主流的用法，但是写操作高可用需要自行处理。
• 双主模式
  互为主从，有双主双写、双主单写两种方式，建议使用双主单写。

4.2 扩展性设计
扩展性主要围绕着读操作扩展和写操作扩展展开。

• 如何扩展以提高读性能
  1、加从库
  简单易操作，方案成熟。
  从库过多会引发主库性能损耗。建议不要作为长期的扩充方案，应该设法用良好的设计避免持续加从库来缓解读性能问题。
  2、分库分表
  可以分为垂直拆分和水平拆分，垂直拆分可以缓解部分压力，水平拆分理论上可以无限扩展。

• 如何扩展以提高写性能
  1、分库分表

4.3 一致性设计
一致性主要考虑集群中各数据库数据同步以及同步延迟问题。可以采用的方案如下：

• 不使用从库
  扩展读性能问题需要单独考虑，否则容易出现系统瓶颈。
• 增加访问路由层
  可以先得到主从同步最长时间t，在数据发生修改后的t时间内，先访问主库。

5 适用场景

5.1 主从模式
MySQL主从模式是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，从节点可以复制主数据库中的所有数据库，或者特定的数据库，或者特定的表。
mysql主从复制用途：

• 实时灾备，用于故障切换（高可用）
• 读写分离，提供查询服务（读扩展）
• 数据备份，避免影响业务（高可用）

主从部署必要条件：

• 从库服务器能连通主库
• 主库开启binlog日志（设置log-bin参数）
• 主从server-id不同

5.2 双主模式

很多企业刚开始都是使用MySQL主从模式，一主多从、读写分离等。但是单主如果发生单点故障，从库切换成主库还需要作改动。因此，如果是双主或者多主，就会增加MySQL入口，提升了主库的可用性。因此随着业务的发展，数据库架构可以由主从模式演变为双主模式。双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。

使用双主双写还是双主单写？
建议大家使用双主单写，因为双主双写存在以下问题：

• ID冲突
  在A主库写入，当A数据未同步到B主库时，对B主库写入，如果采用自动递增容易发生ID主键的冲突。
  可以采用MySQL自身的自动增长步长来解决，例如A的主键为1,3,5,7…，B的主键为2,4,6,8… ，但是对数据库运维、扩展都不友好。
• 更新丢失
  同一条记录在两个主库中进行更新，会发生前面覆盖后面的更新丢失。
  高可用架构如下图所示，其中一个Master提供线上服务，另一个Master作为备胎供高可用切换，
  Master下游挂载Slave承担读请求。

随着业务发展，架构会从主从模式演变为双主模式，建议用双主单写，再引入高可用组件，例如Keepalived和MMM等工具，实现主库故障自动切换。

6 MySQL性能优化
数据库优化维度有四个：硬件升级、系统配置、表结构设计、SQL语句及索引。
优化选择：
优化成本：硬件升级>系统配置>表结构设计>SQL语句及索引。
优化效果：硬件升级<系统配置<表结构设计<SQL语句及索引。

6.1 系统配置优化

• 保证从内存中读取数据
  MySQL会在内存中保存一定的数据，通过LRU算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。扩大innodb_buffer_pool_size，能够全然从内存中读取数据。最大限度降低磁盘操作。
  innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。
  修改 my.cnf
  innodb_buffer_pool_size = 750M
  如果是专用的MySQL Server可以禁用SWAP

• 数据预热
  默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。
  所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。
  数据预热能够提高读取速度。

• 降低磁盘写入次数
  1、增大redolog，减少落盘次数
  innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
  2、通用查询日志、慢查询日志可以不开 ，bin-log开
  生产中不开通用查询日志，遇到性能问题开慢查询日志
  3、写redolog策略 innodb_flush_log_at_trx_commit设置为0或2
  如果不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者事务都非常小，都能够用 0或者 2 来减少磁盘操作。

• 提高磁盘读写性能
  使用SSD或者内存磁盘

6.2 表结构设计优化

• 设计中间表
  设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）

• 设计冗余字段
  为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）

• 拆表
  对于字段太多的大表，考虑拆表（比如一个表有100多个字段）
  对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表

• 主键优化
  张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布
  式系统的情况下 雪花算法）。

• 字段的设计
  数据库中的表越小，在它上面执行的查询也就会越快。
  因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
  尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
  对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
  能用数字的用数值类型

6.3 SQL语句及索引优化
具体可查看阿里的SQL规范等。