我有一個表產品、一個表訂單和一個表訂單產品。
產品的名稱為 PK（蘋果、香蕉、芒果）和價格。
訂單有一個 created_at 日期和一個作為 PK 的 id。
orderProducts 連接訂單和產品，因此它們有一個 product_name 和一個 order_id。現在，我想顯示過去 24 小時內發生的給定產品的所有訂單。
我使用以下查詢：
SELECT
orders.id,
orders.created_at,
products.name,
products.price
FROM
orderProducts
JOIN products ON
products.name=orderProducts.product
JOIN orders ON
orders.id=orderProducts.order
WHERE
products.name='banana'
AND
orders.created_at BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
ORDER BY
orders.created_at
這有效，但我想用索引優化這個查詢。該索引需要首先按以下順序排序
- 產品名稱，因此可以過濾
- 然后訂單的 created_at 降序排列，所以它只能選擇 24 小時前的那些
問題是，從我所見，索引只能在一個表上創建，而不能將另一個表的值連接到它。由于兩個單獨的索引也不能解決這個問題，我想知道是否有其他方法來優化這個特定的查詢。
以下是表腳本：
CREATE TABLE products
(
name text PRIMARY KEY,
price integer,
)
CREATE TABLE orders
(
id SERIAL PRIMARY KEY,
created_at TIMESTAMP DEFAULT NOW(),
)
CREATE TABLE orderProducts
(
product text REFERENCES products(name),
"order" integer REFERENCES orders(id),
)
uj5u.com熱心網友回復：
首先。請不要在任何地方放置索引 - 這會導致操作更改速度變慢......
正如@Laurenz Albe 所提議的——不要猜測——檢查。
除此之外。請注意，您知道產品名稱，價格是重復的 - 因此您可以查詢一次。詢問在您的情況下，兩個查詢是否會比單個查詢更快......檢查一下。
請閱讀檔案。我會嘗試這個索引：
create index orders_id_created_at on orders(created_at desc, id)
通常 id 應該先行，因為它是唯一的，但是這里系統應該能夠過濾掉兩個謂詞 - where/join。這里只是猜測。
orderProducts我想在兩列上都看到索引，但是對于這個查詢，只需要一個。在實踐中，您是從productstoorders或其他方式出發 - 兩條路徑都是可能的，這就是為什么我寫了關于對兩列進行索引的原因。我會使用兩個單獨的索引：
create index orderproducts_product_id on orderproducts (product_id) include (order_id);
create index orderproducts_order_id on orderproducts (order_id) include (product_id);
可能這并沒有太大變化，但是......想法是只使用索引，而不是表本身。
uj5u.com熱心網友回復：
這些規則在性能方面很重要：
- 整數索引比字串索引快，因此，您應該盡量使主鍵始終為整數。因為連接表也使用主鍵。
- 如果 when in where 子句總是使用兩個欄位，那么我們必須為這兩個欄位創建一個索引。
- 外鍵未編入索引，您必須手動為外鍵欄位創建索引。
因此，推薦的表腳本是：
CREATE TABLE products
(
id serial primary key,
name text,
price integer
);
CREATE UNIQUE INDEX products_name_idx ON products USING btree (name);
CREATE TABLE orders
(
id SERIAL PRIMARY KEY,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX orders_created_at_idx ON orders USING btree (created_at);
CREATE TABLE orderProducts
(
product_id integer REFERENCES products(id),
order_id integer REFERENCES orders(id)
);
CREATE INDEX orderproducts_product_id_idx ON orderproducts USING btree (product_id, order_id);
---- OR ----
CREATE INDEX orderproducts_product_id ON orderproducts (product_id);
CREATE INDEX orderproducts_order_id ON orderproducts (order_id);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/349173.html
標籤：PostgreSQL 优化 索引 数据库索引