一、數據庫優化的必要性

1、避免網站頁面出現訪問錯誤

1）、數據庫連接timeout產生頁面5xx錯誤

這個問題也是最直觀的問題，頁面上出現錯誤，在應用層面找開發的同學來排查，開發同學發現應用層面代碼沒有問題，最后檢查發現是因為數據庫超時引起的。那數據庫層面為什么會超時呢？這里的原因可能有很多，比如數據庫連接池已經滿了，或者查詢的數據量比較大，引起數據庫線程的掛死，既然這些問題產生于數據庫層面，那么就需要對數據庫進行調優。

2）、慢查詢造成頁面無法加載

最典型的就是用戶點開頁面后屏幕一片白，為什么？說明你的數據沒有加載進來，比如說電商系統，你點開了這個商品列表，在5秒之內還沒有打開，那么這個時候你的系統基本上已經廢了，這系統已經到了不得不優化的程度了。5秒鐘，這個時間太夸張了，其實用戶的極限基本上在1秒之內，系統數據超時達到了5秒，那肯定是頁面一片白。這個原因說白了就是你數據查詢比較慢，或者說你的表里的數據量很大，可能有上百萬，或者千萬的數據。如果開發的同學當時還忘記了在條件列加索引，那這簡直就是一種災難，別說5分鐘了，半個小時都有可能出不來。

3）、阻塞造成數據無法提交

那這也是有可能的，用戶在頁面上比如提交一個付款的動作，那么你的后臺如果線程繁忙引起阻塞，那這時候的阻塞基本上就是鎖表之內的了。

其實我們優化數據庫的時候。不應該等他出現問題的時候才想起去優化，我們應該在數據庫設計之初就應該考慮的到各種問題出現的可能性，做好防范。等到數據庫出現問題的時候再去優化就有點嫌晚了，因為這時候優化成本就大了。

2、增加數據庫的穩定性

1）、很多數據庫問題都是由于低效的查詢引起的。

那么什么叫低效呢？低效最直接的體現就是你查詢時候進行全表掃描，說白了就是沒有經過索引，那么出現這個問題有可能是開發人員在開發的時候忘記加索引了。

系統可能在上線初期不會出現問題，為什么呢？因為你的用戶量比較少，低并發，所以不會發現問題。但是當你的系統跑了2-3年之后，你的用戶量暴增，這時候就會出現問題。比如你的公司已經成長為一個小獨角獸了，用戶量達到了上百萬，那么這時候問題可能就來了，因為并發量上來了，并發上來之后對數據庫資源的爭搶就開始了。所以開發人員可能在設計表字段的初期沒有預測到這個字段將來會作為條件查詢，所以就沒有在這個字段上加索引?；蛘吣仉m然加了索引，由于隨著時間的推移，這個字段的重復率可能越來越高，那么索引的威力就大大減弱了。

2）、隨著時間的推移，系統變得極其臃腫，數據庫中的數據量越來越大，數據檢索越來越困難，對整個系統帶來的資源消耗也就越來越大，系統越發不穩定。

這個問題其實剛講到的，系統上線之后初期可能不會發生問題，但是隨著你的業務量不斷的膨脹，你的數據庫必然會產生問題。所以數據庫的調優工作是個長期的工作，現在各大互聯網公司都有自己的dba，對數據庫做24小時監控。

而且我們發現其實系統絕大部分性能問題都是出在數據庫層面。因為公司的數據實際上是公司的核心資產，說白了你那些項目代碼弄丟了其實都無所謂。但是呢，你數據庫里的數據萬萬不能丟的，所以對數據庫的維護優化應該作為一個頭等大事。

3、優化用戶體驗

1）、流暢的頁面訪問速度

這個也是我們開發人員所追求的一個目標。就是說系統訪問頁面要非常的流暢，你不能動不動一個查詢就是3-5秒鐘，那這樣的話你的客戶就得跑光了。比如說電商系統，我在你家買東西，每次點頁面，他都給我3-5秒的響應，那客戶肯定會奔潰的，必然拋棄你去其他家去買了，因為你的系統太爛了。所以我們在應用層面頁面都是做成靜態頁面，通過nginx+redis緩存來做了，熱點數據直接就不走數據庫了。

2）、良好的網站功能體驗

這個也非常重要，如果說你的網站里面的功能動不動就不能用，動不動就一片白，那這種用戶體驗是非常差的。基本上用戶來幾次就跑光了，我們的目標是把訪問頁面控制在1秒之內，其實1秒都嫌多啦，像我們公司，優化的目標在500ms-600ms，這是我們的終極目標，1s是最極限的容忍程度。

二、mysql數據庫優化層面

1、圖解優化層面

2、商業需求

1）、不合理需求造成資源投入產出比過低

需求是否合理很多時候可能并不是很容易界定，尤其是作為技術人員來說，可能更難以確定一個需求的合理性。即使指出，也不一定會被產品經理們認可。那作為技術人員的我們怎么來證明一個需求是否合理呢？

第一、每次產品經理們提出新的項目（或者功能需求）的時候，應該要求他們同時給出該項目的預期收益的量化指標，以備項目上先后統計評估投入產出比率； 第二、在每次項目進行過程中，應該詳細記錄所有的資源投入，包括人力投入，硬件設施的投入，以及其他任何項目相關的資源投入； 第三、項目（或者功能需求）上線之后應該及時通過收集相關數據統計出項目的實際收益值，以便計算投入產出比率的時候使用； 第四、技術部門應該盡可能推動設計出一個項目（或者功能需求）的投入產出比率的計算規則。在項目上線一段時間之后，通過項目實際收益的統計數據和項目的投入資源量，計算出整個項目的實際投入產出值，并公布給所有參與項目的部門知曉，同時存放以備后查。

有了實際的投入產出比率，我們就可以和項目立項之初產品經理們的預期投入產出比率做出比較，判定出這個項目做的是否值得。而且當積累了較多的項目投入產出比率之后，我們可以根據歷史數據分析出一個項目合理的投入產出比率應該是多少。這樣，在項目立項之初，我們就可以判定出產品經理們的預期投入產出比率是否合理，項目是否真的有進行的必要。

有了實際的投入產出比率之后，我們還可以拿出數據給老板們看，讓他知道功能并不是越多越好，讓他知道有些功能是應該撤下來的，即使撤下該功能可能需要投入不少資源。實際上，一般來說，在產品開發及運營部門內部都會做上面所說的這些事情的。但很多時候可能更多只是一種形式化的過程。在有些比較規范的公司可能也完成了上面的大部分流程，但是要么數據不公開，要么公開給其他部門的數據存在一定的偏差，不具備真實性。為什么會樣？其實就一個原因，就是部門之間的利益沖突及業績沖突問題。產品經理們總是希望盡可能的讓用戶覺得自己設計的產品功能齊全，讓老板覺得自己做了很多事情。但是從來都不會去關心因為做一個功能所帶來的成本投入，或者說是不會特別的關心這一點。而且很多時候他們也并不能太理解技術方面帶來的復雜度給產品本身帶來的負面影響。

2）、無用功能堆積使系統過度復雜影響整體性能

很多時候，為系統增加某個功能可能并不需要花費太多的成本，而要想將一個已經運行了一段時間的功能從原有系統中撤下來卻是非常困難的。

首先，對于開發部門，可能要重新整理很多的代碼，找出可能存在與增加該功能所編寫的代碼有交集的其他功能點，刪除沒有關聯的代碼，修改有關聯的代碼；

其次，對于測試部門，由于功能的變動，必須要回歸測試所有相關的功能點是否正常?？赡苡捎诮缍ɡщy，不得不將回歸范圍擴展到很大，測試工作量也很大，如果你有自動化測試可能還會好一點。

最后，所有與撤除下線某個功能相關的工作參與者來說，又無法帶來任何實質性的收益，而恰恰相反的是，帶來的只可能是風險。

由于上面的這幾個因素，可能很少有公司能夠有很完善的項目（或者功能）下線機制，也很少有公司能做到及時將系統中某些不合適的功能下線。

所以，我們所面對的應用系統可能總是越來越復雜，越來越龐大，短期內的復雜可能并無太大問題，但是隨著時間的積累，我們所面對的系統就會變得極其臃腫。不僅維護困難，性能也會越來越差。尤其是有些并不合理的功能，在設計之初或者是剛上線的時候由于數據量較小，帶來不了多少性能損耗?？呻S著時間的推移，數據庫中的數據量越來越大，數據檢索越來越困難，對整個系統帶來的資源消耗也就越來越大。而且，由于系統復雜度的不斷增加，給后續其他功能的開發帶來實現的復雜度，可能很多本來很簡單的功能，因為系統的復雜而不得不增加很多的邏輯判斷，造成系統應用程序的計算量不斷增加，本身性能就會受到影響。而如果這些邏輯判斷還需要與數據庫交互通過持久化的數據來完成的話，所帶來的性能損失就更大，對整個系統的性能影響也就更大了。

3、系統架構

1）、數據庫中存放的數據都是適合在數據庫中存放的嗎？

對于有些開發人員來說，數據庫就是一個操作最方便的萬能存儲中心，希望什么數據都存放在數據庫中，不論是需要持久化的數據，還是臨時存放的過程數據，不論是普通的純文本格式的字符數據，還是多媒體的二進制數據，都喜歡全部塞進數據庫中。因為對于應用服務器來說，數據庫很多時候都是一個集中式的存儲環境，不像應用服務器那樣可能有很多臺；而且數據庫有專門的 DBA 去幫忙維護，而不像應用服務器很多時候還需要開發人員去做一些維護；還有一點很關鍵的就是數據庫的操作非常簡單統一，不像文件操作或者其他類型的存儲方式那么復雜。

其實我個人認為，現在的很多數據庫為我們提供了太多的功能，反而讓很多并不是太了解數據庫的人錯誤的使用了數據庫的很多并不是太擅長或者對性能影響很大的功能，最后卻全部怪罪到數據庫身上。

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

• 二進制多媒體數據 將二進制多媒體數據存放在數據庫中，一個問題是數據庫空間資源耗用非常嚴重，另一個問題是這些數據的存儲很消耗數據庫主機的CPU資源。這種數據主要包括圖片，音頻、視頻和其他一些相關的二進制文件。這些數據的處理本不是數據的優勢，如果我們硬要將他們塞入數據庫，肯定會造成數據庫的處理資源消耗嚴重。
• 流水隊列數據 我們都知道，數據庫為了保證事務的安全性（支持事務的存儲引擎）以及可恢復性，都是需要記錄所有變更的日志信息的。而流水隊列數據的用途就決定了存放這種數據的表中的數據會不斷的被 INSERT， UPDATE 和 DELETE，而每一個操作都會生成與之對應的日志信息。在 MySQL 中，如果是支持事務的存儲引擎，這個日志的產生量更是要翻倍。而如果我們通過一些成熟的第三方隊列軟件（例如rabbitmq，rocketmq，kafka等）來實現這個Queue數據的處理功能，性能將會成倍的提升。
• 超大文本數據 對于 5.0.3 之前的 MySQL 版本， VARCHAR 類型的數據最長只能存放 255 個字節，如果需要存儲更長的文本數據到一個字段，我們就必須使用 TEXT 類型（最大可存放 64KB）的字段，甚至是更大的LONGTEXT 類型（最大 4GB）。而TEXT類型數據的處理性能要遠比 VARCHAR 類型數據的處理性能低下很多。從 5.0.3 版本開始， VARCHAR 類型的最大長度被調整到 64KB 了，但是當實際數據小于 255Bytes 的時候，實際存儲空間和實際的數據長度一樣，可一旦長度超過 255 Bytes 之后，所占用的存儲空間就是實際數據長度的兩倍。所以，超大文本數據存放在數據庫中不僅會帶來性能低下的問題，還會帶來空間占用的浪費問題。

2）、是否合理的利用了應用層 Cache 機制？

對于 Web 應用，活躍數據的數據量總是不會特別的大，有些活躍數據更是很少變化。對于這類數據，我們是否有必要每次需要的時候都到數據庫中去查詢呢？如果我們能夠將變化相對較少的部分活躍數據通過應用層的Cache機制Cache 到內存中，對性能的提升肯定是成數量級的，而且由于是活躍數據，對系統整體的性能影響也會很大。

當然，通過 Cache 機制成功的案例數不勝數，但是失敗的案例也同樣并不少見。如何合理的通過Cache 技術讓系統性能得到較大的提升也不是通過寥寥幾筆就能說明的清楚，這里我僅根據以往的經驗列舉一下什么樣的數據適合通過 Cache 技術來提高系統性能：

• 系統各種配置及規則數據； 由于這些配置信息變動的頻率非常低，訪問概率又很高，所以非常適合存使用 Cache；
• 活躍用戶的基本信息數據； 雖然我們經常會聽到某某網站的用戶量達到成百上千萬，但是很少有系統的活躍用戶量能夠都達到這個數量級。也很少有用戶每天沒事干去將自己的基本信息改來改去。更為重要的一點是用戶的基本信息在應用系統中的訪問頻率極其頻繁。所以用戶基本信息的 Cache，很容易讓整個應用系統的性能出現一個質的提升。
• 活躍用戶的個性化定制信息數據； 雖然用戶個性化定制的數據從訪問頻率來看，可能并沒有用戶的基本信息那么的頻繁，但相對于系統整體來說，也占了很大的比例，而且變更頻率一樣不會太多?，F在普遍使用nosql的組件，例如用redis作為熱點數據的存儲引擎實現用戶個性化定制數據，我們就能看出對這部分信息進行Cache 的價值了，Cache 技術的合理利用和擴充造就了項目整體的成功。
• 準實時的統計信息數據； 所謂準實時的統計數據，實際上就是基于時間段的統計數據。這種數據不會實時更新，也很少需要增量更新，只有當達到重新 Build 該統計數據的時候需要做一次全量更新操作。雖然這種數據即使通過數據庫來讀取效率可能也會比較高，但是執行頻率很高之后，同樣會消耗不少資源。既然數據庫服務器的資源非常珍貴，我們為什么不能放在應用相關的內存 Cache 中呢？
• 其他一些訪問頻繁但變更較少的數據； 除了上面這四種數據之外，在我們面對的各種系統環境中肯定還會有各種各樣的變更較少但是訪問很頻繁的數據。只要合適，我們都可以將對他們的訪問從數據庫移到Cache中。

3）、數據層實現都是最精簡的嗎？

以往的經驗來看，一個合理的數據存取實現和一個拙劣的實現相比，在性能方面的差異經常會超出一個甚至幾個數量級。

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

• 通過“SELECT id,subject,url FROM photo WHERE user_id = ? limit 10” 得到第一頁的相片相關信息；
• 通過第 1 步結果集中的 10 個相片 id 循環運行十次“ SELECT COUNT(*) FROM photo_comment WHERE photh_id = ?” 來得到每張相冊的回復數量然后再拼裝展現對象。

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

• 和上面完全一樣的操作步驟；
• 通過程序拼裝上面得到的 10 個 photo 的 id，再通過 in 查詢“SELECT photo_id,count(*) FROM photo_comment WHERE photo_id in (?) GROUP BY photo_id” 一次得到 10 個 photo 的所有回復數量，再組裝兩個結果集得到展現對象。

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

• 從 MySQL 執行的 SQL 數量來看 ，第一種解決方案為11（1+10=11）條SQL語句，第二種解決方案 為2條SQL語句（1+1）;
• 從應用程序與數據庫交互來看，第一種為11次，第二種為2次；
• 從數據庫的IO操作來看，簡單假設每次SQL為1個IO，第一種最少11次IO，第二種小于等于11次IO，而且只有當數據非常之離散的情況下才會需要11次；
• 從數據庫處理的查詢復雜度來看，第一種為兩類很簡單的查詢，第二種有一條SQL語句有GROUP BY 操作，比第一種解決方案增加了排序分組操作；
• 從應用程序結果集處理來看，第一種11次結果集的處理，第二種2次結果集的處理，但是第二種解決方案中第二次結果處理數量是第一次的10倍；
• 從應用程序數據處理來看，第二種比第一種多了一個拼裝photo_id 的過程。

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

• 由于MySQL對客戶端每次提交的 SQL 不管是相同還是不同，都需要進行完全解析，這個動作主要消耗的資源是數據庫主機的CPU，那么這里第一種方案和第二種方案消耗CPU的比例是11:2。SQL語句的解析動作在整個 SQL 語句執行過程中的整體消耗的CPU比例是較多的;
• 應用程序與數據庫交互所消耗的資源基本上都在網絡方面，同樣也是11：2；
• 數據庫 IO 操作資源消耗為小于或者等于1：1；
• 第二種解決方案需要比第一種多消耗內存資源進行排序分組操作，由于數據量不大，多出的消耗在語句整體消耗中占用比例會比較小，大概不會超過 20%，大家可以針對性測試；
• 結果集處理次數也為11：2，但是第二種解決方案第二次處理數量較大，整體來說兩次的性能消耗區別不大；
• 應用程序數據處理方面所多出的這個photo_id的拼裝所消耗的資源是非常小的，甚至比應用程序與MySQL做一次簡單的交互所消耗的資源還要少。

綜合上面的這6點比較，我們可以很容易得出結論，從整體資源消耗來看，第二種方案會遠遠優于第一種解決方案。而在實際開發過程中，我們的程序員卻很少選用。主要原因其實有兩個，一個是第二種方案在程序代碼實現方面可能會比第一種方案略為復雜，尤其是在當前編程環境中面向對象思想的普及，開發工程師可能會更習慣于以對象為中心的思考方式來解決問題。還有一個原因就是我們的程序員同學可能對SQL語句的使用并不是特別的熟悉，并不一定能夠想到第二條SQL 語句所實現的功能。對于第一個原因，我們可能只能通過加強開發工程師的性能優化意識來讓大家能夠自覺糾正，而第二個原因的解決就正是需要我們這個專題的重點。SQL語句的調優正是我們必須要具備的，定期對初級開發工程師進行一些相應的數據庫知識包括SQL語句方面的優化培訓，可能會給大家帶來意想不到的收獲的。這里我們還僅僅只是通過一個很長見的簡單示例來說明數據層架構實現的區別對整體性能的影響，實際上可以簡單的歸結為過渡依賴嵌套循環的使用或者說是過渡弱化 SQL 語句的功能造成性能消耗過多的實例。后面我將進一步分析一下更多的因為架構實現差異所帶來的性能消耗差異。

4、SQL及索引優化

這個主題將是我們研究的重點，我們從以下兩個方面來闡述。

1）、根據需求寫出良好的SQL，并創建有效的索引，實現某一種需求可以多種寫法，我們就要選擇一種效率最高的寫法，這個時候就要了解sql優化。

下面我們將通過一兩個具體的示例來分析寫法不一樣而功能完全相同的兩條 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;

我們先來看看執行計劃：

mysql> 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,20G
************************* 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: 31156Extra: 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:

mysql> 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.idG
*************************** 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條，兩者相差很大，通過本節最初的分析，我們認為第二中解決方案應該明顯優于第一種解決方案。

2）、sql優化的目的之一就是減少中間結果集，降低物理IO

例如：如何優化select t1.id,t2.name from t1,t2 where t1.pid=t2.id;

以這條sql語句為例我們來看一下他的執行流程

• from語句把t1表 和 t2表從數據庫文件加載到內存中。
• 這時候相當于對兩張表做了乘法運算，把t1表中的每一行記錄按照順序和t2表中記錄依次匹配。
• 匹配完成后，我們得到了一張有 （t1表中記錄數 × t2表中記錄數）條的臨時表。 在內存中形成的臨時表稱為‘笛卡爾積表’。

針對以上的理論，我們提出一個問題，難道表連接的時候都要先形成一張笛卡爾積表嗎？如果兩張表的數據量都比較大的話，那樣就會占用很大的內存空間這顯然是不合理的。所以，我們在進行表連接查詢的時候一般都會使用JOIN xxx ON xxx的語法，ON語句的執行是在JOIN語句之前的，也就是說兩張表數據行之間進行匹配的時候，會先判斷數據行是否符合ON語句后面的條件，再決定是否JOIN。

因此，有一個顯而易見的SQL優化的方案是，當兩張表的數據量比較大，又需要連接查詢時，應該使用 FROM table1 JOIN table2 ON xxx的語法，避免使用 FROM table1,table2 WHERE xxx 的語法，因為后者會在內存中先生成一張數據量比較大的笛卡爾積表，增加了內存的開銷。

5、數據庫表結構優化

1）、根據數據庫的范式，設計表結構，表結構設計的好壞直接關系到SQL語句的復雜度

正常情況之下我們都會依據數據庫設計范式來設計數據庫。針對一般的系統我們會設計到第三范式就差不多了，最多到BC范式。

那如果對系統分類特別嚴格的，我們一般是先判斷當前系統是OLTP系統還是OLAP系統，如果是OLAP系統的話，那么查詢語句會相對比較多，那我們在設計表的時候就會適當的進行數據冗余，可以設計到第二范式，這樣設計的結果就是“少表多字段”，這樣人為的設計成冗余字段的表在查詢數據的時候就減少了多表聯表查詢，從而減少了中間的結果集，本質上就是減少了IO，提高了查詢效率。這是典型的以空間換時間的案例。

那如果是OLTP系統，那么增，刪，改操作比較多，那我們可以設計成“多表少字段”，將表盡量拆分，此類系統一般不需要索引或者只要少量索引，因為如果索引多了會影響效率，因為做增，刪，改操作的時候，對應字段的索引頁需要被維護（索引會自行進行裂變等消耗資源的操作）。

2）、適當的將表進行拆分，原本需要做join的查詢只需要一張單表查詢就可以了

這就是上面提到的OLAP系統的情況。這里不再贅述。

6、系統配置優化

大多數運行在Linux機器上，如tcp連接數的限制、打開文件數的限制、安全性的限制，因此我們要對這些配置進行相應的優化。

7、硬件配置優化

1）、數據庫主機的IO性能是需要最優先考慮的一個因素

2）、數據庫主機和普通的應用程序服務器相比，資源要相對集中很多，單臺主機上所需要進行的計算量自然也就比較多，所以數據庫主機的CPU處理能力也是一個重要的因素

3）、數據庫主機的網絡設備（一般指網卡等）的性能也可能會成為系統的瓶頸

三、SQL及索引優化

要進行sql優化，我們得先安裝mysql。

1、mysql安裝（linux在線安裝）

1）、訪問：
https://dev.mysql.com/downloads/repo/yum/

我的操作系統是centos7，所以我選擇如下的版本，大家可以根據自己的系統版本選擇對應的版本來安裝。

2）、點擊Download，選擇最下面的No thanks，just start my download，如下圖：

3）、安裝過程

• 先移除mariadb數據庫：yum remove mariadb-libs.x86_64
• 創建mysql目錄：mkdir /etc/mysql cd /etc/mysql
• 下載安裝wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
• 添加到本地yum localinstall mysql80-community-release-el7-3.noarch.rpm
• 正式安裝yum install mysql-community-server
• 啟動測試service mysqld start service mysqld status
• 查看默認密碼并且登錄查看默認密碼：cat /var/log/mysqld.log | grep password 使用默認密碼登陸：mysql -uroot -p
• 修改密碼set global validate_password.policy=0; set global validate_password.length=1; ALTER USER “root”@”localhost” IDENTIFIED BY “123456”; ##新密碼為123456
• 退出exit
• 輸入密碼即可登錄mysql -uroot -p

2、數據庫版本選擇

1）查看數據庫的版本

執行命令

select @@version;

注意：這里是2個@符號

我們用的是當前最新的mysql8.0.20版本。

2）準備數據

我們要演示的案例的sql腳本來源于官網的sakila這個數據庫，這是一個影片租售商店的系統數據庫，大概10幾張表，下面帶大家下載并導入。

訪問mysql官網。

示例數據：
https://dev.mysql.com/doc/sakila/en/sakila-installation.html

具體的下載操作請同學們觀看視頻，這里不再贅述。

3）導入數據

參照官網：
https://dev.mysql.com/doc/sakila/en/sakila-installation.html

官網上每一步都有具體說明，同學們可以參考官網說明一步一步操作；或者跟著我的視頻操作也是可以的。

4）表結構關系

這里有個簡單的表結構關系，這是pdman導出的物理模型。

我在視頻講解中也帶大家用pd導出了整個sakila庫的表物理模型，通過物理模型，我們可以對表與表之間的關系有一個清晰的了解。

為什么要帶大家做這個導出物理模型的事情呢？因為導出物理模型在開發中還是很常見的，比如我們接手了一個老的項目，整個項目的相關文檔早就缺失了，當年做這個項目的同學也早就跳槽了，那么我們要維護這樣的一個什么資料都沒有，也沒有人可以咨詢的這樣的一個遺留系統的話，我們該如何去理清它的業務邏輯呢？這時候物理模型就能派上用場，導出來之后我們可以一目了然的看到表與表之間的關系，通過這層關系有助于我們理解整個系統的業務邏輯。

3、問題SQL篩查步驟

1）檢查慢查日志是否開啟：

執行如下命令，查看是否開啟：OFF為關閉，ON為開啟

show slow_query_log

2）檢查慢查日志路徑：

執行如下命令查看日志路徑

show variables like‘%slow_query_log%';

3）開啟慢查日志：

執行如下命令開啟慢查日志

set global slow_query_log=on;

4）慢查日志判斷標準（默認查詢時間大于10s的sql語句）：

show variables like 'long_query_time';

為了測試方便可以修改為1秒

set global  long_query_time=1;

5）慢查日志測試：

執行如下sql，休眠11秒，超過10秒，迫使該sql進慢查日志

select sleep(11);

執行

tail -f /var/lib/mysql/myshop02-slow.log

檢查慢查日志記錄情況,發現已經記錄進來了。

6）為了測試方便，所有查詢都記錄進慢查日志：

先檢查下使用索引情況

show variables like'%log%';

設置開啟即可

set global log_queries_not_using_indexes=on;

4、Jmeter壓測mysql

打開jmeter，加入相關測試配置項，具體操作請觀看視頻

5、MySQL慢查日志的存儲格式解析

具體的詳細解析請觀看視頻。

四、mysql慢查詢日志分析工具

1、mysqldumpslow

1）、簡介：

? 如果開啟了慢查詢日志，就會生成大量的數據，然后我們就可以通 過對日志的分析，生成分析報表，通過報表進行優化。

2）、用法：

執行 mysqldumpslow –help 查看詳細用法

注意：在mysql數據庫所在的服務器上，而不是在mysql>命令行中

3）、 執行結果：

mysqldumpslow --help

具體參數講解請觀看視頻

2、mysqldumpslow用法示例

查看慢查詢日志的前10條記錄

mysqldumpslow -t 10 /var/lib/mysql/myshop02-slow.log

mysqldumpslow 分析的結果如下：

日志顯示：select sleep(N)語句共執行了4次，共花費了36s，平均9s/次，鎖表時間0.00s，共返回行rows=4，平均1行。

更詳細的解釋請觀看視頻

3、mysqldumpslow優缺點

這個工具是最常用的工具，通過安裝mysql進行附帶安裝，但是該工具統計的結果比較少，對我們的優化所提供的信息還是比較少，比如cpu，io等信息都沒有，所以我們需要更強大的工具，就是我們下節要講的

pt-query-digest。

4、pt-query-digest

1）、簡介

pt-query-digest是用于分析mysql慢查詢的一個第三方工具，它可以分析binlog、General log、slowlog，也可以通過SHOWPROCESSLIST或者通過tcpdump抓取的MySQL協議數據來進行分析。

可以把分析結果輸出到文件中，分析過程是先對查詢語句的條件進行參數化，然后對參數化以后的查詢進行分組統計，統計出各查詢的執行時間、次數、占比等，可以借助分析結果找出問題進行優化。

2）、pt-query-digest本質是perl腳本，所以首先安裝perl模塊

執行如下命令安裝perl

 yum install -y perl-CPAN perl-Time-HiRes

?

3）、快速安裝

執行wget命令，wget下載后進行本地安裝，執行yum localinstall -y，命令如下

wget https://www.percona.com/downloads/percona-toolkit/3.2.0/binary/redhat/7/x86_64/percona-toolkit-3.2.0-1.el7.x86_64.rpm && yum localinstall –y percona-toolkit-3.2.0-1.el7.x86_64.rpm

4）檢查是否安裝完成

執行

pt-query-digest --help

如出現下圖信息表示已經安裝成功。

5、pt-query-digest常用命令詳解

1）查看服務器信息

命令：

pt-summary

可以看到服務器相關的具體信息：有系統日期，主機，更新時間，內核，平臺，進程數等。

具體信息詳細解釋請觀看視頻

2）查看磁盤開銷使用信息

命令：

pt-diskstats

可以看到磁盤開銷相關的具體信息：磁盤使用率，繁忙度等信息。

具體信息詳細解釋請觀看視頻

3）查看mysql數據庫信息

命令：

pt-mysql-summary --user=root --password=123456

可以看到mysql相關的具體信息：有系統時間，數據庫實例，msyql服務路徑，主從信息等信息。

具體信息詳細解釋請觀看視頻

4）分析慢查詢日志(重點)

命令：

pt-query-digest /var/lib/mysql/myshop02-slow.log

這個統計信息內容有很多，以總-分的形式統計了慢查詢日志的分析信息，有執行總時間，最小時間，最大時間，平均時間，達到95%的時間是多少，標準時間，中位時間（也就是從小到大排列，排在中間的值）等詳細信息，該日志非常重要，是我們判斷慢查詢的重要依據。

具體信息的剖析我在視頻課程里都有詳細的講解。

5）、查找mysql的從庫和同步狀態

命令：

pt-slave-find --host=localhost --user=root --password=123456

這個命令通常DBA同學會用到，這是做完集群之后，查看主從之間的同步狀態的。

6）、查看mysql的死鎖信息

命令：

pt-deadlock-logger --run-time=10 --interval=3 --create-dest-table --dest D=test,t=deadlocks u=root,p=123456

這個命令開發的同學經常會用，通常會用來進行故障排查，當懷疑系統發生死鎖的時候或者已經發生死鎖的時候就可以執行這個命令進行查看。

具體死鎖演示和日志信息解釋見視頻課程

7）、從慢查詢日志中分析索引使用情況

命令：

pt-index-usage --user=root --password=123456 --host=localhost   /var/lib/mysql/myshop02-slow.log

這里日志顯示，我對test庫的t2表做了刪除索引“idx_t2_id”這個動作。

8）、從慢查找數據庫表中重復的索引

命令：

pt-duplicate-key-checker --host=localhost --user=root --password=123456

日志顯示，我對test庫的t3表做了刪除索引“idx_idc_name_id”后，又新增了這個索引。

最后三行顯示了重復索引相關信息。

9）、查看mysql表和文件的當前活動IO開銷（不要在高峰時用）

命令：

pt-ioprofile

當沒有大量IO的時候，查詢不到信息，因為沒有IO開銷

10）、查看不同mysql配置文件的差異（集群常用，雙方都生效的變量）

命令：

pt-config-diff /etc/my.cnf /root/my_master.cnf

這個通常也是用在集群中，主從復制或者出現問題排查，主機和從機差異的時候經常用到。

具體演示可以參見視頻，這里不再贅述。

11）、pt-find查找mysql表和執行命令，示例如下：

• 查找數據庫里大于1M的表

pt-find --user=root --password=123456 --tablesize +1M

這里顯示有4張表的大小超過1M，這個命令通常可以用來決定當表膨脹之后，比如達到百萬級，這時候就需要判斷是否要對表做水平或者垂直拆分，那么判斷標準就可以看表的大小。

• 查看表和索引大小并排序

pt-find --user=root --password=123456 --printf "%Tt%D.%Nn" | sort -rn

可以看到都已經從大到小進行排列了，最大的是test庫t1表。

12）、pt-kill 殺掉符合標準的mysql進程，示例如下：

• 顯示查詢時間大于3秒的查詢

pt-kill --user=root --password=123456 --busy-time 3 --print

這個命令只打印信息，不kill進程，如需要強殺進程可執行下面的命令

• kill掉大于3秒的查詢

pt-kill --user=root --password=123456 --busy-time 3 --kill

13）、查看mysql授權（集群常用，授權復制），示例如下：

pt-show-grants --user=root --password=123456

pt-show-grants --user=root --password=123456 --separate --revoke

可以看到大量的授權信息，這個命令主要也是用在集群的時候，在從機上需要和主機上進行一模一樣的授權，直接可以通過這個命令獲取所有的授權，然后復制過去就ok了。

14）、驗證數據庫復制的完整性（集群常用，主從復制后檢驗），示例如下：

pt-table-checksum --user=root --password=123456

這個信息主要查看點是ERRORS和DIFFS的信息，如果不為0就要注意了，檢查主從復制的時候是不是出現了問題。

五、利用pt-query-digest利器查找三大類有問題的SQL

1、查詢次數多且每次查詢占用時間長的sql

通常為pt-query-digest分析的前幾個查詢，該工具可以很清楚的看出每個SQL執行的次數及百分比等信息，執行的次數多，占比比較大的SQL

具體的排查分析講解請觀看視頻

2、IO大的sql

注意pt-query-digest分析中的Rows examine項，掃描的行數越多，IO越大。

Rows examine項的具體講解在日志分析的視頻課程中，請自行查看

3、未命中索引的SQL

pt-query-digest分析中的Rows examine 和Rows Send的對比。說明該SQL的索引命中率不高，對于這種SQL，我們要重點進行關注。

Rows examine 和Rows Send的關系和區別在視頻中有詳細講解。

六、通過explain分析SQL執行計劃

1、使用explain查詢SQL的執行計劃

SQL的執行計劃反映出了SQL的執行效率，在執行的SQL前面加上explain即可

2、執行計劃的字段解釋與舉例

以下列出了常見的字段解釋，具體舉例請觀看視頻

1）、id列

數字越大越先執行，如果數字一樣大，那么就從上往下依次執行，id列為null就表示這是一個結果集，不需要使用它來進行查詢。

2）、select_type列

• simple：表示不需要union操作或者不包含子查詢的簡單select查詢，有連接查詢時，外層的查詢為simple，且只有一個。
• primary：一個需要union操作或者含有子查詢的select，位于最外層的查詢，select_type即為primary，且只有一個。
• union：union連接的兩個select查詢，第一個查詢是dervied派生表，除了第一個表外，第二個以后的表select_type都是union。
• union result：包含union的結果集，在union和union all語句中,因為它不需要參與查詢，所以id字段為null。
• dependent union：與union一樣，出現在union 或union all語句中，但是這個查詢要受到外部查詢的影響。
• subquery：除了from子句中包含的子查詢外，其他地方出現的子查詢都可能是subquery。
• dependent subquery：與dependent union類似，表示這個subquery的查詢要受到外部表查詢的影響。
• derived：from子句中出現的子查詢，也叫做派生表，其他數據庫中可能叫做內聯視圖或嵌套select。
• materialization :物化通過將子查詢結果作為一個臨時表來加快查詢執行速度，正常來說是常駐內存，下次查詢會再次引用臨時表。

3）、table列

顯示的查詢表名，如果查詢使用了別名，那么這里顯示的是別名，如果不涉及對數據表的操作，那么這顯示為null，如果顯示為尖括號括起來的就表示這個是臨時表，后邊的N就是執行計劃中的id，表示結果來自于這個查詢產生。如果是尖括號括起來的，與類似，也是一個臨時表，表示這個結果來自于union查詢的id為M,N的結果集。

4）、type列

• system：表中只有一行數據或者是空表，且只能用于myisam和memory表，如果是Innodb引擎表，type列在這個情況通常都是all或者index。
• const：使用唯一索引或者主鍵，返回記錄一定是1行記錄的等值where條件時，通常type是const，其他數據庫也叫做唯一索引掃描。
• eq_ref：出現在要連接多個表的查詢計劃中，驅動表循環獲取數據，這行數據是第二個表的主鍵或者唯一索引，作為條件查詢只返回一條數據，且必須為not null，唯一索引和主鍵是多列時，只有所有的列都用作比較時才會出現eq_ref。
• ref：不像eq_ref那樣要求連接順序，也沒有主鍵和唯一索引的要求，只要使用相等條件檢索時就可能出現，常見與輔助索引的等值查找或者多列主鍵、唯一索引中，使用第一個列之外的列作為等值查找也會出現，總之，返回數據不唯一的等值查找就可能出現。
• fulltext：全文索引檢索，全文索引的優先級很高，若全文索引和普通索引同時存在時，mysql不管代價，優先選擇使用全文索引。
• ref_or_null：與ref方法類似，只是增加了null值的比較，實際用的不多。
• unique_subquery：用于where中的in形式子查詢，子查詢返回不重復值唯一值。
• index_subquery：用于in形式子查詢使用到了輔助索引或者in常數列表，子查詢可能返回重復值，可以使用索引將子查詢去重。
• range：索引范圍掃描，常見于使用>,<,is null,between ,in ,like等運算符的查詢中。
• index_merge：表示查詢使用了兩個以上的索引，最后取交集或者并集，常見and ，or的條件使用了不同的索引，官方排序這個在ref_or_null之后，但是實際上由于要讀取多個索引，性能可能大部分時間都不如range。
• index：索引全表掃描，把索引從頭到尾掃一遍，常見于使用索引列就可以處理不需要讀取數據文件的查詢、可以使用索引排序或者分組的查詢。
• all：這個就是全表掃描數據文件，然后再在server層進行過濾返回符合要求的記錄。

type列總結：

依次性能從好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引。一般來說，好的sql查詢至少達到range級別，最好能達到ref。

5）、possible_keys列

查詢可能使用到的索引。

6）、key列

查詢真正使用到的索引，select_type為index_merge時，這里可能出現兩個以上的索引，其他的select_type這里只會出現一個。

7）、key_len列

用于處理查詢的索引長度，如果是單列索引，那就是整個索引長度，如果是多列索引，那么查詢不一定都能使用到所有的列，具體使用到了多少個列的索引，這里就會計算進去，沒有使用到的列不會計算進去。留意下這個列的值，算一下你的多列索引總長度就知道有沒有使用到所有的列了。另外，key_len只計算where條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到key_len中。

8）、ref列

如果是使用的常數等值查詢，這里會顯示const，如果是連接查詢，被驅動表的執行計劃這里會顯示驅動表的關聯字段，如果是條件使用了表達式或者函數，或者條件列發生了內部隱式轉換，這里可能顯示為func

9）、rows列

這里是執行計劃中估算的掃描行數，不是精確值。

10）、extra列

• no tables used：不帶from字句的查詢或者From dual查詢。
• NULL：查詢的列未被索引覆蓋，并且where篩選條件是索引的前導列，意味著用到了索引，但是部分字段未被索引覆蓋，必須通過“回表”來實現，不是純粹地用到了索引，也不是完全沒用到索引。
• using index：查詢時不需要回表查詢，直接通過索引就可以獲取查詢的數據。
• Using where：查詢的列未被索引覆蓋，where篩選條件非索引的前導列。
• Using where Using index：查詢的列被索引覆蓋，并且where篩選條件是索引列之一但是不是索引的前導列，意味著無法直接通過索引查找來查詢到符合條件的數據。
• Using index condition：與Using where類似，查詢的列不完全被索引覆蓋，where條件中是一個前導列的范圍。
• using temporary：表示使用了臨時表存儲中間結果。臨時表可以是內存臨時表和磁盤臨時表，執行計劃中看不出來，需要查看status變量，used_tmp_table，used_tmp_disk_table才能看出來。
• using filesort：mysql 會對結果使用一個外部索引排序，而不是按索引次序從表里讀取行。此時mysql會根據聯接類型瀏覽所有符合條件的記錄，并保存排序關鍵字和行指針，然后排序關鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優化的。
• using intersect：表示使用and的各個索引的條件時，該信息表示是從處理結果獲取交集。
• using union：表示使用or連接各個使用索引的條件時，該信息表示從處理結果獲取并集。
• using sort_union和using sort_intersection：用and和or查詢信息量大時，先查詢主鍵，然后進行排序合并后返回結果集。
• firstmatch(tb_name)：5.6.x開始引入的優化子查詢的新特性之一，常見于where字句含有in()類型的子查詢。如果內表的數據量比較大，就可能出現這個。
• loosescan(m..n)：5.6.x之后引入的優化子查詢的新特性之一，在in()類型的子查詢中，子查詢返回的可能有重復記錄時，就可能出現這個。

11）、filtered列

使用explain extended時會出現這個列，5.7之后的版本默認就有這個字段，不需要使用explain extended了。這個字段表示存儲引擎返回的數據在server層過濾后，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。

七、慢查詢優化思路及案例

1、慢查詢的總體優化思路

• 優化更需要優化的SQL
• 定位優化對象的性能瓶頸
• 明確的優化目標
• 從explain執行計劃入手
• 永遠用小結果集驅動大的結果集
• 盡可能在索引中完成排序
• 只取出自己需要的列，不要用select *
• 僅使用最有效的過濾條件
• 盡可能避免復雜的join和子查詢
• 小心使用order by，group by，distinct語句
• 合理設計并利用索引

以上優化思路的具體說明請觀看視頻

2、永遠用小結果集驅動大的結果集（join操作表小于百萬級別）

1）、驅動表的定義

當進行多表連接查詢時， [驅動表] 的定義為：

• 指定了聯接條件時，滿足查詢條件的記錄行數少的表為[驅動表]
• 未指定聯接條件時，行數少的表為[驅動表]

2）、mysql關聯查詢的概念

MySQL 表關聯的算法是 Nest Loop Join，是通過驅動表的結果集作為循環基礎數據，然后一條一條地通過該結果集中的數據作為過濾條件到下一個表中查詢數據，最后合并結果。

3）、left join，right join，inner join的區別

具體說明見視頻

• left joinselect * from t2 left join t3 on t2.id =t3.id and t3.id in(1,2,3) order by t2.id desc;

• right joinselect * from t2 right join t3 on t2.id =t3.id and t3.id in(1,2,3);

• inner joinselect * from t2 inner join t3 on t2.id =t3.id and t3.id in(1,2,3);

3、join的實現原理

具體的原理講解請觀看視頻

1）、mysql只支持一種join算法：

Nested-Loop Join（嵌套循環連接），但Nested-Loop Join有三種變種：

• Simple Nested-Loop Join（簡單嵌套循環）
• Index Nested-Loop Join（索引嵌套循環）
• Block Nested-Loop Join（塊嵌套循環）

2）、Simple Nested-Loop Join（簡單嵌套循環）

3）、Index Nested-Loop Join（索引嵌套循環）

4）、Block Nested-Loop Join（塊嵌套循環）

5）、Block Nested-Loop Join（3表）

4、join的優化思路

具體的思路講解見視頻

1）、盡可能減少join語句中的Nested Loop的循環總次數

2）、優先優化Nested Loop的內層循環

3）、保證join語句中被驅動表上join條件字段已經被索引

4）、無法保證被驅動表的 Join 條件字段被索引且內存資源充足的前提下，不要太吝惜join Buffer的設置

5、join的優化思路總結

1）、并發量太高的時候，系統整體性能可能會急劇下降。

2）、復雜的 Join 語句，所需要鎖定的資源也就越多，所阻塞的其他線程也就越多

3）、復雜的 Query 語句分拆成多個較為簡單的 Query 語句分步執行

6、只取出需要的列，不要用select *

1）、如果取出的列過多，則傳輸給客戶端的數據量必然很大，浪費帶寬

2）、若在排序的時候輸出過多的列，則會浪費內存（Using filesort）

3）、若在排序的時候輸出過多的列，還有可能改變執行計劃

具體講解及演示見視頻

7、僅使用最有效的過濾條件

1）、Where字句中條件越多越好嗎？

2）、若在多種條件下都使用了索引，那如何選擇？

3）、最終選擇方案：key_len的長度決定使用哪個條件

具體講解及演示見視頻

8、盡可能在索引中完成排序

• order by 字句中的字段加索引（掃描索引即可，內存中完成，邏輯io）。
• 若不加索引的話會可能會啟用一個臨時文件輔助排序（落盤，物理io）。

具體講解及演示見視頻

9、order by排序原理及優化思路

• order by排序可利用索引進行優化，order by子句中只要是索引的前導列都可以使索引生效，可以直接在索引中排序，不需要在額外的內存或者文件中排序。
• 不能利用索引避免額外排序的情況，例如：排序字段中有多個索引，排序順序和索引鍵順序不一致（非前導列）

具體講解及演示見視頻

10、order by排序算法

MySQL對于不能利用索引避免排序的SQL，數據庫不得不自己實現排序功能以滿足用戶需求，此時SQL的執行計劃中會出現“Using filesort”，這里需要注意的是filesort并不意味著就是文件排序，其實也有可能是內存排序，這個主要由sort_buffer_size參數與結果集大小確定。MySQL內部實現排序主要有3種方式，常規排序，優化排序和優先隊列排序，主要涉及3種排序算法：快速排序、歸并排序和堆排序

1）、order by常規排序算法

步驟：

• 從表t1中獲取滿足WHERE條件的記錄。
• 對于每條記錄，將記錄的主鍵+排序鍵(id,col2)取出放入sort buffer。
• 如果sort buffer可以存放所有滿足條件的(id,col2)對，則進行排序；否則sort buffer滿后，進行排序并固化到臨時文件中。(排序算法采用的是快速排序算法)
• 若排序中產生了臨時文件，需要利用歸并排序算法，保證臨時文件中記錄是有序的。
• 循環執行上述過程，直到所有滿足條件的記錄全部參與排序。
• 掃描排好序的(id,col2)對，并利用id去撈取SELECT需要返回的列(col1,col2,col3)。
• 將獲取的結果集返回給用戶。

2）、order by優化排序算法

常規排序方式除了排序本身，還需要額外兩次IO。

優化的排序方式相對于常規排序，減少了第二次IO。

主要區別在于，放入sort buffer不是(id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查詢需要的所有字段，因此排序完成后可以直接返回，無需二次撈數據。這種方式的代價在于，同樣大小的sort buffer，能存放的(col1,col2,col3)數目要小于(id,col2)，如果sort buffer不夠大，可能導致需要寫臨時文件，造成額外的IO。

3）、order by優先隊列排序算法

5.6及之后的版本針對Order by limit M，N語句，在空間層面做了優化，加入了一種新的排序方式–優先隊列，這種方式采用堆排序實現。堆排序算法特征正好可以解limit M，N 這類排序的問題，雖然仍然需要所有元素參與排序，但是只需要M+N個元組的sort buffer空間即可，對于M，N很小的場景，基本不會因為sort buffer不夠而導致需要臨時文件進行歸并排序的問題。

對于升序，采用大頂堆，最終堆中的元素組成了最小的N個元素，對于降序，采用小頂堆，最終堆中的元素組成了最大的N的元素。

11、order by排序不一致問題

1）、MySQL5.6發現分頁出現了重復值

2）、MySQL8查詢正常

3）、原因分析及解決方案

針對limit M,N的語句采用了優先隊列，而優先隊列采用堆實現，比如上述的例子order by idc limit 0，3 需要采用大小為3的大頂堆；limit 3，3需要采用大小為6的大頂堆。由于idc為3的記錄有3條，而堆排序是非穩定的(對于相同的key值，無法保證排序后與排序前的位置一致)，所以導致分頁重復的現象。為了避免這個問題，我們可以在排序中加上唯一值，比如主鍵id，這樣由于id是唯一的，確保參與排序的key值不相同。

12、order by排序案例演示

演示sql及思路：

explain select idc, max(name) from t3 where id>2 and id<10 order by idc,name,idG

分別在查詢字段，where條件，分組字段上做出各種可能的組合，主要就是看有無索引，索引在以上三個關注點上的生效情況。

order by排序的多種案例優化演示請觀看視頻

13、group by分組優化思路

group by本質上也同樣需要進行排序操作，而且與 order by相比，group by主要只是多了排序之后的分組操作。如果在分組的時候還使用了其他的一些聚合函數，那么還需要一些聚合函數的計算。所以，在group by的實現過程中，與group by一樣也可以利用到索引。

具體案例及優化演示講觀看視頻

14、group by的類型

三種實現類型：

• Loose Index Scan（松散的索引掃描）

掃描過程：

先根據group by后面的字段進行分組，分組不需要讀取所有索引的key，例如index（key1，key2，key3），group by key1，key2。此時只要讀取索引中的key1，key2。然后再根據where條件進行篩選。

• Tight Index Scan（緊湊的索引掃描）

掃描過程：

緊湊索引掃描需要在掃描索引的時候，讀取所有滿足條件的索引鍵，然后再根據讀取的數據來完成 GROUP BY 操作得到相應結果。

兩者區別就是緊湊索引掃描是先執行where操作，再進行分組，松散索引掃描剛好相反

• Using temporary 臨時表實現 （非索引掃描）

掃描過程：

MySQL 在進行GROUP BY 操作的時候當MySQL Query Optimizer無法找到合適的索引可以利用的時候，就不得不先讀取需要的數據，然后通過臨時表來完成 GROUP BY操作。

15、group by分組案例演示

演示sql及思路：

explain select idc, max(name) from t3 where id>2 and id<10 group by idc,name,idG

和order by一樣，分別在查詢字段，where條件，分組字段上做出各種可能的組合，主要就是看有無索引，索引在以上三個關注點上的生效情況。

具體案例及優化演示講觀看視頻

16、distinct的實現及優化思路

1）、distinct的原理

distinct實際上和 GROUP BY 的操作非常相似，在GROUP BY之后的每組中只取出一條記錄而已。所以，DISTINCT的實現和GROUP BY的實現也基本差不多，同樣可以通過松散索引掃描或者是緊湊索引掃描來實現，當然，在無法僅僅使用索引即能完成DISTINCT的時候， MySQL只能通過臨時表來完成。但是，和GROUP BY有一點差別的是，DISTINCT并不需要進行排序。

2）、distinct案例演示

演示sql及思路：

explain select distinct name from t3 where idc=3G

（索引中完成，索引默認是排好序的）

explain select distinct name from t3 where idc>1G

（非索引中完成，暗藏排序問題）

具體案例及優化演示講觀看視頻

17、合理的設計并利用索引

索引原理

18、索引失效案例

具體案例見視頻

19、優化終級奧義

• 針對百萬數量級，放棄在mysql中的join操作，推薦分別根據索引單表取數據，然后在程序里面做join，merge數據。
• 盡量使用nosql，例如redis， memcached 等來緩存熱點數據，從而緩解mysql壓力。