用了多年的SQL Server 后, 最近開(kāi)始轉(zhuǎn)用Mysql 作為主要的數(shù)據(jù)庫(kù). 此系列文章記錄了此間學(xué)到了知識(shí)和經(jīng)驗(yàn).

作為開(kāi)篇, 首先介紹的是MySql 的一些基礎(chǔ)知識(shí).

1. MySql 邏輯架構(gòu)

MySq 存儲(chǔ)引擎最與眾不同的特性是它的存儲(chǔ)引擎架構(gòu). 這種架構(gòu)將查詢處理(Query Processing) 以及其它系統(tǒng)任務(wù)(Server Task)和數(shù)據(jù)的存儲(chǔ)/提取 進(jìn)行了分離.

這樣存儲(chǔ)和處理相分離的設(shè)計(jì), 可以在使用時(shí)根據(jù)性能,特性,以及其它需求來(lái)選擇數(shù)據(jù)存儲(chǔ)的方式.

例如, 可以主表使用InnoDB, 而用MyISAM作為緩存表的引擎, 將會(huì)得到更小的索引暫用空間, 并且可以做全文搜索.

從邏輯上, MySql 架構(gòu)分為三層: 連接/線程處理層; 核心服務(wù)層; 存儲(chǔ)引擎.

1.1 連接/線程處理層

類似大多數(shù)基于網(wǎng)絡(luò)的C/S 工具或服務(wù). 蓋層主要負(fù)責(zé)連接處理, 授權(quán)認(rèn)證, 安全等等.

每個(gè)客戶端連接在服務(wù)器進(jìn)程中擁有一個(gè)線程. 服務(wù)器會(huì)負(fù)責(zé)緩存線程, 從而不需要頻繁新建銷毀.

1.2 核心服務(wù)層

包含查詢解析, 分析, 優(yōu)化, 緩存以及所有的內(nèi)置函數(shù).

該層同時(shí)實(shí)現(xiàn)了所有跨存儲(chǔ)引起的功能: 存儲(chǔ)過(guò)程, 觸發(fā)器, 視圖等.

1.3 存儲(chǔ)引擎

• 負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取.
• 除了InnoDB會(huì)解析外鍵定義(因?yàn)镸ySql 服務(wù)器本身沒(méi)有實(shí)現(xiàn)) 外, 存儲(chǔ)引起不會(huì)去解析SQL.
• 核心服務(wù)層通過(guò)存儲(chǔ)引擎API與存儲(chǔ)引擎通信. 該API使得不同存儲(chǔ)引擎的差異對(duì)上層的查詢過(guò)程透明.
• 不同的存儲(chǔ)引擎間, 不會(huì)互通信, 而只是簡(jiǎn)單地響應(yīng)上層服務(wù)器的請(qǐng)求.

2 并發(fā)控制時(shí)的鎖

• 鎖粒度的選擇, 是在鎖的開(kāi)銷和數(shù)據(jù)的安全性之間尋求平衡.
• 多數(shù)數(shù)據(jù)庫(kù)會(huì)在表上施加row-level 鎖, 并采用手段保證鎖較多時(shí)的性能.
• MySql 每種存儲(chǔ)引擎都可以實(shí)現(xiàn)自己的鎖策略和鎖粒度.

3 MySql 中的事務(wù)

• MySql 提供了兩種事務(wù)型的存儲(chǔ)引擎: InnoDB和NDB Cluster.
• MySql 默認(rèn)采用自動(dòng)提交(AutoCommit)模式. 若不是顯式地開(kāi)始一個(gè)事務(wù),則每個(gè)查詢都被當(dāng)做一個(gè)事務(wù)執(zhí)行提交操作.
• 對(duì)于MyISAM或內(nèi)存表, 沒(méi)有Commit 或Rollback 的概念. 相當(dāng)于一直處于AutoCommit 模式.
• 有一些命令, 在執(zhí)行之前會(huì)強(qiáng)制執(zhí)行Commit 提交當(dāng)前的活動(dòng)事務(wù). 典型的例子, 在DDL中, 如果是會(huì)導(dǎo)致大量數(shù)據(jù)改變的操作, 如Alter Table, Lock Tables.
• MySql 服務(wù)層不管理事務(wù), 事務(wù)由下層的存儲(chǔ)引擎實(shí)現(xiàn). 如果在同一事務(wù)中,使用多種存儲(chǔ)引擎是不可靠的.

3.1 隱式和顯式鎖定

• InnoDB采用的是兩階段鎖定協(xié)議. 在事務(wù)執(zhí)行過(guò)程中, 隨時(shí)都可以執(zhí)行鎖定. 鎖只有在執(zhí)行Commit 或者RollBack 時(shí)才會(huì)在同一時(shí)刻釋放.
• InnoDB 會(huì)根據(jù)隔離級(jí)別在需要時(shí)自動(dòng)加鎖.
• 另外,也支持 Lock tables 和UnLock Tables語(yǔ)句來(lái)進(jìn)行顯式加鎖.
• 并且, MySql 在服務(wù)層也實(shí)現(xiàn)了Lock tables 和UnLock Tables的支持. 但并不能替代事務(wù)處理. 如需用到事務(wù), 還是應(yīng)該選擇事務(wù)型存儲(chǔ)引擎.

3.2 事務(wù)日志

• 存儲(chǔ)引擎在修改表數(shù)據(jù)時(shí), 只需修改其內(nèi)存拷貝, 再把該修改行為記錄到持久在硬盤上的事務(wù)日志中, 而不用每次都將修改數(shù)據(jù)本身持久到硬盤.
• 采用追加的方式. 寫日志是磁盤上一小塊區(qū)域內(nèi)的順序I/O, 速度更快.
• 日志持久化后, 內(nèi)存中修改的數(shù)據(jù), 在后天慢慢地刷回硬盤.

事務(wù)日志也被成為預(yù)寫式日志(Write-Ahead Logging). 修改數(shù)據(jù)需要兩次寫磁盤.
若在數(shù)據(jù)還未寫回磁盤時(shí)崩潰, 會(huì)在重啟后自動(dòng)修復(fù)該部分修改的數(shù)據(jù).

3.3 多版本并發(fā)控制(MVCC)

• 通過(guò)保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來(lái)實(shí)現(xiàn).
  • 在每行記錄上保存兩個(gè)隱藏的列, 行的創(chuàng)建時(shí)間和過(guò)期(刪除)時(shí)間. 時(shí)間值是系統(tǒng)版本號(hào).
  • 每開(kāi)始一個(gè)事務(wù), 系統(tǒng)版本號(hào)遞增.
  • 事務(wù)開(kāi)始時(shí)刻的系統(tǒng)版本號(hào)作為事務(wù)的版本號(hào), 用來(lái)和查詢到的每行記錄的版本號(hào)進(jìn)行比較.
• 使多數(shù)讀操作不需要加鎖. 但需要額外的空間和檢查工作.
• 對(duì)隔離級(jí)別的支持.
  • 只支持Repeatable Read和Read Committed隔離級(jí)別.
  • Read unCommitted 總讀取最新的數(shù)據(jù), 而不是當(dāng)前事務(wù)版本的數(shù)據(jù)行.
  • Serialisable 會(huì)對(duì)所有讀取的行都加鎖.

4 MySql 存儲(chǔ)引擎

4.1 存儲(chǔ)

• MySql 使用文件系統(tǒng)的目錄和文件保存數(shù)據(jù)庫(kù)(Schema)和表的定義.
• 庫(kù)是數(shù)據(jù)目錄的子目錄.
• 表會(huì)有同名.frm文件保存表的定義.
• 所以, 大小寫敏感性和具體的平臺(tái)密切相關(guān).

4.2 InnoDB

• 數(shù)據(jù)存儲(chǔ)在表空間(tablespace)中, 表空間是由InnoDB管理的黑盒子, 由一系列的文件組成.
• 采用MVCC 來(lái)支持高并發(fā). 并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別.
• 表基于聚族索引建立的. 對(duì)鑄件查詢有很高的性能, 但是二級(jí)索引中必須包含主鍵列. 所以主鍵列應(yīng)盡可能的小.
• 適合場(chǎng)景: 處理大量的短期事務(wù)(多數(shù)情況下正常提交,很少被回滾), 支持自動(dòng)崩潰恢復(fù).

4.3 MyISAM

• 提供了全文檢索,壓縮,空間函數(shù)等特性.
• 不支持事務(wù)和行級(jí)鎖. 且無(wú)法做到崩潰后恢復(fù).
  • 加鎖與并發(fā). 讀取時(shí)對(duì)表加共享鎖, 寫入時(shí)對(duì)表加排它鎖.
• 適合場(chǎng)景: 對(duì)于只讀數(shù)據(jù), 或者表比較小, 能夠忍受修復(fù)操作.
• 引擎選擇的因素: 事務(wù), 備份, 崩潰恢復(fù), 特有的特性.