一般来说，数据库的数据过多，查询效率就很慢，这时候我们如果把表分库到不同的数据库，这时候访问速度就会快很多，如果并且采用多线程去访问的话，查询速度也会提高的更快，我这里是运行内存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;}/*** * 任务执行函数* */@Overridepublic void run() {try {DBIndetifier.setDBKey(dbKey);Method method = dbAccessObject.getClass().getMethod(methodName,paraClassArray);// 查询操作返回查询结果; 插入、删除、修改操作返回nulloperateResult = 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;}@Overridepublic void insertDemo(DemoDAO demo){idemo.insertDemo(demo);}@Overridepublic 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
{@Overridepublic 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,没有运行择truecontinue;} 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);