解析MySQL索引的作用
面試題:索引的作用?
首先建立一張數(shù)據(jù)庫表:
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) # 二級索引,也是聯(lián)合索引 )Engine=InnoDB CHARSET=utf8;
1、索引用于減少需要掃描的記錄數(shù)量
對于某個查詢來說,最簡單粗暴的執(zhí)行方案就是掃描表中的所有記錄,判斷每一條搜索記錄是否符合搜索條件。如果符合,就將其發(fā)送到客戶端,否則就跳過該記錄。這種執(zhí)行方案被稱為全表掃描。
對于InnoDB
存儲引擎來說,全表掃描意味著從聚簇索引第一個葉子節(jié)點的第一條記錄開始,沿著記錄所在的單向鏈表向后掃描,直到最后一個葉子節(jié)點的最后一條記錄,如果可以利用B+樹查找索引列值等于某個值的記錄,這樣就可以減少需要掃描的記錄的數(shù)量。
由于B+樹葉子節(jié)點中的記錄是按照索引列值有小到大的順序排序的,所以只需要掃描某個區(qū)間或者某些區(qū)間中的記錄也可以明顯減少需要掃描的記錄的數(shù)量。
對于查詢語句:
select * from single_table where id>=2 and id<=100;
這個語句其實就是想查找id
值在[2,100]
區(qū)間中的所有聚簇索引記錄,我們可以通過聚簇索引對應(yīng)的B+樹快速的找到id=2
的那條聚簇索引記錄,然后沿著記錄所在的單向鏈表向后掃描,直到某條聚簇索引記錄的id
值不在[2,100]
區(qū)間中為止,與掃描全部的聚簇索引記錄相比,這種方式大大減少了需要掃描的記錄數(shù)量,所以提升了查詢效率。
其實,對于B+樹來說,只要索引列和常數(shù)使用=、<=>、in、not in、is null、is not null、>、<、>=、<=、between、!=、或者like
操作符連接起來,就可以產(chǎn)生掃描區(qū)間,從而提高查詢效率。
2、索引用于排序
我們在編寫查詢語句時,經(jīng)常需要使用order by
子句對查詢出來的記錄按照某種規(guī)則進行排序。在一般情況下,我們只能把記錄加載到內(nèi)存中,然后再用一些排序算法在內(nèi)存中對這些記錄進行排序。有時查詢的結(jié)果集可能太大以至于在內(nèi)存中無法進行排序,此時就需要暫時借助磁盤的空間來存放中間結(jié)果,在排序操作完成后再把排序的結(jié)果返回給客戶端。
在MySQL中,這種在內(nèi)存中或者磁盤中進行排序的方式稱為文件排序,但是如果order by
子句中使用了索引列,就有可能省去在內(nèi)存或磁盤中排序的步驟。
1、分析下面的查詢語句:
select * form single_table order by key_part1,key_part2,key_part3 limit 10;
這個查詢語句的結(jié)果集需要先按照key_part1
值排序,如果記錄的key_part1
值相同,再按照key_part2
值排序,如果key_part1
值和key_part2
值都相同,再按照key_part3
排序。而我們建立的聯(lián)合索引idx_key_part
就是按照上面的規(guī)則排序的,如下為idx_key_part
索引的簡化示意圖:
所以我們可以從第一條idx_key_part
二級索引記錄開始,沿著記錄所在的單向鏈表向后掃描,取10條二級索引記錄即可。由于我們的查詢列表是*
,也就是需要讀取完整的用戶記錄,所以針對獲取到的每一條二級索引記錄都執(zhí)行一次回表操作,將完整的用戶記錄發(fā)送給客戶端。這樣就省去了給10000條記錄排序的時間。
這里我們在執(zhí)行查詢語句時加了limit語句,如果不限制需要獲取的記錄數(shù)量,會導(dǎo)致為大量二級索引記錄執(zhí)行回表操作,這樣會影響整體的性能。
2、使用聯(lián)合索引進行排序時的注意事項
在使用聯(lián)合索引時,需要注意:order by
子句后面的列的順序也必須按照索引列的順序給出;如果給出order by key_part3,key_part2,key_part1
的順序,則無法使用B+樹索引。
之所以顛倒排序列順序就不能使用索引,原因還是聯(lián)合索引中頁面和記錄的排序規(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、不可以使用索引進行排序的情況:
(1) ASC、DESC
混用;
對于使用聯(lián)合索引進行排序的場景,我們要求各個排序列的排序規(guī)則是一致的,也就是要么各個列都是按照升序規(guī)則排序,要么都是按照降序規(guī)則排序。
(2) 排序列包含非一個索引的列;
有時用來排序的多個列不是同一個索引中的,這種情況也不能使用索引進行排序,比如下面的查詢語句:
select * from single_table order by key1,,key2 limit 10;
對于idx_key1
的二級索引記錄來說,只按照key1
列的值進行排序,而且在key1
列相同的情況下是不按照
key2
列的值進行排序的,所以不能使用idx_key1
索引執(zhí)行上述查詢。
(3) 排序列是某個聯(lián)合索引的索引列,但是這些排序列在聯(lián)合索引中并不連續(xù);
(4) 排序列不是以單獨列名的形式出現(xiàn)在order by
子句中;
3、索引用于分組
有時為了方便統(tǒng)計表中的一些信息,會把表中的記錄按照某些列進行分組。比如下面的分組查詢語句:
select key_part1,key_part2,key_part3,count(*) fron single_table group by key_part1,key_part2,key_part3;
這個查詢語句相當于執(zhí)行了3次分組操作:
- 先按照
key_part1
值把記錄進行分組,key_part1
值相同的所有記錄劃分為一組; - 將
key_part1
值相同的每個分組中的記錄再按照key_part2
的值進行分組,將key_part2
值相同的記錄放到一個小分組中,看起來像是在一個大分組中又細分了好多小分組。 - 再將上一步中產(chǎn)生的小分組按照
key_part3
的值分成更小的分組。所以整體上看起來就像是先把記錄分成一個大分組,然后再把大分組分成若干個小分組,最后把若干個小分組再細分為更多的小分組。
上面這個查詢語句就是統(tǒng)計每個小小分組包含的記錄條數(shù)。
如果沒有idx_key_part
索引,就得建立一個用于統(tǒng)計的臨時表,在掃描聚簇索引的記錄時將統(tǒng)計的中間結(jié)果填入這個臨時表。當掃描完記錄后,再把臨時表中的結(jié)果作為結(jié)果集發(fā)送給客戶端。
如果有了idx_key_part
索引,恰巧這個分組順序又與idx_key_part
的索引列的順序一致,因此可以直接使用idx_key_part
的二級索引進行分組,而不用建立臨時表了。
與使用B+樹索引進行排序差不多,分組列的順序頁需要與索引列的順序一致,也可以值使用索引列中左邊連續(xù)的列進行分組。
總結(jié)
本篇文章就到這里了,希望能夠給你帶來幫助,也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容!
相關(guān)文章
MySQL使用innobackupex備份連接服務(wù)器失敗的解決方法
這篇文章主要為大家詳細介紹了MySQL使用innobackupex備份連接服務(wù)器失敗的解決方法,具有一定的參考價值,感興趣的小伙伴們可以參考一下2017-02-02mysql中l(wèi)eft join設(shè)置條件在on與where時的用法區(qū)別分析
這篇文章主要介紹了mysql中l(wèi)eft join設(shè)置條件在on與where時的用法區(qū)別,結(jié)合實例形式分析了mysql中l(wèi)eft join設(shè)置條件在on與where時的相關(guān)用法區(qū)別與操作注意事項,需要的朋友可以參考下2020-02-02

MySQL Router實現(xiàn)MySQL的讀寫分離的方法

MySql逗號分割的字段數(shù)據(jù)分解為多行代碼示例

詳解mysql8.0創(chuàng)建用戶授予權(quán)限報錯解決方法

MySQL的InnoDB擴容及ibdata1文件瘦身方案完全解析