• 索引
  • 什麼是索引
  • 索引的創建與刪除
    • 創建索引
    • 刪除索引
  • 索引的使用
    • 使用explain分析SQL語句
    • 最佳左首碼
    • 索引覆蓋
    • 避免對索引列進行額外運算
• SQL語句優化
  • 小表驅動大表

索引

什麼是索引

索引是一種方便我們高效查找某一列或幾列數據的一種數據結構，一般是 B+樹或者 hash樹。想象一下在一個表中有一列是我們經常需要用於作為查詢條件的列，也就是它經常出現在 where 子句中，那麼如果每次用到它都要順序遍歷全表數據來找到我們所需要的那一行，聽著好像效率不太高的樣子，所以就出現了索引這個東西。
因為索引一般是使用樹這種數據結構來存儲的，而樹是對排序很友好的一種數據結構，例如一個二叉樹，左邊都是比根小的而右邊都是比根大的，要查找一個數據就很容易。所以有了索引之後就可以增加檢索的效率，大大縮短查找時間。

索引的創建與刪除

創建索引

可以在創建表的時候一起創建索引，也可以在建完表之後單獨創建
在建表的時候創建索引：

CREATE TABLE `tb` (
  `tid` int(3) NOT NULL,
  `tname` varchar(10) DEFAULT NULL,
  `test_column` int(3) DEFAULT NULL,
  PRIMARY KEY (`tid`),
  KEY `name_index` (`tname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

以上語句創建了一個名為 tb 的表（表創建完成之後可以通過以下SQL語句來查看創建該表所需要的SQL語句：

show create table 表名;

我們創建了 tb 表，並指定了 主鍵為 tid 欄位，在 tname 列創建了一個名為 name_index 的索引，並指定了引擎為 InnoDB、字元編碼方式為 utf8mb4。

在建表後通過 alter 語句或 create 語句來創建索引：

alter table 表名 add index 索引名(列1, 列2, 列3...);
create index 索引名 on 表名(列1, 列2, 列3...);

可以對一個或多個列共同添加索引。索引創建完成後可以通過以下語句來查看該表的所有索引信息：

可以看到主鍵也是一個索引

刪除索引

有兩種刪除索引的方式：

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;

索引的使用

使用explain分析SQL語句

使用索引的時候有幾點需要註意的地方來避免讓索引失效，要觀察索引是否失效可以通過 explain 語句來查看 SQL 語句的執行情況。

id
數值越大執行順序越靠前，數值一樣時從上往下順序執行，在本例中也就是 t2 -> subquery2 -> t1。

select_type
查詢類型，取值有SIMPLE（簡單查詢，不包含子查詢或 union）、PRIMARY（主查詢，一般出現在有子查詢的語句中）等。

table
使用的表，有時候會有一些臨時表，比如這裡的 subquery2。

type
類型，這個類型和上面的 select_type 不要一樣，這個 type 欄位可以看成是 SQL 語句執行速度的一個衡量方式，
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般來說 system 和 const 是達不到的，eq_ref 也是比較困難的，所以我們一般能達到的是 ref、range 和 index，當然這些都是針對有索引的情況來說的，沒有索引的話那就只能是 ALL。

possible_keys 和 key
預測會使用的索引和實際使用的索引

extra
一些額外信息，比較常見的幾種有

• using filesort：需要額外一次排序，常見於有 order by的語句中
• using temporary：用到了臨時表，常見於有 group by 的語句中
• using index：代表使用了索引
• using where：意味不明

前兩種代表性能消耗較大，是我們需要避免的，如果出現了這兩個信息說明我們的 SQL 語句需要優化了，using index 意味著性能有所提升，而 using where 的出現好像很難總結出什麼規律，一般不太需要關註它。

最佳左首碼

這個是針對複合索引來說的，也就是一個索引中包含多個列的時候。最佳左首碼的意思是我們使用索引的時候要按照複合索引的順序來使用，不要跨列，也就是說，如果一個索引的定義是（a，b，c，d），那我們使用的時候就要按照 abc 的順序來使用。說到這個使用順序就要提到 SQL 的解析過程了
編寫過程：

select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..

解析過程：

from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

按照這個解析過程，這樣的一條 SQL 語句是符合最佳左首碼的：

select d from tb where a=... and b=... and c=...;

我們同時使用了 abc 這三個欄位，並且解析順序也會是 a -> b -> c -> d
這樣的 SQL 語句是不符合最佳左首碼的，它會使得一部分索引失效：

select d from tb where a=... and c=...;

b 列沒有使用到，也就是說我們只用了 acd 這三列，跨了 b 列，這條語句會導致 a 後面的索引都失效，也就是只有 a 使用到了索引， c=... 語句並沒有使用索引。
舉個例子：

可以看到第二個 SQL 語句中跨了 b2 列，所以 index_b1_b2_b3_b4 部分失效了（索引是否部分失效可以通過 key_len 欄位看出來）。

索引覆蓋

覆蓋索引（covering index ，或稱為索引覆蓋）即從非主鍵索引中就能查到的記錄，而不需要查詢主鍵索引中的記錄，避免了回表的產生減少了樹的搜索次數，顯著提升性能。
儘量不要使用 SELECT *語句，因為這樣會發生回表查詢不能使用索引覆蓋從而導致查詢效率低。觀察以下兩條 SQL 語句，一個是 SELECT * 一個是只選擇需要的列：

可以看到使用SELECT *的語句執行時沒有走複合索引（即 index_a4_a6，這是由 a4 和 a6 功能組成的一個複合索引），而是走了 index_a4 這個只有 a4 組成的索引，而使用 SELECT a4, a6的語句則走了複合索引，因為整條SQL 語句就只用到了 a4 和 a6 這兩列，這兩列在index_a4_a6 存儲了，所以不需要回表查詢，查一次這個複合索引就可以拿到結果了，而前面的SELECT *語句還需要回表查詢那些索引里沒有欄位。所以說儘量不要使用SELECT *，需要用到什麼欄位就 select 什麼欄位，避免索引覆蓋失效同時也可以減少 IO 消耗。

避免對索引列進行額外運算

對索引進行額外的運算（加減乘、類型轉換等）會導致索引失效：

可以看到 type 從ref 退化成了 index，並且 row 是 4 說明發生了回表查詢（test02 表中一共4條數據）。

SQL語句優化

前面我們已經說了索引的重要性了，所以 SQL 優化的很大一部分就是索引的優化，當然還有一些其他的優化原則，這就是我們本節要講的東西。

小表驅動大表

這個原則不只是寫 SQL 語句需要遵循，我們平時寫代碼的時候也要儘量遵循這個原則。比如寫雙層 for 迴圈的時候，儘量把迴圈次數小的那個 for 放在外層而迴圈次數多的放在內層，這樣就可以減少從內外側迴圈切換的次數，減少一些性能消耗。
舉個例子，兩個 for 迴圈，一個要迴圈10次一個要迴圈100次，當然不管兩個迴圈怎麼組合最終都是一共要迴圈1000次，但是如果把迴圈10次的放在外層，那麼就從外層迴圈跳到內層迴圈的次數就只要10次，反之要100次。所以把迴圈次數少的那個 for 迴圈放在外面可以減少棧幀的切換次數從而提升性能。
回到 SQL 場景中就是當存在子查詢的時候，把數據量大的表放在子查詢里而數據量小的表放在主查詢里。當然可能有的場景下我們就是必須得把大表放在主查詢里，因為我們需要的欄位在大表裡，那麼這時候我們就可以使用 exists 和 in 這兩個關鍵詞來做一些轉換來提升 SQL 語句的效率了：
首先說一下 in和exists的區別：

• in: 先查子查詢，查出結果後和主查詢做笛卡爾積，子查詢只查一次。
• exists: 先查主查詢，然後每次進行主查詢的時候都會遍歷一遍子查詢表，也就是說子查詢執行次數為主查詢表中的數據量n。

假設現在t1為小表，t2為大表
小表在外層時：
正例：

select * from t1 where exists(select id from t2 where id=t1.id);

反例：

select * from t1 where id in (select id from t2);

正例之所以是推薦的寫法是因為 exists後面跟了大表，此時子查詢可以使用 t2 表的 id 列的索引來提高查詢效率，外層是一個小表，內層又可以使用索引來加速查詢，所以總體效率較高；而反例之所以不推薦是因為 in 後面跟了大表，所以這個大表會被整個查詢一遍和t1做笛卡爾積，很顯然沒有正例的效率高。
大表在外層時：
正例：

select * from t2 where id in (select id from t1);

反例：

select * from t2 where exists(select id from t1 where id=t2.id);

反例exists後跟小表，索引對於小表的查詢效率提升不明顯。
總結起來就是 in後面跟小表，exists後面跟大表