阿川私房教材：學程式，拿 offer！

63 個專案實戰，直接上手！
無需補習，按步驟打造你的面試作品。

小編精選 - 技術文章翻譯 · 10月03日

提高 PostgreSQL 查詢的效能

我最近想要實現一些原始 SQL 查詢並改善它們的效率，以便從資料庫返回的數據能更快。在閱讀幾篇文章並自己實現了一些變更後，我決定在這裡總結一下，讓你可以在你的專案中使用 :)

現在，讓我們來談談 PostgreSQL。這是一個超酷且靈活的開源關聯資料庫管理系統。雖然 PostgreSQL 已經在它的功能上非常出色，但總是有提升查詢速度的空間！想知道怎麼做嗎？我們有一些靈活的策略來提升你的 PostgreSQL 查詢效率。

享受吧！

限制查詢返回的行數

對於小型資料表，我們可能不需要特別注意返回行數（雖然我強烈建議無論如何都要限制它們！）但對於大型資料表，這對於提高查詢性能極為有用。

我們可以通過在 SQL 中使用 LIMIT 關鍵字來實現：

SELECT * FROM products LIMIT 10

使用 `JOIN` 代替子查詢

如果資料表中有許多記錄，執行包含子查詢的查詢可能無法有效執行 - 相對地，我們可以使用 JOIN 關鍵字，這樣通常會快得多。

與其使用以下的子查詢：

SELECT * FROM orders WHERE product_id IN (SELECT id FROM products WHERE name = 'Phone');

不如使用 JOIN 關鍵字，如下所示：

SELECT orders.* FROM orders JOIN products ON orders.product_id = products.id WHERE products.name = 'Phone';

如果你的資料表有大量行，這樣的方式會快得多。

為欄位使用有效率的資料類型

對於經常執行計算的行，例如 average，檢查欄位的資料類型是有益的。例如，使用 smallint 替代 integer 會更有效率。你可以這樣更改：

ALTER TABLE products ALTER COLUMN price TYPE smallint;

對於經常執行的查詢使用 `PREPARE`

如果你知道某些查詢將被頻繁執行，例如 Fetch_Popular_Products_Query，可以使用 PREPARE 關鍵字，如下所示：

PREPARE get_products_by_name (text) AS SELECT * FROM products WHERE name = $1;
EXECUTE get_products_by_name('Phone');

通過這種方式，查詢只需準備一次，執行時會重用以提高性能。

對於經常查詢的使用 `INDEX`

如果你經常根據產品名稱進行搜索，可以像以下這樣創建 name 欄位的索引：

CREATE INDEX products_name_idx ON products (name);

如果你在常規查詢中使用多個 WHERE 或 JOIN 關鍵字，這將特別有效。

不要使用通配符字符

假設我們有一個查詢，用於搜索所有產品，查找名稱以 phone 結尾的產品：

SELECT * FROM products WHERE name LIKE '%phone';

這個查詢將執行對整個 products 資料表的順序掃描，如果資料表很大，這可能會很慢。為了優化這個查詢，你可以將其改寫為：

SELECT * FROM products WHERE name LIKE 'phone%';

這個查詢將使用前面提到的索引進行更快的索引掃描。

使用 `EXPLAIN` 關鍵字來偵錯查詢

EXPLAIN 命令將輸出查詢的執行計劃，這可以幫助你識別任何性能問題，你可以這樣使用它：

EXPLAIN SELECT COUNT(*) FROM products JOIN orders ON products.id = orders.products_id;

使用全文搜索服務以加快查詢/ID 的搜索

通過百萬條記錄搜索特定記錄的名稱，或更棘手的情況 - 名稱的一部分，可能會非常慢。為了解決這個問題，你可以實現一個全文搜索服務，如 ElasticSearch，這樣會快得多。

這樣的運作方式是，你將以 POSTGRESQL_ID/RECORD_NAME 的形式在 ElasticSearch 資料庫中儲存鍵/值對，然後通過這些值搜索完整或部分名稱和已分配的 PostgreSQL ID，最後進行 PostgreSQL 查詢 WHERE id = 'POSTGRESQL_ID' 而不是 WHERE name LIKE 'RECORD_NAME'，這樣會明顯更有效率，因為你將針對特定記錄進行查詢，而不是符合某些搜索條件的記錄。

這種方法對於性能和查詢優化看起來十分出色，但你必須考慮到它將為你的基礎設施增添額外的複雜性和需要維護的另一個服務。