MySQL邏輯架構(gòu)

MySQL邏輯架構(gòu).png

優(yōu)化與執(zhí)行

• MySQL會解析查詢俘闯，創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)（解析樹）赦拘，對齊進(jìn)行優(yōu)化（重寫查詢、決定表的讀取順序垛贤、選擇合適的索引）焰坪；
• 使用explain，可以解釋優(yōu)化過程的各個因素聘惦，使用戶知道服務(wù)器是如何進(jìn)行優(yōu)化決策的某饰，并提供一個參考基準(zhǔn)，便于重構(gòu)查詢和表善绎、修改配置黔漂；
• 優(yōu)化器不關(guān)心表用的是什么存儲引擎，但存儲引擎對優(yōu)化查詢是有影響的禀酱；
• 例：SELECT語句炬守，解析查詢之前，服務(wù)器先檢查查詢緩存（Query Cache）剂跟，若找到對應(yīng)的查詢减途，服務(wù)器就不必再執(zhí)行查詢解析、優(yōu)化和執(zhí)行的整個過程曹洽，而直接返回查詢緩存中的結(jié)果鳍置。

并發(fā)控制

• 多個查詢需要在同一時刻修改數(shù)據(jù)，即會產(chǎn)生并發(fā)控制的問題送淆；
• 并發(fā)控制有兩個層面：服務(wù)器層税产、存儲引擎層；
• 讀寫鎖：處理并發(fā)讀寫時偷崩，用共享鎖/讀鎖（shared lock/read lock）和排他鎖/寫鎖（exclusive lock/write lock）來進(jìn)行并發(fā)控制砖第；
• 鎖粒度：要提高共享資源的并發(fā)性，需盡量只鎖定需要修改的部分?jǐn)?shù)據(jù)环凿，給定的資源梧兼，鎖定的數(shù)據(jù)量越少，并發(fā)度就越高智听；加鎖也消耗資源（鎖的操作：獲得鎖羽杰、檢查鎖是否解除、釋放鎖）到推；需要采取合適的鎖策略（在鎖的開銷和數(shù)據(jù)的安全性之間制衡）考赛；
• 表鎖：開銷最小的策略，對表插入/更新/刪除莉测，需要先獲取寫鎖颜骤，阻塞其他讀寫操作，沒有寫鎖的時候捣卤，其他讀取操作才能獲得讀鎖忍抽，讀鎖之間不互相阻塞八孝；寫鎖比讀鎖優(yōu)先級高，寫鎖可以插隊到鎖隊列中讀鎖的前面鸠项；服務(wù)器會為ALTER TABLE之類的語句加表鎖干跛，而忽略了存儲引擎的鎖機(jī)制；
• 行鎖：開銷最大的策略祟绊，最大程度支持并發(fā)處理楼入。

事務(wù)

• 事務(wù)是一組原子性的SQL查詢，其中的語句要么全部執(zhí)行成功牧抽，要么全部執(zhí)行失敿涡堋；
• ACID特性：原子性（Atomicity）扬舒、一致性（Consistency）阐肤、隔離性（Isolation）、持久性（Durability）呼巴；
• 四種隔離級別：未提交讀（Read Uncommitted）、提交讀（Read Committed）御蒲、可重復(fù)讀（Repeatable Read）衣赶、可串行化（Serializable）

• 死鎖：兩個或多個事務(wù)在同一資源上相互占用，并請求鎖定對方的資源厚满，導(dǎo)致惡行循環(huán)
• 死鎖的處理：死鎖會造成非常慢的查詢府瞄，InnoDB處理死鎖的方法是，將持有最少行級排他鎖的事務(wù)進(jìn)行回滾
• 死鎖的原因：鎖的行為和順序和存儲引擎相關(guān)碘箍，以同樣順序執(zhí)行語句遵馆，有些存儲引擎會死鎖，有些不會丰榴；死鎖產(chǎn)生原因有兩個货邓，真正的數(shù)據(jù)沖突、存儲引擎的實現(xiàn)方式四濒。
• MySQL中的事務(wù)：自動提交（AUTOCOMMIT）默認(rèn)開啟换况，不是顯示地開始一個事務(wù)，則每個查詢都當(dāng)作一個事務(wù)執(zhí)行提交操作盗蟆；數(shù)據(jù)定義語言（DDL）中戈二，若是會導(dǎo)致大量數(shù)據(jù)改變的操作，如ALTER TABLE喳资、LOCK TABLES觉吭，會在執(zhí)行前強制執(zhí)行COMMIT提交當(dāng)前的活動事務(wù)；
• MYSQL服務(wù)器不管理事務(wù)仆邓，事務(wù)是由下層的存儲引擎實現(xiàn)的鲜滩，在同一個事務(wù)中伴鳖，使用多種存儲引擎是不可靠的，因為非事務(wù)型的表上的變更無法撤銷绒北；
• InnoDB可以進(jìn)行顯示鎖定：SELECT ... LOCK IN SHARE MODE黎侈；SELECT ... FOR UPDATE。

多版本并發(fā)控制

• MVCC是行級鎖的變種闷游，但在很多情況下避免加鎖峻汉，開銷更低；
• MVCC的實現(xiàn)：保存數(shù)據(jù)在某個時間點的某個快照脐往；
• InnoDB的MVCC：在每行記錄后面保存兩個隱藏的列來實現(xiàn)休吠，一個保存行的創(chuàng)建時間，一個保存行的過期時間业簿，存儲的是系統(tǒng)版本號瘤礁，每開始一個新的事務(wù)，系統(tǒng)版本號會自動遞增梅尤，事務(wù)開始時刻的系統(tǒng)版本號會作為事務(wù)的版本號柜思，用來和查詢到的每行記錄的版本號比較；
• MVCC只在REPEATABLE READ巷燥、READ COMMITTED兩個隔離級別下工作（因為赡盘，READ UNCOMMITTED總是讀取最新的行，而非符合當(dāng)前事務(wù)版本的行缰揪；SERIALIZABLE會對所有讀取的行都加鎖）陨享。

MySQL的存儲引擎

• InnoDB存儲引擎：InnoDB的數(shù)據(jù)保存在表空間中，表空間是由InnoDB管理的一個黑盒子钝腺，由一系列數(shù)據(jù)文件組成抛姑；InnoDB采用MVCC來支持高并發(fā)，并實現(xiàn)了四個標(biāo)準(zhǔn)的隔離級別艳狐，默認(rèn)級別是REPEATABLE READ定硝，并通過間隙鎖（next-key locking）策略防止幻讀的出現(xiàn)（間隙鎖使InnoDB不僅鎖定查詢涉及的行，還會對索引中的間隙進(jìn)行鎖定毫目，防止幻影行的插入）喷斋；InnoDB表基于聚簇索引建立，聚簇索引對主鍵查詢由很高的性能蒜茴，但它的二級索引必須包含主鍵列星爪，所以如果主鍵列很大，其他所有的索引都會很大粉私。
• MyISAM存儲引擎：MyISAM支持全文索引顽腾、壓縮、空間函數(shù)（GIS）等，但不支持事務(wù)和行級鎖抄肖，崩潰后無法安全恢復(fù)久信；MyISAM會將表存儲在兩個文件中：數(shù)據(jù)文件和索引文件；MyISAM特性：加鎖與并發(fā)（讀取時會對需要讀到的所有表加共享鎖漓摩，寫入時對表加排他鎖裙士，但在表有讀取查詢的同時，也可以往表中插入新的記錄管毙，即并發(fā)插入）腿椎、BLOB和TEXT等長字段也可以基于前500字符創(chuàng)建索引、支持全文索引夭咬，這是一種基于分詞創(chuàng)建的索引啃炸；MyISAM壓縮表，不能修改卓舵，極大的減少了磁盤空間占用南用，減少磁盤IO，提升查詢性能掏湾，壓縮表也支持索引裹虫，但索引也是只讀的；MyISAM最典型的性能問題是表鎖的問題融击。
• 存儲引擎的選擇：除非需要用到某些InnoDB不具備的特性筑公，并且沒有其他辦法替代，否則都應(yīng)該優(yōu)先選擇InnoDB引擎砚嘴；最好不要混用存儲引擎十酣，需要混用時首先考慮后面幾個因素涩拙，事務(wù)（InnoDB际长、XtraDB最穩(wěn)定，不需要事務(wù)且主要是SELECT和INSERT兴泥，如日志型應(yīng)用工育，則可選擇MyISAM）、備份（InnoDB支持在線熱備份）搓彻、崩潰恢復(fù)（MyISAM崩潰后發(fā)生損壞的概率比InnoDB高很多如绸，且恢復(fù)速度慢）、特有的特性（應(yīng)用依賴聚簇索引的優(yōu)化-InnoDB旭贬，只有MyISAM支持地理空間搜索）怔接。
• 例子：日志型應(yīng)用（MyISAM、Archive稀轨，開銷低扼脐、插入速度非常快，日志記錄表名字包含日期瓦侮，在沒有插入操作的歷史表上可以做頻繁的查詢操作艰赞，不會干擾到最新的當(dāng)前表）、只讀或大部分情況下只讀的表（MyISAM肚吏，但要注意崩潰恢復(fù)的問題）方妖；訂單處理（InnoDB，需要事務(wù)的支持罚攀，對外鍵的支持）党觅。
• 轉(zhuǎn)換表引擎：ALTER TABLE（執(zhí)行時間長，會先將數(shù)據(jù)復(fù)制到一張新表坞生，復(fù)制可能會消耗系統(tǒng)所有的IO能力仔役，原表還會加上讀鎖；轉(zhuǎn)換表引擎是己，會失去原引擎相關(guān)的所有特性）又兵、手動導(dǎo)入導(dǎo)出、創(chuàng)建與查詢（創(chuàng)建一個同樣結(jié)構(gòu)的表卒废，改變存儲引擎沛厨，然后INSERT INTO innodb_table SELECT * FROM myisam_table，可以在執(zhí)行過程中對原表加鎖摔认，以確保新表和原表的數(shù)據(jù)一致）逆皮。