java简单搭建分布式架构

最新推荐文章于 2025-07-05 12:29:51 发布

原创最新推荐文章于 2025-07-05 12:29:51 发布 · 置顶 · 1.8w 阅读

36 ·

CC 4.0 BY-SA版权

本文介绍了一种通过将大型数据库分拆成多个小型数据库并利用多线程技术来提高查询效率的方法。通过对不同数据库并发查询，显著提升了整体查询速度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一般来说，数据库的数据过多，查询效率就很慢，这时候我们如果把表分库到不同的数据库，这时候访问速度就会快很多，如果并且采用多线程去访问的话，查询速度也会提高的更快，我这里是运行内存8核电脑进行测试的单个访问mysql数据库的200万数据，时间在8秒左右，但是把表分到10个数据库里面，每张表20万，总共也是200万，并且采用多线程访问，这里只需要3-5秒左右，下面是代码：

jar包

数据库：这里我们知道我是把所有数据库都放到一个一个服务器里面，当我们有两个服务器，相当于每个服务器是5个数据库，访问效率会更高，如果在往上加，效率就会更低，但是服务器成本也高。

/**
 * 
 */
package com.liuchaojun;

/**
 * @author liuchaojun  数据库访问对象。用于插入数据。
 * @date 2018-3-20 上午10:06:11 
 */
public class DemoDAO
{
    private int a;
    private String b;
    private int c;

    public int getA()
    {
        return a;
    }
    public void setA(int a)
    {
        this.a = a;
    }
    public String getB()
    {
        return b;
    }
    public void setB(String b)
    {
        this.b = b;
    }
    public int getC()
    {
        return c;
    }
    public void setC(int c)
    {
        this.c = c;
    }
}

 /* 
 */
package com.liuchaojun;

/**
 * @author liuchaojun 保存数据库标识。每个线程由独立的对象存储
 * @date 2018-3-20 上午10:05:13 
 */
public class DBIndetifier
{   
    private static ThreadLocal<String> dbKey = new ThreadLocal<String>();

    public static void setDBKey(final String dbKeyPara)
    {   
        dbKey.set(dbKeyPara);
    }

    public static String getDBKey()
    {
        return dbKey.get();
    }
}

/**
 * 
 */
package com.liuchaojun;

import java.lang.reflect.Method;

/**
 * 数据库访问任务定义。将每一个对数据库访问的请求包装为一个任务对象，放到任务管理中， 然后等待任务执行完成，取出执行结果。
 * 
 * @author liuchaojun
 */
public class DBTask implements Runnable {
	// 操作数据库标识，用于指定访问的数据库。与spring配置文件中的数据动态数据源定义一致。
	private final String dbKey;

	// mybatis数据库访问对象
	private final Object dbAccessObject;

	// mysbatis数据库访问方法名称，用于反射调用
	private final String methodName;

	// 存储可变参数的值
	private final Object[] paraArray;

	// 存储可变参数类型
	@SuppressWarnings("rawtypes")
	private final Class[] paraClassArray;

	// 数据库操作结果。查询操作返回查询结果; 插入、删除、修改操作返回null。
	private Object operateResult;

	// 操作数据库抛出的异常信息
	private Exception exception;

	// 标识任务是否已经执行
	private boolean finish;

	/**
	 * 构造函数
	 * 
	 * @param dbKey
	 *            数据库标识
	 * @param dbAccessObject
	 *            数据库访问对象
	 * @param methodName
	 *            数据库访问方法名称
	 * @param paraArray
	 *            参数列表
	 */
	public DBTask(final String dbKey, final Object dbAccessObject,
			final String methodName, final Object... paraArray) {
		this.dbKey = dbKey;
		this.dbAccessObject = dbAccessObject;
		this.methodName = methodName;
		this.paraArray = paraArray;
		finish = false;
		exception = null;

		paraClassArray = new Class[paraArray.length];
		for (int index = 0; index < paraArray.length; ++index) {
			paraClassArray[index] = paraArray[index].getClass();
		}

		operateResult = null;
	}

	/**
	 * 
	 * 任务执行函数
	 * 
	 */
	@Override
	public void run() {
		try {
			DBIndetifier.setDBKey(dbKey);
			Method method = dbAccessObject.getClass().getMethod(methodName,
					paraClassArray);

			// 查询操作返回查询结果; 插入、删除、修改操作返回null
			operateResult = method.invoke(dbAccessObject, paraArray);
		} catch (Exception e) {
			exception = e;
			e.printStackTrace();
		}

		finish = true;
	}

	/**
	 * 
	 * 返回操作结果。查询操作返回查询结果; 插入、删除、修改操作返回null
	 * 
	 * @return 操作结果
	 */
	public Object getRetValue() {
		return operateResult;
	}

	/**
	 * 抛出数据库操作异常
	 * 
	 * @return 异常
	 */
	public Exception getException() {
		return exception;
	}

	/**
	 * 
	 * 返回任务是否已执行
	 * 
	 * @return 标记
	 */
	public boolean isFinish() {
		return finish;
	}
}

/**
 * 
 */
package com.liuchaojun;

import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

/**
 * @author liuchaojun 数据库访问任务管理。将数据库访问任务放到线程池中执行。
 * @date 2018-3-20 上午10:07:58
 */
public class DBTaskMgr {
	private static class DBTaskMgrInstance {
		public static final DBTaskMgr instance = new DBTaskMgr();
	}

	public static DBTaskMgr instance() {
		return DBTaskMgrInstance.instance;
	}

	private ThreadPoolExecutor pool;

	public DBTaskMgr() {
		pool = new ThreadPoolExecutor(10, 50, 60, TimeUnit.SECONDS,
				new ArrayBlockingQueue<Runnable>(10000),
				new ThreadPoolExecutor.CallerRunsPolicy());
	}

	public void excute(Runnable task) {
		pool.execute(task);
	}
}

/**
 * 
 */
package com.liuchaojun;

import java.io.Serializable;

/**
 * @author liuchaojun 映射结果定义
 * @date 2018-3-20 上午10:06:39 
 */
public class DemoResult implements Serializable
{
    /**
     * Comment for <code>serialVersionUID</code><br>
     * 
     */
    private static final long serialVersionUID = -413001138792531448L;
    private DemoDAO demoDAO;
    
	/**
	 * @return the demoDAO
	 */
	public DemoDAO getDemoDAO() {
		return demoDAO;
	}

	/**
	 * @param demoDAO the demoDAO to set
	 */
	public void setDemoDAO(DemoDAO demoDAO) {
		this.demoDAO = demoDAO;
	}
    
    
}

package com.liuchaojun;

import java.util.List;

/**
 * @author liuchaojun Mybatis 映射服务实现
 * @date 2018-3-20 上午09:50:26 
 */
public class DemoServiceImpl implements IDemoService
{
    private IDemo idemo = null;

    public void setIdemo(IDemo idemo) {
        this.idemo = idemo;
    }

    @Override
    public void insertDemo(DemoDAO demo)
    {
        idemo.insertDemo(demo);
    }

    @Override
    public List<Integer> selectGroup()
    {   
        return idemo.selectGroup();
    }
}

package com.liuchaojun;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @author liuchaojun 动态数据源。可根据不同的数据索引连接不同的数据库
 * @date 2018-3-20 上午10:05:48 
 */
public class DynamicDataSource extends AbstractRoutingDataSource
{
    @Override
    public Object determineCurrentLookupKey()
    {
        return DBIndetifier.getDBKey();
    }
}

package com.liuchaojun;

import java.util.List;

/**
 * @author liuchaojun Mybatis 映射接口
 * @date 2018-3-20 上午09:44:03 
 */
public interface IDemo 
{   
    public void insertDemo(DemoDAO demo);
    public List<Integer> selectGroup();
}

package com.liuchaojun;

import java.util.List;

/**
 * @author liuchaojun Mybatis 映射服务接口
 * @date 2018-3-20 上午09:45:34 
 */
public interface IDemoService
{   
    public void insertDemo(DemoDAO demo);
    public List<Integer> selectGroup();
}

package com.liuchaojun;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * @author liuchaojun
 * @date 2018-3-20 上午10:09:12
 */
public class TestMain {
	/**
	 * 测试代码
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext(
				"cfg/spring.xml");
		IDemoService service1 = (IDemoService) context.getBean("iDemoService");

		// 创建任务对象
		DBTask task1 = new DBTask("test1", service1, "selectGroup");
		DBTask task2 = new DBTask("test2", service1, "selectGroup");
		DBTask task3 = new DBTask("test3", service1, "selectGroup");
		DBTask task4 = new DBTask("test4", service1, "selectGroup");
		DBTask task5 = new DBTask("test5", service1, "selectGroup");
		DBTask task6 = new DBTask("test6", service1, "selectGroup");
		DBTask task7 = new DBTask("test7", service1, "selectGroup");
		DBTask task8 = new DBTask("test8", service1, "selectGroup");
		DBTask task9 = new DBTask("test9", service1, "selectGroup");
		DBTask task10 = new DBTask("test10", service1, "selectGroup");
		DBTask task_single = new DBTask("test_single", service1, "selectGroup");

		// 单个线程直接查询单表
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		DBTaskMgr.instance().excute(task_single);
		System.out.println("开始查询test_single表中的200万数据："+ format.format(new Date()));
		while (true) {
			if (!task_single.isFinish()) {
				try {
					Thread.sleep(1000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			} else {
				break;
			}
		}
		System.out.println("结束查询test_single表中的200万数据结束:"+ format.format(new Date()));

		// 多个线程查询多个数据库中的表数据
		List<DBTask> taskList = new ArrayList<DBTask>();
		taskList.add(task1);
		taskList.add(task2);
		taskList.add(task3);
		taskList.add(task4);
		taskList.add(task5);
		taskList.add(task6);
		taskList.add(task7);
		taskList.add(task8);
		taskList.add(task9);
		taskList.add(task10);
		for (DBTask task : taskList) {
			DBTaskMgr.instance().excute(task);
		}
		System.out.println("开始查询10个数据库中的t_test_table表中的20万数据表："+ format.format(new Date()));
		while (true) {
			int success = 0;
			for (DBTask task : taskList) {
				if (!task.isFinish()) { // 运行线程run方法这里为false,没有运行择true
					continue;
				} else {
					++success;
				}
			}
			if (success == 10) {
				break;
			}
		}
        System.out.println("结束查询10个数据库中的t_test_table表中的20万数据表："+ format.format(new Date()));
	}
}

demoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 
<mapper namespace="com.liuchaojun.IDemo">    
    <insert id="insertDemo" parameterType="com.liuchaojun.DemoDAO">
        insert into t_test_table(a, b, c) values(#{a}, #{b}, #{c});
    </insert>

    <resultMap id="demoDAO" type="com.liuchaojun.DemoDAO">
        <id column="a" property="a" />
        <result column="b" property="b" />
        <result column="c" property="c" />
    </resultMap>
 

    <select id="selectGroup" resultMap="demoDAO">
        select * from t_test_table
    </select>
</mapper>

mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 
<mapper namespace="com.liuchaojun.IDemo">    
    <insert id="insertDemo" parameterType="com.liuchaojun.DemoDAO">
        insert into t_test_table(a, b, c) values(#{a}, #{b}, #{c});
    </insert>

    <resultMap id="demoDAO" type="com.liuchaojun.DemoDAO">
        <id column="a" property="a" />
        <result column="b" property="b" />
        <result column="c" property="c" />
    </resultMap>
 

    <select id="selectGroup" resultMap="demoDAO">
        select * from t_test_table
    </select>
</mapper>

spring.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <mappers>
        <mapper resource="cfg/demoMapper.xml"/>
    </mappers>
</configuration>

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

    <bean id="dataSource_1" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test1"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_2" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test2"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_3" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test3"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_4" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test4"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_5" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test5"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_6" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test6"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>


    <bean id="dataSource_7" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test7"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_8" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test8"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_9" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test9"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_10" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test10"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource_single" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test_single"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="maxActive" value="100"></property>
        <property name="maxIdle" value="30"></property>
        <property name="maxWait" value="500"></property>
        <property name="defaultAutoCommit" value="true"></property>
    </bean>

    <bean id="dataSource" class="com.liuchaojun.DynamicDataSource">
        <property name="targetDataSources">
            <map>
                <entry key="test1" value-ref="dataSource_1"/>
                <entry key="test2" value-ref="dataSource_2"/>
                <entry key="test3" value-ref="dataSource_3"/>
                <entry key="test4" value-ref="dataSource_4"/>
                <entry key="test5" value-ref="dataSource_5"/>
                <entry key="test6" value-ref="dataSource_6"/>
                <entry key="test7" value-ref="dataSource_7"/>
                <entry key="test8" value-ref="dataSource_8"/>
                <entry key="test9" value-ref="dataSource_9"/>
                <entry key="test10" value-ref="dataSource_10"/>
                <entry key="test_single" value-ref="dataSource_single"/>
            </map>
        </property>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="configLocation" value="classpath:cfg/mybatis.xml"></property>
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean id="iDemo" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="com.liuchaojun.IDemo"></property>
        <property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
    </bean>

    <bean id="iDemoService" class="com.liuchaojun.DemoServiceImpl">
        <property name="idemo" ref="iDemo"></property>
    </bean>

</beans>

数据表的批量插入数据存储过程：
DROP PROCEDURE IF EXISTS pro_test1_insert;
DELIMITER //
CREATE PROCEDURE pro_test1_insert(cnt int)
BEGIN
DECLARE i int DEFAULT 0;
START TRANSACTION;
WHILE i<cnt
DO
INSERT INTO t_test_table(b,c) VALUES(i,'168');
set i=i+1;
end WHILE;
COMMIT;
end //
DELIMITER;

CALL pro_test1_insert(1000000);