三个分表:
CREATE TABLE `test_1` (
id INT(11) NOT NULL DEFAULT '0',
email varchar(50) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;
CREATE TABLE `test_2` (
id INT(11) NOT NULL DEFAULT '0',
email varchar(50) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;
CREATE TABLE `test_3` (
id INT(11) NOT NULL DEFAULT '0',
email varchar(50) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;
一个总表:
CREATE TABLE `test` (
`id` INT(11) NOT NULL DEFAULT '0'
email varchar(50) NOT NULL
key `i_id` (`id`),
) ENGINE=MRG_MyISAM UNION=(test_1,test_2,test_3) DEFAULT CHARSET=UTF8;
在总表test上面select *的话,会取出三个分表中的数据。
在插入数据的时候根据对象的hash code 取模运算,如果余数为 3 那么就插入 test_3 这个表中。
一般总表不存数据 ,只是定义结构,为了防止别人插入数据 在建表语句后面添加
INSERT_METHOD=NO
在java程序里面首先需要建立总表和分表,由于分了100个表,如果手动下sql,那会烦死人的,所以通过程序生成这些表
package com.chen.crawler.utils;
/**
* 数据工具类
* @author xxx
*
*/
public class DbUtils {
private static final String USER_DB = "user";
private static final DBEngine userDBEngine ;
private static Connection conn = null;
private static Logger logger = Logger.getLogger(DbUtils.class.getName());
static{
userDBEngine = DBFactory.getDBEngine(USER_DB);
try {
conn = DBEngine.getConnection(USER_DB);
} catch (SQLException e) {
e.printStackTrace();
}
boolean tableExists = TableExists("userinfo_1");
if(!tableExists){
CreateMargeTable("userinfo");
for(int i = 0 ; i < 100; i++){
CreateTables("userinfo_"+i);
}
}
}
private DbUtils(){
}
private static boolean TableExists(String tableName){
String sql = "show tables like '"+tableName+"'";
try {
conn = DBEngine.getConnection(USER_DB);
java.sql.PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet resultSet = pstmt.executeQuery();
while (resultSet.next()) {
tableName = resultSet.getString(1);
if(tableName!=null && tableName.length()>1){
return true;
}else{
return false;
}
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return false;
}
/**
*建总表
*/
private static void CreateMargeTable(String tableName){
String sql = "CREATE TABLE `" + tableName + "` ("+
"`userId` bigint(15) NOT NULL ,"+
"`nickname` varchar(50) NOT NULL COMMENT '用户昵称',"+
"`qq` varchar(15) NOT NULL COMMENT 'QQ号码',"+
"`gender` tinyint(2) NOT NULL COMMENT '性别 1 :男 2:女',"
"`astro` varchar(5) NOT NULL COMMENT '星座的编码',"+
"`address` varchar(50) NOT NULL COMMENT '地址',"+
" KEY (`userId`),"+
" KEY `i_nickname` (`nickname`),"+
" KEY `i_address` (`address`)"+
" ) ENGINE=MRG_MyISAM INSERT_METHOD=NO DEFAULT CHARSET=utf8 union=(";
StringBuffer sb = new StringBuffer();
sb.append(sql);
for(int i =0;i<100 ;i++){
sb.append("userinfo_"+i).append(",");
}
sb.deleteCharAt(sb.length()-1).append(");");
try {
java.sql.PreparedStatement pstmt = conn.prepareStatement(sb.toString());
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void CreateTables(String tableName){
String sql = "CREATE TABLE `" + tableName + "` ("+
"`userId` bigint(15) NOT NULL auto_increment,"+
"`nickname` varchar(50) NOT NULL COMMENT '用户昵称',"+
"`qq` varchar(15) NOT NULL COMMENT 'QQ号码',"+
"`gender` tinyint(2) NOT NULL COMMENT '性别 1 :男 2:女',"+
"`astro` varchar(5) NOT NULL COMMENT '星座的编码',"+
"`address` varchar(50) NOT NULL COMMENT '地址',"+
" PRIMARY KEY (`userId`),"+
" KEY `i_nickname` (`nickname`),"+
" KEY `i_address` (`address`)"+
" ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
try {
java.sql.PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
*
* @param user
* @param tableName
* @return
*/
public static boolean InsertUserinfo(User user,String tableName) {
boolean result = false;
if (user == null || tableName==null) {
return false;
}
try {
String sql = "insert into " + tableName + "(nickname,qq,gender,astro,address) values(?,?,?,?,?,?,?)";
logger.info(sql);
result = userDBEngine.executeUpdate(sql, new Object[] {
user.getNickname(),user.getUin(),
user.getGender(),user.getAstro(),user.getAddress()
}) > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
表建好后就是插入表了,这里根据每个对象的hash code 取模运算
User user = new User(nickname,uin,gender,astro,from);
//按照 user对象的hashcode 进行模运算 0-99之间的规则来分表
String tableName = "userinfo_";
int code = user.hashCode()%100;
tableName += code;
InsertUserinfo(user, tableName);
这样就可以让每个表中的数据分布都比较均匀,而不会出现一个表的数据特别多,其他表的数据则特别少。
如果我们需要查询数据,可以直接通过总表查询
select * from userinfo limit 100;