在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

只要心中有 B-樹，SQL 優(yōu)化就不怵！

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

            為什么存在性能問題？
            為什么是 B-樹索引？
            優(yōu)化查詢條件
                等值查找
                復(fù)合條件
                范圍查找
                LIKE 運(yùn)算符
                使用函數(shù)
                空值查找
            多表連接查詢
            排序和分組
            DML
            總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。SQL 語句優(yōu)化是數(shù)據(jù)庫優(yōu)化的重要內(nèi)容；無論開發(fā)人員還是 DBA，在工作中都不可避免需要解決一些 SQL 性能問題。

SQL 優(yōu)化的方法有多種，其中最有效、最常用的方法就是索引（Indexing）。因此，我們就來詳細(xì)討論一下索引（B-樹）的原理，以及如何利用索引編寫高性能的 SQL 語句。

本文內(nèi)容適用于各種數(shù)據(jù)庫，包括但不限于 MySQL、Oracle、SQL Server 、PostgreSQL 以及 SQLite。
為什么存在性能問題？

由于 SQL 是一種關(guān)注結(jié)果（what）的語言，具體過程（how）由數(shù)據(jù)庫系統(tǒng)來實(shí)現(xiàn)，這就往往導(dǎo)致我們?nèi)菀拙帉懗龅托阅艿?SQL 語句。我們先來看看數(shù)據(jù)庫為什么存在性能問題。

目前，數(shù)據(jù)庫管理系統(tǒng)主要還是使用磁盤作為數(shù)據(jù)的存儲(chǔ)介質(zhì)；因?yàn)榇疟P的訪問性能雖然比內(nèi)存慢幾個(gè)數(shù)量級(jí)，但是具有價(jià)格便宜、大容量和斷電不丟失的特性。對于交易類型的數(shù)據(jù)庫（OLTP），一般都是小型的事務(wù)，操作很少的隨機(jī)數(shù)據(jù)，但是并發(fā)很高。這種 IO 密集型數(shù)據(jù)庫操作使用磁盤存儲(chǔ)數(shù)據(jù)文件時(shí)存在兩個(gè)問題：

    直接查找數(shù)據(jù)時(shí)，每次都需要掃描表中的全部數(shù)據(jù)；
    隨著數(shù)據(jù)量的增長，掃描磁盤數(shù)據(jù)的性能急劇下降。

下面是一個(gè)簡單的查詢語句，查找 emp_id 等于 5 的員工（數(shù)據(jù)來源）：

select *
from employee
where emp_name = '張三';

 

那么數(shù)據(jù)庫如何找到我們需要的數(shù)據(jù)呢？如果沒有索引，那就只能掃描整個(gè) employee 表，然后依次判斷 emp_name 字段是否等于“張三”并返回滿足條件的數(shù)據(jù)。這種方式一個(gè)最大的問題就是當(dāng)數(shù)據(jù)量逐漸增加時(shí)，全表掃描的性能也就隨之明顯下降。

因此，數(shù)據(jù)庫的性能優(yōu)化首先就是需要減少磁盤的訪問；其中主要的方法就是利用索引技術(shù)。

    ??另一個(gè)減少磁盤 IO 的常用方法就是利用內(nèi)存進(jìn)行數(shù)據(jù)緩存，在數(shù)據(jù)庫中稱為緩沖區(qū)（Buffer Pool、Buffer Cache）。

那么為什么索引能夠提高查詢的性能？為什么幾乎所有的數(shù)據(jù)庫默認(rèn)使用 B-樹索引（B+樹、B*樹）？這就需要先來了解一下 B-樹。
為什么是 B-樹索引？

為了解決查詢的性能問題，數(shù)據(jù)庫引入了一個(gè)新的數(shù)據(jù)結(jié)構(gòu)：索引。索引就像書籍后面的關(guān)鍵字索引，按照關(guān)鍵字進(jìn)行排序，并且提供了指向具體內(nèi)容的頁碼。如果我們在 id 字段上創(chuàng)建了索引（例如 B-樹索引），數(shù)據(jù)庫查找的過程大概如下圖所示：
 

B-樹（平衡樹、Balanced Tree）索引就像是一棵倒立的樹，其中的節(jié)點(diǎn)按照順序進(jìn)行組織；節(jié)點(diǎn)左側(cè)的數(shù)據(jù)都小于該節(jié)點(diǎn)的值，節(jié)點(diǎn)右側(cè)的數(shù)據(jù)都大于節(jié)點(diǎn)的值。

數(shù)據(jù)庫查找時(shí)，首先通過索引找到工號(hào)為 5 的節(jié)點(diǎn)，再通過該節(jié)點(diǎn)上的指針（通常是數(shù)據(jù)的物理地址）訪問數(shù)據(jù)所在的磁盤位置。舉例來說，假設(shè)每個(gè)索引分支節(jié)點(diǎn)可以存儲(chǔ) 100 個(gè)記錄，100 萬（1003）條記錄只需要 3 層 B-樹即可完成索引。通過索引查找數(shù)據(jù)時(shí)需要讀取 3 次索引數(shù)據(jù)（每次磁盤 IO 讀取整個(gè)分支節(jié)點(diǎn)），加上 1 次磁盤 IO 讀取數(shù)據(jù)即可得到查詢結(jié)果。

相反，如果采用全表掃描，需要執(zhí)行的磁盤 IO 可能高出幾個(gè)數(shù)量級(jí)。當(dāng)數(shù)據(jù)量增加到 1 億時(shí)，B-樹索引只需要再增加 1 次索引 IO 即可；而全表掃描則需要再增加幾個(gè)數(shù)量級(jí)的 IO。

    ??全表掃描并不一定比索引查找更慢，當(dāng)表中的記錄較少，或者查詢需要返回表中大部分的數(shù)據(jù)時(shí)，直接通過全表掃描可能更快。

不僅如此，數(shù)據(jù)庫在實(shí)現(xiàn)上還對 B-樹索引進(jìn)行了改進(jìn)，在索引節(jié)點(diǎn)之間增加互相連接的指針（B+樹、B*樹），能夠提供更好的范圍查詢性能。例如，以下是 Oracle 中的 B*樹索引示意圖：
 

其中，葉子節(jié)點(diǎn)之間存儲(chǔ)了雙向鏈表。

聚集索引與非聚集索引

了解 MySQL 的同學(xué)一定聽說過聚集索引（Clustered index）和非聚集索引。聚集索引將表中的數(shù)據(jù)按照索引的結(jié)構(gòu)（通常是主鍵）進(jìn)行存儲(chǔ)；也就是說，索引的葉子節(jié)點(diǎn)中直接存儲(chǔ)了表的數(shù)據(jù)。聚集索引的結(jié)構(gòu)如下圖所示：
 

聚集索引其實(shí)是一種特殊的表，MySQL（InnoDB）和 SQL Server 將這種結(jié)構(gòu)的表稱為聚集索引，Oracle 中稱為索引組織表（IOT）。這種索引方式下，通過主鍵查找數(shù)據(jù)非?？臁?/p>

非聚集索引就是普通的索引，索引的葉子節(jié)點(diǎn)中存儲(chǔ)了指向數(shù)據(jù)所在磁盤位置的指針，數(shù)據(jù)在磁盤上隨機(jī)分布。MySQL（InnoDB）稱之為二級(jí)索引（Secondary index），葉子節(jié)點(diǎn)存儲(chǔ)的是聚集索引的鍵值（主鍵）；通過二級(jí)索引查找時(shí)需要先找到相應(yīng)的主鍵值，再通過主鍵索引查找數(shù)據(jù)。因此，創(chuàng)建聚集索引的主鍵字段越小，索引就越??；一般采用自增長的數(shù)字作為主鍵。

SQL Server 如果使用聚集索引創(chuàng)建表，非聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)的也是聚集索引的鍵值；否則，非聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)的是指向數(shù)據(jù)行的地址。

從具體實(shí)現(xiàn)來說，B-樹索引可以被分為不同的類型：

    唯一索引（UNIQUE）中的索引值必須唯一，可以確保被索引的數(shù)據(jù)不會(huì)重復(fù)，從而實(shí)現(xiàn)數(shù)據(jù)的唯一性約束。
    非唯一索引允許被索引的字段存在重復(fù)值，僅僅用于提高查詢的性能。
    升序索引，按照索引數(shù)據(jù)升序排序的方式建立索引，通過索引返回?cái)?shù)據(jù)可以避免額外的排序操作。
    降序索引，按照索引數(shù)據(jù)降序排序的方式建立索引，通過索引返回?cái)?shù)據(jù)可以避免額外的排序操作。
    單列索引是基于單個(gè)字段創(chuàng)建的索引。例如，員工表的主鍵使用 emp_id 字段創(chuàng)建，就是一個(gè)單列索引。
    多列索引是基于多個(gè)字段創(chuàng)建的索引，也叫復(fù)合索引。創(chuàng)建多列索引的時(shí)候需要注意字段的順序，查詢條件中最常出現(xiàn)的字段放在最前面，這樣可以最大限度地利用索引優(yōu)化查詢的性能。
    函數(shù)索引，基于函數(shù)或者表達(dá)式的值創(chuàng)建的索引。例如，員工的 email 不區(qū)分大小寫并且唯一，可以基于 UPPER(email) 創(chuàng)建一個(gè)唯一的函數(shù)索引。

總而言之，索引是一個(gè)單獨(dú)的數(shù)據(jù)庫對象，需要占用額外的磁盤空間，用于存儲(chǔ)索引結(jié)構(gòu)以及被索引的數(shù)據(jù)。B-樹（B+樹、B*樹）索引實(shí)現(xiàn)了穩(wěn)定且快速的數(shù)據(jù)查找（O(log n) 對數(shù)時(shí)間復(fù)雜度），適用于 =、<、<=、>、>=、BETWEEN、IN 以及字符串的前向匹配（‘a(chǎn)bc%’）查詢的優(yōu)化，因而成為了數(shù)據(jù)庫主要的索引方式。

既然索引可以優(yōu)化查詢的性能，那么我們是不是遇到性能問題就創(chuàng)建一個(gè)新的索引，或者直接將所有字段都進(jìn)行索引？顯然并非如此，因?yàn)樗饕谔岣卟樵兯俣鹊耐瑫r(shí)也需要付出一定的代價(jià)：

    首先，索引需要占用磁盤空間。索引獨(dú)立于數(shù)據(jù)而存在，過多的索引會(huì)導(dǎo)致占用大量的空間。
    其次，進(jìn)行 DML 操作時(shí)，也需要對索引進(jìn)行維護(hù)；維護(hù)索引有時(shí)候比修改數(shù)據(jù)更加耗時(shí)。

一般來說，可以考慮為以下情況創(chuàng)建索引：

    經(jīng)常出現(xiàn)在 WHERE 條件或者 ORDER BY 中的字段創(chuàng)建索引，可以避免全表掃描和額外的排序操作；
    多表連接查詢的關(guān)聯(lián)字段或者外鍵涉及的字段，可以避免全表掃描和外鍵級(jí)聯(lián)操作導(dǎo)致的鎖表；
    查詢中的 GROUP BY 分組操作字段。

    ??索引一般通過create index語句創(chuàng)建。數(shù)據(jù)庫通常會(huì)自動(dòng)為主鍵和唯一約束增加一個(gè)唯一索引。但是除了 MySQL InnoDB 存儲(chǔ)引擎之外，外鍵約束通常不會(huì)自動(dòng)創(chuàng)建索引，不要忘記手動(dòng)創(chuàng)建相應(yīng)的索引。

接下來我們具體分析索引對不同查詢操作的優(yōu)化原理和注意事項(xiàng)。
優(yōu)化查詢條件

SQL 語句中的 WHERE 子句用于執(zhí)行一個(gè)查詢的過濾條件，只有滿足條件的數(shù)據(jù)才需要返回。因此，這是索引發(fā)揮作用的主要途徑；如果只需要返回很少記錄，通過索引就可以快速找到這些數(shù)據(jù)。
等值查找

等值查找大概是我們最常見的 SQL 查詢，例如上文中按照主鍵 id 查找員工的信息。我們可以通過執(zhí)行計(jì)劃查看數(shù)據(jù)庫的實(shí)現(xiàn)過程，以下是該語句在 MySQL 中的執(zhí)行計(jì)劃：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where emp_id = 5;
id|select_type|table   |partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
--|-----------|--------|----------|-----|-------------|-------|-------|-----|----|--------|-----|
 1|SIMPLE     |employee|          |const|PRIMARY      |PRIMARY|4      |const|   1|   100.0|     |

 

結(jié)果顯示，MySQL 通過主鍵進(jìn)行查找（key = PRIMARY），并且最多返回一條記錄（type= const）。

實(shí)際上這是通過索引唯一掃描（INDEX UNIQUE SCAN）找到唯一的索引項(xiàng)，然后直接讀取數(shù)據(jù)（聚集索引），或者通過物理地址指針（非聚集索引）讀取表中的數(shù)據(jù)。其他數(shù)據(jù)庫對此也采用了類似的實(shí)現(xiàn)，這是一種非常快速的查找方式，而且?guī)缀醪粫?huì)受到數(shù)據(jù)量增加的影響。

    ??關(guān)于不同數(shù)據(jù)庫中的執(zhí)行計(jì)劃的查看方式，可以參考這篇文章。

當(dāng)查詢條件上的索引非唯一時(shí)，情況有所不同。例如：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where dept_id = 5;
id|select_type|table   |partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra|
--|-----------|--------|----------|----|-------------|------------|-------|-----|----|--------|-----|
 1|SIMPLE     |employee|          |ref |idx_emp_dept |idx_emp_dept|4      |const|   8|   100.0|     |

 

結(jié)果顯示，MySQL 通過索引 idx_emp_dept 進(jìn)行查找，并且最多返回多條記錄（type= ref）。

實(shí)際上這是通過索引范圍掃描（INDEX RANGE SCAN）找到所有的索引項(xiàng)，然后通過物理地址指針（非聚集索引）讀取表中的數(shù)據(jù)。這種情況下可能需要掃描多個(gè)索引葉子節(jié)點(diǎn)，而且每次對表的訪問都可能是隨機(jī) I/O，因此導(dǎo)致性能下降。這也就是為什么我們應(yīng)該對選擇性強(qiáng)的字段（例如手機(jī)號(hào)）進(jìn)行索引，而不是重復(fù)性高的字段（例如性別）。

    ??PostgreSQL 針對以上語句選擇了 Seq Scan on employee，也就是全表掃描；因?yàn)樗膬?yōu)化器認(rèn)為這種方法速度更快。

復(fù)合條件

有些時(shí)候，查詢條件可能同時(shí)涉及多個(gè)字段。與此對應(yīng)，我們可以創(chuàng)建基于多個(gè)字段的組合索引（多列索引、復(fù)合索引）。對于組合索引，建立索引樹時(shí)首先考慮第一個(gè)字段的排序，第一個(gè)字段順序相同時(shí)再使用第二個(gè)字段，依次類推。因此，查詢條件中最常出現(xiàn)的列應(yīng)該放在最左邊，這個(gè)稱為組合索引最左前綴原則。

我們創(chuàng)建以下示例表：

create table test (
  id   int not null primary key,
  col1 int,
  col2 int,
  col3 int
);

insert into test
with recursive t(id,c1,c2,c3) as (
  select 1 id,1 c1,1 c2,1 c3
  union all
  select id+1,c1+1,mod(c2+1,10),id+1 from t where id < 10000
)

create unique index idx_test_col1_col2 on test(col1, col2);

analyze table test;

 

我們使用字段 col1 和 col2 創(chuàng)建了一個(gè)組合唯一索引，并且字段 col2 上只有 10 種不同的數(shù)值。

以下是同時(shí)使用這兩個(gè)字段作為查詢條件時(shí)的執(zhí)行計(jì)劃：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from test
where col2 = 9 and col1 = 6669;
id|select_type|table|partitions|type |possible_keys     |key               |key_len|ref        |rows|filtered|Extra|
--|-----------|-----|----------|-----|------------------|------------------|-------|-----------|----|--------|-----|
 1|SIMPLE     |test |          |const|idx_test_col1_col2|idx_test_col1_col2|10     |const,const|   1|   100.0|     |

 

即使 col2 在查詢條件中出現(xiàn)在 col1 前面，查詢?nèi)匀荒軌蛲ㄟ^索引唯一掃描獲取數(shù)據(jù)。

此時(shí)，如果將查詢條件修改為只包含 col1 字段：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from test
where col1 = 6669;
id|select_type|table|partitions|type|possible_keys     |key               |key_len|ref  |rows|filtered|Extra|
--|-----------|-----|----------|----|------------------|------------------|-------|-----|----|--------|-----|
 1|SIMPLE     |test |          |ref |idx_test_col1_col2|idx_test_col1_col2|5      |const|   1|   100.0|     |

 

由于字段 col1 出現(xiàn)在索引的最左端，此時(shí)仍然可以通過索引范圍掃描獲取數(shù)據(jù)。此時(shí)的 key_len 從 10 變成了 5，也就是只利用了其中的 col1 字段。

如果將查詢條件修改為只包含 col2 字段：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from test
where col2 = 9;
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra      |
--|-----------|-----|----------|----|-------------|---|-------|---|-----|--------|-----------|
 1|SIMPLE     |test |          |ALL |             |   |       |   |10173|    10.0|Using where|

 

此時(shí)，type 列顯示為 ALL，執(zhí)行計(jì)劃變成了全表掃描（TABLE ACCESS FULL），通常意味著需要進(jìn)行優(yōu)化。

    ??對于以上情況，Oracle 可能會(huì)通過索引跳躍掃描（INDEX SKIP SCAN）獲取數(shù)據(jù)，但是只有在優(yōu)化器認(rèn)為這種方式更有效時(shí)才會(huì)使用。在上面的示例中，Oracle 仍然會(huì)選擇使用全表掃描。

顯然，如果我們把索引的順序調(diào)整為 (col2, col1)，可以解決使用 col2 單獨(dú)查詢的性能；但是又會(huì)導(dǎo)致使用 col1 查詢時(shí)無法使用索引。還有一個(gè)方法就是為 col2 單獨(dú)再創(chuàng)建一個(gè)索引，當(dāng)然這會(huì)導(dǎo)致索引數(shù)量增加，維護(hù)成本更高。因此，我們不但需要了解索引的工作方式，也需要了解應(yīng)用程序?qū)?shù)據(jù)的查詢方式。
范圍查找

除了等值查找之外，查詢條件中的 <、<=、>、>= 和 BETWEEN 等運(yùn)算符通常用于返回某個(gè)范圍之內(nèi)的數(shù)據(jù)。這些運(yùn)算符也可以利用索引提高查詢的性能。例如，以下語句查找 manager 位于某個(gè)范圍之內(nèi)的員工：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where manager between 10 and 15;
id|select_type|table   |partitions|type |possible_keys  |key            |key_len|ref|rows|filtered|Extra                |
--|-----------|--------|----------|-----|---------------|---------------|-------|---|----|--------|---------------------|
 1|SIMPLE     |employee|          |range|idx_emp_manager|idx_emp_manager|5      |   |   1|   100.0|Using index condition|

 

結(jié)果顯示，MySQL 通過索引 idx_emp_manager 查找范圍值（type= range）。

對于索引范圍掃描（INDEX RANGE SCAN），需要注意過多的葉子節(jié)點(diǎn)遍歷可能導(dǎo)致性能下降。因此，盡量減少索引掃描的范圍。對于上面的示例，使用單個(gè)字段作為查詢條件，索引掃描的范圍完全取決于具體的數(shù)據(jù)。

我們來考慮一個(gè)復(fù)合查詢條件的示例：

select *
from test
where col1 between 5000 and 5001
and col3 = 5555;

 

顯然，為了優(yōu)化查詢，我們需要?jiǎng)?chuàng)建一個(gè)復(fù)合索引。問題在于哪個(gè)字段在前，哪個(gè)字段在后。一般來說，等值條件字段應(yīng)該出現(xiàn)在范圍條件字段之前，這樣能夠減少索引掃描的范圍。

對于上面的示例，我們創(chuàng)建兩個(gè)復(fù)合索引：

create index idx_test_col1_col3 on test(col1, col3);
create index idx_test_col3_col1 on test(col3, col1);

 

然后查看數(shù)據(jù)庫的執(zhí)行計(jì)劃：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from test
where col1 between 5000 and 5001
and col3 = 5555;
id|select_type|table|partitions|type |possible_keys                                           |key               |key_len|ref|rows|filtered|Extra                |
--|-----------|-----|----------|-----|--------------------------------------------------------|------------------|-------|---|----|--------|---------------------|
 1|SIMPLE     |test |          |range|idx_test_col1_col2,idx_test_col3_col1,idx_test_col1_col3|idx_test_col3_col1|10     |   |   1|   100.0|Using index condition|

 

通過結(jié)果可以看出，MySQL 最終選擇了索引 idx_test_col3_col1，而不是 idx_test_col1_col3。因?yàn)楦鶕?jù)索引的組織結(jié)構(gòu)，col3 在前面的話可以更快確定索引掃描的范圍。如果 col1 的范圍更大一點(diǎn)的話，通過索引 idx_test_col3_col1 進(jìn)行范圍查找的優(yōu)勢會(huì)更明顯。
LIKE 運(yùn)算符

對于 LIKE 運(yùn)算符，如果查找的模式不是以通配符開頭，可以像范圍查找條件一樣使用索引。例如，以下語句通過 email 進(jìn)行模糊查找：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where email like 'zhang%';
id|select_type|table   |partitions|type |possible_keys|key         |key_len|ref|rows|filtered|Extra                |
--|-----------|--------|----------|-----|-------------|------------|-------|---|----|--------|---------------------|
 1|SIMPLE     |employee|          |range|uk_emp_email |uk_emp_email|302    |   |   2|   100.0|Using index condition|

 

與此相反，如果通配符出現(xiàn)在左側(cè)無法使用索引。例如以下語句會(huì)導(dǎo)致全表掃描：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where email like '%fei%';
id|select_type|table   |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra      |
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----------|
 1|SIMPLE     |employee|          |ALL |             |   |       |   |  25|   11.11|Using where|

 

總之，LIKE 運(yùn)算符只能使用通配符之前的字符進(jìn)行索引查找，通配符之后的字符只能作為過濾謂詞（filter predicate）使用。

    ??對于這種以通配符開始的模式匹配，如果存在性能問題，可以考慮數(shù)據(jù)庫中提供的全文索引或者專用的全文搜索引擎。

使用函數(shù)

在 WHERE 子句中對索引字段進(jìn)行表達(dá)式運(yùn)算或者使用函數(shù)都會(huì)導(dǎo)致索引失效。例如，以下語句用于實(shí)現(xiàn)不區(qū)分大小寫的 email 查詢：

-- MySQL 執(zhí)行計(jì)劃
select *
from employee
where lower(email) = lower('ZhangFei@shuguo.com');
id|select_type|table   |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra      |
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----------|
 1|SIMPLE     |employee|          |ALL |             |   |       |   |  25|   100.0|Using where|

 

雖然 email 字段上已經(jīng)創(chuàng)建了索引 uk_emp_email，但是數(shù)據(jù)庫仍然使用了全表掃描。因?yàn)樗饕胁]有存儲(chǔ) lower(email) 的數(shù)據(jù)，相當(dāng)于沒有任何索引。另一個(gè)常見的案例就是對日期時(shí)間字段進(jìn)行函數(shù)運(yùn)算。

類似的情況還包括對索引字段進(jìn)行算術(shù)運(yùn)算，例如：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where emp_id + 1 = 10;
id|select_type|table   |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra      |
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----------|
 1|SIMPLE     |employee|          |ALL |             |   |       |   |  25|   100.0|Using where|

 

針對這類問題的解決方法首先就是避免對數(shù)據(jù)庫的字段進(jìn)行運(yùn)算和函數(shù)；如果無法避免，可以考慮使用函數(shù)索引（表達(dá)式索引）。

我們?yōu)?email 字段創(chuàng)建一個(gè)函數(shù)索引：

-- MySQL 8.0
create unique index uk_emp_email_lower on employee( (lower(email)) );

analyze table test;

 

再次查看執(zhí)行計(jì)劃：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where lower(email) = lower('ZhangFei@shuguo.com');
id|select_type|table   |partitions|type |possible_keys     |key               |key_len|ref  |rows|filtered|Extra|
--|-----------|--------|----------|-----|------------------|------------------|-------|-----|----|--------|-----|
 1|SIMPLE     |employee|          |const|uk_emp_email_lower|uk_emp_email_lower|303    |const|   1|   100.0|     |

 

此時(shí)，MySQL 選擇了通過索引 uk_emp_email_lower 進(jìn)行唯一掃描。

    ??另一種更加隱蔽的情況是隱式類型轉(zhuǎn)換。例如數(shù)據(jù)庫使用字符類型的字段存儲(chǔ)數(shù)字，然后使用where string_col = 100作為查詢條件，也會(huì)導(dǎo)致索引失效。

空值查找

在數(shù)據(jù)庫中，空值（NULL）是一個(gè)特殊的值，通常用于表示缺失值或者不適用的值。關(guān)于這方面的詳細(xì)介紹，可以參考這篇文章。

索引可以被用于空值的查找，但是 Oracle 中存在一些例外。例如：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from test
where col1 is null;
id|select_type|table|partitions|type|possible_keys                        |key               |key_len|ref  |rows|filtered|Extra                |
--|-----------|-----|----------|----|-------------------------------------|------------------|-------|-----|----|--------|---------------------|
 1|SIMPLE     |test |          |ref |idx_test_col1_col2,idx_test_col1_col3|idx_test_col1_col2|5      |const|   1|   100.0|Using index condition|

 

MySQL 選擇了通過索引 idx_test_col1_col2 進(jìn)行范圍查找。

對于 Oracle 而言，情況有所不同：

-- Oracle 執(zhí)行計(jì)劃
explain plan for
select *
from test
where col1 IS null;

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT                                                         |
--------------------------------------------------------------------------|
Plan hash value: 1357081020                                               |
                                                                          |
--------------------------------------------------------------------------|
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     ||
--------------------------------------------------------------------------|
|   0 | SELECT STATEMENT  |      |     1 |    11 |     9   (0)| 00:00:01 ||
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    11 |     9   (0)| 00:00:01 ||
--------------------------------------------------------------------------|
                                                                          |
Predicate Information (identified by operation id):                       |
---------------------------------------------------                       |
                                                                          |
   1 - filter("COL1" IS NULL)                                             |

 

顯然，Oracle 選擇了全表掃描 TABLE ACCESS FULL，而不是索引 idx_test_col1_col2。這是因?yàn)?Oracle 對于索引字段全部為 NULL 的數(shù)據(jù)，不會(huì)創(chuàng)建相應(yīng)的索引項(xiàng)。

解決的方法就是在索引中增加一個(gè)不會(huì)為空的字段或者常量。例如：

-- 方法一
create index idx_test_col1 on test(col1, 0);

-- 方法二
alter table test modify col2 not null;

 

使用方法一創(chuàng)建索引之后，Oracle 的執(zhí)行計(jì)劃如下：

PLAN_TABLE_OUTPUT                                                                                    |
-----------------------------------------------------------------------------------------------------|
Plan hash value: 178088924                                                                           |
                                                                                                     |
-----------------------------------------------------------------------------------------------------|
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     ||
-----------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                    |               |     1 |    11 |     1   (0)| 00:00:01 ||
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST          |     1 |    11 |     1   (0)| 00:00:01 ||
|*  2 |   INDEX RANGE SCAN                  | IDX_TEST_COL1 |     1 |       |     1   (0)| 00:00:01 ||
-----------------------------------------------------------------------------------------------------|
                                                                                                     |
Predicate Information (identified by operation id):                                                  |
---------------------------------------------------                                                  |
                                                                                                     |
   2 - access("COL1" IS NULL)                                                                        |

 

多表連接查詢

連接查詢用于從兩個(gè)或者多個(gè)表中獲取相關(guān)的數(shù)據(jù)。對于數(shù)據(jù)庫的實(shí)現(xiàn)而言，主要有三種算法：嵌套循環(huán)連接（Nested Loop Join）、哈希連接（Hash Join）以及排序合并連接（Sort Merge Join）。關(guān)于這些實(shí)現(xiàn)算法的詳細(xì)內(nèi)容，可以參考這篇文章。

對于嵌套循環(huán)連接，WHERE 條件上的索引可以加速外部表的訪問，內(nèi)部表連接字段上的索引可以提高連接的性能；哈希連接以及排序合并連接不需要基于連接字段創(chuàng)建索引，不過 WHERE 條件中其他字段上的索引同樣可以優(yōu)化查詢的性能。
排序和分組

排序是一個(gè)非常消耗資源的操作，需要占用很多 CPU 時(shí)間和臨時(shí)存儲(chǔ)空間。對于大型的數(shù)據(jù)集而言，可能需要使用磁盤作為臨時(shí)存儲(chǔ)，從而導(dǎo)致性能明顯下降。

索引按照順序存儲(chǔ)數(shù)據(jù)，就像ORDER BY子句的效果一樣，因此我們可以通過索引來避免排序操作。

以下查詢使用 dept_id 作為條件，同時(shí)按照 hire_date 進(jìn)行排序：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where dept_id = 3
order by hire_date;
id|select_type|table   |partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra         |
--|-----------|--------|----------|----|-------------|------------|-------|-----|----|--------|--------------|
 1|SIMPLE     |employee|          |ref |idx_emp_dept |idx_emp_dept|4      |const|   2|   100.0|Using filesort|

 

查詢雖然可以通過索引 idx_emp_dept 進(jìn)行查找，但是仍然需要執(zhí)行排序操作（Extra 顯示 Using filesort）。為了避免這個(gè)排序，可以將 hire_date 字段添加到索引中：

create index idx_emp_dept_hiredate on employee(dept_id, hire_date);

 

由于外鍵 fk_emp_dept 依賴于索引 idx_emp_dept，我們這里增加一個(gè)新的索引作為演示。然后再次查看執(zhí)行計(jì)劃：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where dept_id = 3
order by hire_date;
id|select_type|table   |partitions|type|possible_keys                     |key                  |key_len|ref  |rows|filtered|Extra|
--|-----------|--------|----------|----|----------------------------------|---------------------|-------|-----|----|--------|-----|
 1|SIMPLE     |employee|          |ref |idx_emp_dept,idx_emp_dept_hiredate|idx_emp_dept_hiredate|4      |const|   2|   100.0|     |

 

顯然，此時(shí) MySQL 選擇了索引 idx_emp_dept_hiredate，同時(shí)避免了排序。

需要注意的是，并非所有按照 hire_date 排序的查詢都可以利用該索引優(yōu)化。例如：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where dept_id > 3
order by hire_date;
id|select_type|table   |partitions|type|possible_keys                     |key|key_len|ref|rows|filtered|Extra                      |
--|-----------|--------|----------|----|----------------------------------|---|-------|---|----|--------|---------------------------|
 1|SIMPLE     |employee|          |ALL |idx_emp_dept,idx_emp_dept_hiredate|   |       |   |  25|    68.0|Using where; Using filesort|

 

以上語句查找 dept_id 大于 4 的部門員工，此時(shí)執(zhí)行計(jì)劃選擇了全表掃描，而不是 idx_emp_dept_hiredate，同時(shí)需要執(zhí)行排序操作。這是因?yàn)樗饕?idx_emp_dept_hiredate 中 dept_id 大于 3 的多個(gè)索引項(xiàng)之間并不是按照 hire_date 排序，而且通過二級(jí)索引訪問還需要再次掃描數(shù)據(jù)表，優(yōu)化器評估認(rèn)為全表掃描性能更好。

因此，數(shù)據(jù)庫執(zhí)行排序操作的原因有兩種：執(zhí)行排序操作代價(jià)更低；索引掃描時(shí)的順序和 order by 子句的順序不一致。

大部分?jǐn)?shù)據(jù)庫都支持索引的雙向掃描，意味著于索引順序相反的排序操作也可以利用索引進(jìn)行優(yōu)化。例如：

-- MySQL 執(zhí)行計(jì)劃
explain
select *
from employee
where dept_id = 3
order by hire_date desc;
id|select_type|table   |partitions|type|possible_keys                     |key                  |key_len|ref  |rows|filtered|Extra              |
--|-----------|--------|----------|----|----------------------------------|---------------------|-------|-----|----|--------|-------------------|
 1|SIMPLE     |employee|          |ref |idx_emp_dept,idx_emp_dept_hiredate|idx_emp_dept_hiredate|4      |const|   2|   100.0|Backward index scan|

 

其中，Extra 字段顯示的 Backward index scan 表明 MySQL 支持反向索引掃描。

不僅ORDER BY可以利用索引，GROUP BY同樣可以利用索引進(jìn)行分組優(yōu)化。例如：

-- MySQL 執(zhí)行計(jì)劃
explain
select hire_date,count(*)
from employee
where dept_id = 3
group by hire_date;
id|select_type|table   |partitions|type|possible_keys                     |key                  |key_len|ref  |rows|filtered|Extra      |
--|-----------|--------|----------|----|----------------------------------|---------------------|-------|-----|----|--------|-----------|
 1|SIMPLE     |employee|          |ref |idx_emp_dept,idx_emp_dept_hiredate|idx_emp_dept_hiredate|4      |const|   2|   100.0|Using index|

 

從結(jié)果可以看出，MySQL 選擇了索引 idx_emp_dept_hiredate；同時(shí)利用索引進(jìn)行分組，避免了在臨時(shí)表中執(zhí)行的分組操作。我們可以將該索引刪除后再看看執(zhí)行計(jì)劃：

drop index idx_emp_dept_hiredate on employee;

explain
select hire_date,count(*)
from employee
where dept_id = 3
group by hire_date;
id|select_type|table   |partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra          |
--|-----------|--------|----------|----|-------------|------------|-------|-----|----|--------|---------------|
 1|SIMPLE     |employee|          |ref |idx_emp_dept |idx_emp_dept|4      |const|   2|   100.0|Using temporary|

 

其中，Extra 字段顯示為 Using temporary，意味著分組在臨時(shí)表中實(shí)現(xiàn)。
DML

索引不僅會(huì)對查詢產(chǎn)生影響，對數(shù)據(jù)進(jìn)行插入、更新和刪除操作時(shí)也需要同步維護(hù)索引結(jié)構(gòu)。

INSERT 語句

對于 INSERT 語句而言，索引越多執(zhí)行越慢。插入數(shù)據(jù)必然導(dǎo)致增加索引項(xiàng)，這種操作的成本往往比插入數(shù)據(jù)本身更高，因?yàn)樗饕仨毐３猪樞蚝?B-樹的平衡（索引節(jié)點(diǎn)拆分）。因此，優(yōu)化插入語句的最好方法就是減少不必要的索引。

    ??沒有任何索引時(shí)的插入性能是最好的，因此在加載大量數(shù)據(jù)時(shí)，可以臨時(shí)刪除所有的索引并在加載完成后重建。

謹(jǐn)慎而謹(jǐn)慎地使用索引，并盡可能避免使用冗余索引。這對于刪除和更新語句也是有益的。

UPDATE 語句

UPDATE 語句如果指定了 WHERE 條件，可以通過索引提高更新操作的性能，因?yàn)橥ㄟ^索引可以快速找到需要修改的數(shù)據(jù)。

另一方面，UPDATE 語句如果修改了索引字段的值，需要?jiǎng)h除舊的索引項(xiàng)并增加新的索引項(xiàng)。因此，更新操作的性能通常也取決于索引的數(shù)量。為了優(yōu)化 UPDATE 語句，頻繁更新的字段不適合創(chuàng)建索引；同時(shí)應(yīng)該盡量避免修改過多的字段，尤其是使用一些 ORM 框架時(shí)。

DELETE 語句

對于 DELETE 語句而言，如果指定了 WHERE 條件，可以通過索引提高刪除操作的性能。因?yàn)樗?UPDATE 語句一樣，需要先執(zhí)行一個(gè) SELECT 語句找到需要?jiǎng)h除的數(shù)據(jù)。

刪除操作涉及的索引更新和插入操作類似，只不過它是刪除一些索引項(xiàng)并確保索引樹的平衡。因此，索引越多刪除性能越差。不過有一個(gè)例外就是沒有任何索引，這個(gè)時(shí)候性能會(huì)更差，因?yàn)閿?shù)據(jù)庫需要執(zhí)行全表掃描才能找到需要?jiǎng)h除的數(shù)據(jù)。
總結(jié)

B-樹（B+樹、B*樹）索引極大地減少了隨機(jī)磁盤的訪問，同時(shí)具有對數(shù)時(shí)間復(fù)雜度，幾乎不受數(shù)據(jù)量的影響；因而是我們進(jìn)行 SQL 優(yōu)化的最有效、最常用的方法。索引可以用于優(yōu)化各種查詢條件、連接查詢、排序和分組以及 DML 語句，但是也會(huì)占用額外的存儲(chǔ)空間和索引維護(hù)操作，我們在創(chuàng)建索引和編寫 SQL 語句時(shí)需要遵循一定的原則，才能有效地利用索引實(shí)現(xiàn)優(yōu)化。

如果覺得文章對你有用，請不要白嫖！歡迎關(guān)注??、評論??、點(diǎn)贊??！