MySQL索引的作用是什麼

先建立一張資料庫表：

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、>、操作子連接起來，就可以產生掃描區間，進而提高查詢效率。

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+树索引进行排序差不多，分组列的顺序页需要与索引列的顺序一致，也可以值使用索引列中左边连续的列进行分组。

以上是MySQL索引的作用是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！