《mysql學習》-- 索引

mysql

參考：MySQL索引原理及慢查詢優化

​ 了解MySQL——索引與優化

• mysql
  • 索引
    • 索引類型
      • 從資料結構角度
      • 從實體存儲角度
      • 從邏輯角度
    • 什麼樣的字段不适合建索引
    • 建索引建索引的幾大原則
    • 例子

索引

索引是在存儲引擎中實作的，而不是在mysql伺服器層實作。每種存儲引擎的索引都不一定完全相同，并不是所有的存儲引擎都支援所有的索引類型。

一次查詢隻能使用一個索引

索引類型

從資料結構角度

• btree索引

  B-Tree的基本思想是，所有值（被索引的列）都是排過序的，每個葉節點到跟節點距離相等，平衡樹索引,btree索引中的節點都是按索引列中的順序存儲的，B-Tree 索引需要從根節點到枝節點，最後才能通路到頁節點，需要多次的IO通路

  InnoDB和MyISAM都支援B-Tree索引。InnoDB用的是一個變種B+Tree，而MyISAM為了節省空間對索引進行了壓縮，進而犧牲了性能。

  • btree索引使用場景

  • 等值比對

    可用于

    =

    !=

    <>

    IN

    NOT IN

    <=>

    查詢語句的優化

  • 範圍比對

    可用于

    >

    >=

    <

    <=

    BTEWEEN AND

    等範圍查詢語句的優化

  • 比對最左字首

    對于

    name like bai%

    這種後模糊比對的查詢,是可以利用name字段上建立的索引來優化查詢的,但是對于

    name like %bai

    這種前模糊比對的查詢則沒有辦法使用索引了如假如建立一個索引 fisrt+middle+last，不能跳過fisrt直接使用middle進行索引查詢，如果查詢條件為 where first = ‘x’ and middle like ‘x%’ and last = ‘11’,這樣隻會使用到first+middle，而不會用到last

  • 覆寫索引

    B-Tree索引的key存放的是字段的值,如果key中包含所有需要查詢字段的值,我們就稱之為覆寫索引,覆寫索引能夠極大的提高性能。

  • 排序

    B-Tree索引是排好序的,是以MySQL可以用來優化ORDER BY 和 GROUP BY等操作。

• hash索引：

  哈希索引基于哈希表實作,隻有Memory引擎顯示支援哈希索引,使用哈希索引可以一次定位,是以 Hash 索引的查詢效率要遠高于 B-Tree 索引。但是哈希索引是有很多限制的:

鍵值對，鍵是hash碼，值是資料位置

主索引不能使用hash索引。複雜度o(1),适用于等值條件和in(),

不适用于對鍵依賴于索引進行排序

基于hash表，是以不适用于範圍查找，即>、<，因為鍵值已經做成hash碼了 無法使用索引排序，不支援鍵字首查找(like xx%)

• 全文索引：

全文索引是MyISAM的一個特殊索引類型，主要用于全文檢索

• 空間索引(R-Tree)：

MyISAM支援空間索引，主要用于地理空間資料類型，如用于對GIS資料類型建立SPATIAL索引

從實體存儲角度

參考：MySQL索引詳解

• 聚集索引

  索引和資料是在同一個檔案,一張表隻有一個聚集索引，資料隻能按照一種規則存儲，InnoDB是聚集索引,Innode的聚集索引實際上是将主鍵(PRIMARY kEY )與資料行存放在同一個檔案的,一張表隻能有一個聚集索引。

  InnoDB要求表必須有主鍵（MyISAM可以沒有）,如果沒有顯式指定,則MySQL系統會用一個唯一且不為空的索引列做為主鍵,如果不存在這種列,則MySQL自動為InnoDB表生成一個隐含字段作為主鍵。

  InnoDB的普通索引(二級索引)的葉子節點中存放的是PRIMARY KEY的值,是以需要先查詢普通索引(二級索引)的葉子節點找到對應的主鍵值,然後再根據主鍵值去聚集索引中查詢到對應的資料。

  InnoDB将主鍵與資料聚集在一起的方式,使得按主鍵順序的插入和查詢效率會很高,但是更新主鍵的字段或者不按主鍵的順序插入資料的代價會比較高,是以主鍵的選取很重要(使用AUTO INCREMENT字段或者應用程式生成的順序遞增字段要比無序的UUID好的多)

  二級索引會儲存主鍵的值,是以主鍵的值不要太大。

• 非聚集索引

  索引和資料不在同一個檔案，索引通過指針指向資料,myIsam是非聚集索引,MySQL使用索引查詢資料時,先到MYI檔案中找出資料存儲的位置指針,然後再到MYD檔案中讀取資料。

從邏輯角度

• 主索引：
• 輔助索引：
• 稠密索引：
• 稀疏索引：

什麼樣的字段不适合建索引？

• 列的值唯一性太小（如性别，類型什麼的），不适合建索引
• 太長的列，可以選擇隻建立部分索引，（如：隻取前十位做索引）

建索引建索引的幾大原則

1. 最左字首比對原則，非常重要的原則，mysql會一直向右比對直到遇到範圍查詢(>、<、between、like)就停止比對，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
2. =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識别的形式
3. 盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示字段不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀态、性别字段可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難确定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄
4. 索引列不能參與計算，保持列“幹淨”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。是以語句應該寫成create_time = unix_timestamp(’2014-05-29’);
5. 盡量的擴充索引，不要建立索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼隻需要修改原來的索引即可
6. 更新非常頻繁的資料不适宜索引
7. 對where後邊條件為字元串的一定要加引号，字元串如果為數字mysql會自動轉為字元串，但是不使用索引。

例子

• 覆寫索引(Covering Indexes)

  如果索引包含滿足查詢的所有資料，就稱為覆寫索引。覆寫索引是一種非常強大的工具，能大大提高查詢性能。隻需要讀取索引而不用讀取資料有以下一些優點：

  1. 索引項通常比記錄要小，是以MySQL通路更少的資料；
  2. 索引都按值的大小順序存儲，相對于随機通路記錄，需要更少的I/O；
  3. 大多資料引擎能更好的緩存索引。比如MyISAM隻緩存索引。
  4. 覆寫索引對于InnoDB表尤其有用，因為InnoDB使用聚集索引組織資料，如果二級索引中包含查詢所需的資料，就不再需要在聚集索引中查找了。
  5. 覆寫索引不能是任何索引，隻有B-TREE索引存儲相應的值。而且不同的存儲引擎實作覆寫索引的方式都不同，并不是所有存儲引擎都支援覆寫索引(Memory和Falcon就不支援)。
  6. 對于索引覆寫查詢(index-covered query)，使用EXPLAIN時，可以在Extra一列中看到“Using index”。

  • 例子

  select picname, smallimg from pics where user_id=17853; 如果建立一個(user_id,picname,smallimg)的聯合索引會更加快，一個包含查詢所需字段的索引稱為“覆寫索引”，MySQL隻需要通過索引就可以傳回查詢所需要的資料，而不必在查到索引之後進行回表操作，減少IO，提高了效率

• 利用索引進行排序

  MySQL中，有兩種方式生成有序結果集：一是使用filesort，二是按索引順序掃描。利用索引進行排序操作是非常快的，而且可以利用同一索引同時進行查找和排序操作。當索引的順序與ORDER BY中的列順序相同且所有的列是同一方向(全部升序或者全部降序)時，可以使用索引來排序。如果查詢是連接配接多個表，僅當ORDER BY中的所有列都是第一個表的列時才會使用索引。其它情況都會使用filesort。