MySQL 使用索引掃描進行排序

Posted on by

安裝sakila

我們將會使用MySQL示例數據庫sakila來進行sql的演示和講解 dev.mysql.com/doc/sakila/…

索引掃描排序

MySQL有兩種方式可以生成有序的結果:通過排序操作﹔或者按索引順序掃描﹔如果EXPLAIN出來的type列的值為“index”，則說明MySQL使用瞭索引掃描來做排序。
掃描索引本身是很快的，因為隻需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就都回表查詢一次對應的行。這基本上都是隨機I/O，因此按索引順序讀取數據的速度通常要比順序地全表掃描慢，尤其是在IO密集型的工作負載時。此時可能就會用全表掃描而不是按索引查找瞭。
如果可能，設計索引時應該盡可能地同時滿足排序和查找行。
隻有當索引的列順序和0RDER BY子句的順序完全一致，並且所有列的排序方向（倒序或正序）都一樣時，MySQL才能夠使用索引來對結果做排序。如果查詢需要關聯多張表，則隻有當ORDER BY子句引用的字段全部為第一個表時，才能使用索引做排序。ORDER BY子句和查找型查詢的限制是一樣的:需要滿足索引的最左前綴的要求﹔否則,MySQL都需要執行排序操作（filesort），而無法利用索引排序。

表結構

我們將使用rental這個表來進行講解

CREATE TABLE `rental` (
  
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

查看Extra 中是否出現Using filesort（MySQL中無法利用索引完成的排序操作稱為“文件排序”）當我們試圖對一個沒有索引的字段進行排序時，就是filesort。雖然裡面有個file，但它跟文件沒有任何關系，實際上是內部的一個快速排序

可以使用索引掃描來做排序的情況

補足前導列

有一種情況下ORDER BY子句可以不滿足索引的最左前綴的要求，就是前導列為常量的時候。如果WHERE子句或者JOIN子句中對這些列指定瞭常量,就可以“彌補”索引的不足。 我們使用Sakila數據庫來測試一下

可以看到

書上的Extra寫的是Using where，而我執行的時候是Using index condition ，原因是高性能MySQL中使用的版本是5.5，5.6版本中的索引條件推送（index condition pushdown）還處於未正式發佈階段呢。這裡沒有filesort的原因是因為有個rental_date = ‘2005-05-25’的常量條件，相當於將索引的第一列補足瞭，這樣就符合瞭索引的最左前綴要求。

order by 中隻包含一種排序

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY  inventory_id desc

可以看到

需要註意這一條，在書中使用的的條件是rental_date>’2005-05-25′

WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id

此時無法使用索引排序而是直接全表掃描做瞭個排序，原因是因為返回數據的條數過多，用索引查詢此時已經不劃算瞭

需要註意這裡的解釋裡面的rows並不準確，隻是一個估算值，實際上按這個條件查詢有16036條數據 要想解決這個問題，就需要加上limit

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id limit 0,10

對應的執行計劃

可以看到使用瞭索引

無法使用索引掃描的情況

查詢條件中包含不同排序方向

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY  inventory_id desc,customer_id asc

索引中兩列都是正序，現在order by 中一列正序一列倒序就得二次排序瞭。

查詢條件中引用不在索引中的列

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' ORDER BY  inventory_id ,staff_id

無法組合最左前綴時

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' ORDER BY  customer_id

第一列是查詢范圍時

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date > '2005-08-22' ORDER BY  inventory_id,customer_id

where中有多個等於條件

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' and inventory_id in(1,2)  ORDER BY  customer_id

簡單來說就是不符合索引最左前綴的就會進行一次排序。

總結

今天我們講解瞭MySQL中的索引掃描排序，明天我們還將繼續介紹其他建立高性能索引的方法，敬請期待，下篇再見！

以上就是MySQL 索引掃描的簡單使用的詳細內容，更多關於MySQL 索引掃描排序的資料請關註WalkonNet其它相關文章！

推薦閱讀：