mysql分表，批量生成数据

一、mysql的分表策略

根据经验，Mysql表数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉；

1，水平分割：

水平(横向)拆分：将同一个表的数据进行分块保存到不同的数据库中，来解决单表中数据量增长出现的压力。

表结构设计水平切分。常见的一些场景包括
a). 比如在线电子商务网站，订单表数据量过大，按照年度、月度水平切分

b). Web 2.0网站注册用户、在线活跃用户过多，按照用户ID范围等方式，将相关用户以及该用户紧密关联的表做水平切分

c). 例如论坛的置顶帖子，因为涉及到分页问题，每页都需要显示置顶贴，这种情况可以把置顶贴水平切分开来，避免取置顶帖子时从所有帖子的表中读取

例：QQ的登录表。假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1亿条，就小了很多，比如qq0,qq1,qq1...qq99表。

用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。

这就是水平分割。

2，垂直分割：

垂直分割指的是：表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。

表结构设计垂直切分。常见的一些场景包括

a). 大字段的垂直切分。单独将大字段建在另外的表中，提高基础表的访问性能，原则上在性能关键的应用中应当避免数据库的大字段

b). 按照使用用途垂直切分。例如企业物料属性，可以按照基本属性、销售属性、采购属性、生产制造属性、财务会计属性等用途垂直切分

c). 按照访问频率垂直切分。例如电子商务、Web 2.0系统中，如果用户属性设置非常多，可以将基本、使用频繁的属性和不常用的属性垂直切分开

例如学生答题表tt：有如下字段：

Id name 分数题目回答

其中题目和回答是比较大的字段，id name 分数比较小。

如果我们只想查询id为8的学生的分数：select 分数 from tt where id = 8;虽然知识查询分数，但是题目和回答这两个大字段也是要被扫描的，很消耗性能。但是我们只关心分数，并不想查询题目和回答。这就可以使用垂直分割。我们可以把题目单独放到一张表中，通过id与tt表建立一对一的关系，同样将回答单独放到一张表中。这样我们插叙tt中的分数的时候就不会扫描题目和回答了。

3，其他要点：

1)存放图片、文件等大文件用文件系统存储。数据库只存储路径，图片和文件存放在文件系统，甚至单独存放在一台服务器

二、Spring事务的隔离级别

1. ISOLATION_DEFAULT：这是一个PlatfromTransactionManager默认的隔离级别，使用数据库默认的事务隔离级别.

另外四个与JDBC的隔离级别相对应
2. ISOLATION_READ_UNCOMMITTED：这是事务最低的隔离级别，它允许另一个事务可以看到这个事务未提交的数据。
这种隔离级别会产生脏读，不可重复读和幻读。
3. ISOLATION_READ_COMMITTED：保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据
4. ISOLATION_REPEATABLE_READ：这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻读。
它除了保证一个事务不能读取另一个事务未提交的数据外，还保证了避免下面的情况产生(不可重复读)。
5. ISOLATION_SERIALIZABLE 这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻读。

其中的一些概念的说明：

脏读: 指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。（指一个线程中的事务读取到了另外一个线程中未提交的数据。）

不可重复读: 指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

幻读: 指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

三、jdbc批量插入几百万数据怎么实现？

1. 使用mysql的存储过程来实现插入万条记录

DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=100000 DO
        INSERT INTO text VALUES(i,CONCAT('姓名',i),'XXXXXXXXX');
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();

花费时间很长：

2. JDBC往数据库中普通插入方式

先来说说JDBC往数据库中普通插入方式，简单的代码大致如下，循环了1000条，中间加点随机的数值，毕竟自己要拿数据测试，数据全都一样也不好区分

private String url = "jdbc:mysql://localhost:3306/test01";
    private String user = "root";
    private String password = "root";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 1000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);27                     pstm.executeUpdate();
            }
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

输出结果：OK,用时：738199，单位毫秒，也就是说这种方式与直接数据库中循环是差不多的。

在讨论批量处理之前，先说说遇到的坑，首先，JDBC连接的url中要加rewriteBatchedStatements参数设为true是批量操作的前提，其次就是检查mysql驱动包时候是5.1.13以上版本（低于该版本不支持），因网上随便下载了5.1.7版本的，然后执行批量操作（100W条插入），结果因为驱动器版本太低缘故并不支持，导致停止掉java程序后，mysql还在不断的往数据库中插入数据，最后不得不停止掉数据库服务才停下来...

那么低版本的驱动包是否对100W+数据插入就无力了呢？实际还有另外一种方式，效率相比来说还是可以接受的。

3. 使用事务提交方式

先将命令的提交方式设为false，即手动提交conn.setAutoCommit(false);最后在所有命令执行完之后再提交事务conn.commit();

private String url = "jdbc:mysql://localhost:3306/test01";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 100000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.executeUpdate();
            }
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

以上代码插入10W条数据，输出结果：OK,用时：18086，也就十八秒左右的时间，理论上100W也就是3分钟这样，勉强还可以接受。

4. 批量处理

接下来就是批量处理了，注意，一定要5.1.13以上版本的驱动包。

private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";//注意url地址要加上rewriteBatchedStatements=true
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 100000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.addBatch();
            }
            pstm.executeBatch();
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

10W输出结果：OK,用时：3386，才3秒钟.

参考博客：https://www.cnblogs.com/fnz0/p/5713102.html

posted @ 2018-08-22 20:45 四季写爱阅读(2483) 评论(0) 编辑收藏举报

刷新页面返回顶部

四季写爱