MySQL ProxySQL中间件读写分离与负载均衡实战：构建高可用数据库架构

一、ProxySQL 概述：理解其核心价值与架构

ProxySQL 是一款高性能的 MySQL 代理，它位于应用程序和数据库之间，提供智能路由、读写分离、负载均衡、故障转移和查询缓存等功能。它采用单线程事件驱动模型，具有极低的延迟和高吞吐量，是构建高可用数据库架构的理想选择。

1.1 ProxySQL 的核心组件与工作流程

ProxySQL 的内部主要由三个核心模块组成：Admin 模块、Runtime 模块和 Stats 模块。Admin 模块负责管理配置，Runtime 模块处理实际流量，Stats 模块收集性能指标。其工作流程如下所示。

#publish-mermaid-0-1793188918342{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-0-1793188918342 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-0-1793188918342 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-0-1793188918342 .error-icon{fill:#552222;}#publish-mermaid-0-1793188918342 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-0-1793188918342 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-0-1793188918342 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-0-1793188918342 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-0-1793188918342 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-0-1793188918342 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-0-1793188918342 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-0-1793188918342 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-0-1793188918342 .marker.cross{stroke:#333333;}#publish-mermaid-0-1793188918342 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-0-1793188918342 p{margin:0;}#publish-mermaid-0-1793188918342 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-0-1793188918342 .cluster-label text{fill:#333;}#publish-mermaid-0-1793188918342 .cluster-label span{color:#333;}#publish-mermaid-0-1793188918342 .cluster-label span p{background-color:transparent;}#publish-mermaid-0-1793188918342 .label text,#publish-mermaid-0-1793188918342 span{fill:#333;color:#333;}#publish-mermaid-0-1793188918342 .node rect,#publish-mermaid-0-1793188918342 .node circle,#publish-mermaid-0-1793188918342 .node ellipse,#publish-mermaid-0-1793188918342 .node polygon,#publish-mermaid-0-1793188918342 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-0-1793188918342 .rough-node .label text,#publish-mermaid-0-1793188918342 .node .label text,#publish-mermaid-0-1793188918342 .image-shape .label,#publish-mermaid-0-1793188918342 .icon-shape .label{text-anchor:middle;}#publish-mermaid-0-1793188918342 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-0-1793188918342 .rough-node .label,#publish-mermaid-0-1793188918342 .node .label,#publish-mermaid-0-1793188918342 .image-shape .label,#publish-mermaid-0-1793188918342 .icon-shape .label{text-align:center;}#publish-mermaid-0-1793188918342 .node.clickable{cursor:pointer;}#publish-mermaid-0-1793188918342 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-0-1793188918342 .arrowheadPath{fill:#333333;}#publish-mermaid-0-1793188918342 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-0-1793188918342 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-0-1793188918342 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-0-1793188918342 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-0-1793188918342 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-0-1793188918342 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-0-1793188918342 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-0-1793188918342 .cluster text{fill:#333;}#publish-mermaid-0-1793188918342 .cluster span{color:#333;}#publish-mermaid-0-1793188918342 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-0-1793188918342 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-0-1793188918342 rect.text{fill:none;stroke-width:0;}#publish-mermaid-0-1793188918342 .icon-shape,#publish-mermaid-0-1793188918342 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-0-1793188918342 .icon-shape p,#publish-mermaid-0-1793188918342 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-0-1793188918342 .icon-shape .label rect,#publish-mermaid-0-1793188918342 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-0-1793188918342 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-0-1793188918342 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-0-1793188918342 .node .neo-node{stroke:#9370DB;}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].node rect,#publish-mermaid-0-1793188918342 [data-look=\”neo\”].cluster rect,#publish-mermaid-0-1793188918342 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1793188918342 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1793188918342 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}写查询 (INSERT, UPDATE,DELETE)读查询 (SELECT)

客户端应用

ProxySQL

查询分析

Admin 模块配置管理

Runtime 模块流量处理

Stats 模块数据统计

查询类型判断

后端 Master 节点

负载均衡策略

后端 Slave 节点 1

后端 Slave 节点 2

后端 Slave 节点 N

该图展示了 ProxySQL 作为一个中间件，接收客户端请求后，通过 Runtime 模块进行查询分析，并根据预定义的规则将写操作路由到 Master 节点，将读操作通过负载均衡策略分发到多个 Slave 节点。

1.2 ProxySQL 的核心优势

ProxySQL 相较于其他数据库中间件，具备以下显著优势：

二、环境准备与安装：搭建 ProxySQL 实战环境

在开始配置 ProxySQL 之前，我们需要一个可用的 MySQL 主从复制集群作为后端数据源，并完成 ProxySQL 本身的安装部署。

2.1 准备 MySQL 主从复制集群

为了实现读写分离，我们必须首先搭建一个 MySQL 主从复制集群。通常，集群包含一个 Master 节点和一到多个 Slave 节点。Master 负责处理所有写操作，Slave 通过复制 Master 的二进制日志来同步数据，并对外提供读服务。

#publish-mermaid-1-1793188918465{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1-1793188918465 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1-1793188918465 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1-1793188918465 .error-icon{fill:#552222;}#publish-mermaid-1-1793188918465 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1-1793188918465 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1-1793188918465 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1-1793188918465 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1-1793188918465 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1-1793188918465 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1-1793188918465 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1-1793188918465 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1-1793188918465 .marker.cross{stroke:#333333;}#publish-mermaid-1-1793188918465 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1-1793188918465 p{margin:0;}#publish-mermaid-1-1793188918465 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1-1793188918465 .cluster-label text{fill:#333;}#publish-mermaid-1-1793188918465 .cluster-label span{color:#333;}#publish-mermaid-1-1793188918465 .cluster-label span p{background-color:transparent;}#publish-mermaid-1-1793188918465 .label text,#publish-mermaid-1-1793188918465 span{fill:#333;color:#333;}#publish-mermaid-1-1793188918465 .node rect,#publish-mermaid-1-1793188918465 .node circle,#publish-mermaid-1-1793188918465 .node ellipse,#publish-mermaid-1-1793188918465 .node polygon,#publish-mermaid-1-1793188918465 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1-1793188918465 .rough-node .label text,#publish-mermaid-1-1793188918465 .node .label text,#publish-mermaid-1-1793188918465 .image-shape .label,#publish-mermaid-1-1793188918465 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1-1793188918465 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1-1793188918465 .rough-node .label,#publish-mermaid-1-1793188918465 .node .label,#publish-mermaid-1-1793188918465 .image-shape .label,#publish-mermaid-1-1793188918465 .icon-shape .label{text-align:center;}#publish-mermaid-1-1793188918465 .node.clickable{cursor:pointer;}#publish-mermaid-1-1793188918465 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1-1793188918465 .arrowheadPath{fill:#333333;}#publish-mermaid-1-1793188918465 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1-1793188918465 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1-1793188918465 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1-1793188918465 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1-1793188918465 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1-1793188918465 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1-1793188918465 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1-1793188918465 .cluster text{fill:#333;}#publish-mermaid-1-1793188918465 .cluster span{color:#333;}#publish-mermaid-1-1793188918465 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1-1793188918465 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1-1793188918465 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1-1793188918465 .icon-shape,#publish-mermaid-1-1793188918465 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1-1793188918465 .icon-shape p,#publish-mermaid-1-1793188918465 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1-1793188918465 .icon-shape .label rect,#publish-mermaid-1-1793188918465 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1-1793188918465 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1-1793188918465 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1-1793188918465 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].node rect,#publish-mermaid-1-1793188918465 [data-look=\”neo\”].cluster rect,#publish-mermaid-1-1793188918465 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1793188918465 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1793188918465 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}MySQL 主从集群

Master (写节点)

Slave 1 (读节点)

Slave 2 (读节点)

此图描绘了一个典型的 MySQL 主从架构，其中 Master 节点是数据写入的唯一入口，Slave 节点从 Master 同步数据并分担读压力。

2.2 安装 ProxySQL

ProxySQL 的安装非常简单，可以通过 Docker 或直接从官方源码编译安装。以下是使用 Docker 快速启动 ProxySQL 的命令。

docker run -d -p 6032:6032 -p 6033:6033 –name proxysql \\
-e MYSQL_ROOT_PASSWORD=root_password \\
-e PROXYSQL_ADMIN_USERNAME=admin \\
-e PROXYSQL_ADMIN_PASSWORD=admin_password \\
-e PROXYSQL_ADMIN_PASSWORD=proxysql_password \\
proxysql/proxysql:latest

安装完成后，可以通过 6032 端口（Admin 管理端口）和 6033 端口（Proxy 端口）访问 ProxySQL。

三、ProxySQL 核心配置：实现读写分离

配置 ProxySQL 实现读写分离是本文的核心内容，主要包括后端服务器配置和查询规则配置两个关键步骤。

3.1 配置后端 MySQL 服务器

首先，我们需要在 ProxySQL 中添加后端的 Master 和 Slave 服务器。这通过 mysql_servers 表完成。

我们将 Master 服务器分配到 hostgroup 10，所有 Slave 服务器分配到 hostgroup 20。weight 参数用于后续的负载均衡。

3.2 配置查询规则

接下来，我们需要定义查询规则，告诉 ProxySQL 如何根据 SQL 语句的类型路由到不同的后端服务器组。这通过 mysql_query_rules 表实现。

规则 1 匹配所有以 SELECT 开头的查询，并将其路由到 hostgroup 20（Slave 组）。规则 2 是一个默认规则，匹配所有其他查询（主要是写操作），并将其路由到 hostgroup 10（Master 组）。

3.3 验证读写分离效果

配置完成后，我们可以通过简单的测试来验证读写分离是否生效。下图展示了客户端分别执行写操作和读操作时的数据流向。

SlaveMasterProxySQLClientSlaveMasterProxySQLClient#publish-mermaid-2-1793188918500{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-2-1793188918500 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-2-1793188918500 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-2-1793188918500 .error-icon{fill:#552222;}#publish-mermaid-2-1793188918500 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-2-1793188918500 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-2-1793188918500 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-2-1793188918500 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-2-1793188918500 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-2-1793188918500 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-2-1793188918500 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-2-1793188918500 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-2-1793188918500 .marker.cross{stroke:#333333;}#publish-mermaid-2-1793188918500 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-2-1793188918500 p{margin:0;}#publish-mermaid-2-1793188918500 .actor{stroke:#9370DB;fill:#ECECFF;stroke-width:1;}#publish-mermaid-2-1793188918500 rect.actor.outer-path[data-look=\”neo\”]{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1793188918500 rect.note[data-look=\”neo\”]{stroke:#aaaa33;fill:#fff5ad;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1793188918500 text.actor>tspan{fill:black;stroke:none;}#publish-mermaid-2-1793188918500 .actor-line{stroke:#9370DB;}#publish-mermaid-2-1793188918500 .innerArc{stroke-width:1.5;stroke-dasharray:none;}#publish-mermaid-2-1793188918500 .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#publish-mermaid-2-1793188918500 .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#publish-mermaid-2-1793188918500 [id$=\”-arrowhead\”] path{fill:#333;stroke:#333;}#publish-mermaid-2-1793188918500 .sequenceNumber{fill:white;}#publish-mermaid-2-1793188918500 [id$=\”-sequencenumber\”]{fill:#333;}#publish-mermaid-2-1793188918500 [id$=\”-crosshead\”] path{fill:#333;stroke:#333;}#publish-mermaid-2-1793188918500 .messageText{fill:#333;stroke:none;}#publish-mermaid-2-1793188918500 .labelBox{stroke:#9370DB;fill:#ECECFF;filter:none;}#publish-mermaid-2-1793188918500 .labelText,#publish-mermaid-2-1793188918500 .labelText>tspan{fill:black;stroke:none;}#publish-mermaid-2-1793188918500 .loopText,#publish-mermaid-2-1793188918500 .loopText>tspan{fill:black;stroke:none;}#publish-mermaid-2-1793188918500 .sectionTitle,#publish-mermaid-2-1793188918500 .sectionTitle>tspan{fill:black;stroke:none;}#publish-mermaid-2-1793188918500 .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:#9370DB;fill:#9370DB;}#publish-mermaid-2-1793188918500 .note{stroke:#aaaa33;fill:#fff5ad;}#publish-mermaid-2-1793188918500 .noteText,#publish-mermaid-2-1793188918500 .noteText>tspan{fill:black;stroke:none;font-weight:normal;}#publish-mermaid-2-1793188918500 .activation0{fill:#f4f4f4;stroke:#666;}#publish-mermaid-2-1793188918500 .activation1{fill:#f4f4f4;stroke:#666;}#publish-mermaid-2-1793188918500 .activation2{fill:#f4f4f4;stroke:#666;}#publish-mermaid-2-1793188918500 .actorPopupMenu{position:absolute;}#publish-mermaid-2-1793188918500 .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#publish-mermaid-2-1793188918500 .actor-man circle,#publish-mermaid-2-1793188918500 line{fill:#ECECFF;stroke-width:2px;}#publish-mermaid-2-1793188918500 g rect.rect{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));stroke:#9370DB;}#publish-mermaid-2-1793188918500 .node .neo-node{stroke:#9370DB;}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].node rect,#publish-mermaid-2-1793188918500 [data-look=\”neo\”].cluster rect,#publish-mermaid-2-1793188918500 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1793188918500 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1793188918500 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}执行 INSERT 语句 (写操作)路由写请求返回结果返回结果执行 SELECT 语句 (读操作)路由读请求返回结果返回结果

该时序图清晰地展示了 ProxySQL 如何根据 SQL 类型将请求智能地分发到 Master 或 Slave 节点，实现了读写分离。

四、ProxySQL 负载均衡策略：优化后端资源利用

当有多个 Slave 节点时，ProxySQL 可以通过负载均衡策略，将读请求均匀地分发到各个 Slave，从而最大化利用后端资源，提升整体读性能。

4.1 配置负载均衡权重

ProxySQL 支持多种负载均衡算法，如 ROUNDROBIN（轮询）、LEASTCONN（最少连接）等。我们可以在 mysql_servers 表中为每个 Slave 节点设置 weight 权重，以实现自定义的负载分配。

假设我们有三个 Slave 节点，我们希望它们的负载权重比为 3:2:1，以反映其硬件性能差异。可以通过以下饼图直观展示。

#publish-mermaid-3-1793188918521{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-3-1793188918521 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-3-1793188918521 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-3-1793188918521 .error-icon{fill:#552222;}#publish-mermaid-3-1793188918521 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-3-1793188918521 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-3-1793188918521 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-3-1793188918521 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-3-1793188918521 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-3-1793188918521 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-3-1793188918521 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-3-1793188918521 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-3-1793188918521 .marker.cross{stroke:#333333;}#publish-mermaid-3-1793188918521 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-3-1793188918521 p{margin:0;}#publish-mermaid-3-1793188918521 .pieCircle{stroke:black;stroke-width:2px;opacity:0.7;}#publish-mermaid-3-1793188918521 .pieCircle.highlighted{scale:1.05;opacity:1;}#publish-mermaid-3-1793188918521 .pieCircle.highlightedOnHover:hover{transition-duration:250ms;scale:1.05;opacity:1;}#publish-mermaid-3-1793188918521 .pieOuterCircle{stroke:black;stroke-width:2px;fill:none;}#publish-mermaid-3-1793188918521 .pieTitleText{text-anchor:middle;font-size:25px;fill:black;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}#publish-mermaid-3-1793188918521 .slice{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;fill:#333;font-size:17px;}#publish-mermaid-3-1793188918521 .legend text{fill:black;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:17px;}#publish-mermaid-3-1793188918521 .node .neo-node{stroke:#9370DB;}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].node rect,#publish-mermaid-3-1793188918521 [data-look=\”neo\”].cluster rect,#publish-mermaid-3-1793188918521 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1793188918521 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1793188918521 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}50%30%20%Slave 节点负载权重分布Slave A (高性能)Slave B (中性能)Slave C (低性能)

在 ProxySQL 中，我们只需在 mysql_servers 表的 weight 列填入相应数值即可应用此权重。

4.2 验证负载均衡策略

为了验证负载均衡策略是否生效，我们可以使用 proxysql-admin 工具或直接查询 stats_mysql_query_digest 表，查看不同后端节点的查询执行次数分布。

SlaveCClient3SlaveBClient2SlaveAProxySQLClient1SlaveCClient3SlaveBClient2SlaveAProxySQLClient1#publish-mermaid-4-1793188918531{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-4-1793188918531 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-4-1793188918531 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-4-1793188918531 .error-icon{fill:#552222;}#publish-mermaid-4-1793188918531 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-4-1793188918531 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-4-1793188918531 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-4-1793188918531 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-4-1793188918531 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-4-1793188918531 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-4-1793188918531 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-4-1793188918531 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-4-1793188918531 .marker.cross{stroke:#333333;}#publish-mermaid-4-1793188918531 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-4-1793188918531 p{margin:0;}#publish-mermaid-4-1793188918531 .actor{stroke:#9370DB;fill:#ECECFF;stroke-width:1;}#publish-mermaid-4-1793188918531 rect.actor.outer-path[data-look=\”neo\”]{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1793188918531 rect.note[data-look=\”neo\”]{stroke:#aaaa33;fill:#fff5ad;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1793188918531 text.actor>tspan{fill:black;stroke:none;}#publish-mermaid-4-1793188918531 .actor-line{stroke:#9370DB;}#publish-mermaid-4-1793188918531 .innerArc{stroke-width:1.5;stroke-dasharray:none;}#publish-mermaid-4-1793188918531 .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#publish-mermaid-4-1793188918531 .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#publish-mermaid-4-1793188918531 [id$=\”-arrowhead\”] path{fill:#333;stroke:#333;}#publish-mermaid-4-1793188918531 .sequenceNumber{fill:white;}#publish-mermaid-4-1793188918531 [id$=\”-sequencenumber\”]{fill:#333;}#publish-mermaid-4-1793188918531 [id$=\”-crosshead\”] path{fill:#333;stroke:#333;}#publish-mermaid-4-1793188918531 .messageText{fill:#333;stroke:none;}#publish-mermaid-4-1793188918531 .labelBox{stroke:#9370DB;fill:#ECECFF;filter:none;}#publish-mermaid-4-1793188918531 .labelText,#publish-mermaid-4-1793188918531 .labelText>tspan{fill:black;stroke:none;}#publish-mermaid-4-1793188918531 .loopText,#publish-mermaid-4-1793188918531 .loopText>tspan{fill:black;stroke:none;}#publish-mermaid-4-1793188918531 .sectionTitle,#publish-mermaid-4-1793188918531 .sectionTitle>tspan{fill:black;stroke:none;}#publish-mermaid-4-1793188918531 .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:#9370DB;fill:#9370DB;}#publish-mermaid-4-1793188918531 .note{stroke:#aaaa33;fill:#fff5ad;}#publish-mermaid-4-1793188918531 .noteText,#publish-mermaid-4-1793188918531 .noteText>tspan{fill:black;stroke:none;font-weight:normal;}#publish-mermaid-4-1793188918531 .activation0{fill:#f4f4f4;stroke:#666;}#publish-mermaid-4-1793188918531 .activation1{fill:#f4f4f4;stroke:#666;}#publish-mermaid-4-1793188918531 .activation2{fill:#f4f4f4;stroke:#666;}#publish-mermaid-4-1793188918531 .actorPopupMenu{position:absolute;}#publish-mermaid-4-1793188918531 .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#publish-mermaid-4-1793188918531 .actor-man circle,#publish-mermaid-4-1793188918531 line{fill:#ECECFF;stroke-width:2px;}#publish-mermaid-4-1793188918531 g rect.rect{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));stroke:#9370DB;}#publish-mermaid-4-1793188918531 .node .neo-node{stroke:#9370DB;}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].node rect,#publish-mermaid-4-1793188918531 [data-look=\”neo\”].cluster rect,#publish-mermaid-4-1793188918531 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1793188918531 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-4-1793188918531 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}loop[多个并发读请求]SELECT 1路由请求返回结果返回结果SELECT 2路由请求返回结果返回结果SELECT 3路由请求返回结果返回结果

此图模拟了多个客户端并发发起读请求，ProxySQL 根据预设的权重或算法将请求分发到不同的 Slave 节点，实现了负载均衡。

五、ProxySQL 高级特性：动态配置与监控

ProxySQL 的强大之处在于其动态配置能力，可以在不中断服务的情况下调整配置，并提供了丰富的监控指标。

5.1 动态配置管理

ProxySQL 的所有配置都存储在内存中，分为 runtime 和 disk 两部分。我们可以通过 Admin 管理接口动态加载配置，实现平滑更新。

#publish-mermaid-5-1793188918562{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-5-1793188918562 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-5-1793188918562 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-5-1793188918562 .error-icon{fill:#552222;}#publish-mermaid-5-1793188918562 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-5-1793188918562 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-5-1793188918562 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-5-1793188918562 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-5-1793188918562 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-5-1793188918562 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-5-1793188918562 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-5-1793188918562 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-5-1793188918562 .marker.cross{stroke:#333333;}#publish-mermaid-5-1793188918562 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-5-1793188918562 p{margin:0;}#publish-mermaid-5-1793188918562 defs [id$=\”-barbEnd\”]{fill:#333333;stroke:#333333;}#publish-mermaid-5-1793188918562 g.stateGroup text{fill:#9370DB;stroke:none;font-size:10px;}#publish-mermaid-5-1793188918562 g.stateGroup text{fill:#333;stroke:none;font-size:10px;}#publish-mermaid-5-1793188918562 g.stateGroup .state-title{font-weight:bolder;fill:#131300;}#publish-mermaid-5-1793188918562 g.stateGroup rect{fill:#ECECFF;stroke:#9370DB;}#publish-mermaid-5-1793188918562 g.stateGroup line{stroke:#333333;stroke-width:1;}#publish-mermaid-5-1793188918562 .transition{stroke:#333333;stroke-width:1;fill:none;}#publish-mermaid-5-1793188918562 .stateGroup .composit{fill:white;border-bottom:1px;}#publish-mermaid-5-1793188918562 .stateGroup .alt-composit{fill:#e0e0e0;border-bottom:1px;}#publish-mermaid-5-1793188918562 .state-note{stroke:#aaaa33;fill:#fff5ad;}#publish-mermaid-5-1793188918562 .state-note text{fill:black;stroke:none;font-size:10px;}#publish-mermaid-5-1793188918562 .stateLabel .box{stroke:none;stroke-width:0;fill:#ECECFF;opacity:0.5;}#publish-mermaid-5-1793188918562 .edgeLabel .label rect{fill:#ECECFF;opacity:0.5;}#publish-mermaid-5-1793188918562 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-5-1793188918562 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-5-1793188918562 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-5-1793188918562 .edgeLabel .label text{fill:#333;}#publish-mermaid-5-1793188918562 .label div .edgeLabel{color:#333;}#publish-mermaid-5-1793188918562 .stateLabel text{fill:#131300;font-size:10px;font-weight:bold;}#publish-mermaid-5-1793188918562 .node circle.state-start{fill:#333333;stroke:#333333;}#publish-mermaid-5-1793188918562 .node .fork-join{fill:#333333;stroke:#333333;}#publish-mermaid-5-1793188918562 .node circle.state-end{fill:#9370DB;stroke:white;stroke-width:1.5;}#publish-mermaid-5-1793188918562 .end-state-inner{fill:white;stroke-width:1.5;}#publish-mermaid-5-1793188918562 .node rect{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-5-1793188918562 .node polygon{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-5-1793188918562 [id$=\”-barbEnd\”]{fill:#333333;}#publish-mermaid-5-1793188918562 .statediagram-cluster rect{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-5-1793188918562 .cluster-label,#publish-mermaid-5-1793188918562 .nodeLabel{color:#131300;}#publish-mermaid-5-1793188918562 .statediagram-cluster rect.outer{rx:5px;ry:5px;}#publish-mermaid-5-1793188918562 .statediagram-state .divider{stroke:#9370DB;}#publish-mermaid-5-1793188918562 .statediagram-state .title-state{rx:5px;ry:5px;}#publish-mermaid-5-1793188918562 .statediagram-cluster.statediagram-cluster .inner{fill:white;}#publish-mermaid-5-1793188918562 .statediagram-cluster.statediagram-cluster-alt .inner{fill:#f0f0f0;}#publish-mermaid-5-1793188918562 .statediagram-cluster .inner{rx:0;ry:0;}#publish-mermaid-5-1793188918562 .statediagram-state rect.basic{rx:5px;ry:5px;}#publish-mermaid-5-1793188918562 .statediagram-state rect.divider{stroke-dasharray:10,10;fill:#f0f0f0;}#publish-mermaid-5-1793188918562 .note-edge{stroke-dasharray:5;}#publish-mermaid-5-1793188918562 .statediagram-note rect{fill:#fff5ad;stroke:#aaaa33;stroke-width:1px;rx:0;ry:0;}#publish-mermaid-5-1793188918562 .statediagram-note rect{fill:#fff5ad;stroke:#aaaa33;stroke-width:1px;rx:0;ry:0;}#publish-mermaid-5-1793188918562 .statediagram-note text{fill:black;}#publish-mermaid-5-1793188918562 .statediagram-note .nodeLabel{color:black;}#publish-mermaid-5-1793188918562 .statediagram .edgeLabel{color:red;}#publish-mermaid-5-1793188918562 [id$=\”-dependencyStart\”],#publish-mermaid-5-1793188918562 [id$=\”-dependencyEnd\”]{fill:#333333;stroke:#333333;stroke-width:1;}#publish-mermaid-5-1793188918562 .statediagramTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].statediagram-cluster rect{fill:#ECECFF;stroke:#9370DB;stroke-width:1;}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].statediagram-cluster rect.outer{rx:5px;ry:5px;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1793188918562 .node .neo-node{stroke:#9370DB;}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].node rect,#publish-mermaid-5-1793188918562 [data-look=\”neo\”].cluster rect,#publish-mermaid-5-1793188918562 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1793188918562 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-5-1793188918562 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}配置变更保存配置

Load_Config

Apply_Config

Running

Reload_Config

Save_To_Disk

该状态图展示了 ProxySQL 配置的生命周期：从加载配置到应用，再到运行，支持动态重载和持久化保存。

5.2 监控与性能指标

ProxySQL 的 stats 模块提供了海量的运行时统计信息，如连接数、查询数、后端服务器状态等，是性能调优和问题排查的重要依据。

#publish-mermaid-6-1793188918634{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-6-1793188918634 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-6-1793188918634 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-6-1793188918634 .error-icon{fill:#552222;}#publish-mermaid-6-1793188918634 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-6-1793188918634 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-6-1793188918634 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-6-1793188918634 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-6-1793188918634 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-6-1793188918634 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-6-1793188918634 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-6-1793188918634 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-6-1793188918634 .marker.cross{stroke:#333333;}#publish-mermaid-6-1793188918634 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-6-1793188918634 p{margin:0;}#publish-mermaid-6-1793188918634 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-6-1793188918634 .cluster-label text{fill:#333;}#publish-mermaid-6-1793188918634 .cluster-label span{color:#333;}#publish-mermaid-6-1793188918634 .cluster-label span p{background-color:transparent;}#publish-mermaid-6-1793188918634 .label text,#publish-mermaid-6-1793188918634 span{fill:#333;color:#333;}#publish-mermaid-6-1793188918634 .node rect,#publish-mermaid-6-1793188918634 .node circle,#publish-mermaid-6-1793188918634 .node ellipse,#publish-mermaid-6-1793188918634 .node polygon,#publish-mermaid-6-1793188918634 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-6-1793188918634 .rough-node .label text,#publish-mermaid-6-1793188918634 .node .label text,#publish-mermaid-6-1793188918634 .image-shape .label,#publish-mermaid-6-1793188918634 .icon-shape .label{text-anchor:middle;}#publish-mermaid-6-1793188918634 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-6-1793188918634 .rough-node .label,#publish-mermaid-6-1793188918634 .node .label,#publish-mermaid-6-1793188918634 .image-shape .label,#publish-mermaid-6-1793188918634 .icon-shape .label{text-align:center;}#publish-mermaid-6-1793188918634 .node.clickable{cursor:pointer;}#publish-mermaid-6-1793188918634 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-6-1793188918634 .arrowheadPath{fill:#333333;}#publish-mermaid-6-1793188918634 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-6-1793188918634 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-6-1793188918634 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-6-1793188918634 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-6-1793188918634 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-6-1793188918634 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-6-1793188918634 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-6-1793188918634 .cluster text{fill:#333;}#publish-mermaid-6-1793188918634 .cluster span{color:#333;}#publish-mermaid-6-1793188918634 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-6-1793188918634 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-6-1793188918634 rect.text{fill:none;stroke-width:0;}#publish-mermaid-6-1793188918634 .icon-shape,#publish-mermaid-6-1793188918634 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-6-1793188918634 .icon-shape p,#publish-mermaid-6-1793188918634 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-6-1793188918634 .icon-shape .label rect,#publish-mermaid-6-1793188918634 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-6-1793188918634 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-6-1793188918634 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-6-1793188918634 .node .neo-node{stroke:#9370DB;}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].node rect,#publish-mermaid-6-1793188918634 [data-look=\”neo\”].cluster rect,#publish-mermaid-6-1793188918634 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1793188918634 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-6-1793188918634 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}ProxySQL 监控体系

Stats 模块

连接统计

查询统计

后端服务器状态

查询规则匹配统计

Admin 接口

监控工具

此图说明了 ProxySQL 如何通过 Stats 模块收集各类指标，并通过 Admin 接口供外部工具（如 Prometheus、Grafana）进行监控和展示。

六、实战案例总结：从理论到实践的完整闭环

通过本文的实践，我们成功搭建了一个基于 ProxySQL 的 MySQL 读写分离与负载均衡系统。该系统能够智能地将写操作路由到 Master，读操作分发到多个 Slave，并实现了动态的负载均衡。

6.1 优化前后性能对比

引入 ProxySQL 后，数据库集群的读性能和可用性得到了显著提升。下图展示了优化前后的读操作分布对比。

#publish-mermaid-7-1793188918721{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-7-1793188918721 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-7-1793188918721 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-7-1793188918721 .error-icon{fill:#552222;}#publish-mermaid-7-1793188918721 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-7-1793188918721 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-7-1793188918721 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-7-1793188918721 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-7-1793188918721 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-7-1793188918721 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-7-1793188918721 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-7-1793188918721 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-7-1793188918721 .marker.cross{stroke:#333333;}#publish-mermaid-7-1793188918721 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-7-1793188918721 p{margin:0;}#publish-mermaid-7-1793188918721 .pieCircle{stroke:black;stroke-width:2px;opacity:0.7;}#publish-mermaid-7-1793188918721 .pieCircle.highlighted{scale:1.05;opacity:1;}#publish-mermaid-7-1793188918721 .pieCircle.highlightedOnHover:hover{transition-duration:250ms;scale:1.05;opacity:1;}#publish-mermaid-7-1793188918721 .pieOuterCircle{stroke:black;stroke-width:2px;fill:none;}#publish-mermaid-7-1793188918721 .pieTitleText{text-anchor:middle;font-size:25px;fill:black;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}#publish-mermaid-7-1793188918721 .slice{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;fill:#333;font-size:17px;}#publish-mermaid-7-1793188918721 .legend text{fill:black;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:17px;}#publish-mermaid-7-1793188918721 .node .neo-node{stroke:#9370DB;}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].node rect,#publish-mermaid-7-1793188918721 [data-look=\”neo\”].cluster rect,#publish-mermaid-7-1793188918721 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1793188918721 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-7-1793188918721 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}50%10%40%优化前后读操作分布对比Master (读+写)Master (仅写)Slave (读)

从图中可以看出，优化后，Master 节点的读压力被完全卸载，专注于写操作，而读操作则由多个 Slave 节点分担，大大提升了整体系统的吞吐量和响应速度。

6.2 常见问题与解决方案

在 ProxySQL 的使用过程中，可能会遇到一些常见问题，下表列出了一些典型问题及其解决方案。