易妖游戏网

全部频道

您的当前位置：首页【Mysql调优】7、Sharding Sphere 实战

【Mysql调优】7、Sharding Sphere 实战

来源：易妖游戏网

1、Sharding Sphere框架认识

1）介绍

  • Sharding Sphere是一套开源的分布式数据库中间件解决方案组成的生态圈
  • Sharding Sphere的前身是Sharding JDBC
  • Sharding Sphere是为数不多的国产活跃的数据库中间件

2）结构介绍

  • 数据分片就是指分库分表

  • 接入端：

      Sharding JDBC（入侵业务代码，在JDBC层提供额外服务，使用客户端连接数据库，以jar包的方式提供服务，可理解为增强版JDBC，完全兼容JDBC和各种ORM框架）
  Sharding-Proxy（中间件，类似于nginx一样的代理服务器，不需要入侵代码，但是需要单独部署）
  Sharding-Sidecar（研发中）

  • 开发生态：可以自己开发二次更改

2、Sharding Sphere实现分库分表

1）准备

两个MySQL，分别创建数据库mall_0和mall_1，然后创建相同的数据库表。


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0`  (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;


DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1`  (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;


SET FOREIGN_KEY_CHECKS = 1;

然后创建maven项目，导入依赖，并创建applicationContext.xml配置Sharding-sphere分库分表的配置信息

2）Sharding-jdbc配置

Pom依赖

		<!-- sharding-jdbc 核心包-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <!-- sharding-jdbc 与spring 结合的组件：sharding-jdbc-spring-namespace -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <!--spring的核心包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>
        <!--Java链接msyql驱动包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--目前最快的链接池-->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>3.2.0</version>
        </dependency>
        <!--spring单元测试框架-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>
        <!--junit测试框架-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

Sharding-Jdbc的xml配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd">

    <!--JDBC数据源配置-->
    <bean id="ds0" class="com.zaxxer.hikari.HikariDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mall_0?useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>

    <!--JDBC数据源配置-->
    <bean id="ds1" class="com.zaxxer.hikari.HikariDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mall_1?useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>

    <!--分库策略：基于order.user_id值对2取余保存到ds0或ds1的数据库中-->
    <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id"
                              algorithm-expression="ds$->{user_id % 2}"/>
    <!--分表策略：基于order.order_id对2取余决定存储在对应数据库的 t_order_0或者t_order_1表中-->
    <sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id"
                              algorithm-expression="t_order_$->{order_id % 2}"/>
	<!--分库分表数据源策略信息配置-->
    <sharding:data-source id="shardingDataSource">
        <!--sharding:sharding-rule：定义分表规则，data-source-names="ds0,ds1" 说明有几个数据源-->
        <sharding:sharding-rule data-source-names="ds0,ds1">
            <sharding:table-rules>
                <!--
                sharding:table-rul 节点定义数据存储规则
                logic-table="t_order" 代表数据库当中的逻辑表名
                actual-data-nodes="ds$->{0..1}.t_order_$->{0..1} 说明数据分布在ds0与ds1两个数据源,物理表存在t_order_0与t_order_1
                database-strategy-ref="databaseStrategy" 指向分库策略databaseStrategy
                table-strategy-ref="orderTableStrategy"  指向分表策略orderTableStrategy

                实例:insert into t_order(order_id , user_id,status) values( ? , ? , 'N')；
                不同情况的数据分布：
                order_id  user_id
                1         2           -> ds0.order_1
                2         2           -> ds0.order_0
                3         1           -> ds1.order_1
                4         1           -> ds1.order_0
                -->
                <sharding:table-rule logic-table="t_order"
                                     actual-data-nodes="ds$->{0..1}.t_order_$->{0..1}"
                                     database-strategy-ref="databaseStrategy"
                                     table-strategy-ref="orderTableStrategy"
                />
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>
</beans>

3）Sharding- jdbc测试

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author： SheepJin
 * @date： 2022/9/18
 * @description：
 * @modifiedBy：
 */
/*使用SpringJUnit4ClassRunner创建运行环境*/
@RunWith(SpringJUnit4ClassRunner.class)
/*加载Sharding配置信息*/
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class ShardingTests {

    /*指向applicationContext.xml配置文件中sharding:data-source标签的id（name值会爆红，idea的问题，不影响）*/
    @Resource(name = "shardingDataSource")
    private DataSource datasource;

    @Test
    public void testInsert() throws SQLException {
        for (int index = 1; index <= 2; index++) {
            Connection connection = datasource.getConnection();
            int data = index % 2 == 0 ? 1 : 2;
            String saveSql = "insert into t_order(order_id , user_id,status) values( " + data + " , " + data + " , 'N')";
            PreparedStatement preparedStatement = connection.prepareStatement(saveSql);
            preparedStatement.execute();
            connection.close();
        }
    }

    @Test
    public void testQuery() throws SQLException {
        Connection connection = datasource.getConnection();
        String qyerySql = "select * from t_order";
        PreparedStatement preparedStatement = connection.prepareStatement(qyerySql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String queryResult = "{id:" + resultSet.getLong("order_id") + ",user_id:" + resultSet.getLong("user_id") + ",status:" + resultSet.getString("status") + "}";
            System.out.println(queryResult);
        }
        connection.close();
    }
}

插入结果：

总结：

（1）插入查询解析
# 插入时
# 我们的逻辑SQL：
insert into t_order(order_id , user_id,status) values(1，1，'N');
insert into t_order(order_id , user_id,status) values(2，2，'N');

# 分别被分解为：
insert into mall1.t_order_1(order_id , user_id,status) values(1，1，'N');
insert into mall0.t_order_0(order_id , user_id,status) values(2，2，'N');

# 查询时
# 我们的逻辑SQL：
select * from t_order;
# 被分解为
select * from mall0.t_order_0;
select * from mall1.t_order_1;
# 分别进行查询，然后将结果进行拼装后返回
# 需要注意的是如果逻辑SQL后有查询条件，会相应的拼接在分解后的SQL中
# 例如：
select * from t_order where order_id < 10;
# 分解后就是：
select * from mall0.t_order_0 where order_id < 10;
select * from mall1.t_order_1 where order_id < 10;
（2）注意：分库分表严禁使用自增长主键

自增长主键会导致不同表的数据发生主键冲突，推荐使用分布式ID，Sharding-Sphere内置提供了UUID.randomUUID()、SNOWFLAKE、时钟回拨、LEAF等等主键生成器。

使用方法：
在applicationContext.xml当中添加sharding:key-genertor标签，声明逐渐生成策略，并且在具体的分库分表数据源配置当中指向该配置：

<sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="order_id"/>

<sharding:table-rule logic-table="t_order"
                                     actual-data-nodes="ds$->{0..1}.t_order_$->{0..1}"
                                     database-strategy-ref="databaseStrategy"
                                     table-strategy-ref="orderTableStrategy"
                                     key-generator-ref="orderKeyGenerator"
                />

测试：

	//自动生成主键
	@Test
    public void testInsertGeneratorKey() throws SQLException {
        for (int index = 1; index <= 10; index++) {
            Connection connection = datasource.getConnection();
            int data = index % 2 == 0 ? 1 : 2;
            String saveSql = "insert into t_order(order_id , user_id,status) values(" + data + " , 'N')";
            PreparedStatement preparedStatement = connection.prepareStatement(saveSql);
            preparedStatement.execute();
            connection.close();
        }
    }

查询结果：

3、Mysql配置主从数据同步

（1）修改Mysql配置文件

[mysqld]
port=
server-id=
log-bin=
  • port：就是MySQL的启动端口
  • server-id：代表MySQL实例的唯一标识，和需要关联的MySQL实例不冲突即可
  • log-bin：启动bin-log，代表bin-log日志的名字

（2）给主服务器创建从服务器的访问用户

# 创建主从同步的用户，@后则为允许该用户slave访问的远程服务器ip地址，也就是从属服务器（可以写全部，则为%），identified by则为该用户的密码
create user 'slave'@'%' identified by '12345678';
# 给用户授权，*.*为所有权限
grant replication slave on *.* to 'slave'@'%';
# 刷新权限
flush privileges;

（3）查看主服务器的bin-log信息

# 查看最新bin-log信息
show master status;

  • file：当前最新的bin-log名称
  • position：当前bin-log当中最近的日志偏移量
# 查看bin-log当中的详细信息
show binlog events in 'binlog.000003';

  • Log_name：binlog日志文件名
  • Pos：该数据库操作的起始偏移量
  • Event_type：该数据库操作的类型
  • Server_id：Mysql实例的id，（1）中提到过
  • End_log_pos：该数据库操作的终止偏移量
  • Info：该数据库操作的详细信息

（4）给从服务器配置主服务器信息

# 配置主机信息
change master to
master_host = ''
,master_port =
,master_user = ''
,master_password = ''
,master_log_file = ''
,master_log_pos = ;

# 开启主从复制
start slave;

# 查看从节点的状态
show slave status;
# 结果中Slave_IO_Running和Slave_SQL_Running的结果均为Yes，即表示主从复制正在进行
  • master_host：主机ip
  • master_port：主机MySQL实例的端口
  • master_user：主机分配给从机的用户
  • master_password：
  • master_log_file：主机最新的binlog名称
  • master_log_pos：主机binlog日志中最大的偏移量，也就是上图中最后一条数据的End_log_pos，或者show master status结果中的Position。

完成上述操作，主服务器的任何操作即可复制到从从服务器当中。

4、Sharding-Jdbc实现读写分离

同3、分库分表中，在applicationContext.xml中添加读写分离配置：

	<!--
        定义ShardingJDBC主从分离(读写)数据源
        ds0是主服务器,用于写入操作，指向定义的数据源ID
        ds1是从属服务器,用于读取操作，指向定义的数据源ID
        其中,ds0也承担一部分查询职责.(生产环境不建议这样做)
    -->
    <master-slave:data-source id="msDataSouce"
                              master-data-source-name="ds0"
                              slave-data-source-names="ds0,ds1">
        <!--主从节点配置信息-->
        <master-slave:props>
            <!--开启日志打印-->
            <prop key="sql.show">true</prop>
        </master-slave:props>
    </master-slave:data-source>

代码测试：

		@Test
    public void testInsert() throws SQLException {
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into user(username) values('LI')");
        preparedStatement.executeUpdate();
        connection.close();
    }

    @Test
    public void testQuery() throws SQLException {
        Connection conn = dataSource.getConnection();
        for(int i = 0 ; i < 10 ; i++) {
            PreparedStatement pstmt = conn.prepareStatement("select * from user where user_id < " + i);
            ResultSet resultSet = pstmt.executeQuery();

            resultSet.close();
            pstmt.close();
        }
        conn.close();
    }

测试结果：

总结：

从SQL日志可以看出，Sharding-Jdbc是以轮训的方式将每条QuerySQL在不同的数据库当中进行数据读取，这就相对于传统单机MySQL服务器实例，主机的压力就会小很多，从而提高效率。

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文