SQL筆記

02-12

草草翻完了《高性能MySQL》，印象最深的地方就是：這確實不適合初學者去看。

花了三個月的時間慢慢看完了這本，看的一頭霧水。第一章概論講了不少新奇的概念，比如隔離級別，比如多版本並發控制(MVVC)。但是從第二章起，所講的內容基本就和日常應用沒什麼太大關係了。如果要簡單概括一下的話就是

1. 儲存引擎務必使用InnoDB

2. 創建新資料庫時要用最新的MySQL版本(當然是GA版)

3. 輕易不要升級MySQL

然後就沒了。對於其他的內容，不管是伺服器性能優化還是MySQL主從備份，這些其實都和一名普通的技術人員沒有關係。相較於書中提到的通過修改frm文件實現快速新增列這種神奇方法，技術人員最好的做法還是：儘可能的避免使用alter語句或者說在項目一開始時就要想好數據表的用途。如果你是想改進自己的SQL水平的話，那應該去看 SQL快速優化300例 ,至少，不是這本書。

寫一下收穫吧(SQL相關，不僅僅是MySQL)，畢竟翻完了不是。

1. 盡量避免在線上使用alter

在使用alter新增列時,會引發一個全表鎖，資料庫會暫停響應直到新列添加完成(在已有數據中添加新列,在資料庫表結構中添加列定義)。鎖定的時間隨表內數據量的大小線性增長。如果是在線上環境的話，即使很短的時間，也夠阻塞不少請求了←_←

2. 可以通過冗餘數據來提升SQL查詢的性能

在標準的資料庫教科書中，數據表結構按說是要符合範式要求的(1NF~5NF)。比如，通過把用戶信息和訂單內容獨立開，這樣就可以設計出來沒有冗餘數據的資料庫表結構——俗稱學院派資料庫表結構。但是，學院派依託的環境是銀行系統這樣的大型工程，查詢帶來的性能損耗遠小於冗餘數據帶來的損耗。但在真實應用中，表裡的數據很少能達到1000萬行以上的，在這時，大部分的性能全浪費在多次查詢上了。這時候，學院派的做法就不如直接在數據表中添加冗餘數據(例如把用戶手機號和訂單一起存取),這樣在展示的時候一條SQL就可以搞定。

> 『PHP絕大部分的性能都浪費在和MySQL伺服器通訊上了』

3. 使用tinyint或者varchar作為枚舉類型，而不是使用enum

理由很簡單: enum在新增類型的時候需要使用alter語句進行全表新增，線上資料庫時不時的來上一回全表鎖誰受的了。。。

一般來說，使用 tinyint + 代碼中利用常量進行定義是最好的方案，如果要增強可讀性的話可以使用varchar, 因為常量一共也不超過10個字母，從性能上來說varchar也可以接受

4. 可視化工具

客戶端的話個人建議使用adminer,有ngnix之後配上一個index.php文件就能用。非要使用客戶端的話用MySQL Workbench也可以，MySQL自己出的。這兩個都在《高性能MySQL》的推薦之列，可以考慮

5. 使用調試語句查看性能

常用的調試語句如EXPLAIN, SHOW這種，現用現查即可

6. 索引數據一般都在內存里

結論:排序時直接使用索引排序是最快的,索引不要太多，太多之後跟把整個表放內存里就等效了(還不如使用Redis)

補充:排序時EXPLAIN發現不是index,而是filesort也不用太擔心，因為只有這兩種狀態，不是index就是filesort，性能只要不是太坑直接上就行

7. 分表分庫，歷史數據獨立建表

MySQL處理1000萬行以下的數據時性能是非常好的——那1000萬行以上時怎麼辦呢？

直接分表啊。

比如，可以按時間分，自增id在500萬之前的，獨立分到一個表裡，在程序代碼里寫死，用到的時候再去讀

或者，按一個數取模，根據餘數選擇對應的表。

8. 對於重要數據，一定要開啟二進位日誌

手滑刪過全表的同學都懂。。。

然後，解釋下兩個概念：

1. 隔離級別

每執行一次SQL稱為一件事務，如果事務所涉及到的內容在事務進行中發生了改變，對應於事務所能讀取到的實際內容，就產生了四種標準情況，這四種標準情況被稱為四種隔離級別(僅就MySQL而言，對於其他資料庫實現可能會有不同的區分標準)

1. READ UNCOMMIT(未提交讀)

在READ UNCOMMIT級別中，即使沒有提交，每個事務的操作對於其他事務也都是可見的。在這種情況下，事務可以讀取未提交的數據，又稱臟讀(DIRTY READ)。從性能上來說，臟讀並不比其他模式優秀多少，但是會引發各種嚴重的問題(比如說銀行存款數據寫入到一半來了一個讀操作。。。)。一般情況下，不建議使用

2. READ COMMIT(未提交讀)

大部分資料庫系統默認的隔離級別都是READ COMMIT(但MySQL不是)。在READ COMMIT這一級別中，事務所修改的數據只有提交了之後才會被其他事務讀取到。換句話說，一個事物從開始之後到結束之前，所做的任何修改對其他事物都是不可見的。這個級別實際上已經比較符合我們讀取數據的預期了。但是，如果執行兩次同樣的查詢，可能會出現兩遍結果不一致的情況(查詢執行過程中有其他事務提交完成),所以，這一級別又叫不可重複讀(nonrepeatable read)

3. REPEATABLE READ(可重複讀)

REPEATABLE READ解決了臟讀的問題，同時也是MySQL的默認事務隔離級別。這一級別保證了在同一個事務中多次讀取同樣的記錄結果是一致的。但是理論上，可重複讀還是沒法解決幻讀(Phantom Read)的問題。幻讀是指：在某個事務讀取某個範圍內的的記錄時(id>1 && id < 100),另外一個事物又再該範圍內插入了新紀錄，當之前的事務再次讀取該範圍的記錄時，就會產生幻行(Phantom Row)。不過InnoDB通過多版本並發控制(MVVC, Multiversion Concurrency Control)解決了這個問題

4. SERIALIZABLE(可串列話)

SERIALIZABLE是最高的隔離級別。它通過強制讓事務串列執行，可以避免前面所說的全部問題。本質上說，SERIALIZABLE會在讀取的每一行數據上都加上鎖, 對性能影響非常嚴重。只有在非常需要確保數據一致性，且可以接受沒有並發的情況下，才可以考慮使用此級別

2. 多版本並發控制

這是個很玄乎的詞，但說白了就是：通過保存數據在某個時間點的快照，來確保對於不同開始時間的事務，他們對於同一張表，在同一時刻看到的數據都是一樣的。

對於InnoDB來說就是：通過在每行記錄後邊保存兩個隱藏列，一列記錄創建時間，一列記錄過期時間（實際上存儲的是系統版本號）,每開始一個事務，系統版本號都會自動遞增。在事務開始時刻的系統版本號就會作為事務的版本號，用來作為資料庫查詢的依據，以此實現：多版本並發控制

大致就這些。看起來很高大上的一本書，實際上看了跟沒看差不多(DBA除外)。不推薦閱讀/購買