解析MySQL索引的作用

Posted on by

面試題：索引的作用？

首先建立一張數據庫表：

create table single_table(
	id int not auto_increment, 
	key1 varchar(100),         
	key2 int,
	key3 varchar(100),
	key_part1 varchar(100),
	key_part2 varchar(100),
	key_part3 varchar(100),
    common_field varchar(100),
	primary key(id),          # 聚簇索引
	key idx_key1(key1),       # 二級索引
	unique key uk_key2(key2), # 二級索引，而且該索引是唯一二級索引
	key idx_key3(key3),       # 二級索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二級索引，也是聯合索引
)Engine=InnoDB CHARSET=utf8;

1、索引用於減少需要掃描的記錄數量

對於某個查詢來說，最簡單粗暴的執行方案就是掃描表中的所有記錄，判斷每一條搜索記錄是否符合搜索條件。如果符合，就將其發送到客戶端，否則就跳過該記錄。這種執行方案被稱為全表掃描。

對於InnoDB存儲引擎來說，全表掃描意味著從聚簇索引第一個葉子節點的第一條記錄開始，沿著記錄所在的單向鏈表向後掃描，直到最後一個葉子節點的最後一條記錄，如果可以利用B+樹查找索引列值等於某個值的記錄，這樣就可以減少需要掃描的記錄的數量。

由於B+樹葉子節點中的記錄是按照索引列值有小到大的順序排序的，所以隻需要掃描某個區間或者某些區間中的記錄也可以明顯減少需要掃描的記錄的數量。

對於查詢語句：

select * from single_table where id>=2 and id<=100;

這個語句其實就是想查找id值在[2,100]區間中的所有聚簇索引記錄，我們可以通過聚簇索引對應的B+樹快速的找到id=2的那條聚簇索引記錄，然後沿著記錄所在的單向鏈表向後掃描，直到某條聚簇索引記錄的id值不在[2,100]區間中為止，與掃描全部的聚簇索引記錄相比，這種方式大大減少瞭需要掃描的記錄數量，所以提升瞭查詢效率。

其實，對於B+樹來說，隻要索引列和常數使用=、<=>、in、not in、is null、is not null、>、<、>=、<=、between、!=、或者like操作符連接起來，就可以產生掃描區間，從而提高查詢效率。

2、索引用於排序

我們在編寫查詢語句時，經常需要使用order by子句對查詢出來的記錄按照某種規則進行排序。在一般情況下，我們隻能把記錄加載到內存中，然後再用一些排序算法在內存中對這些記錄進行排序。有時查詢的結果集可能太大以至於在內存中無法進行排序，此時就需要暫時借助磁盤的空間來存放中間結果，在排序操作完成後再把排序的結果返回給客戶端。

在MySQL中，這種在內存中或者磁盤中進行排序的方式稱為文件排序，但是如果order by子句中使用瞭索引列，就有可能省去在內存或磁盤中排序的步驟。

1、分析下面的查詢語句：

select * form single_table order by key_part1,key_part2,key_part3 limit 10;

這個查詢語句的結果集需要先按照key_part1值排序，如果記錄的key_part1值相同，再按照key_part2值排序，如果key_part1值和key_part2值都相同，再按照key_part3排序。而我們建立的聯合索引idx_key_part就是按照上面的規則排序的，如下為idx_key_part索引的簡化示意圖：

在這裡插入圖片描述

所以我們可以從第一條idx_key_part二級索引記錄開始，沿著記錄所在的單向鏈表向後掃描，取10條二級索引記錄即可。由於我們的查詢列表是*，也就是需要讀取完整的用戶記錄，所以針對獲取到的每一條二級索引記錄都執行一次回表操作，將完整的用戶記錄發送給客戶端。這樣就省去瞭給10000條記錄排序的時間。

這裡我們在執行查詢語句時加瞭limit語句，如果不限制需要獲取的記錄數量，會導致為大量二級索引記錄執行回表操作，這樣會影響整體的性能。

2、使用聯合索引進行排序時的註意事項

在使用聯合索引時，需要註意：order by子句後面的列的順序也必須按照索引列的順序給出；如果給出order by key_part3,key_part2,key_part1的順序，則無法使用B+樹索引。

之所以顛倒排序列順序就不能使用索引，原因還是聯合索引中頁面和記錄的排序規則是規定的，即先按照key_part1值排序，如果記錄的key_part1值相同，再按照key_part2值排序，如果記錄的key_part1值和key_part2值都相同，再按照key_part3值排序。如果order by子句的內容是order by key_part3,key_part2,key_part1，那就要求先按照key_part3值排序，如果記錄的key_part3值相同，再按照key_part2值排序，如果記錄的key_part3值和key_part2值都相同，再按照key_part1值排序，這顯然是沖突的。

3、不可以使用索引進行排序的情況：

(1) ASC、DESC混用；

對於使用聯合索引進行排序的場景，我們要求各個排序列的排序規則是一致的，也就是要麼各個列都是按照升序規則排序，要麼都是按照降序規則排序。

(2) 排序列包含非一個索引的列；

有時用來排序的多個列不是同一個索引中的，這種情況也不能使用索引進行排序，比如下面的查詢語句：

select * from single_table order by key1,,key2 limit 10;

對於idx_key1的二級索引記錄來說，隻按照key1列的值進行排序，而且在key1列相同的情況下是不按照

key2列的值進行排序的，所以不能使用idx_key1索引執行上述查詢。

(3) 排序列是某個聯合索引的索引列，但是這些排序列在聯合索引中並不連續；

(4) 排序列不是以單獨列名的形式出現在order by子句中；

3、索引用於分組

有時為瞭方便統計表中的一些信息，會把表中的記錄按照某些列進行分組。比如下面的分組查詢語句：

select key_part1,key_part2,key_part3,count(*) fron single_table group by key_part1,key_part2,key_part3;

這個查詢語句相當於執行瞭3次分組操作：

  • 先按照key_part1值把記錄進行分組，key_part1值相同的所有記錄劃分為一組；
  • 將key_part1值相同的每個分組中的記錄再按照key_part2的值進行分組，將key_part2值相同的記錄放到一個小分組中，看起來像是在一個大分組中又細分瞭好多小分組。
  • 再將上一步中產生的小分組按照key_part3的值分成更小的分組。所以整體上看起來就像是先把記錄分成一個大分組，然後再把大分組分成若幹個小分組，最後把若幹個小分組再細分為更多的小分組。

上面這個查詢語句就是統計每個小小分組包含的記錄條數。

如果沒有idx_key_part索引，就得建立一個用於統計的臨時表，在掃描聚簇索引的記錄時將統計的中間結果填入這個臨時表。當掃描完記錄後，再把臨時表中的結果作為結果集發送給客戶端。

如果有瞭idx_key_part索引，恰巧這個分組順序又與idx_key_part的索引列的順序一致，因此可以直接使用idx_key_part的二級索引進行分組，而不用建立臨時表瞭。

與使用B+樹索引進行排序差不多，分組列的順序頁需要與索引列的順序一致，也可以值使用索引列中左邊連續的列進行分組。

總結

本篇文章就到這裡瞭，希望能夠給你帶來幫助，也希望您能夠多多關註WalkonNet的更多內容!   

推薦閱讀：