Postgresql
僅為 PostgreSQL 中的最新條目編制索引
考慮下表:
|-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | id | shuffle | user_id | ... | notify_at | |----|---------|----------|-----|---------------------| | 1 | 4D6G8Z1 | 542 | ... | 2019-12-01 14:00:00 | | 2 | 64G264D | 6 | ... | 2019-12-28 14:00:00 | | 3 | 4IPF93D | 58 | ... | 2020-01-01 14:00:00 | | 4 | D25LF03 | 58 | ... | 2020-01-14 14:00:00 | | 5 | G04LDWE | 684 | ... | 2020-03-02 13:00:00 |
在這個表中,大多數請求都不是針對
id
列,而是針對user_id
andnotify_at
,這是一個 64 位時間戳(沒有 2038 錯誤):SELECT * FROM [raffle] WHERE [user_id] = ? AND [notify_at] = ?
該表按分鐘增長,但這不是問題,而是
notify_at
當月的記錄比其他記錄訪問次數最多。在 10.000.000 條記錄中,索引user_id
和notify_at
總和為 160MB,其中只有 1% 被頻繁訪問。有沒有辦法優化索引(或任何其他策略)以使檢索當月的記錄更快捷(例如,“嘗試使用索引而不是掃描整個表的記錄)?
更新 1:我這樣問是因為該表包含許多通知。隨著時間的推移,這會變得越來越大,並且 SQL 查詢只會佔用當月的那些:
SELECT * FROM raffle WHERE user_id = 542 AND notify_at > '2020-01-01 00:00:00' AND notify_at < '2020-01-31 23:59:59'
如您所見,索引也會變大。
您要查找的索引類型是Range Type
另一個可能的解決方案需要修改表。這種更簡單的方法使用簡單的相等方法並使用複合索引
create table plrange ( id serial primary key, user_id int, notify_start date default now()::date, notify_end date default now()::date + 30); insert into plrange (user_id, notify_start, notify_end) select (random()*100)::int, now()::date - (random()*10)::int , now()::date from generate_series(1,1000); create index on plrange using btree(user_id, notify_end ); explain select * from plrange where user_id =4 and notify_end > now()::date