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

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

選擇 InnoDB 作為存儲引擎

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

優(yōu)化數據庫結構

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

設計數據表應盡量使其占用的空間最小化，表的主鍵應盡可能短。

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

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

InnoDB 的 Change Buffering 特性

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

InnoDB 頁(yè)面壓縮

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

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

使用批量數據導入

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

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

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

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

減少和隔離需要耗費大量時(shí)間的函數調用。

盡可能的減少查詢(xún)中的全表掃描次數。

調整緩存區域的大小和屬性，比如 InnoDB 緩沖池，MySQL 查詢(xún)緩存等，這樣會(huì )通過(guò)從內存而非從硬盤(pán)獲取數據而讓重復的查詢(xún)變得更快。

優(yōu)化存儲結構

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

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

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

增加 InnoDB 緩沖池大小可以讓查詢(xún)從緩沖池訪(fǎng)問(wèn)而不是通過(guò)磁盤(pán) I/O 訪(fǎng)問(wèn)。通過(guò)調整系統變量 innodb_flush_method 來(lái)調整清除緩沖的指標使其達到較佳水平。

使用其它存儲設備配置 RAID。

MySQL 的內存分配

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

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

內存碎片可以消耗大約 10％ 或更多的內存。像 innodb_buffer_pool_size、key_buffer_size、query_cache_size 等緩存和緩沖區要消耗大約 80％ 的已分配內存。

日常維護

定期檢查慢的查詢(xún)日志并優(yōu)化查詢(xún)機制以有效使用緩存來(lái)減少磁盤(pán) I/O。優(yōu)化它們，以?huà)呙枳钌俚男袛?，而不是進(jìn)行全表掃描。

其他可以幫助 DBA 檢查和分析性能的日志包括：錯誤日志、常規查詢(xún)日志、二進(jìn)制日志、DDL日志（元數據日志）。

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