MySQL 二轮学习笔记·运维篇·(三) 分库分表

分库分表

当数据库满足以下任一条件时，就必须考虑分库分表：

• 数据量过大：单表数据量超 1000 万（MySQL 单表最优性能区间是 100 万 - 500 万），查询时 “全表扫描” 耗时超 1 秒，甚至出现 “锁表” 导致写入阻塞；
• 访问压力过高：单库 QPS 超 5000（MySQL 单库默认能承载的 QPS 约 2000-3000），高峰时出现 “连接耗尽”，应用无法正常访问；
• 业务耦合严重：单库中包含 “用户、订单、商品” 等所有业务表，一次表结构变更（如订单表加字段）会影响所有业务，运维风险高。

1.分库分表的核心拆分方式

（1）垂直拆分：按 “业务维度” 拆

拆分逻辑：把单库中不同业务的表，拆到不同的数据库中。

示例：电商单库拆成 3 个独立库：

• 用户库（user_db）：存储用户表（user）、用户地址表（user_address）；
• 订单库（order_db）：存储订单表（order）、订单详情表（order_item）；
• 商品库（product_db）：存储商品表（product）、商品分类表（product_category）。

解决问题：

• 降低业务耦合：用户业务变更（如加 “会员等级” 字段），不会影响订单库；
• 分散存储压力：订单库的写入压力（每秒 1000 单），不会占用用户库的磁盘和 IO。

注意：垂直拆分后，跨库查询会变复杂（如 “查用户的所有订单” 需关联 user_db 和 order_db），需依赖中间件（如 MyCat）实现 “虚拟关联”。

（2）水平拆分：按 “数据维度” 拆

拆分逻辑：把单张 “大表”，按某一字段（如用户 ID、时间）拆成多张 “小表”，这些小表结构完全相同，数据分散存储。

示例：订单表（order）按 “用户 ID 取模” 拆成 10 张表：

• order_0：存储用户 ID%10=0 的数据；
• order_1：存储用户 ID%10=1 的数据；
• ...
• order_9：存储用户 ID%10=9 的数据。

解决问题：

• 提升单表效率：原 1 亿数据的订单表，拆后每张表仅 1000 万数据，查询耗时从 1 秒降到 100ms；
• 支持弹性扩容：后续数据量增长，可新增 order_10、order_11，无需重构表结构。

注意：水平拆分后，“全局查询”（如 “查昨天所有订单总数”）需聚合所有分表数据，需中间件支持 “分布式统计”。

(3)实际场景：垂直 + 水平结合

大多数业务会用 “混合拆分”：先垂直拆库，再对核心表水平拆表。

示例：电商系统的最终拆分方案：

1. 垂直拆库：拆成 user_db、order_db、product_db；
2. 水平拆表：对 order_db 中的 order 表，按 “用户 ID 取模” 拆成 order_0~order_9，分散到 2 台数据库服务器（每台存 5 张表）。

2.分库分表中间件-MyCat

分库分表后，应用不能直接连接多个后端库（否则需改代码适配路由逻辑）——MyCat就是解决这个问题的 “分布式数据库中间件”：它对外伪装成 “单台 MySQL”，接收应用请求后，自动路由到后端真实分库分表，对应用完全透明。

3.8种核心分片规则详解

(1)范围分片

核心逻辑：按分片字段的 “数值范围” 划分分片，例如按user_id划分：

• user_id 1~10000 → 路由到order_0；
• user_id 10001~20000 → 路由到order_1；
• user_id 20001~30000 → 路由到order_2。

配置关键：在rule.xml中通过PartitionByRange算法定义范围映射，示例：

<tableRule name="range-user-id">
  <rule>
    <columns>user_id</columns>
    <algorithm>range-alg</algorithm>
  </rule>
</tableRule>
<function name="range-alg" class="io.mycat.route.function.PartitionByRange">
  <property name="mapFile">range-map.properties</property> <!-- 范围配置文件 -->
</function>

在range-map.properties中定义范围：

10000=0  # user_id≤10000 → 分片0
20000=1  # 10000<user_id≤20000 → 分片1
30000=2  # 20000<user_id≤30000 → 分片2

适用场景：

分片字段为 “连续增长数值”（如user_id、order_id），或 “时间字段”（如按天 / 按月拆分日志表）；

优势是 “扩容方便”（新增分片只需添加更大范围，无需迁移历史数据）。

注意：避免 “热点分片”（如最新时间范围的分表写入压力过大）。

(3)取模分片

核心逻辑：按分片字段的 “数值取模” 结果划分分片，公式为分片索引 = 字段值 % 分片数量。

示例：分片数量 = 3，user_id=100

→ 100%3=1 → 路由到分片 1。

配置关键：通过PartitionByMod算法定义 “分片数量”，参考前文 “水平分表实操” 中的配置。

适用场景：

分片字段为 “均匀分布的数值”（如user_id），需 “数据均匀分散到各分表” 的场景（如用户数据、订单数据）；

优势是 “数据分布均衡”，查询时能精准定位单个分片。

注意：

扩容时需 “迁移大量数据”（如分片数量从 3→4，原取模结果会变化），需配合 “一致性 hash” 优化。

(3)一致性 Hash 算法

核心逻辑：基于 “Hash 环” 实现分片：

1. 将 “分片节点”（如 order_0、order_1）的 Hash 值映射到一个 32 位的 Hash 环上；

2. 计算 “分片字段值”（如user_id=100）的 Hash 值，在 Hash 环上顺时针查找最近的分片节点，即为目标分片。

   （为避免 “数据倾斜”，会添加 “虚拟节点”—— 每个真实分片对应多个虚拟节点，均匀分布在 Hash 环上）

配置关键：通过PartitionByConsistentHash

算法配置，示例：

<function name="consistent-hash" class="io.mycat.route.function.PartitionByConsistentHash">
  <property name="partitionCount">3</property> <!-- 真实分片数量 -->
  <property name="virtualBucketTimes">10</property> <!-- 每个真实分片的虚拟节点数 -->
</function>

适用场景：

需 “动态扩容” 的场景（如业务增长快，需频繁新增分表 / 分库）；

优势是 “扩容时数据迁移量少”（新增分片仅需迁移 Hash 环上相邻的部分数据）。

(3)枚举分片

核心逻辑：按分片字段的 “固定枚举值” 划分分片，例如按 “省份” 拆分：

• province=北京/天津/河北 → 路由到order_0；
• province=上海/江苏/浙江 → 路由到order_1；
• province=广东/广西/福建 → 路由到order_2。

配置关键：通过PartitionByFileMap算法，在配置文件中定义 “枚举值→分片” 映射，示例：

<function name="enum-province" class="io.mycat.route.function.PartitionByFileMap">
  <property name="mapFile">enum-province.properties</property>
  <property name="defaultNode">0</property> <!-- 未匹配枚举值时的默认分片 -->
</function>

在enum-province.properties中定义：

北京=0,天津=0,河北=0
上海=1,江苏=1,浙江=1
广东=2,广西=2,福建=2

适用场景：

分片字段的 “枚举值固定且数量少”（如省份、用户等级、订单状态）；

优势是 “查询时可精准定位分片”（如查 “北京的订单” 直接路由到 order_0）。

(5)应用指定算法

核心逻辑：

由应用层决定数据路由到哪个分片

—— 应用在执行 SQL 时，通过/*!mycat:dataNode=dn1*/

语法指定目标分片，示例：

-- 应用指定数据写入dn1（对应order_0）
insert /*!mycat:dataNode=dn1*/ into order(id, user_id) values(4, 103);

配置关键：在schema.xml中给逻辑表指定rule="sharding-by-subtable-interval"（或自定义应用指定规则），无需在rule.xml中额外配置算法。

适用场景：

应用层需 “自定义路由逻辑” 的场景（如按 “业务线” 拆分，A 业务线数据存分片 0，B 业务线存分片 1）；

优势是 “灵活性高”，路由逻辑完全由应用控制。

注意：需 “应用层与 MyCat 强耦合”，若应用多，需统一路由逻辑，否则易出错。

(6)固定 Hash 算法

核心逻辑：对分片字段（如user_id）做 “固定 Hash 计算”（如 CRC32 Hash），再将 Hash 结果映射到指定分片数量，公式为分片索引 = Hash(字段值) % 分片数量。

与 “取模分片” 的区别：取模分片直接用字段值取模，固定 Hash 用字段的 Hash 值取模，能避免 “字段值连续导致的分片热点”。

配置关键：通过PartitionByFixedHash算法配置，示例：

<function name="fixed-hash" class="io.mycat.route.function.PartitionByFixedHash">
  <property name="partitionCount">3</property> <!-- 分片数量 -->
  <property name="hashSeed">1024</property> <!-- Hash种子（自定义，保证一致性） -->
</function>

适用场景：分片字段为 “非连续数值” 或 “字符串”（如order_no），需 “数据均匀分布” 且 “避免热点” 的场景；优势是 “数据分布比普通取模更均匀”。

(7)字符串 Hash 解析

核心逻辑：对 “字符串类型的分片字段”（如手机号、订单号）做 Hash 处理，再映射到分片：

• 方式 1：截取字符串的 “部分字符”（如手机号后 4 位），转成数值后取模；
• 方式 2：对整个字符串做 Hash 计算（如 MD5），取 Hash 结果的部分数值取模。

配置关键：通过PartitionByString算法配置，示例（截取手机号后 4 位取模）：

<function name="str-hash-phone" class="io.mycat.route.function.PartitionByString">
  <property name="length">4</property> <!-- 截取字符串最后4位 -->
  <property name="count">3</property> <!-- 分片数量 -->
  <property name="hashSlice"> -4:-1 </property> <!-- 截取范围：最后4位（负数表示从后往前数） -->
</function>

适用场景：

分片字段为 “字符串”（如手机号、用户唯一标识uuid）；

优势是 “支持非数值型分片字段”，适配更多业务场景。

(8)按天 / 自然月分片

核心逻辑：按 “时间字段”（如create_time）的 “天 / 月” 划分分片，自动生成对应分表名：

• 按天分片：create_time=2024-05-01 → 路由到order_20240501；
• 按自然月分片：create_time=2024-05-15 → 路由到order_202405。

配置关键：

• 按天分片：用PartitionByDay算法，示例：

  <function name="sharding-by-day" class="io.mycat.route.function.PartitionByDay">
    <property name="dateFormat">yyyyMMdd</property> <!-- 分表名格式 -->
    <property name="sBeginDate">20240501</property> <!-- 起始日期 -->
  </function>

• 按自然月分片：用PartitionByMonth算法，仅需将dateFormat改为yyyyMM。

适用场景：

时间序列数据（如日志表、流水表、订单表），需 “按时间归档” 的场景；

优势是 “查询时可按时间范围快速定位分片”（如查 “2024 年 5 月的订单” 直接路由到order_202405），且历史数据可单独迁移 / 删除。

4.MyCat 管理与监控

(1)MyCat 管理原理与基础命令

MyCat 提供 “管理端口（默认 9066）”，用于执行管理命令（区别于服务端口 8066，后者用于接收 SQL 请求）。

连接管理端口

# 格式：mysql -h MyCatIP -P 9066 -u 用户名 -p 密码（用户名/密码在server.xml中配置）
mysql -h 192.168.1.101 -P 9066 -u mycat_user -p123456

常用管理命令

(2)MyCat 管理工具

除了命令行，还可通过 “可视化工具” 更直观地管理 MyCat，常用工具包括：

MyCat Web

定位：MyCat 官方提供的可视化管理平台，支持配置管理、分片监控、SQL 统计。

核心功能

配置管理：在线编辑schema.xml/rule.xml，支持热加载；

分片监控：查看各数据节点的连接数、SQL 执行量、错误率；

慢 SQL 分析：记录执行时间超阈值的 SQL，展示路由分片信息。

部署方式：需单独部署 MyCat Web 服务，关联 MyCat 的管理端口，通过浏览器访问（默认端口 8082）。

第三方工具

Navicat/DBeaver：通过 “MySQL 连接”（端口 8066）连接 MyCat，可视化执行 SQL、查看逻辑表结构；

Prometheus+Grafana：通过 MyCat 的 “监控指标接口” 采集数据，自定义监控面板（后续 “监控” 部分详细讲）。

(3)MyCat 监控实战

监控的核心目标是 “提前发现问题”（如连接耗尽、分片延迟、SQL 错误率飙升），需重点监控以下指标：

核心监控指标

基于 Prometheus+Grafana 的监控部署

这是企业级常用的监控方案，步骤如下：

1. 部署 Prometheus：

   下载 Prometheus，配置prometheus.yml，添加 MyCat 的监控目标（MyCat 需开启 “Prometheus 指标暴露”，通过server.xml配置prometheusPort=9100）：

   scrape_configs:
     - job_name: 'mycat'
       static_configs:
         - targets: ['192.168.1.101:9100'] # MyCat的Prometheus端口

2. 部署 Grafana：

   下载并启动 Grafana（默认端口 3000），通过浏览器访问并登录（初始账号 admin/admin）；

   在 Grafana 中添加 “Prometheus 数据源”：配置数据源类型为 Prometheus，填写 Prometheus 地址（如http://192.168.1.102:9090）；

   导入 MyCat 监控面板：在 Grafana 中搜索 “MyCat” 相关的开源面板（如面板 ID：12345，具体可在 Grafana 官网查找），或自定义面板，添加上述核心监控指标的图表（如 “活跃连接数趋势图”“SQL 执行成功率饼图”）。

3. 配置告警规则：

   在 Prometheus 中定义告警规则（如alert.rules.yml），示例 “活跃连接数过高” 的告警：

   groups:
   - name: mycat_alerts
     rules:
     - alert: MyCatHighActiveConnections
       expr: mycat_active_connections / mycat_total_connections > 0.9
       for: 5m
       labels:
         severity: warning
       annotations:
         summary: "MyCat活跃连接数过高"
         description: "MyCat当前活跃连接数{{ $value }}，已超过总连接数的90%，持续5分钟"

   在 Grafana 中配置告警渠道（如邮件、企业微信、钉钉），当指标触发阈值时，自动发送告警通知。

MyCat 自带监控工具

若无需复杂监控，可使用 MyCat 自带的轻量监控：

mycat-monitor.sh脚本：位于 MyCat 的bin目录下，执行后可实时查看 MyCat 的连接数、SQL 执行量等基础指标；

日志监控：MyCat 的日志文件（位于logs目录，如mycat.log、sql.log）记录了 SQL 执行详情、错误信息，可通过tail -f mycat.log实时查看，或结合 ELK（Elasticsearch+Logstash+Kibana）进行日志聚合分析。