大数据分表

我之前用过各种分表分区的方式去处理大数据的问题，但始终会存在一些问题，例如不能解决外键的关联问题。

这里我给出我研究后采用结合的视图的方式实现了主从表的关系。

首先创建两张user表：

CREATE TABLE IF NOT EXISTS `user1` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
 `name` varchar(50) DEFAULT NULL,  
 `sex` int(1) NOT NULL DEFAULT '0',  
PRIMARY KEY (`id`)  
) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 


CREATE TABLE IF NOT EXISTS `user2` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
 `name` varchar(50) DEFAULT NULL,  
 `sex` int(1) NOT NULL DEFAULT '0',  
PRIMARY KEY (`id`)  
) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 

然后给这两张表分别插入一条数据

INSERT INTO `user1` (`name`, `sex`) VALUES('张三', 0);  
INSERT INTO `user2` (`name`, `sex`) VALUES('李四', 1); 

接下来就是如何得到主表，这里我是使用一个视图来当成是分表的主表

create view user as ((select * from user1) union (select * from user2) )

这样user就能得到所有分表的数据了。

select * from user;

经过我的测试，使用视图来实现主从表，效果和性能都是OK的。

另外我们项目采用的Hibernate框架，同样也可以完美解决分表问题，包括实体类与主表映射，其实这里就变成了实现类与视图的映射，原来用的增删改查都不用变，完全可以用。查询时为了提高查询速度根据条件直接去查对应的分表即可，大大提高了查询效率。

这里我再帖出按年份，跨年时自动产生分表，并自动与主表关联的java代码

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"></span><pre name="code" class="java">import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

public class SubTableService implements ServletContextListener {
	
	private Timer timer;
	private String DB = "test";

	public void contextDestroyed(ServletContextEvent arg0) {
		if (timer!=null) {
			timer.cancel();
			System.out.println("Calander timer destroy.");
		}
	}

	public void contextInitialized(ServletContextEvent event) {
		System.out.println("Sub table service start......");
		timer = new java.util.Timer(true);
		int year = new Date().getYear();
		
		//初始化分表结构
		createSubTableConstruct(event.getServletContext(),year+1900,"user");
		
		//跨年时添加新年份的一张分表
		for (int i = 0; i < 10; i++) {
			year = year+1;
			SubTableTask subTableTask = new SubTableTask(event.getServletContext(),year+1900,"user");
			timer.schedule(subTableTask, new Date(year, 0, 1, 0, 0, 0));//在指定的时间点执行(跨年时执行)			
		}
		
	}
	
	private Connection getConnection() {
        Connection con = null;
        try {
        	Class.forName("com.mysql.jdbc.Driver");
        	con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+DB,"root","123456");
        } catch (Exception e) {
//            log.warn("数据库连接异常，请检查（IP、账号、密码、数据库名）是否正确");
        }
        return con;
    }
	
	private void createSubTableConstruct(ServletContext context, int year, String table){
		Connection con = getConnection();
        Statement stat = null;
        try {
            stat = con.createStatement();
            String sql = "SHOW TABLES LIKE '"+table+"_old'";
            ResultSet resultSet = stat.executeQuery(sql);
            if (resultSet.next()) {

			}else{
				sql = "create table "+table+"_"+year+" ENGINE=INNODB as select * from "+table+" where 1=2";
				stat.execute(sql);	
				
				sql = "alter table "+table+" rename "+table+"_old;";
				stat.execute(sql);
				
				sql = "create view "+table+" as (select * from "+table+"_"+year+")";
				stat.execute(sql);
				
				/*******************查找参照了此表的所有外键***********************/
				sql = "use information_schema";
				stat.execute(sql);								
				sql = "select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,"+
					  "REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from " +
					  "KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA = '"+DB+"' and REFERENCED_TABLE_NAME = '"+table+"_old'; ";
				ResultSet referenceResult = stat.executeQuery(sql);	
				List<RelatedTable> relatedTableList = new ArrayList<RelatedTable>();
				while(referenceResult.next()){
					RelatedTable relatedTable = new RelatedTable();
					relatedTable.setTablenNme(referenceResult.getString(1));
					relatedTable.setColumnName(referenceResult.getString(2));
					relatedTable.setConstraintName(referenceResult.getString(3));
					relatedTable.setReferencedTable(referenceResult.getString(4));
					relatedTable.setReferencedColumnName(referenceResult.getString(5));
					relatedTableList.add(relatedTable);
				}				
				sql = "use "+DB;
				stat.execute(sql);
				
				//删除关联的外键
				for (RelatedTable relatedTable : relatedTableList) {
					sql = "ALTER TABLE "+relatedTable.getTablenNme()+" DROP FOREIGN KEY "+relatedTable.getConstraintName()+";";
					stat.execute(sql);
				}
			}
        } catch (SQLException e) {
        	e.printStackTrace();
//            log.warn("SubTableTask.run()-->" + e.getMessage());
        } finally {
            try {
                if (con != null)
                    con.close();
                if (stat != null)
                    stat.close();
            } catch (SQLException e) {
//                log.warn("SubTableTask.run()-->" + e.getMessage());
            }
        }
	}

	private class SubTableTask extends TimerTask{
		
		private ServletContext context;  
		private int year;
		private String table;
		
		public SubTableTask(ServletContext context,int year, String table){
			this.context = context; 
			this.year = year;
			this.table = table;
		}

		@Override
		public void run() {
			Connection con = getConnection();
	        Statement stat = null;
	        try {
	            stat = con.createStatement();
	            String sql = "SHOW TABLES LIKE '"+table+"_"+year+"'";
	            ResultSet resultSet = stat.executeQuery(sql);
	            if (resultSet.next()) {
					
				}else{
					System.out.println("add sub table: "+table+"_"+year+"...");
					sql = "create table "+table+"_"+year+" ENGINE=INNODB as select * from "+table+"_old where 1=2";
					stat.execute(sql);						
					
					sql = "alter view "+table+" as "+getSubTableNames(con, table);
					stat.execute(sql);	
				}
	        } catch (SQLException e) {
	        	e.printStackTrace();
//	            log.warn("SubTableTask.run()-->" + e.getMessage());
	        } finally {
	            try {
	                if (con != null)
	                    con.close();
	                if (stat != null)
	                    stat.close();
	            } catch (SQLException e) {
//	                log.warn("SubTableTask.run()-->" + e.getMessage());
	            }
	        }
		}
		
	}
	
	private class RelatedTable {
		private String tablenNme;
		private String columnName;
		private String constraintName;
		private String referencedTable;
		private String referencedColumnName;
		
		public RelatedTable() {
			super();
		}
		
		public String getTablenNme() {
			return tablenNme;
		}
		public void setTablenNme(String tablenNme) {
			this.tablenNme = tablenNme;
		}
		public String getColumnName() {
			return columnName;
		}
		public void setColumnName(String columnName) {
			this.columnName = columnName;
		}
		public String getConstraintName() {
			return constraintName;
		}
		public void setConstraintName(String constraintName) {
			this.constraintName = constraintName;
		}
		public String getReferencedTable() {
			return referencedTable;
		}
		public void setReferencedTable(String referencedTable) {
			this.referencedTable = referencedTable;
		}
		public String getReferencedColumnName() {
			return referencedColumnName;
		}
		public void setReferencedColumnName(String referencedColumnName) {
			this.referencedColumnName = referencedColumnName;
		}
		
	}
	
	private String getSubTableNames(Connection con, String table) throws SQLException{
		Statement stat = null;
		stat = con.createStatement();
        String sql = "SHOW TABLES LIKE '"+table+"_%'";
        ResultSet resultSet = stat.executeQuery(sql);
        String subTableNames = "";
        while (resultSet.next()) {
			String tablename = resultSet.getString(1);
			if (!tablename.equals(""+table+"_old")) {
				if (subTableNames.equals("")) {
					subTableNames = "(select * from "+tablename+") ";
				}else{
					subTableNames = subTableNames+" union (select * from "+tablename+") ";					
				}
			}
		}
        return subTableNames;
	}
	
}

当然这需要在web.xml中配置好这个兼听器，让它在服务器启动时就有这个自动分表功能。

在插入时根据年份把它放到相应的分表，查询时同样也根据年份到分表里去查数据。