目錄安裝sakila索引掃描排序表結(jié)構(gòu)可以使用索引掃描來(lái)做排序的情況補(bǔ)足前導(dǎo)列order by 中只包含一種排序無(wú)法使用索引掃描的情況查詢(xún)條件中包含不同排序方向查詢(xún)條件中引用不在索引中的列無(wú)法組合最左前綴時(shí)第一列是查詢(xún)范圍時(shí)where中有多個(gè)等于條件總結(jié)安裝sakila

我們將會(huì)使用MySQL示例數(shù)據(jù)庫(kù)sakila來(lái)進(jìn)行sql的演示和講解 dev.mysql.com/doc/sakila/…

索引掃描排序

MySQL有兩種方式可以生成有序的結(jié)果:通過(guò)排序操作?或者按索引順序掃描?如果EXPLAIN出來(lái)的type列的值為“index”，則說(shuō)明MySQL使用了索引掃描來(lái)做排序。掃描索引本身是很快的，因?yàn)橹恍枰獜囊粭l索引記錄移動(dòng)到緊接著的下一條記錄。但如果索引不能覆蓋查詢(xún)所需的全部列，那就不得不每掃描一條索引記錄就都回表查詢(xún)一次對(duì)應(yīng)的行。這基本上都是隨機(jī)I/O，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序地全表掃描慢，尤其是在IO密集型的工作負(fù)載時(shí)。此時(shí)可能就會(huì)用全表掃描而不是按索引查找了。如果可能，設(shè)計(jì)索引時(shí)應(yīng)該盡可能地同時(shí)滿(mǎn)足排序和查找行。只有當(dāng)索引的列順序和0RDER BY子句的順序完全一致，并且所有列的排序方向（倒序或正序）都一樣時(shí)，MySQL才能夠使用索引來(lái)對(duì)結(jié)果做排序。如果查詢(xún)需要關(guān)聯(lián)多張表，則只有當(dāng)ORDER BY子句引用的字段全部為第一個(gè)表時(shí)，才能使用索引做排序。ORDER BY子句和查找型查詢(xún)的限制是一樣的:需要滿(mǎn)足索引的最左前綴的要求?否則,MySQL都需要執(zhí)行排序操作（filesort），而無(wú)法利用索引排序。

表結(jié)構(gòu)

我們將使用rental這個(gè)表來(lái)進(jìn)行講解

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 中是否出現(xiàn)Using filesort（MySQL中無(wú)法利用索引完成的排序操作稱(chēng)為“文件排序”）當(dāng)我們?cè)噲D對(duì)一個(gè)沒(méi)有索引的字段進(jìn)行排序時(shí)，就是filesort。雖然里面有個(gè)file，但它跟文件沒(méi)有任何關(guān)系，實(shí)際上是內(nèi)部的一個(gè)快速排序

可以使用索引掃描來(lái)做排序的情況補(bǔ)足前導(dǎo)列

有一種情況下ORDER BY子句可以不滿(mǎn)足索引的最左前綴的要求，就是前導(dǎo)列為常量的時(shí)候。如果WHERE子句或者JOIN子句中對(duì)這些列指定了常量,就可以“彌補(bǔ)”索引的不足。 我們使用Sakila數(shù)據(jù)庫(kù)來(lái)測(cè)試一下

可以看到

書(shū)上的Extra寫(xiě)的是Using where，而我執(zhí)行的時(shí)候是Using index condition ，原因是高性能MySQL中使用的版本是5.5，5.6版本中的索引條件推送（index condition pushdown）還處于未正式發(fā)布階段呢。這里沒(méi)有filesort的原因是因?yàn)橛袀€(gè)rental_date = ’2005-05-25’的常量條件，相當(dāng)于將索引的第一列補(bǔ)足了，這樣就符合了索引的最左前綴要求。

order by 中只包含一種排序

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

可以看到

需要注意這一條，在書(shū)中使用的的條件是rental_date>’2005-05-25’

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

此時(shí)無(wú)法使用索引排序而是直接全表掃描做了個(gè)排序，原因是因?yàn)榉祷財(cái)?shù)據(jù)的條數(shù)過(guò)多，用索引查詢(xún)此時(shí)已經(jīng)不劃算了

需要注意這里的解釋里面的rows并不準(zhǔn)確，只是一個(gè)估算值，實(shí)際上按這個(gè)條件查詢(xún)有16036條數(shù)據(jù) 要想解決這個(gè)問(wèn)題，就需要加上limit

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

對(duì)應(yīng)的執(zhí)行計(jì)劃

可以看到使用了索引

無(wú)法使用索引掃描的情況查詢(xún)條件中包含不同排序方向

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

索引中兩列都是正序，現(xiàn)在order by 中一列正序一列倒序就得二次排序了。

查詢(xún)條件中引用不在索引中的列

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

無(wú)法組合最左前綴時(shí)

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

第一列是查詢(xún)范圍時(shí)

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

where中有多個(gè)等于條件

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

簡(jiǎn)單來(lái)說(shuō)就是不符合索引最左前綴的就會(huì)進(jìn)行一次排序。

總結(jié)

今天我們講解了MySQL中的索引掃描排序，明天我們還將繼續(xù)介紹其他建立高性能索引的方法，敬請(qǐng)期待，下篇再見(jiàn)！

以上就是MySQL 索引掃描的簡(jiǎn)單使用的詳細(xì)內(nèi)容，更多關(guān)于MySQL 索引掃描排序的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！