mysql分库分表时的主键生成策略

传送门:http://blog.youkuaiyun.com/bluishglc/article/details/7710738#java

请各位参考上面地址.

这种主键生成方式,相对于twritter的生成方式,好处在于,id的值是可以控制的,你想让它生成什么样子的id都可以实现.

这里贴一下测试 程序 ,在我的机器上I7 2720 ,16G.单台mysql,最高达到2800/s.两台mysql并发,最高达到4800/s

受制于cpu的多线程处理能力 ,当用3台,4台mysql测试时,性能并没有任何 提高 ,由此可知,已经达到了cpu多线程的最高处理能力

package mysqlJDBC;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;

public class JdbcTest {

	@Test
	public void test() throws SQLException {

		 getConn37();
		  getConn38();
		long begin = System.currentTimeMillis();
		int step = 10000;
		long  total=3061;


		for (int i = 0; i < step; i+=2) {
//			System.out.println("count i="+i);
			new Thread(new Runnable() {

				@Override
				public void run() {

					try {
						DruidPooledConnection conn = getConn37();
						PreparedStatement ps1 = conn
								.prepareStatement("REPLACE INTO Tickets64 (stub) VALUES ('a')");
						PreparedStatement ps2 = conn
								.prepareStatement("SELECT LAST_INSERT_ID()");
						ps1.execute();
						ResultSet rs = ps2.executeQuery(); // 执行预处理语句获取查询结果集
						while (rs.next()) {
							System.out.println(rs.getString(1));
							System.out.println(System.currentTimeMillis() - begin);
						}
						rs.close();
						 ps1.close();
						 ps2.close();
						 conn.close();

					} catch (SQLException e) {
						e.printStackTrace();
					}

				}
			}).start();
			new Thread(new Runnable() {

				@Override
				public void run() {

					try {
						DruidPooledConnection conn = getConn38();
						PreparedStatement ps1 = conn
								.prepareStatement("REPLACE INTO Tickets64 (stub) VALUES ('a')");
						PreparedStatement ps2 = conn
								.prepareStatement("SELECT LAST_INSERT_ID()");
						ps1.execute();
						ResultSet rs = ps2.executeQuery(); // 执行预处理语句获取查询结果集
						while (rs.next()) {
							System.out.println(rs.getString(1));
							System.out.println(System.currentTimeMillis() - begin);
						}
						rs.close();
						 ps1.close();
						 ps2.close();
						 conn.close();

					} catch (SQLException e) {
						e.printStackTrace();
					}

				}
			}).start();

		}


		try {
			Thread.sleep(10000);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
		System.out.println("total is " + total);
		System.out.println("per fix is " + (step * 1000) / total);
	}

	private static DruidPooledConnection getConn37() {




		DruidPooledConnection conn = null;
		try {
			  conn = DbConnectionFactory.getDataSource37().getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}


private static DruidPooledConnection getConn38() {




		DruidPooledConnection conn = null;
		try {
			  conn = DbConnectionFactory.getDataSource38().getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}


}

 class DbConnectionFactory{
	 private DbConnectionFactory(){

	 }
	 public static DruidDataSource getDataSource38()
	 {
		 return Holder38.druidDataSource;
	 }
	 public static DruidDataSource getDataSource37()
	 {
		 return Holder37.druidDataSource;
	 }
	 static class  Holder38{
		 public  static  DruidDataSource druidDataSource= Holder38.getConnectionPool();
		 private static   DruidDataSource getConnectionPool(){
			 DruidDataSource	dataSource = new DruidDataSource();
				dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 
				dataSource.setUsername("root"); 
				dataSource.setPassword("1qaz2wsx"); 
				dataSource.setUrl("jdbc:mysql://192.168.1.38:3308/idserver"); 
				dataSource.setInitialSize(100); 
				dataSource.setMinIdle(50); 
				dataSource.setMaxActive(200); 
				// 启用监控统计功能  dataSource.setFilters("stat");
				dataSource.setPoolPreparedStatements(true);// for mysql
				dataSource.setMaxPoolPreparedStatementPerConnectionSize(100);
				//配置一个连接在池中最小生存的时间，单位是毫秒
				dataSource.setMinEvictableIdleTimeMillis(300000);
				 return dataSource;
		 }
	 }

	 static class  Holder37{
		 public  static  DruidDataSource druidDataSource= Holder37.getConnectionPool();
		 private static   DruidDataSource getConnectionPool(){
			 DruidDataSource	dataSource = new DruidDataSource();
				dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 
				dataSource.setUsername("root"); 
				dataSource.setPassword("1qaz2wsx"); 
				dataSource.setUrl("jdbc:mysql://192.168.1.37:3308/idserver"); 
				dataSource.setInitialSize(100); 
				dataSource.setMinIdle(50); 
				dataSource.setMaxActive(200); 
				// 启用监控统计功能  dataSource.setFilters("stat");
				dataSource.setPoolPreparedStatements(true);// for mysql
				dataSource.setMaxPoolPreparedStatementPerConnectionSize(100);
				//配置一个连接在池中最小生存的时间，单位是毫秒
				dataSource.setMinEvictableIdleTimeMillis(300000);
				 return dataSource;
		 }
	 }
 }