java简单搭建分布式架构

旧城等待， 2022-05-29 08:43 159阅读 0赞

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

# **jar包** #

![104a8979550e14b48382463cd661ccf2.png][]

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

![3389221c34239c27860ef4d3f9fdb9b0.png][]![451187ac04150cd76393ff814080343a.png][]

/**
     * 
     */
    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

### 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);

项目源代码资源下载地址：https://download.csdn.net/download/qq\_27026603/10298566

[20180320201813545]: /images/20220529/f5fa84d4842f4cc094aaa99fb1bcf3a2.png
[104a8979550e14b48382463cd661ccf2.png]: /images/20220529/0295191ac80a49309ed42a4c4959de7b.png
[3389221c34239c27860ef4d3f9fdb9b0.png]: https://img-blog.csdnimg.cn/img_convert/3389221c34239c27860ef4d3f9fdb9b0.png
[451187ac04150cd76393ff814080343a.png]: /images/20220529/dae405f4608a46a2ae31320bbc0bc8c0.png