当当分库分表（二）

最新推荐文章于 2022-03-29 15:30:57 发布

火柴头9527

最新推荐文章于 2022-03-29 15:30:57 发布

阅读量700

点赞数

分类专栏： sharding-jdbc 文章标签：分库分表 sharding-jdbc

本文链接：https://blog.csdn.net/jinjiankang/article/details/78895976

版权

sharding-jdbc 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

动一动、改一改，会有新的体验。本文在sharding-jdbc-example-mybatis原生代码的基础上稍加改动，和各位看官一起体验下在分库分表的背景下执行排序、分页查询与传统单表下的不同。

在OrderMapper.xml里新增：

<select id="selectWhereOrderLimit" resultMap="baseResultMap">
    SELECT
    <include refid="baseColumnList"/>
    FROM t_order where user_id > 50 order BY user_id desc limit 10, 10
</select>

在OrderRepository.java里新增：

List<Order> selectWhereOrderLimit();

在OrderService.java里新增，import语句省略：

public void fooService_02() {
    Random random = new Random();
    for (int i = 0; i < 100; i++) {
        Order order = new Order();
        int randomUserId = random.nextInt(100);
        order.setUserId(randomUserId);
        orderRepository.insert(order);
    }

    List<Order> list = orderRepository.selectWhereOrderLimit();
    System.out.println("###### list.size()=" + list.size());
    for (Order order : list) {
        System.out.println("###### order=" + order);
    }
}

新增类Main02.java：

@Service
@Transactional
public class Main02 {
    public static void main(final String[] args) {
        // CHECKSTYLE:ON
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("META-INF/mybatis/mysql/mybatisContext.xml");
        OrderService orderService = applicationContext.getBean(OrderService.class);
        orderService.fooService_02();
    }
}

配置JDBC监控https://github.com/ttddyy/datasource-proxy。顺便说一下，但单数据源下，使用p6spy可以很好地监控JDBC SQL，但在多个数据下，p6spy无法区分某个SQL是哪个数据源执行的，至少我没有找到配置方法。如果你知道，请留言给我。

在顶层pom.xml里新增：

<properties>
    <datasource-proxy.version>1.4.5</datasource-proxy.version>
</properties>

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>net.ttddyy</groupId>
            <artifactId>datasource-proxy</artifactId>
            <version>${datasource-proxy.version}</version>
        </dependency>
    </dependencies>
</dependencyManagement>

在本项目，称为模块可能更合理，pom.xml里新增：

<dependency>
    <groupId>net.ttddyy</groupId>
    <artifactId>datasource-proxy</artifactId>
</dependency>

将shardingContext.xml拷贝一份，改名为：shardingContext02.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:context="http://www.springframework.org/schema/context" 
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:rdb="http://www.dangdang.com/schema/ddframe/rdb"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd 
                        http://www.springframework.org/schema/tx 
                        http://www.springframework.org/schema/tx/spring-tx.xsd
                        http://www.springframework.org/schema/context 
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://www.dangdang.com/schema/ddframe/rdb 
                        http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">
    <context:component-scan base-package="com.dangdang.ddframe.rdb.sharding.example.jdbc" />

    <bean id="proxyConfig0"
          factory-bean="proxyConfigSupport0"
          factory-method="create"/>

    <bean id="proxyConfigSupport0" class="net.ttddyy.dsproxy.support.ProxyConfigSpringXmlSupport">
        <property name="dataSourceName" value="my-ds-0"/>
        <property name="queryListener" ref="queryListener"/>
        <property name="methodListener" ref="methodListener"/>
    </bean>

    <bean id="proxyConfig1"
          factory-bean="proxyConfigSupport1"
          factory-method="create"/>

    <bean id="proxyConfigSupport1" class="net.ttddyy.dsproxy.support.ProxyConfigSpringXmlSupport">
        <property name="dataSourceName" value="my-ds-1"/>
        <property name="queryListener" ref="queryListener"/>
        <property name="methodListener" ref="methodListener"/>
    </bean>

    <bean id="queryListener" class="net.ttddyy.dsproxy.listener.ChainListener">
        <property name="listeners">
            <list>
                <bean class="net.ttddyy.dsproxy.listener.logging.SystemOutQueryLoggingListener"/>
            </list>
        </property>
    </bean>

    <bean id="methodListener" class="net.ttddyy.dsproxy.listener.CompositeMethodListener">
        <property name="listeners">
            <list>
                <bean class="net.ttddyy.dsproxy.listener.TracingMethodListener"/>
            </list>
        </property>
    </bean>

    <bean id="ds_0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ds_0"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    
    <bean id="ds_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ds_1"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <bean id="dataSource0" primary="true" class="net.ttddyy.dsproxy.support.ProxyDataSource">
        <property name="dataSource" ref="ds_0"/>
        <property name="proxyConfig" ref="proxyConfig0"/>
    </bean>

    <bean id="dataSource1" primary="true" class="net.ttddyy.dsproxy.support.ProxyDataSource">
        <property name="dataSource" ref="ds_1"/>
        <property name="proxyConfig" ref="proxyConfig1"/>
    </bean>
    
    <rdb:strategy id="databaseShardingStrategy" sharding-columns="user_id" algorithm-class="com.dangdang.ddframe.rdb.sharding.example.jdbc.algorithm.SingleKeyModuloDatabaseShardingAlgorithm"/>
    <rdb:strategy id="tableShardingStrategy" sharding-columns="order_id" algorithm-class="com.dangdang.ddframe.rdb.sharding.example.jdbc.algorithm.SingleKeyModuloTableShardingAlgorithm"/>
    
    <rdb:data-source id="shardingDataSource">
        <rdb:sharding-rule data-sources="dataSource0, dataSource1">
            <rdb:table-rules>
                <rdb:table-rule logic-table="t_order" actual-tables="t_order_${0..1}" database-strategy="databaseShardingStrategy" table-strategy="tableShardingStrategy">
                    <rdb:generate-key-column column-name="order_id"/>
                </rdb:table-rule>
            </rdb:table-rules>
        </rdb:sharding-rule>
    </rdb:data-source>
    
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="shardingDataSource" />
    </bean>
    <tx:annotation-driven transaction-manager="transactionManager" />
</beans>

运行Main02.java，可以得到如下日志：

###### list.size()=10
###### order=order_id: 150306572881362944, user_id: 85, status: null
###### order=order_id: 150306573174964224, user_id: 83, status: null
###### order=order_id: 150306573208518656, user_id: 82, status: null
###### order=order_id: 150306573019774976, user_id: 81, status: null
###### order=order_id: 150306573334347776, user_id: 80, status: null
###### order=order_id: 150306573242073088, user_id: 80, status: null
###### order=order_id: 150306572801671168, user_id: 79, status: null
###### order=order_id: 150306573414039552, user_id: 78, status: null
###### order=order_id: 150306573342736384, user_id: 76, status: null
###### order=order_id: 150306573372096512, user_id: 75, status: null

将ds_1.t_order_0里的数据导出，并导入ds_0.t_order_0，让MySQL帮我们执行：

通过对比日志和MySQL帮我们执行的结果截图可以看出，Sharding-JDBC帮我们实现了分库分表环境下的条件查询、排序、Limit获取。

datasource-proxy生成的日志：

[815][success][0ms][conn=2] DelegatingPreparedStatement#getConnection()
Name:my-ds-0, Connection:2, Time:3, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["SELECT
         
        order_id,
        user_id,
        status
     
        FROM t_order_1 where user_id > 50 order BY user_id desc limit 0, 20"], Params:[()]
[816][success][3ms][conn=2] DelegatingPreparedStatement#execute()
[817][success][0ms][conn=2] DelegatingPreparedStatement#getConnection()
Name:my-ds-1, Connection:2, Time:6, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["SELECT
         
        order_id,
        user_id,
        status
     
        FROM t_order_1 where user_id > 50 order BY user_id desc limit 0, 20"], Params:[()]
[818][success][7ms][conn=2] DelegatingPreparedStatement#execute()
[819][success][0ms][conn=2] DelegatingPreparedStatement#getConnection()
Name:my-ds-1, Connection:2, Time:1, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["SELECT
         
        order_id,
        user_id,
        status
     
        FROM t_order_0 where user_id > 50 order BY user_id desc limit 0, 20"], Params:[()]
[820][success][1ms][conn=2] DelegatingPreparedStatement#execute()
[821][success][0ms][conn=2] DelegatingPreparedStatement#getConnection()
Name:my-ds-0, Connection:2, Time:2, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["SELECT
         
        order_id,
        user_id,
        status
     
        FROM t_order_0 where user_id > 50 order BY user_id desc limit 0, 20"], Params:[()]

my-ds-0和my-ds-1是我为两个数据源起的别名。从上述日志可以看出：

1、sharding-jdbc分别对my-ds-0和my-ds-1执行了分页查询，而且使用了 limit 0, 20，而不是limit 10, 10。

2、按我的理解，应该分别对my-ds-0和my-ds-1执行1次分页查询，但日志里却分别执行了两次，不理解？？？

MyBatis层与sharding-jdbc层分页查询规律：

limit 0, 10 ===> limit 0, 10
limit 10, 10 ===> limit 0, 20
limit 20, 10 ===> limit 0, 30
limit 30, 10 ===> limit 0, 40

。。。

可见，查询的页数越大，sharding-jdbc处理的记录数就越大。应该限制页数在一定范围内。当业务要查询的页数较大时，必须借助其他技术手段。

2018.1.12，更新：sharding-jdbc提供的SQL打印功能：

    <rdb:data-source id="shardingDataSource">
        <rdb:props>
            <prop key="sql.show">true</prop>
        </rdb:props>
        <rdb:sharding-rule data-sources="dbtbl_0,dbtbl_1">
（剩余省略）

火柴头9527

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
当当分库分表（二）

动一动、改一改，会有新的体验。本文在sharding-jdbc-example-mybatis原生代码的基础上稍加改动，和各位看官一起体验下在分库分表的背景下执行排序、分页查询与传统单表下的不同。在OrderMapper.xml里新增： SELECT FROM t_order where user_id > 50 order BY user_id desc
复制链接

扫一扫