騰訊 WXG 后臺開發(fā)工程師對 MySQL 索引知識點(diǎn)總結(jié)

知其然知其所以然！本文介紹索引的數(shù)據(jù)結(jié)構(gòu)、查找算法、常見的索引概念和索引失效場景。

什么是索引？

在關(guān)系數(shù)據(jù)庫中，索引是一種單獨(dú)的、物理的對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種存儲結(jié)構(gòu)，它是某個表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單。索引的作用相當(dāng)于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。（百度百科）

索引的目的是提高查找效率，對數(shù)據(jù)表的值集合進(jìn)行了排序，并按照一定數(shù)據(jù)結(jié)構(gòu)進(jìn)行了存儲。

本文將從一個案例開始，從索引的數(shù)據(jù)結(jié)構(gòu)、分類、關(guān)鍵概念及如何使用索引提高查找效率等方面對索引知識進(jìn)行總結(jié)。

從一個案例開始

現(xiàn)象

業(yè)務(wù)中有個既存的歷史 SQL 語句在運(yùn)行時會導(dǎo)致 DB 服務(wù)器過載，進(jìn)而導(dǎo)致相關(guān)服務(wù)阻塞無法及時完成。CPU 監(jiān)控曲線如下：

image
image.gif

?

image
image.gif

?

從 DB 的 CPU 使用率曲線可以看到業(yè)務(wù)運(yùn)行一直處于“亞健康”狀態(tài)（1），隨著業(yè)務(wù)的增長隨時都可能出現(xiàn)問題。這種問題（2）在 11 月 11 日凌晨出現(xiàn)，當(dāng)時 DB CPU 一直處于 100%高負(fù)荷狀態(tài)，且存在大量的慢查詢語句。最終以殺死進(jìn)程降低 DB 負(fù)載、減少業(yè)務(wù)進(jìn)程（3）的方式恢復(fù)業(yè)務(wù)。

在 11 月 11 日下午，對該業(yè)務(wù)的 SQL 語句進(jìn)行了優(yōu)化，優(yōu)化的效果如下。業(yè)務(wù)運(yùn)行時的 CPU 使用率峰值有很大的降低（對比圖 2 的 1，2，3 可見）；慢查詢語句幾乎在監(jiān)控曲線上也無法明顯觀察到（對比圖 3 的 1，2，3 可見）。

image
image.gif

?

image
image.gif

?

分析

表結(jié)構(gòu)

<pre>CREATE TABLE T_Mch******Stat (FStatDate int unsigned NOT NULL DEFAULT 19700101 COMMENT '統(tǒng)計日期',
FMerchantId bigint unsigned NOT NULL DEFAULT 0 COMMENT '商戶ID',
FVersion int unsigned NOT NULL DEFAULT 0 COMMENT '數(shù)據(jù)版本號',
FBatch bigint unsigned NOT NULL DEFAULT 0 COMMENT '統(tǒng)計批次',
FTradeAmount bigint NOT NULL DEFAULT 0 COMMENT '交易金額'
PRIMARY KEY (FStatDate,FMerchantId,FVersion),
INDEX i_FStatDate_FVersion (FStatDate,FVersion))
DEFAULT CHARSET = utf8 ENGINE = InnoDB;</pre>

從建表語句可以知道該表有兩個索引：

  1. 主鍵索引，是一個組合索引，由字段 FStateDate、FMerchantId 和 FVersion 組成；
  2. 普通索引，是一個組合索引，由字段 FStateDate 和 FVersion 組成；

優(yōu)化前的 SQL 語句（做了部分裁剪）A：

<pre>SELECT SQL_CALC_FOUND_ROWS FStatDate,
FMerchantId,
FVersion,
FBatch,
FTradeAmount,
FTradeCount
FROM T_Mch******Stat_1020
WHERE FStatDate = 20201020
AND FVersion = 0
AND FMerchantId > 0
ORDER BY FMerchantId ASC LIMIT 0, 8000</pre>

對該 SQL 進(jìn)行 explain 得到如下結(jié)果，Extra 字段的值為 using where，說明并沒有使用到索引。

image
image.gif

?

優(yōu)化后的 SQL 語句（做了部分裁剪）B：

<pre>SELECT SQL_CALC_FOUND_ROWS a1.FStatDate,
a1.FMerchantId,
a1.FVersion,
FBatch,
FTradeAmount,
FTradeCount
FROM T_Mch******Stat_1020 a1, (
SELECT FStatDate, FMerchantId, FVersion
FROM T_Mch******Stat_1020
WHERE FStatDate = 20201020
AND FVersion = 0
AND FMerchantId > 0
ORDER BY FMerchantId ASC LIMIT 0, 8000 ) a2
where a1.FStatDate = a2.FStatDate
and a1.FVersion = a2.FVersion
and a1.FMerchantId = a2.FMerchantId;</pre>

優(yōu)化關(guān)鍵步驟為：

  • 新增一個子查詢，select 字段只有主鍵字段；

該 SQL 的 explain 結(jié)果如下，子查詢語句使用了索引，而最終在線上運(yùn)行結(jié)果也證明了優(yōu)化效果顯著。

image
image.gif

?

疑問

優(yōu)化后的 SQL 語句 B 比原來的 SQL 語句 A 復(fù)雜的多（子查詢，臨時表關(guān)聯(lián)等），怎么效率會提升，違反直覺？有三個疑問：

  1. SQL 語句 A 的查詢條件字段都在主鍵中，主鍵索引用到了沒？
  2. SQL 語句 B 的子查詢?yōu)槭裁茨軌蛴玫剿饕?/li>
  3. 前后兩條語句執(zhí)行流程的差異是什么？

索引的數(shù)據(jù)結(jié)構(gòu)

在 MySQL 中，索引是在存儲引擎層實現(xiàn)的，而不同的存儲引擎根據(jù)其業(yè)務(wù)場景特點(diǎn)會有不同的實現(xiàn)方式。這里會先介紹我們常見的有序數(shù)組、Hash 和搜索樹，最后看下 Innodb 的引擎支持的 B+樹。

有序數(shù)組

數(shù)組是在任何一本數(shù)據(jù)結(jié)構(gòu)和算法的書籍都會介紹到的一種重要的數(shù)據(jù)結(jié)構(gòu)。有序數(shù)組如其字面意思，以 Key 的遞增順序保存數(shù)據(jù)在數(shù)組中。非常適合等值查詢和范圍查詢。

| ID：1 | ID：2 | ...... | ID:N |

在 ID 值沒有重復(fù)的情況下，上述數(shù)組按照 ID 的遞增順序進(jìn)行保存。這個時候如果需要查詢特定 ID 值的 name，用二分法就可以快速得到，時間復(fù)雜度是 O(logn)。

<pre>// 二分查找遞歸實現(xiàn)方式
int binary_search(const int arr[], int start, int end, int key)
{
if (start > end)
return -1;

int mid = start + (end - start) / 2;
if (arr[mid] > key)
    return binary_search(arr, start, mid - 1, key);
else if (arr[mid] < key)
    return binary_search(arr, mid + 1, end, key);
else
    return mid;

}</pre>

有序數(shù)組的優(yōu)點(diǎn)很明顯，同樣其缺點(diǎn)也很明顯。其只適合靜態(tài)數(shù)據(jù)，如遇到有數(shù)據(jù)新增插入，則就會需要數(shù)據(jù)移動（新申請空間、拷貝數(shù)據(jù)和釋放空間等動作），這將非常消耗資源。

Hash

哈希表是一種以鍵-值（K-V）存儲數(shù)據(jù)的結(jié)構(gòu)，我們只需要輸入鍵 K，就可以找到對應(yīng)的值 V。哈希的思路是用特定的哈希函數(shù)將 K 換算到數(shù)組中的位置，然后將值 V 放到數(shù)組的這個位置。如果遇到不同的 K 計算出相同的位置，則在這個位置拉出一個鏈表依次存放。哈希表適用于等值查詢的場景，對應(yīng)范圍查詢則無能為力。

image
image.gif

?

二叉搜索樹

二叉搜索樹，也稱為二叉查找樹、有序二叉樹或排序二叉樹，是指一顆空樹或者具有以下性質(zhì)的二叉樹：

  1. 若任意節(jié)點(diǎn)的左子樹不空，則左子樹上所有節(jié)點(diǎn)的值均小于它的根節(jié)點(diǎn)的值；
  2. 若任意節(jié)點(diǎn)的右子樹不空，則右子樹上所有節(jié)點(diǎn)的值均大于或等于它的根節(jié)點(diǎn)的值；
  3. 任意節(jié)點(diǎn)的左、右子樹也分別為二叉查找樹；
image
image.gif

?

二叉搜索樹相比于其它數(shù)據(jù)結(jié)構(gòu)的優(yōu)勢在于查找、插入的時間復(fù)雜度較低，為 O(logn)。為了維持 O(logn)的查詢復(fù)雜度，需要保持這棵樹是平衡二叉樹。

二叉搜索樹的查找算法：

  1. 若 b 是空樹，則搜索失敗，否則：
  2. 若 x 等于 b 的根節(jié)點(diǎn)的值，則查找成功；否則：
  3. 若 x 小于 b 的根節(jié)點(diǎn)的值，則搜索左子樹；否則：
  4. 查找右子樹。

相對于有序數(shù)組和 Hash，二叉搜索樹在查找和插入兩端的表現(xiàn)都非常不錯。后續(xù)基于此不斷的優(yōu)化，發(fā)展出 N 叉樹等。

B+樹

Innodb 存儲引擎支持 B+樹索引、全文索引和哈希索引。其中 Innodb 存儲引擎支持的哈希索引是自適應(yīng)的，Innodb 存儲引擎會根據(jù)表的使用情況自動為表生成哈希索引，不能人為干預(yù)。B+樹索引是關(guān)系型數(shù)據(jù)庫中最常見的一種索引，也將是本文的主角。

數(shù)據(jù)結(jié)構(gòu)

在前文簡單介紹了有序數(shù)組和二叉搜索樹，對二分查找法和二叉樹有了基本了解。B+樹的定義相對復(fù)雜，在理解索引工作機(jī)制上無須深入、只需理解數(shù)據(jù)組織形式和查找算法即可。我們可以簡單的認(rèn)為 B+樹是一種 N 叉樹和有序數(shù)組的結(jié)合體。

例如：

image
image.gif

?

B+樹的 3 個優(yōu)點(diǎn):

  1. 層級更低，IO 次數(shù)更少
  2. 每次都需要查詢到葉子節(jié)點(diǎn)，查詢性能穩(wěn)定
  3. 葉子節(jié)點(diǎn)形成有序鏈表，范圍查詢方便

操作算法

  • 查找

由根節(jié)點(diǎn)自頂向下遍歷樹，根據(jù)分離值在要查找的一邊的指針；在節(jié)點(diǎn)內(nèi)使用二分查找來確定位置。

  • 插入

| Leaf Page（葉子）滿 | Index Page（索引）滿 | 操作 |

image
image.gif

?

  • 刪除

| 葉子節(jié)點(diǎn)小于填充因子 | 中間節(jié)點(diǎn)小于填充因子 | 操作 |

image
image.gif

?

注：插入和刪除兩個表格內(nèi)容來自《MySQL 技術(shù)內(nèi)幕-InnoDB 存儲引擎》

填充因子（innodb_fill_factor）：索引構(gòu)建期間填充的每個 B-tree 頁面上的空間百分比，其余空間保留給未來索引增長。從插入和刪除操作中可以看到填充因子的值會影響到數(shù)據(jù)頁的 split 和 merge 的頻率。將值設(shè)置小些，可以減少 split 和 merge 的頻率，但是索引相對會占用更多的磁盤空間；反之，則會增加 split 和 merge 的頻率，但是可以減少占用磁盤空間。Innodb 對于聚集索引默認(rèn)會預(yù)留 1/16 的空間保證后續(xù)的插入和升級索引。

Innodb B+樹索引

前文介紹了索引的基本數(shù)據(jù)結(jié)構(gòu)，現(xiàn)在開始我們從 Innodb 的角度了解如何使用 B+樹構(gòu)建索引，索引如何工作和如何使用索引提升查找效率。

聚集索引和非聚集索引

數(shù)據(jù)庫中的 B+樹索引可以分為聚集索引和非聚集索引。聚集索引和非聚集索引的不同點(diǎn)在于葉子節(jié)點(diǎn)是否是完整行數(shù)據(jù)。

Innodb 存儲引擎表是索引組織表，即表中的數(shù)據(jù)按照主鍵順序存放。聚集索引就是按照每張表的主鍵構(gòu)造一棵 B+樹，葉子節(jié)點(diǎn)存放的是表的完整行記錄。非聚集索引的葉子節(jié)點(diǎn)不包含行記錄的全部數(shù)據(jù)。Innodb 存儲引擎的非聚集索引的葉子節(jié)點(diǎn)的內(nèi)容為主鍵索引值。

若數(shù)據(jù)表沒有主鍵聚集索引是怎么建立的？在沒有主鍵時 Innodb 會給數(shù)據(jù)表的每條記錄生成一個 6 個字節(jié)長度的 RowId 字段，會以此建立聚集索引。

Select 語句查找記錄的過程

下面例子將展示索引數(shù)據(jù)的組織形式及 Select 語句查詢數(shù)據(jù)的過程。

  • 建表語句：

<pre>create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k)
) engine=InnoDB DEFAULT CHARSET=utf8;

insert into T values(100, 1, 'aa'),(200, 2, 'bb'),(300, 3, 'cc'),(500, 5, 'ee'),(600,6,'ff'),(700,7,'gg');</pre>

  • 索引結(jié)構(gòu)示意
image
image.gif

?

左邊是以主鍵 ID 建立起的聚集索引，其葉子節(jié)點(diǎn)存儲了完整的表記錄信息；右邊是以普通字段 K 建立的普通索引，其葉子節(jié)點(diǎn)的值是主鍵 ID。

  • Select 語句執(zhí)行過程

<pre>select * from T where k between 3 and 5;</pre>

執(zhí)行流程如下：

  1. 在 K 索引樹上找到 k=3 的記錄，取得 ID=300；
  2. 再到 ID 索引樹上查找 ID=300 對應(yīng)的 R3；
  3. 在 k 索引樹取下一個值 k=5，取得 ID=500；
  4. 再回到 ID 索引樹查到 ID=500 對應(yīng)的 R4；
  5. 在 k 索引樹取下一個值 k=6，不滿足條件，循環(huán)結(jié)束。

上述查找記錄的過程中引入了一個重要的概念： 回表 ，即回到主鍵索引樹搜索的過程。避免回表操作是提升 SQL 查詢效率的常規(guī)思路及重要方法。那么如何避免回表？

注：該例子來自《MySQL 實戰(zhàn) 45 講》

覆蓋索引

MySQL 5.7,建表語句：

<pre>CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender enum('M','F') NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no),
KEY i_first_name (first_name),
KEY i_hire_date (hire_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</pre>

  • SQL 語句 A

<pre>explain select * from employees where hire_date > '1990-01-14';</pre>

explain 結(jié)果：

image
image.gif

?

  • SQL 語句 B

<pre>explain select emp_no from employees where hire_date > '1990-01-14';</pre>

explain 結(jié)果：

image
image.gif

?

  • 分析

從前后兩次 explain 的結(jié)果可以看到 SQL 語句 A 的 extra 為 using where，SQL 語句 B 的 extra 為 using where;using index。這說明 A 沒有使用索引，而 B 使用了索引。

索引 K 中包含了查詢語句所需要的字段 ID 的值，無需再次回到主鍵索引樹查找，也就是“覆蓋”了我們的查詢需求，我們稱之為覆蓋索引。覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能。

最左匹配

  • SQL 語句 A

<pre>explain select * from employees where hire_date > '1990-01-14' and first_name like '%Hi%';</pre>

image
image.gif

?

  • SQL 語句 B

<pre>explain select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';</pre>

image
image.gif

?

  • 分析

在上述測試的 SQL 語句 A 使用了極端方式: first_name like '%Hi%'，前后都增加模糊匹配使得 SQL 語句無法使用到索引；當(dāng)去掉最左邊的‘%’后，SQL 語句 B 就使用了索引。最左匹配可以是字符串索引的最左 N 個字符，也可以是聯(lián)合索引的最左 M 的字段。合理規(guī)劃、使用最左匹配可以減少索引，從而節(jié)約磁盤空間。

索引下推

何為索引下推？我們先從下面這組對比測試開始，將在 MySQL5.5 版本和 MySQL5.7 版本中執(zhí)行同一條 SQL 語句：

<pre>select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';</pre>

  • 在 MySQL 5.5 執(zhí)行 explain，extra 字段的值顯示沒有使用索引
image
image.gif

?

執(zhí)行查詢花費(fèi)時間為 0.12s

image
image.gif

?

  • 在 MySQL 5.7 執(zhí)行 explain，extra 字段的值顯示使用了索引下推
image
image.gif

?

執(zhí)行查詢花費(fèi)時間為 0.02s

image
image.gif

?

  • 索引下推

explain 結(jié)果中的 extra 字段值包含 using index condition，則說明使用了索引下推。索引下推功能是從 5.6 版本開始支持的。在 5.6 版本之前，i_first_name 索引是沒有使用上的，需要每次去主鍵索引表取完整的記錄值進(jìn)行比較。從 5.6 版本開始，由于索引 i_first_name 的存在，可以直接取索引的 first_name 值進(jìn)行過濾，這樣不符合"first_name like 'Hi%'"條件的記錄就不再需要回表操作。

MRR 優(yōu)化

MySQL 5.6 版本開始支持 Multi-Range Read(MRR)優(yōu)化，MRR 優(yōu)化的目的是為減少磁盤的隨機(jī)訪問，并且將隨機(jī)訪問轉(zhuǎn)化為較為順序的數(shù)據(jù)訪問，對于 IO-bound 類型的 SQL 查詢語句可帶來性能極大提升。我們先看下對比測試，以下測試語句在同一個 MySQL 實例下執(zhí)行，執(zhí)行前均進(jìn)行 mysql 服務(wù)重啟，以保證緩存此沒被預(yù)熱。

  • 關(guān)閉 MRR

<pre>SET @@optimizer_switch='mrr=off';
select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';</pre>

執(zhí)行耗時未 0.90s

image
image.gif

?

  • 開啟 MRR

<pre>SET @@optimizer_switch='mrr=on,mrr_cost_based=off';
select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';</pre>

image
image.gif

?

  • 分析

從測試結(jié)果可以發(fā)現(xiàn)在 mrr 從關(guān)閉到開啟，耗時從 0.90s 減少到 0.03s，查詢速率達(dá)到 30 倍的提升。

常見的索引失效場景

在 MySQL 表中建立了索引，SQL 查詢語句就會一定使用到索引么？不一定，存在著索引失效的場景。我們給 employees 表增一個組合索引，后續(xù)例子均基于此表進(jìn)行分析、測試。

<pre>alter table employees add index i_b_f_l(birth_date, first_name, last_name)
alter table employees add index i_h(hire_date);</pre>

image
image.gif

?

失效場景

  • 范圍查詢（>,<,<>）

<pre>explain select * from employees where hire_date > '1989-06-02';</pre>

image
image.gif

?

  • 查詢條件類型不一致

<pre>alter table employees add index i_first_name (first_name);
explain select * from employees where first_name = 1;</pre>

image
image.gif

?

  • 查詢條件使用了函數(shù)

<pre>explain select * from employees where CHAR_LENGTH(hire_date) = 10;</pre>

image
image.gif

?

  • 模糊查詢

<pre>explain select * from employees where hire_date like '%1995';</pre>

image
image.gif

?

  • 不使用組合索引的首個字段當(dāng)條件

<pre>explain select * from employees where last_name = 'Kalloufi' and first_name = 'Saniya';</pre>

image
image.gif

?

為什么會失效？

  • 順序讀比離散讀性能要好

    范圍查詢一定會導(dǎo)致索引失效么？

    并不會！稍微更改下查詢條件看下 explain 的對比結(jié)果，可以看到新語句用到索引下推，說明索引并未失效。為什么？

    在不使用覆蓋索引的情況下，優(yōu)化器只有在數(shù)據(jù)量小的時候才會選擇使用非聚集索引。受制于傳統(tǒng)的機(jī)械磁盤特性，通過聚集索引順序讀數(shù)據(jù)行的性能會比通過非聚集索引離散讀數(shù)據(jù)行要好。所以，優(yōu)化器在即使有非聚集索引、但是訪問數(shù)據(jù)量可能達(dá)到送記錄數(shù)的 20%時會選擇聚集索引。當(dāng)然也可以用 Force index 強(qiáng)制使用索引。

<pre>explain select * from employees where hire_date > '1999-06-02';</pre>

image
image.gif

?

  • 無法使用 B+索引快速查找

    B+樹索引支持快速查詢的基本要素是因為其索引鍵值是有序存儲的，從左到右由小到大，這樣就可以在每個層級的節(jié)點(diǎn)中快速查并進(jìn)入下一層級，最終在葉子節(jié)點(diǎn)找到對應(yīng)的值。

    使用函數(shù)會使得 MySQL 無法使用索引進(jìn)行快速查詢，因為對索引字段做函數(shù)操作會破壞索引值的有序性，所以優(yōu)化器選擇不使用索引。而查詢條件類型不一致其實也是同樣的情況，因為其使用了隱式類型轉(zhuǎn)換*。

模糊匹配和不使用組合索引的首字段作為查詢條件均是無法快速定位索引位置從而導(dǎo)致無法使用索引。模糊匹配當(dāng)查詢條件是 lwhere A ike 'a%'，a 是 A 的最左前綴時是可能用上索引的（最左匹配），是否用上最終還是依賴優(yōu)化器對查詢數(shù)據(jù)量的評估。

回到初始的案例

讓我們回到文章初的案例，嘗試回答下當(dāng)時提出的 3 個問題。

<pre>-- A語句
SELECT FStatDate, FMerchantId, FVersion, FBatch, FTradeAmount, FTradeCount FROM T_Mch******Stat_1020 WHERE FStatDate = 20201020 AND FVersion = 0 AND FMerchantId > 0 ORDER BY FMerchantId ASC LIMIT 0, 8000;

-- B語句
SELECT SQL_CALC_FOUND_ROWS a1.FStatDate,
a1.FMerchantId,
a1.FVersion,
FBatch,
FTradeAmount,
FTradeCount
FROM T_Mch******Stat_1020 a1, (
SELECT FStatDate, FMerchantId, FVersion
FROM T_Mch******Stat_1020
WHERE FStatDate = 20201020
AND FVersion = 0
AND FMerchantId > 0
ORDER BY FMerchantId ASC LIMIT 0, 8000 ) a2
where a1.FStatDate = a2.FStatDate
and a1.FVersion = a2.FVersion
and a1.FMerchantId = a2.FMerchantId;</pre>

SQL 語句 A 的查詢條件字段都在主鍵中，主鍵索引用到了沒？

主鍵索引其實是有被使用的：索引的范圍查詢，只是其需要逐條讀取和解析所有記錄才導(dǎo)致慢查詢。

SQL 語句 B 的子查詢?yōu)槭裁茨軌蛴玫剿饕?/strong>

  1. 前文中我們介紹了聚集索引，其索引鍵值就是主鍵。
  2. 兩條 SQL 語句的不同之處在于 B 語句的子查詢語句的 Select 字段都包含在主鍵字段中，而 A 語句還有其它字段（例如 FBatch 和 FTradeAmount 等）。這種情況下只憑主鍵索引的鍵值就能滿足 B 語句的字段要求；A 語句則需要逐條取整行記錄進(jìn)行解析。

前后兩條語句執(zhí)行流程的差異是什么？

  • SQL 語句 A 的執(zhí)行過程：
  1. 逐條掃描索引表并比較查詢條件
  2. 遇到符合查詢條件的則讀取整行數(shù)據(jù)返回
  3. 回到 a 步驟，直至完成所有索引記錄的比較
  4. 對返回的所有符合條件的記錄（完整的記錄）進(jìn)行排序
  5. 選取前 8000 條數(shù)據(jù)返回
  • SQL 語句 B 的執(zhí)行過程：
  1. 逐條掃描索引表并比較查詢條件
  2. 遇到符合查詢條件的則從索引鍵中取相關(guān)字段值返回
  3. 回到 a 步驟，直至完成所有索引記錄的比較
  4. 對返回的所有符合條件的記錄（每條記錄只有 3 個主鍵）進(jìn)行排序
  5. 選取前 8000 條數(shù)據(jù)返回形成臨時表
  6. 關(guān)聯(lián)臨時表與主表，使用主鍵相等比較查詢 8000 條數(shù)據(jù)
  • 對比兩個 SQL 語句的執(zhí)行過程，可以發(fā)現(xiàn)差異點(diǎn)集中在步驟 2 和步驟 4。在步驟 2 中 SQL 語句 A 需要隨機(jī)讀取整行數(shù)據(jù)并解析非常耗資源；步驟 4 涉及 MySQL 的排序算法，這里也會對執(zhí)行效率有影響，排序效果上看 SQL 語句 B 比 SQL 語句 A 好。
image
image.gif

?

名詞解釋

  • 主鍵索引

顧名思義該類索引由表的主鍵組成，從左到右由小到大排序。一個 Innodb 存儲表只有一張主鍵索引表（聚集索引）。

  • 普通索引

最為平常的一種索引，沒有特別限制。

  • 唯一索引

該索引的字段不能有相同值，但允許有空值。

  • 組合索引

由多列字段組合而成的索引，往往是為了提升查詢效率而設(shè)置。

總結(jié)

在文章開始時介紹了常見的幾種索引數(shù)據(jù)結(jié)構(gòu)，適合靜態(tài)數(shù)據(jù)的有序數(shù)組、適合 KV 結(jié)構(gòu)的哈希索引及兼顧查詢及插入性能的搜索二叉樹；然后介紹了 Innodb 的常見索引實現(xiàn)方式 B+樹及 Select 語句使用 B+樹索引查找記錄的執(zhí)行過程，在這個部分我們了解了幾個關(guān)鍵的概念，回表、覆蓋索引、最左匹配、索引下推和 MMR；之后還總結(jié)了索引的失效場景及背后的原因。最后，我們回到最初的案例，分析出優(yōu)化前后 SQL 語句在使用索引的差異，進(jìn)而導(dǎo)致執(zhí)行效率的差異。

本文介紹了索引的一些粗淺知識，希望能夠?qū)ψx者有些許幫助。作為階段性學(xué)習(xí)的一個總結(jié)，文章對 MySQL 索引的相關(guān)知識基本上是淺藏輒止，日后還需多多使用和深入學(xué)習(xí)。

何以解憂？唯有學(xué)習(xí)。

image
image.gif

?

推薦閱讀

MySQL從入門到進(jìn)階教程，主講老師：馬士兵、連鵬舉

字節(jié)跳動總結(jié)的設(shè)計模式 PDF 火了，完整版開放分享

刷Github時發(fā)現(xiàn)了一本阿里大神的算法筆記！標(biāo)星70.5K

如果能聽懂這個網(wǎng)約車實戰(zhàn)，哪怕接私活你都可以月入40K

為什么阿里巴巴的程序員成長速度這么快，看完他們的內(nèi)部資料我懂了

程序員達(dá)到50W年薪所需要具備的知識體系。

關(guān)于【暴力遞歸算法】你所不知道的思路

看完三件事??

如果你覺得這篇內(nèi)容對你還蠻有幫助，我想邀請你幫我三個小忙：
點(diǎn)贊，轉(zhuǎn)發(fā)，有你們的 『點(diǎn)贊和評論』，才是我創(chuàng)造的動力。
關(guān)注公眾號 『 Java斗帝 』，不定期分享原創(chuàng)知識。
同時可以期待后續(xù)文章ing??

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容