MySQL进阶（事务）——转账事务的问题 COMMIT，ROLLBACK & 百万条数据插入的性能调优=3万次提交变成1次

布满荆棘的人生 2024-02-22 09:30 39阅读 0赞

![在这里插入图片描述][ead904c8c50d4caeab4c25257a53001e.png]

前言

MySQL是一种开源的关系型数据库管理系统，它使用SQL作为其查询语言。MySQL是最流行的开源数据库之一，它具有高性能、可靠性和可扩展性。MySQL支持多用户、多线程和多表操作，可以在各种操作系统上运行。

MySQL最为最流行的开源数据库，其重要性不言而喻，也是大多数程序员接触的第一款数据库，深入认识和理解MySQL也比较重要。

本篇博客结合生活中常见的转账案例，分析了事务的案例，模拟网络失败情况下事务回滚的情况；分析了百万数据插入数据库时，如何借助提交模式来进行MySQL插入数据库的性能调优。

![在这里插入图片描述][95648726d67e4c3fa3a1e82d1be9013a.png]  
本系列文章合集如下：

[【合集】MySQL的入门进阶强化——从 普通人 到 超级赛亚人 的 华丽转身][MySQL_ _ _ _ _]

#### 目录 ####

*  引出
 *  转账业务事务问题
 *   *  SQL实现
     *   *  例子：网络中断了
         *  例子：自动提交模式下失效
     *  程序实现
 *  百万数据的性能调优
 *   *  1.逐条提交
     *  2.积攒一批提交
     *  3.变成1次提交
 *  总结

## 引出 ##

--------------------

1.结合生活中常见的转账案例，分析了事务的案例，模拟网络失败情况下事务回滚的情况；  
2.分析了百万数据插入数据库时，如何借助提交模式来进行MySQL插入数据库的性能调优；

## 转账业务事务问题 ##

![在这里插入图片描述][260af1f0e0d243639edee4a40d003c2f.png]

DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `cardId` int(0) NOT NULL,
      `money` int(0) NULL DEFAULT NULL,
      PRIMARY KEY (`cardId`) USING BTREE
    ) ;
    INSERT INTO `user` VALUES (1, 1000);
    INSERT INTO `user` VALUES (2, 1000);

![在这里插入图片描述][ac5cc4a56eff47b8be23173bbc238c7a.png]

### SQL实现 ###

SHOW VARIABLES LIKE 'autocommit';  -- on就是自动提交事务，所以我们没法做回滚....
     COMMIT;  -- 手动提交事务 ，数据就是正式的了，没法回滚了。
     -- 手动关闭以下自动提交事务  0
     set autocommit = 0;
     
    -- 1、转账业务事务问题
    select * from `user`;
    
    -- =================模拟正常转账，用户a给用户b转1000块钱================
    update `user` set money = money - 1000  where cardId = 1;     -- 用户a 扣除
    UPDATE `user` set money = money + 1000     where cardId = 2;     -- 用户b 增加
    
    COMMIT; 
    set autocommit = 1;
    
    -- 手动关闭以下自动提交事务  0
    set autocommit = 0;
    -- ===========模拟转账失败，用户a给用户b转1000块钱过程中，突然发生不可预知的问题
    update `user` set money = money - 1000  where cardId = 1;     -- 用户a 扣除  。 自动提交事务，也就意味这没法回滚了！！！！！
    
    settt @m = 5 / 0; -- 模拟网络中断问题.   (解决方案：把上一句代码的更新给他还原回去，事务回滚) 
    
    ROLLBACK;   -- 把上一次执行的语句全部还原。没效果。
    UPDATE `user` set money = money + 1000     where cardId = 2;     -- 用户b 增加

> 先把自动提交事务关闭

![在这里插入图片描述][9447bc07e6aa496aa2c646fb768def70.png]

> 此时，执行sql语句，并没有自动提交，数据库数据不变化，当执行commit提交之后，数据库数据才变化。

![在这里插入图片描述][e579c978e083419683dec81bd042db36.png]

#### 例子：网络中断了 ####

模拟转账的时候，出现网络中断的情况，此时由于关闭了事务自动提交，因此，数据库的数据还没有更新

![在这里插入图片描述][17f2c4cb0a3a4f69bb4b7a9b098ec1d6.png]  
数据库的数据还没变化

![在这里插入图片描述][be784e97aeb94e628f49ca545f62ef02.png]  
然后rollback回滚，再手动提交，数据库就回滚了。

![在这里插入图片描述][9a9284225ac6468a9d993eec400f39c5.png]

#### 例子：自动提交模式下失效 ####

![在这里插入图片描述][57affb9efd864fe99d601022a50c0b48.png]

在自动提交模式下，rollback命令失效

![在这里插入图片描述][71b1294b9ec94fa581e108ec67c47c36.png]

### 程序实现 ###

程序中来实现。 mysql存储引擎必须是innodb， 否则不支持事务

package com.tianju;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    /**
     * 模拟转账业务
     */
    public class SQLTestDemo {
        
        public static void main(String[] args) throws SQLException {
        
            String url = "jdbc:mysql://localhost:3306/car_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2b8";
            Connection conn = DriverManager.getConnection(url, "root", "123");
            // 手动关闭自动提交事务
            conn.setAutoCommit(false);
            try {
        
                PreparedStatement cmd1 = conn.prepareStatement("update `user` set money = money - 1000  where cardId = 1");
                cmd1.executeUpdate();
    
                // 模拟网络故障问题
                System.out.println(5 / 0);
    
                PreparedStatement cmd2 = conn.prepareStatement("update `user` set money = money + 1000  where cardId = 2");
                cmd2.executeUpdate();
    
                System.out.println("转账成功！！");
                conn.commit(); // 提交事务，让数据真正的持久化。（同时也不能回滚了！！！）
    
            } catch (Exception e) {
        
                System.out.println("转账失败.....");
                e.printStackTrace();
                // 回滚
                conn.rollback();
                System.out.println("事务已经回滚成功了.....");
            } finally {
        
    
                conn.setAutoCommit(true); //手动把自动提交事务打开，因为我们这个业务比较特殊，所以只是临时用一下而已。。。
                conn.close();  // 关闭连接
            }
        }
    }

查看一下系统所支持的引擎类型

> SHOW ENGINES

![在这里插入图片描述][24c08373240c4d7f9d18e65912ce19f6.png]

> 转账失败，捕获异常，事务回滚

![在这里插入图片描述][90648ff6653f4b458de421eafbe60b0e.png]

## 百万数据的性能调优 ##

> SHOW VARIABLES LIKE ‘autocommit’;

自动提交已经打开

![在这里插入图片描述][b438f8bba67e442d937183583d6ac347.png]

假设现在要插入30000条，3万条数据，对比不同提交方式下的性能

### 1.逐条提交 ###

![在这里插入图片描述][4a63d232faac4dfd974c4c701793c670.png]

### 2.积攒一批提交 ###

![在这里插入图片描述][02e9e25371ed41a99156901418437164.png]

### 3.变成1次提交 ###

![在这里插入图片描述][b1efc6286b014047b274ec7c6b9d73bf.png]

package com.tianju;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    /**
     * 性能调优演示
     */
    public class TimeTestDemo {
        
        // 添加10万条数据到user表
        // 一条一条添加,总共耗时：74232
        // 每执行一条SQL语句，就和数据据交互
        // 解决方案：批量执行SQL语句
        public static void v1() throws SQLException {
        
            String url = "jdbc:mysql://localhost:3306/car_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2b8";
            Connection conn = DriverManager.getConnection(url, "root", "123");
            PreparedStatement cmd = conn.prepareStatement("insert into user values(? , ?)");
            int count = 30000;
            // 大忌：尽量避免在循环里面访问数据库.....
            for (int i = 1; i <= count ; i++) {
        
                cmd.setObject(1,i);
                cmd.setObject(2, 1000);
                cmd.executeUpdate();
            }
            conn.close();
        }
    
    
        /**
         * 第二个版本，批量执行SQL
         * 总共耗时：130346
         * @throws SQLException
         */
        public static void v2() throws SQLException {
        
            String url = "jdbc:mysql://localhost:3306/car_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2b8";
            Connection conn = DriverManager.getConnection(url, "root", "123");
            PreparedStatement cmd = conn.prepareStatement("insert into user values(? , ?)");
            int count = 30000;
            // 大忌：尽量避免在循环里面访问数据库.....
            for (int i = 1; i <= count ; i++) {
        
                cmd.setObject(1,i);
                cmd.setObject(2, 1000);
                cmd.addBatch(); // 积攒SQL，把SQL堆在一起
                if (i % 2000 == 0) {
         //优化算法，每积攒到2000条SQL的时候，批量执行一次
                    cmd.executeBatch(); // 批量执行一堆SQL
                    cmd.clearBatch(); // 清空SQL缓存
                }
            }
            conn.close();
        }
    
    
        // 终极版本。
        // 每更新一条数据，会自动提交事务。（提交很影响性能！！）
        // 一共要提交3万次事务.........
        // 要把3万次事务，变成1次！！
        // 总共耗时：5885  , 放到上百万的服务器，几毫秒
        public static void v3() throws SQLException {
        
            String url = "jdbc:mysql://localhost:3306/car_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2b8";
            Connection conn = DriverManager.getConnection(url, "root", "123");
            conn.setAutoCommit(false);
            PreparedStatement cmd = conn.prepareStatement("insert into user values(? , ?)");
            int count = 30000;
            // 大忌：尽量避免在循环里面访问数据库.....
            for (int i = 1; i <= count ; i++) {
        
                cmd.setObject(1,i);
                cmd.setObject(2, 1000);
                cmd.executeUpdate();
            }
            conn.commit();
            conn.setAutoCommit(true);
            conn.close();
        }
    
    
    
        public static void main(String[] args) throws SQLException {
        
            long start = System.currentTimeMillis(); // 开始时间
            v3();
            long end = System.currentTimeMillis(); // 结束时间
            System.out.println("总共耗时：" + (end - start));
        }
    }

## 总结 ##

--------------------

[ead904c8c50d4caeab4c25257a53001e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/e5fe40b12f5a4ee88a26471030ea654d.png
[95648726d67e4c3fa3a1e82d1be9013a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/7e46b5d1031f455f9224be9f3e63b1c1.png
[MySQL_ _ _ _ _]: https://blog.csdn.net/Pireley/article/details/133816035
[260af1f0e0d243639edee4a40d003c2f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/0bd6c945513842f7982e0f80a099d13e.png
[ac5cc4a56eff47b8be23173bbc238c7a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/0a73284747f34f41967782e2b0e7c223.png
[9447bc07e6aa496aa2c646fb768def70.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/831e6d3fc60d42068c4591fbefc55982.png
[e579c978e083419683dec81bd042db36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/0f28861148a444ba9f52ebc7ccc11c97.png
[17f2c4cb0a3a4f69bb4b7a9b098ec1d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/891a946bc758469a8b46306ca5094ba7.png
[be784e97aeb94e628f49ca545f62ef02.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/9b784aed84c74a53a1f76f774e2261db.png
[9a9284225ac6468a9d993eec400f39c5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/3a5d6e3b34ce437381e1a5136ff95088.png
[57affb9efd864fe99d601022a50c0b48.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/7af145771fdd47209845c85f1f98f594.png
[71b1294b9ec94fa581e108ec67c47c36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/df508c3b033a4927ac847246d0a3f893.png
[24c08373240c4d7f9d18e65912ce19f6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/d59edbd17a2a4f0083ef6ec108fd491b.png
[90648ff6653f4b458de421eafbe60b0e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/bfd85c8cf68d42728c0f0aa8677280f3.png
[b438f8bba67e442d937183583d6ac347.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/d00d981d086745048875e3ac777db295.png
[4a63d232faac4dfd974c4c701793c670.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/653388068aba46b994a2b79714de571a.png
[02e9e25371ed41a99156901418437164.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/bc9b8919dc3545d7ac2eb7b8c29e1cc7.png
[b1efc6286b014047b274ec7c6b9d73bf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/22/fa53bbb7926d4cbe9c9943efe2ba2799.png