白話文解說 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 筆資料都得更新索引,有多個索引就得更新多個)。

對抗久坐職業傷害

研究指出每天增加 2 小時坐著的時間,會增加大腸癌、心臟疾病、肺癌的風險,也造成肩頸、腰背疼痛等常見問題。

然而對抗這些問題,卻只需要工作時定期休息跟伸展身體即可!

你想輕鬆改變現狀嗎?試試看我們的 PomodoRoll 番茄鐘吧! PomodoRoll 番茄鐘會根據你所設定的專注時間,定期建議你 1 項辦公族適用的伸展運動,幫助你打敗久坐所帶來的傷害!

追蹤新知

看完這篇文章了嗎?還意猶未盡的話,追蹤粉絲專頁吧!

我們每天至少分享 1 篇文章/新聞或者實用的軟體/工具,讓你輕鬆增廣見聞提升專業能力!如果你喜歡我們的文章,或是想了解更多特定主題的教學,歡迎到我們的粉絲專頁按讚、留言讓我們知道。你的鼓勵,是我們的原力!

贊助我們的創作

看完這篇文章了嗎? 休息一下,喝杯咖啡吧!

如果你覺得 MyApollo 有讓你獲得實用的資訊,希望能看到更多的技術分享,邀請你贊助我們一杯咖啡,讓我們有更多的動力與精力繼續提供高品質的文章,感謝你的支持!