從原理到優化，深入淺出資料庫索引

来源:https://www.cnblogs.com/binyue/archive/2020/02/13/12305670.html
-Advertisement-
Play Games

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。 資料庫查詢是資料庫的最主要功能之一，我們都希望查詢數據的速度能儘可能的快，因此資料庫系統的設計者會從查詢演算法的角度進行優化，這篇文章對索引做一個系統的梳理，希望對大家有幫助。 一、MySQL有哪些索引類型 索引 ...


MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。
資料庫查詢是資料庫的最主要功能之一，我們都希望查詢數據的速度能儘可能的快，因此資料庫系統的設計者會從查詢演算法的角度進行優化，這篇文章對索引做一個系統的梳理，希望對大家有幫助。

一、MySQL有哪些索引類型

索引的分類可以從多個角度進行，下麵分別從數據結構，物理存儲和業務邏輯三個維度進行劃分。

1、從數據結構角度

（1）B+樹索引(O(log(n)))

關於B+樹索引，後面會深入解析

（2）hash索引

  • 僅僅能滿足"=","IN"和"<=>"查詢，不能使用範圍查詢
  • 其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree 索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次的IO訪問，所以 Hash 索引的查詢效率要遠高於 B-Tree 索引
  • 只有Memory存儲引擎顯示支持hash索引

（3）FULLTEXT索引

現在MyISAM和InnoDB引擎都支持了

（4）R-Tree索引

用於對GIS數據類型創建SPATIAL索引

2、從物理存儲角度

（1）聚集索引（clustered index）

  • 正文內容按照一個特定維度排序存儲，這個特定的維度就是聚集索引；
  • Innodb存儲引擎中行記錄就是按照聚集索引維度順序存儲的，Innodb的表也稱為索引表；因為行記錄只能按照一個維度進行排序，所以一張表只能有一個聚集索引。

（2）非聚集索引（non-clustered index）

  • 索引是通過二叉樹的數據結構來描述的，我們可以這麼理解聚簇索引：索引的葉節點就是數據節點。而非聚簇索引的葉節點仍然是索引節點，只不過有一個指針指向對應的數據塊。

  • 非聚集索引索引項順序存儲，但索引項對應的內容卻是隨機存儲的；

舉個例子說明下：

create table student (
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY(`id`),
KEY(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

該表中主鍵id是該表的聚集索引、name為非聚集索引；表中的每行數據都是按照聚集索引id排序存儲的；比如要查找name='Arla'和name='Arle'的兩個同學，他們在name索引表中位置可能是相鄰的，但是實際存儲位置可能差的很遠。name索引表節點按照name排序，檢索的是每一行數據的主鍵。聚集索引表按照主鍵id排序，檢索的是每一行數據的真實內容。

3、從邏輯角度

（1）主鍵索引

主鍵索引是一種特殊的唯一索引，不允許有空值

（2）普通索引或者單列索引

（3）多列索引（複合索引）

複合索引指多個欄位上創建的索引，只有在查詢條件中使用了創建索引時的第一個欄位，索引才會被使用。使用複合索引時遵循最左首碼集合

（4）唯一索引或者非唯一索引

（5）空間索引

空間索引是對空間數據類型的欄位建立的索引，MYSQL中的空間數據類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL關鍵字進行擴展，使得能夠用於創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲引擎為MYISAM的表中創建.

二、索引創建方式

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]
  • unique|fulltext|spatial為可選參數，分別表示唯一索引、全文索引和空間索引；
  • index和key為同義詞，兩者作用相同，用來指定創建索引
  • col_name為需要創建索引的欄位列，該列必須從數據表中該定義的多個列中選擇；
  • index_name指定索引的名稱，為可選參數，如果不指定，MYSQL預設col_name為索引值；length為可選參數，表示索引的長度，只有字元串類型的欄位才能指定索引長度；
  • asc或desc指定升序或降序的索引值存儲

1、創建表時建立索引

（1）創建普通索引

create table table_name(
    id int(11),
    name varchar(20),
    sex boolean,
    INDEX(id)
);

查看表結構

show create table table_name;
可以使 EXPLAIN 語句查看索引是否被使用

explain select * from table_name where id = 1\G

（2）創建唯一索引

create table index2(
    id int unique,
    name varchar(20),
    unique INDEX index_2(id asc)
);

（3）創建全文索引

全文索引只能在char，varchar或者text 類型的欄位上。而且，只有MyISAM 儲存引擎支持全文索引。

create table idnex3(
    id int,
    info varchar(20),
    FULLTEXT INDEX index3_info(info)
)ENGINE=MyISAM;

（4）創建單列索引

create table index4(
    id int,
    subject varchar(255),
    index index4_st(subject(10))
);

這裡需要註意的，subject 的長度為255，但是index4_st索引只有10。這樣做的目的還是為了提高查詢速度。對於字元型的數據，可以不用查詢全部信息，只查詢其前面的若幹字元信息。

（5）創建多列索引

create table index5(
    id int,
    name varchar(20),
    sex char(4),
    index index5_ns(name.sex)
);

這是我們可以看到，name 和sex欄位上已經創建了index_ns索引。

2、在已經存在的表中創建索引

（1）創建普通索引

在example0() 表中的id 創建名為index7_id 的索引。

create index index7_id on example0(id);

（2）創建唯一索引

create UNIQUE index index_name on table_name(name);

（3）創建全文索引

create FULLTEXT index index_name on table_name(info);

（4）創建單列索引

create INDEX index_name ON table_name(name(10));

（5）創建多列索引

create INDEX index_name ON table_name(name,sex);

3、用alter table 語句來創建索引

（1）創建普通索引

在name欄位上創建名為indx_name 的索引

alter table table_name ADD INDEX index_name(name(20));

（2）創建唯一性索引

alter table table_name ADD UNIQUE INDEX index_name(id);

（3）創建全文索引

alter table table_name ADD FULLTEXT INDEX index_name(info);

（4）創建單列索引

alter table table_name ADD INDEX index_name(name(4));

（5）創建多列索引

alter tabel table_name ADD INDEX index_name(name.sex);

4、刪除索引

DROP INDEX index_name ON table_name;

三、索引樹是如何維護的

目前大部分資料庫系統及文件系統都採用B-Tree或其變種B+Tree作為索引結構，那麼索引樹是如何維護的？

1、查找結構進化史

查找是數據結構和演算法中一個非常重要的概念。

  • 線性查找：一個個找；實現簡單；太慢
  • 二分查找：有序；簡單；要求是有序的，插入特別慢
  • HASH查找：查詢快；占用空間；不太適合存儲大規模數據
  • 二叉查找樹：插入和查詢很快(log(n))；無法存大規模數據，複雜度退化
  • 平衡樹：解決 BST 退化問題，樹是平衡的；節點非常多的時候，依然樹高很高
  • 多路查找樹：一個父親多個孩子節點（度）；節點過多樹高不會特別深
  • 多路平衡查找樹：B-Tree

2、B-Tree

B-Tree是一種多路搜索樹（並不是二叉的）：

  1. 定義任意非葉子結點最多只有M個兒子；且M>2；
  2. 根結點的兒子數為[2, M]；
  3. 除根結點以外的非葉子結點的兒子數為[M/2, M]；
  4. 每個結點存放至少M/2-1（取上整）和至多M-1個關鍵字；（至少2個關鍵字）
  5. 非葉子結點的關鍵字個數=指向兒子的指針個數-1；
  6. 非葉子結點的關鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
  7. 非葉子結點的指針：P[1], P[2], …, P[M]；其中P[1]指向關鍵字小於K[1]的
    子樹，P[M]指向關鍵字大於K[M-1]的子樹，其它P[i]指向關鍵字屬於(K[i-1], K[i])的子樹；
  8. 所有葉子結點位於同一層；
  9. 每個k對應一個data。
    如：（M=3）相當於一個2–3樹，2–3樹是一個這樣的一棵樹， 它的每個節點要麼有2個孩子和1個數據元素，要麼有3個孩子和2個數據元素，葉子節點沒有孩子，並且有1個或2個數據元素。

B-樹的搜索，從根結點開始，對結點內的關鍵字（有序）序列進行二分查找，如果命中則結束，否則進入查詢關鍵字所屬範圍的兒子結點；重覆，直到所對應的兒子指針為空，或已經是葉子結點；B-Tree上查找演算法的偽代碼如下：

BTree_Search(node, key) { if(node == null) return null; foreach(node.key) { if(node.key[i] == key) return node.data[i]; if(node.key[i] > key) return BTree_Search(point[i]->node); } return BTree_Search(point[i+1]->node); } data = BTree_Search(root, my_key);

（1）B-樹的特性

  1. 關鍵字集合分佈在整顆樹中；
  2. 任何一個關鍵字出現且只出現在一個結點中；
  3. 搜索有可能在非葉子結點結束；
  4. 其搜索性能等價於在關鍵字全集內做一次二分查找；
  5. 自動層次控制；

（2）B-樹的自控制

B樹中每一個內部節點會包含一定數量的鍵值。通常，鍵值的數量被選定在d和2d之間。在實際中，鍵值占用了節點中大部分的空間。因數2將保證節點可以被拆分或組合。如果一個內部節點有2d個鍵值，那麼添加一個鍵值給此節點的過程，將會拆分2d鍵值為2個d鍵值的節點，並把此鍵值添加給父節點。每一個拆分的節點需要最小數目的鍵值。相似地，如果一個內部節點和他的鄰居兩者都有d個鍵值，那麼將通過它與鄰居的合併來刪除一個鍵值。刪除此鍵值將導致此節點擁有d-1個鍵值;與鄰居的合併則加上d個鍵值，再加上從鄰居節點的父節點移來的一個鍵值。結果為完全填充的2d個鍵值。

（3）B-樹的構造過程

下麵是往B樹中依次插入

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

3、B+Tree

B-Tree有許多變種，其中最常見的是B+Tree，MySQL就普遍使用B+Tree實現其索引結構。
與B-Tree相比，B+Tree有以下不同點：

  1. 非葉子結點的子樹指針與關鍵字個數相同；
  2. 非葉子結點的子樹指針P[i]，指向關鍵字值屬於[K[i], K[i+1])的子樹（B-樹是開區間）；
  3. 為所有葉子結點增加一個鏈指針；
  4. 所有關鍵字都在葉子結點出現；
  5. 內節點不存儲data，只存儲key
    如：（M=3）

B+的搜索與B-樹也基本相同，區別是B+樹只有達到葉子結點才命中（B-樹可以在非葉子結點命中），其性能也等價於在關鍵字全集做一次二分查找；

（1）B+的特性

  1. 所有關鍵字都出現在葉子結點的鏈表中（稠密索引），且鏈表中的關鍵字恰好是有序的；
  2. 不可能在非葉子結點命中；
  3. 非葉子結點相當於是葉子結點的索引（稀疏索引），葉子結點相當於是存儲（關鍵字）數據的數據層；
  4. 更適合文件索引系統；

（2）B+樹的構造過程

下麵是往B+樹中依次插入

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

3、索引的物理存儲

一般來說，索引本身也很大，不可能全部存儲在記憶體中，因此索引往往以索引文件的形式存儲的磁碟上。

這樣的話，索引查找過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗要高幾個數量級，所以評價一個數據結構作為索引的優劣最重要的指標就是在查找過程中磁碟I/O操作次數的漸進複雜度。換句話說，索引的結構組織要儘量減少查找過程中磁碟I/O的存取次數。

假如每個盤塊可以正好存放一個B樹的結點（正好存放2個文件名）。那麼一個BTNODE結點就代表一個盤塊，而子樹指針就是存放另外一個盤塊的地址。

（1）模擬B+樹查找過程

下麵，咱們來模擬下查找文件29的過程：

  1. 根據根結點指針找到文件目錄的根磁碟塊1，將其中的信息導入記憶體。【磁碟IO操作 1次】
  2. 此時記憶體中有兩個文件名17、35和三個存儲其他磁碟頁面地址的數據。根據演算法我們發現：17<29<35，因此我們找到指針p2。
  3. 根據p2指針，我們定位到磁碟塊3，並將其中的信息導入記憶體。【磁碟IO操作 2次】
  4. 此時記憶體中有兩個文件名26，30和三個存儲其他磁碟頁面地址的數據。根據演算法我們發現：26<29<30，因此我們找到指針p2。
  5. 根據p2指針，我們定位到磁碟塊8，並將其中的信息導入記憶體。【磁碟IO操作 3次】
  6. 此時記憶體中有兩個文件名28，29。根據演算法我們查找到文件名29，並定位了該文件記憶體的磁碟地址。
    分析上面的過程，發現需要3次磁碟IO操作和3次記憶體查找操作。關於記憶體中的文件名查找，由於是一個有序表結構，可以利用折半查找提高效率。至於IO操作是影響整個B樹查找效率的決定因素。
    當然，如果我們使用平衡二叉樹的磁碟存儲結構來進行查找，磁碟4次，最多5次，而且文件越多，B樹比平衡二叉樹所用的磁碟IO操作次數將越少，效率也越高。

4、B+tree的優點

（1）B+-tree的磁碟讀寫代價更低

B+-tree的內部結點並沒有指向關鍵字具體信息的指針。因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入記憶體中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
舉個例子，假設磁碟中的一個盤塊容納16bytes，而一個關鍵字2bytes，一個關鍵字具體信息指針2bytes。一棵9階B-tree(一個結點最多8個關鍵字)的內部結點需要2個盤快。而B+
樹內部結點只需要1個盤快。當需要把內部結點讀入記憶體中的時候，B 樹就比B+樹多一次盤塊查找時間(在磁碟中就是碟片旋轉的時間)。

（2）B+-tree的查詢效率更加穩定

由於非終結點並不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。

四、索引創建有哪些原則

索引查詢是資料庫中重要的記錄查詢方法，要不要進入索引以及在那些欄位上建立索引都要和實際資料庫系統的查詢要求結合來考慮，下麵給出實際中的一些通用的原則：

  1. 在經常用作過濾器的欄位上建立索引；
  2. 在SQL語句中經常進行GROUP BY、ORDER BY的欄位上建立索引；
  3. 在不同值較少的欄位上不必要建立索引，如性別欄位；
  4. 對於經常存取的列避免建立索引；
  5. 用於聯接的列（主健/外健）上建立索引；
  6. 在經常存取的多個列上建立複合索引，但要註意複合索引的建立順序要按照使用的頻度來確定；
  7. 預設情況下建立的是非簇集索引，但在以下情況下最好考慮簇集索引，如：含有有限數目（不是很少）唯一的列；進行大範圍的查詢；充分的利用索引可以減少表掃描I/0的次數，有效的避免對整表的搜索。
  8. 經常用在WHERE子句中的數據列；
  9. 經常出現在關鍵字order by、group by、distinct後面的欄位，建立索引。如果建立的是複合索引，索引的欄位順序要和這些關鍵字後面的欄位順序一致，否則索引不會被使用；
  10. 對於那些查詢中很少涉及的列，重覆值比較多的列不要建立索引；
  11. 對於定義為text、image和bit的數據類型的列不要建立索引；
  12. 對於經常存取的列避免建立索引；
  13. 限製表上的索引數目。對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個。索引雖說提高了訪問速度，但太多索引會影響數據的更新操作。
  14. 對複合索引，按照欄位在查詢條件中出現的頻度建立索引。在複合索引中，記錄首先按照第一個欄位排序。對於在第一個欄位上取值相同的記錄，系統再按照第二個欄位的取值排序，以此類推。因此只有複合索引的第一個欄位出現在查詢條件中，該索引才可能被使用,因此將應用頻度高的欄位，放置在複合索引的前面，會使系統最大可能地使用此索引，發揮索引的作用。

1、組合多個索引

一個單獨的索引掃描只能用於這樣的條件子句：使用被索引欄位和索引操作符類中的操作符， 並且這些條件以AND連接。

假設在(a, b)上有一個索引， 那麼類似WHERE a = 5 AND b = 6的條件可以使用索引，但是像WHERE a = 5 OR b = 6的條件就不能直接使用索引。

一個類似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 這樣的查詢可以分解成四個在x上的獨立掃描，每個掃描使用一個條件， 最後將這些掃描的結果OR 在一起，生成最終結果。

另外一個例子是，如果我們在x 和y上有獨立的索引，一個類似WHERE x = 5 AND y = 6 這樣的查詢可以分解為幾個使用獨立索引的子句，然後把這幾個結果AND 在一起，生成最終結果。

五、索引失效有哪幾種情況

  1. 如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什麼儘量少用or的原因)
  2. 對於多列索引，不是使用的第一部分(第一個)，則不會使用索引
  3. like查詢是以%開頭
  4. 如果列類型是字元串，那一定要在條件中將數據使用引號引用起來,否則不使用索引
  5. 如果mysql估計使用全表掃描要比使用索引快,則不使用索引

1、聯合索引失效的條件

聯合索引又叫複合索引。兩個或更多個列上的索引被稱作複合索引。

對於複合索引：Mysql從左到右的使用索引中的欄位，一個查詢可以只使用索引中的一部份，但只能是最左側部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 .當最左側欄位是常量引用時，索引就十分有效。

所以說創建複合索引時，應該仔細考慮列的順序。對索引中的所有列執行搜索或僅對前幾列執行搜索時，複合索引非常有用；僅對後面的任意列執行搜索時，複合索引則沒有用處。

六、如何查看索引的使用情況

這裡記錄兩種方式，分別是

1、使用Handler_read查看索引的使用情況

show status like ‘Handler_read%';

大家可以註意：

  • handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數
  • handler_read_rnd_next:這個值越高，說明查詢低效
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Handler_read_first    | 153      |
| Handler_read_key      | 364   |
| Handler_read_next     | 425    |
| Handler_read_prev     | 598     |
| Handler_read_rnd      | 605     |
| Handler_read_rnd_next | 860571 |
+-----------------------+--------------+
6 rows in set (0.00 sec)
————————————————

分析這幾個值，我們可以查看當前索引的使用情況：

  • Handler_read_first：索引中第一條被讀的次數。如果較高，它表示伺服器正執行大量全索引掃描；例如，SELECT col1 FROM foo，假定col1有索引（這個值越低越好）。
  • Handler_read_key：如果索引正在工作，這個值代表一個行被索引值讀的次數，如果值越低，表示索引得到的性能改善不高，因為索引不經常使用（這個值越高越好）。
  • Handler_read_next ：按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列，該值增加。
  • Handler_read_prev：按照鍵順序讀前一行的請求數。該讀方法主要用於優化ORDER BY ... DESC。
  • Handler_read_rnd ：根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連接沒有正確使用鍵。這個值較高，意味著運行效率低，應該建立索引來補救。
  • Handler_read_rnd_next：在數據文件中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。

2、在sys庫中查看沒用的索引

查詢 schema_unused_indexes庫。

root@localhost [sys]>select * from schema_unused_indexes;
+-------------------+-------------+------------+
| object_schema     | object_name | index_name |
+-------------------+-------------+------------+
| sysbench_testdata | sbtest1     | k_1        |
| sysbench_testdata | sbtest10    | k_10       |
| sysbench_testdata | sbtest3     | k_3        |
| sysbench_testdata | sbtest4     | k_4        |
| sysbench_testdata | sbtest5     | k_5        |
| sysbench_testdata | sbtest6     | k_6        |
| sysbench_testdata | sbtest7     | k_7        |
| sysbench_testdata | sbtest8     | k_8        |
| sysbench_testdata | sbtest9     | k_9        |
+-------------------+-------------+------------+
9 rows in set (0.00 sec)

七、EXPLAIN解釋命令查看索引是否生效

explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。

1、一個實際例子

新建一張表，

CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);

執行查詢，

EXPLAIN
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1

響應數據如下，

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

type 是 ALL,即最壞的情況。Extra 里還出現了 Using filesort,也是最壞的情況。

2、EXPLAIN列的解釋：

  • table：顯示這一行的數據是關於哪張表的
  • type：這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL
  • possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句
  • key： 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MYSQL忽略索引
  • key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
  • ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數
  • rows：MYSQL認為必須檢查的用來返回請求數據的行數
  • Extra：關於MYSQL如何解析查詢的額外信息。將在表4.3中討論，但這裡可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結果是檢索會很慢

3、type返回結果的解釋

MySQL 在表裡找到所需行的方式。包括（由左至右，由最差到最好）：
| All | index | range | ref | eq_ref | const,system | null |

  • system 表只有一行：system表。這是const連接類型的特殊情況
  • const:表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MYSQL先讀這個值然後把它當做常數來對待
  • eq_ref:在連接中，MYSQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用
  • ref:這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊首碼）時發生。對於之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴於根據索引匹配的記錄多少—越少越好
  • range:這個連接類型使用索引返回一個範圍中的行，比如使用>或<查找東西時發生的情況
  • index: 這個連接類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小於表數據）
  • ALL:這個連接類型對於前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該儘量避免

4、extra列返回的描述的意義

  • Distinct:一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了
  • Not exists: MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了
  • Range checked for each Record（index map:#）:沒有找到理想的索引，因此對於從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的連接之一
  • Using filesort: 看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行
  • Using index: 列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對錶的全部的請求列都是同一個索引的部分的時候
  • Using temporary 看到這個的時候，查詢需要優化了。這裡，MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上
  • Using where 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，並且連接類型ALL或index，這就會發生，或者是查詢有問題不同連接類型的解釋（按照效率高低的順序排序）

參考文檔

最官方的 mysql explain type 欄位解讀
MySQL聚集索引和非聚集索引


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • JS高階編程技巧--柯理化函數
    首先看一段代碼： let obj = { x: 100 }; function fn(y) { this.x += y; console.log(this); } 現在有一個需求：在1秒後，執行函數fn，並讓其this指向obj。 如果寫成 setTimeout(fn, 1000); 這麼寫的話，f ...
  • java設計模式3——建造者模式
    1、建造者模式介紹： 2、建造者模式角色分析 3、方式一：建造模式的常規用法（有指揮） 4、方式二：用戶可以自定義套餐和選擇預設套餐（沒有指揮者） 5、優點分析： 6、缺點分析： 7、應用場景： 8、建造者模式與抽象工廠模式的比較 ...
  • 分散式系統的CAP定理
    CAP定理： 在一個分散式系統中，Consistency（數據一致性）、 Availability（服務可用性）、Partition tolerance（分區容錯性），三者不可兼得。 一致性(Consistency) 在分散式系統中的所有數據備份（副本），在同一時刻數據的值是否一致。（等同於所有節點 ...
  • gRPC in ASP.NET Core 3.x -- Protocol Buffer（3）更新消息類型
    當你第一次定義Protocol Buffer的消息的時候，你肯定會給消息設定一套規則需求。但是隨著時間的推進，你的業務可能會發生了變化，與此同時，你的Protocol Buffer消息類型的需求也會隨之變化。 也就是說：有一些欄位可能會發生變化，可能會添加一些欄位，也可能會刪除一些欄位。但是可能有很 ...
  • 瞭解反射
    00.瞭解反射 請從記憶體的角度分析對象創建的過程 1.找到主函數所在的類,該類.class載入到方法區,通過反射調用這個主方法,方法進棧 01.瞭解IDE,類路徑 02.位元組碼對象Class 源代碼部分截圖: 知識點1: 3種獲取Class位元組碼對象的方式及優缺點 知識點2: 通過位元組碼Class創 ...
  • 實用代碼，批量下載手機壁紙！
    前言 今天一番在22:30開始準備今日的日更，冒著極大的斷更風險，研究了一個開源項目，批量下載手機壁紙。 因為一番每天都為文章開頭的配圖撓頭，正向從網上批量抓取一些美圖，以充實庫存。 剛好，一番昨日的文章里有這麼一個抓取手機背景圖片的開源項目，於是一番今天嘗試了下。 要知道，一般調試一段未知代碼，一 ...
  • Python爬蟲連載7-cookie的保存與讀取、SSL講解
    一、cookie的保存與讀取 1.cookie的保存-FileCookie.Jar from urllib import request,parse from http import cookiejar #創建cookiejar實例 filename = "cookie.txt" cookie = ...
  • JavaSE學習筆記（12）---線程
    JavaSE學習筆記（12） 線程 多線程 併發與並行 併發 ：指兩個或多個事件在 同一個時間段內 發生。 並行 ：指兩個或多個事件在 同一時刻 發生（同時發生）。 在操作系統中，安裝了多個程式，併發指的是在一段時間內巨集觀上有多個程式同時運行，這在單 CPU 系統中，每一時刻只能有一道程式執行，即微 ...
一周排行
    -Advertisement-
    Play Games
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類