高性能MySQL（第三版）

高性能MySQL（第3版）目錄\h第1章MySQL架構(gòu)與歷史\h1.1MySQL邏輯架構(gòu)\h1.1.1連接管理與安全性\h1.1.2優(yōu)化與執(zhí)行\(zhòng)h1.2并發(fā)控制\h1.2.1讀寫鎖\h1.2.2鎖粒度\h1.3事務(wù)\h1.3.1隔離級(jí)別\h1.3.2死鎖\h1.3.3事務(wù)日志\h1.3.4MySQL中的事務(wù)\h1.4多版本并發(fā)控制\h1.5MySQL的存儲(chǔ)引擎\h1.5.1InnoDB存儲(chǔ)引擎\h1.5.2MyISAM存儲(chǔ)引擎\h1.5.3MySQL內(nèi)建的其他存儲(chǔ)引擎\h1.5.4第三方存儲(chǔ)引擎\h1.5.5選擇合適的引擎\h1.5.6轉(zhuǎn)換表的引擎\h1.6MySQL時(shí)間線（Timeline）\h1.7MySQL的開發(fā)模式\h1.8總結(jié)\h第2章MySQL基準(zhǔn)測(cè)試\h2.1為什么需要基準(zhǔn)測(cè)試\h2.2基準(zhǔn)測(cè)試的策略\h2.2.1測(cè)試何種指標(biāo)\h2.3基準(zhǔn)測(cè)試方法\h2.3.1設(shè)計(jì)和規(guī)劃基準(zhǔn)測(cè)試\h2.3.2基準(zhǔn)測(cè)試應(yīng)該運(yùn)行多長(zhǎng)時(shí)間\h2.3.3獲取系統(tǒng)性能和狀態(tài)\h2.3.4獲得準(zhǔn)確的測(cè)試結(jié)果\h2.3.5運(yùn)行基準(zhǔn)測(cè)試并分析結(jié)果\h2.3.6繪圖的重要性\h2.4基準(zhǔn)測(cè)試工具\(yùn)h2.4.1集成式測(cè)試工具\(yùn)h2.4.2單組件式測(cè)試工具\(yùn)h2.5基準(zhǔn)測(cè)試案例\h2.5.1http_load\h2.5.2MySQL基準(zhǔn)測(cè)試套件\h2.5.3sysbench\h2.5.4數(shù)據(jù)庫測(cè)試套件中的dbt2TPC-C測(cè)試\h2.5.5Percona的TPCC-MySQL測(cè)試工具\(yùn)h2.6總結(jié)\h第3章服務(wù)器性能剖析\h3.1性能優(yōu)化簡(jiǎn)介\h3.1.1通過性能剖析進(jìn)行優(yōu)化\h3.1.2理解性能剖析\h3.2對(duì)應(yīng)用程序進(jìn)行性能剖析\h3.2.1測(cè)量PHP應(yīng)用程序\h3.3剖析MySQL查詢\h3.3.1剖析服務(wù)器負(fù)載\h3.3.2剖析單條查詢\h3.3.3使用性能剖析\h3.4診斷間歇性問題\h3.4.1單條查詢問題還是服務(wù)器問題\h3.4.2捕獲診斷數(shù)據(jù)\h3.4.3一個(gè)診斷案例\h3.5其他剖析工具\(yùn)h3.5.1使用USER_STATISTICS表\h3.5.2使用strace\h3.6總結(jié)\h第4章Schema與數(shù)據(jù)類型優(yōu)化\h4.1選擇優(yōu)化的數(shù)據(jù)類型\h4.1.1整數(shù)類型\h4.1.2實(shí)數(shù)類型\h4.1.3字符串類型\h4.1.4日期和時(shí)間類型\h4.1.5位數(shù)據(jù)類型\h4.1.6選擇標(biāo)識(shí)符（identifier）\h4.1.7特殊類型數(shù)據(jù)\h4.2MySQLschema設(shè)計(jì)中的陷阱\h4.3范式和反范式\h4.3.1范式的優(yōu)點(diǎn)和缺點(diǎn)\h4.3.2反范式的優(yōu)點(diǎn)和缺點(diǎn)\h4.3.3混用范式化和反范式化\h4.4緩存表和匯總表\h4.4.1物化視圖\h4.4.2計(jì)數(shù)器表\h4.5加快ALTERTABLE操作的速度\h4.5.1只修改.frm文件\h4.5.2快速創(chuàng)建MyISAM索引\h4.6總結(jié)\h第5章創(chuàng)建高性能的索引\h5.1索引基礎(chǔ)\h5.1.1索引的類型\h5.2索引的優(yōu)點(diǎn)\h5.3高性能的索引策略\h5.3.1獨(dú)立的列\(zhòng)h5.3.2前綴索引和索引選擇性\h5.3.3多列索引\h5.3.4選擇合適的索引列順序\h5.3.5聚簇索引\h5.3.6覆蓋索引\h5.3.7使用索引掃描來做排序\h5.3.8壓縮（前綴壓縮）索引\h5.3.9冗余和重復(fù)索引\h5.3.10未使用的索引\h5.3.11索引和鎖\h5.4索引案例學(xué)習(xí)\h5.4.1支持多種過濾條件\h5.4.2避免多個(gè)范圍條件\h5.4.3優(yōu)化排序\h5.5維護(hù)索引和表\h5.5.1找到并修復(fù)損壞的表\h5.5.2更新索引統(tǒng)計(jì)信息\h5.5.3減少索引和數(shù)據(jù)的碎片\h5.6總結(jié)\h第6章查詢性能優(yōu)化\h6.1為什么查詢速度會(huì)慢\h6.2慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問\h6.2.1是否向數(shù)據(jù)庫請(qǐng)求了不需要的數(shù)據(jù)\h6.2.2MySQL是否在掃描額外的記錄\h6.3重構(gòu)查詢的方式\h6.3.1一個(gè)復(fù)雜查詢還是多個(gè)簡(jiǎn)單查詢\h6.3.2切分查詢\h6.3.3分解關(guān)聯(lián)查詢\h6.4查詢執(zhí)行的基礎(chǔ)\h6.4.1MySQL客戶端/服務(wù)器通信協(xié)議\h6.4.2查詢緩存\h6.4.3查詢優(yōu)化處理\h6.4.4查詢執(zhí)行引擎\h6.4.5返回結(jié)果給客戶端\h6.5MySQL查詢優(yōu)化器的局限性\h6.5.1關(guān)聯(lián)子查詢\h6.5.2UNION的限制\h6.5.3索引合并優(yōu)化\h6.5.4等值傳遞\h6.5.5并行執(zhí)行\(zhòng)h6.5.6哈希關(guān)聯(lián)\h6.5.7松散索引掃描\h6.5.8最大值和最小值優(yōu)化\h6.5.9在同一個(gè)表上查詢和更新\h6.6查詢優(yōu)化器的提示（hint）\h6.7優(yōu)化特定類型的查詢\h6.7.1優(yōu)化COUNT()查詢\h6.7.2優(yōu)化關(guān)聯(lián)查詢\h6.7.3優(yōu)化子查詢\h6.7.4優(yōu)化GROUPBY和DISTINCT\h6.7.5優(yōu)化LIMIT分頁\h6.7.6優(yōu)化SQL_CALC_FOUND_ROWS\h6.7.7優(yōu)化UNION查詢\h6.7.8靜態(tài)查詢分析\h6.7.9使用用戶自定義變量\h6.8案例學(xué)習(xí)\h6.8.1使用MySQL構(gòu)建一個(gè)隊(duì)列表\h6.8.2計(jì)算兩點(diǎn)之間的距離\h6.8.3使用用戶自定義函數(shù)\h6.9總結(jié)\h第7章MySQL高級(jí)特性\h7.1分區(qū)表\h7.1.1分區(qū)表的原理\h7.1.2分區(qū)表的類型\h7.1.3如何使用分區(qū)表\h7.1.4什么情況下會(huì)出問題\h7.1.5查詢優(yōu)化\h7.1.6合并表\h7.2視圖\h7.2.1可更新視圖\h7.2.2視圖對(duì)性能的影響\h7.2.3視圖的限制\h7.3外鍵約束\h7.4在MySQL內(nèi)部存儲(chǔ)代碼\h7.4.1存儲(chǔ)過程和函數(shù)\h7.4.2觸發(fā)器\h7.4.3事件\h7.4.4在存儲(chǔ)程序中保留注釋\h7.5游標(biāo)\h7.6綁定變量\h7.6.1綁定變量的優(yōu)化\h7.6.2SQL接口的綁定變量\h7.6.3綁定變量的限制\h7.7用戶自定義函數(shù)\h7.8插件\h7.9字符集和校對(duì)\h7.9.1MySQL如何使用字符集\h7.9.2選擇字符集和校對(duì)規(guī)則\h7.9.3字符集和校對(duì)規(guī)則如何影響查詢\h7.10全文索引\h7.10.1自然語言的全文索引\h7.10.2布爾全文索引\h7.10.3MySQL5.1中全文索引的變化\h7.10.4全文索引的限制和替代方案\h7.10.5全文索引的配置和優(yōu)化\h7.11分布式（XA）事務(wù)\h7.11.1內(nèi)部XA事務(wù)\h7.11.2外部XA事務(wù)\h7.12查詢緩存\h7.12.1MySQL如何判斷緩存命中\(zhòng)h7.12.2查詢緩存如何使用內(nèi)存\h7.12.3什么情況下查詢緩存能發(fā)揮作用\h7.12.4如何配置和維護(hù)查詢緩存\h7.12.5InnoDB和查詢緩存\h7.12.6通用查詢緩存優(yōu)化\h7.12.7查詢緩存的替代方案\h7.13總結(jié)\h第8章優(yōu)化服務(wù)器設(shè)置\h8.1MySQL配置的工作原理\h8.1.1語法、作用域和動(dòng)態(tài)性\h8.1.2設(shè)置變量的副作用\h8.1.3入門\h8.1.4通過基準(zhǔn)測(cè)試迭代優(yōu)化\h8.2什么不該做\h8.3創(chuàng)建MySQL配置文件\h8.3.1檢查MySQL服務(wù)器狀態(tài)變量\h8.4配置內(nèi)存使用\h8.4.1MySQL可以使用多少內(nèi)存\h8.4.2每個(gè)連接需要的內(nèi)存\h8.4.3為操作系統(tǒng)保留內(nèi)存\h8.4.4為緩存分配內(nèi)存\h8.4.5InnoDB緩沖池（BufferPool）\h8.4.6MyISAM鍵緩存（KeyCaches）\h8.4.7線程緩存\h8.4.8表緩存（TableCache）\h8.4.9InnoDB數(shù)據(jù)字典（DataDictionary）\h8.5配置MySQL的I/O行為\h8.5.1InnoDBI/O配置\h8.5.2MyISAM的I/O配置\h8.6配置MySQL并發(fā)\h8.6.1InnoDB并發(fā)配置\h8.6.2MyISAM并發(fā)配置\h8.7基于工作負(fù)載的配置\h8.7.1優(yōu)化BLOB和TEXT的場(chǎng)景\h8.7.2優(yōu)化排序（Filesorts）\h8.8完成基本配置\h8.9安全和穩(wěn)定的設(shè)置\h8.10高級(jí)InnoDB設(shè)置\h8.11總結(jié)\h第9章操作系統(tǒng)和硬件優(yōu)化\h9.1什么限制了MySQL的性能\h9.2如何為MySQL選擇CPU\h9.2.1哪個(gè)更好：更快的CPU還是更多的CPU\h9.2.2CPU架構(gòu)\h9.2.3擴(kuò)展到多個(gè)CPU和核心\h9.3平衡內(nèi)存和磁盤資源\h9.3.1隨機(jī)I/O和順序I/O\h9.3.2緩存，讀和寫\h9.3.3工作集是什么\h9.3.4找到有效的內(nèi)存/磁盤比例\h9.3.5選擇硬盤\h9.4固態(tài)存儲(chǔ)\h9.4.1閃存概述\h9.4.2閃存技術(shù)\h9.4.3閃存的基準(zhǔn)測(cè)試\h9.4.4固態(tài)硬盤驅(qū)動(dòng)器（SSD）\h9.4.5PCIe存儲(chǔ)設(shè)備\h9.4.6其他類型的固態(tài)存儲(chǔ)\h9.4.7什么時(shí)候應(yīng)該使用閃存\h9.4.8使用Flashcache\h9.4.9優(yōu)化固態(tài)存儲(chǔ)上的MySQL\h9.5為備庫選擇硬件\h9.6RAID性能優(yōu)化\h9.6.1RAID的故障轉(zhuǎn)移、恢復(fù)和鏡像\h9.6.2平衡硬件RAID和軟件RAID\h9.6.3RAID配置和緩存\h9.7SAN和NAS\h9.7.1SAN基準(zhǔn)測(cè)試\h9.7.2使用基于NFS或SMB的SAN\h9.7.3MySQL在SAN上的性能\h9.7.4應(yīng)該用SAN嗎\h9.8使用多磁盤卷\h9.9網(wǎng)絡(luò)配置\h9.10選擇操作系統(tǒng)\h9.11選擇文件系統(tǒng)\h9.12選擇磁盤隊(duì)列調(diào)度策略\h9.13線程\h9.14內(nèi)存交換區(qū)\h9.15操作系統(tǒng)狀態(tài)\h9.15.1如何閱讀vmstat的輸出\h9.15.2如何閱讀iostat的輸出\h9.15.3其他有用的工具\(yùn)h9.15.4CPU密集型的機(jī)器\h9.15.5I/O密集型的機(jī)器\h9.15.6發(fā)生內(nèi)存交換的機(jī)器\h9.15.7空閑的機(jī)器\h9.16總結(jié)\h第10章復(fù)制\h10.1復(fù)制概述\h10.1.1復(fù)制解決的問題\h10.1.2復(fù)制如何工作\h10.2配置復(fù)制\h10.2.1創(chuàng)建復(fù)制賬號(hào)\h10.2.2配置主庫和備庫\h10.2.3啟動(dòng)復(fù)制\h10.2.4從另一個(gè)服務(wù)器開始復(fù)制\h10.2.5推薦的復(fù)制配置\h10.3復(fù)制的原理\h10.3.1基于語句的復(fù)制\h10.3.2基于行的復(fù)制\h10.3.3基于行或基于語句：哪種更優(yōu)\h10.3.4復(fù)制文件\h10.3.5發(fā)送復(fù)制事件到其他備庫\h10.3.6復(fù)制過濾器\h10.4復(fù)制拓?fù)鋅h10.4.1一主庫多備庫\h10.4.2主動(dòng)-主動(dòng)模式下的主-主復(fù)制\h10.4.3主動(dòng)-被動(dòng)模式下的主-主復(fù)制\h10.4.4擁有備庫的主-主結(jié)構(gòu)\h10.4.5環(huán)形復(fù)制\h10.4.6主庫、分發(fā)主庫以及備庫\h10.4.7樹或金字塔形\h10.4.8定制的復(fù)制方案\h10.5復(fù)制和容量規(guī)劃\h10.5.1為什么復(fù)制無法擴(kuò)展寫操作\h10.5.2備庫什么時(shí)候開始延遲\h10.5.3規(guī)劃冗余容量\h10.6復(fù)制管理和維護(hù)\h10.6.1監(jiān)控復(fù)制\h10.6.2測(cè)量備庫延遲\h10.6.3確定主備是否一致\h10.6.4從主庫重新同步備庫\h10.6.5改變主庫\h10.6.6在一個(gè)主-主配置中交換角色\h10.7復(fù)制的問題和解決方案\h10.7.1數(shù)據(jù)損壞或丟失的錯(cuò)誤\h10.7.2使用非事務(wù)型表\h10.7.3混合事務(wù)型和非事務(wù)型表\h10.7.4不確定語句\h10.7.5主庫和備庫使用不同的存儲(chǔ)引擎\h10.7.6備庫發(fā)生數(shù)據(jù)改變\h10.7.7不唯一的服務(wù)器ID\h10.7.8未定義的服務(wù)器ID\h10.7.9對(duì)未復(fù)制數(shù)據(jù)的依賴性\h10.7.10丟失的臨時(shí)表\h10.7.11不復(fù)制所有的更新\h10.7.12InnoDB加鎖讀引起的鎖爭(zhēng)用\h10.7.13在主-主復(fù)制結(jié)構(gòu)中寫入兩臺(tái)主庫\h10.7.14過大的復(fù)制延遲\h10.7.15來自主庫的過大的包\h10.7.16受限制的復(fù)制帶寬\h10.7.17磁盤空間不足\h10.7.18復(fù)制的局限性\h10.8復(fù)制有多快\h10.9MySQL復(fù)制的高級(jí)特性\h10.10其他復(fù)制技術(shù)\h10.11總結(jié)\h第11章可擴(kuò)展的MySQL\h11.1什么是可擴(kuò)展性\h11.1.1正式的可擴(kuò)展性定義\h11.2擴(kuò)展MySQL\h11.2.1規(guī)劃可擴(kuò)展性\h11.2.2為擴(kuò)展贏得時(shí)間\h11.2.3向上擴(kuò)展\h11.2.4向外擴(kuò)展\h11.2.5通過多實(shí)例擴(kuò)展\h11.2.6通過集群擴(kuò)展\h11.2.7向內(nèi)擴(kuò)展\h11.3負(fù)載均衡\h11.3.1直接連接\h11.3.2引入中間件\h11.3.3一主多備間的負(fù)載均衡\h11.4總結(jié)\h第12章高可用性\h12.1什么是高可用性\h12.2導(dǎo)致宕機(jī)的原因\h12.3如何實(shí)現(xiàn)高可用性\h12.3.1提升平均失效時(shí)間（MTBF）\h12.3.2降低平均恢復(fù)時(shí)間（MTTR）\h12.4避免單點(diǎn)失效\h12.4.1共享存儲(chǔ)或磁盤復(fù)制\h12.4.2MySQL同步復(fù)制\h12.4.3基于復(fù)制的冗余\h12.5故障轉(zhuǎn)移和故障恢復(fù)\h12.5.1提升備庫或切換角色\h12.5.2虛擬IP地址或IP接管\h12.5.3中間件解決方案\h12.5.4在應(yīng)用中處理故障轉(zhuǎn)移\h12.6總結(jié)\h第13章云端的MySQL\h13.1云的優(yōu)點(diǎn)、缺點(diǎn)和相關(guān)誤解\h13.2MySQL在云端的經(jīng)濟(jì)價(jià)值\h13.3云中的MySQL的可擴(kuò)展性和高可用性\h13.4四種基礎(chǔ)資源\h13.5MySQL在云主機(jī)上的性能\h13.5.1在云端的MySQL基準(zhǔn)測(cè)試\h13.6MySQL數(shù)據(jù)庫即服務(wù)（DBaaS）\h13.6.1AmazonRDS\h13.6.2其他DBaaS解決方案\h13.7總結(jié)\h第14章應(yīng)用層優(yōu)化\h14.1常見問題\h14.2Web服務(wù)器問題\h14.2.1尋找最優(yōu)并發(fā)度\h14.3緩存\h14.3.1應(yīng)用層以下的緩存\h14.3.2應(yīng)用層緩存\h14.3.3緩存控制策略\h14.3.4緩存對(duì)象分層\h14.3.5預(yù)生成內(nèi)容\h14.3.6作為基礎(chǔ)組件的緩存\h14.3.7使用HandlerSocket和memcached\h14.4拓展MySQL\h14.5MySQL的替代品\h14.6總結(jié)\h第15章備份與恢復(fù)\h15.1為什么要備份\h15.2定義恢復(fù)需求\h15.3設(shè)計(jì)MySQL備份方案\h15.3.1在線備份還是離線備份\h15.3.2邏輯備份還是物理備份\h15.3.3備份什么\h15.3.4存儲(chǔ)引擎和一致性\h15.4管理和備份二進(jìn)制日志\h15.4.1二進(jìn)制日志格式\h15.4.2安全地清除老的二進(jìn)制日志\h15.5備份數(shù)據(jù)\h15.5.1生成邏輯備份\h15.5.2文件系統(tǒng)快照\h15.6從備份中恢復(fù)\h15.6.1恢復(fù)物理備份\h15.6.2還原邏輯備份\h15.6.3基于時(shí)間點(diǎn)的恢復(fù)\h15.6.4更高級(jí)的恢復(fù)技術(shù)\h15.6.5InnoDB崩潰恢復(fù)\h15.7備份和恢復(fù)工具\(yùn)h15.7.1MySQLEnterpriseBackup\h15.7.2PerconaXtraBackup\h15.7.3mylvmbackup\h15.7.4ZmandaRecoveryManager\h15.7.5mydumper\h15.7.6mysqldump\h15.8備份腳本化\h15.9總結(jié)\h第16章MySQL用戶工具\(yùn)h16.1接口工具\(yùn)h16.2命令行工具集\h16.3SQL實(shí)用集\h16.4監(jiān)測(cè)工具\(yùn)h16.4.1開源的監(jiān)控工具\(yùn)h16.4.2商業(yè)監(jiān)控系統(tǒng)\h16.4.3Innotop的命令行監(jiān)控\h16.5總結(jié)\h附錄AMySQL分支與變種\h附錄BMySQL服務(wù)器狀態(tài)\h附錄C大文件傳輸\h附錄DEXPLAIN\h附錄E鎖的調(diào)試\h附錄F在MySQL上使用Sphinx\h索引注：原文檔電子版（非掃描），需要的請(qǐng)下載本文檔后留言謝謝。第1章MySQL架構(gòu)與歷史和其他數(shù)據(jù)庫系統(tǒng)相比，MySQL有點(diǎn)與眾不同，它的架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮好的作用，但同時(shí)也會(huì)帶來一點(diǎn)選擇上的困難。MySQL并不完美，卻足夠靈活，能夠適應(yīng)高要求的環(huán)境，例如Web類應(yīng)用。同時(shí)，MySQL既可以嵌入到應(yīng)用程序中，也可以支持?jǐn)?shù)據(jù)倉庫、內(nèi)容索引和部署軟件、高可用的冗余系統(tǒng)、在線事務(wù)處理系統(tǒng)（OLTP）等各種應(yīng)用類型。為了充分發(fā)揮MySQL的性能并順利地使用，就必須理解其設(shè)計(jì)。MySQL的靈活性體現(xiàn)在很多方面。例如，你可以通過配置使它在不同的硬件上都運(yùn)行得很好，也可以支持多種不同的數(shù)據(jù)類型。但是，MySQL最重要、最與眾不同的特性是它的存儲(chǔ)引擎架構(gòu)，這種架構(gòu)的設(shè)計(jì)將查詢處理（QueryProcessing）及其他系統(tǒng)任務(wù)（ServerTask）和數(shù)據(jù)的存儲(chǔ)/提取相分離。這種處理和存儲(chǔ)分離的設(shè)計(jì)可以在使用時(shí)根據(jù)性能、特性，以及其他需求來選擇數(shù)據(jù)存儲(chǔ)的方式。本章概要地描述了MySQL的服務(wù)器架構(gòu)、各種存儲(chǔ)引擎之間的主要區(qū)別，以及這些區(qū)別的重要性。另外也會(huì)回顧一下MySQL的歷史背景和基準(zhǔn)測(cè)試，并試圖通過簡(jiǎn)化細(xì)節(jié)和演示案例來討論MySQL的原理。這些討論無論是對(duì)數(shù)據(jù)庫一無所知的新手，還是熟知其他數(shù)據(jù)庫的專家，都不無裨益。1.1MySQL邏輯架構(gòu)如果能在頭腦中構(gòu)建出一幅MySQL各組件之間如何協(xié)同工作的架構(gòu)圖，就會(huì)有助于深入理解MySQL服務(wù)器。圖1-1展示了MySQL的邏輯架構(gòu)圖。圖1-1：MySQL服務(wù)器邏輯架構(gòu)圖最上層的服務(wù)并不是MySQL所獨(dú)有的，大多數(shù)基于網(wǎng)絡(luò)的客戶端/服務(wù)器的工具或者服務(wù)都有類似的架構(gòu)。比如連接處理、授權(quán)認(rèn)證、安全等等。第二層架構(gòu)是MySQL比較有意思的部分。大多數(shù)MySQL的核心服務(wù)功能都在這一層，包括查詢解析、分析、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如，日期、時(shí)間、數(shù)學(xué)和加密函數(shù)），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)：存儲(chǔ)過程、觸發(fā)器、視圖等。第三層包含了存儲(chǔ)引擎。存儲(chǔ)引擎負(fù)責(zé)MySQL中數(shù)據(jù)的存儲(chǔ)和提取。和GNU/Linux下的各種文件系統(tǒng)一樣，每個(gè)存儲(chǔ)引擎都有它的優(yōu)勢(shì)和劣勢(shì)。服務(wù)器通過API與存儲(chǔ)引擎進(jìn)行通信。這些接口屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對(duì)上層的查詢過程透明。存儲(chǔ)引擎API包含幾十個(gè)底層函數(shù)，用于執(zhí)行諸如“開始一個(gè)事務(wù)”或者“根據(jù)主鍵提取一行記錄”等操作。但存儲(chǔ)引擎不會(huì)去解析SQL\h\h(1)\h，不同存儲(chǔ)引擎之間也不會(huì)相互通信，而只是簡(jiǎn)單地響應(yīng)上層服務(wù)器的請(qǐng)求。1.1.1連接管理與安全性每個(gè)客戶端連接都會(huì)在服務(wù)器進(jìn)程中擁有一個(gè)線程，這個(gè)連接的查詢只會(huì)在這個(gè)單獨(dú)的線程中執(zhí)行，該線程只能輪流在某個(gè)CPU核心或者CPU中運(yùn)行。服務(wù)器會(huì)負(fù)責(zé)緩存線程，因此不需要為每一個(gè)新建的連接創(chuàng)建或者銷毀線程\h\h(2)\h。當(dāng)客戶端（應(yīng)用）連接到MySQL服務(wù)器時(shí)，服務(wù)器需要對(duì)其進(jìn)行認(rèn)證。認(rèn)證基于用戶名、原始主機(jī)信息和密碼。如果使用了安全套接字（SSL）的方式連接，還可以使用X.509證書認(rèn)證。一旦客戶端連接成功，服務(wù)器會(huì)繼續(xù)驗(yàn)證該客戶端是否具有執(zhí)行某個(gè)特定查詢的權(quán)限（例如，是否允許客戶端對(duì)world數(shù)據(jù)庫的Country表執(zhí)行SELECT語句）。1.1.2優(yōu)化與執(zhí)行MySQL會(huì)解析查詢，并創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)（解析樹），然后對(duì)其進(jìn)行各種優(yōu)化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等。用戶可以通過特殊的關(guān)鍵字提示（hint）優(yōu)化器，影響它的決策過程。也可以請(qǐng)求優(yōu)化器解釋（explain）優(yōu)化過程的各個(gè)因素，使用戶可以知道服務(wù)器是如何進(jìn)行優(yōu)化決策的，并提供一個(gè)參考基準(zhǔn)，便于用戶重構(gòu)查詢和schema、修改相關(guān)配置，使應(yīng)用盡可能高效運(yùn)行。第6章我們將討論更多優(yōu)化器的細(xì)節(jié)。優(yōu)化器并不關(guān)心表使用的是什么存儲(chǔ)引擎，但存儲(chǔ)引擎對(duì)于優(yōu)化查詢是有影響的。優(yōu)化器會(huì)請(qǐng)求存儲(chǔ)引擎提供容量或某個(gè)具體操作的開銷信息，以及表數(shù)據(jù)的統(tǒng)計(jì)信息等。例如，某些存儲(chǔ)引擎的某種索引，可能對(duì)一些特定的查詢有優(yōu)化。關(guān)于索引與schema的優(yōu)化，請(qǐng)參見第4章和第5章。對(duì)于SELECT語句，在解析查詢之前，服務(wù)器會(huì)先檢查查詢緩存（QueryCache），如果能夠在其中找到對(duì)應(yīng)的查詢，服務(wù)器就不必再執(zhí)行查詢解析、優(yōu)化和執(zhí)行的整個(gè)過程，而是直接返回查詢緩存中的結(jié)果集。第7章詳細(xì)討論了相關(guān)內(nèi)容。1.2并發(fā)控制無論何時(shí)，只要有多個(gè)查詢需要在同一時(shí)刻修改數(shù)據(jù)，都會(huì)產(chǎn)生并發(fā)控制的問題。本章的目的是討論MySQL在兩個(gè)層面的并發(fā)控制：服務(wù)器層與存儲(chǔ)引擎層。并發(fā)控制是一個(gè)內(nèi)容龐大的話題，有大量的理論文獻(xiàn)對(duì)其進(jìn)行過詳細(xì)的論述。本章只簡(jiǎn)要地討論MySQL如何控制并發(fā)讀寫，因此讀者需要有相關(guān)的知識(shí)來理解本章接下來的內(nèi)容。以Unix系統(tǒng)的emailbox為例，典型的mbox文件格式是非常簡(jiǎn)單的。一個(gè)mbox郵箱中的所有郵件都串行在一起，彼此首尾相連。這種格式對(duì)于讀取和分析郵件信息非常友好，同時(shí)投遞郵件也很容易，只要在文件末尾附加新的郵件內(nèi)容即可。但如果兩個(gè)進(jìn)程在同一時(shí)刻對(duì)同一個(gè)郵箱投遞郵件，會(huì)發(fā)生什么情況？顯然，郵箱的數(shù)據(jù)會(huì)被破壞，兩封郵件的內(nèi)容會(huì)交叉地附加在郵箱文件的末尾。設(shè)計(jì)良好的郵箱投遞系統(tǒng)會(huì)通過鎖（lock）來防止數(shù)據(jù)損壞。如果客戶試圖投遞郵件，而郵箱已經(jīng)被其他客戶鎖住，那就必須等待，直到鎖釋放才能進(jìn)行投遞。這種鎖的方案在實(shí)際應(yīng)用環(huán)境中雖然工作良好，但并不支持并發(fā)處理。因?yàn)樵谌我庖粋€(gè)時(shí)刻，只有一個(gè)進(jìn)程可以修改郵箱的數(shù)據(jù)，這在大容量的郵箱系統(tǒng)中是個(gè)問題。1.2.1讀寫鎖從郵箱中讀取數(shù)據(jù)沒有這樣的麻煩，即使同一時(shí)刻多個(gè)用戶并發(fā)讀取也不會(huì)有什么問題。因?yàn)樽x取不會(huì)修改數(shù)據(jù)，所以不會(huì)出錯(cuò)。但如果某個(gè)客戶正在讀取郵箱，同時(shí)另外一個(gè)用戶試圖刪除編號(hào)為25的郵件，會(huì)產(chǎn)生什么結(jié)果？結(jié)論是不確定，讀的客戶可能會(huì)報(bào)錯(cuò)退出，也可能讀取到不一致的郵箱數(shù)據(jù)。所以，為安全起見，即使是讀取郵箱也需要特別注意。如果把上述的郵箱當(dāng)成數(shù)據(jù)庫中的一張表，把郵件當(dāng)成表中的一行記錄，就很容易看出，同樣的問題依然存在。從很多方面來說，郵箱就是一張簡(jiǎn)單的數(shù)據(jù)庫表。修改數(shù)據(jù)庫表中的記錄，和刪除或者修改郵箱中的郵件信息，十分類似。解決這類經(jīng)典問題的方法就是并發(fā)控制，其實(shí)非常簡(jiǎn)單。在處理并發(fā)讀或者寫時(shí)，可以通過實(shí)現(xiàn)一個(gè)由兩種類型的鎖組成的鎖系統(tǒng)來解決問題。這兩種類型的鎖通常被稱為共享鎖（sharedlock）和排他鎖（exclusivelock），也叫讀鎖（readlock）和寫鎖（writelock）。這里先不討論鎖的具體實(shí)現(xiàn)，描述一下鎖的概念如下：讀鎖是共享的，或者說是相互不阻塞的。多個(gè)客戶在同一時(shí)刻可以同時(shí)讀取同一個(gè)資源，而互不干擾。寫鎖則是排他的，也就是說一個(gè)寫鎖會(huì)阻塞其他的寫鎖和讀鎖，這是出于安全策略的考慮，只有這樣，才能確保在給定的時(shí)間里，只有一個(gè)用戶能執(zhí)行寫入，并防止其他用戶讀取正在寫入的同一資源。在實(shí)際的數(shù)據(jù)庫系統(tǒng)中，每時(shí)每刻都在發(fā)生鎖定，當(dāng)某個(gè)用戶在修改某一部分?jǐn)?shù)據(jù)時(shí)，MySQL會(huì)通過鎖定防止其他用戶讀取同一數(shù)據(jù)。大多數(shù)時(shí)候，MySQL鎖的內(nèi)部管理都是透明的。1.2.2鎖粒度一種提高共享資源并發(fā)性的方式就是讓鎖定對(duì)象更有選擇性。盡量只鎖定需要修改的部分?jǐn)?shù)據(jù)，而不是所有的資源。更理想的方式是，只對(duì)會(huì)修改的數(shù)據(jù)片進(jìn)行精確的鎖定。任何時(shí)候，在給定的資源上，鎖定的數(shù)據(jù)量越少，則系統(tǒng)的并發(fā)程度越高，只要相互之間不發(fā)生沖突即可。問題是加鎖也需要消耗資源。鎖的各種操作，包括獲得鎖、檢查鎖是否已經(jīng)解除、釋放鎖等，都會(huì)增加系統(tǒng)的開銷。如果系統(tǒng)花費(fèi)大量的時(shí)間來管理鎖，而不是存取數(shù)據(jù)，那么系統(tǒng)的性能可能會(huì)因此受到影響。所謂的鎖策略，就是在鎖的開銷和數(shù)據(jù)的安全性之間尋求平衡，這種平衡當(dāng)然也會(huì)影響到性能。大多數(shù)商業(yè)數(shù)據(jù)庫系統(tǒng)沒有提供更多的選擇，一般都是在表上施加行級(jí)鎖（row-levellock），并以各種復(fù)雜的方式來實(shí)現(xiàn)，以便在鎖比較多的情況下盡可能地提供更好的性能。而MySQL則提供了多種選擇。每種MySQL存儲(chǔ)引擎都可以實(shí)現(xiàn)自己的鎖策略和鎖粒度。在存儲(chǔ)引擎的設(shè)計(jì)中，鎖管理是個(gè)非常重要的決定。將鎖粒度固定在某個(gè)級(jí)別，可以為某些特定的應(yīng)用場(chǎng)景提供更好的性能，但同時(shí)卻會(huì)失去對(duì)另外一些應(yīng)用場(chǎng)景的良好支持。好在MySQL支持多個(gè)存儲(chǔ)引擎的架構(gòu)，所以不需要單一的通用解決方案。下面將介紹兩種最重要的鎖策略。表鎖（tablelock）表鎖是MySQL中最基本的鎖策略，并且是開銷最小的策略。表鎖非常類似于前文描述的郵箱加鎖機(jī)制：它會(huì)鎖定整張表。一個(gè)用戶在對(duì)表進(jìn)行寫操作（插入、刪除、更新等）前，需要先獲得寫鎖，這會(huì)阻塞其他用戶對(duì)該表的所有讀寫操作。只有沒有寫鎖時(shí)，其他讀取的用戶才能獲得讀鎖，讀鎖之間是不相互阻塞的。在特定的場(chǎng)景中，表鎖也可能有良好的性能。例如，READLOCAL表鎖支持某些類型的并發(fā)寫操作。另外，寫鎖也比讀鎖有更高的優(yōu)先級(jí)，因此一個(gè)寫鎖請(qǐng)求可能會(huì)被插入到讀鎖隊(duì)列的前面（寫鎖可以插入到鎖隊(duì)列中讀鎖的前面，反之讀鎖則不能插入到寫鎖的前面）。盡管存儲(chǔ)引擎可以管理自己的鎖，MySQL本身還是會(huì)使用各種有效的表鎖來實(shí)現(xiàn)不同的目的。例如，服務(wù)器會(huì)為諸如ALTERTABLE之類的語句使用表鎖，而忽略存儲(chǔ)引擎的鎖機(jī)制。行級(jí)鎖（rowlock）行級(jí)鎖可以最大程度地支持并發(fā)處理（同時(shí)也帶來了最大的鎖開銷）。眾所周知，在InnoDB和XtraDB，以及其他一些存儲(chǔ)引擎中實(shí)現(xiàn)了行級(jí)鎖。行級(jí)鎖只在存儲(chǔ)引擎層實(shí)現(xiàn)，而MySQL服務(wù)器層（如有必要，請(qǐng)回顧前文的邏輯架構(gòu)圖）沒有實(shí)現(xiàn)。服務(wù)器層完全不了解存儲(chǔ)引擎中的鎖實(shí)現(xiàn)。在本章的后續(xù)內(nèi)容以及全書中，所有的存儲(chǔ)引擎都以自己的方式顯現(xiàn)了鎖機(jī)制。1.3事務(wù)在理解事務(wù)的概念之前，接觸數(shù)據(jù)庫系統(tǒng)的其他高級(jí)特性還言之過早。事務(wù)就是一組原子性的SQL查詢，或者說一個(gè)獨(dú)立的工作單元。如果數(shù)據(jù)庫引擎能夠成功地對(duì)數(shù)據(jù)庫應(yīng)用該組查詢的全部語句，那么就執(zhí)行該組查詢。如果其中有任何一條語句因?yàn)楸罎⒒蚱渌驘o法執(zhí)行，那么所有的語句都不會(huì)執(zhí)行。也就是說，事務(wù)內(nèi)的語句，要么全部執(zhí)行成功，要么全部執(zhí)行失敗。本節(jié)的內(nèi)容并非專屬于MySQL，如果讀者已經(jīng)熟悉了事務(wù)的ACID的概念，可以直接跳轉(zhuǎn)到1.3.4節(jié)。銀行應(yīng)用是解釋事務(wù)必要性的一個(gè)經(jīng)典例子。假設(shè)一個(gè)銀行的數(shù)據(jù)庫有兩張表：支票（checking）表和儲(chǔ)蓄（savings）表。現(xiàn)在要從用戶Jane的支票賬戶轉(zhuǎn)移200美元到她的儲(chǔ)蓄賬戶，那么需要至少三個(gè)步驟：檢查支票賬戶的余額高于200美元。從支票賬戶余額中減去200美元。在儲(chǔ)蓄賬戶余額中增加200美元。上述三個(gè)步驟的操作必須打包在一個(gè)事務(wù)中，任何一個(gè)步驟失敗，則必須回滾所有的步驟?？梢杂肧TARTTRANSACTION語句開始一個(gè)事務(wù)，然后要么使用COMMIT提交事務(wù)將修改的數(shù)據(jù)持久保留，要么使用ROLLBACK撤銷所有的修改。事務(wù)SQL的樣本如下：1STARTTRANSACTION;2SELECTbalanceFROMcheckingWHEREcustomer_id=10233276;3UPDATEcheckingSETbalance=balance-200.00WHEREcustomer_id=10233276;4UPDATEsavingsSETbalance=balance+200.00WHEREcustomer_id=10233276;5COMMIT;單純的事務(wù)概念并不是故事的全部。試想一下，如果執(zhí)行到第四條語句時(shí)服務(wù)器崩潰了，會(huì)發(fā)生什么？天知道，用戶可能會(huì)損失200美元。再假如，在執(zhí)行到第三條語句和第四條語句之間時(shí)，另外一個(gè)進(jìn)程要?jiǎng)h除支票賬戶的所有余額，那么結(jié)果可能就是銀行在不知道這個(gè)邏輯的情況下白白給了Jane200美元。除非系統(tǒng)通過嚴(yán)格的ACID測(cè)試，否則空談事務(wù)的概念是不夠的。ACID表示原子性（atomicity）、一致性（consistency）、隔離性（isolation）和持久性（durability）。一個(gè)運(yùn)行良好的事務(wù)處理系統(tǒng)，必須具備這些標(biāo)準(zhǔn)特征。原子性（atomicity）一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元，整個(gè)事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾，對(duì)于一個(gè)事務(wù)來說，不可能只執(zhí)行其中的一部分操作，這就是事務(wù)的原子性。一致性（consistency）數(shù)據(jù)庫總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài)。在前面的例子中，一致性確保了，即使在執(zhí)行第三、四條語句之間時(shí)系統(tǒng)崩潰，支票賬戶中也不會(huì)損失200美元，因?yàn)槭聞?wù)最終沒有提交，所以事務(wù)中所做的修改也不會(huì)保存到數(shù)據(jù)庫中。隔離性（isolation）通常來說，一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其他事務(wù)是不可見的。在前面的例子中，當(dāng)執(zhí)行完第三條語句、第四條語句還未開始時(shí)，此時(shí)有另外一個(gè)賬戶匯總程序開始運(yùn)行，則其看到的支票賬戶的余額并沒有被減去200美元。后面我們討論隔離級(jí)別（Isolationlevel）的時(shí)候，會(huì)發(fā)現(xiàn)為什么我們要說“通常來說”是不可見的。持久性（durability）一旦事務(wù)提交，則其所做的修改就會(huì)永久保存到數(shù)據(jù)庫中。此時(shí)即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會(huì)丟失。持久性是個(gè)有點(diǎn)模糊的概念，因?yàn)閷?shí)際上持久性也分很多不同的級(jí)別。有些持久性策略能夠提供非常強(qiáng)的安全保障，而有些則未必。而且不可能有能做到100％的持久性保證的策略（如果數(shù)據(jù)庫本身就能做到真正的持久性，那么備份又怎么能增加持久性呢？）。在后面的一些章節(jié)中，我們會(huì)繼續(xù)討論MySQL中持久性的真正含義。事務(wù)的ACID特性可以確保銀行不會(huì)弄丟你的錢。而在應(yīng)用邏輯中，要實(shí)現(xiàn)這一點(diǎn)非常難，甚至可以說是不可能完成的任務(wù)。一個(gè)兼容ACID的數(shù)據(jù)庫系統(tǒng)，需要做很多復(fù)雜但可能用戶并沒有覺察到的工作，才能確保ACID的實(shí)現(xiàn)。就像鎖粒度的升級(jí)會(huì)增加系統(tǒng)開銷一樣，這種事務(wù)處理過程中額外的安全性，也會(huì)需要數(shù)據(jù)庫系統(tǒng)做更多的額外工作。一個(gè)實(shí)現(xiàn)了ACID的數(shù)據(jù)庫，相比沒有實(shí)現(xiàn)ACID的數(shù)據(jù)庫，通常會(huì)需要更強(qiáng)的CPU處理能力、更大的內(nèi)存和更多的磁盤空間。正如本章不斷重復(fù)的，這也正是MySQL的存儲(chǔ)引擎架構(gòu)可以發(fā)揮優(yōu)勢(shì)的地方。用戶可以根據(jù)業(yè)務(wù)是否需要事務(wù)處理，來選擇合適的存儲(chǔ)引擎。對(duì)于一些不需要事務(wù)的查詢類應(yīng)用，選擇一個(gè)非事務(wù)型的存儲(chǔ)引擎，可以獲得更高的性能。即使存儲(chǔ)引擎不支持事務(wù)，也可以通過LOCKTABLES語句為應(yīng)用提供一定程度的保護(hù)，這些選擇用戶都可以自主決定。1.3.1隔離級(jí)別隔離性其實(shí)比想象的要復(fù)雜。在SQL標(biāo)準(zhǔn)中定義了四種隔離級(jí)別，每一種級(jí)別都規(guī)定了一個(gè)事務(wù)中所做的修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見的，哪些是不可見的。較低級(jí)別的隔離通?？梢詧?zhí)行更高的并發(fā)，系統(tǒng)的開銷也更低。每種存儲(chǔ)引擎實(shí)現(xiàn)的隔離級(jí)別不盡相同。如果熟悉其他的數(shù)據(jù)庫產(chǎn)品，可能會(huì)發(fā)現(xiàn)某些特性和你期望的會(huì)有些不一樣（但本節(jié)不打算討論更詳細(xì)的內(nèi)容）。讀者可以根據(jù)所選擇的存儲(chǔ)引擎，查閱相關(guān)的手冊(cè)。下面簡(jiǎn)單地介紹一下四種隔離級(jí)別。READUNCOMMITTED（未提交讀）在READUNCOMMITTED級(jí)別，事務(wù)中的修改，即使沒有提交，對(duì)其他事務(wù)也都是可見的。事務(wù)可以讀取未提交的數(shù)據(jù)，這也被稱為臟讀（DirtyRead）。這個(gè)級(jí)別會(huì)導(dǎo)致很多問題，從性能上來說，READUNCOMMITTED不會(huì)比其他的級(jí)別好太多，但卻缺乏其他級(jí)別的很多好處，除非真的有非常必要的理由，在實(shí)際應(yīng)用中一般很少使用。READCOMMITTED（提交讀）大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認(rèn)隔離級(jí)別都是READCOMMITTED（但MySQL不是）。READCOMMITTED滿足前面提到的隔離性的簡(jiǎn)單定義：一個(gè)事務(wù)開始時(shí)，只能“看見”已經(jīng)提交的事務(wù)所做的修改。換句話說，一個(gè)事務(wù)從開始直到提交之前，所做的任何修改對(duì)其他事務(wù)都是不可見的。這個(gè)級(jí)別有時(shí)候也叫做不可重復(fù)讀（nonrepeatableread），因?yàn)閮纱螆?zhí)行同樣的查詢，可能會(huì)得到不一樣的結(jié)果。REPEATABLEREAD（可重復(fù)讀）REPEATABLEREAD解決了臟讀的問題。該級(jí)別保證了在同一個(gè)事務(wù)中多次讀取同樣記錄的結(jié)果是一致的。但是理論上，可重復(fù)讀隔離級(jí)別還是無法解決另外一個(gè)幻讀（PhantomRead）的問題。所謂幻讀，指的是當(dāng)某個(gè)事務(wù)在讀取某個(gè)范圍內(nèi)的記錄時(shí)，另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時(shí)，會(huì)產(chǎn)生幻行（PhantomRow）。InnoDB和XtraDB存儲(chǔ)引擎通過多版本并發(fā)控制（MVCC，MultiversionConcurrencyControl）解決了幻讀的問題。本章稍后會(huì)做進(jìn)一步的討論。可重復(fù)讀是MySQL的默認(rèn)事務(wù)隔離級(jí)別。SERIALIZABLE（可串行化）SERIALIZABLE是最高的隔離級(jí)別。它通過強(qiáng)制事務(wù)串行執(zhí)行，避免了前面說的幻讀的問題。簡(jiǎn)單來說，SERIALIZABLE會(huì)在讀取的每一行數(shù)據(jù)上都加鎖，所以可能導(dǎo)致大量的超時(shí)和鎖爭(zhēng)用的問題。實(shí)際應(yīng)用中也很少用到這個(gè)隔離級(jí)別，只有在非常需要確保數(shù)據(jù)的一致性而且可以接受沒有并發(fā)的情況下，才考慮采用該級(jí)別。表1-1：ANSISQL隔離級(jí)別1.3.2死鎖死鎖是指兩個(gè)或者多個(gè)事務(wù)在同一資源上相互占用，并請(qǐng)求鎖定對(duì)方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。當(dāng)多個(gè)事務(wù)試圖以不同的順序鎖定資源時(shí)，就可能會(huì)產(chǎn)生死鎖。多個(gè)事務(wù)同時(shí)鎖定同一個(gè)資源時(shí)，也會(huì)產(chǎn)生死鎖。例如，設(shè)想下面兩個(gè)事務(wù)同時(shí)處理StockPrice表：事務(wù)1STARTTRANSACTION;UPDATEStockPriceSETclose=45.50WHEREstock_id=4anddate='2002-05-01';UPDATEStockPriceSETclose=19.80WHEREstock_id=3anddate='2002-05-02';COMMIT;事務(wù)2STARTTRANSACTION;UPDATEStockPriceSEThigh=20.12WHEREstock_id=3anddate='2002-05-02';UPDATEStockPriceSEThigh=47.20WHEREstock_id=4anddate='2002-05-01';COMMIT;如果湊巧，兩個(gè)事務(wù)都執(zhí)行了第一條UPDATE語句，更新了一行數(shù)據(jù)，同時(shí)也鎖定了該行數(shù)據(jù)，接著每個(gè)事務(wù)都嘗試去執(zhí)行第二條UPDATE語句，卻發(fā)現(xiàn)該行已經(jīng)被對(duì)方鎖定，然后兩個(gè)事務(wù)都等待對(duì)方釋放鎖，同時(shí)又持有對(duì)方需要的鎖，則陷入死循環(huán)。除非有外部因素介入才可能解除死鎖。為了解決這種問題，數(shù)據(jù)庫系統(tǒng)實(shí)現(xiàn)了各種死鎖檢測(cè)和死鎖超時(shí)機(jī)制。越復(fù)雜的系統(tǒng)，比如InnoDB存儲(chǔ)引擎，越能檢測(cè)到死鎖的循環(huán)依賴，并立即返回一個(gè)錯(cuò)誤。這種解決方式很有效，否則死鎖會(huì)導(dǎo)致出現(xiàn)非常慢的查詢。還有一種解決方式，就是當(dāng)查詢的時(shí)間達(dá)到鎖等待超時(shí)的設(shè)定后放棄鎖請(qǐng)求，這種方式通常來說不太好。InnoDB目前處理死鎖的方法是，將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾（這是相對(duì)比較簡(jiǎn)單的死鎖回滾算法）。鎖的行為和順序是和存儲(chǔ)引擎相關(guān)的。以同樣的順序執(zhí)行語句，有些存儲(chǔ)引擎會(huì)產(chǎn)生死鎖，有些則不會(huì)。死鎖的產(chǎn)生有雙重原因：有些是因?yàn)檎嬲臄?shù)據(jù)沖突，這種情況通常很難避免，但有些則完全是由于存儲(chǔ)引擎的實(shí)現(xiàn)方式導(dǎo)致的。死鎖發(fā)生以后，只有部分或者完全回滾其中一個(gè)事務(wù)，才能打破死鎖。對(duì)于事務(wù)型的系統(tǒng)，這是無法避免的，所以應(yīng)用程序在設(shè)計(jì)時(shí)必須考慮如何處理死鎖。大多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可。1.3.3事務(wù)日志事務(wù)日志可以幫助提高事務(wù)的效率。使用事務(wù)日志，存儲(chǔ)引擎在修改表的數(shù)據(jù)時(shí)只需要修改其內(nèi)存拷貝，再把該修改行為記錄到持久在硬盤上的事務(wù)日志中，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤。事務(wù)日志采用的是追加的方式，因此寫日志的操作是磁盤上一小塊區(qū)域內(nèi)的順序I/O，而不像隨機(jī)I/O需要在磁盤的多個(gè)地方移動(dòng)磁頭，所以采用事務(wù)日志的方式相對(duì)來說要快得多。事務(wù)日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺(tái)可以慢慢地刷回到磁盤。目前大多數(shù)存儲(chǔ)引擎都是這樣實(shí)現(xiàn)的，我們通常稱之為預(yù)寫式日志（Write-AheadLogging），修改數(shù)據(jù)需要寫兩次磁盤。如果數(shù)據(jù)的修改已經(jīng)記錄到事務(wù)日志并持久化，但數(shù)據(jù)本身還沒有寫回磁盤，此時(shí)系統(tǒng)崩潰，存儲(chǔ)引擎在重啟時(shí)能夠自動(dòng)恢復(fù)這部分修改的數(shù)據(jù)。具體的恢復(fù)方式則視存儲(chǔ)引擎而定。1.3.4MySQL中的事務(wù)MySQL提供了兩種事務(wù)型的存儲(chǔ)引擎：InnoDB和NDBCluster。另外還有一些第三方存儲(chǔ)引擎也支持事務(wù)，比較知名的包括XtraDB和PBXT。后面將詳細(xì)討論它們各自的一些特點(diǎn)。自動(dòng)提交（AUTOCOMMIT）MySQL默認(rèn)采用自動(dòng)提交（AUTOCOMMIT）模式。也就是說，如果不是顯式地開始一個(gè)事務(wù)，則每個(gè)查詢都被當(dāng)作一個(gè)事務(wù)執(zhí)行提交操作。在當(dāng)前連接中，可以通過設(shè)置AUTOCOMMIT變量來啟用或者禁用自動(dòng)提交模式：1或者ON表示啟用，0或者OFF表示禁用。當(dāng)AUTOCOMMIT=0時(shí)，所有的查詢都是在一個(gè)事務(wù)中，直到顯式地執(zhí)行COMMIT提交或者ROLLBACK回滾，該事務(wù)結(jié)束，同時(shí)又開始了另一個(gè)新事務(wù)。修改AUTOCOMMIT對(duì)非事務(wù)型的表，比如MyISAM或者內(nèi)存表，不會(huì)有任何影響。對(duì)這類表來說，沒有COMMIT或者ROLLBACK的概念，也可以說是相當(dāng)于一直處于AUTOCOMMIT啟用的模式。另外還有一些命令，在執(zhí)行之前會(huì)強(qiáng)制執(zhí)行COMMIT提交當(dāng)前的活動(dòng)事務(wù)。典型的例子，在數(shù)據(jù)定義語言（DDL）中，如果是會(huì)導(dǎo)致大量數(shù)據(jù)改變的操作，比如ALTERTABLE，就是如此。另外還有LOCKTABLES等其他語句也會(huì)導(dǎo)致同樣的結(jié)果。如果有需要，請(qǐng)檢查對(duì)應(yīng)版本的官方文檔來確認(rèn)所有可能導(dǎo)致自動(dòng)提交的語句列表。MySQL可以通過執(zhí)行SETTRANSACTIONISOLATIONLEVEL命令來設(shè)置隔離級(jí)別。新的隔離級(jí)別會(huì)在下一個(gè)事務(wù)開始的時(shí)候生效?？梢栽谂渲梦募性O(shè)置整個(gè)數(shù)據(jù)庫的隔離級(jí)別，也可以只改變當(dāng)前會(huì)話的隔離級(jí)別：mysql>SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;MySQL能夠識(shí)別所有的4個(gè)ANSI隔離級(jí)別，InnoDB引擎也支持所有的隔離級(jí)別。在事務(wù)中混合使用存儲(chǔ)引擎MySQL服務(wù)器層不管理事務(wù)，事務(wù)是由下層的存儲(chǔ)引擎實(shí)現(xiàn)的。所以在同一個(gè)事務(wù)中，使用多種存儲(chǔ)引擎是不可靠的。如果在事務(wù)中混合使用了事務(wù)型和非事務(wù)型的表（例如InnoDB和MyISAM表），在正常提交的情況下不會(huì)有什么問題。但如果該事務(wù)需要回滾，非事務(wù)型的表上的變更就無法撤銷，這會(huì)導(dǎo)致數(shù)據(jù)庫處于不一致的狀態(tài)，這種情況很難修復(fù)，事務(wù)的最終結(jié)果將無法確定。所以，為每張表選擇合適的存儲(chǔ)引擎非常重要。在非事務(wù)型的表上執(zhí)行事務(wù)相關(guān)操作的時(shí)候，MySQL通常不會(huì)發(fā)出提醒，也不會(huì)報(bào)錯(cuò)。有時(shí)候只有回滾的時(shí)候才會(huì)發(fā)出一個(gè)警告：“某些非事務(wù)型的表上的變更不能被回滾”。但大多數(shù)情況下，對(duì)非事務(wù)型表的操作都不會(huì)有提示。隱式和顯式鎖定InnoDB采用的是兩階段鎖定協(xié)議（two-phaselockingprotocol）。在事務(wù)執(zhí)行過程中，隨時(shí)都可以執(zhí)行鎖定，鎖只有在執(zhí)行COMMIT或者ROLLBACK的時(shí)候才會(huì)釋放，并且所有的鎖是在同一時(shí)刻被釋放。前面描述的鎖定都是隱式鎖定，InnoDB會(huì)根據(jù)隔離級(jí)別在需要的時(shí)候自動(dòng)加鎖。另外，InnoDB也支持通過特定的語句進(jìn)行顯式鎖定，這些語句不屬于SQL規(guī)范\h\h(3)\h：SELECT...LOCKINSHAREMODESELECT...FORUPDATEMySQL也支持LOCKTABLES和UNLOCKTABLES語句，這是在服務(wù)器層實(shí)現(xiàn)的，和存儲(chǔ)引擎無關(guān)。它們有自己的用途，但并不能替代事務(wù)處理。如果應(yīng)用需要用到事務(wù)，還是應(yīng)該選擇事務(wù)型存儲(chǔ)引擎。經(jīng)?？梢园l(fā)現(xiàn)，應(yīng)用已經(jīng)將表從MyISAM轉(zhuǎn)換到InnoDB，但還是顯式地使用LOCKTABLES語句。這不但沒有必要，還會(huì)嚴(yán)重影響性能，實(shí)際上InnoDB的行級(jí)鎖工作得更好。LOCKTABLES和事務(wù)之間相互影響的話，情況會(huì)變得非常復(fù)雜，在某些MySQL版本中甚至?xí)a(chǎn)生無法預(yù)料的結(jié)果。因此，本書建議，除了事務(wù)中禁用了AUTOCOMMIT，可以使用LOCKTABLES之外，其他任何時(shí)候都不要顯式地執(zhí)行LOCKTABLES，不管使用的是什么存儲(chǔ)引擎。1.4多版本并發(fā)控制MySQL的大多數(shù)事務(wù)型存儲(chǔ)引擎實(shí)現(xiàn)的都不是簡(jiǎn)單的行級(jí)鎖。基于提升并發(fā)性能的考慮，它們一般都同時(shí)實(shí)現(xiàn)了多版本并發(fā)控制（MVCC）。不僅是MySQL，包括Oracle、PostgreSQL等其他數(shù)據(jù)庫系統(tǒng)也都實(shí)現(xiàn)了MVCC，但各自的實(shí)現(xiàn)機(jī)制不盡相同，因?yàn)镸VCC沒有一個(gè)統(tǒng)一的實(shí)現(xiàn)標(biāo)準(zhǔn)?？梢哉J(rèn)為MVCC是行級(jí)鎖的一個(gè)變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實(shí)現(xiàn)機(jī)制有所不同，但大都實(shí)現(xiàn)了非阻塞的讀操作，寫操作也只鎖定必要的行。MVCC的實(shí)現(xiàn)，是通過保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來實(shí)現(xiàn)的。也就是說，不管需要執(zhí)行多長(zhǎng)時(shí)間，每個(gè)事務(wù)看到的數(shù)據(jù)都是一致的。根據(jù)事務(wù)開始的時(shí)間不同，每個(gè)事務(wù)對(duì)同一張表，同一時(shí)刻看到的數(shù)據(jù)可能是不一樣的。如果之前沒有這方面的概念，這句話聽起來就有點(diǎn)迷惑。熟悉了以后會(huì)發(fā)現(xiàn)，這句話其實(shí)還是很容易理解的。前面說到不同存儲(chǔ)引擎的MVCC實(shí)現(xiàn)是不同的，典型的有樂觀（optimistic）并發(fā)控制和悲觀（pessimistic）并發(fā)控制。下面我們通過InnoDB的簡(jiǎn)化版行為來說明MVCC是如何工作的。InnoDB的MVCC，是通過在每行記錄后面保存兩個(gè)隱藏的列來實(shí)現(xiàn)的。這兩個(gè)列，一個(gè)保存了行的創(chuàng)建時(shí)間，一個(gè)保存行的過期時(shí)間（或刪除時(shí)間）。當(dāng)然存儲(chǔ)的并不是實(shí)際的時(shí)間值，而是系統(tǒng)版本號(hào)（systemversionnumber）。每開始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)都會(huì)自動(dòng)遞增。事務(wù)開始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)，用來和查詢到的每行記錄的版本號(hào)進(jìn)行比較。下面看一下在REPEATABLEREAD隔離級(jí)別下，MVCC具體是如何操作的。SELECTInnoDB會(huì)根據(jù)以下兩個(gè)條件檢查每行記錄：InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行（也就是，行的系統(tǒng)版本號(hào)小于或等于事務(wù)的系統(tǒng)版本號(hào)），這樣可以確保事務(wù)讀取的行，要么是在事務(wù)開始前已經(jīng)存在的，要么是事務(wù)自身插入或者修改過的。行的刪除版本要么未定義，要么大于當(dāng)前事務(wù)版本號(hào)。這可以確保事務(wù)讀取到的行，在事務(wù)開始之前未被刪除。只有符合上述兩個(gè)條件的記錄，才能返回作為查詢結(jié)果。INSERTInnoDB為新插入的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)。DELETEInnoDB為刪除的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行刪除標(biāo)識(shí)。UPDATEInnoDB為插入一行新記錄，保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)，同時(shí)保存當(dāng)前系統(tǒng)版本號(hào)到原來的行作為行刪除標(biāo)識(shí)。保存這兩個(gè)額外系統(tǒng)版本號(hào)，使大多數(shù)讀操作都可以不用加鎖。這樣設(shè)計(jì)使得讀數(shù)據(jù)操作很簡(jiǎn)單，性能很好，并且也能保證只會(huì)讀取到符合標(biāo)準(zhǔn)的行。不足之處是每行記錄都需要額外的存儲(chǔ)空間，需要做更多的行檢查工作，以及一些額外的維護(hù)工作。MVCC只在REPEATABLEREAD和READCOMMITTED兩個(gè)隔離級(jí)別下工作。其他兩個(gè)隔離級(jí)別都和MVCC不兼容\h\h(4)\h，因?yàn)镽EADUNCOMMITTED總是讀取最新的數(shù)據(jù)行，而不是符合當(dāng)前事務(wù)版本的數(shù)據(jù)行。而SERIALIZABLE則會(huì)對(duì)所有讀取的行都加鎖。1.5MySQL的存儲(chǔ)引擎本節(jié)只是概要地描述MySQL的存儲(chǔ)引擎，而不會(huì)涉及太多細(xì)節(jié)。因?yàn)殛P(guān)于存儲(chǔ)引擎的討論及其相關(guān)特性將會(huì)貫穿全書，而且本書也不是存儲(chǔ)引擎的完全指南，所以有必要閱讀相關(guān)存儲(chǔ)引擎的官方文檔。在文件系統(tǒng)中，MySQL將每個(gè)數(shù)據(jù)庫（也可以稱之為schema）保存為數(shù)據(jù)目錄下的一個(gè)子目錄。創(chuàng)建表時(shí)，MySQL會(huì)在數(shù)據(jù)庫子目錄下創(chuàng)建一個(gè)和表同名的.frm文件保存表的定義。例如創(chuàng)建一個(gè)名為MyTable的表，MySQL會(huì)在MyTable.frm文件中保存該表的定義。因?yàn)镸ySQL使用文件系統(tǒng)的目錄和文件來保存數(shù)據(jù)庫和表的定義，大小寫敏感性和具體的平臺(tái)密切相關(guān)。在Windows中，大小寫是不敏感的；而在類Unix中則是敏感的。不同的存儲(chǔ)引擎保存數(shù)據(jù)和索引的方式是不同的，但表的定義則是在MySQL服務(wù)層統(tǒng)一處理的。可以使用SHOWTABLESTATUS命令（在MySQL5.0以后的版本中，也可以查詢INFORMATION_SCHEMA中對(duì)應(yīng)的表）顯示表的相關(guān)信息。例如，對(duì)于mysql數(shù)據(jù)庫中的user表：mysql>SHOWTABLESTATUSLIKE'user'\G***************************1.row***************************Name:userEngine:MyISAMRow_format:DynamicRows:6Avg_row_length:59Data_length:356Max_data_length:4294967295Index_length:2048Data_free:0Auto_increment:NULLCreate_time:2002-01-2418:07:17Update_time:2002-01-2421:56:29Check_time:NULLCollation:utf8_binChecksum:NULLCreate_options:Comment:Usersandglobalprivileges1rowinset(0.00sec)輸出的結(jié)果表明，這是一個(gè)MyISAM表。輸出中還有很多其他信息以及統(tǒng)計(jì)信息。下面簡(jiǎn)單介紹一下每一行的含義。Name表名。Engine表的存儲(chǔ)引擎類型。在舊版本中，該列的名字叫Type，而不是Engine。Row_format行的格式。對(duì)于MyISAM表，可選的值為Dynamic、Fixed或者Compressed。Dynamic的行長(zhǎng)度是可變的，一般包含可變長(zhǎng)度的字段，如VARCHAR或BLOB。Fixed的行長(zhǎng)度則是固定的，只包含固定長(zhǎng)度的列，如CHAR和INTEGER。Compressed的行則只在壓縮表中存在，請(qǐng)參考第19頁“MyISAM壓縮表”一節(jié)。Rows表中的行數(shù)。對(duì)于MyISAM和其他一些存儲(chǔ)引擎，該值是精確的，但對(duì)于InnoDB，該值是估計(jì)值。Avg_row_length平均每行包含的字節(jié)數(shù)。Data_length表數(shù)據(jù)的大?。ㄒ宰止?jié)為單位）。Max_data_length表數(shù)據(jù)的最大容量，該值和存儲(chǔ)引擎有關(guān)。Index_length索引的大小（以字節(jié)為單位）。Data_free對(duì)于MyISAM表，表示已分配但目前沒有使用的空間。這部分空間包括了之前刪除的行，以及后續(xù)可以被INSERT利用到的空間。Auto_increment下一個(gè)AUTO_INCREMENT的值。Create_time表的創(chuàng)建時(shí)間。Update_time表數(shù)據(jù)的最后修改時(shí)間。Check_time使用CKECKTABLE命令或者myisamchk工具最后一次檢查表的時(shí)間。Collation表的默認(rèn)字符集和字符列排序規(guī)則。Checksum如果啟用，保存的是整個(gè)表的實(shí)時(shí)校驗(yàn)和。Create_options創(chuàng)建表時(shí)指定的其他選項(xiàng)。Comment該列包含了一些其他的額外信息。對(duì)于MyISAM表，保存的是表在創(chuàng)建時(shí)帶的注釋。對(duì)于InnoDB表，則保存的是InnoDB表空間的剩余空間信息。如果是一個(gè)視圖，則該列包含“VIEW”的文本字樣。1.5.1InnoDB存儲(chǔ)引擎InnoDB是MySQL的默認(rèn)事務(wù)型引擎，也是最重要、使用最廣泛的存儲(chǔ)引擎。它被設(shè)計(jì)用來處理大量的短期（short-lived）事務(wù)，短期事務(wù)大部分情況是正常提交的，很少會(huì)被回滾。InnoDB的性能和自動(dòng)崩潰恢復(fù)特性，使得它在非事務(wù)型存儲(chǔ)的需求中也很流行。除非有非常特別的原因需要使用其他的存儲(chǔ)引擎，否則應(yīng)該優(yōu)先考慮InnoDB引擎。如果要學(xué)習(xí)存儲(chǔ)引擎，InnoDB也是一個(gè)非常好的值得花最多的時(shí)間去深入學(xué)習(xí)的對(duì)象，收益肯定比將時(shí)間平均花在每個(gè)存儲(chǔ)引擎的學(xué)習(xí)上要高得多。InnoDB的歷史InnoDB有著復(fù)雜的發(fā)布?xì)v史，了解一下這段歷史對(duì)于理解InnoDB很有幫助。2008年，發(fā)布了所謂的InnoDBplugin，適用于MySQL5.1版本，但這是Oracle創(chuàng)建的下一代InnoDB引擎，其擁有者是InnoDB而不是MySQL。這基于很多原因，這些原因如果要一一道來，恐怕得喝掉好幾桶啤酒。MySQL默認(rèn)還是選擇了集成舊的InnoDB引擎。當(dāng)然用戶可以自行選擇使用新的性能更好、擴(kuò)展性更佳的InnoDBplugin來覆蓋舊的版本。直到最后，在Oracle收購了Sun公司后發(fā)布的MySQL5.5中才徹底使用InnoDBplugin替代了舊版本的InnoDB（是的，這也意味著InnoDBplugin已經(jīng)是原生編譯了，而不是編譯成一個(gè)插件，但名字已經(jīng)約定俗成很難更改）。這個(gè)現(xiàn)代的InnoDB版本，也就是MySQL5.1中所謂的InnoDBplugin，支持一些新特性，諸如利用排序創(chuàng)建索引（buildingindexbysorting）、刪除或者增加索引時(shí)不需要復(fù)制全表數(shù)據(jù)、新的支持壓縮的存儲(chǔ)格式、新的大型列值如BLOB的存儲(chǔ)方式，以及文件格式管理等。很多用戶在MySQL5.1中沒有使用InnoDBplugin，或許是因?yàn)樗麄儧]有注意到有這個(gè)區(qū)別。所以如果你使用的是MySQL5.1，一定要使用InnoDBplugin，真的比舊版本的InnoDB要好很多。InnoDB是一個(gè)很重要的存儲(chǔ)引擎，很多個(gè)人和公司都對(duì)其貢獻(xiàn)代碼，而不僅僅是Oracle公司的開發(fā)團(tuán)隊(duì)。一些重要的貢獻(xiàn)者包括Google、YasufumiKinoshita、Percona、Facebook等，他們的一些改進(jìn)被直接移植到官方版本，也有一些由InnoDB團(tuán)隊(duì)重新實(shí)現(xiàn)。在過去的幾年間，InnoDB的改進(jìn)速度大大加快，主要的改進(jìn)集中在可測(cè)量性、可擴(kuò)展性、可配置化、性能、各種新特性和對(duì)Windows的支持等方面。MySQL5.6實(shí)驗(yàn)室預(yù)覽版和里程碑版也包含了一系列重要的InnoDB新特性。為改善InnoDB的性能，Oracle投入了大量的資源，并做了很多卓有成效的工作（外部貢獻(xiàn)者對(duì)此也提供了很大的幫助）。在本書的第二版中，我們注意到在超過四核CPU的系統(tǒng)中InnoDB表現(xiàn)不佳，而現(xiàn)在已經(jīng)可以很好地?cái)U(kuò)展至24核的系統(tǒng)，甚至在某些場(chǎng)景，32核或者更多核的系統(tǒng)中也表現(xiàn)良好。很多改進(jìn)將在即將發(fā)布的MySQL5.6中引入，當(dāng)然也還有機(jī)會(huì)做更進(jìn)一步的改善。InnoDB概覽InnoDB的數(shù)據(jù)存儲(chǔ)在表空間（tablespace）中，表空間是由InnoDB管理的一個(gè)黑盒子，由一系列的數(shù)據(jù)文件組成。在MySQL4.1以后的版本中，InnoDB可以將每個(gè)表的數(shù)據(jù)和索引存放在單獨(dú)的文件中。InnoDB也可以使用裸設(shè)備作為表空間的存儲(chǔ)介質(zhì)，但現(xiàn)代的文件系統(tǒng)使得裸設(shè)備不再是必要的選擇。InnoDB采用MVCC來支持高并發(fā)，并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別。其默認(rèn)級(jí)別是REPEATABLEREAD（可重復(fù)讀），并且通過間隙鎖（next-keylocking）策略防止幻讀的出現(xiàn)。間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行，還會(huì)對(duì)索引中的間隙進(jìn)行鎖定，以防止幻影行的插入。InnoDB表是基于聚簇索引建立的，我們會(huì)在后面的章節(jié)詳細(xì)討論聚簇索引。InnoDB的索引結(jié)構(gòu)和MySQL的其他存儲(chǔ)引擎有很大的不同，聚簇索引對(duì)主鍵查詢有很高的性能。不過它的二級(jí)索引（secondaryindex，非主鍵索引）中必須包含主鍵列，所以如果主鍵列很大的話，其他的所有索引都會(huì)很大。因此，若表上的索引較多的話，主鍵應(yīng)當(dāng)盡可能的小。InnoDB的存儲(chǔ)格式是平臺(tái)獨(dú)立的，也就是說可以將數(shù)據(jù)和索引文件從Intel平臺(tái)復(fù)制到PowerPC或者SunSPARC平臺(tái)。InnoDB內(nèi)部做了很多優(yōu)化，包括從磁盤讀取數(shù)據(jù)時(shí)采用的可預(yù)測(cè)性預(yù)讀，能夠自動(dòng)在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應(yīng)哈希索引（adaptivehashindex），以及能夠加速插入操作的插入緩沖區(qū)（insertbuffer）等。本書后面將更詳細(xì)地討論這些內(nèi)容。InnoDB的行為是非常復(fù)雜的，不容易理解。如果使用了InnoDB引擎，筆者強(qiáng)烈建議閱讀官方手冊(cè)中的“InnoDB事務(wù)模型和鎖”一節(jié)。如果應(yīng)用程序基于InnoDB構(gòu)建，則事先了解一下InnoDB的MVCC架構(gòu)帶來的一些微妙和細(xì)節(jié)之處是非常有必要的。存儲(chǔ)引擎要為所有用戶甚至包括修改數(shù)據(jù)的用戶維持一致性的視圖，是非常復(fù)雜的工作。作為事務(wù)型的存儲(chǔ)引擎，InnoDB通過一些機(jī)制和工具支持真正的熱備份，Oracle提供的MySQLEnterpriseBackup、Percona提供的開源的XtraBackup都可以做到這一點(diǎn)。MySQL的其他存儲(chǔ)引擎不支持熱備份，要獲取一致性視圖需要停止對(duì)所有表的寫入，而在讀寫混合場(chǎng)景中，停止寫入可能也意味著停止讀取。1.5.2MyISAM存儲(chǔ)引擎在MySQL5.1及之前的版本，MyISAM是默認(rèn)的存儲(chǔ)引擎。MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(shù)（GIS）等，但MyISAM不支持事務(wù)和行級(jí)鎖，而且有一個(gè)毫無疑問的缺陷就是崩潰后無法安全恢復(fù)。正是由于MyISAM引擎的緣故，即使MySQL支持事務(wù)已經(jīng)很長(zhǎng)時(shí)間了，在很多人的概念中MySQL還是非事務(wù)型的數(shù)據(jù)庫。盡管MyISAM引擎不支持事務(wù)、不支持崩潰后的安全恢復(fù)，但它絕不是一無是處的。對(duì)于只讀的數(shù)據(jù)，或者表比較小、可以忍受修復(fù)（repair）操作，則依然可以繼續(xù)使用MyISAM（但請(qǐng)不要默認(rèn)使用MyISAM，而是應(yīng)當(dāng)默認(rèn)使用InnoDB）。存儲(chǔ)MyISAM會(huì)將表存儲(chǔ)在兩個(gè)文件中：數(shù)據(jù)文件和索引文件，分別以.MYD和.MYI為擴(kuò)展名。MyISAM表可以包含動(dòng)態(tài)或者靜態(tài)（長(zhǎng)度固定）行。MySQL會(huì)根據(jù)表的定義來決定采用何種行格式。MyISAM表可以存儲(chǔ)的行記錄數(shù)，一般受限于可用的磁盤空間，或者操作系統(tǒng)中單個(gè)文件的最大尺寸。在MySQL5.0中，MyISAM表如果是變長(zhǎng)行，則默認(rèn)配置只能處理256TB的數(shù)據(jù)，因?yàn)橹赶驍?shù)據(jù)記錄的指針長(zhǎng)度是6個(gè)字節(jié)。而在更早的版本中，指針長(zhǎng)度默認(rèn)是4字節(jié)，所以只能處理4GB的數(shù)據(jù)。而所有的MySQL版本都支持8字節(jié)的指針。要改變MyISAM表指針的長(zhǎng)度（調(diào)高或者調(diào)低），可以通過修改表的MAX_ROWS和AVG_ROW_LENGTH選項(xiàng)的值來實(shí)現(xiàn)，兩者相乘就是表可能達(dá)到的最大大小。修改這兩個(gè)參數(shù)會(huì)導(dǎo)致重建整個(gè)表和表的所有索引，這可能需要很長(zhǎng)的時(shí)間才能完成。MyISAM特性作為MySQL最早的存儲(chǔ)引擎之一，MyISAM有一些已經(jīng)開發(fā)出來很多年的特性，可以滿足用戶的實(shí)際需求。加鎖與并發(fā)MyISAM對(duì)整張表加鎖，而不是針對(duì)行。讀取時(shí)會(huì)對(duì)需要讀到的所有表加共享鎖，寫入時(shí)則對(duì)表加排他鎖。但是在表有讀取查詢的同時(shí)，也可以往表中插入新的記錄（這被稱為并發(fā)插入，CONCURRENTINSERT）。修復(fù)對(duì)于MyISAM表，MySQL可以手工或者自動(dòng)執(zhí)行檢查和修復(fù)操作，但這里說的修復(fù)和事務(wù)恢復(fù)以及崩潰恢復(fù)是不同的概念。執(zhí)行表的修復(fù)可能導(dǎo)致一些數(shù)據(jù)丟失，而且修復(fù)操作是非常慢的?？梢酝ㄟ^CHECKTABLEmytable檢查表的錯(cuò)誤，如果有錯(cuò)誤可以通過執(zhí)行REPAIRTABLEmytable進(jìn)行修復(fù)。另外，如果MySQL服務(wù)器已經(jīng)關(guān)閉，也可以通過myisamchk命令行工具進(jìn)行檢查和修復(fù)操作。索引特性對(duì)于MyISAM表，即使是BLOB和TEXT等長(zhǎng)字段，也可以基于其前500個(gè)字符創(chuàng)建索引。MyISAM也支持全文索引，這是一種基于分詞創(chuàng)建的索引，可以支持復(fù)雜的查詢。關(guān)于索引的更多信息請(qǐng)參考第5章。延遲更新索引鍵（DelayedKeyWrite）創(chuàng)建MyISAM表的時(shí)候，如果指定了DELAY_KEY_WRITE選項(xiàng)，在每次修改執(zhí)行完成時(shí)，不會(huì)立刻將修改的索引數(shù)據(jù)寫入磁盤，而是會(huì)寫到內(nèi)存中的鍵緩沖區(qū)（in-memorykeybuffer），只有在清理鍵緩沖區(qū)或者關(guān)閉表的時(shí)候才會(huì)將對(duì)應(yīng)的索引塊寫入到磁盤。這種方式可以極大地提升寫入性能，但是在數(shù)據(jù)庫或者主機(jī)崩潰時(shí)會(huì)造成索引損壞，需要執(zhí)行修復(fù)操作。延遲更新索引鍵的特性，可以在全局設(shè)置，也可以為單個(gè)表設(shè)置。MyISAM壓縮表如果表在創(chuàng)建并導(dǎo)入數(shù)據(jù)以后，不會(huì)再進(jìn)行修改操作，那么這樣的表或許適合采用MyISAM壓縮表。可以使用myisampack對(duì)MyISAM表進(jìn)行壓縮（也叫打包pack）。壓縮表是不能進(jìn)行修改的（除非先將表解除壓縮，修改數(shù)據(jù)，然后再次壓縮）。壓縮表可以極大地減少磁盤空間占用，因此也可以減少磁盤I/O，從而提升查詢性能。壓縮表也支持索引，但索引也是只讀的。以現(xiàn)在的硬件能力，對(duì)大多數(shù)應(yīng)用場(chǎng)景，讀取壓縮表數(shù)據(jù)時(shí)的解壓帶來的開銷影響并不大，而減少I/O帶來的好處則要大得多。壓縮時(shí)表中的記錄是獨(dú)立壓縮的，所以讀取單行的時(shí)候不需要去解壓整個(gè)表（甚至也不解壓行所在的整個(gè)頁面）。MyISAM性能MyISAM引擎設(shè)計(jì)簡(jiǎn)單，數(shù)據(jù)以緊密格式存儲(chǔ)，所以在某些場(chǎng)景下的性能很好。MyISAM有一些服務(wù)器級(jí)別的性能擴(kuò)展限制，比如對(duì)索引鍵緩沖區(qū)（keycache）的Mutex鎖，MariaDB基于段（segment）的索引鍵緩沖區(qū)機(jī)制來避免該問題。但MyISAM最典型的性能問題還是表鎖的問題，如果你發(fā)現(xiàn)所有的查詢都長(zhǎng)期處于“Locked”狀態(tài)，那么毫無疑問表鎖就是罪魁禍?zhǔn)住?.5.3MySQL內(nèi)建的其他存儲(chǔ)引擎MySQL還有一些有特殊用途的存儲(chǔ)引擎。在新版本中，有些可能因?yàn)橐恍┰蛞呀?jīng)不再支持；另外還有些會(huì)繼續(xù)支持，但是需要明確地啟用后才能使用。Archive引擎Archive存儲(chǔ)引擎只支持INSERT和SELECT操作，在MySQL5.1之前也不支持索引。Archive引擎會(huì)緩存所有的寫并利用zlib對(duì)插入的行進(jìn)行壓縮，所以比MyISAM表的磁盤I/O更少。但是每次SELECT查詢都需要執(zhí)行全表掃描。所以Archive表適合日志和數(shù)據(jù)采集類應(yīng)用，這類應(yīng)用做數(shù)據(jù)分析時(shí)往往需要全表掃描。或者在一些需要更快速的INSERT操作的場(chǎng)合下也可以使用。Archive引擎支持行級(jí)鎖和專用的緩沖區(qū)，所以可以實(shí)現(xiàn)高并發(fā)的插入。在一個(gè)查詢開始直到返回表中存在的所有行數(shù)之前，Archive引擎會(huì)阻止其他的SELECT執(zhí)行，以實(shí)現(xiàn)一致性讀。另外，也實(shí)現(xiàn)了批量插入在完成之前對(duì)讀操