1. 商業需求對性能的影響

這里我們就拿一個看上去很簡單的功能來分析一下。

需求：一個論壇帖子總量的統計

附加要求：實時更新

在很多人看來，這個功能非常容易實現，不就是執行一條SELECT COUNT(*)的Query 就可以得到結果了么？是的，確實只需要如此簡單的一個Query 就可以得到結果。但是，如果我們采用不是MyISAM 存儲引擎，而是使用的Innodb 的存儲引擎，那么大家可以試想一下，如果存放帖子的表中已經有上千萬的帖

子的時候，執行這條Query 語句需要多少成本？恐怕再好的硬件設備，恐怕都不可能在10 秒之內完成一次查詢吧。如果我們的訪問量再大一點，還有人覺得這是一件簡單的事情么？

既然這樣查詢不行，那我們是不是該專門為這個功能建一個表，就只有一個字段，一條記錄，就存放這個統計量，每次有新的帖子產生的時候，都將這個值增加1，這樣我們每次都只需要查詢這個表就可以得到結果了，這個效率肯定能夠滿足要求了。確實，查詢效率肯定能夠滿足要求，可是如果我們的系統帖子產生很快，在高峰時期可能每秒就有幾十甚至上百個帖子新增操作的時候，恐怕這個統計表又要成為大家的噩夢了。要么因為并發的問題造成統計結果的不準確，要么因為鎖資源爭用嚴重造成整體性能的大幅度下降。

其實這里問題的焦點不應該是實現這個功能的技術細節，而是在于這個功能的附加要求“實時更新”上面。當一個論壇的帖子數量很大了之后，到底有多少人會關注這個統計數據是否是實時變化的？有多少人在乎這個數據在短時間內的不精確性？我想恐怕不會有人會傻傻的盯著這個統計數字并追究當自己發了一個帖子然后回頭刷新頁面發現這個統計數字沒有加1 吧？即使明明白白的告訴用戶這個統計數據是每過多長時間段更新一次，那有怎樣？難道會有很多用戶就此很不爽么？

只要去掉了這個“實時更新”的附加條件，我們就可以非常容易的實現這個功能了。就像之前所提到的那樣，通過創建一個統計表，然后通過一個定時任務每隔一定時間段去更新一次里面的統計值，這樣既可以解決統計值查詢的效率問題，又可以保證不影響新發貼的效率，一舉兩得。

實際上，在我們應用的系統中還有很多很多類似的功能點可以優化。如某些場合的列表頁面參與列表的數據量達到一個數量級之后，完全可以不用準確的顯示這個列表總共有多少條信息，總共分了多少頁，而只需要一個大概的估計值或者一個時間段之前的統計值。這樣就省略了我們的分頁程序需要在分以前實時COUNT 出滿足條件的記錄數。

其實，在很多應用系統中，實時和準實時，精確與基本準確，在很多地方所帶來的性能消耗可能是幾個性能的差別。在系統性能優化中，應該盡量分析出那些可以不實時和不完全精確的地方，作出一些相應的調整，可能會給大家帶來意想不到的巨大性能提升。

2. 系統架構及實現對性能的影響

實際上，以下幾類數據都是不適合在數據庫中存放的：

1. 二進制多媒體數據

將二進制多媒體數據存放在數據庫中，一個問題是數據庫空間資源耗用非常嚴重，另一個問題是這些數據的存儲很消耗數據庫主機的CPU 資源。這種數據主要包括圖片，音頻、視頻和其他一些相關的二進制文件。這些數據的處理本不是數據的優勢，如果我們硬要將他們塞入數據庫，肯定會造成數據庫的處理資源消耗嚴重。

2. 流水隊列數據

我們都知道，數據庫為了保證事務的安全性（支持事務的存儲引擎）以及可恢復性，都是需要記錄所有變更的日志信息的。而流水隊列數據的用途就決定了存放這種數據的表中的數據會不斷的被INSERT，UPDATE 和DELETE，而每一個操作都會生成與之對應的日志信息。在MySQL 中，如果是支持事務的存儲引擎，這個日志的產生量更是要翻倍。而如果我們通過一些成熟的第三方隊列軟件來實現這個Queue 數據的處理功能，性能將會成倍的提升。

3. 超大文本數據

對于5.0.3 之前的MySQL 版本，VARCHAR 類型的數據最長只能存放255 個字節，如果需要存儲更長的文本數據到一個字段，我們就必須使用TEXT 類型（最大可存放64KB）的字段，甚至是更大的LONGTEXT 類型（最大4GB）。而TEXT 類型數據的處理性能要遠比VARCHAR 類型數據的處理性能低下很多。從5.0.3 版本開始，VARCHAR 類型的最大長度被調整到64KB 了，但是當實際數據小于255Bytes 的時候，實際存儲空間和實際的數據長度一樣，可一旦長度超過255 Bytes 之后，所占用的存儲空間就是實際數據長度的兩倍。所以，超大文本數據存放在數據庫中不僅會帶來性能低下的問題，還會帶來空間占用的浪費問題。

舉一下什么樣的數據適合通過Cache 技術來提高系統性能：

1. 系統各種配置及規則數據；

由于這些配置信息變動的頻率非常低，訪問概率又很高，所以非常適合存使用Cache；

2. 活躍用戶的基本信息數據；

雖然我們經常會聽到某某網站的用戶量達到成百上千萬，但是很少有系統的活躍用戶量能夠都達到這個數量級。也很少有用戶每天沒事干去將自己的基本信息改來改去。更為重要的一點是用戶的基本信息在應用系統中的訪問頻率極其頻繁。所以用戶基本信息的Cache，很容易讓整個應用系統的性能出現一個質的提升。

3. 活躍用戶的個性化定制信息數據；

雖然用戶個性化定制的數據從訪問頻率來看，可能并沒有用戶的基本信息那么的頻繁，但相對于系統整體來說，也占了很大的比例，而且變更皮律一樣不會太多。從Ebay 的PayPal 通過MySQL 的Memory 存儲引擎實現用戶個性化定制數據的成功案例我們就能看出對這部分信息進行Cache 的價值了。雖然通過MySQL 的Memory 存儲引擎并不像我們傳統意義層面的Cache 機制，但正是對Cache 技術的合理利用和擴充造就了項目整體的成功。

4. 準實時的統計信息數據；

所謂準實時的統計數據，實際上就是基于時間段的統計數據。這種數據不會實時更新，也很少需要增量更新，只有當達到重新Build 該統計數據的時候需要做一次全量更新操作。雖然這種數據即使通過數據庫來讀取效率可能也會比較高，但是執行頻率很高之后，同樣會消耗不少資源。既然數據庫服務器的資源非常珍貴，我們為什么不能放在應用相關的內存Cache 中呢？

5. 其他一些訪問頻繁但變更較少的數據；

出了上面這四種數據之外，在我們面對的各種系統環境中肯定還會有各種各樣的變更較少但是訪問很頻繁的數據。只要合適，我們都可以將對他們的訪問從數據庫移到Cache 中。

我們的數據層實現都是最精簡的嗎？

在我們的示例網站系統中，現在要實現每個用戶查看各自相冊列表（假設每個列表顯示10 張相片）的時候，能夠在相片名稱后面顯示該相片的留言數量。這個需求大家認為應該如何實現呢？我想90%的開發開發工程師會通過如下兩步來實現該需求：

1、通過“SELECT id,subject,url FROM photo WHERE user_id = ? limit 10” 得到第一頁的相片相關信息；

2、通過第1 步結果集中的10 個相片id 循環運行十次“SELECT COUNT(*) FROM photo_comment WHERE photh_id = ?” 來得到每張相冊的回復數量然后再瓶裝展現對象。

此外可能還有部分人想到了如下的方案：

1、和上面完全一樣的操作步驟；

2、通過程序拼裝上面得到的10 個photo 的id，再通過in 查詢“SELECT photo_id,count(*) FROM

photo_comment WHERE photo_id in (?) GROUP BY photo_id” 一次得到10 個photo 的所有回復數量，再組裝兩個結果集得到展現對象。

我們來對以上兩個方案做一下簡單的比較：

1、從MySQL 執行的SQL 數量來看，第一種解決方案為11（1+10=11）條SQL 語句，第二種解決方案

為2 條SQL 語句（1+1）;

2、從應用程序與數據庫交互來看，第一種為11 次，第二種為2 次；

3、從數據庫的IO 操作來看，簡單假設每次SQL 為1 個IO，第一種最少11 次IO，第二種小于等于11次IO，而且只有當數據非常之離散的情況下才會需要11 次；

4、從數據庫處理的查詢復雜度來看，第一種為兩類很簡單的查詢，第二種有一條SQL 語句有GROUPBY 操作，比第一種解決方案增加了了排序分組操作；

5、從應用程序結果集處理來看，第一種11 次結果集的處理，第二中2 次結果集的處理，但是第二種解決方案中第二詞結果處理數量是第一次的10 倍；

6、從應用程序數據處理來看，第二種比第一種多了一個拼裝photo_id 的過程。

我們先從以上6 點來做一個性能消耗的分析：

1、由于MySQL 對客戶端每次提交的SQL 不管是相同還是不同，都需要進行完全解析，這個動作主要消耗的資源是數據庫主機的CPU，那么這里第一種方案和第二種方案消耗CPU 的比例是11:2。SQL 語句的解析動作在整個SQL 語句執行過程中的整體消耗的CPU 比例是較多的;

2、應用程序與數據庫交互所消耗的資源基本上都在網絡方面，同樣也是11：2；

3、數據庫IO 操作資源消耗為小于或者等于1：1；

4、第二種解決方案需要比第一種多消耗內存資源進行排序分組操作，由于數據量不大，多出的消耗在語句整體消耗中占用比例會比較小，大概不會超過20%，大家可以針對性測試；

5、結果集處理次數也為11：2，但是第二中解決方案第二次處理數量較大，整體來說兩次的性能消耗區別不大；

6、應用程序數據處理方面所多出的這個photo_id 的拼裝所消耗的資源是非常小的，甚至比應用程序與MySQL 做一次簡單的交互所消耗的資源還要少。

綜合上面的這6 點比較，我們可以很容易得出結論，從整體資源消耗來看，第二中方案會遠遠優于第一種解決方案。而在實際開發過程中，我們的程序員卻很少選用。主要原因其實有兩個，一個是第二種方案在程序代碼實現方面可能會比第一種方案略為復雜，尤其是在當前編程環境中面向對象思想的普及，開發工程師可能會更習慣于以對象為中心的思考方式來解決問題。還有一個原因就是我們的程序員可能對SQL 語句的使用并不是特別的熟悉，并不一定能夠想到第二條SQL 語句所實現的功能。對于第一個原因，我們可能只能通過加強開發工程師的性能優化意識來讓大家能夠自覺糾正，而第二個原因的解決就正是需要我們出馬的時候了。SQL 語句正是我們的專長，定期對開發工程師進行一些相應的數據庫知

識包括SQL 語句方面的優化培訓，可能會給大家帶來意想不到的收獲的。

過度依賴數據庫SQL 語句的功能造成數據庫操作效率低下

案例：在群組簡介頁面需要顯示群名稱和簡介，每個群成員的nick_name，以及群主的個人簽名信息。

需求中所需信息存放在以下四個表中：user，user_PRofile，groups，user_group

我們先看看最簡單的實現方法，一條SQL 語句搞定所有事情：

當然我們也可以通過如下稍微復雜一點的方法分兩步搞定：

首先取得所有需要展示的group 的相關信息和所有群組員的nick_name 信息和組員類別：

然后在程序中通過上面結果集中的user_type 找到群主的user_id 再到user_profile 表中取得群主的簽名信息：

SELECT sign FROM user_profile WHERE user_id = ?

大家應該能夠看出兩者的區別吧，兩種解決方案最大的區別在于交互次數和SQL 復雜度。而帶來的實際影響是第一種解決方案對user_profile 表有不必要的訪問（非群主的profile 信息），造成IO 訪問的直接增加在20%左右。而大家都知道，IO 操作在數據庫應用系統中是非常昂貴的資源。尤其是當這個

功能的PV 較大的時候，第一種方案造成的IO 損失是相當大的。

重復執行相同的SQL 造成資源浪費

我曾經在一個性能優化項目中遇到過一個案例，某個功能頁面一側是“分組”列表，是一列“分組”的名字。頁面主要內容則是該“分組”的所有“項目”列表。每個“項目”以名稱（或者圖標）顯示，同時還有一個SEO 相關的需求就是每個“項目”名稱的鏈接地址中是需要有“分組”的名稱的。所以在“項目”列表的每個“項目”的展示內容中就需要得到該項目所屬的組的名稱。按照開發工程師開發思路，非常容易產生取得所有“項目”結果集并映射成相應對象之后，再從對象集中獲取“項目”所屬組的標識字段，然后循環到“分組”表中取得需要的”組名“。然后再將拼裝成展示對象。

看到這里，我想大家應該已經知道這里存在的一個最大的問題就是多次重復執行了完全相同的SQL得到完全相同的內容。同時還犯了前面第一個案例中所犯的錯誤?；蛟S大家看到之后會不相信有這樣的案例存在，我可以非?？隙ǖ母嬖V大家，事實就是這樣。同時也請大家如果有條件的話，好好Review 自己所在的系統的代碼，非常有可能同樣存在上面類似的情形。

還有部分解決方案要遠優于上面的做法，那就是不循環去取了，而是通過Join 一次完成，也就是解決了第一個案例所描述的性能問題。但是又誤入了類似于第二個案例所描述的陷阱中了，因為實際上他只需要一次查詢就可以得到所有“項目”所屬的“分組”的名稱（所有項目都是同一個組的）。

當然，也有部分解決方案也避免了第二個案例的問題，分為兩條SQL，兩步完成了這個需求。這樣在性能上面基本上也將近是數量級的提升了。

但是這就是性能最優的解決方案了么？不是的，我們甚至可以連一次都不需要訪問就獲得所需要的“分組”名稱。首先，側欄中的“分組”列表是需要有名稱的，我們為什么不能直接利用到呢？

上面還僅僅只是列舉了我們平時比較常見的一些實現差異對性能所帶來的影響，除了這些實現方面所帶來的問題之外，應用系統的整體架構實現設計對系統性能的影響可能會更嚴重。下面大概列舉了一些較為常見的架構設計實現不當帶來的性能問題和資源浪費情況。

1、Cache 系統的不合理利用導致Cache 命中率低下造成數據庫訪問量的增加，同時也浪費了Cache系統的硬件資源投入；

2、過度依賴面向對象思想，對系統

3、對可擴展性的過渡追求，促使系統設計的時候將對象拆得過于離散，造成系統中大量的復雜Join語句，而MySQL Server 在各數據庫系統中的主要優勢在于處理簡單邏輯的查詢，這與其鎖定的機制也有較大關系；

4、對數據庫的過渡依賴，將大量更適合存放于文件系統中的數據存入了數據庫中，造成數據庫資源的浪費，影響到系統的整體性能，如各種日志信息；

5、過度理想化系統的用戶體驗，使大量非核心業務消耗過多的資源，如大量不需要實時更新的數據做了實時統計計算。

3. Query 語句對系統性能的影響

為什么返回完全相同結果集的不同SQL 語句，在執行性能方面存在差異呢？這里我們先從SQL 語句在數據庫中執行并獲取所需數據這個過程來做一個大概的分析了。

當MySQL Server 的連接線程接收到Client 端發送過來的SQL 請求之后，會經過一系列的分解Parse，進行相應的分析。然后，MySQL 會通過查詢優化器模塊（Optimizer）根據該SQL 所設涉及到的數據表的相關統計信息進行計算分析，然后再得出一個MySQL 認為最合理最優化的數據訪問方式，也就是我們常說的“執行計劃”，然后再根據所得到的執行計劃通過調用存儲引擎借口來獲取相應數據。然后再將存儲引擎返回的數據進行相關處理，并以Client 端所要求的格式作為結果集返回給Client 端的應用程序。

注：這里所說的統計數據，是我們通過ANALYZE TABLE 命令通知MySQL 對表的相關數據做分析之后所獲得到的一些數據統計量。這些統計數據對MySQL 優化器而言是非常重要的，優化器所生成的執行計劃的好壞，主要就是由這些統計數據所決定的。實際上，在其他一些數據庫管理軟件中也有類似相應的統

計數據。

對于唯一一個SQL 語句來說，經過MySQL Parse 之后分解的結構都是固定的，只要統計信息穩定，其執行計劃基本上都是比較固定的。而不同寫法的SQL 語句，經過MySQL Parse 之后分解的結構結構就可能完全不同，即使優化器使用完全一樣的統計信息來進行優化，最后所得出的執行計劃也可能完全不一

樣。而執行計劃又是決定一個SQL 語句最終的資源消耗量的主要因素。所以，實現功能完全一樣的SQL 語句，在性能上面可能會有差別巨大的性能消耗。當然，如果功能一樣，而且經過MySQL 的優化器優化之后的執行計劃也完全一致的不同SQL 語句在資源消耗方面可能就相差很小了。當然這里所指的消耗主要

是IO 資源的消耗，并不包括CPU 的消耗。

下面我們將通過一兩個具體的示例來分析寫法不一樣而功能完全相同的兩條SQL 的在性能方面的差異。

示例一

需求：取出某個group（假設id 為100）下的用戶編號（id），用戶昵稱（nick_name）、用戶性別（ sexuality ） 、用戶簽名（ sign ） 和用戶生日（ birthday ） ， 并按照加入組的時間（user_group.gmt_create）來進行倒序排列，取出前20 個。

解決方案一、

SELECT id,nick_name

FROM user,user_group

WHERE user_group.group_id = 1

and user_group.user_id = user.id

limit 100,20;

解決方案二、

SELECT user.id,user.nick_name

FROM (SELECT user_id

FROM user_group

WHERE user_group.group_id = 1

ORDER BY gmt_create desc

limit 100,20) t,user

WHERE t.user_id = user.id;

我們先來看看執行計劃：

sky@localhost : example 10:32:13> explain

-> SELECT id,nick_name

-> FROM user,user_group

-> WHERE user_group.group_id = 1

-> and user_group.user_id = user.id

-> ORDER BY user_group.gmt_create desc

-> limit 100,20/G

*************************** 1. row ************************

id: 1

select_type: SIMPLE

table: user_group

type: ref

possible_keys: user_group_uid_gid_ind,user_group_gid_ind

key: user_group_gid_ind

key_len: 4

ref: const

rows: 31156

Extra: Using where; Using filesort

*************************** 2. row ***********************

id: 1

select_type: SIMPLE

table: user

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: example.user_group.user_id

rows: 1

Extra:

sky@localhost : example 10:32:20> explain

-> SELECT user.id,user.nick_name

-> FROM (

-> SELECT user_id

-> FROM user_group

-> WHERE user_group.group_id = 1

-> ORDER BY gmt_create desc

-> limit 100,20) t,user

-> WHERE t.user_id = user.id/G

*************************** 1. row ************************

id: 1

select_type: PRIMARY

table: <derived2>

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 20

Extra:

*************************** 2. row ***********************

id: 1

select_type: PRIMARY

table: user

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: t.user_id

rows: 1

Extra:

*************************** 3. row ***********************

id: 2

select_type: DERIVED

table: user_group

type: ref

possible_keys: user_group_gid_ind

key: user_group_gid_ind

key_len: 4

ref: const

rows: 31156

Extra: Using filesort

執行計劃對比分析：

解決方案一中的執行計劃顯示MySQL 在對兩個參與Join 的表都利用到了索引，user_group 表利用了user_group_gid_ind 索引（ key: user_group_gid_ind ） ， user 表利用到了主鍵索引（ key:PRIMARY），在參與Join 前MySQL 通過Where 過濾后的結果集與user 表進行Join，最后通過排序取出Join 后結果的“limit 100,20”條結果返回。

解決方案二的SQL 語句利用到了子查詢，所以執行計劃會稍微復雜一些，首先可以看到兩個表都和解決方案1 一樣都利用到了索引（所使用的索引也完全一樣），執行計劃顯示該子查詢以user_group 為驅動，也就是先通過user_group 進行過濾并馬上進行這一論的結果集排序，也就取得了SQL 中的

“limit 100,20”條結果，然后與user 表進行Join，得到相應的數據。這里可能有人會懷疑在自查詢中從user_group表所取得與user 表參與Join的記錄條數并不是20 條，而是整個group_id=1 的所有結果。

那么清大家看看該執行計劃中的第一行，該行內容就充分說明了在外層查詢中的所有的20 條記錄全部被返回。

通過比較兩個解決方案的執行計劃，我們可以看到第一中解決方案中需要和user 表參與Join 的記錄數MySQL 通過統計數據估算出來是31156，也就是通過user_group 表返回的所有滿足group_id=1 的記錄數（系統中的實際數據是20000）。而第二種解決方案的執行計劃中，user 表參與Join 的數據就只有20條，兩者相差很大，通過本節最初的分析，我們認為第二中解決方案應該明顯優于第一種解決方案。

下面我們通過對比兩個解決覺方案的SQL 實際執行的profile 詳細信息，來驗證我們上面的判斷。由于SQL 語句執行所消耗的最大兩部分資源就是IO和CPU，所以這里為了節約篇幅，僅列出BLOCK IO 和CPU兩項profile 信息（Query Profiler 的詳細介紹將在后面章節中獨立介紹）：

先打開profiling 功能，然后分別執行兩個解決方案的SQL 語句：

sky@localhost : example 10:46:43> set profiling = 1;

Query OK, 0 rows affected (0.00 sec)

sky@localhost : example 10:46:50> SELECT id,nick_name

-> FROM user,user_group

-> WHERE user_group.group_id = 1

-> and user_group.user_id = user.id

-> ORDER BY user_group.gmt_create desc

-> limit 100,20;

+--------+-----------+

| id | nick_name |

+--------+-----------+

| 990101 | 990101 |

| 990102 | 990102 |

| 990103 | 990103 |

| 990104 | 990104 |

| 990105 | 990105 |

| 990106 | 990106 |

| 990107 | 990107 |

| 990108 | 990108 |

| 990109 | 990109 |

| 990110 | 990110 |

| 990111 | 990111 |

| 990112 | 990112 |

| 990113 | 990113 |

| 990114 | 990114 |

| 990115 | 990115 |

| 990116 | 990116 |

| 990117 | 990117 |

| 990118 | 990118 |

| 990119 | 990119 |

| 990120 | 990120 |

+--------+-----------+

20 rows in set (1.02 sec)

sky@localhost : example 10:46:58> SELECT user.id,user.nick_name

-> FROM (

-> SELECT user_id

-> FROM user_group

-> WHERE user_group.group_id = 1

-> ORDER BY gmt_create desc

-> limit 100,20) t,user

-> WHERE t.user_id = user.id;

+--------+-----------+

| id | nick_name |

+--------+-----------+

| 990101 | 990101 |

| 990102 | 990102 |

| 990103 | 990103 |

| 990104 | 990104 |

| 990105 | 990105 |

| 990106 | 990106 |

| 990107 | 990107 |

| 990108 | 990108 |

| 990109 | 990109 |

| 990110 | 990110 |

| 990111 | 990111 |

| 990112 | 990112 |

| 990113 | 990113 |

| 990114 | 990114 |

| 990115 | 990115 |

| 990116 | 990116 |

| 990117 | 990117 |

| 990118 | 990118 |

| 990119 | 990119 |

| 990120 | 990120 |

+--------+-----------+

20 rows in set (0.96 sec)

查看系統中的profile 信息，剛剛執行的兩個SQL 語句的執行profile 信息已經記錄下來了：

sky@localhost : example 10:47:07> show profiles/G

*************************** 1. row ************************

Query_ID: 1

Duration: 1.02367600

Query: SELECT id,nick_name

FROM user,user_group

WHERE user_group.group_id = 1

and user_group.user_id = user.id

ORDER BY user_group.gmt_create desc

limit 100,20

*************************** 2. row ************************

Query_ID: 2

Duration: 0.96327800

Query: SELECT user.id,user.nick_name

FROM (

SELECT user_id

FROM user_group

WHERE user_group.group_id = 1

ORDER BY gmt_create desc

limit 100,20) t,user

WHERE t.user_id = user.id

2 rows in set (0.00 sec)

sky@localhost : example 10:47:34> SHOW profile CPU,BLOCK IO io FOR query 1;

sky@localhost : example 10:47:40> SHOW profile CPU,BLOCK IO io FOR query 2;

我們先看看兩條SQL 執行中的IO 消耗，兩者區別就在于“Sorting result”，我們回顧一下前面執行計劃的對比，兩個解決方案的排序過濾數據的時機不一樣，排序后需要取得的數據量一個是20000，一個是20，正好和這里的profile 信息吻合，第一種解決方案的“Sorting result”的IO 值是第二種解決方案的將近500 倍。然后再來看看CPU 消耗，所有消耗中，消耗最大的也是“Sorting result”這一項，第一個消耗多出的緣由和上面IO 消耗差異是一樣的。

結論：

通過上面兩條功能完全相同的SQL 語句的執行計劃分析，以及通過實際執行后的profile 數據的驗證，都證明了第二種解決方案優于第一種解決方案。同時通過后者的實際驗證，也再次證明了我們前面所做的執行計劃基本決定了SQL 語句性能。

4. Schema 設計對系統的性能影響

所以這里暫時先不介紹如何來設計性能優異的數據庫Schema 結構，僅僅通過一個實際的示例來展示Schema 結構的不一樣在性能方面所帶來的差異。

需求概述：一個簡單的討論區系統，需要有用戶，用戶組，組討論區這三部分基本功能

簡要分析：

1、需要存放用戶數據的表；

2、需要存放分組信息和存放用戶與組關系的表

3、需要存放討論信息的表；

解決方案：

原始方案一：分別用用四個表來存放用戶，分組，用戶與組關系以及各組的討論帖子的信息如下：

user 用戶表：

groups 分組表：

user_group 關系表：

group_message 討論組帖子表：

優化后方案二：

user 用戶表：

user_profile 用戶屬性表（記錄與user 一一對應）：

groups 和user_group 這兩個表和方案一完全一樣

group_message 討論組帖子表：

group_message_content 帖子內容表（記錄與group_message 一一對應）：

我們先來比較一下兩個解決方案所設計的Schema 的區別。區別主要體現在兩點，一個區別是在group_message 表中增加了author 字段來存放發帖作者的昵稱，與user 表的nick_name 相對應，另外一個就是第二個解決方案將user 表和group_message 表都分拆成了兩個表，關系分別都是一一對應。

方案二看上去比方案一要更復雜一些，首先是表的數量多了2 個，然后是在group_message 中冗余存放了作者昵稱。我們試想一下，一個討論區系統，訪問最多的頁面會是什么？我想大家都會很清楚是帖子標題列表頁面。而帖子標題列表頁面最主要的信息就是都是來自group_message 表中，同時帖子標題

后面的作者一般都是通過用戶名成（昵稱）來展示。按照第一種解決方案來設計的Schema，我們就需要執行類似如下這樣的SQL 語句來得到數據：

SELECT t.id, t.subject,user.id, u.nick_name

FROM (SELECT id, user_id, subject

FROM group_message

WHERE group_id = ?

ORDER BY gmt_modified DESC LIMIT 20

) t, user u

WHERE t.user_id = u.id

但是第二中解決方案所需要執行的SQL 就會簡單很多，如下：

SELECT t.id, t.subject, t.user_id, t.author

FROM group_message

WHERE group_id = ?

ORDER BY gmt_modified DESC 

LIMIT 20

兩個SQL 相比較，大家都能很明顯的看出誰優誰劣了，第一個是需要讀取兩個表的數據進行Join，與第二個SQL 相比性能差距很大，尤其是如果第一個再寫的差一點，性能更是非常糟糕，兩者所帶來的資源消耗就更相差玄虛了。

不僅僅如此，由于第一個方案中的group_message 表中還包含一個大字段“content”，該字段所存放的信息要占整個表的絕大部分存儲空間，但在這條系統中執行最頻繁的SQL 之一中是完全不需要該字段所存放信息的，但是由于這個SQL 又沒辦法做到不訪問group_message 表的數據，所以第一條SQL 在數據讀取過程中會需要讀取大量沒有任何意義的數據。

在系統中用戶數據的讀取也是比較頻繁的，但是大多數地方所需要的用戶數據都只是用戶的幾個基本屬性，如用戶的id，昵稱，密碼，狀態，郵箱等，所以將用戶表的這幾個屬性單獨分離出來后，也會讓大量的SQL 語句在運行的時候減少數據的檢索量，從而提高性能。

可能有人會覺得，在我們將一個表分成兩個表的時候，我們如果要訪問被分拆出去的信息的時候，性能不是就會變差了嗎？是的，對于那些需要訪問如user 的sign，msn 等原來只需要一個表就可以完成的SQL 來說，現在都需要兩條SQL 來完成，性能確實會有所降低，但是由于兩個表都是一對一的關聯關

系，關聯字段的過濾性也非常高，而且這樣的查詢需求在整個系統中所占有的比例也并不高，所以這里所帶來的性能損失實際上要遠遠小于在其他SQL 上所節省出來的資源，所以完全不必為此擔心

5. 硬件環境對系統性能的影響

首先，數據庫主機是存取數據的地方，那么其IO 操作自然不會少，所以數據庫主機的IO 性能肯定是需要最優先考慮的一個因素，這一點不管是什么類型的數據庫應用都是適用的。不過，這里的IO 性能并不僅僅只是指物理的磁盤IO，而是主機的整體IO 性能，是主機整個IO 系統的總體IO 性能。而IO 性能

本身又可以分為兩類，一類是每秒可提供的IO 訪問次數，也就是我們常說的IOPS 數量，還有一種就是每秒的IO 總流量，也就是我們常說的IO 吞吐量。在主機中決定IO 性能部件主要由磁盤和內存所決定，當然也包括各種與IO 相關的板卡。

其次，由于數據庫主機和普通的應用程序服務器相比，資源要相對集中很多，單臺主機上所需要進行的計算量自然也就比較多，所以數據庫主機的CPU 處理能力也不能忽視。

最后，由于數據庫負責數據的存儲，與各應用程序的交互中傳遞的數據量比其他各類服務器都要多，所以數據庫主機的網絡設備的性能也可能會成為系統的瓶頸。

由于上面這三類部件是影響數據庫主機性能的最主要因素，其他部件成為性能瓶頸的幾率要小很多，所以后面我們通過對各種類型的應用做一個簡單的分析，再針對性的給出這三類部件的基本選型建議。

1、典型OLTP 應用系統

對于各種數據庫系統環境中大家最常見的OLTP 系統，其特點是并發量大，整體數據量比較多，但每次訪問的數據比較少，且訪問的數據比較離散，活躍數據占總體數據的比例不是太大。對于這類系統的數據庫實際上是最難維護，最難以優化的，對主機整體性能要求也是最高的。因為他不僅訪問量很高，數據量也不小。

針對上面的這些特點和分析，我們可以對OLTP 的得出一個大致的方向。

雖然系統總體數據量較大，但是系統活躍數據在數據總量中所占的比例不大，那么我們可以通過擴大內存容量來盡可能多的將活躍數據cache 到內存中；

雖然IO 訪問非常頻繁，但是每次訪問的數據量較少且很離散，那么我們對磁盤存儲的要求是IOPS 表現要很好，吞吐量是次要因素；并發量很高，CPU 每秒所要處理的請求自然也就很多，所以CPU 處理能力需要比較強勁；雖然與客戶端的每次交互的數據量并不是特別大，但是網絡交互非常頻繁，所以主機與客戶端交互的網絡設備對流量能力也要求不能太弱。

2、典型OLAP 應用系統

用于數據分析的OLAP 系統的主要特點就是數據量非常大，并發訪問不多，但每次訪問所需要檢索的數據量都比較多，而且數據訪問相對較為集中，沒有太明顯的活躍數據概念。

基于OLAP 系統的各種特點和相應的分析，針對OLAP 系統硬件優化的大致策略如下：

數據量非常大，所以磁盤存儲系統的單位容量需要盡量大一些；

單次訪問數據量較大，而且訪問數據比較集中，那么對IO 系統的性能要求是需要有盡可能大的每秒IO 吞吐量，所以應該選用每秒吞吐量盡可能大的磁盤；雖然IO 性能要求也比較高，但是并發請求較少，所以CPU 處理能力較難成為性能瓶頸，所以CPU 處理能力沒有太苛刻的要求；

雖然每次請求的訪問量很大，但是執行過程中的數據大都不會返回給客戶端，最終返回給客戶端的數據量都較小，所以和客戶端交互的網絡設備要求并不是太高；

此外，由于OLAP 系統由于其每次運算過程較長，可以很好的并行化，所以一般的OLAP 系統都是由多臺主機構成的一個集群，而集群中主機與主機之間的數據交互量一般來說都是非常大的，所以在集群中主機之間的網絡設備要求很高。

3、除了以上兩個典型應用之外，還有一類比較特殊的應用系統，他們的數據量不是特別大，但是訪問請求及其頻繁，而且大部分是讀請求。可能每秒需要提供上萬甚至幾萬次請求，每次請求都非常簡單，可能大部分都只有一條或者幾條比較小的記錄返回，就比如基于數據庫的DNS 服務就是這樣類型的服務。

雖然數據量小，但是訪問極其頻繁，所以可以通過較大的內存來cache 住大部分的數據，這能夠保證非常高的命中率，磁盤IO 量比較小，所以磁盤也不需要特別高性能的；

并發請求非常頻繁，比需要較強的CPU 處理能力才能處理；

雖然應用與數據庫交互量非常大，但是每次交互數據較少，總體流量雖然也會較大，但是一般來說普通的千兆網卡已經足夠了。

在很多人看來，性能的根本決定因素是硬件性能的好壞。但實際上，硬件性能只能在某些階段對系統性能產生根本性影響。當我們的CPU 處理能力足夠的多，IO 系統的處理能力足夠強的時候，如果我們的應用架構和業務實現不夠優化，一個本來很簡單的實現非得繞很多個彎子來回交互多次，那再強的硬件也沒有用，因為來回的交互總是需要消耗時間所以，在應用系統的硬件配置方面，我們應該要以一個理性的眼光來看待，只有合適的才是最好的。并不是說硬件資源越好，系統性能就一定會越好。而且，硬件系統本身總是有一個擴展極限的，如果我們一味的希望通過升級硬件性能來解決系統的性能問題，那么總有一天將會遇到無法逾越的瓶頸。到那時候，就算有再多的錢去砸也無濟于事了。

通過筆者的經驗，在整個系統的性能優化中，如果按照百分比來劃分上面幾個層面的優化帶來的性能收益，可以得出大概如下的數據：

需求和架構及業務實現優化：55%

Query 語句的優化：30%

數據庫自身的優化：15%