【開發總結】order by 為什麼沒有走索引？

貓咪大王_lkb發表於2020-09-19
索引

1.  現象

表結構如下

CREATE TABLE `ACT_HI_INST` (
  `ID` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '主鍵',
  `INST_ID_` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '流程例項id',
  `BUSINESS_KEY_` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '流程編號',
  `CREATE_TIME_` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `UPDATE_TIME_` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UIDX_INST` (`INST_ID_`),
  UNIQUE KEY `UIDX_BKEY` (`BUSINESS_KEY_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
執行下面語句， 發現沒有走索引
explain
SELECT
*
FROM ACT_HI_INST hi 
ORDER BY hi.BUSINESS_KEY_ DESC;

 

select count(*) from ACT_HI_INST ;
-- 4332

同時我們發現rows與實際表的數量存在差異，使用下面語句重新統計索引資訊， 更正統計資訊， 更新之後發現 rows 就資料表的數量基本一致（不完全一樣是因為rows是取樣統計而來的）。

analyze table ACT_HI_INST;

也就是說對於下面這條語句，儘管BUSINESS_KEY_  上有索引，MySQL還是選擇了全表掃描。為什麼會這樣呢？

SELECT * FROM ACT_HI_INST hi ORDER BY hi.BUSINESS_KEY_ DESC;

 

2. 猜想

我猜想原因是 因為MySQL認為即使 使用 BUSINESS_KEY_  索引樹進行查詢，不需要排序，但是最後仍然需要回表，回表的次數 == 表的大小，這種代價比全表掃描然後排序的代價更大。

 

3. 驗證

為了驗證我的猜想，我加上在SQL語句最後加上 LIMIT 10 。如下所示

explain
SELECT
*
FROM ACT_HI_INST hi 
ORDER BY hi.BUSINESS_KEY_ DESC limit 10;

果然我們看到加上 LIMIT 的語句走了索引。因為這個時候MySQL認為回表的代價比排序的代價更小，所以這個時候選擇了走 BUSINESS_KEY_  索引。

除了上面這種方式可以驗證我的猜想，還有一種方式，如下所示。我們只查詢BUSINESS_KEY_ 。

因為此時葉子節點就包含我們需要查詢的欄位，這個時候不需要再回表，所以MySQL選擇BUSINESS_KEY_  代價最小。

explain
SELECT
hi.BUSINESS_KEY_
FROM ACT_HI_INST hi 
ORDER BY hi.BUSINESS_KEY_ DESC ;

 

4. 補充

這部分主要來源於極客時間林曉斌老師的《MySQL實戰45講》第16講

order by a

如果 a 欄位上有索引，MySQL innodb引擎是按照上訴的方式進行選擇。如果a欄位上沒有索引，MySQL  innodb引擎 就會有兩種排序方式：

全欄位排序 和 rowid 排序。

全欄位排序：將所有要選擇的欄位加入到sort_buffer中，然後在記憶體或者外部進行排序。如果能在記憶體中進行排序就在記憶體中進行排序。

如果要排序的資料量小於 sort_buffer_size，排序就在記憶體中完成。但如果排序資料量太大，記憶體放不下，則不得不利用磁碟臨時檔案輔助排序。

如果查詢要返回的欄位很多的話，那麼 sort_buffer 裡面要放的欄位數太多，這樣記憶體裡能夠同時放下的行數很少，要分成很多個臨時檔案，排序的效能會很差。這個時候MySQL就會採用rowId排序。

rowid排序：MySQL取出需要排序的欄位和ID放入sort_buffer中進行排序，最後按照排序的結果，通過ID回表，返回資料到客戶端。

MySQL 的一個設計思想是如果記憶體夠，就要多利用記憶體，儘量減少磁碟訪問。所以對應sort_buffer足夠大的情況，MySQL會優選選擇全欄位排序。

相關文章