MySQL运维之分库分表（一）

一枝梧桐知识技能 2024-11-21 6

问题分析

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

IO 瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。
CPU 瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU 出现瓶颈。

为了解决上述问题，我们需要对数据库进行分库分表处理。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分策略

分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下：

垂直拆分

垂直分库

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

特点：

每个库的表结构都不一样
每个库的数据也不一样
所有库的并集是全量数据

垂直分表

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：

每个表的结构都不一样
每个表的数据也不一样，一般通过一列（主键/外键）关联
所有表的并集是全量数据

水平拆分

水平分库

水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。

特点：

每个库的表结构都一样
每个库的数据都不一样
所有库的并集是全量数据

水平分表

水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

特点：

每个表的表结构都一样
每个表的数据都不一样
所有表的并集是全量数据

在业务系统中，为了缓解磁盘 IO 及 CPU 的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分库，还是分表，都需要根据具体的业务需求具体分析。

实现技术

shardingJDBC:基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持 Java 语言，性能较高。
MyCat:数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

本次课程，我们选择了是 MyCat 数据库中间件，通过 MyCat 中间件来完成分库分表操作。

MyCat概述

介绍

MyCat 是开源的、活跃的、基于 Java 语言编写的 MySQL 数据库中间件。可以像使用 mysql 一样来使用 MyCat,对于开发人员来说根本感觉不到 MyCat 的存在。

开发人员只需要连接 MyCat 即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。具体的分库分表的策略，只需要在 MyCat 中配置即可。

优势：

性能可靠稳定
强大的技术团队
体系完善
社区活跃

下载

下载地址：http://dl.mycat.org.cn/

安装

MyCat 是采用 Java 语言开发的开源的数据库中间件，支持 Windows 和 Linux 运行环境，下面介绍MyCat 的 Linux 中的环境搭建。我们需要在准备好的服务器中安装如下软件。

MySQL
JDK
Mycat

服务器	安装软件	说明
192.168.200.210	JDK、Mycat	MyCat中间件服务器
192.168.200.210	MySQL	分片服务器
192.168.200.213	MySQL	分片服务器
192.168.200.214	MySQL	分片服务器

具体的安装步骤：参考资料中提供的《MyCat安装文档》即可，里面有详细的安装及配置步骤。

目录介绍

bin:存放可执行文件，用于启动停止 MyCat
conf:存放 MyCat 的配置文件
lib:存放 MyCat 的项目依赖包（jar）
logs:存放 MyCat 的日志文件

概念介绍

在 MyCat 的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构。

在 MyCat 的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的数据存储还是在物理结构，也就是数据库服务器中存储的。

在后面讲解 MyCat 入门以及 MyCat 分片时，还会讲到上面所提到的概念。

MyCat入门

需求

由于 tb_order 表中数据量很大，磁盘 IO 及容量都到达了瓶颈，现在需要对 tb_order 表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上, 具体的结构，参考下图：

环境准备

准备 3 台服务器：

192.168.200.210: MyCat 中间件服务器，同时也是第一个分片服务器
192.168.200.213:第二个分片服务器
192.168.200.214:第三个分片服务器

并且在上述 3 台数据库中创建数据库 db01.

配置

1、schema.xml

在 schema.xml 中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下：

<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
  <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"/>
  </schema>

  <dataNode name="dn1" dataHost="dhost1" database="db01" />
  <dataNode name="dn2" dataHost="dhost2" database="db01" />
  <dataNode name="dn3" dataHost="dhost3" database="db01" />

  <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
  slaveThreshold="100">
  <heartbeat>select user()</heartbeat>

  <writeHost host="master" url="jdbc:mysql://192.168.200.210:3306?
  useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
  user="root" password="1234" />
  </dataHost>

  <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
  <heartbeat>select user()</heartbeat>

  <writeHost host="master" url="jdbc:mysql://192.168.200.213:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
  </dataHost>

  <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
  <heartbeat>select user()</heartbeat>

  <writeHost host="master" url="jdbc:mysql://192.168.200.214:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"user="root" password="1234" />
  </dataHost></mycat:schema>

2、server.xml

需要在 server.xml 中配置用户名、密码，以及用户的访问权限信息，具体的配置如下：

<user name="root" defaultAccount="true">
  <property name="password"> 123456 </property>
  <property name="schemas">DB01</property>
  <!-- 表级 DML 权限设置 -->
  <!--
<privileges check="true">
<schema name="DB01" dml="0110" >
<table name="TB_ORDER" dml="1110"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
  <property name="password"> 123456 </property>
  <property name="schemas">DB01</property>
  <property name="readOnly">true</property></user>

上述的配置表示，定义了两个用户 root 和 user ，这两个用户都可以访问 DB01 这个逻辑库，访问密码都是 123456 ，但是 root 用户访问 DB01 逻辑库，既可以读，又可以写，但是 user 用户访问 DB01逻辑库是只读的。

测试

启动

配置完毕后，先启动涉及到的 3 台分片服务器，然后启动 MyCat 服务器。切换到 MyCat 的安装目录，执行如下指令，启动 MyCat:

# 启动bin/mycat start
# 停止bin/mycat stop

MyCat 启动之后，占用端口号 8066.

启动完毕之后，可以查看 logs 目录下的启动日志，查看 MyCat 是否启动完成。

测试

1、连接 MyCat

通过如下指令，就可以连接并登陆 MyCat.

mysql -h 192.168.200.210 -P 8066 -uroot -p

我们看到我们是通过 MySQL 的指令来连接的 MyCat,因为 MyCat 在底层实际上是模拟了 MySQL 的协议。

2、数据测试

然后就可以在 MyCat 中来创建表，并往表结构中插入数据，查看数据在 MySQL 中的分布情况。

CREATE TABLE TB_ORDER (
  id BIGINT( 20 ) NOT NULL,
  title VARCHAR( 100 ) NOT NULL,
  PRIMARY KEY (id)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  INSERT INTO TB_ORDER(id, title) VALUES(1, 'goods1');
  INSERT INTO TB_ORDER(id, title) VALUES(2, 'goods2');
  INSERT INTO TB_ORDER(id, title) VALUES(3, 'goods3');
  INSERT INTO TB_ORDER(id, title) VALUES(1, 'goods1');
  INSERT INTO TB_ORDER(id, title) VALUES(2, 'goods2');
  INSERT INTO TB_ORDER(id, title) VALUES(3, 'goods3');
  INSERT INTO TB_ORDER(id, title) VALUES(5000000, 'goods5000000');
  INSERT INTO TB_ORDER(id, title) VALUES(10000000, 'goods10000000');
  INSERT INTO TB_ORDER(id, title) VALUES(10000001, 'goods10000001');
  INSERT INTO TB_ORDER(id, title) VALUES(15000000, 'goods15000000');
  INSERT INTO TB_ORDER(id, title) VALUES(15000001, 'goods15000001');

经过测试，我们发现，在往 TB_ORDER 表中插入数据时：