白話文解說 obfuscated indexes

Obfuscated indexes, 是描述 database 的索引(index)被混淆造成 query engine 無法順利使用正確索引進行查詢的問題。

舉個例子說明會更清楚，首先考慮以下的 table schema:

CREATE TABLE `todos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
)

當我們用 EXPLAIN 指令執行以下查詢時，會發現這個查詢並沒有用到 created_at 這個索引，因此這個查詢會造成 full table scan:

EXPLAIN SELECT * FROM todos WHERE YEAR(created_at) = 2023;

簡而言之， full table scan 會查詢整張 table 的所有資料，如果 table 很小的話，可能感覺不到問題，一旦 table 資料很多，查詢就會變得很慢，這不是我們樂見的情況。

究其原因，就是資料庫的 query engine 無法判斷 YEAR(created_at) 可以使用 created_at 索引，這種因為額外的 SQL function 或者 SQL 語法造成 query engine 無法辨識該使用哪個索引的情況，就稱為 obfuscated indexes 。

要解決這種問題有 3 種：

1. 拿掉造成混淆的部分，前述例子可以改成 WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31 23:59:59' 就可以讓 query engine 知道要使用 created_at 索引
2. 給定 1 個或多個額外的條件，逼近/縮小要查找的資料範圍，舉 SELECT * FROM todos WHERE YEAR(updated_at) = 2023 為例， updated_at 欄位並沒有索引可用，此時可以用額外的條件告訴 database 怎麼找資料，進一步改成 SELECT * FROM todos WHERE YEAR(updated_at) = 2023 and created_at < 2024-01-01 （因為 2023 年有更新的資料，肯定是 2023 年或更早之前建立的資料），如此一來就能使用 created_at 索引，縮小要查找的資料範圍，避免 full table scan 的問題
3. 建立 function-based index, 也就是針對 YEAR(欄位) 這類的函數執行結果做索引，例如 ALTER TABLE todos ADD INDEX(YEAR(created_at)); ，如此一來，當 query engine 遇到 YEAR(created_at) 時，就會知道可以用 function-based index 優化查詢

如果你只是偶爾查詢資料，建議使用第 1 或第 2 種方法，因為建立過多索引會影響資料庫寫入效能（每新增 1 筆資料都得更新索引，有多個索引就得更新多個）。