1：创建表和存储过程

mysql> create database trx;

Query OK, 1 row affected (0.02 sec)

mysql> USE trx

Database changed

mysql> CREATE TABLE test_trx(

    -> id int(5),

    -> uname char(30))

    -> engine =innodb;

Query OK, 0 rows affected (0.01 sec)

mysql> DROP PROCEDURE IF EXISTS pro_trx $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS pro_trx $$

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE pro_trx(count int UNSIGNED)

    -> BEGIN

    -> DECLARE fir INT UNSIGNED DEFAULT 1;

    -> DECLARE sec CHAR(50) DEFAULT REPEAT('id',50);

    -> WHILE fir <= count

    -> DO

    -> INSERT INTO test_trx SELECT null,fir;

    -> COMMIT;

    -> SET fir = fir+1;

    -> END WHILE;

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

　　

2:

mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

1 row in set (0.00 sec)

　　

设置为0

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 0     |

+--------------------------------+-------+

1 row in set (0.00 sec)

　　

mysql> CALL pro_trx(100000);

Query OK, 0 rows affected (10.20 sec)

　　

设置为1：

mysql> SET GLOBAL innodb_flush_log_at_trx_commit =1;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

1 row in set (0.00 sec)

　　

mysql> CALL pro_trx(100000);

Query OK, 0 rows affected (4 min 45.12 sec)

设置为2：

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 2;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 2     |

+--------------------------------+-------+

1 row in set (0.00 sec)

　　

mysql> CALL pro_trx(100000);

Query OK, 0 rows affected (13.80 sec)

　　

重新设置为0：观察下

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 0     |

+--------------------------------+-------+

1 row in set (0.00 sec)

　　

mysql> CALL pro_trx(100000);

Query OK, 0 rows affected (10.25 sec)

　　

innodb_flush_log_at_trx_commit设置为不同的值，性能差别很明显。

可以通过设置为0或者2来提高事物提高性能。但是这种设置丧失了ACID特性。而针对上述存储过程，为了提高事物的提交性能，应该在将10万行记录插入表后进行一次的COMMIT操作。而不是每插入一条就提交一次。这样做的优点是事物可以回滚到最初的状态。

2：改写一下存储过程

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS pro_trx_one_commit$$

Query OK, 0 rows affected, 1 warning (0.00 sec)

　　

mysql> CREATE PROCEDURE pro_trx_one_commit(count int UNSIGNED)

    -> BEGIN

    -> DECLARE fir INT UNSIGNED DEFAULT 1;

    -> DECLARE sec CHAR(50) DEFAULT REPEAT('id',50);

    -> WHILE fir <=count

    -> DO

    -> INSERT INTO test_trx SELECT null,fir;

    -> SET fir = fir+1;

    -> END WHILE;

    -> COMMIT;

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

　　

innodb_flush_log_at_trx_commit 设置为0：

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 0     |

+--------------------------------+-------+

1 row in set (0.00 sec)

　　

mysql> CALL pro_trx_one_commit(100000);

Query OK, 0 rows affected (10.19 sec)

　　

innodb_flush_log_at_trx_commit 设置为1：

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

1 row in set (0.00 sec)

　　

mysql> CALL pro_trx_one_commit(100000);

Query OK, 0 rows affected (5 min 27.33 sec)

　　

innodb_flush_log_at_trx_commit 设置为2：

mysql> CALL pro_trx_one_commit(100000);

Query OK, 0 rows affected (13.50 sec)

　　

3:再次改写存储过程

mysql> DELIMITER $$

mysql> CREATE PROCEDURE pro_trx_no_commit(count int UNSIGNED)

    -> BEGIN

    -> DECLARE fir INT UNSIGNED DEFAULT 1;

    -> DECLARE sec CHAR(50) DEFAULT REPEAT('id',50);

    -> WHILE fir <=count

    -> DO

    -> INSERT INTO test_trx SELECT null,fir;

    -> SET fir = fir+1;

    -> END WHILE;

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

　　

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

1 row in set (0.00 sec)

　　

mysql> CALL pro_trx_no_commit(100000)  ;

Query OK, 1 row affected (4 min 55.05 sec)

　　

mysql> SHOW VARIABLES LIKE '%commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| autocommit                     | ON    |

| innodb_commit_concurrency      | 0     |

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

3 rows in set (0.00 sec)

　　

mysql> SET GLOBAL autocommit = OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| autocommit                     | ON    |

| innodb_commit_concurrency      | 0     |

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

3 rows in set (0.00 sec)

另外开一个连接

mysql> SHOW VARIABLES LIKE '%commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| autocommit                     | OFF   |

| innodb_commit_concurrency      | 0     |

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

3 rows in set (0.00 sec)

　　

把自动提交关掉之后重新调用存储过程查看性能：

mysql> CALL pro_trx_no_commit(100000);

Query OK, 1 row affected (2.66 sec)

　　

mysql> USE trx

Database changed

mysql> CALL pro_trx_no_commit(100000);

Query OK, 1 row affected (2.66 sec)

mysql> CALL pro_trx_one_commit(100000);

Query OK, 0 rows affected (2.48 sec)

mysql> CALL pro_trx(100000);

Query OK, 0 rows affected (4 min 47.90 sec)

　　

最新文章

  1. 我的ORM之八-- 事件
  2. php课程---Windows.open()方法参数详解
  3. JAVA 上加密算法的实现用例---转载
  4. 如何测试私有 Private/Internal 方法
  5. Oracle 集群心跳及其參数misscount/disktimeout/reboottime
  6. 物理机（真实机）能ping通虚拟机，但是虚拟机无法ping通真实机（可能是防火墙问题）
  7. php 启动过程 - reqeust RINIT 过程
  8. laravel中with()方法，has()方法和whereHas()方法的区别
  9. 根据 inotify 自己开发软件监控文件系统活动
  10. Apollo单向SSL认证（1）
  11. es6学习笔记-set和map数据结构
  12. [UWP开发] 在低版本中使用亚克力刷以及部分高版本控件
  13. 使用 Crash 工具分析 Linux dump 文件
  14. cocos2d-js 和 createjs 性能对比（HTML5）
  15. date 命令之日期和秒数转换
  16. (转)MySQL 加锁处理分析
  17. 【Oozie】ambari安装oozie失败
  18. kaggle之数据分析从业者用户画像分析
  19. KVC, KVO 实现原理
  20. [Linux][Ubuntu18.04.1] nginx+php+MySQL环境搭建

热门文章

  1. 行尸走肉第一季/全集The Walking Dead迅雷下载
  2. http链接的性能测试工具httping
  3. 将iPod中的音乐拷贝到Mac中
  4. [转]Apache 配置虚拟主机三种方式
  5. 这篇NLP文章不错，BERT，好好读一下
  6. cesium原理篇（二）--网格划分【转】
  7. webAR涉及的技术【转】
  8. 如何使用chrome自带的Javascript调试工具 【转】
  9. 什么是L2 frame?
  10. IOS 沙盒与清除缓存