MySql索引筆記

小東呱呱發表於2020-11-09
MySql索引筆記

文章目錄

一、索引的類別

由於本文是基於mysql的InnoDB儲存引擎，索引我們主要看第一個表格，其他的表格可以自行的觀看，都不難，從表格我們可以看出來，InnoDB儲存引擎索引只支援BTREE型別的索引，索引的類別有Primary Key，Unique，Key，FULLTEXT和SPATIAL。當然也有其他的分法，按照索引列的數量分為單列索引和組合索引。

  1. Primary Key（聚集索引）： InnoDB儲存引擎的表會存在主鍵（唯一非null），如果建表的時候沒有指定主鍵，則會使用第一非空的唯一索引作為聚集索引，否則InnoDB會自動幫你建立一個不可見的、長度為6位元組的row_id用來作為聚集索引；
  2. 單列索引： 單列索引即一個索引只包含單個列；
  3. 聯合索引： 組合索引指在表的多個欄位組合上建立的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用。使用組合索引時遵循最左字首集合 ；
  4. Unique（唯一索引）： 索引列的值必須唯一，但允許有空值。若是組合索引，則列值的組合必須唯一。主鍵索引是一種特殊的唯一索引，不允許有空值；
  5. Key（普通索引）： 是MySQL中的基本索引型別，允許在定義索引的列中插入重複值和空值；
  6. ** FULLTEXT（全文索引）：** 全文索引型別為FULLTEXT，在定義索引的列上支援值的全文查詢，允許在這些索引列中插入重複值和空值。全文索引可以在CHAR、VARCHAR或者TEXT型別的列上建立；
  7. SPATIAL（空間索引）： 空間索引是對空間資料型別的欄位建立的索引，MySQL中的空間資料型別有4種，分別是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL關鍵字進行擴充套件，使得能夠用於建立正規索引類似的語法建立空間索引。建立空間索引的列必須宣告為NOT NULL

二、索引的建立原則

  1. 索引並非越多越好，一個表中如果有大量的索引，不僅佔用磁碟空間，而且會影響INSERT、DELETE、UPDATE等語句的效能，因為在表中的資料更改的同時，索引也會進行調整和更新
  2. 避免對經常更新的表進行過多的索引，並且索引中的列儘可能少。而對經常用於查詢的欄位應該建立索引，但要避免新增不必要的欄位。
  3. 資料量小的表最好不要使用索引，由於資料較少，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。
  4. 在條件表示式中經常用到的不同值較多的列上建立索引，在不同值很少的列上不要建立索引。比如在學生表的“性別”欄位上只有“男”與“女”兩個不同值，因此就無須建立索引。如果建立索引，不但不會提高查詢效率，反而會嚴重降低資料更新速度。
  5. 當唯一性是某種資料本身的特徵時，指定唯一索引。使用唯一索引需能確保定義的列的資料完整性，以提高查詢速度。
  6. 在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引。
  7. 搜尋的索引列，不一定是所要選擇的列。換句話說，最適合索引的列是出現在WHERE子句中的列，或連線子句中指定的列，而不是出現在SELECT關鍵字後的選擇列表中的列。
  8. 使用短索引。如果對字串列進行索引，應該指定一個字首長度，只要有可能就應該這樣做。例如，有一個CHAR(200)列，如果在前10個或20個字元內，多數值是唯一的，那麼就不要對整個列進行索引。對前10個或20個字元進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁碟 IO 較少，較短的值比較起來更快。更為重要的是，對於較短的鍵值，索引快取記憶體中的塊能容納更多的鍵值，因此，MySQL 也可以在記憶體中容納更多的值。這樣就增加了找到行而不用讀取索引中較多塊的可能性。
  9. 利用最左字首。在建立一個n列的索引時，實際是建立了MySQL可利用的n個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左字首。
  10. 對於InnoDB儲存引擎的表，記錄預設會按照一定的順序儲存，如果有明確定義的主鍵，則按照主鍵順序儲存。如果沒有主鍵，但是有唯一索引，那麼就是按照唯一索引的順序儲存。如果既沒有主鍵又沒有唯一索引，那麼表中會自動生成一個內部列，按照這個列的順序儲存。按照主鍵或者內部列進行的訪問是最快的，所以InnoDB表儘量自己指定主鍵，當表中同時有幾個列都是唯一的，都可以作為主鍵的時候，要選擇最常作為訪問條件的列作為主鍵，提高查詢的效率。另外，還需要注意，InnoDB 表的普通索引都會儲存主鍵的鍵值，所以主鍵要儘可能選擇較短的資料型別，可以有效地減少索引的磁碟佔用，提高索引的快取效果

三、索引的管理和使用

1、檢視索引

SHOW INDEX FROM <表名> [ FROM <資料庫名>]

Table： 表的名稱。

Non_unique： 如果索引不能包括重複詞，則為0。如果可以，則為1。

Key_name： 索引的名稱。

Seq_in_index： 索引中的列序列號，從1開始。

Column_name： 列名稱。

Collation： 列以什麼方式儲存在索引中。在MySQL中，有值‘A’（升序）或NULL（無分類）。

Cardinality： 索引中唯一值的數目的估計值。通過執行ANALYZE TABLE或myisamchk -a可以更新。基數根據被儲存為整數的統計資料來計數，所以即使對於小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL使用該索引的機會就越大。

Sub_part： 如果列只是被部分地編入索引，則為被編入索引的字元的數目。如果整列被編入索引，則為NULL。

Packed： 指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。

Null： 如果列含有NULL，則含有YES。如果沒有，則該列含有NO。

Index_type： 用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

2、新增索引

在執行CREATE TABLE語句時可以建立索引，也可以單獨用CREATE INDEX或ALTER TABLE來為表增加索引。

ALTER TABLE
ALTER TABLE用來建立普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。索引名index_name可選，預設時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時建立多個索引。

CREATE INDEX
CREATE INDEX可對錶增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有與ALTER TABLE語句中相同的含義，索引名不可選。另外，不能用CREATE INDEX語句建立PRIMARY KEY索引。

3、刪除索引

可利用ALTER TABLE或DROP INDEX語句來刪除索引。類似於CREATE INDEX語句，DROP INDEX可以在ALTER TABLE內部作為一條語句處理，語法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

其中，前兩條語句是等價的，刪除掉table_name中的索引index_name。

第3條語句只在刪除PRIMARY KEY索引時使用，因為一個表只可能有一個PRIMARY KEY索引，因此不需要指定索引名。如果沒有建立PRIMARY KEY索引，但表具有一個或多個UNIQUE索引，則MySQL將刪除第一個UNIQUE索引。

如果從表中刪除了某列，則索引會受到影響。對於多列組合的索引，如果刪除其中的某列，則該列也會從索引中刪除。如果刪除組成索引的所有列，則整個索引將被刪除。

4、EXPLAIN語句

** EXPLAIN語句的基本語法如下：**

explain select * from sys_user;

**select_options是SELECT語句的查詢選項，包括FROMWHERE子句等 **

id: SELECT識別符。這是SELECT的查詢序列號,表示查詢中執行select子句或操作表的順序,id相同，執行順序從上到下,id不同，id值越大執行優先順序越高
select_type：表示SELECT語句的型別。它可以是以下幾種取值：
    SIMPLE:表示簡單查詢，其中不包括連線查詢和子查詢；
    PRIMARY:表示主查詢，或者是最外層的查詢語句，最外層查詢為PRIMARY，也就是最後載入的就是PRIMARY；
    UNION:表示連線查詢的第2個或後面的查詢語句， 不依賴於外部查詢的結果集
    DEPENDENT UNION:連線查詢中的第2個或後面的SELECT語句，依賴於外面的查詢；
    UNION RESULT:連線查詢的結果；
    SUBQUERY:子查詢中的第1個SELECT語句；不依賴於外部查詢的結果集
    DEPENDENT SUBQUERY:子查詢中的第1個SELECT，依賴於外面的查詢；
    DERIVED:匯出表的SELECT（FROM子句的子查詢）,MySQL會遞迴執行這些子查詢，把結果放在臨時表裡。
    DEPENDENT DERIVED:派生表依賴於另一個表
    MATERIALIZED:物化子查詢
    UNCACHEABLE SUBQUERY:子查詢，其結果無法快取，必須針對外部查詢的每一行重新進行評估
    UNCACHEABLE UNION:UNION中的第二個或隨後的 select 查詢，屬於不可快取的子查詢
table:表示查詢的表
partitions:查詢將從中匹配記錄的分割槽。該值適用NULL於未分割槽的表
type:表示表的連線型別
    system:該表是僅有一行的系統表。這是const連線型別的一個特例
    const: 資料表最多隻有一個匹配行，它將在查詢開始時被讀取，並在餘下的查詢優化中作為常量對待。const表查詢速度很快，因為只讀取一次,const用於使用常數值比較PRIMARY KEY或UNIQUE索引的所有部分的場合。
    eq_ref:對於每個來自前面的表的行組合，從該表中讀取一行,可以用於使用=運算子進行比較的索引列 。比較值可以是常量，也可以是使用在此表之前讀取的表中列的表示式
    ref:對於來自前面的表的任意行組合，將從該表中讀取所有匹配的行，ref可以用於使用“＝”或“＜＝＞”操作符的帶索引的列。
    fulltext:使用FULLTEXT 索引執行聯接
    ref_or_null:這種連線型別類似於ref，但是除了MySQL還會額外搜尋包含NULL值的行。此聯接型別優化最常用於解析子查詢
    index_merge:此聯接型別指示使用索引合併優化。在這種情況下，key輸出行中的列包含使用的索引列表，並key_len包含使用的索引 的最長鍵部分的列表
    unique_subquery:型別替換 以下形式的eq_ref某些 IN子查詢,unique_subquery 只是一個索引查詢函式，它完全替代了子查詢以提高效率。
    index_subquery:連線型別類似於 unique_subquery。它代替IN子查詢,但只適合子查詢中的非唯一索引
    range:只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。當使用＝、＜＞、＞、＞＝、＜、＜＝、IS NULL、＜＝＞、BETWEEN或者IN操作符用常量比較關鍵字列時，型別為range
    index:該index聯接型別是一樣的 ALL，只是索引樹被掃描。這發生兩種方式：1、如果索引是查詢的覆蓋索引，並且可用於滿足表中所需的所有資料，則僅掃描索引樹。在這種情況下，Extra列顯示為 Using index，2、使用對索引的讀取執行全表掃描，以按索引順序查詢資料行。 Uses index沒有出現在 Extra列中。
    ALL:對於前面的表的任意行組合進行完整的表掃描    
possible_keys:指出MySQL能使用哪個索引在該表中找到行。若該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看它是否引用某些列或適合索引的列來提高查詢效能。如果是這樣，可以建立適合的索引來提高查詢的效能。
kye:表示查詢實際使用的索引，如果沒有選擇索引，該列的值是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
key_len：表示MySQL選擇的索引欄位按位元組計算的長度，若鍵是NULL，則長度為NULL。注意，通過key_len值可以確定MySQL將實際使用一個多列索引中的幾個欄位
ref：表示使用哪個列或常數與索引一起來查詢記錄。
rows：顯示MySQL在表中進行查詢時必須檢查的行數。
Extra：表示MySQL在處理查詢時的詳細資訊

相關文章