數(shù)據(jù)庫優(yōu)化可以說是后臺開發(fā)中永恒的話題，數(shù)據(jù)庫的性能通常是整個服務(wù)吞吐量的瓶頸之所在。
1. 索引概述
InnoDB中的表都是按照主鍵順序組織存放的，這種組織方式稱之為索引組織表，對比于MyISAM的表組織方式。在InnoDB中每張表都必須有一個主鍵，如果在創(chuàng)建表的時候沒有顯式定義主鍵，則InnoDB首先會判斷表中是否有非空的唯一索引，如果有則將該列作為主鍵；否則InnoDB會自動創(chuàng)建一個6字節(jié)大小的指針作為主鍵。除主鍵之外，InnoDB還可以有輔助索引，而輔助索引頁中僅僅存放鍵值和指向數(shù)據(jù)頁的偏移量，而不像主鍵數(shù)據(jù)頁存儲的是一個完整的行記錄。
InnoDB存儲引擎中，所有的數(shù)據(jù)都被邏輯地存放在一個表空間中，表空間又被分為段(Segment)、區(qū)(Extent)、頁(Page)組成，其中段由存儲引擎自動管理，區(qū)的大小固定為1M，然后默認(rèn)情況下頁的大小為16KB，也就是一個區(qū)總共有64個連續(xù)的頁組成。不過在MySQL5.6開始，頁的大小可以設(shè)置為4K、8K了，設(shè)置成4K除了可以提高磁盤的利用率之外，對于現(xiàn)代SSD硬盤將更加合適，不過這中更新比較的麻煩，需要將輸入導(dǎo)出后再重新導(dǎo)入，一般的備份恢復(fù)工具都是原樣復(fù)制數(shù)據(jù)，沒有辦法支持變更頁大小。
默認(rèn)的B+樹索引其查找次數(shù)(效率)取決于B+樹的高度，生產(chǎn)環(huán)境下一般樹高為3~4層，即查詢一條記錄需要經(jīng)過3~4個索引頁，而且B+樹索引并不能找到一個給定鍵值的具體行，其只能根據(jù)鍵和索引找到數(shù)據(jù)行所在的頁，然后數(shù)據(jù)庫把對應(yīng)的頁讀取到內(nèi)存，再在內(nèi)存中執(zhí)行查找，并最后得到需要查詢的數(shù)據(jù)。InnoDB還會監(jiān)控對表上各索引頁的查詢操作，如果觀察到通過建立hash索引可以帶來速度提升，則會根據(jù)訪問頻率和訪問模式自動為部分熱點頁建立hash索引，這個過程稱之為自適應(yīng)哈希索引，而且該過程是人為無法干預(yù)、存儲引擎自動實現(xiàn)的。
使用索引的一大禁忌是不要在引用索引列的時候使用函數(shù)，比如max(id)、id+3>5等，或者隱式的數(shù)據(jù)類型轉(zhuǎn)換操作，這樣會導(dǎo)致索引失效導(dǎo)致全掃描。
2. 在線修改數(shù)據(jù)表
在MySQL 5.5之前修改表結(jié)構(gòu)、或者創(chuàng)建新索引的時候，需要經(jīng)過：先鎖定原始表，創(chuàng)建一張新的臨時表(臨時使用tmpdir路徑，確保有足夠空間可用)，然后把原表中的數(shù)據(jù)導(dǎo)入到新的臨時表中，接著刪除原表，最后再把臨時表重新命名為原來的表名。所以修改表結(jié)構(gòu)需要注意，將對同一個表的ALTER TABLE多個操作合并到一條語句中，減少上述重復(fù)的步驟。同時，針對修改列名、修改數(shù)值類型的表示長度INT(3)->INT(10)、修改數(shù)據(jù)表注釋、向ENUM增加新的類型、修改數(shù)據(jù)表名這些操作不需要將數(shù)據(jù)表中的所有記錄都復(fù)制到臨時表。
新版MySQL支持Fast Index Creation，具體說來就是對于新輔助索引的創(chuàng)建，InnoDB會對要創(chuàng)建索引的表上一個S鎖，使該表以只讀的可用性提供服務(wù)，由于不需要重新創(chuàng)建表、拷貝數(shù)據(jù)，因而輔助索引的創(chuàng)建速度也快很多；刪除索引的時候InnoDB只需更新內(nèi)部試圖標(biāo)記輔助索引的空間為可用，同時刪除MySQL數(shù)據(jù)庫內(nèi)部試圖上對應(yīng)表的索引定義即可。
MySQL 5.6的版本支持Online DDL，允許在輔助索引創(chuàng)建的同時，還允許對表同時執(zhí)行諸如INSERT、UPDATE、DELETE等DML操作而不會被阻塞，其原理是在執(zhí)行索引創(chuàng)建或者刪除操作的時候，將INSERT、UPDATE、DELETE這類的操作日志先記錄到一個叫做“在線修改日志”的內(nèi)存空間中，當(dāng)索引完成后再重新應(yīng)用這些更新到表上，以此達(dá)到數(shù)據(jù)的一致性。不過“在線修改日志“只存留在內(nèi)存中，默認(rèn)大小是128MB，如果修改表結(jié)構(gòu)時候DML操作太多，會導(dǎo)致該空間不夠用而撤銷修改。
3. 創(chuàng)建索引
創(chuàng)建索引的時候講求一個Cardinality指標(biāo)，該值表示索引中唯一值的估計數(shù)目，理想情況下該值除以表行數(shù)應(yīng)該盡可能接近1，否則表示該列選擇性太低而應(yīng)該考慮刪除該索引。 對Cardinality的統(tǒng)計是使用采樣方式進(jìn)行估算的，當(dāng)表的修改數(shù)目超過總記錄的1/16、或者修改總次數(shù)超過20億次，則會隨機(jī)選擇8個數(shù)據(jù)頁重新統(tǒng)計該值，不過通過ANALYZE TABLE命令可以強(qiáng)制讓數(shù)據(jù)庫重新收集相關(guān)的統(tǒng)計信息。
實踐中OLTP和OLAP對索引的要求是有差異的，在OLTP應(yīng)用中查詢操作通常只從數(shù)據(jù)庫返回很小部分?jǐn)?shù)據(jù)集，此時根據(jù)查詢條件選擇高區(qū)分度的列來創(chuàng)建索引是很有意義的；對于OLAP應(yīng)用通常都需要返回大批量的數(shù)據(jù)，很多情況下建立索引意義不是很大，因為大量數(shù)據(jù)返回的話往往全表順序掃描效率更高，不過OLAP中對時間創(chuàng)建索引是很常見的操作。
4. 覆蓋索引
表示直接從輔助索引中就可以得到需要的查詢記錄，而不需要再從聚簇索引中查詢行記錄。使用覆蓋索引的好處是輔助索引不包含整行記錄，所以索引大小會遠(yuǎn)遠(yuǎn)小于聚簇索引，單個索引頁就可以存儲更多的索引項，那么訪問索引本身的操作就可以減少順序IO操作了。有些情況，比如在MySQL中SELECT COUNT(*) FROM t;優(yōu)化器是可以選擇使用輔助索引來優(yōu)化查詢速度的，因為可以訪問更少的索引頁就可以統(tǒng)計到查詢結(jié)果了。
如果SELECT列不能使用覆蓋索引完成，那么除了在輔助索引上查到指定記錄后，還需要進(jìn)行一次書簽訪問才能查找到整行中其他列的數(shù)據(jù)，并且此時的查找將是成本很高的隨機(jī)離散讀操作(相對于傳統(tǒng)機(jī)械磁盤)。
所以如果優(yōu)化器覺得需要返回的數(shù)據(jù)量很少，則優(yōu)化器還是可能會選擇使用輔助索引外加訪問聚簇索引的方式來返回記錄的；但是當(dāng)訪問數(shù)據(jù)量占整個表記錄中挺大一部分的時候(比如20%)，則優(yōu)化器可能會選擇全表掃描的方式來查找數(shù)據(jù)，因為全表順序讀的代價可能比大量隨機(jī)讀的效率要高。大部分時候優(yōu)化器都能做的不錯，不過當(dāng)用戶有對索引的使用有足夠信心的時候還是可以影響優(yōu)化器執(zhí)行計劃的生成的，比如：可以使用USE INDEX的方式來提示優(yōu)化器使用某個索引，不過實際上優(yōu)化器還是會根據(jù)自己的判斷確定是否需要使用該索引；而通過FORCE INDEX則會強(qiáng)制選擇使用該索引；使用IGNORE INDEX會使優(yōu)化器不能使用指定的索引，這通?？梢哉T導(dǎo)觸發(fā)執(zhí)行全表掃描。
5. Multi-Range Read(MRR)優(yōu)化
為了防止非覆蓋索引取數(shù)據(jù)的時候造成的大量隨機(jī)I/O，MyISAM和InnoDB會將查詢到的輔助索引存放在一個緩存中，然后將他們通過主鍵進(jìn)行排序，并按排序后的主鍵進(jìn)行順序書簽查找。通過這種方式可以將低效隨機(jī)訪問轉(zhuǎn)化為高效順序數(shù)據(jù)訪問，而且同一數(shù)據(jù)塊確保只需要被訪問一次，同時也減少緩沖池中頁被替換的次數(shù)，所以可以帶來查詢性能的極大提升。
MySQL5.6開始支持該項優(yōu)化，使用的時候需要SET optimizer_switch=’mrr=on|off’的方式打開。MRR特性可以用于range、ref、eq_ref類型的查詢操作，當(dāng)查詢使用到該特性的時候就可以在Extra看到Using MRR提示了，當(dāng)在有表連接的情況下，如果連接鍵是被驅(qū)動表的主鍵的時候，也會先基于驅(qū)動表的連接鍵進(jìn)行排序，按照這個順序就可以MRR按照被驅(qū)動表的主鍵訪問數(shù)據(jù)了。
從上面的介紹看到MRR是一個思路簡單但是卻很重要的優(yōu)化，但是在某些情況下使用也可能會有負(fù)面效應(yīng)。當(dāng)表的數(shù)據(jù)量很小，大部分?jǐn)?shù)據(jù)也都被緩存的時候，使用MRR不會帶來隨機(jī)訪問的收益，反而會因為額外的排序操作增加資源消耗；當(dāng)限制只需要返回LIMIT n的時候，這種優(yōu)化會讀取排序很多不需要的索引，性能反而會降低；排序使用的內(nèi)存空間大小由mrr_buffer_size設(shè)定的，如果該內(nèi)存較小但是待排序的索引數(shù)量大的時候，就需要使用磁盤輔助進(jìn)行多塊排序歸并，這也會降低性能。
6. Index Condition Pushdown(ICP)優(yōu)化
老舊數(shù)據(jù)庫版本只有索引可用的限制條件才會被傳輸?shù)酱鎯σ鎸?，在新版本開啟ICP優(yōu)化的時候，針對選用索引涉及到的數(shù)據(jù)列條件就都會被傳輸?shù)酱鎯σ鎸?，所以在支持ICP特性后，存儲引擎在處理索引的同時就可以判斷是否可以通過下推的選擇條件對部分記錄直接進(jìn)行過濾操作了。所以在老版本的數(shù)據(jù)庫，都是存儲引擎對索引可以直接使用的條件進(jìn)行操作，然后再將這些數(shù)據(jù)傳遞給MySQL引擎，這樣就會涉及到大量數(shù)據(jù)條目的讀取、傳遞和篩選工作，這時候在Extra中肯定會看到Using where的提示，因為MySQL引擎對存儲引擎?zhèn)鬟f來的數(shù)據(jù)進(jìn)行了篩選加工；現(xiàn)在將索引涉及到的篩選條件下推放到了存儲引擎層，就大大減少了上面的操作任務(wù)。
該功能可以使用SET optimizer_switch=’index_condition_pushdown=on|off’的方式打開或者關(guān)閉。ICP優(yōu)化可以用于range、ref、req_ref、ref_or_null類型的查詢，當(dāng)查詢使用到該特性的時候可以在Extra看到Using index condition。
7. 索引合并
當(dāng)查詢WHERE中羅列有多個條件，他們都可以使用不同的索引進(jìn)行優(yōu)化查詢的時候，如果優(yōu)化器發(fā)現(xiàn)某一個索引返回的記錄相比其他索引顯著的要少，那么執(zhí)行計劃就會選用這個索引；而如果優(yōu)化器發(fā)現(xiàn)多個索引都不高效的時候，優(yōu)化器會將這些查詢條件分離，用各自的索引分別獨立執(zhí)行檢索，最后再將多個結(jié)果集合進(jìn)行合并后返回。當(dāng)然，這種情況優(yōu)化器也可能使用全表掃面的方式處理。
