關於MySQL索引知識與小妙招 — 學到了！

一、索引基本知識

1.1 索引的優點

1. 大大減少了伺服器需要掃描的資料量，加快資料庫的檢索速度
2. 幫助伺服器避免排序和臨時表
3. 將隨機io變成順序io

1.2 索引的用處

1. 速查詢匹配WHERE子句的行
2. 從consideration中消除行,如果可以在多個索引之間進行選擇，mysql通常會使用找到最少行的索引
3. 如果表具有多列索引，則優化器可以使用索引的任何最左字首來查詢行
4. 當有表連線的時候，從其他表檢索行資料
5. 查詢特定索引列的min或max值
6. 如果排序或分組時在可用索引的最左字首上完成的，則對錶進行排序和分組
7. 在某些情況下，可以優化查詢以檢索值而無需查詢資料行

1.3 索引的分類

資料庫會預設建立索引，但是並不是給主鍵建立索引，而是給唯一鍵建裡索引的，因為主鍵的特性是唯一且非空

• 主鍵索引： 是一種特殊的唯一索引，不允許有空值。（主鍵約束，就是一個主鍵索引）

• 唯一索引： 索引列中的值必須是唯一的，但是允許為空值。

• 普通索引： MySQL中基本索引型別，沒有什麼限制，允許在定義索引的列中插入重複值和空值，純粹為了查詢資料更快一 點。

• 全文索引： 只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT型別欄位上使用全文索引

  什麼是全文索引，就是在一堆文字中，通過其中的某個關鍵字等，就能找到該欄位所屬的記錄行，比如有"LOL LPL 牧小農" 通過牧小農，可能就可以找到該條記錄。這裡說的是可能，因為全文索引的使用涉及了很多細節，我們只需要知道這個大概意思。一般開發中，不貴用到全文索引，因為其佔用很大的物理空間和降低了記錄修改性，故較為少用。

• 組合索引： 在表中的多個欄位組合上建立的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用，使用組合索引時遵循最左字首集合。

  例如這裡由id、name和age3個欄位構成的索引，索引行中就按id/name/age的順序存放，索引可以索引下面欄位組合(id，name，age)、(id，name)或者(id)。如果要查詢的欄位不構成索引最左面的字首，那麼就不會是用索引，比如，age或者（name，age）組合就不會使用索引查詢。

1.4 面試技術名詞

• 回表： 資料庫根據索引（非主鍵）找到了指定的記錄所在行後，還需要根據主鍵再次到資料塊裡獲取資料，這種稱之為回表

• 覆蓋索引： 看我寫的一篇文章：面試三輪我倒在了一道sql題上——sql效能優化

• 最左匹配： 指在聯合索引中，如果你的 SQL 語句中用到了聯合索引中的最左邊的索引，那麼這條 SQL 語句就可以利用這個聯合索引去進行匹配，如果遇到範圍查詢(>、<、between、like)就會停止匹配。

  select * from t where a=1 and b=1 and c =1; #這樣可以利用到定義的索引（a,b,c）,用上a,b,c
  select * from t where a=1 and b=1; #這樣可以利用到定義的索引（a,b,c）,用上a,b
  select * from t where b=1 and a=1; #這樣可以利用到定義的索引（a,b,c）,用上a,c（mysql有查詢優化器）
  select * from t where a=1; #這樣也可以利用到定義的索引（a,b,c）,用上a
  select * from t where b=1 and c=1; #這樣不可以利用到定義的索引（a,b,c）
  select * from t where a=1 and c=1; #這樣可以利用到定義的索引（a,b,c），但只用上a索引，b,c索引用不到

• 索引下推： 稱為 Index Condition Pushdown (ICP)，這是MySQL提供的用某一個索引對一個特定的表從表中獲取元組”，注意我們這裡特意強調了“一個”，這是因為這樣的索引優化不是用於多表連線而是用於單表掃描，確切地說，是單表利用索引進行掃描以獲取資料的一種方式。

1.5 索引採用的資料結構

1.5.1 雜湊表

缺點︰

1、利用hash儲存的話需要將所有的資料檔案新增到記憶體，比較耗費記憶體空間
2、如果所有的查詢都是等值查詢，那麼hash確實很快，但是在企業或者實際工作環境中範圍查詢的資料更多，而不是等值查詢，因此hash就不太適合了

1.5.2 二叉樹

缺點∶

無論是二叉樹還是紅黑樹，都會因為樹的深度過深而造成io次數變多，影響資料讀取的效率

1.5.3 B+樹

B樹特點:
1、所有鍵值分佈在整顆樹中
2、搜尋有可能在非葉子結點結束，在關鍵字全集內做一次查詢,效能逼近二分查詢
3、每個節點最多擁有m個子樹
4、根節點至少有2個子樹
5、分支節點至少擁有m/2顆子樹(除根節點和葉子節點外都是分支節點)
6、所有葉子節點都在同一層、每個節點最多可以有m-1個key，並且以升序排列

例項圖說明∶
每個節點佔用一個磁碟塊，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指標，指標儲存的是子節點所在磁碟塊的地址。兩個關鍵詞劃分成的三個範圍域對應三個指標指向的子樹的資料的範圍域。以根節點為例，關鍵字為16和34，P1指標指向的子樹的資料範圍為小於16,P2指標指向的子樹的資料範圍為16~34 ,P3指標指向的子樹的資料範圍為大於34。

查詢關鍵字過程:

1. 根據根節點找到磁碟塊1，讀入記憶體。【磁碟I/O操作第1次】
2. 比較關鍵字28在區間(16,34 )，找到磁碟塊1的指標P2。
3. 根據P2指標找到磁碟塊3，讀入記憶體。【磁碟I/O操作第2次】
4. 比較關健字28在區間(25,31 )，找到磁碟塊3的指標P2。
5. 根據P2指標找到磁碟塊8，讀入記憶體。【磁碟I/O 操作第3次】
6. 在磁碟塊8中的關健寧列表中找到關健字28。

缺點:

• 每個節點都有key，同時也包含data，而每個頁儲存空間是有限的，如果data比較大的話會導致每個節點儲存的k ey數量變小
• 當儲存的資料量很大的時候會導致深度較大，增大查詢時磁碟io次數，進而影響查詢效能

1.6 索引匹配方式

全值匹配： 全值匹配指的是和索引中的所有列進行匹配

explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';

匹配最左字首： 只匹配前面的幾列

explain select * from staffs where name = 'July' and age = '23';

explain select * from staffs where name = 'July';

匹配列字首： 可以匹配某一列的值的開頭部分

explain select * from staffs where name like 'J%';

explain select * from staffs where name like '%y';

匹配範圍值： 可以查詢某一個範圍的資料

explain select * from staffs where name > 'Mary';

精確匹配某一列並範圍匹配另外一列：可以查詢第一列的全部和第二列的部分

explain select * from staffs where name = 'July' and age > 25;

只訪問索引的查詢： 查詢的時候只需要訪問索引，不需要訪問資料行，本質上就是覆蓋索引

explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

二、雜湊索引

• 基於雜湊表的實現，只有精確匹配索引所有列的查詢才有效
• 在mysql中，只有memory的儲存引擎顯式支援雜湊索引
• 雜湊索引自身只需儲存對應的hash值，所以索引的結構十分緊湊，這讓雜湊索引查詢的速度非常快

2.1 雜湊索引的限制

1. 雜湊索引只包含雜湊值和行指標，而不儲存欄位值，索引不能使用索引中的值來避免讀取行
2. 雜湊索引資料並不是按照索引值順序儲存的，所以無法進行排序
3. 雜湊索引不支援部分列匹配查詢，雜湊索引是使用索引列的全部內容來計算雜湊值
4. 雜湊索引支援等值比較查詢，也不支援任何範圍查詢
5. 訪問雜湊索引的資料非常快，除非有很多雜湊衝突，當出現雜湊衝突的時候，儲存引擎必須遍歷連結串列中的所有行指標，逐行進行比較，直到找到所有符合條件的行
6. 雜湊衝突比較多的話，維護的代價也會很高

2.2 案例

當需要儲存大量的URL，並且根據URL進行搜尋查詢，如果使用B+樹，儲存的內容就會很大：select id from url where url=""

也可以利用將url使用CRC32做雜湊，可以使用以下查詢方式：select id fom url where url="" and url_crc=CRC32("")

此查詢效能較高原因是使用體積很小的索引來完成查詢

三、組合索引

當包含多個列作為索引，需要注意的是正確的順序依賴於該索引的查詢，同時需要考慮如何更好的滿足排序和分組的需要

案例： 建立組合索引 a,b,c ，不同SQL語句使用索引情況

語句	索引是否發揮作用
where a=3	是，只使用了a
where a=3 and b=5	是，使用了a,b
where a =3 and b = 5 and c= 4	是，使用了a,b,c
where a = 3 or c = 4	否
where a = 3 and c= 4	是，僅使用了a
where a = 3 and b > 10 and c = 7	是，使用了a,b
where a = 3 and b like '%mxn%' and c=7	使用了a

四、聚簇索引與非聚簇索引

4.1 聚簇索引

• 不是單獨的索引型別，而是一種資料儲存方式，指的是資料行跟相鄰的鍵值緊湊的儲存在一起，將資料儲存與索引放到了一塊，找到索引也就找到了資料
• 如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有唯一索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。InnoDB 只聚集在同一個頁面中的記錄。包含相鄰健值的頁面可能相距甚遠。

4.2 非聚簇索引

• 資料檔案跟索引檔案分開存放，將資料儲存於索引分開結構，索引結構的葉子節點指向了資料的對應行，myisam通過key_buffer把索引先快取到記憶體中，當需要訪問資料時（通過索引訪問資料），在記憶體中直接搜尋索引，然後通過索引找到磁碟相應資料，這也就是為什麼索引不在key buffer命中時，速度慢的原因

• 通過葉子節點指標找到資料頁中的資料，所以非聚簇索引是邏輯順序

五、覆蓋索引

5.1 基本介紹

1. 如果一個索引包含所有需要查詢的欄位的值，我們稱之為覆蓋索引
2. 不是所有型別的索引都可以稱為覆蓋索引，覆蓋索引必須要儲存索引列的值
3. 不同的儲存實現覆蓋索引的方式不同，不是所有的引擎都支援覆蓋索引，memory不支援覆蓋索引

5.2 優勢

1、索引條目通常遠小於資料行大小，如果只需要讀取索引，那麼mysql就會極大的較少資料訪問量
2、因為索引是按照列值順序儲存的，所以對於IO密集型的範圍查詢會比隨機從磁碟讀取每一行資料的IO要少的多
3、一些儲存引擎如MYISAM在記憶體中只快取索引，資料則依賴於作業系統來快取，因此要訪問資料需要一次系統呼叫，這可能會導致嚴重的效能問題
4、由於INNODB的聚簇索引，覆蓋索引對INNODB表特別有用

5.3 案例演示

1、當發起一個被索引覆蓋的查詢時，在explain的extra列可以看到using index的資訊，此時就使用了覆蓋索引
2、在大多數儲存引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以進一步的進行優化，可以使用innodb的二級索引來覆蓋查詢。

例如：actor使用innodb儲存引擎，並在last_name欄位又二級索引，雖然該索引的列不包括主鍵actor_id，但也能夠用於對actor_id做覆蓋查詢

六、優化小細節

• 當使用索引列進行查詢的時候儘量不要使用表示式，把計算放到業務層而不是資料庫層
• 儘量使用主鍵查詢，而不是其他索引，因為主鍵查詢不會觸發回表查詢
• 使用字首索引

  有時候需要索引很長的字串，這會讓索引變的大且慢，通常情況下可以使用某個列開始的部分字串，這樣大大的節約索引空間，從而提高索引效率，但這會降低索引的選擇性，索引的選擇性是指不重複的索引值和資料表記錄總數的比值，範圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性更高的索引可以讓mysql在查詢的時候過濾掉更多的行。
  ​ 一般情況下某個列字首的選擇性也是足夠高的，足以滿足查詢的效能，但是對應BLOB,TEXT,VARCHAR型別的列，必須要使用字首索引，因為mysql不允許索引這些列的完整長度，使用該方法的訣竅在於要選擇足夠長的字首以保證較高的選擇性，通過又不能太長。

--建立資料表
create table citydemo(city varchar(50) not null);
insert into citydemo(city) select city from city;

--重複執行5次下面的sql語句
insert into citydemo(city) select city from citydemo;

--更新城市表的名稱
update citydemo set city=(select city from city order by rand() limit 1);

--查詢最常見的城市列表，發現每個值都出現45-65次，
select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

--查詢最頻繁出現的城市字首，先從3個字首字母開始，發現比原來出現的次數更多，可以分別擷取多個字元檢視城市出現的次數
select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
--此時字首的選擇性接近於完整列的選擇性

--還可以通過另外一種方式來計算完整列的選擇性，可以看到當字首長度到達7之後，再增加字首長度，選擇性提升的幅度已經很小了
select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7,
count(distinct left(city,8))/count(*) as sel8 
from citydemo;

--計算完成之後可以建立字首索引
alter table citydemo add key(city(7));

--注意：字首索引是一種能使索引更小更快的有效方法，但是也包含缺點：mysql無法使用字首索引做order by 和 group by。 

• 使用索引掃描來排序

  mysql有兩種方式可以生成有序的結果：通過排序操作或者按索引順序掃描，如果explain出來的type列的值為index,則說明mysql使用了索引掃描來做排序
  ​ 掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那麼就不得不每掃描一條索引記錄就得回表查詢一次對應的行，這基本都是隨機IO，因此按索引順序讀取資料的速度通常要比順序地全表掃描慢
  ​ mysql可以使用同一個索引即滿足排序，又用於查詢行，如果可能的話，設計索引時應該儘可能地同時滿足這兩種任務。
  ​ 只有當索引的列順序和order by子句的順序完全一致，並且所有列的排序方式都一樣時，mysql才能夠使用索引來對結果進行排序，如果查詢需要關聯多張表，則只有當orderby子句引用的欄位全部為第一張表時，才能使用索引做排序。order by子句和查詢型查詢的限制是一樣的，需要滿足索引的最左字首的要求，否則，mysql都需要執行順序操作，而無法利用索引排序

• union all,in,or都能夠使用索引，但是推薦使用in

• 範圍列可以用到索引，範圍條件是：<、>,範圍列可以用到索引，但是範圍列後面的列無法用到索引，索引最多用於一個範圍列

• 強制型別轉換會全表掃描

create table user(id int,name varchar(10),phone varchar(11));
alter table user add index idx_1(phone);
explain select * from user where phone=13800001234;(不會觸發索引)
explain select * from user where phone='13800001234';(觸發索引)

• 更新十分頻繁，資料區分度不高的欄位上不宜建立索引

  更新會變更B+樹，更新頻繁的欄位建議索引會大大降低資料庫效能.
  類似於性別這類區分不大的屬性，建立索引是沒有意義的，不能有效的過濾資料，
  一般區分度在80%以上的時候就可以建立索引，區分度可以使用 count(distinct(列名))/count(*) 來計算

• 建立索引的列，不允許為null，可能會得到不符合預期的結果

• 當需要進行表連線的時候，最好不要超過三張表，因為需要join的欄位，資料型別必須一致

• 能使用limit的時候儘量使用limit

• 單表索引建議控制在5個以內

• 單索引欄位數不允許超過5個（組合索引）

• 建立索引的時候應該避免以下錯誤概念

  索引越多越好(錯誤)
  過早優化，在不瞭解系統的情況下進行優化(錯誤)