索引
- 什么是索引
- 索引的創建與洗掉
  - 創建索引
  - 洗掉索引
- 索引的使用
  - 使用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后面跟大表

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/541155.html

標籤：MySQL

上一篇：SQL201 查找薪水記錄超過15條的員工號emp_no以及其對應的記錄次數t

下一篇：《迷你世界》億級玩家都在用的游戲場景推薦系統長啥樣？

[MySQL] 索引的使用、SQL陳述句優化策略

索引