MySQL 性能優化之影響性能的因素

轉載於：martin

https://gitbook.cn/books/5ae950b4795eef762711ee63/index.html

既然要優化數據庫，我們就首先要知道，優化的是什麼，或者說：什麼因素影響了數據庫的性能。

影響數據庫因素主要因素總結如下：

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

2. 系統架構（存儲架構）及實現對性能的影響*

3. query語句對數據庫性能的影響*

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

5. 硬件環境對數據庫的性能的影響*

商業需求對性能的影響

不合理需求造成資源投入產出比過低，這裡我們就用一個看上去很簡單的功能來分析。

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

附加要求：實時更新

從功能上來看非常容易實現，執行一條 SELECT COUNT(*) from 表名的 Query 就可以得到結果。但是，如果我們採用不是 MyISAM 存儲引擎，而是使用的 Innodb 的存儲引擎，那麼大家可以試想一下，如果存放帖子的表中已經有上千萬的帖子的時候，執行這條 Query 語句需要多少成本？恐怕再好的硬件設備，都不可能在 10 秒之內完成一次查詢吧。

注：沒有 where 的 count(* ) 使用 MyISAM 要比 InnoDB 快得多。因為 MyISAM 內置了一個計數器，count(* ) 時它直接從計數器中讀，而 InnoDB 必須掃描全表。所以在 InnoDB 上執行 count(*) 時一般要伴隨 where，且 where 中要包含主鍵以外的索引列。

既然這樣查詢不行，那我們是不是該專門為這個功能建一個表，就只有一個字段，一條記錄，就存放這個統計量，每次有新的帖子產生的時候，都將這個值增加 1，這樣我們每次都只需要查詢這個表就可以得到結果了，這個效率肯定能夠滿足要求了。

確實，查詢效率肯定能夠滿足要求，可是如果帖子產生很快，在高峰時期可能每秒就有幾十甚至上百個帖子新增操作的時候，恐怕這個統計表又要成為大家的噩夢了。要麼因為併發的問題造成統計結果的不準確，要麼因為鎖資源爭用嚴重造成整體性能的大幅度下降。

其實這裡問題的焦點不應該是實現這個功能的技術細節，而是在於這個功能的附加要求“實時更新”上面。當一個論壇的帖子數量很大了之後，到底有多少人會關注這個統計數據是否是實時變化的？有多少人在乎這個數據在短時間內的不精確性？恐怕不會有人會盯著這個統計數字並追究當自己發了一個帖子然後回頭刷新頁面發現這個統計數字沒有加 1 吧？所以只要去掉了這個“實時更新”的附加條件，就可以非常容易的實現這個功能了。就像之前所提到的那樣，通過創建一個統計表，然後通過一個定時任務每隔一定時間段去更新一次裡面的統計值，這樣既可以解決統計值查詢的效率問題，又可以保證不影響新發貼的效率，一舉兩得。

系統架構（存儲架構）及實現對性能的影響

所有數據都是適合在數據庫中存放的嗎？數據庫為我們提供了太多的功能，反而讓很多並不是太瞭解數據庫的人，錯誤的使用數據庫中很多並不太擅長、或對性能影響很大的功能，最後卻全部怪罪到數據庫身上。

比如有些數據需要考慮是否存儲在MySQL

1. 流水隊列數據

2. 二進制多媒體數據

3. 超大的文本數據

4. 其它用戶上傳的文件、圖片等資源

是否考慮了緩存

對於 Web 系統或者 APP 應用，是否有大量熱讀數據及無需及時變更的數據，然而這些數據可以考慮緩存起來，提高 MySQL 的性能及節約 DB 資源。比如筆者公司的優惠券列表、廣告列表、配置規則信息等，屬於用戶附表信息，無需頻繁更新，可以利用 Redis 緩存，讓應用跑得更快，用戶體驗更好。

query 語句對數據庫性能的影響

開發人員不能只關注查詢結果不關注查詢過程，比如每個用戶查詢各自相冊列表（假設每個列顯示 10 張相片），能夠在相片後有留言，我們要查看留言的數量。

實現的話有好幾種方案。

方案1：

<code>select id,subject,url from photo where user_id=? limit 10
/<code>

通過第一步的結果循環10次執行

<code>select count(*) from photo_comment where photo_id=？
/<code>

方案2：

第一步和上面是一樣的，第二步通過程序拼裝上面得到的 10 個 photo_id，通過 in 查詢，

<code>select photo_id,count(*) from photo_comment where photo_id in(?)group by photo_id"
/<code>

一次得到 10 個 photo_id 所有的回覆數量。

簡要分析的話，方案2更簡單一些。

也可以根據 explain 執行分析，具體看執行計劃和性能損耗情況（cpu、io 的損耗情況）

<code>mysql>set profiling=1;
mysql>select id,subject,url from photo where user_id=? limit 10;
mysql>select photo_id,count(*) from photo_comment where photo_id in(?)group by photo_id;
mysql>show profiles;
mysql>show profile cpu,block io for query ?;
/<code>

如上系列操作，是分析 SQL 語句的執行計劃和性能損耗情況。

Schema 設計對系統性能影響

論壇帖子案例：假設現在是高併發的一個論壇系統。

你需要考慮高併發的論壇最高的併發在哪裡？可能最高的併發是查看帖子標題列表，現在往往帖子標題後面會跟一個作者的暱稱。 然而根據需求帖子標題（作者暱稱），這裡需要關聯（這裡就需要有一個 join 查詢），但是由於高併發業務儘量避免使用關聯查詢， 儘量走單表查詢，那此時就會在帖子表冗餘作者的暱稱，違反了範式設計，但是卻提高了系統性能和 QPS。

關於 Schema 設計大多秉承的基礎是基於範式設計，然而真實系統中其實個人總結有如下建立：

1. 短小、精簡（字段選型、表列數、char(N)、varchar(N) 等）

2. 字段冗餘

3. 大小字段拆分（text、varchar(255) 等）

4. 單錶行數拆分

最終的目的就是：表小、行小、字段小

硬件環境對數據庫的性能的影響

數據庫是存取數據的地方，所以數據庫主機的 IO 性能肯定是需要最優先考慮的一個因素，這一點不管是什麼類型的數據庫應用都是適用的。在主機中決定 IO 性能部件主要由磁盤和內存所決定，當然也包括各種與 IO 相關的板卡。

如何去選擇具體的硬件，一般要基於業務是 OLTP 還是 OLAP，這樣你才能選擇適合的數據庫或對應的存儲引擎。

• OLTP系統：

  一般併發量大，整體數據量多，每次訪問數據較少，訪問數據比較離散, 有活躍數據並且比例不大。龐大的內存活躍數據可以 Cache，訪問頻繁每次訪問數據少，那麼對磁盤的 IOPS 表現要好，吞吐量是次要的。併發高，CPU 要求高，網絡交互頻繁網絡設備要求較高。

• OLAP系統：

  一般數據統計類，大多選用商業數據庫，不過基於 Oracle 的大多費用昂貴，MySQL 也確實提供的 Inforbright 列式存儲的存儲引擎，其此類業務具有數據量大，併發訪問不多，每次訪問需要檢索的數據都比較多，訪問集中，沒有明顯的活躍數據的特點，需要儘可能大的磁盤吞吐量，併發不多，CPU 要求不高。

總之：要根據自己系統的特性選擇更適合更廉價的硬件設備。

總結

只有瞭解哪些因素會影響數據庫的性能，才能結合業務去逐個優化和提升對應的性能，不管是存儲層面、硬件層面、數據層面等。