在需要支持移動/平板電腦應(yīng)用及普通桌面瀏覽器訪問的時代，網(wǎng)站的普及率和有效性很大程度上取決于其可用性和性能。一個訪問緩慢的網(wǎng)站會使得訪問者或潛在的客戶流失，并導(dǎo)致商業(yè)的失敗。一個訪問速度相當(dāng)快的網(wǎng)站將會決定訪客是否會使用網(wǎng)站提供的產(chǎn)品或服務(wù)。

擁有大規(guī)模數(shù)據(jù)庫的網(wǎng)站始終需要適當(dāng)?shù)年P(guān)注、配置、優(yōu)化、調(diào)整和維護(hù)，以確保網(wǎng)站的快速加載。這篇文章將討論如何優(yōu)化有海量數(shù)據(jù)的 MySQL 數(shù)據(jù)庫。

選擇 InnoDB 作為存儲引擎

大型產(chǎn)品的數(shù)據(jù)庫對于可靠性和并發(fā)性的要求較高，InnoDB 作為默認(rèn)的 MySQL 存儲引擎，相對于 MyISAM 來說是個更佳的選擇。

優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)

• 組織數(shù)據(jù)庫的 schema、表和字段以降低 I/O 的開銷，將相關(guān)項保存在一起，并提前規(guī)劃，以便隨著數(shù)據(jù)量的增長，性能可以保持較高的水平。
• 設(shè)計數(shù)據(jù)表應(yīng)盡量使其占用的空間最小化，表的主鍵應(yīng)盡可能短。
• 對于 InnoDB 表，主鍵所在的列在每個輔助索引條目中都是可復(fù)制的，因此如果有很多輔助索引，那么一個短的主鍵可以節(jié)省大量空間。
• 僅創(chuàng)建你需要改進(jìn)查詢性能的索引。索引有助于檢索，但是會增加插入和更新操作的執(zhí)行時間。

InnoDB 的 Change Buffering 特性

InnoDB 提供了 change buffering 的配置，可減少維護(hù)輔助索引所需的磁盤 I/O。大規(guī)模的數(shù)據(jù)庫可能會遇到大量的表操作和大量的 I/O，以保證輔助索引保持***。當(dāng)相關(guān)頁面不在緩沖池里面時，InnoDB 的 change buffer 將會更改緩存到輔助索引條目，從而避免因不能立即從磁盤讀取頁面而導(dǎo)致耗時的 I/O 操作。當(dāng)頁面被加載到緩沖池時，緩沖的更改將被合并，更新的頁面之后會刷新到磁盤。這樣做可提高性能，適用于 MySQL 5.5 及更高版本。

InnoDB 頁面壓縮

InnoDB 支持對表進(jìn)行頁面級的壓縮。當(dāng)寫入數(shù)據(jù)頁的時候，會有特定的壓縮算法對其進(jìn)行壓縮。壓縮后的數(shù)據(jù)會寫入磁盤，其打孔機(jī)制會釋放頁面末尾的空塊。如果壓縮失敗，數(shù)據(jù)會按原樣寫入。表和索引都會被壓縮，因為索引通常是數(shù)據(jù)庫總大小中占比很大的一部分，壓縮可以顯著節(jié)約內(nèi)存，I/O 或處理時間，這樣就達(dá)到了提高性能和伸縮性的目的。它還可以減少內(nèi)存和磁盤之間傳輸?shù)臄?shù)據(jù)量。MySQL 5.1 及更高版本支持該功能。

注意，頁面壓縮并不能支持共享表空間中的表。共享表空間包括系統(tǒng)表空間、臨時表空間和常規(guī)表空間。

使用批量數(shù)據(jù)導(dǎo)入

在主鍵上使用已排序的數(shù)據(jù)源進(jìn)行批量數(shù)據(jù)的導(dǎo)入可加快數(shù)據(jù)插入的過程。否則，可能需要在其他行之間插入行以維護(hù)排序，這會導(dǎo)致磁盤 I/O 變高，進(jìn)而影響性能，增加頁的拆分。關(guān)閉自動提交的模式也是有好處的，因為它會為每個插入執(zhí)行日志刷新到磁盤。在批量插入期間臨時轉(zhuǎn)移唯一鍵和外鍵檢查也可顯著降低磁盤 I/O。對于新建的表，***的做法是在批量導(dǎo)入后創(chuàng)建外鍵/唯一鍵約束。

SQL 語句優(yōu)化

為了提升查詢的速度，可以為 WHERE 字句中使用的列添加索引。此外，不要將主鍵索引用于太多或太長的列，因為這些列值在輔助索引進(jìn)行復(fù)制的時候會增加讀取所需要的 I/O 資源并占用緩存。

如果索引包含了不必要的數(shù)據(jù)，通過 I/O 讀取這些數(shù)據(jù)并進(jìn)行緩存就會減弱服務(wù)器的性能和伸縮性。也不要為不必要的列使用唯一鍵索引，因為它會禁用 change buffering。應(yīng)該使用常規(guī)索引代替。

減少和隔離需要耗費(fèi)大量時間的函數(shù)調(diào)用。

盡可能的減少查詢中的全表掃描次數(shù)。

調(diào)整緩存區(qū)域的大小和屬性，比如 InnoDB 緩沖池，MySQL 查詢緩存等，這樣會通過從內(nèi)存而非從硬盤獲取數(shù)據(jù)而讓重復(fù)的查詢變得更快。

優(yōu)化存儲結(jié)構(gòu)

對于大型的表，或者包含大量重復(fù)文本或數(shù)值數(shù)據(jù)的表，應(yīng)該考慮使用 COMPRESSED(壓縮的) 行格式。這樣只需要較少的 I/O 就可以把數(shù)據(jù)取到緩沖池，或執(zhí)行全表掃描。

一旦你的數(shù)據(jù)達(dá)到穩(wěn)定的大小，或者增長的表增加了幾十或幾百兆字節(jié)，就應(yīng)該考慮使用 OPTIMIZE TABLE 語句重新組織表并壓縮浪費(fèi)的空間。對重新組織后的表進(jìn)行全表掃描所需要的 I/O 會更少。

優(yōu)化 InnoDB 磁盤 I/O

增加 InnoDB 緩沖池大小可以讓查詢從緩沖池訪問而不是通過磁盤 I/O 訪問。通過調(diào)整系統(tǒng)變量 innodb_flush_method 來調(diào)整清除緩沖的指標(biāo)使其達(dá)到***水平。

使用其它存儲設(shè)備配置 RAID。

MySQL 的內(nèi)存分配

在為 MySQL 分配足夠的內(nèi)存之前，請考慮不同領(lǐng)域?qū)?MySQL 的內(nèi)存需求。

要考慮的關(guān)鍵領(lǐng)域是：并發(fā)連接 —— 對于大量并發(fā)連接，排序和臨時表將需要大量內(nèi)存。在撰寫本文時，對于處理 3000+ 并發(fā)連接的數(shù)據(jù)庫，16GB 到 32GB的 RAM 是足夠的。

內(nèi)存碎片可以消耗大約 10% 或更多的內(nèi)存。像 innodb_buffer_pool_size、key_buffer_size、query_cache_size 等緩存和緩沖區(qū)要消耗大約 80% 的已分配內(nèi)存。

日常維護(hù)

定期檢查慢的查詢?nèi)罩静?yōu)化查詢機(jī)制以有效使用緩存來減少磁盤 I/O。優(yōu)化它們，以掃描最少的行數(shù)，而不是進(jìn)行全表掃描。

其他可以幫助 DBA 檢查和分析性能的日志包括：錯誤日志、常規(guī)查詢?nèi)罩尽⒍M(jìn)制日志、DDL日志(元數(shù)據(jù)日志)。

定期刷新緩存和緩沖區(qū)以降低碎片化。使用 OPTIMIZE TABLE 語句重新組織表并壓縮任何可能被浪費(fèi)的空間。