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

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

選擇 InnoDB 作為存儲(chǔ)引擎

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

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

組織數(shù)據(jù)庫(kù)的 schema、表和字段以降低 I/O 的開(kāi)銷，將相關(guān)項(xiàng)保存在一起，并提前規(guī)劃，以便隨著數(shù)據(jù)量的增長(zhǎng)，性能可以保持較高的水平。

設(shè)計(jì)數(shù)據(jù)表應(yīng)盡量使其占用的空間最小化，表的主鍵應(yīng)盡可能短。

對(duì)于 InnoDB 表，主鍵所在的列在每個(gè)輔助索引條目中都是可復(fù)制的，因此如果有很多輔助索引，那么一個(gè)短的主鍵可以節(jié)省大量空間。

僅創(chuàng)建你需要改進(jìn)查詢性能的索引。索引有助于檢索，但是會(huì)增加插入和更新操作的執(zhí)行時(shí)間。

InnoDB 的 Change Buffering 特性

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

InnoDB 頁(yè)面壓縮

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

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

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

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

SQL 語(yǔ)句優(yōu)化

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

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

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

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

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

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

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

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

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

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

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

MySQL 的內(nèi)存分配

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

要考慮的關(guān)鍵領(lǐng)域是：并發(fā)連接 —— 對(duì)于大量并發(fā)連接，排序和臨時(shí)表將需要大量?jī)?nèi)存。在撰寫本文時(shí)，對(duì)于處理 3000+ 并發(fā)連接的數(shù)據(jù)庫(kù)，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ī)制以有效使用緩存來(lái)減少磁盤 I/O。優(yōu)化它們，以掃描最少的行數(shù)，而不是進(jìn)行全表掃描。

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

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

來(lái)源：36大數(shù)據(jù)