MySQL架构（四）-性能优化整体步骤

不想当咸鱼的咸鱼
362 阅读14分钟

1.优化思路

我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的 动作又是由很多个环节组成的，每个环节都会消耗时间。

我们要减少查询所消耗的时间，就要从每一个环节入手。

2.服务端优化

数据库

从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括CPU、内存、磁盘、网络。

在不同的硬件环境下，操作系统和MySQL的参数的配置是不同的，没有标准的配置。 大多数参数都提供了一个默认值，比如默认的buffer_pool_size，默认的页大小，InnoDB并发线程数等等。

这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义 的情况下再去修改它。修改配置的工作一般由专业的DBA完成。

硬件

至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的CPU型号、选择内存更大、CPU核数更多的机器，这也是一种最简单的解决方案，至于如何搭配就不是我们关心的范围了。

3.连接——配置优化

第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题？

有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error 1040: Too many connections 的错误。

我们可以从两个方面来解决连接数不够的问题：

  1. 从服务端来说，我们可以增加服务端的可用连接数。 或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。
  2. 从客户端来说，可以减少从服务端获取的连接数，可以引入连接池，实现连接的重用

我们可以在哪些层面使用连接池？ORM层面（MyBatis自带了一个连接池）；或者 使用专用的连接池工具（阿里的Druid、Spring Boot 2.x版本默认的连接池Hikari、老 牌的DBCP和C3P0）。

当客户端改成从连接池获取连接之后，连接池的大小应该怎么设置呢？大家可能会 有一个误解，觉得连接池的最大连接数越大越好，这样在高并发的情况下客户端可以获 取的连接数更多，不需要排队。

实际情况并不是这样。连接池并不是越大越好，只要维护一定数量大小的连接池， 其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。 Druid的默认最大连接池大小是8。Hikari的默认最大连接池大小是10。

为什么连接池的大小默认都这么小呢，因为cpu是通过时间片来同时执行远超过他的核数的任务，频繁进行上下文切换会导致性能开销很大。

4.缓存

在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：

  1. 是会给数据库带来很大的压力。
  2. 从应用的层面来说，操作数据的速度也会受到影响。

我们可以用第三方的缓存服务来解决这个问题，例如Redis。

5.数据库集群

  • 这也是作为架构师最应该考虑的解决方案

主从复制

如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。

集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持一致？

这个时候我们需要用到复制技术（replication），被复制的节点称为master，复制的节点称为slave。slave本身也可以作为其他节点的数据来源，这个叫做级联复制。

  1. 一主多从
  2. 多主
  3. 多主一从
  4. 级联复制
  • 主从复制就会产生一种问题，数据的一致性是怎么实现的

主从复制是怎么实现的呢？更新语句会记录binlog，它是一种逻辑日志。

有了这个binlog，从服务器会获取主服务器的 binlog文件，然后解析里面的 SQL 语句，在从服务器上面执行一遍，保持主从的数据一致。

这里面涉及到三个线程，连接到master获取binlog，并且解析binlog写入中继日志，这个线程叫做I/O线程。

Master节点上有一个log dump线程，是用来发送binlog给slave的。从库的SQL线程，是用来读取relay log，把数据写入到数据库的。

做了主从复制的方案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做读写分离。 读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。如果我们在master写入了，马上到slave查询，而这个时候slave的数据还没有同步过来，怎么办？

所以，基于主从复制的原理，我们需要弄明白，主从复制到底慢在哪里？

单线程

在早期的MySQL中，slave的SQL线程是单线程。master可以支持SQL语句的并行执行，配置了多少的最大连接数就是最多同时多少个SQL并行执行。

而slave的SQL却只能单线程排队执行，在主库并发量很大的情况下，同步数据肯定会出现延迟。

为什么从库上的SQL Thread不能并行执行呢？举个例子，主库执行了多条SQL语句，首先用户发表了一条评论，然后修改了内容，最后把这条评论删除了。这三条语句在从库上的执行顺序肯定是不能颠倒的。

异步与全同步

首先我们需要知道，在主从复制的过程中，MySQL默认是异步复制的。也就是说，对于主节点来说，写入binlog，事务结束，就返回给客户端了。对于slave来说，接收到binlog，就完事儿了，master不关心slave的数据有没有写入成功。

这样就会导致如果此时另一个事务开启后，访问数据有可能出现数据没有同步到从数据库，从而查询到的数据是没更新之前的。

如果要减少延迟，是不是可以等待全部从库的事务执行完毕，才返回给客户端呢？这样的方式叫做全同步复制。从库写完数据，主库才返会给客户端。

这种方式虽然可以保证在读之前，数据已经同步成功了，但是带来的副作用大家应 该能想到，事务执行的时间会变长，它会导致master节点性能下降。

  • 引入另一种模式，半同步复制

半同步复制

半同步复制是什么样的呢？

主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到binlog并写到relaylog中才返回给客户端。master不会等待很长的时间，但是返回给客户端的时候，数据就即将写入成功了，因为它只剩最后一步了：就是读取relaylog，写入从库。 相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，它需要等待一个slave写入中继日志，这里多了一个网络交互的过程，所以，半同步复制最好在低延时的网络中使用。

这个是从主库和从库连接的角度，来保证slave数据的写入。

多库并行复制

另一个思路，如果要减少主从同步的延迟，减少SQL执行造成的等待的时间，那有没有办法在从库上，让多个SQL语句可以并行执行，而不是排队执行呢？ 怎么实现并行复制呢？设想一下，如果3条语句是在三个数据库执行，操作各自的数据库，是不是肯定不会产生并发的问题呢？执行的顺序也没有要求。当然是，所以如果是操作三个数据库，这三个数据库的从库的SQL线程可以并发执行。这是MySQL5.6版本里面支持的多库并行复制。

但是在大部分的情况下，我们都是单库多表的情况，在一个数据库里面怎么实现并 行复制呢？或者说，我们知道，数据库本身就是支持多个事务同时操作的；为什么这些 事务在主库上面可以并行执行，却不会出现问题呢？

因为他们本身就是互相不干扰的，比如这些事务是操作不同的表，或者操作不同的 行，不存在资源的竞争和数据的干扰。那在主库上并行执行的事务，在从库上肯定也是 可以并行执行，是不是？比如在master上有三个事务同时分别操作三张表，这三个事务 是不是在slave上面也可以并行执行呢？

GTID 复制

我们可以把那些在主库上并行执行的事务，分为一个组，并且给他们编号，这一个组的事务在从库上面也可以并行执行。

这个编号，我们把它叫做 GTID（GlobalTransaction Identifiers），这种主从复制的方式，我们把它叫做基于GTID的复制。

6.分库分表

我们在做了主从复制之后，如果单个 master 节点或者单张表存储的数据过大的时候，比如一张表有上亿的数据，单表的查询性能还是会下降，我们要进一步对单台数据库节点的数据分型拆分，这个就是分库分表。

  1. 垂直分库，减少并发压力。水平分表，解决存储瓶颈
  • 分库的做法，把一个数据库按照业务拆分成不同的数据库
  1. 水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。

7.高可用方案

通过主从或者分库分表可以减少单个数据库节点的访问压力和存储压力，达到提升数据库性能的目的，但是如果master节点挂了，怎么办？

  1. 主从复制
  2. NDB Cluster
  3. Galera
  4. MHA
  5. MGR
  • 每种方案的具体实现自己了解

高可用HA方案需要解决的问题都是当一个master节点宕机的时候，如何提升一个数据最新的slave成为master。如果同时运行多个master，又必须要解决master之间数据复制，以及对于客户端来说连接路由的问题。

不同的方案，实施难度不一样，运维管理的成本也不一样。

  • 以上是架构层面的优化，可以用缓存，主从，分库分表。

8.SQL语句分析与优化

EXPLAIN

  1. id是查询序列编号，值越小代表开销越小的执行计划
  2. select_type 查询类型，简单查询、子查询、联合查询、衍生查询等等
  3. table 数据来自的表名
  4. type 连接类型 system > const > eq_ref > ref > range > index > all
  5. possible_key 计划用到的索引
  6. key 实际用到的索引
  7. key_len 索引的长度（使用的字节数）。跟索引字段的类型、长度有关。
  8. rows MySQL认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越 好。
  9. filtered 这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。
  10. ref 使用哪个列或者常数和索引一起从表中筛选数据。
  11. Extra 执行计划给出的额外的信息说明。

当我们的SQL语句比较复杂，有多个关联和子查询的时候，就要分析SQL语句有没 有改写的方法。

举个简单的例子，一模一样的数据：

对于具体的 SQL 语句的优化，MySQL官网也提供了很多建议，这个是我们在分析 具体的SQL语句的时候需要注意的，也是大家在以后的工作里面要去慢慢地积累的（这 里我们就不一一地分析了）。

9.存储引擎

  1. 为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用MyISAM。 临时数据用Memeroy。常规的并发大更新多的表用InnoDB。

  2. 分区不推荐。 交易历史表：在年底为下一年度建立12个分区，每个月一个分区。 渠道交易表：分成当日表；当月表；历史表，历史表再做分区。

  3. 原则：使用可以正确存储数据的最小数据类型。 为每一列选择合适的字段类型：

  4. INT有8种类型，不同的类型的最大存储范围是不一样的。 性别？用TINYINT，因为ENUM也是整型存储。

  5. 变长情况下，varchar更节省空间，但是对于varchar字段，需要一个字节来记录长 度。固定长度的用char，不要用varchar

  6. 非空字段尽量定义成NOT NULL，提供默认值，或者使用特殊值、空串代替null。 NULL类型的存储、优化、使用都会存在问题。

  7. 不要用外键、触发器、视图 降低了可读性； 影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储； 数据的完整性应该在程序中检查。

  8. 不要用数据库存储图片（比如base64编码）或者大文件； 把文件放在NAS 上，数据库只需要存储URI（相对路径），在应用中配置 NAS服 务器地址。

  9. 将不常用的字段拆分出去，避免列数过多和数据量过大。比如在业务系统中，要记录所有接收和发送的消息，这个消息是 XML 格式的，用blob 或者text存储，用来追踪和判断重复，可以建立一张表专门用来存储报文。

10.总结

  • 除了对于代码、SQL语句、表定义、架构、配置优化之外，业务层面的优化也不能忽视。

在应用层面同样有很多其他的方案来优化，达到尽量减轻数据库的压力的目的，比 如限流，或者引入MQ削峰等等。

为什么同样用MySQL，有的公司可以扛住百万千万级别的并发，而有的公司几百个 并发都扛不住，关键在于怎么用。所以，用数据库慢，不代表数据库本身慢，有的时候 还要往上层去优化。

当然，如果关系型数据库解决不了的问题，我们可能需要用到搜索引擎或者大数据 的方案了，并不是所有的数据都要放到关系型数据库存储

avatar
文章
阅读
粉丝