高效能MySQL讀書筆記---索引優化

索引優化

索引是儲存引擎中用於快鎖查詢記錄的一種資料結構

1. 索引型別

   • B-Tree索引
     B-Tree通常意味著所有的值都是按順序儲存的，並且每一個葉子頁到根的距離相同。

   B-Tree對索引是順序組織儲存的，所以很適合查詢範圍資料。
   B-Tree索引適用於全鍵值、鍵值範圍或鍵字首查詢。其中鍵字首查詢只適用於根據最左字首的查詢。
   適用場景：

    1. 全值匹配,和索引中的所有列進行匹配。
    2. 匹配最左字首,只能使用索引的第一列
    建立channel_id,ref索引,單獨查詢channel_id時可以用到索引,單獨查詢ref時用不到索引,檢索了全部資料         

   

     3. 匹配列字首,使用LIKE查詢時只能查詢以什麼開頭時才能用到索引,並且只能用到索引第一列  
     例如建立了name_age(name,age) 的聚合索引,查詢以張字開頭的姓並且年齡為18的資料時只能用到name索引,而查以輝字結尾的姓別時用不到索引。
     建立ref,channel_id的索引,ref使用like查詢時掃描了70萬行,改為等於查詢時掃描了10萬行
     

     4. 匹配範圍值 當索引中列有範圍查詢時其後面列也將無法使用到索引
     當channel_id使用範圍查詢時,使用索引檢索了所有大於30的channel_id,但是索引第二列的ref沒有使用索引

 - 雜湊索引
 雜湊索引是基於雜湊表實現的。(感覺使用場景不多,而且只支援Memory引擎表,略過)
 - 空間資料索引
 - 全文索引 用於查詢列中關鍵詞,不是直接比較索引中的值。

1. 索引的優點
   可以減少伺服器需要掃描的資料量,避免排序和臨時表,將隨機I/O變成順序I/O；
   什麼時候使用索引：只有當索引幫助引擎快速查詢記錄的好處大於它帶來的額外工作時才使用索引。

2. 索引優化

   • 簡化WHERE條件 始終將索引列放入=號得一側
   • 儘量使用多列索引而不是吧WHERE的每一列都建上單獨的索引,而且當有多列索引的最左列索引和單列索引共同存在時MySQL查詢時會使用多列索引而不是單列索引,例如建立了ab(a,b),a,b三個索引 WHERE a=1 and
     b=2時只能使用到ab索引

   

- 當伺服器出現對多個索引的相交操作通常需要一個包含查詢列的多列索引而不是多個單列索引
- 建立多列索引時將選擇性最高的列放入索引的最左邊。
- 當資料重複性太高時,例如一張大表中有一半資料都包含著某個值,另一邊包含著另一個值   這樣的列就沒有建立所以的必要了。

例如圖中channel_id欄位在400萬資料中有四分之一都是,就算建立索引每次查詢也會檢索100萬條記錄。

- 當某幾個列同時查詢的頻率很高的時候,可以針對這幾個列建立一個全覆蓋索引。
- 避免重複順序索引的建立,會造成索引冗餘。
建立一個多列索引,兩個單列索引,查詢時會使用多列索引而不是兩個單列索引

- 儘量把需要範圍查詢的列放入多列索引的右面,便於優化器儘可能索引大多數的列。