首頁

目前文章總數：229 篇

  

最後更新：2026年 04月 25日

0002. Mysql 8.0 降序索引對查詢的效能優化有多少

日期：2024年 03月 03日

標籤： MySQL DataBase

摘要：效能議題


  結論：有適合用途時才使用，非必要不使用。
問題討論：探討Mysql 8.0 降序索引配置，在何種情境下使用可以提高查詢效率、效能。
基本介紹：本篇分為4大部分。
第一部分：創建升序、降序索引
第二部分：升序和降序排序的區別
第三部分：升序索引 vs 降序索引效能測試
第四部分：結論-降序索引適合場景




第一部分：創建升序、降序索引

Step 1：官方預設索引

參考官方：建立索引說明
看重點框選的地方，翻譯成中文大意就是：
1. Mysql官方建立索引時可以決定 ASC (升序)或者 DESC (降序)
2. 如果沒有設定，預設為 ASC (升序)


Step 2：創建[升序索引]舉例語法

以下是創建測試資料表 ‘test_asc’ ，此表視為 Log表。
其中時間(CreateTime)紀錄為 ‘TIMESTAMP(6)’ 記錄到毫秒第6位，便於說明。

-- 1. 創建資料表
CREATE TABLE test_asc (
    Logid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    LogInfo VARCHAR(255) DEFAULT '',
    CreateTime TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)
);


-- 2. 在 CreateTime 欄位上創建升序索引
CREATE INDEX idx_CreateTime_asc ON test_asc (CreateTime ASC);

-- 3. 單筆插入可以看到毫秒數
INSERT INTO test_asc(LogInfo) VALUE('testLog');
INSERT INTO test_asc(LogInfo) VALUE('testLog');
INSERT INTO test_asc(LogInfo) VALUE('testLog');
INSERT INTO test_asc(LogInfo) VALUE('testLog');
INSERT INTO test_asc(LogInfo) VALUE('testLog');

-- 4. 捨棄批次插入，跳過 Mysql 對 CURRENT_TIMESTAMP(6) 緩存
INSERT INTO test_asc(LogInfo)
VALUES('testLog'),
VALUES('testLog'),
VALUES('testLog'),
...
VALUES('testLog');



Step 3：創建[升序索引]舉例語法-補充說明1

關於此段語法：

-- 2. 在 CreateTime 欄位上創建升序索引
CREATE INDEX idx_CreateTime_asc ON test_asc (CreateTime ASC);


創建索引預設都是用 ASC(升序)，也就是以下，這裡完整帶出。

CREATE INDEX idx_CreateTime_asc ON test_asc (CreateTime);



Step 4：創建[升序索引]舉例語法-補充說明2

為了便於看出資料差異，不能用Bulk Insert ，當用Bulk Insert時，整批的 CURRENT_TIMESTAMP(6) 會視為同一筆。
為了觀察插入資料的毫秒差，改成單筆插入

-- 3. 單筆插入可以看到毫秒數
INSERT INTO test_asc(LogInfo) VALUE('testLog');
INSERT INTO test_asc(LogInfo) VALUE('testLog');
INSERT INTO test_asc(LogInfo) VALUE('testLog');
INSERT INTO test_asc(LogInfo) VALUE('testLog');
INSERT INTO test_asc(LogInfo) VALUE('testLog');

-- 4. 捨棄批次插入，跳過 Mysql 對 CURRENT_TIMESTAMP(6) 緩存
INSERT INTO test_asc(LogInfo)
VALUES('testLog'),
VALUES('testLog'),
VALUES('testLog'),
...
VALUES('testLog');



Step 5：創建[降序索引]語法

以下是創建測試資料表 ‘test_desc’ ，此表視為 Log表。
與[升序索引]的差別在於 2. 在 id 欄位上創建降序索引，改成 DESC 排序

-- 1. 創建資料表
CREATE TABLE test_desc (
    Logid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    LogInfo VARCHAR(255) DEFAULT '',
    CreateTime TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)
);

-- 2. 在 id 欄位上創建降序索引
CREATE INDEX idx_CreateTime_desc ON test_desc (CreateTime DESC);

-- 3. 單筆插入可以看到毫秒數
INSERT INTO test_desc(LogInfo) VALUE('testLog');
INSERT INTO test_desc(LogInfo) VALUE('testLog');
INSERT INTO test_desc(LogInfo) VALUE('testLog');
INSERT INTO test_desc(LogInfo) VALUE('testLog');
INSERT INTO test_desc(LogInfo) VALUE('testLog');


-- 4. 捨棄批次插入，跳過 Mysql 對 CURRENT_TIMESTAMP(6) 緩存
INSERT INTO test_desc(LogInfo)
VALUES('testLog'),
VALUES('testLog'),
VALUES('testLog'),
...
VALUES('testLog');



第二部分：升序和降序排序的區別

Step 1：預設查詢 - 無效

無效的原因：等於全查，Filter效果為0，並且條件式不含 CreateTime，因此不降序排序 

-- 查詢升序表
SELECT Logid,CreateTime,LogInfo FROM test_asc;
-- 查詢降序表
SELECT Logid,CreateTime,LogInfo FROM test_desc;




Step 2：查詢-包含非索引欄位 - 無效

無效的原因：LogInfo 未吃到索引，並且條件式不含 CreateTime，因此不降序排序 

-- 查詢升序表
SELECT CreateTime,LogInfo FROM test_asc;
-- 查詢降序表
SELECT CreateTime,LogInfo FROM test_desc;




Step 3：查詢-只有索引欄位 - 有效

有效的原因：Logid 是主鍵 + CreateTime 有建立索引，因此條件式影響結果 

-- 查詢升序表
SELECT Logid,CreateTime FROM test_asc;
-- 查詢降序表
SELECT Logid,CreateTime FROM test_desc;




Step 4：查詢-條件式含索引欄位 - 有效

有效的原因：條件式包含索引條件，因此有[降序索引]的效果
※以此條件式對時間範圍會得到 Type=Range

-- 查詢升序表
SELECT Logid,CreateTime,LogInfo FROM test_asc WHERE CreateTime > '2024-02-02 14:44:55.53000';
-- 查詢降序表
SELECT Logid,CreateTime,LogInfo FROM test_desc WHERE CreateTime > '2024-02-02 14:47:10.60000';




Step 5：升序和降序排序的區別

上述四種是常見的基本查詢方式，這個效果是針對特殊狀況才會生效，在預設情況，欄位並不會被影響(預設始終是 ASC 升序排序)。

查詢內容 是否有降序索引效果
查詢全部欄位 無效
查詢欄位包含非索引欄位 無效
查詢欄位只有索引欄位 有效
查詢全部但條件式含索引欄位 有效



第三部分：升序索引 vs 降序索引效能測試

計算方式

計算方式相當直覺，如果功能需求是【最新一筆】的資料 (EX: 最新登入的紀錄)
使用情境決定【索引的效率】。

1. 升序的情況 -> 先進行排序 -> 找出第一筆 ※資料量越大，浪費資源越多。

-- 升序表 - 需要先排序，再取第一筆
SELECT Logid,CreateTime FROM test_asc
ORDER BY CreateTime DESC
LIMIT 1;




2. 降序的情況 -> 第一筆就是結果 ※資料量越大，省的資源越多。

-- 降序表 - 不用排序，利用索引直接取第一筆
SELECT Logid,CreateTime FROM test_desc
LIMIT 1;






第四部分：結論-降序索引適合場景

Step 1：降序索引的需求

在以下情況下會有[降序索引]的需求：

  說明
1. 查詢需求： 某些查詢需要按照降序排序返回結果，而降序索引可以更有效地支援這樣的查詢需求。
2. 最新數據檢索： 在一些應用中，需要檢索最新的數據，這時使用降序索引可以更有效地提取最新添加的數據。
3. 範圍查詢： 某些範圍查詢需要按照降序排列的索引，這樣可以更快速地找到符合條件的數據。



Step 2：適用情境

依照上述需求，可以知道如果有遇到情境開發對應功能時，降序索引將可提升查詢效能：

  說明
1. 查詢需求： 特定欄位配置，分頁功能、Log表，可以將時間做為降序索引，然後再搭配子查詢快速查出百萬筆內的排序資料
2. 最新數據檢索： 最新一筆資料：用戶登入、用戶登出等最新一筆紀錄
3. 範圍查詢： 時間範圍類(最近範圍內資料)：報表、帳單、交易資料



Step 3：建議結論

在以上情境上確實可以使用降序索引，但在使用前要考量以下：

  說明
1. 維護成本： MySQL 中的索引是按照 B-tree 結構組織的，而 B-tree 結構是設計用來支持升序排序的。
  降序索引雖然可以實現，但可能會增加索引的維護成本，因為它不是 B-tree 結構的天然選擇。
2. 默認排序順序： MySQL 默認使用升序排序，因此在大多數情況下，使用升序索引可能更符合預期。
3. 查詢性能： 降序索引在某些特定情況下可能提供更好的查詢性能，但在其他情況下可能沒有太大差異。
  性能的影響因數很多，包括查詢模式、數據分佈等。


給出結論 => 有適合用途時才使用，非必要不使用。：

小結1： 使用[降序索引]不適合Mysql B-tree結構 & 必定[增加維護複雜度]
  ※大家普遍認知都是ASC排序，有可能導致其他同事維護代碼時發生非認知上的結果
小結2： [降序索引]的表不適合於產品會頻繁的需求異動，畢竟[降序索引]定位在特殊查詢上
  ※需求導致查詢變動很容易廢掉此降序索引導致增加空間成本、效能成本。