程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 《高性能mysql》札記第一波

《高性能mysql》札記第一波

編輯：MySQL綜合教程

《高性能mysql》札記第一波

事務就是一組原子性的SQL查詢，或者說一個獨立的工作單元。那麼如果數據庫引擎能夠成功的執行該組內的所有語句，那麼就執行該組語句。如果其中有任何一條語句執行失敗，則所有語句都不執行。也就是說，事務內的語句，要麼全部執行，要不都不執行。然後是事務的ACID特征。原子性：一個事務必須被視作一個不可分割的最小單元。整個事務中的所有操作要麼全部執行成功，否則全部回滾，不可能只執行其中一部分，確保事務內的所有語句是一個原子。一致性：數據庫總是從一個一致性的狀態到另外一個一致性的狀態。如果事務中一條語句執行失敗，任何已經執行成功的語句也不會保存到數據庫中。隔離性：通常情況下（這個因事務隔離級別有不同的情況），一個事務在最終提交之前，對其他事務時不可見的，注意是通常情況。持久性：一旦事務提交，則其所作的修改就會永久保存到數據庫中。這個概念我本人還沒有很好的理解（比如對與innodb來說，其中的一個參數innodb_flush_log_at_trx_commit，對於事務的提交後數據寫入磁盤的時間點有很大的不同。）一個實現了ACID的數據庫，比如對於mysql數據庫，通常會需要更強的CPU處理能力、更多的磁盤空間、更大的內存大小。但具體需要什麼樣的配置，我也沒有找出合理的方案，對於我們的期貨交易平台，到底需要什麼樣的配置更合理。隔離級別（每一種級別都規定了一個事務中所做的修改，哪些在事務內和事務間是可見的，哪些不可見。較低級別的隔離通常可以執行更高的並發，系統的開銷個更低）： READ UNCOMMITTED（未提交讀）：在這個級別，事務中的修改，即使沒有提交，對其他事務也都是可見的。事務可以讀取未提交的數據（被稱為髒讀），這種級別會有很多問題，性能也不會比其他級別好，不推薦使用。READ COMMITTED（提交讀）：一個事務開始時，只能看見已經提交的事務所作的修改，換言之，就是只要該事務沒有提交，其他事務是無法看見本事務已經做的修改。REPEATABLE READ（可重復讀）：該級別保證了同一個事務中多次讀取同樣記錄的結果是一致的（這塊我稍後會做出測試，但是有疑問），對於mysql的innodb引擎來說，該引擎使用了MVCC模式進行控制，解決了幻讀的問題，但是理論上，其他的引擎如果沒有解決幻讀的問題時，會出現幻行（對於幻讀和幻行，稍後再進行研究）。mysql默認的隔離級別為可重復讀。SERIALIZABLE（可串行化）：它強制事務串行執行，避免了幻讀的問題。簡單來說，該級別時，事務在讀取到每一行數據時都會加鎖，所以可能導致大量的爭鎖和超時問題，實際應用下，很少用到。自動提交（AUTOCOMMIT）：mysql默認采用自動提交模式。也就是說，如果不顯式的開始一個事務，則每個查詢都會被當作一個事務提交。也這樣說，如果顯示的開始一個事務，就是說使用了start transaction開啟事務的時候，事務內的所有查詢不再分離成一個個小的事務進行提交操作。事務日志：事務日志可以幫助提高事務的效率，存儲引擎在修改表的數據時只需要修改你內存拷貝，再把修改行為記錄到持久在硬盤上的事務日志中，而不用每次都將修改的數據持久到磁盤。事務日志采用的是追加的方式，因此寫日志的操作時磁盤上一小塊區域內的順序IO，所以采用事務日志的方式相對來說快很多。事務日志持久後，內存中修改的數據在後台可以慢慢的刷回磁盤，修改數據需要寫兩次磁盤。如果修改的數據已經記錄到事務日志並持久化，即使數據本身還沒有寫回磁盤，操作系統崩潰了，存儲引擎在重啟時能夠自動回復這部分數據。

實踐

提交讀和可重復讀的區別 提交讀的隔離級別下，我們進行下面的試驗，驗證“一個事務從開始知道提交之前，所作的任何修改對其他事務都是不可見的”，先測試insert的情況事務1 事務2 mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

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

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

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

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
+----+-------+
2 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 通過上面的順序執行，可以看出來“一個事務開始時，只能看到已經提交的事務所作的修改”，並且“一個事務從開始直到提交之前，所作的任何修改對其他事務都是不可見的”，然後在一個事務內“兩次執行同樣的查詢，可能會得到不一樣的結果”。
隔離級別不變，測試update情況事務1 事務2 mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

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

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows 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 * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 11 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 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 | 11 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set

mysql> commit;
Query OK, 0 rows affected 5 mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

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

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

mysql> select value from test where id = 1;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set mysql> select value from test where id = 1;
+-------+
| value |
+-------+
| 11 |
+-------+
1 row in set 8 9 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 10 從以上步驟可以看出來，使用READ-COMMITTED的時候，一個事務內部，其查詢結果會受到其他事務的update和insert影響。

隔離級別為可重復讀，測試insert情況首先我們先看一下命令行模式下，怎麼修改事務隔離級別（以下是把可重復讀改成提交讀，注意如果按照我的測試順序，請注意修改）

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql>

貌似隔離級別沒有變？關掉當前命令行，重新打開一個命令行

mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set

mysql>

事務隔離級別修改完畢後，我們開始測試事務1 事務2 mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set 1 2 mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from test;
+----+-------+
| 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.事務提交後，在寫入事務日志的時候，發生斷電，此時無論是參數的值是1還是2，都應該恢復不了數據了，3.每秒鐘刷新磁盤時，發生斷電，按照《高性能mysql》的字面意思，此時既然事務日志已經持久化了，那麼重啟後，數據是會自動恢復的。那麼疑問來了，2和1的隱患到底在什麼情況下會發生。關於autocommit mysql默認情況下，該參數是打開的，那麼這個參數對於應用了spring和mybatis的項目來說，到底是關閉該參數還是打開該參數，會有怎麼樣的性能影響，我沒有弄清楚，如果有熱心的讀者可以給出一點答復。另外我們可以通過以下命令進行該參數的設置，當然1為ON，0為OFF。

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set

mysql> set autocommit = 0;
Query OK, 0 rows affected

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set

總結：總體研究了不少時間，但是還沒有徹底挖清楚這個參數對性能的影響，但是以上這些知識點也非常的重要，再次懇請有這方面經驗的人給出幫助，非常感謝。