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;