面試題：索引的作用？

首先建立一張數(shù)據(jù)庫(kù)表：

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),       # 二級(jí)索引
	unique key uk_key2(key2), # 二級(jí)索引，而且該索引是唯一二級(jí)索引
	key idx_key3(key3),       # 二級(jí)索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二級(jí)索引，也是聯(lián)合索引
)Engine=InnoDB CHARSET=utf8;

1、索引用于減少需要掃描的記錄數(shù)量

對(duì)于某個(gè)查詢(xún)來(lái)說(shuō)，最簡(jiǎn)單粗暴的執(zhí)行方案就是掃描表中的所有記錄，判斷每一條搜索記錄是否符合搜索條件。如果符合，就將其發(fā)送到客戶(hù)端，否則就跳過(guò)該記錄。這種執(zhí)行方案被稱(chēng)為全表掃描。

對(duì)于InnoDB存儲(chǔ)引擎來(lái)說(shuō)，全表掃描意味著從聚簇索引第一個(gè)葉子節(jié)點(diǎn)的第一條記錄開(kāi)始，沿著記錄所在的單向鏈表向后掃描，直到最后一個(gè)葉子節(jié)點(diǎn)的最后一條記錄，如果可以利用B+樹(shù)查找索引列值等于某個(gè)值的記錄，這樣就可以減少需要掃描的記錄的數(shù)量。

由于B+樹(shù)葉子節(jié)點(diǎn)中的記錄是按照索引列值有小到大的順序排序的，所以只需要掃描某個(gè)區(qū)間或者某些區(qū)間中的記錄也可以明顯減少需要掃描的記錄的數(shù)量。

對(duì)于查詢(xún)語(yǔ)句：

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

這個(gè)語(yǔ)句其實(shí)就是想查找id值在[2,100]區(qū)間中的所有聚簇索引記錄，我們可以通過(guò)聚簇索引對(duì)應(yīng)的B+樹(shù)快速的找到id=2的那條聚簇索引記錄，然后沿著記錄所在的單向鏈表向后掃描，直到某條聚簇索引記錄的id值不在[2,100]區(qū)間中為止，與掃描全部的聚簇索引記錄相比，這種方式大大減少了需要掃描的記錄數(shù)量，所以提升了查詢(xún)效率。

其實(shí)，對(duì)于B+樹(shù)來(lái)說(shuō)，只要索引列和常數(shù)使用=、<=>、in、not in、is null、is not null、>、<、>=、<=、between、!=、或者like操作符連接起來(lái)，就可以產(chǎn)生掃描區(qū)間，從而提高查詢(xún)效率。

2、索引用于排序

我們?cè)诰帉?xiě)查詢(xún)語(yǔ)句時(shí)，經(jīng)常需要使用order by子句對(duì)查詢(xún)出來(lái)的記錄按照某種規(guī)則進(jìn)行排序。在一般情況下，我們只能把記錄加載到內(nèi)存中，然后再用一些排序算法在內(nèi)存中對(duì)這些記錄進(jìn)行排序。有時(shí)查詢(xún)的結(jié)果集可能太大以至于在內(nèi)存中無(wú)法進(jìn)行排序，此時(shí)就需要暫時(shí)借助磁盤(pán)的空間來(lái)存放中間結(jié)果，在排序操作完成后再把排序的結(jié)果返回給客戶(hù)端。

在MySQL中，這種在內(nèi)存中或者磁盤(pán)中進(jìn)行排序的方式稱(chēng)為文件排序，但是如果order by子句中使用了索引列，就有可能省去在內(nèi)存或磁盤(pán)中排序的步驟。

1、分析下面的查詢(xún)語(yǔ)句：

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

這個(gè)查詢(xún)語(yǔ)句的結(jié)果集需要先按照key_part1值排序，如果記錄的key_part1值相同，再按照key_part2值排序，如果key_part1值和key_part2值都相同，再按照key_part3排序。而我們建立的聯(lián)合索引idx_key_part就是按照上面的規(guī)則排序的，如下為idx_key_part索引的簡(jiǎn)化示意圖：

所以我們可以從第一條idx_key_part二級(jí)索引記錄開(kāi)始，沿著記錄所在的單向鏈表向后掃描，取10條二級(jí)索引記錄即可。由于我們的查詢(xún)列表是*，也就是需要讀取完整的用戶(hù)記錄，所以針對(duì)獲取到的每一條二級(jí)索引記錄都執(zhí)行一次回表操作，將完整的用戶(hù)記錄發(fā)送給客戶(hù)端。這樣就省去了給10000條記錄排序的時(shí)間。

這里我們?cè)趫?zhí)行查詢(xún)語(yǔ)句時(shí)加了limit語(yǔ)句，如果不限制需要獲取的記錄數(shù)量，會(huì)導(dǎo)致為大量二級(jí)索引記錄執(zhí)行回表操作，這樣會(huì)影響整體的性能。

2、使用聯(lián)合索引進(jìn)行排序時(shí)的注意事項(xiàng)

在使用聯(lián)合索引時(shí)，需要注意：order by子句后面的列的順序也必須按照索引列的順序給出；如果給出order by key_part3,key_part2,key_part1的順序，則無(wú)法使用B+樹(shù)索引。

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

3、不可以使用索引進(jìn)行排序的情況：

(1) ASC、DESC混用；

對(duì)于使用聯(lián)合索引進(jìn)行排序的場(chǎng)景，我們要求各個(gè)排序列的排序規(guī)則是一致的，也就是要么各個(gè)列都是按照升序規(guī)則排序，要么都是按照降序規(guī)則排序。

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

有時(shí)用來(lái)排序的多個(gè)列不是同一個(gè)索引中的，這種情況也不能使用索引進(jìn)行排序，比如下面的查詢(xún)語(yǔ)句：

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

對(duì)于idx_key1的二級(jí)索引記錄來(lái)說(shuō)，只按照key1列的值進(jìn)行排序，而且在key1列相同的情況下是不按照

key2列的值進(jìn)行排序的，所以不能使用idx_key1索引執(zhí)行上述查詢(xún)。

(3) 排序列是某個(gè)聯(lián)合索引的索引列，但是這些排序列在聯(lián)合索引中并不連續(xù)；

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

3、索引用于分組

有時(shí)為了方便統(tǒng)計(jì)表中的一些信息，會(huì)把表中的記錄按照某些列進(jìn)行分組。比如下面的分組查詢(xún)語(yǔ)句：

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

這個(gè)查詢(xún)語(yǔ)句相當(dāng)于執(zhí)行了3次分組操作：

• 先按照key_part1值把記錄進(jìn)行分組，key_part1值相同的所有記錄劃分為一組；
• 將key_part1值相同的每個(gè)分組中的記錄再按照key_part2的值進(jìn)行分組，將key_part2值相同的記錄放到一個(gè)小分組中，看起來(lái)像是在一個(gè)大分組中又細(xì)分了好多小分組。
• 再將上一步中產(chǎn)生的小分組按照key_part3的值分成更小的分組。所以整體上看起來(lái)就像是先把記錄分成一個(gè)大分組，然后再把大分組分成若干個(gè)小分組，最后把若干個(gè)小分組再細(xì)分為更多的小分組。

上面這個(gè)查詢(xún)語(yǔ)句就是統(tǒng)計(jì)每個(gè)小小分組包含的記錄條數(shù)。

如果沒(méi)有idx_key_part索引，就得建立一個(gè)用于統(tǒng)計(jì)的臨時(shí)表，在掃描聚簇索引的記錄時(shí)將統(tǒng)計(jì)的中間結(jié)果填入這個(gè)臨時(shí)表。當(dāng)掃描完記錄后，再把臨時(shí)表中的結(jié)果作為結(jié)果集發(fā)送給客戶(hù)端。

如果有了idx_key_part索引，恰巧這個(gè)分組順序又與idx_key_part的索引列的順序一致，因此可以直接使用idx_key_part的二級(jí)索引進(jìn)行分組，而不用建立臨時(shí)表了。

與使用B+樹(shù)索引進(jìn)行排序差不多，分組列的順序頁(yè)需要與索引列的順序一致，也可以值使用索引列中左邊連續(xù)的列進(jìn)行分組。

總結(jié)

本篇文章就到這里了，希望能夠給你帶來(lái)幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容!