Mysql地毯式由淺入深學習（一）——Mysql的整體架構簡介，mysql地毯式

Mysql地毯式由淺入深學習（一）——Mysql的整體架構簡介，mysql地毯式

最近我覺得自己各項技能都是隨著工作而學習的，感覺總有一些知識沒有掌握，特準備在基於《Mysql權威指南》和《高效能Mysql》這兩本書來系統學習下，把每次學習的筆記整理成部落格的形式。一是為了加深自己的印象，二是為了給自己提供更好的學習能力，三是和大家共同分享。
1、邏輯架構

第一層：並不是Mysql專屬，大多數基於網路用戶端、伺服器工具，例如：串連處理、授權認證、安全等
第二層：核心服務層，包括查詢解析、分析、最佳化、緩衝、內建函數，所有跨域儲存引擎都在這一層：預存程序、觸發器、視圖
第三層：包含儲存引擎，負責Mysql資料的儲存和提取。
2、Mysql的並發控制
Mysql在伺服器層和儲存引擎層的的並發控制。
2.1 讀寫鎖
在處理並發讀寫時可以通過實現一個由兩種類型的鎖組成的鎖系統來解決問題，這兩種類型的鎖被稱為共用鎖定和獨佔鎖定（exclusive lock），也叫讀鎖和寫鎖
描述下鎖的概念：讀鎖是共用的，寫鎖是排他的。
在實際資料庫應用中每時每刻都在發生鎖定，Mysql鎖內部管理透明。
2.3 鎖粒度
一種提高共用資源並發性的方式就是讓鎖定對象更有選擇性。盡量只鎖定需要修改的部分資料。更理想的方式只對修改的資料片進行精確鎖定。鎖定的資料量越少並發程度越高。
加鎖也是消耗資源的，鎖的各種操作，包括獲得鎖、檢測鎖是否是否已解除、釋放鎖等。
所謂的鎖策略就是鎖的開銷和資料的安全性之間尋求平衡，這種平衡當然也會影響效能。
Mysql提供了多種選擇，每種儲存引擎都可以實現自己的鎖策略和鎖粒度。下面來介紹兩種鎖策略：
1、表鎖
Mysql基本策略，開銷最小。
2、行級鎖
最大程度的支援並發，同時也帶來了最大的鎖開銷。行級鎖只在儲存引擎層實現，而Mysql伺服器層沒有實現。伺服器層完全不瞭解儲存引擎中的鎖實現。
所有的儲存引擎都以自己的方式顯現了鎖機制。
3、事務
ACID原則
原子性atomicity、一致性consistency、隔離性isolation、持久性durability
1、隔離等級
read uncommitted（未提交讀），事務可以讀取到未提交的資料，稱為髒讀
read committed,一個事務未提交之前所做的對其他事務都是不可見的
repeatable read，解決了髒讀問題，該層級保證了同一個事務中多次讀取同樣記錄的結果是一致的。但是理論上無法解決幻讀，所謂幻讀是，當某個事務正在讀取某一個範圍的記錄時，另外一個事務又向該範圍插入一條新的記錄，當之前事務再次讀取時會產生幻行。Mysql的InnoDB和XtrsDB儲存引擎通過多版本並發控制（MVVC，multiversion concurrency control ）解決了幻讀問題。
serializable可序列化，串列執行，隔離等級最高，逾時和鎖爭用嚴重，不常用
2、死結
死結是指兩個或多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性迴圈。當事務試圖以不同的順序鎖定資源時，就可能產生死結。多個事務同時鎖定同一個資源時也會產生死結。
例如：
事務1：
start transaction;
update goods set price = 20 where goods_id = 4 and date=’2015-4-22’；
update goods set price = 30 where goods_id = 3 and date = ‘2015-4-22’；
commit;
事務2：
start transction;
update goods set price = 40 where goods_id = 3 and date = ‘2015-4-22’;
update goods set price = 50 where goods_id = 4 and date = ‘2015-4-22’;
commit;
為瞭解決這種問題，資料庫系統實現了各種死結檢測和死結逾時的機制，越複雜的系統，比如：InnoDB儲存引擎，越能檢測到死結的循環相依性，並立即返回一個錯誤。
還有一種解放方式，當查詢的時間達到鎖等待逾時的設定後放棄鎖請求。InnoDB目前處理死結的方法是：將持有最少行級鎖獨佔鎖定事務進行復原。
鎖的行為和順序和儲存引擎相關。以同樣的順序執行語句，有些儲存引擎會產生死結有些不會。死結有雙重原因：真正的資料衝突；儲存引擎的實現方式。
3、交易記錄
提高事務效率，如果修改的已經記錄到交易記錄並持久化，但資料本身還沒回寫磁碟，此時系統崩潰，儲存引擎在重啟時能夠自動回復這部分修改的資料。具體實現方式視儲存引擎而定。
4、Mysql中的事務
自動認可（autocommit）
Mysql預設採用自動認可模式，可以通過設定autocommit變數來啟用或禁用自動認可模式：
show VARIABLES like ‘autocommit’;

1或on表示啟用，0或off表示關閉。
Mysql可以通過執行 set transaction isolation level命令來設定隔離等級，新的隔離等級會在下一個事務開始的時候生效。
例如：set session transaction isolation level read committed;
在事務中混合使用儲存引擎
如果在事務中混合使用了事務型和非事務型的表（InnoDB和Myisam）正常提交是不會有問題的，如果交易回復，非事務型的表變更是無法撤銷的，這會導致資料庫處於不一致狀態。
隱式和顯式鎖定
InnoDB在事務執行過程中，隨時都可以執行鎖定，鎖只有在執行commit或者rollback的時候才會釋放，並且所有的鎖都是在同一時刻被釋放，前面描述的都是隱式鎖，InnoDB會根據隔離等級在需要的時候自動加鎖。
另外，InnoDB也支援通過特定的語句進行顯示鎖定
select … lock in share mode – 共用鎖定
select … for update – 獨佔鎖定
Mysql也支援lock tables和unlock tables，這都是在伺服器層實現的，和儲存引擎無關。
5、多版本並發控制
Mysql的大多數事務型儲存引擎實現都不是簡單的行級鎖。基於提升並發性考慮，一般都同時實現了多版本並發控制（MVCC），包括Oracle、PostgreSQL。不過實現各不相同。
可以認為MVCC是行級鎖一個變種，但是他很多情況下避免了加鎖操作，開銷更低。雖然實現機制有所不同，但大都實現了非阻塞的讀操作，寫操作也只鎖定必要的行。
MVCC的實現是通過儲存資料在某一個時間點快照來實現的。也就是說不管實現時間多長，每個事物看到的資料都是一致的。
分為樂觀（optimistic）並發控制和悲觀（pressimistic）並發控制。下面來說說是如何工作的：
InnoDB的MVCC是通過在每行記錄後面儲存兩個隱藏的列來實現。這兩個列一個儲存了行的建立時間，一個儲存行的到期時間（刪除時間）。當然儲存的並不是真實的時間而是系統版本號碼，每開始一個新的事務，系統版本號碼都會自動新增。事務開始時刻的系統版本號碼會作為事務的版本號碼，用來查詢到每行記錄的版本號碼進行比較，下面看下REPEATABLE READ隔離等級下MVCC如何工作：
SELECT
InnoDB會根據以下條件檢查每一行記錄：
a.InnoDB只尋找版本早於當前事務版本的資料行，這樣可以確保事務讀取的行要麼是在開始事務之前已經存在要麼是事務自身插入或者修改過的
b.行的刪除版本號碼要麼未定義，要麼大於當前事務版本號碼，這樣可以確保事務讀取到的行在事務開始之前未被刪除
只有符合上述兩個條件的才會被查詢出來
INSERT
InnoDB為新插入的每一行儲存當前系統版本號碼作為行版本號碼
DELETE
InnoDB為刪除的每一行儲存當前系統版本號碼作為行刪除標識
UPDATE
InnoDB為插入的一行新紀錄儲存當前系統版本號碼作為行版本號碼，同時儲存當前系統版本號碼到原來的行作為刪除標識
儲存這兩個版本號碼使大多數操作都不用加鎖。使資料操作簡單，效能很好，並且能保證只會讀取到複合要求的行。不足之處是每行記錄都需要額外的儲存空間，需要做更多的行檢查工作和一些額外的維護工作。
MVCC只在REPEATABLE READ和COMMITTED READ
6、Mysql的儲存引擎
6.1 InnoDB儲存引擎
資料存放區在資料表空間，資料表空間是由InnoDB管理的一個黑盒子，由一系列資料檔案組成，在Mysql4.1以後版本，InnoDB可以將表的資料和索引放在單獨的檔案中。
InnoDB採用MVCC來支援高並發，預設隔離等級是REPEATEABLE READ，並且通過間隙鎖(nex-key locking)策略防止幻讀的出現，使得InnoDB不僅僅鎖定查詢涉及的行還會對索引中的間隙進行鎖定，防止幻影行的插入。
InnoDB基於聚簇索引建立，對主鍵查詢有很高的效能，不過他的二級索引（非主鍵索引）必須能包含主鍵列，如果主鍵列很大的話其他的所有索引都會很大。因此如果索引較多的話主鍵當儘可能的小。
6.2 Myisam儲存引擎
Myisam會將表格儲存體在兩個檔案中：資料檔案和索引檔案，可儲存的行記錄數一般受限於可用的磁碟空間或者作業系統中單個檔案的最大尺寸。
特性：
1、加鎖與並發
對整張表加鎖，讀取時會對需要讀到的所有表加共用鎖定，寫入時則對錶加獨佔鎖定
2、修複
Mysql可用手工或自動執行檢查和修複操作，但不同於事務回複及崩潰修複。執行表的修複可能導致一些資料丟失，而且修複操作非常慢。
3、索引特性
即使是BLOB和TEXT等長欄位也可以基於其前500個字元建立索引。也支援全文索引，這是一種基於分詞建立的索引
大資料量
當我們建立或者管理很多InnoDB資料的資料量在3~5TB之間或者更大，這是但台機器的量不是一個分區（shard），這些系統運行得還不錯。如果資料量繼續增長到10TB可能需要建立資料倉儲，Infobright是Mysql資料倉儲最成功的解決方案。
轉換表的儲存引擎:
1、alter table mytable engine = InnoDB;執行時間長，消耗I/O能力，同時會在原表上加鎖，將會失去原引擎相關的所有特性，例如：如果將一張InnoDB錶轉換為Myisam再轉回去，原InnoDB表上所有的外鍵將丟失。
2、匯出與匯入
使用mysqldump工具將資料匯出到檔案，然後修改檔案create table語句的儲存引擎選項，同時修改表名，因為同一個資料庫中不能存在相同的表名，即使使用不同的儲存引擎。
3、建立與查詢
綜合上述兩種方法的高效和安全，不需要匯出整個表的資料，而是先建立一個新的儲存引擎表。然後利用insert … select文法來到資料
create table innodb_table like myisam_table;
alter table innodb_table engine=InnoDB;
insert into innodb_table select * from myisam_table;
資料量大考慮分批處理
start transaction;
insert into innodb_table select * from myisam_table where id between x and y;
commit;