本文轉載自http://www.jianshu.com/p/d7665192aaaf 說起MySQL的查詢優化，相信大家積累一堆技巧：不能使用SELECT *、不使用NULL欄位、合理創建索引、為欄位選擇合適的數據類型..... 你是否真的理解這些優化技巧？是否理解其背後的工作原理？在實際場景下性能 ...
本文轉載自http://www.jianshu.com/p/d7665192aaaf
說起MySQL的查詢優化，相信大家積累一堆技巧：不能使用SELECT *、不使用NULL欄位、合理創建索引、為欄位選擇合適的數據類型..... 你是否真的理解這些優化技巧？是否理解其背後的工作原理？在實際場景下性能真有提升嗎？我想未必。因而理解這些優化建議背後的原理就尤為重要，希望本文能讓你重新審視這些優化建議，併在實際業務場景下合理的運用。 MySQL邏輯架構 如果能在頭腦中構建一幅MySQL各組件之間如何協同工作的架構圖，有助於深入理解MySQL伺服器。下圖展示了MySQL的邏輯架構圖。
MySQL邏輯架構，來自：高性能MySQL MySQL邏輯架構整體分為三層，最上層為客戶端層，並非MySQL所獨有，諸如：連接處理、授權認證、安全等功能均在這一層處理。 MySQL大多數核心服務均在中間這一層，包括查詢解析、分析、優化、緩存、內置函數(比如：時間、數學、加密等函數)。所有的跨存儲引擎的功能也在這一層實現：存儲過程、觸發器、視圖等。 最下層為存儲引擎，其負責MySQL中的數據存儲和提取。和Linux下的文件系統類似，每種存儲引擎都有其優勢和劣勢。中間的服務層通過API與存儲引擎通信，這些API介面屏蔽了不同存儲引擎間的差異。 MySQL查詢過程 我們總是希望MySQL能夠獲得更高的查詢性能，最好的辦法是弄清楚MySQL是如何優化和執行查詢的。一旦理解了這一點，就會發現：很多的查詢優化工作實際上就是遵循一些原則讓MySQL的優化器能夠按照預想的合理方式運行而已。 當向MySQL發送一個請求的時候，MySQL到底做了些什麼呢？
MySQL查詢過程 客戶端/服務端通信協議 MySQL客戶端/服務端通信協議是“半雙工”的：在任一時刻，要麼是伺服器向客戶端發送數據，要麼是客戶端向伺服器發送數據，這兩個動作不能同時發生。一旦一端開始發送消息，另一端要接收完整個消息才能響應它，所以我們無法也無須將一個消息切成小塊獨立發送，也沒有辦法進行流量控制。 客戶端用一個單獨的數據包將查詢請求發送給伺服器，所以當查詢語句很長的時候，需要設置max_allowed_packet參數。但是需要註意的是，如果查詢實在是太大，服務端會拒絕接收更多數據並拋出異常。 與之相反的是，伺服器響應給用戶的數據通常會很多，由多個數據包組成。但是當伺服器響應客戶端請求時，客戶端必須完整的接收整個返回結果，而不能簡單的只取前面幾條結果，然後讓伺服器停止發送。因而在實際開發中，儘量保持查詢簡單且只返回必需的數據，減小通信間數據包的大小和數量是一個非常好的習慣，這也是查詢中儘量避免使用SELECT *以及加上LIMIT限制的原因之一。 查詢緩存 在解析一個查詢語句前，如果查詢緩存是打開的，那麼MySQL會檢查這個查詢語句是否命中查詢緩存中的數據。如果當前查詢恰好命中查詢緩存，在檢查一次用戶許可權後直接返回緩存中的結果。這種情況下，查詢不會被解析，也不會生成執行計劃，更不會執行。 MySQL將緩存存放在一個引用表（不要理解成table，可以認為是類似於HashMap的數據結構），通過一個哈希值索引，這個哈希值通過查詢本身、當前要查詢的資料庫、客戶端協議版本號等一些可能影響結果的信息計算得來。所以兩個查詢在任何字元上的不同（例如：空格、註釋），都會導致緩存不會命中。 如果查詢中包含任何用戶自定義函數、存儲函數、用戶變數、臨時表、mysql庫中的系統表，其查詢結果 都不會被緩存。比如函數NOW()或者CURRENT_DATE()會因為不同的查詢時間，返回不同的查詢結果，再比如包含CURRENT_USER或者CONNECION_ID()的查詢語句會因為不同的用戶而返回不同的結果，將這樣的查詢結果緩存起來沒有任何的意義。 既然是緩存，就會失效，那查詢緩存何時失效呢？MySQL的查詢緩存系統會跟蹤查詢中涉及的每個表，如果這些表（數據或結構）發生變化，那麼和這張表相關的所有緩存數據都將失效。正因為如此，在任何的寫操作時，MySQL必須將對應表的所有緩存都設置為失效。如果查詢緩存非常大或者碎片很多，這個操作就可能帶來很大的系統消耗，甚至導致系統僵死一會兒。而且查詢緩存對系統的額外消耗也不僅僅在寫操作，讀操作也不例外：
- 任何的查詢語句在開始之前都必須經過檢查，即使這條SQL語句永遠不會命中緩存
- 如果查詢結果可以被緩存，那麼執行完成後，會將結果存入緩存，也會帶來額外的系統消耗
- 用多個小表代替一個大表，註意不要過度設計
- 批量插入代替迴圈單條插入
- 合理控制緩存空間大小，一般來說其大小設置為幾十兆比較合適
- 可以通過SQL_CACHE和SQL_NO_CACHE來控制某個查詢語句是否需要進行緩存
- mysql> select * from t_message limit 10;
- ...省略結果集
- mysql> show status like 'last_query_cost';
- +-----------------+-------------+
- | Variable_name | Value |
- +-----------------+-------------+
- | Last_query_cost | 6391.799000 |
- +-----------------+-------------+
- 重新定義表的關聯順序（多張表關聯查詢時，並不一定按照SQL中指定的順序進行，但有一些技巧可以指定關聯順序）
- 優化MIN()和MAX()函數（找某列的最小值，如果該列有索引，只需要查找B+Tree索引最左端，反之則可以找到最大值，具體原理見下文）
- 提前終止查詢（比如：使用Limit時，查找到滿足數量的結果集後會立即終止查詢）
- 優化排序（在老版本MySQL會使用兩次傳輸排序，即先讀取行指針和需要排序的欄位在記憶體中對其排序，然後再根據排序結果去讀取數據行，而新版本採用的是單次傳輸排序，也就是一次讀取所有的數據行，然後根據給定的列排序。對於I/O密集型應用，效率會高很多）
- 客戶端向MySQL伺服器發送一條查詢請求
- 伺服器首先檢查查詢緩存，如果命中緩存，則立刻返回存儲在緩存中的結果。否則進入下一階段
- 伺服器進行SQL解析、預處理、再由優化器生成對應的執行計劃
- MySQL根據執行計劃，調用存儲引擎的API來執行查詢
- 將結果返回給客戶端，同時緩存查詢結果
- 通常來說把可為NULL的列改為NOT NULL不會對性能提升有多少幫助，只是如果計劃在列上創建索引，就應該將該列設置為NOT NULL。
- 對整數類型指定寬度，比如INT(11)，沒有任何卵用。INT使用16為存儲空間，那麼它的表示範圍已經確定，所以INT(1)和INT(20)對於存儲和計算是相同的。
- UNSIGNED表示不允許負值，大致可以使正數的上限提高一倍。比如TINYINT存儲範圍是通常來講，沒有太大的必要使用DECIMAL數據類型。即使是在需要存儲財務數據時，仍然可以使用BIGINT。比如需要精確到萬分之一，那麼可以將數據乘以一百萬然後使用TIMESTAMP使用4個位元組存儲空間，DATETIME使用8個位元組存儲空間。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的範圍小得多，而且TIMESTAMP的值因時區不同而不同。
- 大多數情況下沒有使用枚舉類型的必要，其中一個缺點是枚舉的字元串列表是固定的，添加和刪除字元串（枚舉選項）必須使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。
- schema的列不要太多。原因是存儲引擎的API工作時需要在伺服器層和存儲引擎層之間通過行緩衝格式拷貝數據，然後在伺服器層將緩衝內容解碼成各個列，這個轉換過程的代價是非常高的。如果列太多而實際使用的列又很少的話，有可能會導致CPU占用過高。
- 大表ALTER TABLE非常耗時，MySQL執行大部分修改表結果操作的方法是用新的結構創建一個張空表，從舊表中查出所有的數據插入新表，然後再刪除舊表。尤其當記憶體不足而表又很大，而且還有很大索引的情況下，耗時更久。當然有一些奇淫技巧可以解決這個問題，有興趣可自行查閱。
二叉查找樹和平衡二叉樹 由於二叉查找樹可以任意構造，同樣的值，可以構造出如圖②的二叉查找樹，顯然這棵二叉樹的查詢效率和順序查找差不多。若想二叉查找數的查詢性能最高，需要這棵二叉查找樹是平衡的，也即平衡二叉樹（AVL樹）。 平衡二叉樹首先需要符合二叉查找樹的定義，其次必須滿足任何節點的兩個子樹的高度差不能大於1。顯然圖②不滿足平衡二叉樹的定義，而圖①是一課平衡二叉樹。平衡二叉樹的查找性能是比較高的（性能最好的是最優二叉樹），查詢性能越好，維護的成本就越大。比如圖①的平衡二叉樹，當用戶需要插入一個新的值9的節點時，就需要做出如下變動。
平衡二叉樹旋轉 通過一次左旋操作就將插入後的樹重新變為平衡二叉樹是最簡單的情況了，實際應用場景中可能需要旋轉多次。至此我們可以考慮一個問題，平衡二叉樹的查找效率還不錯，實現也非常簡單，相應的維護成本還能接受，為什麼MySQL索引不直接使用平衡二叉樹？ 隨著資料庫中數據的增加，索引本身大小隨之增加，不可能全部存儲在記憶體中，因此索引往往以索引文件的形式存儲的磁碟上。這樣的話，索引查找過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗要高幾個數量級。可以想象一下一棵幾百萬節點的二叉樹的深度是多少？如果將這麼大深度的一顆二叉樹放磁碟上，每讀取一個節點，需要一次磁碟的I/O讀取，整個查找的耗時顯然是不能夠接受的。那麼如何減少查找過程中的I/O存取次數？ 一種行之有效的解決方法是減少樹的深度，將二叉樹變為m叉樹（多路搜索樹），而B+Tree就是一種多路搜索樹。理解B+Tree時，只需要理解其最重要的兩個特征即可：第一，所有的關鍵字（可以理解為數據）都存儲在葉子節點（Leaf Page），非葉子節點（Index Page）並不存儲真正的數據，所有記錄節點都是按鍵值大小順序存放在同一層葉子節點上。其次，所有的葉子節點由指針連接。如下圖為高度為2的簡化了的B+Tree。
簡化B+Tree 怎麼理解這兩個特征？MySQL將每個節點的大小設置為一個頁的整數倍（原因下文會介紹），也就是在節點空間大小一定的情況下，每個節點可以存儲更多的內結點，這樣每個結點能索引的範圍更大更精確。所有的葉子節點使用指針鏈接的好處是可以進行區間訪問，比如上圖中，如果查找大於20而小於30的記錄，只需要找到節點20，就可以遍歷指針依次找到25、30。如果沒有鏈接指針的話，就無法進行區間查找。這也是MySQL使用B+Tree作為索引存儲結構的重要原因。 MySQL為何將節點大小設置為頁的整數倍，這就需要理解磁碟的存儲原理。磁碟本身存取就比主存慢很多，在加上機械運動損耗（特別是普通的機械硬碟），磁碟的存取速度往往是主存的幾百萬分之一，為了儘量減少磁碟I/O，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的數據放入記憶體，預讀的長度一般為頁的整數倍。 引用 頁是電腦管理存儲器的邏輯塊，硬體及OS往往將主存和磁碟存儲區分割為連續的大小相等的塊，每個存儲塊稱為一頁（許多OS中，頁的大小通常為4K）。主存和磁碟以頁為單位交換數據。當程式要讀取的數據不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤信號，磁碟會找到數據的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續運行。 MySQL巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。為了達到這個目的，每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁里，加之電腦存儲分配都是按頁對齊的，就實現了讀取一個節點只需一次I/O。假設B+Tree的高度為h，一次檢索最多需要h-1I/O（根節點常駐記憶體），複雜度$O(h) = O(\log_{M}N)$。實際應用場景中，M通常較大，常常超過100，因此樹的高度一般都比較小，通常不超過3。 最後簡單瞭解下B+Tree節點的操作，在整體上對索引的維護有一個大概的瞭解，雖然索引可以大大提高查詢效率，但維護索引仍要花費很大的代價，因此合理的創建索引也就尤為重要。 仍以上面的樹為例，我們假設每個節點只能存儲4個內節點。首先要插入第一個節點28，如下圖所示。
leaf page和index page都沒有滿 接著插入下一個節點70，在Index Page中查詢後得知應該插入到50 - 70之間的葉子節點，但葉子節點已滿，這時候就需要進行也分裂的操作，當前的葉子節點起點為50，所以根據中間值來拆分葉子節點，如下圖所示。
Leaf Page拆分 最後插入一個節點95，這時候Index Page和Leaf Page都滿了，就需要做兩次拆分，如下圖所示。
Leaf Page與Index Page拆分 拆分後最終形成了這樣一顆樹。
最終樹 B+Tree為了保持平衡，對於新插入的值需要做大量的拆分頁操作，而頁的拆分需要I/O操作，為了儘可能的減少頁的拆分操作，B+Tree也提供了類似於平衡二叉樹的旋轉功能。當LeafPage已滿但其左右兄弟節點沒有滿的情況下，B+Tree並不急於去做拆分操作，而是將記錄移到當前所在頁的兄弟節點上。通常情況下，左兄弟會被先檢查用來做旋轉操作。就比如上面第二個示例，當插入70的時候，並不會去做頁拆分，而是左旋操作。
左旋操作 通過旋轉操作可以最大限度的減少頁分裂，從而減少索引維護過程中的磁碟的I/O操作，也提高索引維護效率。需要註意的是，刪除節點跟插入節點類型，仍然需要旋轉和拆分操作，這裡就不再說明。 高性能策略 通過上文，相信你對B+Tree的數據結構已經有了大致的瞭解，但MySQL中索引是如何組織數據的存儲呢？以一個簡單的示例來說明，假如有如下數據表： Mysql代碼
- CREATE TABLE People(
- last_name varchar(50) not null,
- first_name varchar(50) not null,
- dob date not null,
- gender enum(`m`,`f`) not null,
- key(last_name,first_name,dob)
- );
索引如何組織數據存儲，來自：高性能MySQL 可以看到，索引首先根據第一個欄位來排列順序，當名字相同時，則根據第三個欄位，即出生日期來排序，正是因為這個原因，才有了索引的“最左原則”。 1、MySQL不會使用索引的情況：非獨立的列 “獨立的列”是指索引列不能是表達式的一部分，也不能是函數的參數。比如： Mysql代碼
- select * from where id + 1 = 5
- select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1
- select film_id,actor_id from film_actor where actor_id = 1
- union all
- select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1
- 當出現多個索引做相交操作時（多個AND條件），通常來說一個包含所有相關列的索引要優於多個獨立索引。
- 當出現多個索引做聯合操作時（多個OR條件），對結果集的合併、排序等操作需要耗費大量的CPU和記憶體資源，特別是當其中的某些索引的選擇性不高，需要返回合併大量數據時，查詢成本更高。所以這種情況下還不如走全表掃描。
- SELECT * FROM payment where staff_id = 2 and customer_id = 584
- select count(distinct staff_id)/count(*) as staff_id_selectivity,
- count(distinct customer_id)/count(*) as customer_id_selectivity,
- count(*) from payment
- select user_id from trade where user_group_id = 1 and trade_amount > 0
- select user.* from user where login_time > '2017-04-01' and age between 18 and 30;
- 索引條目遠小於數據行大小，如果只讀取索引，極大減少數據訪問量
- 索引是有按照列值順序存儲的，對於I/O密集型的範圍查詢要比隨機從磁碟讀取每一行數據的IO要少的多
- // 最左列為常數，索引：(date,staff_id,customer_id)
- select staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id
- 確保ON和USING字句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表A和表B用列c關聯的時候，如果優化器關聯的順序是A、B，那麼就不需要在A表的對應列上創建索引。沒有用到的索引會帶來額外的負擔，一般來說，除非有其他理由，只需要在關聯順序中的第二張表的相應列上創建索引（具體原因下文分析）。
- 確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列，這樣MySQL才有可能使用索引來優化。
- SELECT A.xx,B.yy
- FROM A INNER JOIN B USING(c)
- WHERE A.xx IN (5,6)
- outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
- outer_row = outer_iterator.next;
- while(outer_row) {
- inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
- inner_row = inner_iterator.next;
- while(inner_row) {
- output[inner_row.yy,outer_row.xx];
- inner_row = inner_iterator.next;
- }
- outer_row = outer_iterator.next;
- }
- SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
- SELECT film.film_id,film.description
- FROM film INNER JOIN (
- SELECT film_id FROM film ORDER BY title LIMIT 50,5
- ) AS tmp USING(film_id);
- SELECT id FROM t LIMIT 10000, 10;
- 改為：
- SELECT id FROM t WHERE id > 10000 LIMIT 10;
- 有非常多的程式員在分享時都會拋出這樣一個觀點：儘可能不要使用存儲過程，存儲過程非常不容易維護，也會增加使用成本，應該把業務邏輯放到客戶端。既然客戶端都能幹這些事，那為什麼還要存儲過程？
- JOIN本身也挺方便的，直接查詢就好了，為什麼還需要視圖呢？
參考資料
[1] 薑承堯 著；MySQL技術內幕-InnoDB存儲引擎；機械工業出版社，2013
[2] Baron Scbwartz 等著；寧海元 周振興等譯；高性能MySQL（第三版）; 電子工業出版社， 2013
[3] 由 B-/B+樹看 MySQL索引結構
