wwweww
20 lines
905 B
SQL
20 lines
905 B
SQL
CREATE TABLE favorites (
|
|
id BIGINT PRIMARY KEY,
|
|
user_id BIGINT NOT NULL,
|
|
target_type VARCHAR(20) NOT NULL,
|
|
target_id BIGINT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT chk_target_type CHECK (target_type IN ('player', 'shop')),
|
|
UNIQUE(user_id, target_type, target_id)
|
|
);
|
|
|
|
-- 基础索引
|
|
CREATE INDEX idx_favorites_user ON favorites(user_id, created_at DESC);
|
|
CREATE INDEX idx_favorites_target ON favorites(target_type, target_id);
|
|
|
|
-- 复合索引优化收藏列表查询
|
|
CREATE INDEX idx_favorites_user_type_created ON favorites(user_id, target_type, created_at DESC);
|
|
|
|
-- 目标反查索引（统计收藏数）
|
|
CREATE INDEX idx_favorites_target_count ON favorites(target_type, target_id); |