深入MySQL实战 (it-ebooks) (Z-Library)

📄 File Format: PDF

💾 File Size: 3.6 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

MySQL电子书 深入MySQL实战 快速理解MySQL核心技术 业内资深大咖联合出品，详细解读AliSQL 在双11等高并发场景下的应用与实践

📄 Page 2

1 MySQL电子书 3 12 30 49 68 82 91 MySQL高可用——MGR8.0 最佳实践 MySQL高并发场景实战 RDS MySQL Java 开发实战 MySQL查询优化 MySQL 开发规约实战 RDS for MySQL 表和索引优化实战 从研发角度深入了解RDS AliSQL内核2020新特性 深入MySQL实战 微信关注公众号：阿里云数据库 第一时间，获取更多技术干货 阿里云开发者“藏经阁” 海量免费电子书下载

📄 Page 3

3 4MySQL电子书 MySQL高可用 MGR8.0 最佳实践 （一）MGR是什么 （一）MGR插件组成 （二）单主模式 （二）示例 1. MGR的定义 MGR是具备强大的分布式协调能力，可用于创建弹性、高可用性、高容错的复制拓扑的一个MySQL插件。 2. 通讯协议 基于Paxos算法的GCS原子广播协议，保证了一条事务在集群内要么在全部节点上提交，要么全部回滚。 3. 组成员资格 MGR内部提供一个视图服务，集群节点之间相互交换各自的视图信息， 从而且实现集群整体的稳态。 4. 数据一致性 MGR内部实现了一套不同事务之间修改数据的冲突认证检测机制。在集群的所有节点当中进行一个冲突认证检测，反 之，通过冲突认证检测的事务即可提交成功。 当一个事务发起提交后，它会通过原子广播协议分发到集群其他Secondary节点。集群的Secondary节点通过冲突检测之 后，事务提交成功。在大多数的Secondary节点提交成功之后，会在Primary节点进行提交。 反之，如果在冲突认证检测失败，Secondary节点会丢弃这段事务对应的Binlog，Primary节点回滚该事务。 上图是一个三节点的MGR实例集群，Member1代表Primary节点，Member2、Member3代表Secondary节点。 如上图所示，MGR插件使用 MySQL Server 与 API 接口层以及若干组件，最后由GCS（Group Communication System） 协议封装而成。 MySQL Server调用MGR插件是基于MySQL现有的主从复制，利用Row格式的Binlog和Gtid等功能实现的集群架构。 API接口层复制基于MySQL Server交互的接口集，在逻辑上将MySQL内核与MGR插件隔绝开来。其他组件例如Capture 组件，它是负责事务状态在集群内提交或是回滚，以及通过Binlog event广播到其他节点上进行的冲突认证检测进行到哪个 阶段。Apply组件代表MGR集群Secondary节点Binlog回放，Recovery组件代表进行崩溃恢复或集群扩容时增量数据的 应用。 ON表示单主模式，也是默认模式，OFF表示多主模式。 如下图所示，在单主模式下（group_replication_single_primary_mode = ON）: 1．该变量在所有组成员中必须设置为相同的值，同一个组中，不能将成员部署在不同模式中。例如，一个成员配置为单主 模式，另一个成员配置为多主模式。 2．该集群具有一个设置为读写模式的主节点，组中的所有其他成员都设置为只读模式（superread-only = ON）； MGR架构分为单主模式和多主模式。 MGR特性 集群架构作者：张彦东 Member 1 Member 2 Member 3 execute certify binlog binlog commit commitcertify relay log apply binlog commitcertify relay log apply Consensus MySQL Server APIs：Capture / Apply / Lifecycle ApplierCapture Recovery Replication Protocol Logics Group Communication System API M yS Q L G ro u p R e p lic a tio n P lu g in Group Group Communication Engine (Paxos variant)

📄 Page 4

5 6MySQL电子书 3.读写节点通常是引导该组的第一个节点，加入该集群的所有其他只读节点均需要从读写节点同步数据，并自动设置为只 读模式。 单主模式集群原理流程图 多主模式集群原理流程图 同步原理示例 冲突检测原理 Write Clients Server S1 is the primary. Read Clients S1 S2 S3 S4 S5 Write Clients ? Server S1 fails. Read Clients S1 S2 S3 S4 S5 Write Clients Server S2 is the new primary. Read Clients S2 S3 S4 S5 （三）多主模式 在多主模式下（group_replication_single_primary_mode = OFF）: 1.所有节点不会区分Primary和Standby角色； 2.加入该集群时，与其他组成员兼容的任何节点都被设置为读写模式，并且可以处理写请求，即使它们在集群内是并发执 行的； 3.如果组复制中的某个节点停止接受写事务，例如，在某个节点意外宕机的情况下，可以将与其连接的客户端重定向或故 障转移到处于读写模式的任何其他健康的节点； 4.组复制本身不处理客户端故障转移，因此需要使用中间件框架（例如MySQL Router 8.0代理，连接器或应用程序本 身）来实现。 如上图所示，以一个三节点的MGR集群为例。在单主模式下，当一个事务发起提交，它会通过原子广播协议将事务伴随 着Binlog Event广播到其他Secondary节点上。在获得集群大多数节点同意之后，它会进行一个提交。如果通过冲突认证 检测，那么该事务最终会在集群当中提交。 如果在Secondary节点上面没有通过冲突认证检测，那么Secondary节点丢弃该事务对应的Binlog，Primary节点回滚该 事务。 如上图所示，在冲突检测时： 1.每个事务的Gtid Set和对应的主键Hash值组成事务认证列表，在每个节点的内存当中都维护这样一个冲突检测库。 2.Gtid set：标记数据库的快照版本，事务提交前从gtid_execute变量中获取该值； Write Clients All server are primaries. Read Clients S1 S2 S3 S4 S5 Write Clients ? Server S1 fails. Read Clients S1 S2 S3 S4 S5 Write Clients S1’s client connects to S3. Read Clients S2 S3 S4 S5 （一）同步原理示例 （二）冲突检测原理 数据同步原理 Prepare Binlog Commit COMMIT DB1 DB3 TX TX Succeeds ERROR OK Falls Certify Rollback Paxos DB2 Relay log Apply TX Succeeds Falls Certify Drop Paxos DB2 DB1 DB3 PK HASH db1.tb1.pk=1 db1.tb1.pk=2 …… GTID SET group_name:1-10 group_name:1-15 ……

📄 Page 5

7 8MySQL电子书 3.事务提交前，数据库中执行了的Gtid集合，随着Binlog中的Event通过原子广播的方式分发到集群的所有节点上进行事 务冲突检测。 如上图所示，以T1与T2这两条Update语句为例。 若T2修改的数据在冲突检测数据库中无匹配记录，则判定为通过冲突检测认证； 若T2中的GTID SET包含了冲突检测数据库中相同主键值的GTID SET，则冲突认证检测通过； 冲突认证检测通过后，每个节点的冲突检测数据库按照如下规则变更： 1.若在冲突检测数据库中无匹配记录，则向其中插入一条新的记录。 2.如果有记录，则更新冲突检测数据库中的GTID SET值。 若T1修改的数据在冲突检测数据库中有匹配到记录，且T1的GTID SET不包括冲突检测数据库中的GTID SET，则判定为 冲突检测不通过。 注意：冲突检测不通过时，不会更新认证数据库里的GTID SET。 目前业内存在以下问题： 1.MGR可确保仅在集群中的大多数节点都已收到事务，并就并发发送的所有事务之间的相对顺序达成一致后才提交事务。 相对顺序意味着，在分发到Secondary节点之后，可以不按照Primary节点提交的顺序进行提交，只需保证和集群的一致 性即可。 2.在流量小的时候不存在任何的性能问题。当流量突增时，如果集群中某些节点的写入吞吐量比其他节点少，尤其是小于 Primary节点，则这些节点的数据和Primary节点的数据存在偏差。例如说在集群当中，一个3节点的集群，如果节点之间 服务性能存在差异的话，则会存在性能问题。 3.在单主模式的集群中，如果发生故障转移，在新的Primary节点可以立刻接受写入请求的情况下，则存在集群内事务一 致性的问题。 举例说明，当集群扩容时，例如由3节点集群扩容到5节点集群： 1.无论采用Clone的方式还是用Xtrabackup做全量数据恢复后，都避免不了在集群扩容期间产生的增量数据以二进制日志 的方式来追平； 2.若新扩容的节点配置较低，写入吞吐差，则新加入的节点很有可能一直处于Recover的状态，该节点很难达到Online的 状态。 在进行高可用切换之后，存在事务一致性保证，这是由于Secondary节点和Primary节点存在追数据的过程，如果数据 没有追平，那么业务数据可能会读到旧的数据，用户可以根据group_replication_consistency参数对应的可选值进行调 整，总共有5个值如下： 1. EVENTUAL （三）冲突检测示例 DB2 DB1 DB3 PK HASH …… T1 update tb1 set xid=2 where PK=1 gtid_executed: group_name:1-100 gtid_executed: group_name:1-100 T2 update tb1 set xid=3 where PK=1 T1:update tb1 set xid=1 where pk=1(snapshot:group_name:1-100) T2:update tb1 set xid=2 where pk=1(snapshot:group_name:1-100) certify db1.tb1.pk=1 …… GTID SET …… update group_name:1-50 …… PK HASH …… db1.tb1.pk=1 …… GTID SET …… group_name:1-101 …… PK HASH …… T1:update tb1 set xid=1 where pk=1(snapshot:group_name:1-100) certify db1.tb1.pk=1 …… GTID SET …… No need to update group_name:1-101 …… （一）存在的问题 （二）事务一致性保证 性能分析

📄 Page 6

9 10MySQL电子书 开启该级别的事务（T2），事务执行前不会等待先序事务（T1）的回放完成，也不会影响后序事务等待该事务回放完成。 2.BEFORE 开启了该级别的事务（T2），在开始前首先要等待先序事务（T1）的回放完成，确保此事务将在最新的数据上执行。 3.AFTER 开启该级别的事务（T1），只有等该事务回放完成。其他后序事务（T2）才开始执行，这样所有后序事务都会读取包含其 更改的数据库状态，而不管它们在哪个节点上执行。 4. BEFORE_AND_AFTER 开启该级别等事务（T2），需要等待前序事务的回放完成（T1）； 同时后序事务（T3）等待该事务的回放完成。 5. BEFORE_ON_PRIMARY_FAILOVER 在发生切换时，连到新主的事务会被阻塞，等待先序提交的事务回放完成； 这样确保在故障切换时客户端都能读取到主服务器上的最新数据，保证了一致性。 用户根据不同的级别，根据业务上是读多写少，还是写多读少，或者是读写均衡的请求，不同的场景选择不同的值即可。 1.流控机制的功能 性能的问题对应着解决方案，MGR的流控机制试图解决以下问题： 1）集群内节点之间不会相差太多的事务； 2）快速适应集群内不断变化的负载，例如集群内的写压力暴增或集群中增加更多的节点； 3）均分可用写容量到集群内的所有节点上； 4）避免减少吞吐量而造成资源浪费。 2.基本机制 流控机制存在两个基本机制： 1）监控集群内所有节点以收集有关吞吐量和队列大小的一些统计信息，以便对每个节点能承受的最大写入压力进行有 根据的评估； 2）对集群中的所有节点的并发写能力时刻保持监控，一旦某节点的并发压力超过了集群中所有节点的平均写能力，就 会对其执行流量控制。 3.基本队列 流控机制存在两个基本队列：认证队列和二进制日志应用队列。 当其中一个队列的大小超过用户定义的阈值时，就会触发流量控制机制。 对于流量控制配置： 首先，需要选择对谁配置流量控制，是对认证队列、还是针对应用队列、还是两者都需要配置流量控制。然后，对需要配 置流量控制的对象（认证队列和应用队列）设置流量控制阈值。 4.流控过程 流控具体过程如下： 1）将根据上一阶段延迟的事务数量逐步减少10%，让触发限流机制的队列减小到限流机制被触发的阈值之内， 待到恢 复后，为避免在队列大小超过阈值时出现吞吐量的陡增，在此之后，每个时间段的吞吐量只允许增长相同的10%； 2）当前的限流机制不会影响到触发限流机制阈值内的事务，但是会延迟应用超过阈值的事务，直到本监控周期结束； 3）如果触发节流机制的阈值设置的非常小，部分事务的延迟可能会接近一个完整的监控周期。 在日常业务中，MGR高可用集群存在如下经典适用场景： 1.弹性复制 需要非常灵活的复制基础设施的环境，其中MySQL Server的数量必须动态增加或减少，并且在增加或减少Server的过程 中，对业务的副作用尽可能少。例如，云数据库服务。 2.高可用分片 分片是实现写扩展的一种流行方法。基于MGR实现的高可用分片，其中每个分片都会映射到一个复制组上（逻辑上需要 一一对应，但在物理上一个复制组可以承载多个分片）。 3.替代主从复制 在某些情况下，使用一个主库会造成单点争用。在某些情况下，向整个组内的多个成员同时写入数据，多种模式可以避免 单点争用，对应用来说可能伸缩性更强。 （三）流控机制 适用场景

📄 Page 7

11 12MySQL电子书 上图为业内的一个常用的高可用方案。 通常在云数据库里，一个三节点的MGR集群本身不保证业务的写入重定向，那么在MGR集群上面加一个读写分离的中间 件Maxscale。 将源代码重新编译之后，会打开一个它自带的保活机制，Maxscale会自动探测MGR集群里Primer节点状态，如果发生高 可用切换或者是当前的Primary节点宕机之后，它会重新探测选取出来一个新的Primary节点，然后自动将业务写请求重定 向到新的Primary节点上。业务只需要将Client端经过SQL解析连到Maxscale，然后经Maxscale做一个路由转发，即可 实现一个灵活的高可用。 高可用方案 Member 1 SQL解析 路由转发 Member 2 Member 3 execute clientclient client certify binlog binlog commit commitcertify relay log apply binlog commitcertify relay log apply Consensus maxscale MySQL高并发场景实战 阿里巴巴CEO逍遥子曾说过：“双11是商业界的奥林匹克。” 如上图所示，双十一购物始于2009年，历年的订单创建、支付笔数与交易总额都是成倍增长，这不仅带来许多商业机遇， 也给后端技术、架构等各个模块带来技术沉淀。 双11为MySQL带来了高并发场景的问题与挑战，主要表现在： 1）洪峰般的并发 根据市场部部门的推广和引流、历年双11的经验，大促的起始时刻呈现接近90度上升趋势。在这么大访问流量下，所有的 核心链路的增删改查都是在数据库上操作，对数据库有比较大的冲击，在大量线程并发工作时线程调度工作过多、大量缓 存失效、资源竞争加剧、锁冲突严重，如果有复杂SQL或大事务的话还可能导致系统资源耗尽，整个数据库服务不可用， 进而导致大促收到影响，甚至失败，比如：下单失败、网页无法打开、无法支付等。此外此类场景也会发生在在线教育、 直播电商、在线协同办公等。 2）热点行更新 问题和挑战 作者：凌洛

📄 Page 8

13 14MySQL电子书 库存扣减场景是一个典型的热点问题，当多个用户去争抢扣减同一个商品的库存（对数据库来说，一个商品的库存就是数 据库内的一行记录），数据库内对同一行的更新由行锁来控制并发。当单线程（排队）去更新一行记录时，性能非常高， 但是当非常多的线程去并发更新一行记录时，整个数据库的性能会跌到趋近于零。 3）突发SQL访问 当缓存穿透或异常调用、有数据倾斜SQL、未创建索引SQL等情况发生时，在高并发场景下很容易导致数据库压力过大， 响应过慢，导致应用链接释放慢，导致整个系统不可用。 4）智能化运维 双十一期间这些实例的水位管控，机器水位管控，高风险实例识别，高风险实例优化，在流量高峰期从收到报警、识别问 题、解决问题至少需要十多分钟，如果处理不及时峰值已经过去，导致大促失败。 此外，结合业务还有商品超卖、资源的挑战等问题存在。 sysbench /usr/share/sysbench/oltp_read_write.lua -- mysql-host={host} --mysql-port={port} -- mysql-user={user} --mysql-password={passwd} --mysql-db={db} --db-driver=mysql -- tables={count} --table-size={size} --report interval={interval} --threads={threads} -- time={time} prepare/run/clean 此外还有mysqlslap，ab… 1.性能评测的意义 1）目的 发现基础设施瓶颈、中间件瓶颈、系统容量瓶颈； 发现分布式系统短板。 2）用途 保障大促容量充足，保障业务正常运转； 保障核心功能、保证用户体验； 评估大促成本，包含人工成本、机器成本、运维成本等。 3）难点 真实业务场景压测； 真实SQL模拟。 2.基准测试 系统调优 为解决以上挑战与问题，我们需要做系统调优，主要从六个方面进行：容量评估、性能评测、架构调优、实例调优、内核 调优和监控报警。 压测可以分为基准测试和仿真测试。对于MySQL而言，基准测试可以用sysbench或mysqlslap等。基于通用场景的测 试，每个实例在不同的业务场景下达到的容量也不同，通常情况下，同一个实例的应用真实的业务场景的值不会超过其基 准测试的值。 3. 全链路压测是大促备战核武器 真实业务场景的压测往往比基准测试更加复杂，阿里巴巴在双11的场景下整个压测过程大概可以分为4个步骤，如下图所示： 容量评估主要分为三个部分：经验评估、单元压测与全链路压测。 经验评估 容量评估刚开始阶段是经验评估，根据以往经验值给出一个预估的压力，再除以单台机器的性能，大致可得出所需服务器 的数量。根据服务器数量、应用机器数量与DB实例数量，可以得出整个数据库（如链接池）的设置，以及要扩充多少实例 和应用机器等，判断能否支撑得住双11的峰值。需要针对上述的预估做一个判断验证，通过压测来完成。 单元压测 经过经验预估后，需要针对上述的预估做判断验证，可以通过单元压测来完成。由于是分布式的系统，需要预先针对单个 实例、单个单元以及单个应用模块来进行压测。单元压测的主要功能是完成单元内的验证，系统整个的架构很多是异地 多活，因此不但需要验证整个双11的流量，还要验证在单元内的容量是否充足，以及单个系统的容量，例如压测某一个模 块、交易模块、优惠模块等容量是否充足。 全链路压测 等每一个应用模块验证完成之后，需要对整个链路进行压测，也就是全链路压测。全链路压测是基于场景化的仿真测试， 其数据最接近业务的系统值，针对全链路压测，可以借助压测来验证整个分布式系统的容量是否充足。 （一）容量评估 （二）性能评测 经验评估 预估压力/单机性能=服务器数量 应用机器设置、数据库设置等 单元压测 验证单元内容量 验证单个系统容量 全链路压测 场景化压测 解决分布式系统容量评估 相关系统梳理 涉及业务梳理 技术架构梳理 容量预估 环境准备 服务器准备 数据构造 业务请求准备 正式压测 短板压测 容量验证/优化 预案验证 优化 短板优化 架构优化 容量优化

📄 Page 9

15 16MySQL电子书 首先针对涉及到的业务进行模块梳理与技术架构梳理，以及容量预估是否充足。梳理完成之后需要准备测试环境，包含服 务器准备、数据构造与业务请求准备等。第三步正式压测，可以通过压测来发现短板，验证容量是否充足。预案验证可以 验证在某个压力场景下，降级某个预案会给系统减少多少压力。验证压测执行完之后，需要针对过程中发现的短板、架构 以及容量等问题进行优化，这个过程并非一蹴而就，而是要经过多轮压测。 阿里巴巴集团及各BU每年压测4000+次，13年全链路发现700+问题，14年发现500+问题，15年发现400+问题。基于真 实业务场景的全链路压测，已成为每年双11前筹备核心工作之一。 4.工具 对于上述的全链路压测操作，我们有一些现成的工具以供使用。 1）PTS（Performance Testing Service） 是面向所有技术背景人员的云化测试工具，可理解为全链路压测工具。有别于传统工具的繁复，PTS以互联网化的交互， 提供性能测试、API调试和监测等多种能力。自研和适配开源的功能可以轻松模拟任意体量的用户访问业务的场景，任务 随时发起，免去繁琐的搭建和维护成本。紧密结合监控、流控等兄弟产品提供一站式高可用能力，高效检验和管理业务性 能。 2）DAS（Database Autonomy Service） 智能压测主要应用在以下两种场景： 为应对即将到来的短期业务高峰，验证当前的RDS MySQL规格是否需要扩容； 数据库迁移上云前，验证目标RDS MySQL的规格是否满足业务需求。 5.注意事项 根据以往的情况，性能评测时主要有4个方面需要特别注意： 1）参数 如果想对比一两个参数在不同情况下，例如打开和关闭、设置不同的值时对性能的影响，可以单独做测试。一旦测试出来 最优值，需在压测之前将参数调到最优值。 2）网络 曾有用户拿线下的机器链接本地数据库，然后拿本地的应用机器链接RDS做性能对比，这种测试没有可对比性，主要原因 是压测机到RDS的网络延迟和到本地数据库的延迟存在很大差异。 3）规格 RDS的规格的性能差别较大,如果想测试CPU的性能，物理IO要少，数据大小在内存容量范围内即可。但大多数业务场景 都是涉及到物理IO，所以在到测试数据时，数据量要大于内存的大小。 4）ECS的网络带宽 阿里云的ECS是限制网络带宽的，以往有用户在做测试时，RDS的资源没有用满，压力也上不去，经过定位发现是ECS 的网络带宽打满了，因此在准备整个压测环境时，要将这些内容调好。 如果业务的访问都用数据库支撑的话成本高昂，缓存可以代替一部分关系型数据库在读方面的请求。基于原理的设计以及 成本方面考虑，缓存的读性能比关系型数据库好，性价比较高。 到数据库层面，如果是读多写少，针对于单个实例很难支撑的情况下，可以借助于只读实例。只读实例可以实现在线弹性 的扩展读能力，读的业务请求可以实现隔离，例如可以把轻分析型以及拖数据类型在只读实例内完成。 此外，每个只读实例都有一个单独的链接地址，如果把某一类的业务和其他的业务区分开，例如某一类的只读的这个场 景，只到某一个实例访问，可以单独链接只读实例的链接串。 如果要是想从整个层面来控制主实例和只读实例的访问，可以借助负载均衡独享代理完成。独享代理可以缓解大量短链接 1.数据库架构调优 （三）架构调优 读多写少场景 超高性价比 有效提升应用性能 在线弹性扩展读能力 读业务请求隔离，如：轻分析型、 拖数据型 容灾 单RDS很难支撑业务 RDS->Polar MySQL RDS->Polar X 2.0 可以缓解大量短链接的场景 使用代理连接地址后可以不用反 复变更应用内的连接地址，减少 维护成本，只要不释放代理，代 理连接地址可以永远使用。 使用独立实例代理，资源可扩展， 可承载更高流量 数据库 缓存 只读实例 独享代理数据库架构升级

📄 Page 10

17 18MySQL电子书 的场景，使用代理后不用反复变更应用类的链接地址，减少维护成本。使用独享代理之后，可以对线上的资源实现可扩 展，承受更高的流量。如果是RDS的实例规格以及只读实例都已经升到最大，但仍然不能支撑业务发展的话，可以考虑把 RDS的升级到Polar MySQL或者是分库分表PolarDB X 2.0，完成读写容量的扩展。 2. 降低只读实例和主实例的延迟 针对主实例和只读实例的数据一致性的问题，例如有延迟以及中断场景。针对线上延迟的问题我们做了分析，得出原因主 要包括5个方面： 1）主实例的DDL 占40%，如Alter、Drop等。需要Kill DDL语句或用DMS的无锁变更等。 2）主实例的大事务 占20%，如大批量导入、删除、更新数据。需要将大事务拆分成为小事务进行批量提交，这样只读节点就可以迅速地完成 事务的执行，不会造成数据的延迟。 3）主实例写入压力过大 占20%，如主实例规格较小、压力过大。这种情况需要升级主实例和只读实例的规格。 4）只读节点规格过小 占10%，这种情况升级只读实例规格。 5）其他（无主键） 占10%， RDS目前已经支持对表添加隐式主键，但是对于以前历史创建的表需要进行重建才能支持隐式主键。 3. 提升缓存命中率 在高并发场景下，写入压力过大，如何提升缓存的命中率？ 根据以往的经验，有4种更新方式：Cache aside，Read through，Write through，Write behind caching。 缓存的更新时，应用可以从Cache里面读取数据，取到后返回，没有得到从数据库里面取，成功后再返回缓存当中； Read through是已读没有读到，就更新缓存； Right through是在数据更新时，发现缓存中没有就更新缓存； Write behind caching类似于底层的 Linux操作系统机制，如果要是用Write behind caching合并同一个数据的多次操 作，以上几种方式都不能保证缓存命中率。 我们做核心系统时有一个小巧思，下面以产品的形式进行解释。 如上图所示，应用程序把写请求到 RDS,读请求到 Redis。RDS实时获取变更数据，通过订阅数据变更的方式拿到增量数 据后，更新Redis，这个巧思有以下好处： 更新路径短，延迟低 缓存失效为异步流程，业务更新DB完成后直接返回，不需要关心缓存失效流程， 整个更新路径短 ，更新延迟低。 应用简单可靠 应用无需实现复杂双写逻辑，只需启动异步线程监听增量数据，更新缓存数据即可。 应用更新无额外性能消耗 因为数据订阅是通过解析DB的增量日志来获取增量数据，获取数据的过程对业务、DB性能无损。 实例调优主要包含两部分：弹性扩容和参数调优。 1.弹性扩容 写请求 下游通过 SDK订阅 变更数据 实时获取变更数据 根据变更数据，更新缓存数据 读请求 应用程序 RDS Redis/ocs 数据传输 数据传输SDK （四）实例调优 本机升级 6G 6C RDS主库 12G 12C RDS备库 RDS备库 RDS主库 备份 日志 跨机升级 6G 6C RDS主库 12G 12C RDS主库 RDS备库 RDS备库

📄 Page 11

19 20MySQL电子书 2.参数调优 参数调优主要从三个方面进行，分别问连接、内存和IO，如下图所示，这里重点阐述几个参数。 连接 参数调优 内存 IO 和连接相关的几个参数 back_log table_open_cache thread_cache_size loose_thread_pool_enabled 和IO相关的几个参数 sync_binlog innodb_flush_logs_at_trx_commit innodb_io_capacity innodb_io_capacity_max 和内存相关的几个参数 innodb_buffer_pool_instances join_buffer_size tmp_table_size 连接相关的参数，back_log值表示在MySQL暂时停止响应新请求之前的这段短时间内可以堆叠多少请求。如果在短时 间内有大量的连接请求时，主线程会在一段时间内或者是瞬间检查连接数并启动新连接。 table_open_cache是所有线程打开表的数量，性能较小时会导致性能下降，但也不能设置过大，否则可能会造成 OOM。 IO相关的参数，sync_binlog大家不会陌生，我们在每年的双11会将这些数值调到最优。io_capacity和刷脏是IO两个比 较重要的参数。 内存相关的主要参数是innodb_buffer_pool_instances。在5.6之前，相对来说innodb_buffer_pool_instances只有一 个时，如果内存越大，它的性能越不稳定，因为刷脏的时候要锁。当有buffer_pool_instances了之后，把 Buffer Pool 按buffer_pool_instances拆分。这种情况下，每一个内存都会维护自己的List和锁结构，性能会提升很多。此外还有和 几个连接相关的参数，例如join_buffer以及临时表。 针对影响性能几个主要的参数，用户可以单独设置。这里我们已经将对性能影响较大的参数调到高性能模板中，应用时可 以找到对应的参数，应用到对应的实例上即可。 下图是普通模板与高性能模板的一个对比，可以看到，相比高性能模板，普通模板的性能要低25%左右。 路径：参数模板->系统参数模板->找到对应版本的参数模板->应用到实例 1.版本升级 （五）内核调优 PS：升级有风险，需充分验证

📄 Page 12

21 22MySQL电子书 如上图所示，目前官方维护的是5.6、5.7和8.0版本，每一个大版本的升级会带来一些新的特性，同时它的性能也会有上 升，从表中可以看到8.0性能是最强的。但升级时也会遇到一些问题，例如执行计划有一些和原来版本不太兼容的地方。 2. AliSQL特性 1）内容调优的4个补丁 针对高并发与热点库存的场景，AliSQL提供了4个补丁解决，其中内存注释、语句队列以及语句返回这3个补丁是针对热 点库存更新。 库存注释 在秒杀业务中，从逻辑上来说减库存包含两个场景：拍下减库存和付款减库存。 拍下减库存表示客户拍下时把库存减掉，业务逻辑较为简单。付款减库存表示客户付完款后减掉库存，业务逻辑较为复 杂，不是一条语句能够完成的，而是在事务中完成。在事务中，行锁冲突本来就较为严重，因此需要提升事务的性能。 通常的情况下，释放事务、开启事务和结束事务都是由应用来完成，如果是应用处理得过慢，或是网络交互的时间过慢， 或者是网络拥堵，就会增加行锁持有的时间。 库存注释是把持有行锁的时间行锁释放，交给数据库自己来做。使用排队和事务性Hint来控制并发控制、快速提交或者回 滚事务，成功就提交，失败就回滚，将控制权交给MySQL内核可以减少行锁持有的时间，快速地结束事务，提升减库存 的吞吐能力。 InnoDB的事务锁是最细粒度的行级锁，如果语句针对相同行进行并发操作，会导致冲突比较严重，AliSQL将冲突放在 Server层进行排队。对于相同行的冲突，如果让它在Server层一个桶（队列）内排队，会减少InnoDB层冲突检测的开 销，进而减少引擎层和Server层上下文切换带来的消耗。 语句返回 语句返回即更新完之后直接把结果返回，这个特性在PG和Oracle中都有，叫Returning，在MySQL没有，AliSQL吸纳 了这个特性。 如果在一个事务中更新完一行记录之后，应用再发请求Select这一条结果再返回给应用，会多一次网络交互。如果直接把 Update的结果返回，就可以减少这一次网络交互。如果一个事务里面有多条类似的语句的话，可以节省多次网络交互和 应用判断的时间，带来事务性能与应用性能的提升。 线程池 语句队列 内核调优 语句返回 库存注释 线程池 语句队列 内核调优 语句返回 库存注释 线程池 语句队列 内核调优 语句返回 库存注释 线程池 语句队列 内核调优 语句返回 库存注释 线程池 语句队列 内核调优 语句返回 库存注释 线程池 语句队列 在秒杀等业务场景中，减库存是一个常见的高 并发，串行化比较严重的模型，对数据库的吞 吐能力产生巨大的挑战，AliSQL使用排队和事 务性hint来控制并发控制和快速提交或者回滚事 务，一方面控制并发，另外一方面快速结束事 务释放事务锁，来提高减库存的吞吐能力。 MySQL的server层和引擎层在statement并发执行 过程中，有很多串行化的点，在DML语句中，事务 锁冲突比较常见，InnoDB中事务锁的最细粒度是行 级锁，如果语句针对相同行进行并发操作，会导致冲 突比较严重，系统吞吐量会随着并发的增加而递减。 AliSQL设计了针对语句的排队机制，相同的行或者 不同的语句进行分桶排队，尽可能的把具有相同冲 突可能的在一个桶内排队，减少conflict的开销。 可以减少事务里多次语句交互的时长 MySQL对于statement执行结果报文通常分为两类 Resultset和OK/ERR，针对DML语句则返回OK/ERR 报文，其中包括几个影响记录，扫码记录等属性。 将线程和会话分离，在拥有大量会话的同时，只需要少量 线程完成活跃会话的任务即可。 阿里云RDS的线程池实现了不同类型SQL操作的优先级 及并发控制机制，将连接数始终控制在最佳连接数附近， 使RDS数据库在高连接大并发情况下始终保持高性能。

📄 Page 13

23 24MySQL电子书 对于高并发场景，AliSQL使用线程池来解决。 在有大量线程进行并发访问时，线程池会自动调节并发的线程数量在合理范围内，避免线程池因为线程数量过多造成大量 缓存失效。 线程池会将语句和事务分为不同的优先级，分别控制语句和事务的并发数量，减少资源竞争，根据不同语句的复杂性来控 制它的优先级。 因此使用线程池可以将不同的SQL控制在一个合理的连接数范围，使数据库在高并发的场景下保持较高性能。 监控报警是系统调优里必不可少的一部分。 监控有RDS自带的监控，资源层监控，引擎层监控，慢日志的监控，收费的有审计日志，以及自治服务会基于上面RDS 自带的以及审计日志做一些分析，在这个基础上做一些报警。此外，分布式监控可以监控不同组件的瓶颈。 基于生态工具的保障，可以从变更流程、稳定性和数据恢复这三方面来介绍。 在大促期间，通过增加审批节点与流程，很好地提升我们的稳定性。 上图是用库存注释与语句队列后的性能对比，可以看到和原生性能相比翻了40倍不止。 资源层监控 链路追踪TracingAnalysis 引擎层监控 数据库自治服务DAS 审计日志 慢日志 6000 5000 4000 3000 2000 1000 原生 加库存注释 TPS query = "UPDATE kucun SET c=c-1 WHERE id=1;" db_query("BEGIN;") rs = db_query(query) db_query("COMMIT;") 加语句队列+注释 0 （六）监控报警 （一）变更流程 监控 报警 流程管理和生态工具 增加审批节点 增加审批流程

📄 Page 14

25 26MySQL电子书 可以通过设置DMS来执行的这些SQL，不让它超过一定的时间。也可以针对某一类的DDL或者是DML不允许执行，设 置安全规则。 可针对DDL、DML设置部分语法不允许在大促期间执行。 3. 异步清理大表 ·内核特性 AliSQL支持通过异步删除大文件的方式保证系统稳定性。使用InnoDB引擎时，直接删除大文件会导致POSIX文件系统 出现严重的稳定性问题，因此InnoDB会启动一个后台线程来异步清理数据文件。当删除单个表空间时，会将对应的数据 文件先重命名为临时文件，然后清除线程将异步、缓慢地清理文件。 路径：RDS管理控制台->实例列表->参数设置 4. 突发SQL访问控制 ·内核特性--并发控制 当业务流量突然暴涨，或出现 Bad SQL 时，DBA要考虑做限流，止损恢复业务。AliSQL设计了基于语句规则的并发控 制，Statement Concurrency Control，简称 CCL。 ·DAS限流 为防止数据库压力过大，一般都会在应用端做优化和控制。但在以下场景，也需要在数据库端做优化控制，如： 1）某类SQL并发急剧上升； 2）有数据倾斜SQL； 3）未创建索引SQL。 稳定性相关，不要因为人为的因素给系统造成压力，可以通过设置DMS来执行的这些SQL，不让它超过一定的时间，超 过一定时间则Kill掉。 2. 禁用部分语法(企业版) 查询超时时间由日常的60s调整为5s 路径：左侧实例导航-右键-编辑实例，查询超时时间设置 1.设置超时时间（通用） （二）稳定性相关

📄 Page 15

27 28MySQL电子书 1.DMS数据追踪 在大促高峰期间，经常会人工做一些数据变更，如果数据变更出错的话，可以通过数据追踪恢复，最快五分钟内可完全 恢复。 （三）数据恢复 ·使用场景 1）MySQL 5.5/5.6/5.7/8.0版本； 2）DELETE/UPDATE/INSERT； 3）少量数据。 ·功能 1）在线搜索日志内容，无需手工下载 Binlog； 2）支持数据的插入/更新/删除日志搜索，无需手工解析Binlog； 3）支持逐条数据恢复，无需手工生成回滚语句。 ·支持的Binlog 1）OSS Binlog（RDS会定时将Binlog备份到OSS上）； 2）本地热Binlog（数据库服务器上Binlog）。 2.控制台克隆实例/库表级别恢复 数据恢复还可通过备份来实现，可分为克隆整个实例恢复与库表级别恢复，还可以通过DBS数据库备份来恢复。 路径：RDS管理控制台->实例列表->单击实例ID->备份恢复

📄 Page 16

29 30MySQL电子书 压测验证 规格弹升 语句返回 只读实例 线程池 数据库架构升级 并发控制 经验预估 参数调优 库存注释 缓存 语句队列 独享代理 RDS自带监控 基准测试 数据库自治服务DAS 全链路压测 链路追踪TracingAnalysis 系统 调优 容量评估 内核调优 架构调优 实例调优 性能评测监控 总结以上内容，系统调优主要从内容评估、性能评测、架构调优、实例调优以及内核调优等方面实现，可以借助监控报警 来发现问题、解决问题，最终实现系统优化。 RDS MySQL Java 开发实战 深入浅出ORM框架MyBatis 作者：义泊 （一）为什么要选MyBatis （二）MyBatis基本概念介绍 JDBC 简单直接 开发效率低 Hibernate / JPA ORM 屏蔽底层数据库差异 MyBatis 轻量+动态SQL+关联查询 国内生态 重复冗余 关联查询、动态SQL不友好 不能屏蔽数据库差异 在以前是直接用JDBC进行数据库查询，优点是简单直接，缺点是开发效率低。用JDBC写程序，需要大量手工写代码， 代码重复率较高，后来逐渐演化出ORM框架。 ORM框架最早期有Hibernate以及JPA规范，Hibernate能够屏蔽底层数据库差异，自动根据SQL语言生成对应底层不 同数据库的方言，缺点是对关联查询支持与动态SQL能力不太友好，很难写出高效SQL。 国内目前流行的是轻量级MyBatis，对动态SQL以及关联查询的支持性较高，缺点是因为它绑定一个DB，手写SQL还要 动态拼接，很难从一个DB自由的切换到另外一个DB，但由于平时很少切换DB，因此问题不是很大。 MyBatis主要分为三层：接口层，核心层与基础层。 1.接口层 是通过提供的API作为数据库进行增/删/改/查，都是MyBatis的API。 接口层 增/删/改/查 核心层 SQL执行 结果映射SQL预处理 基础层 事务管理日志 连接池缓存 配置解析动态代理

📄 Page 17

31 32MySQL电子书 2.核心层 是SQL预处理、SQL执行、结果映射。 1）SQL预处理：是对代码里的变量进行绑定，以及动态SQL生成； 2）SQL执行：是把生成好的SQL，通过JDBC驱动，传到对应的DB里执行，而且要负责网络通信的部分； 3）结果映射：是把数据库返回的结果从关系型数据转换成Java对象数据。 3.基础层 包括日志、事务管理、缓存、连接池、动态代理、配置解析。 1）日志：是做框架里面的日志输出以及SQL语句输出； 2）事务管理：是对 JDBC事物、数据库事物做管理； 3）缓存：能够把结果集缓存在JVM的内存内部。优点是比较快，缺点是会占用堆内存。有条件的情况下，建议用户多 使用分布式缓存； 4）连接池：能够加速查询，提高性能； 5）动态代理：在用MyBatis编程时，核心是通过接口执行数据库查询。而Mapper接口本身是没有实现的，通过注解 或者XML配置SQL语句，动态代理会在运行时生成代理，当调用Mapper接口时，转换成实际的SQL语句； 6）配置解析：因为MyBatis里面有存在大量配置，需要配置新模块，读取XML配置，并把它映射为配置属性。 工程的搭建主要包括三部分：技术选型，项目依赖和工程结构。 1.技术选型 如上图所示，现在主流是用Spring框架做结合，还有底层有连接池也要去做结合。最新的选型是Spring-boot2，需要开 发Web工程，选择Spring-Webmvc框架，持久层或ORM映射层用MyBatis框架，连接池选择HikariCP，HikariCP是 Spring-boot2的默认连接池，数据库使用RDS MySQL，也是国内比较流行的云数据库。 有了以上选型之后，可以通过Spring官方网站上面的链接：“https://spring.io/quickstart”填入相关信息，就可以生成 框项目模板。模板下载之后在IDE里面打开导入项目即可。 2.项目依赖： 第一部分依赖：“spring-boot-starter”，依赖的作用是自动管理开始spring-boot项目的默认依赖，已经集成在 Starter里面了。 第二部分依赖：用到了Spring-webmvc，是最新的2.4.1版本。 第三部分依赖：因为用到MyBatis，所以还需要依赖 MyBatis starter。需要注意的是MyBatis并没有Spring官方的 Starter， 而是MyBatis社区提供的 MyBatis–spring-boot-starter。 第四部分依赖：JDBC实现，“mysql-connector-java”因为JDBC是一套规范API，具体实现由各个数据库的厂商 实现。 第五部分依赖：测试框架。 第六部分依赖：为了减少代码量，还用到了Lombok。 （三）MyBatis从0开始搭建工程 spring-boot2 spring-webmvc MyBatis HikariCP 创建RDS MySQL

📄 Page 18

33 34MySQL电子书 3.工程结构 上图为工程结构图，从上往下看： 第1层：控制器层Controller，这里有一个自己的控制器。 第2层：Mapper，主要是数据库增/删/改/查的接口。 第3层：Model层，主要是在Java里面的对象定义。 第4层：Service层，包括 API层和Impl层。 API层主要是接口的定义； Impl层主要是对接口的实现。 在接口里面调用Impl层，或者调用业务层实现业务逻辑组装和编排。 第5层：是Java应用的启动入口。选择用XML方式配置MyBatis，所以在Resource目录里面，需要增加Mapper目 录，mapper里面放入StaffMapper文件。 第6层：是Spring的配置文件，还有Log4j配置文件，还有Mybatis配置文件。Mybatis配置的也可以通过编程的方式实 现。 第7层：有Pom文件。 通过以上操作，从0开始搭建的MyBatis工程就完成了。当工程启动后，在浏览器里面输入“http://localhost:8001/ query?name=yanglong”链接，这里的端口还有路径都是自己定义的，可以验证工程是否正常运作。 不用连接池会存在以下问题： 1）如果不用连接池，每一次查询都需要建立连接，有两层握手。第一层是TCP层的握手，第二层是 MySQL协议握手。 两层协议大概需要有多个TCP数据包，这些都需要时间，在数据库内部还需要处理，建立连接是费时间的操作； 2）对于当代的应用来说，应用服务器一般有很多台，而数据库服务器相比之下可能少一些。大量应用服务器会存在一个 问题，在业务流量高峰期存在对DB的连接，而DB能够承载的连接数有限。所以说如果不用连接池，那么这个连接的数 量就不受控制，严重情况下可导致DB性能降低； 3）如果不用连接池，意味着每次执行SQL语句时，都需要创建TCL链接和关闭TCL链接，而关闭动作是在应用端完成， 导致应用服务器上存在较多TimeWait状态的TCP连接。TimeWait状态的连接数达到一定数量之后会引起应用问题，例 如端口不够用。 用连接池的优点： 1）不用每次都建立连接，而是直接从连接池里取连接，性能更佳； 2）连接池可以控制连接数量，以及当连接出现问题时，连接池能够去自动探测连接是否存活，如果连接中断，连接池会 自动重建； 比如应用使用RDS的MySQL，需要对MySQL的实例进行配置变更。如升规格，提高磁盘空间，遇到问题者压力大时， 希望能够重启MySQL，有了连接池就能够自动处理好这些问题。 连接池框架剖析和最佳实践 （一）为什么要用连接池？ 不用连接池 建连接较耗时（两层握手） 连接数有限 连接池 性能更佳 连接数受控+健康探测 大量TIME_WAIT 监控和管理灵活

📄 Page 19

35 36MySQL电子书 3）连接池能够对连接进行灵活的管理，对连接池配置与连接池状态监控，看到连接池里面的各种连接数量和性能指标。 （二）连接池架构 （三）Druid最佳实践 接口层 获取连接 归还连接 核心层 连接控制 异常处理并发控制 基础层 监控配置管理 日志定时任务 字节码操纵 连接池架构分为：接口层、核心层、基础层。 接口层：对于MyBatis是从连接池里获取连接，连接用完之后关闭，调用连接的Close，归还连接。 核心层：负责并发控制、连接控制、异常处理。 1）并发控制：连接池里的连接数量有限，应用里面的线程数量多于连接池的连接数量。 第一种情况，当连接池里的连接都处于活跃状态时，下一个请求，想要继续得到连接需要等待，因为数量有限，需要排队。 第二种情况，同一个链接，不能分配给多个线程，否则可能会事务混乱。 2）连接控制：需要能够动态调整连接池大小，同时连接池保证连接池里面的连接数量在期望范围内。 3）异常处理：出现异常时，比如底层数据库重启，网络中断，或者连接里面发生了协议层引擎层面错误，连接已经 不能再使用，这个时候连接池自动处理这些问题，将连接关闭并重新创建链接。 基础层：包括配置管理、监控、定时任务、日志、字节码操纵。 1）配置管理：连接池里面有很多的配置项，虽然常用的不多，但是可配置的点很多，需要进行解析管理。 2）监控：连接运行时需要统计和监控，最好能够提供查看的页面。 3）定时任务：连接池里空闲连接数量超过一定的程度，释放空闲连接，是通过定时任务完成。 4）字节码操纵：在Java框架里面会存在大量的字节码操纵，动态生成代理。 1.参数配置 如下图所示，常用配置包括： 1）Max-active：指的是连接池里允许的最大活跃连接数，这个值根据应用实际情况调整。 2）Min-idle：关掉多余连接，保留有效连接，节省数据库的资源，这个值根据应用实际情况调整。 3）Max-wait，指应用线程等待连接的超时。可以配几秒范围，根据业务应用实际情况进行判定。 4）Validation-query，指的是连接池探测当前连接是否是健康的SQL语句。如果是较新的JDBC，不会发SQL语句，而 是发Ping命令。 5）Validation-query-timeout，指的就是探测超时的时间。 6）Test-on-borrow指连接从连接池里取出时，连接池是否需要对连接进行健康探测。建议关闭False。 7）Test-on-return，建议关闭False。 8）Test-while-idle，指的是控制当连接处于空闲状态时，是否需检测连接的健康状态。建议打开True。 9）Time-between-eviction-runs-millis指的是触发空闲连接健康探测阈值，需要跟上面的Test-while结合起来。 10）Remove-abandoned，泄露连接强制回收，默认是False关闭。 11）Remove-abandoned-timeout，指的是强制回收的触发时间阈值。配置时间不要太短，因为业务长时间使用连 接，所以超时时间要比业务实际合理时间要高。配置参数单位是“秒”。 12）Log-abandoned，指的是关闭被泄露连接时输出堆栈。当一个连接被探测为连接泄露且强制关闭的时候，是否要 在日志里面输出获取连接的线程的堆栈。

📄 Page 20

37 38MySQL电子书 如上图所示，当用户开启了空闲连接的健康探测时，在Druid的源码内部会出发怎么样的逻辑？ 入口是MyBatis连接Druid时，getConnectionDirect是一个循环，在循环里从连接池里取一个空闲连接，当探测空闲连接 健康开关开启时，Druid会去检测连接的空闲时间是否超过配置的阈值。 如果超过了，循环会发起一个连接的健康探测。如果探测出来连接有问题，Druid会直接把连接关闭。在循环里面，会回到 循环开头，从连接池里再拿一个连接，直到拿到一个可用的连接，如果连接池里所有的连接都有问题，会重新创建连接。 基于Web的页面管理页面可以去监控连接池的运行状态。如上图所示，可以查看连接时的配置参数、版本、内路径，也可 以查看连接实名的连接数、 SQL监控线程监控。通过API可以也拿到这些监控数据。 除此以外，通过JMX直接连到应用内部，查看JMX对象的一些属性，如图内有些参数是可以修改的，有些只能看的还有一 些操作，一些API可以去调用。 3.连接泄露诊断 ·现象 1）正常请求拿不到连接报错； 2）响应时间增大； 3）应用不可用； ·原因 1）连接被取出后没有正常归还，导致连接长时间被占有但没有使用； 2）一般都是代码问题； ·解决办法 http://localhost:8001/druid/datasource.html 2.监控 在配置中开启监控选项，就会记录连接池的内部状态。