【離散讀、ICP優化、全文檢索】

​

​

文章目錄

• ​​什麼是離散讀？​​
• ​​優化器如何優化離散讀？你是如何避免離散讀的​​
• ​​什麼是ICP優化​​
• ​​什麼是全文檢索​​

什麼是離散讀？

在某些情況下，當執行EXPLAIN指令進行SQL語句的分析時，會發現優化器并沒有選擇索引去查找資料，而是通過掃描聚集索引，也就是直接進行全表的掃描來得到資料。這種情況多發生于範圍查找、JOIN連結操作等情況下。假設表：t_index 。其中 id 為主鍵；c1 與 c2 組成了聯合索引（c1，c2）；此外，c1還是一個單獨索引。進行如下查詢操作：SELECT*FROMt_ index WHERE c1＞1andc1＜100000;可以看到表t_index有（c1，c2）的聯合主鍵，此外還有對于列c1的單個索引。上述這句SQL顯然是可以通過掃描OrderID上的索引進行資料的查找。然而通過EXPLAIN指令，使用者會發現優化器并沒有按照OrderID上的索引來查找資料。在最後的索引使用中，優化器選擇了PRIMARY id 聚集索引，也就是表掃描（table scan），而非c1輔助索引掃描（indexscan）。這是為什麼呢？因為如果強制使用c1索引，就會造成離散讀。具體原因在于使用者要選取的資料是整行資訊，而c1作為輔助索引不能覆寫到我們要查詢的資訊，是以在對c1索引查詢到指定資料後，還需要一次書簽通路來查找整行資料的資訊。雖然c1索引中資料是順序存放的，但是再一次進行書簽查找的資料則是無序的，是以變為了磁盤上的離散讀操作。如果要求通路的資料量很小，則優化器還是會選擇輔助索引，但是當通路的資料占整個表中資料的蠻大一部分時（一般是20%左右），優化器會選擇通過聚集索引來查找資料。

優化器如何優化離散讀？你是如何避免離散讀的

MySQL 5.6之前，優化器在進行離散讀決策的時候，如果資料量比較大，會選擇使用聚集索引，全表掃描。MySQL5.6版本開始支援Multi-Range Read（MRR）優化。Multi-Range Read優化的目的就是為了減少磁盤的随機通路，并且将随機通路轉化為較為順序的資料通路，這對于IO-bound類型的SQL查詢語句可帶來性能極大的提升。Multi-Range Read優化可适用于range，ref，eq_ref類型的查詢。MRR優化有以下幾個好處：❑MRR使資料通路變得較為順序。在查詢輔助索引時，首先根據得到的查詢結果，按照主鍵進行排序，并按照主鍵排序的順序進行書簽查找。❑減少緩沖池中頁被替換的次數。（順序查找可以對一個頁進行順序查找，無需離散加載資料頁）❑批量處理對鍵值的查詢操作。對于InnoDB和MyISAM存儲引擎的範圍查詢和JOIN查詢操作，MRR的工作方式如下：❑将查詢得到的輔助索引鍵值存放于一個緩存中，這時緩存中的資料是根據輔助索引鍵值排序的。❑将緩存中的鍵值根據RowID進行排序。❑根據RowID的排序順序來通路實際的資料檔案。

舉例說明：SELECT * FROM salaries WHERE salary＞10000ANDsalary＜40000;salary上有一個輔助索引idx_s，是以除了通過輔助索引查找鍵值外，還需要通過書簽查找來進行對整行資料的查詢。

Multi-Range Read還可以将某些範圍查詢，拆分為鍵值對，以此來進行批量的資料查詢。這樣做的好處是可以在 拆分過程中，直接過濾一些不符合查詢條件的資料，例如：SELECT*FROMtWHERE key_part1＞=1000ANDkey_part1＜2000ANDkey_part2=10000;表t有（key_part1，key_part2）的聯合索引，是以索引根據key_part1，key_part2的位置關系進行排序。若沒有Multi-Read Range，此時查詢類型為Range，SQL優化器會先将key_part1大于1000且小于2000的資料都取出，即使key_part2不等于1000。待取出行資料後再根據key_part2的條件進行過濾。這會導緻無用資料被取出。如果有大量的資料且其key_part2不等于1000，則啟用Mulit-Range Read優化會使性能有巨大的提升。倘若啟用了Multi-Range Read優化，優化器會先将查詢條件進行拆分，然後再進行資料查詢。就上述查詢語句而言，優化器會将查詢條件拆分為（1000，10000），（1001，10000），（1002，10000），…，（1999，10000），最後再根據這些拆分出的條件進行資料的查詢。我是如何優化的：在非必要的情況下，拒絕使用 select * ；在必須 select * 的情況下，盡量使用MySQL5.6+的版本開啟MRR； 在必須 select * 的情況下且MySQL 小于 5.6 版本下，可以根據資料量進行離散讀和聚集索引兩種情況下的性能進行對比，必要時采用force index語句強制指定索引。

什麼是ICP優化

和Multi-Range Read一樣，Index Condition Pushdown同樣是MySQL 5.6開始支援的一種根據索引進行查詢的優化方式。之前的MySQL資料庫版本不支援Index Condition Pushdown，當進行索引查詢時，首先根據索引

來查找記錄，然後再根據WHERE條件來過濾記錄。在支援Index Condition Pushdown後，MySQL資料庫會在取出索引的同時，判斷是否可以進行WHERE條件的過濾，也就是将WHERE的部分過濾操作放在了存儲引擎層。在某些查詢下，可以大大減少上層SQL層對記錄的索取（fetch），進而提高資料庫的整體性能。Index Condition Pushdown優化支援range、ref、eq_ref、ref_or_null類型的查詢，目前支援MyISAM和InnoDB存儲引擎。當優化器選擇Index Condition Pushdown優化時，可在執行計劃的列Extra看到Usingindexcondition提示。

什麼是全文檢索

例：​

​SELECT * FROM blog WHERE content like '%xxx%'​

​ 根據B+樹索引的特性，上述SQL語句即便添加了B+樹索引也是需要進行索引的掃描來得到結果。類似這樣的需求在網際網路應用中還有很多。例如，搜尋引擎需要根據使用者輸入的關鍵字進行全文查找，電子商務網站需要根據使用者的查詢條件，在可能需要在商品的詳細介紹中進行查找，這些都不是B+樹索引所能很好地完成的工作。全文檢索（Full-Text Search）是将存儲于資料庫中的整本書或整篇文章中的任意内容資訊查找出來的技術。它可以根據需要獲得全文中有關章、節、段、句、詞等資訊，也可以進行各種統計和分析。在之前的MySQL資料庫中，InnoDB存儲引擎并不支援全文檢索技術。大多數的使用者轉向MyISAM存儲引擎，這可能需要進行表的拆分，并将需要進行全文檢索的資料存儲為MyISAM表。這樣的确能夠解決邏輯業務的需求，但是卻喪失了InnoDB存儲引擎的事務性，而這在生産環境應用中同樣是非常關鍵的。從InnoDB 1.2.x版本開始，InnoDB存儲引擎開始支援全文檢索，其支援MyISAM存儲引擎的全部功能，并且還支援其他的一些特性。InnoDB存儲引擎從1.2.x版本開始支援全文檢索的技術，其采用full inverted index的方式。在InnoDB存儲引擎中，将(DocumentId，Position)視為一個“ilist”。是以在全文檢索的表中，有兩個列，一個是word字段，一個是ilist字段，并且在word字段上有設有索引。

• NATURALLANGUAGEMODE全文檢索通過MATCH函數進行查詢，預設采用Natural Language模式，其表示查詢帶有指定word的文檔。
• BOOLEANMODEMySQL資料庫允許使用IN BOOLEAN MODE修飾符來進行全文檢索。當使用該修飾符時，查詢字元串的前後字元 會有特殊的含義，例如下面的語句要求查詢有字元串Pease但沒有hot的文檔，其中+和-分别表示這個單詞必須出現，或者一定不存在。
• WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITHQUERYEXPANSIONMySQL資料庫還支援全文檢索的擴充查詢。這種查詢通常在查詢的關鍵詞太短，使用者需要impliedknowledge（隐含知識）時進行。例如，對于單詞database的查詢，使用者可能希望查詢的不僅僅是包含database的文檔，可能還指那些包含MySQL、Oracle、DB2、RDBMS的單詞。而這時可以使用Query Expansion模式來開啟全文檢索的impliedknowledge。