MySQL 索引初探

praglody發表於2018-11-09
MySql索引

MYSQL索引

什麼是索引

為什麼要建立索引

索引的優缺點和使用原則

索引的分類

  1. 單列索引：一個索引只包含單個列，但一個表中可以有多個單列索引。

    1. 普通索引：MySQL中基本索引型別，沒有什麼限制，允許在定義索引的列中插入重複值和空值;
    2. 唯一索引：索引列中的值必須是唯一的，但是允許為空值;
    3. 主鍵索引：是一種特殊的唯一索引，不允許有空值;

  2. 組合索引：在表中的多個欄位組合上建立的索引；如果列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。

    1. 覆蓋索引: 如果一個索引包含(或覆蓋)所有需要查詢的欄位的值，稱為‘覆蓋索引’。即只需掃描索引而無須回表。

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

  4. 空間索引：空間索引是對空間資料型別的欄位建立的索引，MySQL中的空間資料型別有四種，GEOMETRY、POINT、LINESTRING、POLYGON。要求引擎為MyISAM，建立空間索引的列，必須將其宣告為NOT NULL。

聚簇索引: 待完善

索引執行策略

注意: 一次查詢只會使用一個索引

待完善

ORDER BY最佳化

  1. Where子句與Order BY子句列組合滿足索引最左字首規則。
  2. 查詢滿足覆蓋索引規則。

GROUP BY 最佳化

一、GROUP BY 使用索引原理

  1. group by 使用排序來讀取資料，所以只能用 Btree 索引，不能使用在hash索引是因為hash索引是一種類似鍵值對的快速訪問方式，這個對於指定某個值查詢很好，但無法排序;
  2. 當使用索引排序來查詢資料時，不會在 explain 中 extra 列看到有using filesort;
  3. 在group by操作完成後，還會對group出來的結果進行排序，因此如果對排序的結果沒有排序的需求，可以考慮在其後面加上order by null;

二、GROUP BY 訪問索引的方法

group by 訪問資料有兩種方法：

  1. 邊掃描邊執行group操作，叫做鬆散索引掃描(Loose index scan)；
  2. 先執行一個範圍(range)掃描，然後在執行group 操作,叫做緊索引掃描(Tight index scan)；

鬆散索引掃描 Loose index scan

最高效的處理group by的方法是，直接訪問相應的索引，所以不用排序就能根據索引來讀取需要的資料，而對於如聚簇索引(cluster index)，我們可以讀取前面的一部分的欄位索引來獲取資料，而不用滿足所有的列，這就叫做鬆散索引掃描，也叫做：字首索引掃描。

鬆散索引的條件：

  1. 查詢只能針對一個單表進行操作，這個可是個致命的缺點啊，但如果where條件比較多，選出來的資料少的話，還是不用擔憂的；
  2. group by使用索引為：對聚簇索引使用字首索引；
  3. 使用類似group by 的操作的函式有distinct函式，使用此函式時，要麼在一個索引上使用，要麼在group by時，其group by的字句是索引掃描，否則會引起全表掃描；
  4. 在使用group by語句中，如果使用聚合函式max(), min()等，如果列不在group by的列中，或不在group by 列的聚簇索引的一部分，這將會用到排序操作；
    5、只能對整個列的值排序時使用到索引，而只有前面一部分索引不能用到排序，如： 列 c1 char(20), index(c1(10))、這個只用了一半索引，將無法使用來對整個資料排序；

假設我們在表t1(c1, c2, c3, c4)有聚簇索引index(c1, c2, c3)，能使用Loose index scan的例子:

1、SELECT c1, c2 FROM t1 GROUP BY c1, c2;
2、SELECT DISTINCT c1, c2 FROM t1;
3、SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
4、SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
5、SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
6、SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
7、SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

緊索引掃描 Tight index scan

使用緊索引掃描和松索引掃描類似，只是會先根據where條件來獲取所有的行，然後根據group by的欄位來分組，這種的使用方法，一般是，where條件返回的行較少時使用，比如，你的where字句中使用了主鍵或唯一鍵=const等，這樣的代價是，透過where過濾出來的行很少，再分組操作時也很快的

使用 EXPLAIN 檢視SQL執行計劃

*使用方式

  1. EXPLAIN SELECT ……
  2. EXPLAIN EXTENDED SELECT ……
    將執行計劃"反編譯"成SELECT語句，執行SHOW WARNINGS，可得到被MySQL最佳化器最佳化後的查詢語句。
  3. EXPLAIN PARTITIONS SELECT ……
    用於分割槽表的EXPLAIN生成QEP的資訊

執行計劃包含的資訊

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

1. id
包含一組數字，表示查詢中執行select子句或操作表的順序

  • id相同，可以認為是一組，從上往下順序執行；執行順序由上至下，
  • 如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行，
  • 在所有組中，id值越大，優先順序越高，越先執行

2. select_type
查詢中每個select子句的型別

  • SIMPLE：查詢中不包含子查詢或者UNION
  • 查詢中若包含任何複雜的子部分，最外層查詢則被標記為：PRIMARY
  • 在SELECT或WHERE列表中包含了子查詢，該子查詢被標記為：SUBQUERY
  • 在FROM列表中包含的子查詢被標記為：DERIVED（衍生）用來表示包含在from子句中的子查詢的select，mysql會遞迴執行並將結果放到一個臨時表中。伺服器內部稱為"派生表"，因為該臨時表是從子查詢中派生出來的
  • 若第二個SELECT出現在UNION之後，則被標記為UNION；若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED
  • 從UNION表獲取結果的SELECT被標記為：UNION RESULT

SUBQUERY和UNION還可以被標記為DEPENDENT和UNCACHEABLE。
DEPENDENT意味著select依賴於外層查詢中發現的資料。
UNCACHEABLE意味著select中的某些 特性阻止結果被快取於一個item_cache中。

3. table
使用到的資料表

4. type

表示MySQL在表中找到所需行的方式，又稱“訪問型別”，常見型別如下:
all < index < range < ref < eq_ref < const < system < NULL
從左到右，效能從最差到最好

  • ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行
  • index：Full Index Scan，index與ALL區別為index型別只遍歷索引樹
  • range:索引範圍掃描，對索引的掃描開始於某一點，返回匹配值域的行。顯而易見的索引範圍掃描是帶有between或者where子句裡帶有<, >查詢。當mysql使用索引去查詢一系列值時，例如IN()和OR列表，也會顯示range（範圍掃描）
  • ref：使用非唯一索引掃描或者唯一索引的字首掃描，返回匹配某個單獨值的記錄行
  • eq_ref：類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連線中使用primary key或者 unique key作為關聯條件
  • const、system：當MySQL對查詢某部分進行最佳化，並轉換為一個常量時，使用這些型別訪問。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量
  • NULL：MySQL在最佳化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裡選取最小值可以透過單獨索引查詢完成。

5. possible_keys
指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用

6. key
顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL

7. key_len
表示索引中使用的位元組數，可透過該列計算查詢中使用的索引的長度（key_len顯示的值為索引欄位的最大可能長度，並非實際使用長度，即key_len是根據表定義計算而得，不是透過表內檢索出的）

8. ref
這一列顯示了在key列記錄的索引中，表查詢值所用到的列或常量，常見的有：const（常量），func，NULL，欄位名（例：film.id）

9. rows
表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數

10. Extra
包含不適合在其他列中顯示但十分重要的額外資訊

  • Using index: 表示相應的select操作中使用了覆蓋索引（Covering Index）
  • using index condition：在5.6版本後加入的新特性（Index Condition Pushdown）；Using index condition 會先條件過濾索引，過濾完索引後找到所有符合索引條件的資料行，隨後用 WHERE 子句中的其他條件去過濾這些資料行；
  • Using where: 在查詢使用索引的情況下，需要回表去查詢所需的資料
  • Using temporary: 表示MySQL需要使用臨時表來儲存結果集，常見於排序和分組查詢
  • Using filesort: MySQL中無法利用索引完成的排序操作稱為“檔案排序”
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章