# 1. 数据库设计
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
sku VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
category VARCHAR(100) NOT NULL,
data JSONB NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 2. 创建索引
CREATE INDEX idx_fgedu_products_category ON fgedu_products (category);
CREATE INDEX idx_fgedu_products_data ON fgedu_products USING GIN (data);
CREATE INDEX idx_fgedu_products_brand ON fgedu_products ((data->>’brand’));
CREATE INDEX idx_fgedu_products_price ON fgedu_products (((data->>’price’)::numeric));

# 3. 插入产品数据
INSERT INTO fgedu_products (sku, name, category, data)
VALUES
(‘PROD001’, ‘Smartphone’, ‘Electronics’, ‘{
“brand”: “Apple”,
“model”: “iPhone 15”,
“price”: 999.99,
“specs”: {
“display”: “6.1 inches”,
“storage”: “128GB”,
“camera”: “48MP”,
“battery”: “4000mAh”
},
“colors”: [“black”, “white”, “red”],
“stock”: 100
}’),
(‘PROD002’, ‘Laptop’, ‘Electronics’, ‘{
“brand”: “Dell”,
“model”: “XPS 13”,
“price”: 1299.99,
“specs”: {
“display”: “13.3 inches”,
“processor”: “Intel i7”,
“ram”: “16GB”,
“storage”: “512GB SSD”
},
“colors”: [“silver”, “carbon black”],
“stock”: 50
}’),
(‘PROD003’, ‘T-Shirt’, ‘Clothing’, ‘{
“brand”: “Nike”,
“model”: “Sportswear”,
“price”: 29.99,
“specs”: {
“material”: “cotton”,
“size”: [“S”, “M”, “L”, “XL”],
“fit”: “regular”
},
“colors”: [“black”, “white”, “blue”],
“stock”: 200
}’);

# 4. 查询示例
— 按类别查询产品
SELECT id, sku, name, data->>’price’ AS price
FROM fgedu_products
WHERE category = ‘Electronics’;

— 按品牌查询产品
SELECT id, sku, name, data->>’model’ AS model
FROM fgedu_products
WHERE data->>’brand’ = ‘Apple’;

— 按价格范围查询产品
SELECT id, sku, name, data->>’price’ AS price
FROM fgedu_products
WHERE (data->>’price’)::numeric BETWEEN 100 AND 1000;

— 按规格查询产品
SELECT id, sku, name, data->>’brand’ AS brand
FROM fgedu_products
WHERE data->’specs’->>’storage’ = ‘128GB’;

— 按颜色查询产品
SELECT id, sku, name, data->>’brand’ AS brand
FROM fgedu_products
WHERE data->’colors’ @> ‘”black”‘::jsonb;

# 5. 更新产品数据
— 更新库存
UPDATE fgedu_products
SET data = jsonb_set(data, ‘{stock}’, ’95’)
WHERE sku = ‘PROD001’;

— 添加新颜色
UPDATE fgedu_products
SET data = jsonb_set(data, ‘{colors}’, data->’colors’ || ‘”green”‘::jsonb)
WHERE sku = ‘PROD003’;

— 更新价格
UPDATE fgedu_products
SET data = data || ‘{“price”: 899.99}’::jsonb
WHERE sku = ‘PROD001’;

# 1. 数据库设计
CREATE TABLE fgedu_fgedu_settings (
id SERIAL PRIMARY KEY,
fgedu_id INTEGER REFERENCES fgedu_fgedus(id),
settings JSONB NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 2. 创建索引
CREATE INDEX idx_fgedu_fgedu_settings_fgedu_id ON fgedu_fgedu_settings (fgedu_id);
CREATE INDEX idx_fgedu_fgedu_settings_settings ON fgedu_fgedu_settings USING GIN (settings);

# 3. 插入用户设置
INSERT INTO fgedu_fgedu_settings (fgedu_id, settings)
VALUES
(1, ‘{
“notifications”: {
“email”: true,
“sms”: false,
“push”: true
},
“preferences”: {
“theme”: “dark”,
“language”: “en”,
“timezone”: “UTC”
},
“privacy”: {
“public_profile”: true,
“show_email”: false,
“show_phone”: false
}
}’),
(2, ‘{
“notifications”: {
“email”: true,
“sms”: true,
“push”: false
},
“preferences”: {
“theme”: “light”,
“language”: “zh”,
“timezone”: “Asia/Shanghai”
},
“privacy”: {
“public_profile”: false,
“show_email”: true,
“show_phone”: true
}
}’);

# 4. 查询示例
— 获取用户的通知设置
SELECT fgedu_id, settings->’notifications’ AS notifications
FROM fgedu_fgedu_settings
WHERE fgedu_id = 1;

— 查询使用深色主题的用户
SELECT fgedu_id
FROM fgedu_fgedu_settings
WHERE settings->’preferences’->>’theme’ = ‘dark’;

— 查询启用邮件通知的用户
SELECT fgedu_id
FROM fgedu_fgedu_settings
WHERE settings->’notifications’->>’email’ = ‘true’;

# 5. 更新用户设置
— 更新主题设置
UPDATE fgedu_fgedu_settings
SET settings = jsonb_set(settings, ‘{preferences, theme}’, ‘”light”‘)
WHERE fgedu_id = 1;

— 更新通知设置
UPDATE fgedu_fgedu_settings
SET settings = jsonb_set(settings, ‘{notifications, push}’, ‘false’)
WHERE fgedu_id = 1;

— 添加新的设置项
UPDATE fgedu_fgedu_settings
SET settings = settings || ‘{“accessibility”: {“font_size”: “medium”, “high_contrast”: false}}’::jsonb
WHERE fgedu_id = 1;

# 1. 使用适当的索引
— 为频繁查询的字段创建索引
CREATE INDEX idx_fgedu_products_price ON fgedu_products (((data->>’price’)::numeric));

— 使用索引的查询
EXPLAIN ANALYZE SELECT * FROM fgedu_products WHERE (data->>’price’)::numeric > 1000;

# 2. 避免全表扫描
— 不好的查询（可能导致全表扫描）
SELECT * FROM fgedu_products WHERE data @> ‘{“specs”: {“camera”: “48MP”}}’;

— 好的查询（使用索引）
CREATE INDEX idx_fgedu_products_camera ON fgedu_products ((data->’specs’->>’camera’));
SELECT * FROM fgedu_products WHERE data->’specs’->>’camera’ = ’48MP’;

# 3. 使用JSON路径表达式
— 使用jsonb_path_exists进行复杂查询
SELECT * FROM fgedu_products
WHERE jsonb_path_exists(data, ‘$.specs.storage ? (@ like_regex “128GB|256GB”)’);

# 4. 限制返回数据
— 只返回需要的字段
SELECT id, name, data->>’price’ AS price
FROM fgedu_products
WHERE data->>’brand’ = ‘Apple’;

# 5. 使用LIMIT和OFFSET
— 分页查询
SELECT * FROM fgedu_products
WHERE data->>’category’ = ‘Electronics’
ORDER BY (data->>’price’)::numeric DESC
LIMIT 10 OFFSET 0;

# 1. 压缩JSON数据
— PostgreSQL会自动压缩JSONB数据
— 可以通过pg_column_size查看存储大小
SELECT id, name, pg_column_size(data) AS size
FROM fgedu_products
ORDER BY size DESC;

# 2. 扁平化JSON结构
— 不好的结构（深层嵌套）
{
“fgedu”: {
“profile”: {
“address”: {
“city”: “New York”
}
}
}
}

— 好的结构（扁平化）
{
“fgedu_profile_address_city”: “New York”
}

# 3. 避免存储重复数据
— 不好的做法（存储重复数据）
{
“product”: {
“name”: “Smartphone”,
“category”: “Electronics”,
“brand”: “Apple”,
“model”: “iPhone 15”
},
“order”: {
“product_name”: “Smartphone”,
“product_category”: “Electronics”
}
}

— 好的做法（避免重复）
{
“product”: {
“id”: 1,
“name”: “Smartphone”,
“category”: “Electronics”,
“brand”: “Apple”,
“model”: “iPhone 15”
},
“order”: {
“product_id”: 1,
“quantity”: 2
}
}

# 4. 合理使用JSONB与传统字段
— 对于频繁查询的字段，使用传统字段
— 对于结构多变的字段，使用JSONB
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL, — 传统字段
price DECIMAL(10,2) NOT NULL, — 传统字段
category VARCHAR(100) NOT NULL, — 传统字段
attributes JSONB, — JSONB字段
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 2. 索引未使用
# 错误信息：查询执行计划显示全表扫描
# 解决方法：
– 确保创建了适当的索引
– 检查查询条件是否使用了索引字段
– 运行VACUUM ANALYZE更新统计信息

# 3. JSONB字段过大
# 错误信息：ERROR: row is too big
# 解决方法：
– 拆分JSONB字段为多个字段
– 减少JSONB字段的大小
– 考虑使用大对象存储大型JSON数据

# 4. 性能问题
# 错误信息：查询执行缓慢
# 解决方法：
– 创建适当的索引
– 优化查询语句
– 考虑使用物化视图
– 调整数据库参数

# 5. JSON路径查询错误
# 错误信息：ERROR: jsonpath syntax error
# 解决方法：
– 检查JSON路径语法
– 使用正确的JSON路径表达式
– 风哥教程参考PostgreSQL官方文档中的JSON路径语法

# 1. 监控JSONB字段大小
SELECT table_name, column_name, avg(pg_column_size(column_name)) as avg_size
FROM information_schema.columns
JOIN pg_stat_fgedu_tables ON table_name = relname
WHERE data_type = ‘jsonb’
GROUP BY table_name, column_name;

# 2. 监控JSONB查询性能
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE ‘%jsonb%’ OR query LIKE ‘%data->%’
ORDER BY total_exec_time DESC
LIMIT 10;

# 3. 监控索引使用情况
SELECT indexrelid::regclass AS index_name,
relid::regclass AS table_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_fgedu_indexes
JOIN pg_stat_fgedu_tables ON pg_stat_fgedu_indexes.relid = pg_stat_fgedu_tables.relid
WHERE indexrelid::regclass::text LIKE ‘%jsonb%’ OR
indexrelid::regclass::text LIKE ‘%data%’
ORDER BY idx_scan DESC;

# 4. 监控JSONB更新操作
SELECT query, calls, total_exec_time
FROM pg_stat_statements
WHERE query LIKE ‘%UPDATE%jsonb%’ OR query LIKE ‘%jsonb_set%’
ORDER BY total_exec_time DESC
LIMIT 10;