MySQL常見索引概念

weixin_34357887發表於2018-07-09
MySql索引

1. 什麼是索引

索引就好比一本書的目錄，它會讓你更快的找到內容，但是目錄（索引）並不是越多越好，假如這本書1000頁，有500也是目錄，它當然效率低，目錄是要佔紙張的,而索引是要佔磁碟空間的。

2. Mysql索引主要有兩種結構：

B+Tree索引和Hash索引.

  • Hash索引

MySQL中，只有Memory（Memory表只存在記憶體中，斷電會消失，適用於臨時表）儲存引擎顯示支援Hash索引，是Memory表的預設索引型別，儘管Memory表也可以使用B+Tree索引。hsah索引把資料的索引以hash形式組織起來，因此當查詢某一條記錄的時候,速度非常快。但因為是hash結構，每個鍵只對應一個值，而且是雜湊的方式分佈。所以他並不支援範圍查詢和排序等功能。

  • B+樹索引

B+tree是mysql使用最頻繁的一個索引資料結構，是Inodb和Myisam儲存引擎模式的索引型別。相對Hash索引，B+樹在查詢單條記錄的速度比不上Hash索引，但是因為更適合排序等操作，所以他更受使用者的歡迎。畢竟不可能只對資料庫進行單條記錄的操作。

3. 選擇索引的資料型別

MySQL支援很多資料型別，選擇合適的資料型別儲存資料對效能有很大的影響。通常來說，可以遵循以下一些指導原則：

  • 小的資料型別通常更好：越小的資料型別通常在磁碟、記憶體和CPU快取中都需要更少的空間，處理起來更快。
  • 簡單的資料型別更好：整型資料比起字元，處理開銷更小，因為字串的比較更復雜。在MySQL中，應該用內建的日期和時間資料型別，而不是用字串來儲存時間；以及用整型資料型別儲存IP地址。
  • 儘量避免NULL：應該指定列為NOT NULL，除非你想儲存NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值。

4. 選擇主鍵型別

選擇合適的識別符號是非常重要的。選擇時不僅應該考慮儲存型別，而且應該考慮MySQL是怎樣進行運算和比較的。一旦選定資料型別，應該保證所有相關的表都使用相同的資料型別。

  • 整型：通常是作為識別符號的最好選擇，因為可以更快的處理，而且可以設定為AUTO_INCREMENT。

  • 字串：儘量避免使用字串作為識別符號，它們消耗更好的空間，處理起來也較慢。而且，通常來說，字串都是隨機的，所以它們在索引中的位置也是隨機的，這會導致頁面分裂、隨機訪問磁碟，聚簇索引分裂（對於使用聚簇索引的儲存引擎）。

Mysql常見索引有：主鍵索引、唯一索引、普通索引、全文索引、組合索引

  • PRIMARY KEY（主鍵索引）: ALTER TABLE table_name ADD PRIMARY KEY ( col )

  • UNIQUE(唯一索引): ALTER TABLE table_name ADD UNIQUE (col)

  • INDEX(普通索引): ALTER TABLE table_name ADD INDEX index_name (col)

  • FULLTEXT(全文索引): ALTER TABLE table_name ADD FULLTEXT ( col )

  • 組合索引: ALTER TABLE table_name ADD INDEX index_name (col1, col2, col3 )

Mysql各種索引區別：

  • 普通索引：最基本的索引，沒有任何限制

普通索引（由關鍵字KEY或INDEX定義的索引）的唯一任務是加快對資料的訪問速度。因此，應該只為那些最經常出現在查詢條件（WHEREcolumn=）或排序條件（ORDERBYcolumn）中的資料列建立索引。只要有可能，就應該選擇一個資料最整齊、最緊湊的資料列（如一個整數型別的資料列）來建立索引。

  • 唯一索引：與"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。

普通索引允許被索引的資料列包含重複的值。比如說，因為人有可能同名，所以同一個姓名在同一個“員工個人資料”資料表裡可能出現兩次或更多次。

如果能確定某個資料列將只包含彼此各不相同的值，在為這個資料列建立索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。這麼做的好處：一是簡化了MySQL對這個索引的管理工作，這個索引也因此而變得更有效率；二是MySQL會在有新記錄插入資料表時，自動檢查新記錄的這個欄位的值是否已經在某個記錄的這個欄位裡出現過了；如果是，MySQL將拒絕插入那條新記錄。也就是說，唯一索引可以保證資料記錄的唯一性。事實上，在許多場合，人們建立唯一索引的目的往往不是為了提高訪問速度，而只是為了避免資料出現重複。

  • 主鍵索引：它 是一種特殊的唯一索引，不允許有空值。

必須為主鍵欄位建立一個索引，這個索引就是所謂的“主索引”。主索引與唯一索引的唯一區別是：前者在定義時使用的關鍵字是PRIMARY而不是UNIQUE。

  • 全文索引：僅可用於 MyISAM 表，針對較大的資料，生成全文索引很耗時耗空間。

MySql自帶的全文索引只能用於資料庫引擎為MYISAM的資料表，如果是其他資料引擎，則全文索引不會生效。此外，MySql自帶的全文索引只能對英文進行全文檢索，目前無法對中文進行全文檢索。

  • 外來鍵索引:

如果為某個外來鍵欄位定義了一個外來鍵約束條件，MySQL就會定義一個內部索引來幫助自己以最有效率的方式去管理和使用外來鍵約束條件。

  • 組合索引：為了更多的提高mysql效率可建立組合索引，遵循”最左字首“原則。建立複合索引時應該將最常用（頻率）作限制條件的列放在最左邊，依次遞減。

索引可以覆蓋多個資料列，如像INDEX（columnA，columnB）索引。這種索引的特點是MySQL可以有選擇地使用一個這樣的索引。如果查詢操作只需要用到columnA資料列上的一個索引，就可以使用複合索引INDEX（columnA,columnB）。不過，這種用法僅適用於在複合索引中排列在前的資料列組合。比如說，INDEX（A，B，C）可以當做A或（A,B）的索引來使用，但不能當做B、C或（B，C）的索引來使用

組合索引最左欄位用in是可以用到索引的，最好explain一下select。

注：

explain顯示了MySQL如何使用索引來處理select語句以及連線表。可以幫助選擇更好的索引和寫出更優化的查詢語句。簡單講，它的作用就是分析查詢效能。

explain關鍵字的使用方法很簡單，就是把它放在select查詢語句的前面。

mysql檢視是否使用索引，簡單的看type型別就可以。如果它是all，那說明這條查詢語句遍歷了所有的行，並沒有使用到索引。

圖片參考地址

本文是整理多個mysql部落格的筆記，總結出來的，主要是關於一些常見索引的概念介紹。如果想深入理解還請自行查詢文件

時：2018年7月5日

相關文章