当前位置：首页 -> 基础 -> 数据库编程

《高性能mysql》札记第一波(三)

2015-01-23 22:03:53 · 作者: · 浏览: 15
标签: 《高性能 mysql 札记 第一
-----+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set


mysql> insert into test values(4,4);
Query OK, 1 row affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+-------+
4 rows in set

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set 3 4 mysql> commit;
Query OK, 0 rows affected mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set


mysql> commit;
Query OK, 0 rows affected 5 ? ? 从以上结果可以看得出来“该级别保证了同一个事务中，多次读取同样的结果是一致的”。这里并没有出现幻行，因为我的数据库引擎为innodb，书中说这种引擎可以解决幻读的问题。

?

?

隔离级别为可重复读，测试update情况
事务1 事务2
mysql> start transaction;
Query OK, 0 rows affected


mysql> select value from test where id=1;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set		 ?
? mysql> start transaction;
Query OK, 0 rows affected


mysql> select value from test where id =1;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set


mysql> update test set value=11 where id=1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0


mysql> select value from test where id =1;
+-------+
| value |
+-------+
| 11 |
+-------+
1 row in set
mysql> select value from test where id=1;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set		 ?
? mysql> commit;
Query OK, 0 rows affected


mysql> select value from test where id=1;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set


mysql> commit;
Query OK, 0 rows affected		 ?
? ?
update的时候同样符合预期结果。

?

?

关于事务日志 关于事务日志的说明中，我们可以看得出来，只要修改的数据已经写入到日志并且持久化了，数据本身还没有写入磁盘时，即使断电了，系统在重启的时候依然会将数据恢复。那么我们再来看看官网给出的innodb_flush_log_at_trx_commit参数的介绍 
Controls the balance between strict ACID compliance for commit operations, and higher performance
that is possible when commit-related I/O operations are rearranged and done in batches. You can
achieve better performance by changing the default value, but then you can lose up to a second of
transactions in a crash.
? The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB
log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.
? With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once
per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed
at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second,
due to process scheduling issues. Because the flush to disk operation only occurs approximately once
per second, you can lose up to a second of transactions with any mysqld process crash.
? With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction
commit and the log file is flushed to disk approximately once per second. Once-per-second flushing
is not 100% guaranteed to happen every second, due to process scheduling issues. Because the
flush to disk operation only occurs approximately once per second,
大致的意思是将该属性主要是为数据库的ACID原则进行服务的，并且默认为1，但是实际情况下（我们项目是结合spring和mybatis，可能是某一方面设置不当），设置为2会提高很多的事务性能，从文档中可以看得出来，“ 1的时候，innodb的缓存会在事务提交或者每秒钟时都会进行磁盘的刷新操作，2的时候，innodb缓存会在提交事务时写入到事务日志但不会刷新磁盘，然后在每秒钟时进行磁盘刷新操作”，2要比1提高很多性能，但是对于隐患来说，我没有太好的理解，按照文档中给出的结果好像是“在操作系统崩溃的时候，2的情况下，会丢失1秒的数据”，但是仔细想想发生的时间节点，1.事务没有commit时，断电了，此时肯定数据是没有更新成功的，因为都还没有来得及写入事务日志，2.事务提交后，