iT邦幫忙

2025 iThome 鐵人賽

DAY 18
1

在資料庫管理中,索引是一個不可忽視的重要工具。它能大幅提升查詢效能,讓資料庫能以更快的速度搜尋特定資料,然而,索引的使用也伴隨著系統額外的負擔,因此必須謹慎使用。接下來將會介紹如何使用索引,以及有哪些不同的索引。

為什麼需要索引?

假如使用下面 SQL 產生一百萬筆的假資料。

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    level VARCHAR(10) NOT NULL,
    message TEXT NOT NULL
);

DO $$
DECLARE
    log_levels TEXT[] := ARRAY['INFO', 'DEBUG', 'WARN', 'ERROR'];
    log_messages TEXT[] := ARRAY[
        'User logged in successfully',
        'System startup completed',
        'Database connection established',
        'Configuration file loaded',
        'User session timeout',
        'File not found: config.yml',
        'Permission denied for user: guest',
        'API request received',
        'Cache cleared successfully',
        'Unexpected null value encountered',
        'Server shutting down',
        'Memory usage exceeded threshold',
        'Scheduled task executed',
        'Invalid input format',
        'Failed to send email notification'
    ];
    i INTEGER;
BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO logs (timestamp, level, message)
        VALUES (
            NOW() - (random() * INTERVAL '365 days'), -- 隨機生成過去一年的時間
            log_levels[FLOOR(random() * 4 + 1)::INTEGER], -- 隨機選擇日誌級別
            log_messages[FLOOR(random() * array_length(log_messages, 1) + 1)::INTEGER] -- 隨機選擇日誌訊息
        );
    END LOOP;
END $$;

如果使用下面 query 把 level 是 ERROR 的 log 撈出來,會花費0.317秒

SELECT * FROM logs WHERE level = 'ERROR'

接著針對 level 這個欄位加上索引。

CREATE INDEX logs_level_index ON logs (level);

然後再次 query 出 levelERROR 的 log,可以發現加上索引之後,只花費0.191秒

減少搜尋的時間,就是加入索引的最大優點。當然,索引也並非沒有缺點,太多索引會影響新增和更新的效能,所以也不能濫用,不使用索引時,要記得移除

索引運作機制

當我們建立索引的時候,資料庫會根據索引的欄位對所有資料進行掃描,並且依據指定的索引類型,為這些資料生成對應的數據結構,使查詢的時候,可以加快搜尋資料的速度,例如使用 B-Tree 這個索引類型,資料庫會依照 B-Tree 演算法去產生資料結構,像是下面的範例。

當我們使用 WHERE 條件要找該欄位為 6 的那筆資料時,就可以快速地透過 B-Tree 的結構找到對應的資料。當然,如果不使用 WHERE 或其他條件搜尋,而是每次都把所有資料撈出來,建立索引就對查詢沒有幫助了。

而當有新的資料被新增進來,資料庫需要對現有的資料結構進行調整,以增加查詢的速度,這也是為什麼前面強調不使用索引時,也要記得移除的原因。


上一篇
Day 17: Generated Columns 的作用與如何使用
下一篇
Day 19: 深入淺出索引 (下)
系列文
我所不知道的PostgreSQL 30天30
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言