MySql索引筆記
一、索引的類別
由於本文是基於mysql的InnoDB儲存引擎,索引我們主要看第一個表格,其他的表格可以自行的觀看,都不難,從表格我們可以看出來,InnoDB儲存引擎索引只支援BTREE型別的索引,索引的類別有Primary Key,Unique,Key,FULLTEXT和SPATIAL。當然也有其他的分法,按照索引列的數量分為單列索引和組合索引。
- Primary Key(聚集索引): InnoDB儲存引擎的表會存在主鍵(唯一非null),如果建表的時候沒有指定主鍵,則會使用第一非空的唯一索引作為聚集索引,否則InnoDB會自動幫你建立一個不可見的、長度為6位元組的row_id用來作為聚集索引;
- 單列索引: 單列索引即一個索引只包含單個列;
- 聯合索引: 組合索引指在表的多個欄位組合上建立的索引,只有在查詢條件中使用了這些欄位的左邊欄位時,索引才會被使用。使用組合索引時遵循最左字首集合 ;
- Unique(唯一索引): 索引列的值必須唯一,但允許有空值。若是組合索引,則列值的組合必須唯一。主鍵索引是一種特殊的唯一索引,不允許有空值;
- Key(普通索引): 是MySQL中的基本索引型別,允許在定義索引的列中插入重複值和空值;
- ** FULLTEXT(全文索引):** 全文索引型別為FULLTEXT,在定義索引的列上支援值的全文查詢,允許在這些索引列中插入重複值和空值。全文索引可以在CHAR、VARCHAR或者TEXT型別的列上建立;
- SPATIAL(空間索引): 空間索引是對空間資料型別的欄位建立的索引,MySQL中的空間資料型別有4種,分別是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL關鍵字進行擴充套件,使得能夠用於建立正規索引類似的語法建立空間索引。建立空間索引的列必須宣告為NOT NULL
二、索引的建立原則
- 索引並非越多越好,一個表中如果有大量的索引,不僅佔用磁碟空間,而且會影響INSERT、DELETE、UPDATE等語句的效能,因為在表中的資料更改的同時,索引也會進行調整和更新
- 避免對經常更新的表進行過多的索引,並且索引中的列儘可能少。而對經常用於查詢的欄位應該建立索引,但要避免新增不必要的欄位。
- 資料量小的表最好不要使用索引,由於資料較少,查詢花費的時間可能比遍歷索引的時間還要短,索引可能不會產生優化效果。
- 在條件表示式中經常用到的不同值較多的列上建立索引,在不同值很少的列上不要建立索引。比如在學生表的“性別”欄位上只有“男”與“女”兩個不同值,因此就無須建立索引。如果建立索引,不但不會提高查詢效率,反而會嚴重降低資料更新速度。
- 當唯一性是某種資料本身的特徵時,指定唯一索引。使用唯一索引需能確保定義的列的資料完整性,以提高查詢速度。
- 在頻繁進行排序或分組(即進行group by或order by操作)的列上建立索引,如果待排序的列有多個,可以在這些列上建立組合索引。
- 搜尋的索引列,不一定是所要選擇的列。換句話說,最適合索引的列是出現在WHERE子句中的列,或連線子句中指定的列,而不是出現在SELECT關鍵字後的選擇列表中的列。
- 使用短索引。如果對字串列進行索引,應該指定一個字首長度,只要有可能就應該這樣做。例如,有一個CHAR(200)列,如果在前10個或20個字元內,多數值是唯一的,那麼就不要對整個列進行索引。對前10個或20個字元進行索引能夠節省大量索引空間,也可能會使查詢更快。較小的索引涉及的磁碟 IO 較少,較短的值比較起來更快。更為重要的是,對於較短的鍵值,索引快取記憶體中的塊能容納更多的鍵值,因此,MySQL 也可以在記憶體中容納更多的值。這樣就增加了找到行而不用讀取索引中較多塊的可能性。
- 利用最左字首。在建立一個n列的索引時,實際是建立了MySQL可利用的n個索引。多列索引可起幾個索引的作用,因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左字首。
- 對於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在處理查詢時的詳細資訊
相關文章
- MySQL 筆記 - 索引優化MySql筆記索引優化
- mysql總結筆記 -- 索引篇MySql筆記索引
- MySQL學習筆記:索引失效MySql筆記索引
- MySql 學習筆記二:索引MySql筆記索引
- MySQL學習筆記:組合索引-索引下推MySql筆記索引
- Mysql索引讀書筆記(待續)MySql索引筆記
- mysql 執行計劃索引分析筆記MySql索引筆記
- MySQL優化學習筆記之索引MySql優化筆記索引
- MySQL-覆蓋索引總結筆記MySql索引筆記
- MySQL學習筆記:組合索引-最左原則MySql筆記索引
- 高效能MySQL讀書筆記---索引優化MySql筆記索引優化
- [每天進步一點點]mysql筆記整理(三):索引MySql筆記索引
- CUUG筆記 ORACLE索引學習筆記筆記Oracle索引
- 《MySQL實戰45講》學習筆記4——MySQL中InnoDB的索引MySql筆記索引
- 筆記五:倒排索引筆記索引
- 索引學習筆記索引筆記
- BI專案記筆記索引筆記索引
- ES 筆記五:倒排索引筆記索引
- 18.管理索引(筆記)索引筆記
- Oracle筆記 之 索引(index)Oracle筆記索引Index
- Bootstrap~學習筆記索引boot筆記索引
- mysql 筆記MySql筆記
- MySQL筆記MySql筆記
- 分割槽索引學習筆記索引筆記
- oracle index索引相關筆記OracleIndex索引筆記
- mysql CRUD筆記MySql筆記
- MYSQL筆記01MySql筆記
- Mysql 工作筆記MySql筆記
- oracle學習筆記——檢視、索引Oracle筆記索引
- C#筆記----------------------------索引指示器C#筆記索引
- mysql中文全文索引的記錄MySql索引
- 【記錄】MySQL 學習筆記MySql筆記
- [記錄] MySQL 學習筆記MySql筆記
- MySQL基礎筆記MySql筆記
- MySql學習筆記MySql筆記
- MySQL 優化筆記MySql優化筆記
- mysql引擎筆記整理MySql筆記
- 筆記mysql優化筆記MySql優化