MySQL 中 一條 order by index limit 語句的分析

在開發過程中，遇到了一條 order by index limit 的語句，執行時間慢，下面記錄一下分析過程和原因

問題 SQL

select * from t1 where call_type = 2 and sys_type = 1 order by third_id desc limit 1;

說明：資料庫為 MySQL，引擎為 Innodb，版本為 5.6，其中third_id為普通索引。

對於一個慢 SQL，想到的第一部是通過 explain 檢視其執行計劃，上面這條 SQL 的執行計劃如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	call_record_log	index		idx_third_id	4		1	Using where

可以看到，啟用了索引 id_third_id，下面就需要搞明白為什麼 SQL 在啟用了索引的情況下，還是很慢，有下面3個猜測：

1. 因為在 SQL 中使用了 desc，猜測索引在被倒序排序的過程中耗時較長。
2. 在我們使用 limit 的情況下引發了慢查詢
3. order by index 的原因，根據 explain 的解釋資訊可以，type 型別為 index，而 index 型別通常來說是比較慢的一種情況。

下面我們針對這兩種情況進行驗證。

猜測1：索引倒序排序的問題

1、通過查詢資料得到，在 MySQL 中，使用 Innodb 引擎的時候，索引的排序時有序的，通過正序使用和倒序使用不應該影響 MySQL 的查詢效率（）。

2、通過將 desc 改為 asc，在實際測試過程中發現，對返回資料影響最大的是第一條符合條件的記錄出現的位置，即在找到第一條記錄前，需要掃描多少條語句。

根據上面兩點，可知：order by index desc/asc 並不是引發這條 SQL 產生慢查詢的原因。

猜測2：limit 引發了慢查詢

針對 limit 的使用情況，查詢了 MySQL 使用手冊，證實了猜測。摘錄關鍵資訊如下：

If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

大意：如果只是查詢幾條資料，MySQL 在某些情況下會使用索引，通常情況下回權標掃描。

這一點和我們上面看到的 explain 是一致的，使用了索引。但是並沒有告訴我們會什麼在使用了索引的情況下會慢，繼而引發了猜測，是不是 limit 也是導致這個問題的原因呢。將 limit 條件去掉看一下 explain 的資訊：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	call_record_log	ALL					2133780	Using where; Using filesort

發現，此時執行的是全表掃描，並沒有繼續使用索引。可以得出的推斷：

1. 沒有 limit 的情況下，沒有像希望的那樣使用索引，說明 MySQL 認為使用索引代價更大。
2. 在有 limit 的情況下，會相當於強制使用了索引。

繼續閱讀手冊中 order by 相關的東西，發現如下：

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

大意：如果 order by 和 limit 組合使用，MySQL 在查詢到對應的資料之後就會立即停止而不是排序全部的結果。如果 order by 使用了索引那麼通常來說這個過程是很快的。如果 filesort 的操作必須被執行，在匹配到程式要查詢的結果之前，這個 SQL 在沒有 limit 的情況下查出來的絕大部分或者全部結果都會被排序。在最原始的記錄被找到之後，MySQL 不再排序後面的任何結果集。

手冊說在 order by 後面有索引的情況會 very fast，但是和我們的結果完全不同，沒有在文件中找到解釋慢查詢的原因，先去看看 手冊中關於 order by 的說明。

猜測3：order by index 引發了慢查詢

檢視對應的文件

In some cases, MySQL may use an index to satisfy an ORDER BY clause and avoid the extra sorting involved in performing a filesort operation.

The index may also be used even if the ORDER BY does not match the index exactly, as long as all unused portions of the index and all extra ORDER BY columns are constants in the WHERE clause. If the index does not contain all columns accessed by the query, the index is used only if index access is cheaper than other access methods.

大意：

在某些情況下，MySQL可能會使用索引來滿足ORDER BY子句，並避免執行filesort操作時涉及的額外排序。

即使ORDER BY與索引不完全匹配，也可以使用索引，只要索引的所有未使用部分和所有額外的ORDER BY列都是WHERE子句中的常量即可。 如果索引不包含查詢訪問的所有列，則僅在索引訪問比其他訪問方法更便宜時才使用索引。

通過上面的描述，我們看到了解決問題的希望，即並不是在 order by 後面指定了索引索引就會被啟用，只有當使用索引更為合適的情況下才會被使用，這和我們上面的 SQL 在沒有 limit 的情況下執行的是全表掃面是一致的，即此種情況下 MySQL 執行全表掃描都比按照索引執行的順序更高效。繼續看文件。

SELECT * FROM t1ORDER BY key_part1, key_part2;

However, the query uses SELECT *, which may select more columns than key_part1 and key_part2. In that case, scanning an entire index and looking up table rows to find columns not in the index may be more expensive than scanning the table and sorting the results. If so, the optimizer probably will not use the index. If SELECT * selects only the index columns, the index will be used and sorting avoided.

大意：
但是，查詢使用SELECT ，它可以選擇比key_part1和key_part2更多的列。 在這種情況下，掃描整個索引並查詢錶行以查詢不在索引中的列可能比掃描表並對結果進行排序更昂貴。 如果是這樣，優化器可能不會使用索引。 如果SELECT 僅選擇索引列，則將使用索引並避免排序。

我們發現如果 order by 的索引沒有覆蓋掉全部的查詢欄位的時候，優化器認為使用索引是一種更昂貴的操作，就不在使用索引。終於在文件中發現了能夠解釋我們上面的 SQL 是查詢耗時較長的原因了。

後續的文件給出了哪幾種情況下 order by 的索引會生效，哪幾種情況下是無效的，在此就不一一列舉了。

到底如何優化上面的 SQL 呢

我們私下討論的存在三個方向：

1. 將 where 條件中涉及到的幾個欄位和 thrid 欄位建立聯合索引，同時select 欄位要覆蓋索引
2. 將 order by third 替換成 primary key，可以在一定程度上解決這個問題。
3. 根據具體的業務邏輯，重構 SQL。