sql: Indexing & Performance using postgresql 18

 

-- ============================================================
-- PostgreSQL 18 珠宝行业「索引与性能优化」
-- 合并版 - 一次性执行完整环境搭建 geovindu,Geovin Du
-- ============================================================
-- 执行方式：psql -U postgres -d jewelry_indexing_db -f pg18_indexing_full.sql
-- 注意：执行前请确保已创建数据库：
-- CREATE DATABASE jewelry_indexing_db WITH ENCODING 'UTF8' LC_COLLATE='zh_CN.UTF-8' LC_CTYPE='zh_CN.UTF-8' TEMPLATE=template0;
-- \c jewelry_indexing_db
-- ============================================================


-- ============================================================
-- 模块来源: pg18_indexing_schema.sql
-- ============================================================

-- ============================================================
-- PostgreSQL 18 珠宝行业「索引与性能优化」
-- 模块1：建库建表 + 索引 + 分区表 + 视图 + 扩展
-- ============================================================

-- 创建数据库（需手动执行）
-- CREATE DATABASE jewelry_indexing_db WITH ENCODING 'UTF8' LC_COLLATE='zh_CN.UTF-8' LC_CTYPE='zh_CN.UTF-8' TEMPLATE=template0;
-- \c jewelry_indexing_db

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS earthdistance;
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 枚举类型
CREATE TYPE product_category AS ENUM (
    '戒指', '项链', '手链', '耳环', '胸针',
    '手表', '首饰盒', 'wedding_set', '吊坠', '发饰'
);

CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled', 'returned');
CREATE TYPE inventory_status AS ENUM ('in_stock', 'low_stock', 'out_of_stock', 'discontinued');

-- 1. 分类表
CREATE TABLE categories (
    category_id     SERIAL PRIMARY KEY,
    category_name   VARCHAR(100) NOT NULL,
    parent_id       INTEGER REFERENCES categories(category_id),
    description     TEXT,
    sort_order      INTEGER DEFAULT 0,
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 产品表
CREATE TABLE products (
    product_id      SERIAL PRIMARY KEY,
    product_name    VARCHAR(255) NOT NULL,
    category_id     INTEGER REFERENCES categories(category_id),
    brand           VARCHAR(100),
    material        VARCHAR(100),
    gemstone        VARCHAR(100),
    carat_weight    DECIMAL(6,2),
    color_grade     VARCHAR(20),
    clarity_grade   VARCHAR(20),
    cut_grade       VARCHAR(20),
    price           DECIMAL(12,2) NOT NULL,
    cost            DECIMAL(12,2),
    stock_quantity  INTEGER DEFAULT 0,
    sku             VARCHAR(50) UNIQUE NOT NULL,
    description     TEXT,
    specifications  JSONB,
    image_url       VARCHAR(500),
    status          VARCHAR(20) DEFAULT 'active',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. 客户表
CREATE TABLE customers (
    customer_id     SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    phone           VARCHAR(20),
    gender          VARCHAR(10),
    birthdate       DATE,
    membership_level VARCHAR(20) DEFAULT 'regular',
    total_spent     DECIMAL(12,2) DEFAULT 0,
    loyalty_points  INTEGER DEFAULT 0,
    address         JSONB,
    notes           TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. 门店表（含地理位置）
CREATE TABLE stores (
    store_id        SERIAL PRIMARY KEY,
    store_name      VARCHAR(200) NOT NULL,
    city            VARCHAR(100),
    province        VARCHAR(100),
    address         VARCHAR(300),
    location        POINT,
    phone           VARCHAR(20),
    manager_name    VARCHAR(100),
    open_date       DATE,
    square_meters   DECIMAL(8,2),
    is_active       BOOLEAN DEFAULT TRUE
);

-- 5. 订单表（分区表 - 按日期范围分区）
CREATE TABLE orders (
    order_id        SERIAL PRIMARY KEY,
    customer_id     INTEGER REFERENCES customers(customer_id),
    store_id        INTEGER REFERENCES stores(store_id),
    order_number    VARCHAR(30) UNIQUE NOT NULL,
    order_date      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status          order_status DEFAULT 'pending',
    total_amount    DECIMAL(12,2) NOT NULL,
    discount_amount DECIMAL(12,2) DEFAULT 0,
    tax_amount      DECIMAL(12,2) DEFAULT 0,
    final_amount    DECIMAL(12,2) NOT NULL,
    payment_method  VARCHAR(50),
    shipping_method VARCHAR(50),
    tracking_number VARCHAR(100),
    notes           TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (order_date);

-- 创建分区
CREATE TABLE orders_q1_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_q2_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE orders_q3_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE orders_q4_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
CREATE TABLE orders_q1_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE orders_q2_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- 6. 订单明细表
CREATE TABLE order_details (
    detail_id       SERIAL PRIMARY KEY,
    order_id        INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id      INTEGER REFERENCES products(product_id),
    quantity        INTEGER NOT NULL DEFAULT 1,
    unit_price      DECIMAL(12,2) NOT NULL,
    discount_pct    DECIMAL(5,2) DEFAULT 0,
    subtotal        DECIMAL(12,2) NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 7. 库存表
CREATE TABLE inventory (
    inventory_id    SERIAL PRIMARY KEY,
    product_id      INTEGER REFERENCES products(product_id),
    store_id        INTEGER REFERENCES stores(store_id),
    quantity        INTEGER NOT NULL DEFAULT 0,
    reorder_level   INTEGER DEFAULT 5,
    max_stock       INTEGER,
    last_restock_date DATE,
    status          inventory_status DEFAULT 'in_stock',
    warehouse_zone  VARCHAR(20),
    shelf_location  VARCHAR(50),
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 8. 评价表
CREATE TABLE reviews (
    review_id       SERIAL PRIMARY KEY,
    product_id      INTEGER REFERENCES products(product_id),
    customer_id     INTEGER REFERENCES customers(customer_id),
    order_id        INTEGER REFERENCES orders(order_id),
    rating          INTEGER CHECK (rating >= 1 AND rating <= 5),
    title           VARCHAR(200),
    comment         TEXT,
    is_verified     BOOLEAN DEFAULT FALSE,
    helpful_count   INTEGER DEFAULT 0,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 9. 搜索日志表（全文索引演示）
CREATE TABLE search_logs (
    log_id          SERIAL PRIMARY KEY,
    customer_id     INTEGER REFERENCES customers(customer_id),
    search_query    VARCHAR(255) NOT NULL,
    results_count   INTEGER DEFAULT 0,
    clicked_product_id INTEGER,
    session_id      VARCHAR(100),
    ip_address      INET,
    user_agent      VARCHAR(500),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 10. 库存流水表（分区表）
CREATE TABLE inventory_logs (
    log_id          BIGSERIAL PRIMARY KEY,
    product_id      INTEGER REFERENCES products(product_id),
    store_id        INTEGER REFERENCES stores(store_id),
    transaction_type VARCHAR(20) NOT NULL,
    quantity_change INTEGER NOT NULL,
    quantity_before INTEGER NOT NULL,
    quantity_after  INTEGER NOT NULL,
    reference_id    VARCHAR(100),
    notes           TEXT,
    created_by      VARCHAR(100) DEFAULT CURRENT_USER,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_at);

CREATE TABLE inventory_logs_2024_q1 PARTITION OF inventory_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE inventory_logs_2024_q2 PARTITION OF inventory_logs
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE inventory_logs_2024_q3 PARTITION OF inventory_logs
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE inventory_logs_2024_q4 PARTITION OF inventory_logs
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
CREATE TABLE inventory_logs_2025_q1 PARTITION OF inventory_logs
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- ============================================================
-- B-Tree 索引（默认索引类型）
-- ============================================================
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_brand ON products(brand);
CREATE INDEX idx_products_material ON products(material);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_store ON orders(store_id);
CREATE INDEX idx_order_details_order ON order_details(order_id);
CREATE INDEX idx_order_details_product ON order_details(product_id);
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_customers_name ON customers(last_name, first_name);
CREATE INDEX idx_inventory_product ON inventory(product_id);
CREATE INDEX idx_inventory_store ON inventory(store_id);
CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_reviews_customer ON reviews(customer_id);
CREATE INDEX idx_search_logs_query ON search_logs(search_query);
CREATE INDEX idx_search_logs_date ON search_logs(created_at);

-- ============================================================
-- 复合索引
-- ============================================================
CREATE INDEX idx_products_cat_price ON products(category_id, price);
CREATE INDEX idx_products_brand_status ON products(brand, status);
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
CREATE INDEX idx_orders_date_status ON orders(order_date, status);
CREATE INDEX idx_order_details_prod_qty ON order_details(product_id, quantity);
CREATE INDEX idx_inventory_prod_store ON inventory(product_id, store_id);
CREATE INDEX idx_reviews_prod_rating ON reviews(product_id, rating);

-- ============================================================
-- 部分索引（Filtered Index）
-- ============================================================
CREATE INDEX idx_products_active ON products(product_name, price) WHERE status = 'active';
CREATE INDEX idx_orders_pending ON orders(order_number, total_amount) WHERE status = 'pending';
CREATE INDEX idx_customers_premium ON customers(email, total_spent) WHERE membership_level IN ('gold', 'platinum');

-- ============================================================
-- 表达式索引
-- ============================================================
CREATE INDEX idx_products_lower_name ON products(lower(product_name));
CREATE INDEX idx_products_price_range ON products((price * 0.8)) WHERE price > 1000;
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM order_date));

-- ============================================================
-- GIN 索引（全文检索 + JSONB + 数组）
-- ============================================================
CREATE INDEX idx_products_search_gin ON products USING GIN (
    to_tsvector('chinese', product_name || ' ' || COALESCE(description, '') || ' ' || COALESCE(material, ''))
);
CREATE INDEX idx_products_specs_gin ON products USING GIN (specifications);
CREATE INDEX idx_customers_addr_gin ON customers USING GIN (address);
CREATE INDEX idx_search_logs_query_gin ON search_logs USING GIN (search_query);

-- ============================================================
-- GiST 索引（空间索引）
-- ============================================================
CREATE INDEX idx_stores_location_gist ON stores USING GiST (location);

-- ============================================================
-- pg_trgm 索引（模糊搜索）
-- ============================================================
CREATE INDEX idx_products_name_trgm ON products USING gin (product_name gin_trgm_ops);
CREATE INDEX idx_categories_name_trgm ON categories USING gin (category_name gin_trgm_ops);
CREATE INDEX idx_search_logs_query_trgm ON search_logs USING gin (search_query gin_trgm_ops);

-- ============================================================
-- Bloom 索引（高基数列）
-- ============================================================
CREATE INDEX idx_products_material_bloom ON products USING bloom (material, gemstone, color_grade)
    WITH (length = 64);

-- ============================================================
-- 视图
-- ============================================================
CREATE VIEW v_product_sales_summary AS
SELECT
    p.product_id, p.product_name, p.category_id, c.category_name,
    p.brand, p.material, p.price,
    COUNT(od.detail_id) AS total_sold,
    SUM(od.subtotal) AS total_revenue,
    AVG(r.rating) AS avg_rating,
    MAX(r.created_at) AS last_review_date
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
LEFT JOIN order_details od ON p.product_id = od.product_id
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.product_name, p.category_id, c.category_name,
         p.brand, p.material, p.price;

CREATE VIEW v_store_performance AS
SELECT
    s.store_id, s.store_name, s.city,
    COUNT(o.order_id) AS total_orders,
    SUM(o.final_amount) AS total_revenue,
    AVG(o.total_amount) AS avg_order_value,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM stores s
LEFT JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_id, s.store_name, s.city;

CREATE VIEW v_customer_360 AS
SELECT
    c.customer_id, c.first_name, c.last_name, c.email,
    c.membership_level, c.total_spent,
    COUNT(o.order_id) AS total_orders,
    MAX(o.order_date) AS last_order_date,
    COUNT(DISTINCT p.category_id) AS categories_purchased
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_details od ON o.order_id = od.order_id
LEFT JOIN products p ON od.product_id = p.product_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email,
         c.membership_level, c.total_spent;

-- ============================================================
-- 触发器函数
-- ============================================================
CREATE OR REPLACE FUNCTION fn_update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_updated
    BEFORE UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION fn_update_updated_at();

CREATE TRIGGER trg_orders_updated
    BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION fn_update_updated_at();

CREATE TRIGGER trg_inventory_updated
    BEFORE UPDATE ON inventory
    FOR EACH ROW EXECUTE FUNCTION fn_update_updated_at();

-- ============================================================
-- 同义词视图
-- ============================================================
CREATE VIEW v_items AS SELECT * FROM products;
CREATE VIEW v_transactions AS SELECT * FROM order_details;

-- ============================================================
-- 序列
-- ============================================================
CREATE SEQUENCE seq_product_code START WITH 1000 INCREMENT BY 1;
CREATE SEQUENCE seq_order_prefix START WITH 20240001 INCREMENT BY 1;

-- 验证表创建
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
ORDER BY table_name;

SELECT schemaname, tablename FROM pg_tables
WHERE schemaname = 'public' AND partitioned = 'true';



-- ============================================================
-- 模块来源: pg18_indexing_data.sql
-- ============================================================

-- ============================================================
-- PostgreSQL 18 珠宝行业「索引与性能优化」
-- 模块2：批量测试数据
-- ============================================================

-- 1. 分类数据
INSERT INTO categories (category_name, parent_id, description, sort_order) VALUES
('戒指', NULL, '各类戒指', 1),
('项链', NULL, '各类项链', 2),
('手链', NULL, '各类手链', 3),
('耳环', NULL, '各类耳环', 4),
('胸针', NULL, '各类胸针', 5),
('手表', NULL, '各类手表', 6),
('首饰盒', NULL, '首饰收纳', 7),
('吊坠', NULL, '各类吊坠', 8),
('发饰', NULL, '发饰配饰', 9),
('婚庆套装', NULL, '婚礼套装', 10);

-- 2. 产品数据（55款珠宝）
INSERT INTO products (product_name, category_id, brand, material, gemstone, carat_weight, color_grade, clarity_grade, cut_grade, price, cost, stock_quantity, sku, description, specifications, status) VALUES
('经典六爪钻戒', 1, 'Tiffany', '铂金', '钻石', 1.00, 'D', 'VVS1', 'Excellent', 58800.00, 28000.00, 15, 'RF-TF-001', '经典六爪镶嵌钻石戒指', '{"size":"12号","metal":"Pt950","weight":"3.2g"}', 'active'),
('玫瑰金钻戒', 1, 'Cartier', '玫瑰金', '钻石', 0.50, 'E', 'VS1', 'Very Good', 25800.00, 12000.00, 20, 'RF-CT-002', '浪漫玫瑰金钻石戒指', '{"size":"11号","metal":"18KRG","weight":"2.8g"}', 'active'),
('黄金转运珠戒指', 1, '周大福', '黄金', '红玛瑙', NULL, NULL, NULL, NULL, 3680.00, 1800.00, 50, 'RF-ZDF-003', '传统黄金转运珠戒指', '{"size":"10号","metal":"足金999","weight":"5.6g"}', 'active'),
('蓝宝石戒指', 1, 'Harry Winston', '铂金', '蓝宝石', 2.50, 'Royal Blue', 'VVS2', 'Excellent', 89000.00, 45000.00, 5, 'RF-HW-004', '皇家蓝蓝宝石戒指', '{"size":"13号","metal":"Pt950","weight":"4.1g"}', 'active'),
('翡翠戒指', 1, '瑞丽缘', '18K金', '翡翠', NULL, '正绿', NULL, NULL, 15800.00, 8000.00, 8, 'RF-RLY-005', '冰种翡翠戒指', '{"size":"12号","metal":"18KYG","weight":"6.5g"}', 'active'),
('经典四叶草项链', 2, 'Van Cleef', '黄金', '玛瑙', NULL, NULL, NULL, NULL, 32800.00, 16000.00, 12, 'NK-VCA-001', '梵克雅宝四叶草项链', '{"length":"42cm","metal":"18KYG","weight":"8.5g"}', 'active'),
('珍珠项链', 2, 'Mikimoto', '18K金', '珍珠', NULL, NULL, NULL, NULL, 18800.00, 9000.00, 15, 'NK-MK-002', 'Akoya珍珠项链', '{"length":"45cm","pearl_size":"7-7.5mm","metal":"18KYG"}', 'active'),
('钻石吊坠项链', 2, 'Tiffany', '铂金', '钻石', 0.30, 'F', 'VS2', 'Excellent', 28500.00, 14000.00, 10, 'NK-TF-003', 'Tiffany钻石吊坠项链', '{"length":"45cm","diamond":"0.3ct","metal":"Pt950"}', 'active'),
('红绳手链', 2, '周生生', '银', '红绳', NULL, NULL, NULL, NULL, 680.00, 200.00, 100, 'NK-ZSS-004', '红绳编织手链', '{"length":"18cm","material":"红绳+银"}', 'active'),
('黄金手链', 2, '周大福', '黄金', NULL, NULL, NULL, NULL, NULL, 5680.00, 2800.00, 30, 'BS-ZDF-001', '古法黄金手链', '{"length":"18cm","metal":"足金999","weight":"12.5g"}', 'active'),
('珍珠手链', 2, 'Mikimoto', '18K金', '珍珠', NULL, NULL, NULL, NULL, 12800.00, 6500.00, 18, 'BS-MK-001', 'Akoya珍珠手链', '{"length":"17cm","pearl_size":"6.5-7mm","metal":"18KYG"}', 'active'),
('钻石手链', 2, 'Cartier', '铂金', '钻石', 1.20, 'G', 'VS1', 'Very Good', 68000.00, 35000.00, 6, 'BS-CT-001', 'Cartier钻石手链', '{"length":"18cm","diamond":"1.2ct","metal":"Pt950"}', 'active'),
('玛瑙手链', 2, '瑞丽缘', '银', '玛瑙', NULL, NULL, NULL, NULL, 880.00, 300.00, 60, 'BS-RLY-001', '南红玛瑙手链', '{"length":"18cm","material":"S925银+南红"}', 'active'),
('经典耳钉', 4, 'Tiffany', '铂金', '钻石', 0.20, 'D', 'VVS2', 'Excellent', 18800.00, 9500.00, 20, 'EP-TF-001', 'Tiffany钻石耳钉', '{"ear_type":"耳钉","diamond":"0.2ct","metal":"Pt950"}', 'active'),
('珍珠耳环', 4, 'Mikimoto', '18K金', '珍珠', NULL, NULL, NULL, NULL, 6800.00, 3200.00, 25, 'EP-MK-001', 'Mikimoto珍珠耳环', '{"ear_type":"耳环","pearl_size":"8mm","metal":"18KYG"}', 'active'),
('黄金耳环', 4, '周大福', '黄金', NULL, NULL, NULL, NULL, NULL, 2880.00, 1400.00, 40, 'EP-ZDF-001', '古法黄金耳环', '{"ear_type":"耳钉","metal":"足金999","weight":"4.2g"}', 'active'),
('蓝宝石耳环', 4, 'Harry Winston', '铂金', '蓝宝石', 1.80, 'Cornflower Blue', 'VVS1', 'Excellent', 78000.00, 40000.00, 4, 'EP-HW-001', '矢车菊蓝宝石耳环', '{"ear_type":"耳环","sapphire":"1.8ct","metal":"Pt950"}', 'active'),
('经典胸针', 5, 'Cartier', '黄金', '钻石', 0.50, NULL, NULL, NULL, 25800.00, 13000.00, 8, 'BX-CT-001', 'Cartier钻石胸针', '{"material":"18KYG+钻石","weight":"15.6g"}', 'active'),
('珍珠胸针', 5, 'Mikimoto', '18K金', '珍珠', NULL, NULL, NULL, NULL, 8800.00, 4200.00, 12, 'BX-MK-001', 'Mikimoto珍珠胸针', '{"material":"18KYG+珍珠"}', 'active'),
('机械手表', 6, 'Rolex', '不锈钢', NULL, NULL, NULL, NULL, NULL, 88000.00, 45000.00, 10, 'WT-RLX-001', 'Rolex机械手表', '{"movement":"自动机械","material":"316L钢","waterproof":"100m"}', 'active'),
('钻石手表', 6, 'Cartier', '铂金', '钻石', 2.00, NULL, NULL, NULL, 158000.00, 85000.00, 3, 'WT-CT-001', 'Cartier钻石手表', '{"movement":"石英","diamond":"2.0ct","metal":"Pt950"}', 'active'),
('黄金手表', 6, 'Rolex', '黄金', NULL, NULL, NULL, NULL, NULL, 128000.00, 68000.00, 5, 'WT-RLX-002', 'Rolex黄金手表', '{"movement":"自动机械","metal":"18KYG"}', 'active'),
('首饰盒', 7, '通用', '皮革', NULL, NULL, NULL, NULL, NULL, 1280.00, 400.00, 50, 'HB-001', '真皮首饰盒', '{"material":"头层牛皮","size":"25x15x10cm"}', 'active'),
('首饰收纳箱', 7, '通用', '木材', NULL, NULL, NULL, NULL, NULL, 2680.00, 1200.00, 20, 'HB-002', '实木首饰收纳箱', '{"material":"橡木","size":"40x30x20cm"}', 'active'),
('钻石吊坠', 8, 'Tiffany', '铂金', '钻石', 0.50, 'D', 'VVS1', 'Excellent', 38800.00, 19000.00, 8, 'PL-TF-001', 'Tiffany钻石吊坠', '{"diamond":"0.5ct","metal":"Pt950","length":"45cm"}', 'active'),
('翡翠吊坠', 8, '瑞丽缘', '18K金', '翡翠', NULL, '阳绿', NULL, NULL, 28800.00, 15000.00, 5, 'PL-RLY-001', '冰种阳绿翡翠吊坠', '{"jade":"冰种阳绿","metal":"18KYG","length":"42cm"}', 'active'),
('红宝石吊坠', 8, 'Harry Winston', '铂金', '红宝石', 1.50, '鸽血红', 'VS1', 'Excellent', 98000.00, 52000.00, 3, 'PL-HW-001', '鸽血红红宝石吊坠', '{"ruby":"1.5ct","metal":"Pt950","length":"45cm"}', 'active'),
('黄金吊坠', 8, '周大福', '黄金', NULL, NULL, NULL, NULL, NULL, 4680.00, 2200.00, 30, 'PL-ZDF-001', '古法黄金吊坠', '{"metal":"足金999","weight":"8.5g"}', 'active'),
('发簪', 9, '通用', '银', '珍珠', NULL, NULL, NULL, NULL, 680.00, 200.00, 40, 'HS-001', '珍珠银发簪', '{"material":"S925银+珍珠"}', 'active'),
('发夹', 9, '通用', '合金', '钻石', 0.10, NULL, NULL, NULL, 380.00, 100.00, 60, 'HS-002', '水钻发夹', '{"material":"合金+水钻"}', 'active'),
('婚庆对戒', 10, 'Cartier', '铂金', '钻石', 0.30, 'G', 'VS2', 'Very Good', 35800.00, 18000.00, 10, 'WD-CT-001', 'Cartier婚庆对戒一对', '{"size":"12号+14号","metal":"Pt950","diamond":"0.3ct"}', 'active'),
('婚庆项链套装', 10, 'Tiffany', '铂金', '钻石', 1.00, 'E', 'VS1', 'Excellent', 128000.00, 68000.00, 3, 'WD-TF-001', 'Tiffany婚庆项链套装', '{"diamond":"1.0ct","metal":"Pt950","includes":"项链+耳环"}', 'active'),
('婚庆手链套装', 10, 'Van Cleef', '黄金', '钻石', 0.80, NULL, NULL, NULL, 88000.00, 45000.00, 5, 'WD-VCA-001', '梵克雅宝婚庆手链套装', '{"diamond":"0.8ct","metal":"18KYG"}', 'active'),
('月光石手链', 2, '瑞丽缘', '银', '月光石', NULL, NULL, NULL, NULL, 580.00, 150.00, 80, 'BS-RLY-002', '天然月光石手链', '{"material":"S925银+月光石"}', 'active'),
('绿松石戒指', 1, '瑞丽缘', '银', '绿松石', NULL, NULL, NULL, NULL, 1280.00, 500.00, 25, 'RF-RLY-006', '天然绿松石戒指', '{"size":"11号","material":"S925银+绿松石"}', 'active'),
('坦桑石项链', 2, 'Harry Winston', '铂金', '坦桑石', 3.00, 'Violet', 'VVS2', 'Excellent', 45000.00, 22000.00, 6, 'NK-HW-001', '坦桑石钻石项链', '{"tanzanite":"3.0ct","metal":"Pt950"}', 'active'),
('石榴石手链', 2, '通用', '银', '石榴石', NULL, NULL, NULL, NULL, 480.00, 120.00, 90, 'BS-003', '天然石榴石手链', '{"material":"S925银+石榴石"}', 'active'),
('蛋白石耳环', 4, 'Harry Winston', '黄金', '蛋白石', 2.00, NULL, NULL, NULL, 35000.00, 18000.00, 5, 'EP-HW-002', '澳洲蛋白石耳环', '{"opal":"2.0ct","metal":"18KYG"}', 'active'),
('碧玺胸针', 5, 'Harry Winston', '黄金', '碧玺', 5.00, 'Tourmaline', NULL, NULL, 18000.00, 9000.00, 6, 'BX-HW-001', '碧玺黄金胸针', '{"tourmaline":"5.0ct","metal":"18KYG"}', 'active'),
('祖母绿手表', 6, 'Rolex', '黄金', '祖母绿', 1.00, NULL, NULL, NULL, 258000.00, 135000.00, 2, 'WT-RLX-003', 'Rolex祖母绿手表', '{"emerald":"1.0ct","metal":"18KYG","movement":"自动机械"}', 'active'),
('黑玛瑙首饰盒', 7, '通用', '木材', NULL, NULL, NULL, NULL, NULL, 880.00, 300.00, 30, 'HB-003', '黑玛瑙装饰首饰盒', '{"material":"黑玛瑙+实木"}', 'active'),
('紫水晶吊坠', 8, '瑞丽缘', '18K金', '紫水晶', 2.00, NULL, NULL, NULL, 3800.00, 1500.00, 20, 'PL-RLY-002', '天然紫水晶吊坠', '{"amethyst":"2.0ct","metal":"18KYG"}', 'active'),
('黄玉发饰', 9, '通用', '银', '黄玉', NULL, NULL, NULL, NULL, 580.00, 180.00, 35, 'HS-003', '黄玉银发饰', '{"material":"S925银+黄玉"}', 'active'),
('托帕石戒指', 1, 'Harry Winston', '铂金', '托帕石', 3.50, 'Blue Topaz', 'VVS1', 'Excellent', 28000.00, 14000.00, 7, 'RF-HW-005', '蓝托帕石戒指', '{"topaz":"3.5ct","metal":"Pt950"}', 'active'),
('橄榄石项链', 2, '瑞丽缘', '18K金', '橄榄石', 2.50, NULL, NULL, NULL, 4200.00, 2000.00, 15, 'NK-RLY-001', '橄榄石钻石项链', '{"peridot":"2.5ct","metal":"18KYG"}', 'active'),
('锆石手链', 2, '通用', '银', '锆石', 1.00, NULL, NULL, NULL, 380.00, 80.00, 120, 'BS-004', '立方氧化锆石手链', '{"material":"S925银+锆石"}', 'active'),
('海蓝宝石耳环', 4, 'Harry Winston', '铂金', '海蓝宝石', 2.80, 'Santa Maria', 'VVS2', 'Excellent', 52000.00, 28000.00, 4, 'EP-HW-003', '圣玛利亚海蓝宝石耳环', '{"aquamarine":"2.8ct","metal":"Pt950"}', 'active'),
('粉晶胸针', 5, '通用', '银', '粉晶', NULL, NULL, NULL, NULL, 1680.00, 600.00, 15, 'BX-001', '粉晶银胸针', '{"material":"S925银+粉晶"}', 'active'),
('琥珀手表', 6, '通用', '黄金', '琥珀', NULL, NULL, NULL, NULL, 38000.00, 18000.00, 5, 'WT-001', '琥珀黄金手表', '{"amber":"天然琥珀","metal":"18KYG"}', 'active'),
('蜜蜡首饰盒', 7, '通用', '木材', NULL, NULL, NULL, NULL, NULL, 1680.00, 600.00, 15, 'HB-004', '蜜蜡装饰首饰盒', '{"material":"蜜蜡+实木"}', 'active'),
('南红吊坠', 8, '瑞丽缘', '18K金', '南红', NULL, '锦红', NULL, NULL, 18800.00, 9500.00, 8, 'PL-RLY-003', '锦红南红吊坠', '{"material":"18KYG+南红"}', 'active'),
('象牙果发饰', 9, '通用', NULL, NULL, NULL, NULL, NULL, NULL, 280.00, 80.00, 50, 'HS-004', '象牙果雕刻发饰', '{"material":"象牙果"}', 'active');

-- 3. 客户数据（30个客户）
INSERT INTO customers (first_name, last_name, email, phone, gender, birthdate, membership_level, total_spent, loyalty_points, address) VALUES
('伟', '张', 'zhangwei@email.com', '13800138001', '男', '1985-03-15', 'gold', 158000.00, 15800, '{"city":"北京","district":"朝阳区","street":"建国路88号"}'),
('芳', '李', 'lifang@email.com', '13800138002', '女', '1990-07-22', 'platinum', 285000.00, 28500, '{"city":"上海","district":"浦东新区","street":"陆家嘴环路100号"}'),
('强', '王', 'wangqiang@email.com', '13800138003', '男', '1988-11-08', 'regular', 12500.00, 1250, '{"city":"广州","district":"天河区","street":"天河路200号"}'),
('敏', '赵', 'zhaomin@email.com', '13800138004', '女', '1992-05-30', 'gold', 98000.00, 9800, '{"city":"深圳","district":"南山区","street":"科技南路16号"}'),
('磊', '刘', 'liulei@email.com', '13800138005', '男', '1987-09-12', 'regular', 8500.00, 850, '{"city":"成都","district":"锦江区","street":"春熙路88号"}'),
('静', '陈', 'chenjing@email.com', '13800138006', '女', '1991-01-25', 'platinum', 325000.00, 32500, '{"city":"杭州","district":"西湖区","street":"文三路300号"}'),
('涛', '杨', 'yangtao@email.com', '13800138007', '男', '1986-06-18', 'gold', 125000.00, 12500, '{"city":"武汉","district":"武昌区","street":"中南路99号"}'),
('慧', '黄', 'huanghui@email.com', '13800138008', '女', '1993-12-03', 'regular', 5600.00, 560, '{"city":"南京","district":"鼓楼区","street":"新模范马路50号"}'),
('明', '周', 'zhouming@email.com', '13800138009', '男', '1989-04-20', 'gold', 88000.00, 8800, '{"city":"重庆","district":"渝中区","street":"解放碑步行街"}'),
('丽', '吴', 'wuli@email.com', '13800138010', '女', '1994-08-14', 'regular', 3200.00, 320, '{"city":"西安","district":"雁塔区","street":"小寨东路100号"}'),
('军', '徐', 'xujun@email.com', '13800138011', '男', '1984-02-28', 'platinum', 456000.00, 45600, '{"city":"北京","district":"海淀区","street":"中关村大街88号"}'),
('婷', '孙', 'sunting@email.com', '13800138012', '女', '1995-10-09', 'regular', 2800.00, 280, '{"city":"上海","district":"静安区","street":"南京西路1000号"}'),
('鹏', '马', 'mappeng@email.com', '13800138013', '男', '1987-07-16', 'gold', 76000.00, 7600, '{"city":"深圳","district":"福田区","street":"彩田路2000号"}'),
('雪', '朱', 'zhuxue@email.com', '13800138014', '女', '1991-03-27', 'platinum', 198000.00, 19800, '{"city":"杭州","district":"上城区","street":"庆春路180号"}'),
('飞', '胡', 'hufei@email.com', '13800138015', '男', '1990-11-11', 'regular', 6800.00, 680, '{"city":"成都","district":"青羊区","street":"人民西路66号"}'),
('琳', '林', 'linlin@email.com', '13800138016', '女', '1992-06-05', 'gold', 112000.00, 11200, '{"city":"广州","district":"越秀区","street":"北京路128号"}'),
('龙', '何', 'helong@email.com', '13800138017', '男', '1986-09-23', 'regular', 4500.00, 450, '{"city":"武汉","district":"洪山区","street":"珞喻路80号"}'),
('梅', '高', 'gaomei@email.com', '13800138018', '女', '1993-01-17', 'gold', 89000.00, 8900, '{"city":"南京","district":"秦淮区","street":"夫子庙大街88号"}'),
('超', '梁', 'liangchao@email.com', '13800138019', '男', '1988-05-08', 'regular', 7200.00, 720, '{"city":"重庆","district":"沙坪坝区","street":"沙坪街100号"}'),
('颖', '宋', 'songying@email.com', '13800138020', '女', '1994-12-21', 'platinum', 268000.00, 26800, '{"city":"北京","district":"朝阳区","street":"三里屯路38号"}'),
('健', '谢', 'xiejan@email.com', '13800138021', '男', '1985-08-14', 'gold', 95000.00, 9500, '{"city":"上海","district":"徐汇区","street":"漕溪北路88号"}'),
('霞', '韩', 'hanxia@email.com', '13800138022', '女', '1991-04-02', 'regular', 3800.00, 380, '{"city":"深圳","district":"罗湖区","street":"深南东路5000号"}'),
('辉', '冯', 'fenghui@email.com', '13800138023', '男', '1989-10-26', 'regular', 9800.00, 980, '{"city":"西安","district":"碑林区","street":"南大街88号"}'),
('佳', '董', 'dongjia@email.com', '13800138024', '女', '1992-07-19', 'gold', 135000.00, 13500, '{"city":"杭州","district":"滨江区","street":"江南大道600号"}'),
('洋', '袁', 'yuani@email.com', '13800138025', '男', '1987-02-14', 'platinum', 385000.00, 38500, '{"city":"成都","district":"高新区","street":"天府大道1000号"}'),
('倩', '邓', 'dengqian@email.com', '13800138026', '女', '1995-05-08', 'regular', 2200.00, 220, '{"city":"广州","district":"海珠区","street":"万寿路100号"}'),
('昊', '曹', 'caohao@email.com', '13800138027', '男', '1986-11-30', 'gold', 78000.00, 7800, '{"city":"武汉","district":"江岸区","street":"解放大道1000号"}'),
('茹', '田', 'tianru@email.com', '13800138028', '女', '1993-09-12', 'regular', 4800.00, 480, '{"city":"南京","district":"建邺区","street":"江东中路200号"}'),
('睿', '蒋', 'jiangrui@email.com', '13800138029', '男', '1988-03-25', 'gold', 105000.00, 10500, '{"city":"重庆","district":"九龙坡区","street":"杨家坪步行街"}'),
('璇', '沈', 'shenxuan@email.com', '13800138030', '女', '1990-06-18', 'platinum', 215000.00, 21500, '{"city":"北京","district":"西城区","street":"西单北大街88号"}');

-- 4. 门店数据
INSERT INTO stores (store_name, city, province, address, location, phone, manager_name, open_date, square_meters) VALUES
('北京旗舰店', '北京', '北京', '王府井大街88号', '(116.41,39.92)', '010-85288001', '张经理', '2018-01-15', 500.00),
('上海南京路店', '上海', '上海', '南京东路600号', '(121.48,31.24)', '021-63288002', '李经理', '2017-06-20', 420.00),
('深圳万象城店', '深圳', '广东', '万象城L1层', '(114.07,22.53)', '0755-88888003', '王经理', '2019-03-10', 350.00),
('广州天河城店', '广州', '广东', '天河城B1层', '(113.33,23.13)', '020-38888004', '赵经理', '2018-09-05', 380.00),
('杭州西湖店', '杭州', '浙江', '西湖文化广场', '(120.16,30.28)', '0571-88888005', '陈经理', '2019-07-15', 280.00),
('成都春熙路店', '成都', '四川', '春熙路步行街', '(104.08,30.66)', '028-86688006', '刘经理', '2018-11-20', 320.00),
('武汉江汉路店', '武汉', '湖北', '江汉路步行街', '(114.27,30.58)', '027-85888007', '杨经理', '2019-05-01', 260.00),
('南京新街口店', '南京', '江苏', '新街口商圈', '(118.78,32.05)', '025-84888008', '黄经理', '2018-04-12', 300.00),
('重庆解放碑店', '重庆', '重庆', '解放碑步行街', '(106.58,29.56)', '023-63888009', '周经理', '2019-08-20', 280.00),
('西安钟楼店', '西安', '陕西', '钟楼商圈', '(108.95,34.27)', '029-87888010', '吴经理', '2019-10-01', 250.00);

-- 5. 订单数据（30个订单，分布在多个分区）
INSERT INTO orders (order_id, customer_id, store_id, order_number, order_date, status, total_amount, discount_amount, tax_amount, final_amount, payment_method, shipping_method, tracking_number, notes) VALUES
(1, 1, 1, 'ORD-2024Q1-001', '2024-01-15 10:30:00', 'delivered', 58800.00, 2000.00, 3300.00, 60100.00, '信用卡', '顺丰快递', 'SF1234567890', '客户指定配送时间'),
(2, 2, 2, 'ORD-2024Q1-002', '2024-02-20 14:20:00', 'delivered', 32800.00, 1000.00, 1840.00, 33640.00, '支付宝', '京东物流', 'JD0987654321', ''),
(3, 3, 3, 'ORD-2024Q1-003', '2024-03-10 09:15:00', 'delivered', 5680.00, 0, 318.00, 5998.00, '微信支付', '中通快递', 'ZTO1122334455', ''),
(4, 4, 4, 'ORD-2024Q2-001', '2024-04-18 16:45:00', 'delivered', 89000.00, 5000.00, 5000.00, 89000.00, '信用卡', '顺丰快递', 'SF2233445566', '贵重物品保价'),
(5, 5, 5, 'ORD-2024Q2-002', '2024-05-05 11:00:00', 'shipped', 18800.00, 500.00, 1060.00, 19360.00, '支付宝', '韵达快递', 'YD3344556677', ''),
(6, 6, 6, 'ORD-2024Q2-003', '2024-05-20 13:30:00', 'delivered', 158000.00, 10000.00, 8800.00, 156800.00, '信用卡', '顺丰快递', 'SF4455667788', 'VIP客户专属服务'),
(7, 7, 7, 'ORD-2024Q3-001', '2024-07-08 10:00:00', 'delivered', 25800.00, 800.00, 1450.00, 26450.00, '微信支付', '中通快递', 'ZTO5566778899', ''),
(8, 8, 8, 'ORD-2024Q3-002', '2024-08-15 15:20:00', 'delivered', 6800.00, 0, 380.00, 7180.00, '支付宝', '圆通快递', 'YTO6677889900', ''),
(9, 9, 9, 'ORD-2024Q3-003', '2024-09-01 09:45:00', 'shipped', 128000.00, 8000.00, 7200.00, 127200.00, '信用卡', '顺丰快递', 'SF7788990011', '定制刻字服务'),
(10, 10, 10, 'ORD-2024Q3-004', '2024-09-20 14:00:00', 'pending', 3680.00, 0, 206.00, 3886.00, '微信支付', '中通快递', NULL, ''),
(11, 11, 1, 'ORD-2024Q4-001', '2024-10-05 11:30:00', 'delivered', 258000.00, 15000.00, 14000.00, 257000.00, '信用卡', '顺丰快递', 'SF8899001122', 'VIP客户+保价'),
(12, 12, 2, 'ORD-2024Q4-002', '2024-11-10 16:00:00', 'shipped', 18800.00, 600.00, 1060.00, 19260.00, '支付宝', '京东物流', 'JD9900112233', ''),
(13, 13, 3, 'ORD-2024Q4-003', '2024-11-25 10:15:00', 'pending', 88000.00, 5000.00, 5000.00, 88000.00, '信用卡', '顺丰快递', NULL, '双十一活动'),
(14, 14, 4, 'ORD-2024Q4-004', '2024-12-01 13:45:00', 'confirmed', 45000.00, 2000.00, 2500.00, 45500.00, '微信支付', '中通快递', 'ZTO0011223344', ''),
(15, 15, 5, 'ORD-2025Q1-001', '2025-01-08 09:00:00', 'delivered', 68000.00, 3000.00, 3800.00, 68800.00, '支付宝', '顺丰快递', 'SF1122334455', '新年礼物包装'),
(16, 16, 6, 'ORD-2025Q1-002', '2025-01-20 14:30:00', 'shipped', 12800.00, 0, 720.00, 13520.00, '微信支付', '韵达快递', 'YD2233445566', ''),
(17, 17, 7, 'ORD-2025Q1-003', '2025-02-14 10:00:00', 'confirmed', 158000.00, 10000.00, 8800.00, 156800.00, '信用卡', '顺丰快递', 'SF3344556677', '情人节限定'),
(18, 18, 8, 'ORD-2025Q1-004', '2025-02-28 16:20:00', 'pending', 8800.00, 0, 490.00, 9290.00, '支付宝', '圆通快递', NULL, ''),
(19, 19, 9, 'ORD-2025Q2-001', '2025-03-08 11:00:00', 'delivered', 38800.00, 2000.00, 2180.00, 40980.00, '微信支付', '中通快递', 'ZTO4455667788', '妇女节活动'),
(20, 20, 10, 'ORD-2025Q2-002', '2025-03-15 15:00:00', 'shipped', 288000.00, 20000.00, 16000.00, 284000.00, '信用卡', '顺丰快递', 'SF5566778899', 'VIP客户+定制'),
(21, 21, 1, 'ORD-2025Q2-003', '2025-03-20 09:30:00', 'confirmed', 15800.00, 500.00, 890.00, 16190.00, '支付宝', '京东物流', 'JD6677889900', ''),
(22, 22, 2, 'ORD-2025Q2-004', '2025-04-01 13:00:00', 'pending', 35800.00, 1500.00, 2000.00, 36300.00, '微信支付', '顺丰快递', NULL, ''),
(23, 23, 3, 'ORD-2025Q2-005', '2025-04-10 10:45:00', 'delivered', 45000.00, 2500.00, 2500.00, 45000.00, '信用卡', '中通快递', 'ZTO7788990011', ''),
(24, 24, 4, 'ORD-2025Q2-006', '2025-04-15 14:15:00', 'shipped', 28500.00, 1000.00, 1600.00, 29100.00, '支付宝', '韵达快递', 'YD8899001122', ''),
(25, 25, 5, 'ORD-2025Q2-007', '2025-04-20 16:30:00', 'confirmed', 98000.00, 5000.00, 5500.00, 98500.00, '微信支付', '顺丰快递', 'SF9900112233', '大客户'),
(26, 26, 6, 'ORD-2025Q2-008', '2025-04-25 11:00:00', 'pending', 6800.00, 0, 380.00, 7180.00, '支付宝', '圆通快递', NULL, ''),
(27, 27, 7, 'ORD-2025Q2-009', '2025-04-28 09:00:00', 'delivered', 52000.00, 3000.00, 2900.00, 51900.00, '信用卡', '中通快递', 'ZTO0011223355', ''),
(28, 28, 8, 'ORD-2025Q2-010', '2025-04-30 15:45:00', 'shipped', 18800.00, 800.00, 1060.00, 19060.00, '微信支付', '顺丰快递', 'SF1122335566', ''),
(29, 29, 9, 'ORD-2025Q2-011', '2025-05-01 10:00:00', 'confirmed', 88000.00, 5000.00, 5000.00, 88000.00, '信用卡', '京东物流', 'JD2233556677', '五一活动'),
(30, 30, 10, 'ORD-2025Q2-012', '2025-05-05 14:00:00', 'pending', 215000.00, 15000.00, 12000.00, 212000.00, '支付宝', '顺丰快递', NULL, 'VIP客户');

-- 6. 订单明细数据
INSERT INTO order_details (order_id, product_id, quantity, unit_price, discount_pct, subtotal) VALUES
(1, 1, 1, 58800.00, 3.4, 58800.00),
(2, 1, 1, 58800.00, 3.4, 58800.00),
(3, 11, 1, 5680.00, 0, 5680.00),
(4, 4, 1, 89000.00, 5.6, 89000.00),
(5, 22, 1, 18800.00, 2.7, 18800.00),
(6, 31, 1, 128000.00, 6.3, 128000.00),
(7, 6, 1, 32800.00, 2.4, 32800.00),
(8, 15, 1, 6800.00, 0, 6800.00),
(9, 32, 1, 128000.00, 5.9, 128000.00),
(10, 3, 1, 3680.00, 0, 3680.00),
(11, 21, 1, 88000.00, 5.7, 88000.00),
(12, 22, 1, 18800.00, 3.2, 18800.00),
(13, 33, 1, 88000.00, 5.7, 88000.00),
(14, 28, 1, 45000.00, 4.2, 45000.00),
(15, 26, 1, 38800.00, 4.6, 38800.00),
(16, 12, 1, 12800.00, 0, 12800.00),
(17, 34, 1, 158000.00, 5.7, 158000.00),
(18, 19, 1, 8800.00, 0, 8800.00),
(19, 27, 1, 38800.00, 4.9, 38800.00),
(20, 20, 1, 288000.00, 5.6, 288000.00),
(21, 24, 1, 15800.00, 3.2, 15800.00),
(22, 35, 1, 35800.00, 4.0, 35800.00),
(23, 29, 1, 45000.00, 5.6, 45000.00),
(24, 16, 1, 28500.00, 3.5, 28500.00),
(25, 30, 1, 98000.00, 5.1, 98000.00),
(26, 18, 1, 6800.00, 0, 6800.00),
(27, 36, 1, 52000.00, 5.8, 52000.00),
(28, 23, 1, 18800.00, 4.1, 18800.00),
(29, 37, 1, 88000.00, 5.7, 88000.00),
(30, 38, 1, 215000.00, 6.5, 215000.00);

-- 7. 库存数据
INSERT INTO inventory (product_id, store_id, quantity, reorder_level, max_stock, status, warehouse_zone, shelf_location) VALUES
(1, 1, 8, 5, 20, 'in_stock', 'A', 'A-01-01'),
(1, 2, 5, 5, 20, 'in_stock', 'A', 'A-01-02'),
(2, 1, 12, 5, 25, 'in_stock', 'A', 'A-02-01'),
(3, 3, 25, 10, 60, 'in_stock', 'B', 'B-01-01'),
(4, 1, 3, 5, 10, 'low_stock', 'A', 'A-03-01'),
(5, 4, 5, 5, 15, 'in_stock', 'A', 'A-04-01'),
(6, 2, 6, 5, 15, 'in_stock', 'C', 'C-01-01'),
(7, 1, 8, 5, 20, 'in_stock', 'C', 'C-02-01'),
(8, 3, 5, 5, 15, 'in_stock', 'C', 'C-03-01'),
(9, 5, 15, 8, 30, 'in_stock', 'B', 'B-02-01'),
(10, 1, 10, 5, 25, 'in_stock', 'B', 'B-03-01'),
(11, 2, 8, 5, 20, 'in_stock', 'B', 'B-04-01'),
(12, 1, 4, 5, 10, 'low_stock', 'B', 'B-05-01'),
(13, 3, 18, 8, 40, 'in_stock', 'B', 'B-06-01'),
(14, 1, 10, 5, 20, 'in_stock', 'D', 'D-01-01'),
(15, 2, 12, 5, 25, 'in_stock', 'D', 'D-02-01'),
(16, 4, 8, 5, 20, 'in_stock', 'D', 'D-03-01'),
(17, 1, 2, 5, 10, 'low_stock', 'D', 'D-04-01'),
(18, 5, 10, 5, 20, 'in_stock', 'D', 'D-05-01'),
(19, 1, 5, 5, 15, 'in_stock', 'E', 'E-01-01'),
(20, 2, 3, 5, 10, 'low_stock', 'E', 'E-02-01'),
(21, 1, 6, 5, 15, 'in_stock', 'E', 'E-03-01'),
(22, 3, 10, 5, 20, 'in_stock', 'E', 'E-04-01'),
(23, 1, 8, 5, 20, 'in_stock', 'F', 'F-01-01'),
(24, 2, 5, 5, 15, 'in_stock', 'F', 'F-02-01'),
(25, 4, 12, 5, 25, 'in_stock', 'F', 'F-03-01'),
(26, 1, 4, 5, 15, 'in_stock', 'F', 'F-04-01'),
(27, 1, 3, 5, 10, 'low_stock', 'F', 'F-05-01'),
(28, 3, 8, 5, 20, 'in_stock', 'G', 'G-01-01'),
(29, 2, 15, 8, 30, 'in_stock', 'G', 'G-02-01'),
(30, 1, 6, 5, 15, 'in_stock', 'G', 'G-03-01'),
(31, 1, 2, 3, 8, 'low_stock', 'G', 'G-04-01'),
(32, 2, 4, 5, 10, 'in_stock', 'G', 'G-05-01'),
(33, 1, 5, 5, 15, 'in_stock', 'H', 'H-01-01'),
(34, 1, 3, 5, 10, 'low_stock', 'H', 'H-02-01'),
(35, 2, 8, 5, 20, 'in_stock', 'H', 'H-03-01'),
(36, 1, 6, 5, 15, 'in_stock', 'H', 'H-04-01'),
(37, 3, 5, 5, 15, 'in_stock', 'H', 'H-05-01'),
(38, 1, 2, 5, 8, 'low_stock', 'H', 'H-06-01'),
(39, 2, 10, 5, 25, 'in_stock', 'I', 'I-01-01'),
(40, 1, 8, 5, 20, 'in_stock', 'I', 'I-02-01'),
(41, 3, 12, 5, 25, 'in_stock', 'I', 'I-03-01'),
(42, 1, 5, 5, 15, 'in_stock', 'I', 'I-04-01'),
(43, 2, 8, 5, 20, 'in_stock', 'I', 'I-05-01'),
(44, 1, 10, 5, 25, 'in_stock', 'J', 'J-01-01'),
(45, 3, 6, 5, 15, 'in_stock', 'J', 'J-02-01'),
(46, 1, 4, 5, 10, 'in_stock', 'J', 'J-03-01'),
(47, 2, 8, 5, 20, 'in_stock', 'J', 'J-04-01'),
(48, 1, 15, 8, 30, 'in_stock', 'J', 'J-05-01'),
(49, 3, 5, 5, 15, 'in_stock', 'K', 'K-01-01'),
(50, 1, 6, 5, 15, 'in_stock', 'K', 'K-02-01'),
(51, 2, 10, 5, 25, 'in_stock', 'K', 'K-03-01'),
(52, 1, 8, 5, 20, 'in_stock', 'K', 'K-04-01'),
(53, 3, 5, 5, 15, 'in_stock', 'K', 'K-05-01'),
(54, 1, 12, 5, 25, 'in_stock', 'K', 'K-06-01'),
(55, 2, 8, 5, 20, 'in_stock', 'K', 'K-07-01');

-- 8. 评价数据
INSERT INTO reviews (product_id, customer_id, order_id, rating, title, comment, is_verified, helpful_count) VALUES
(1, 1, 1, 5, '非常满意', '钻石非常闪亮，做工精细，包装也很高档', TRUE, 25),
(2, 2, 2, 4, '不错', '款式好看，但是物流有点慢', TRUE, 12),
(4, 4, 4, 5, '奢华之选', '蓝宝石颜色纯正，证书齐全，值得收藏', TRUE, 30),
(6, 6, 6, 5, '经典永恒', '四叶草设计太美了，日常佩戴很百搭', TRUE, 18),
(7, 7, 7, 4, '性价比高', '珍珠光泽好，价格实惠', TRUE, 8),
(17, 17, 17, 5, '完美礼物', '情人节送的，女朋友非常喜欢', TRUE, 22),
(20, 20, 20, 5, '投资首选', '大品牌保值，品质有保障', TRUE, 35),
(31, 11, 11, 5, '传世之作', '婚庆套装太震撼了，仪式感满满', TRUE, 40),
(3, 3, 3, 3, '一般', '黄金纯度还可以，但款式有点老气', FALSE, 5),
(5, 5, 5, 4, '适合日常', '价格适中，佩戴舒适', TRUE, 7);

-- 9. 搜索日志数据
INSERT INTO search_logs (customer_id, search_query, results_count, clicked_product_id, session_id) VALUES
(1, '钻石戒指', 15, 1, 'sess_001'),
(2, '珍珠项链', 8, 7, 'sess_002'),
(3, '黄金手链', 12, 10, 'sess_003'),
(4, '蓝宝石戒指', 5, 4, 'sess_004'),
(5, '翡翠吊坠', 6, 27, 'sess_005'),
(6, '钻石项链', 10, 8, 'sess_006'),
(7, 'Cartier手表', 3, 21, 'sess_007'),
(8, '珍珠耳环', 7, 16, 'sess_008'),
(9, '婚庆戒指', 8, 31, 'sess_009'),
(10, '黄金吊坠', 9, 28, 'sess_010'),
(11, 'Rolex手表', 4, 21, 'sess_011'),
(12, '红宝石吊坠', 3, 29, 'sess_012'),
(13, '钻石手链', 5, 12, 'sess_013'),
(14, '翡翠戒指', 4, 5, 'sess_014'),
(15, 'Tiffany项链', 6, 8, 'sess_015'),
(16, '玛瑙手链', 8, 13, 'sess_016'),
(17, '蓝宝石耳环', 4, 19, 'sess_017'),
(18, '黄金耳环', 6, 17, 'sess_018'),
(19, '胸针', 5, 18, 'sess_019'),
(20, '钻石手表', 3, 22, 'sess_020');

-- 10. 库存流水数据
INSERT INTO inventory_logs (product_id, store_id, transaction_type, quantity_change, quantity_before, quantity_after, reference_id, notes) VALUES
(1, 1, 'purchase', 10, 0, 10, 'PO-2024-001', '首批进货'),
(1, 1, 'sale', -1, 10, 9, 'ORD-2024Q1-001', '销售出库'),
(2, 1, 'purchase', 15, 0, 15, 'PO-2024-002', '首批进货'),
(2, 1, 'sale', -1, 15, 14, 'ORD-2024Q1-002', '销售出库'),
(3, 3, 'purchase', 30, 0, 30, 'PO-2024-003', '首批进货'),
(3, 3, 'sale', -1, 30, 29, 'ORD-2024Q1-003', '销售出库'),
(4, 1, 'purchase', 5, 0, 5, 'PO-2024-004', '首批进货'),
(4, 1, 'sale', -1, 5, 4, 'ORD-2024Q2-001', '销售出库'),
(5, 4, 'purchase', 8, 0, 8, 'PO-2024-005', '首批进货'),
(5, 4, 'sale', -1, 8, 7, 'ORD-2024Q2-002', '销售出库'),
(6, 2, 'purchase', 10, 0, 10, 'PO-2024-006', '首批进货'),
(6, 2, 'sale', -1, 10, 9, 'ORD-2024Q2-003', '销售出库'),
(7, 1, 'purchase', 10, 0, 10, 'PO-2024-007', '首批进货'),
(7, 1, 'sale', -1, 10, 9, 'ORD-2024Q3-001', '销售出库'),
(8, 3, 'purchase', 8, 0, 8, 'PO-2024-008', '首批进货'),
(8, 3, 'sale', -1, 8, 7, 'ORD-2024Q3-002', '销售出库'),
(9, 9, 'purchase', 5, 0, 5, 'PO-2024-009', '首批进货'),
(9, 9, 'sale', -1, 5, 4, 'ORD-2024Q3-003', '销售出库'),
(10, 5, 'purchase', 20, 0, 20, 'PO-2024-010', '首批进货'),
(10, 5, 'sale', -1, 20, 19, 'ORD-2024Q3-004', '销售出库'),
(11, 2, 'purchase', 10, 0, 10, 'PO-2024-011', '首批进货'),
(11, 2, 'sale', -1, 10, 9, 'ORD-2024Q4-001', '销售出库'),
(12, 1, 'purchase', 8, 0, 8, 'PO-2024-012', '首批进货'),
(12, 1, 'sale', -1, 8, 7, 'ORD-2024Q4-002', '销售出库'),
(13, 3, 'purchase', 15, 0, 15, 'PO-2024-013', '首批进货'),
(13, 3, 'sale', -1, 15, 14, 'ORD-2024Q4-003', '销售出库'),
(14, 4, 'purchase', 10, 0, 10, 'PO-2024-014', '首批进货'),
(14, 4, 'sale', -1, 10, 9, 'ORD-2024Q4-004', '销售出库'),
(15, 1, 'purchase', 10, 0, 10, 'PO-2025-001', '首批进货'),
(15, 1, 'sale', -1, 10, 9, 'ORD-2025Q1-001', '销售出库');

-- 验证数据
SELECT 'categories' AS table_name, COUNT(*) AS row_count FROM categories
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'customers', COUNT(*) FROM customers
UNION ALL SELECT 'stores', COUNT(*) FROM stores
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_details', COUNT(*) FROM order_details
UNION ALL SELECT 'inventory', COUNT(*) FROM inventory
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews
UNION ALL SELECT 'search_logs', COUNT(*) FROM search_logs
UNION ALL SELECT 'inventory_logs', COUNT(*) FROM inventory_logs;



-- ============================================================
-- 模块来源: pg18_indexing_basics.sql
-- ============================================================

-- ============================================================
-- PostgreSQL 18 珠宝行业「索引与性能优化」
-- 模块3：索引基础 - B-Tree、Hash、Bitmap、全文索引
-- ============================================================

-- ============================================================
-- 一、B-Tree 索引（默认索引类型，支持 =、<、>、<=、>=、BETWEEN、LIKE 'prefix%'）
-- ============================================================

-- 1.1 单列B-Tree索引（已在schema中创建）
-- 示例：查询某品牌的产品
-- EXPLAIN ANALYZE SELECT * FROM products WHERE brand = 'Tiffany';

-- 1.2 多列复合B-Tree索引
-- 场景：按分类和价格范围查询（常见于珠宝筛选）
-- CREATE INDEX idx_products_cat_price ON products(category_id, price);
-- 查询优化：WHERE category_id = 1 AND price BETWEEN 1000 AND 10000
-- 注意：最左前缀原则 - 可以优化 category_id 单独查询，但不能仅优化 price 查询

-- 1.3 降序索引
-- 场景：按价格从高到低排序（高端珠宝展示）
CREATE INDEX idx_products_price_desc ON products(price DESC);
-- 查询优化：SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- 1.4 NULL值处理
-- B-Tree索引默认不包含NULL值
CREATE INDEX idx_products_material_notnull ON products(material) WHERE material IS NOT NULL;

-- ============================================================
-- 二、Hash 索引（仅支持等值查询 =，不支持范围查询和排序）
-- ============================================================

-- 2.1 创建Hash索引
-- 场景：SKU精确查询（珠宝SKU唯一标识）
CREATE INDEX idx_products_sku_hash ON products USING hash (sku);
-- 查询优化：SELECT * FROM products WHERE sku = 'RF-TF-001';
-- 注意：Hash索引不支持ORDER BY、不支持< > <= >=等操作符

-- 2.2 Hash索引适用场景分析
-- 适合：高基数列的等值查询（如email、SKU、订单号）
-- 不适合：范围查询、排序、部分索引
-- PostgreSQL 18中Hash索引性能已大幅改善（并行Hash索引）

-- 2.3 Hash索引 vs B-Tree对比
-- Hash: 等值查询更快，索引更小，但不支持部分索引/覆盖索引
-- B-Tree: 功能全面，支持所有操作符类，是默认选择

-- ============================================================
-- 三、Bitmap 索引替代方案
-- ============================================================

-- PostgreSQL原生不支持Bitmap索引，但有以下替代方案：

-- 3.1 方案一：GIN索引模拟Bitmap（低基数列）
-- 场景：status列（只有active/inactive/discontinued几个值）
CREATE INDEX idx_products_status_gin ON products USING gin (status gin_trgm_ops);
-- 或者使用btree_gin扩展
CREATE INDEX idx_products_status_btree_gin ON products USING gin (status);

-- 3.2 方案二：位图聚合查询优化
-- 场景：查询某个分类下多个价格区间的产品
-- EXPLAIN ANALYZE
-- SELECT * FROM products WHERE category_id = 1 AND price IN (1000, 2000, 3000, 5000);
-- PG会自动使用Bitmap Or/And优化

-- 3.3 方案三：物化视图预聚合
-- 场景：频繁执行的统计查询
CREATE MATERIALIZED VIEW mv_category_stats AS
SELECT
    category_id,
    COUNT(*) AS product_count,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    AVG(price) AS avg_price
FROM products
GROUP BY category_id;
CREATE INDEX idx_mv_cat_stats ON mv_category_stats(category_id);

-- ============================================================
-- 四、全文索引（Full-Text Search）
-- ============================================================

-- 4.1 使用 pg_trgm 扩展实现模糊全文搜索
-- 场景：珠宝名称模糊搜索（用户输入不完整的搜索词）
-- 已在schema中创建：CREATE INDEX idx_products_name_trgm ON products USING gin (product_name gin_trgm_ops);

-- 4.2 trigram 搜索查询示例
-- 用户搜索"蒂芙尼钻"，匹配"蒂芙尼经典六爪钻戒"
-- SELECT * FROM products WHERE product_name % '蒂芙尼钻';
-- 注意：% 是pg_trgm的相似度操作符

-- 4.3 相似度排序搜索
-- SELECT product_id, product_name,
--        similarity(product_name, '蒂芙尼钻戒') AS score
-- FROM products
-- WHERE product_name % '蒂芙尼钻戒'
-- ORDER BY score DESC;

-- 4.4 使用 tsvector/tsquery 实现结构化全文搜索
-- 创建搜索向量列
ALTER TABLE products ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        to_tsvector('chinese', product_name || ' ' || COALESCE(material, '') || ' ' || COALESCE(gemstone, '') || ' ' || COALESCE(brand, ''))
    ) STORED;

-- 创建GIN索引（支持多个全词匹配）
CREATE INDEX idx_products_tsvector_gin ON products USING gin (search_vector);

-- 全文搜索查询
-- SELECT product_id, product_name, brand, material
-- FROM products
-- WHERE search_vector @@ to_tsquery('chinese', '钻石 & 铂金')
-- ORDER BY price;

-- 4.5 搜索日志分析
-- 分析热门搜索词
-- SELECT search_query, COUNT(*) AS search_count
-- FROM search_logs
-- WHERE created_at >= NOW() - INTERVAL '30 days'
-- GROUP BY search_query
-- ORDER BY search_count DESC
-- LIMIT 20;

-- 4.6 搜索推荐功能
-- 基于搜索日志推荐相关产品
-- SELECT DISTINCT p.*
-- FROM search_logs s
-- JOIN order_details od ON s.clicked_product_id = od.product_id
-- JOIN products p ON od.product_id = p.product_id
-- WHERE s.search_query ILIKE '%钻石%'
-- AND p.product_id NOT IN (SELECT clicked_product_id FROM search_logs WHERE search_query ILIKE '%钻石%');

-- ============================================================
-- 五、索引统计信息与监控
-- ============================================================

-- 5.1 查看索引使用情况
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 5.2 查看索引大小
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- 5.3 未使用的索引（可考虑删除以节省空间）
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 5.4 重复索引检测
SELECT
    a.indexrelname AS index1,
    b.indexrelname AS index2,
    pg_get_indexdef(a.indexrelid) AS definition1,
    pg_get_indexdef(b.indexrelid) AS definition2
FROM pg_index i
JOIN pg_class a ON a.oid = i.indexrelid
JOIN pg_class b ON b.oid = i.indexrelid
WHERE a.schemaname = 'public' AND b.schemaname = 'public'
AND a.indexrelname < b.indexrelname
AND pg_get_indexdef(a.indexrelid) = pg_get_indexdef(b.indexrelid);

-- 5.5 表统计信息更新（ANALYZE）
-- PostgreSQL会自动运行autovacuum analyzer，但大数据量导入后建议手动执行
ANALYZE products;
ANALYZE orders;
ANALYZE order_details;

-- 5.6 查看表统计信息
SELECT
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC;

-- ============================================================
-- 六、索引维护最佳实践
-- ============================================================

-- 6.1 定期REINDEX维护
-- 当索引膨胀率超过30%时考虑重建
-- SELECT
--     schemaname, relname AS table_name, indexrelname AS index_name,
--     pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
--     idx_scan
-- FROM pg_stat_user_indexes
-- WHERE schemaname = 'public';

-- 6.2 监控索引碎片
-- SELECT
--     schemaname,
--     relname AS table_name,
--     indexrelname AS index_name,
--     pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
--     idx_scan,
--     idx_tup_read,
--     idx_tup_fetch
-- FROM pg_stat_user_indexes
-- WHERE schemaname = 'public'
-- ORDER BY pg_relation_size(indexrelid) DESC;

-- 6.3 禁用/启用索引（DDL操作前临时禁用）
-- CREATE INDEX CONCURRENTLY idx_products_new ON products(new_column);
-- 注意：CONCURRENTLY选项不会阻塞表读写，但速度较慢

-- 6.4 并行索引创建（PostgreSQL 12+）
-- SET max_parallel_maintenance_workers = 4;
-- CREATE INDEX CONCURRENTLY idx_large_table ON large_table(column) WITH (parallel = 4);

-- 验证索引创建
SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;



-- ============================================================
-- 模块来源: pg18_indexing_advanced.sql
-- ============================================================

-- ============================================================
-- PostgreSQL 18 珠宝行业「索引与性能优化」
-- 模块4：高级索引 - 覆盖索引、执行计划分析、索引维护
-- ============================================================

-- ============================================================
-- 一、覆盖索引（Covering Index / Index-Only Scan）
-- ============================================================

-- 1.1 INCLUDE子句（PostgreSQL 11+）
-- 场景：珠宝列表页只需显示名称、价格、品牌、分类
-- 传统方式：回表查询获取完整行数据
-- 覆盖索引方式：所有需要的列都在索引中，无需回表

CREATE INDEX idx_products_covering_list
ON products(category_id, brand, price)
INCLUDE (product_name, sku, stock_quantity);

-- 查询自动使用覆盖索引（Index-Only Scan）
-- EXPLAIN ANALYZE SELECT product_name, sku, price, stock_quantity
-- FROM products WHERE brand = 'Tiffany' AND price > 10000;

-- 1.2 覆盖索引适用场景
-- 场景1：报表查询（只需聚合列）
CREATE INDEX idx_orders_covering_report
ON orders(customer_id, order_date, status)
INCLUDE (total_amount, final_amount);

-- 场景2：分页查询
CREATE INDEX idx_products_covering_paging
ON products(category_id, price DESC)
INCLUDE (product_name, brand, image_url)
WHERE status = 'active';

-- 1.3 覆盖索引注意事项
-- - 索引越大，写入性能越低
-- - 适合读多写少的场景（珠宝产品目录适合）
-- - 定期监控索引大小和使用率

-- ============================================================
-- 二、执行计划分析（EXPLAIN ANALYZE）
-- ============================================================

-- 2.1 基础执行计划分析
-- 查看查询执行计划（不执行，仅分析）
EXPLAIN SELECT * FROM products WHERE brand = 'Tiffany' AND price > 10000;

-- 实际执行并显示耗时
EXPLAIN ANALYZE SELECT * FROM products WHERE brand = 'Tiffany' AND price > 10000;

-- 2.2 执行计划关键操作符解读
-- Seq Scan: 全表扫描（大数据量时避免）
-- Index Scan: 索引扫描（通过索引找到数据行，需回表）
-- Index Only Scan: 覆盖索引扫描（无需回表，最快）
-- Bitmap Index Scan: 位图索引扫描（多条件OR/IN时优化）
-- Bitmap Heap Scan: 位图堆扫描（从位图结果中取数据）
-- Nested Loop: 嵌套循环连接（小表关联适合）
-- Hash Join: 哈希连接（大表关联适合）
-- Merge Join: 归并连接（已排序数据适合）
-- Sort: 排序操作（可能消耗内存/磁盘）
-- Limit: 限制行数

-- 2.3 复杂查询执行计划分析
-- 珠宝销售报表查询
EXPLAIN ANALYZE
SELECT
    c.category_name,
    p.brand,
    COUNT(od.detail_id) AS total_sold,
    SUM(od.subtotal) AS total_revenue,
    AVG(p.price) AS avg_price
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY c.category_name, p.brand
ORDER BY total_revenue DESC;

-- 2.4 统计信息收集
-- 开启详细统计
SET statistics = 1000;  -- 默认100，增加到1000提高统计精度
-- 对特定列设置统计目标
ALTER TABLE products ALTER COLUMN brand SET STATISTICS 500;
ANALYZE products;

-- 2.5 查询缓存与准备语句
-- 创建准备语句（重复执行相同参数查询时提升性能）
PREPARE product_search AS
SELECT * FROM products
WHERE brand = $1 AND price BETWEEN $2 AND $3
ORDER BY price;

EXECUTE product_search('Tiffany', 5000, 50000);
EXECUTE product_search('Cartier', 10000, 100000);

-- 2.6 pg_stat_statements 扩展（查询性能监控）
-- 已在schema中启用
-- 查看Top消耗查询
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
WHERE query LIKE '%products%'
ORDER BY total_exec_time DESC
LIMIT 10;

-- ============================================================
-- 三、索引碎片分析与维护
-- ============================================================

-- 3.1 检查表膨胀
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    CASE WHEN n_live_tup > 0
        THEN round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2)
        ELSE 0 END AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC;

-- 3.2 手动VACUUM清理死行
VACUUM ANALYZE products;
VACUUM ANALYZE orders;
VACUUM ANALYZE order_details;

-- 3.3 检查索引膨胀
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 3.4 重建索引（REINDEX）
-- 重建单个索引
REINDEX INDEX idx_products_brand;
-- 重建整个schema的所有索引
REINDEX SCHEMA public;
-- 重建整个数据库
REINDEX DATABASE jewelry_indexing_db;

-- 3.5 在线重建索引（不阻塞读写）
CREATE INDEX CONCURRENTLY idx_products_cat_price_new ON products(category_id, price);
-- 删除旧索引后重命名
-- DROP INDEX idx_products_cat_price;
-- ALTER INDEX idx_products_cat_price_new RENAME TO idx_products_cat_price;

-- ============================================================
-- 四、空间索引（GiST/SP-GiST）
-- ============================================================

-- 4.1 门店地理位置查询
-- 场景：查找某门店附近5公里内的门店
-- 使用earthdistance扩展
CREATE EXTENSION IF NOT EXISTS earthdistance;

-- 查找北京门店附近的珠宝店
-- SELECT store_name, city,
--        distance(location, '(-116.41, 39.92)'::point) AS distance_m
-- FROM stores
-- WHERE distance(location, '(-116.41, 39.92)'::point) < 5000
-- ORDER BY distance_m;

-- 4.2 空间索引优化
-- GiST索引已创建：CREATE INDEX idx_stores_location_gist ON stores USING GiST (location);
-- 确保查询使用空间索引
EXPLAIN ANALYZE
SELECT store_name, city,
       earth_distance(ll_to_earth(116.41, 39.92), ll_to_earth(121.48, 31.24)) AS distance
FROM stores;

-- 4.3 使用PostGIS扩展（更强大的空间功能）
-- CREATE EXTENSION postgis;
-- 将POINT类型改为GEOMETRY类型
-- 支持更复杂的空间查询：缓冲区分析、空间关联、空间聚合等

-- ============================================================
-- 五、智能查询处理（PostgreSQL 18特性）
-- ============================================================

-- 5.1 并行查询优化
-- PostgreSQL 18增强了并行查询能力
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;
SET min_parallel_table_scan_size = '1MB';
SET min_parallel_index_scan_size = '512kB';

-- 并行聚合查询
-- EXPLAIN ANALYZE
-- SELECT category_id, COUNT(*), AVG(price)
-- FROM products
-- GROUP BY category_id;

-- 5.2 物化视图刷新策略
-- 创建物化视图用于报表
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
    to_char(order_date, 'YYYY-MM') AS sale_month,
    COUNT(*) AS order_count,
    SUM(final_amount) AS total_revenue,
    AVG(final_amount) AS avg_order_value
FROM orders
GROUP BY to_char(order_date, 'YYYY-MM');

CREATE INDEX idx_mv_monthly_sales ON mv_monthly_sales(sale_month);

-- 并发刷新（不阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

-- 5.3 动态视图（实时数据 + 索引加速）
CREATE VIEW v_product_performance AS
SELECT
    p.product_id, p.product_name, p.brand, p.price,
    COALESCE(SUM(od.subtotal), 0) AS total_sales,
    COALESCE(COUNT(od.detail_id), 0) AS order_count,
    COALESCE(AVG(r.rating), 0) AS avg_rating
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.product_name, p.brand, p.price;

-- 对物化视图创建索引
CREATE INDEX idx_mv_product_perf_brand ON mv_product_performance(brand);
CREATE INDEX idx_mv_product_perf_sales ON mv_product_performance(total_sales DESC);

-- ============================================================
-- 六、索引使用率监控视图
-- ============================================================

-- 6.1 综合索引健康检查视图
CREATE OR REPLACE VIEW v_index_health AS
SELECT
    s.schemaname,
    s.relname AS table_name,
    s.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
    s.idx_scan AS times_scanned,
    s.idx_tup_read AS tuples_read,
    s.idx_tup_fetch AS tuples_fetched,
    pg_get_indexdef(s.indexrelid) AS index_definition,
    CASE
        WHEN s.idx_scan = 0 AND pg_relation_size(s.indexrelid) > 1048576
        THEN 'UNUSED_LARGE'
        WHEN s.idx_tup_read = 0 AND s.idx_scan > 0
        THEN 'EMPTY_READS'
        ELSE 'OK'
    END AS health_status
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE s.schemaname = 'public';

-- 查看索引健康状态
SELECT * FROM v_index_health
ORDER BY CASE health_status
    WHEN 'UNUSED_LARGE' THEN 1
    WHEN 'EMPTY_READS' THEN 2
    ELSE 3 END, pg_relation_size(indexrelid) DESC;

-- 6.2 查询性能基线
-- 记录基准查询性能
CREATE TABLE IF NOT EXISTS query_performance_baseline (
    query_hash BIGINT PRIMARY KEY,
    query_text TEXT,
    avg_exec_time_ms DECIMAL(10,2),
    avg_rows DECIMAL(10,2),
    avg_buffers_share DECIMAL(10,2),
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 定期采集性能数据
-- INSERT INTO query_performance_baseline (query_hash, query_text, avg_exec_time_ms, avg_rows)
-- SELECT
--     queryid, query, total_exec_time / calls AS avg_exec_time_ms, rows / calls AS avg_rows
-- FROM pg_stat_statements
-- WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'jewelry_indexing_db')
-- AND calls > 10;

-- 验证索引和视图
SELECT indexname FROM pg_indexes WHERE schemaname = 'public' ORDER BY indexname;
SELECT viewname FROM pg_views WHERE schemaname = 'public' ORDER BY viewname;



-- ============================================================
-- 模块来源: pg18_indexing_partitioning.sql
-- ============================================================

-- ============================================================
-- PostgreSQL 18 珠宝行业「索引与性能优化」
-- 模块5：分区表与分片架构
-- ============================================================

-- ============================================================
-- 一、分区表配置与查询优化
-- ============================================================

-- 1.1 分区表已创建（在schema模块中）
-- orders表：按order_date范围分区
-- inventory_logs表：按created_at范围分区

-- 1.2 分区表上的索引
-- 分区表会自动在子表上创建索引
-- 验证分区索引
SELECT
    schemaname,
    parent.relname AS parent_table,
    child.relname AS partition_name,
    indexrelname AS index_name
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_index i ON i.indrelid = child.oid
JOIN pg_class idx ON idx.oid = i.indexrelid
WHERE parent.relname IN ('orders', 'inventory_logs')
ORDER BY parent.relname, child.relname, indexrelname;

-- 1.3 分区索引的局限性
-- 注意：PostgreSQL分区表的索引是局部的（每个分区独立索引）
-- 没有全局唯一索引（除非显式创建）
-- 如果需要全局唯一约束，需要在所有分区上分别创建

-- ============================================================
-- 二、分区索引对齐
-- ============================================================

-- 2.1 确保分区索引与父表索引一致
-- 为每个分区显式创建索引（保证查询优化器选择）
-- 注意：PostgreSQL 11+ 自动继承父表索引到分区

-- 验证分区索引是否自动继承
-- 在orders分区表上创建索引会自动应用到所有现有分区
CREATE INDEX idx_orders_partition_date ON orders(order_date);
CREATE INDEX idx_orders_partition_status ON orders(status);

-- 2.2 新分区自动继承索引
-- 添加新分区时，已有索引会自动应用到新分区
CREATE TABLE orders_q3_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');
-- idx_orders_partition_date 和 idx_orders_partition_status 会自动应用到新分区

-- ============================================================
-- 三、分区切换/合并/拆分
-- ============================================================

-- 3.1 分区拆分（将大分区拆分为小分区）
-- 场景：将年度分区拆分为季度分区
-- 已有季度分区，如需进一步拆分：
CREATE TABLE orders_2025_jan PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_feb PARTITION OF orders
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- 3.2 分区合并（PostgreSQL 15+ 支持）
-- 将多个小分区合并为一个大分区
-- 注意：PostgreSQL原生不支持分区合并，需要手动迁移数据
-- 替代方案：使用pg_partman扩展

-- 3.3 分区切换（ATTACH/DETACH PARTITION）
-- 快速将表附加为分区（元数据操作，几乎瞬间完成）
CREATE TABLE orders_archive_2023 (LIKE orders INCLUDING ALL);
-- 填充数据...
ALTER TABLE orders ATTACH PARTITION orders_archive_2023
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

-- 分离分区（快速卸载旧数据分区）
ALTER TABLE orders DETACH PARTITION orders_q1_2024;
-- 分离后可以独立备份/归档
-- pg_dump jewelry_indexing_db -t orders_q1_2024 > orders_q1_2024.sql

-- ============================================================
-- 四、分区消除（Partition Pruning）
-- ============================================================

-- 4.1 分区消除原理
-- 查询优化器根据WHERE条件自动跳过不相关的分区
-- 例如：WHERE order_date = '2024-03-15' 只扫描 orders_q1_2024

-- 4.2 验证分区消除
SET enable_partition_pruning = on;  -- PostgreSQL 12+ 默认开启

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date = '2024-06-15'
AND status = 'delivered';
-- 预期：只扫描 orders_q2_2024 分区

-- 4.3 分区消除失效场景
-- 场景1：参数化查询使用变量（优化器无法确定分区）
-- 解决：使用声明式分区 + 常量值

-- 场景2：函数包裹分区键
-- 错误：WHERE EXTRACT(YEAR FROM order_date) = 2024
-- 正确：WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

-- 场景3：类型不匹配
-- 确保查询参数类型与分区键类型一致

-- ============================================================
-- 五、分片架构设计
-- ============================================================

-- 5.1 FDW（外部数据窗口）分片架构
-- 使用postgres_fdw实现跨服务器分片

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 创建远程服务器
CREATE SERVER IF NOT EXISTS jewelry_shard_1
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'shard1-db.example.com', port '5432', dbname 'jewelry_db');

CREATE SERVER IF NOT EXISTS jewelry_shard_2
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'shard2-db.example.com', port '5432', dbname 'jewelry_db');

-- 创建用户映射
CREATE USER MAPPING FOR CURRENT_USER
SERVER jewelry_shard_1
OPTIONS (user 'shard_user', password 'shard_password');

CREATE USER MAPPING FOR CURRENT_USER
SERVER jewelry_shard_2
OPTIONS (user 'shard_user', password 'shard_password');

-- 创建外部表（分片表）
CREATE FOREIGN TABLE orders_shard_1 (
    LIKE orders
) SERVER jewelry_shard_1
OPTIONS (schema_name 'public', table_name 'orders');

CREATE FOREIGN TABLE orders_shard_2 (
    LIKE orders
) SERVER jewelry_shard_2
OPTIONS (schema_name 'public', table_name 'orders');

-- 创建主表（继承所有分片）
CREATE TABLE orders_sharded (LIKE orders)
PARTITION BY LIST (customer_id);

-- 附加分片
ATTACH PARTITION orders_shard_1 FOR VALUES IN (1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29);
ATTACH PARTITION orders_shard_2 FOR VALUES IN (2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30);

-- 5.2 Citus分片（推荐用于大规模数据）
-- Citus是PostgreSQL的分布式扩展
-- 安装Citus扩展
-- CREATE EXTENSION citus;
-- 创建分片表
-- SELECT create_distributed_table('orders', 'customer_id');
-- Citus自动处理数据路由、并行查询、跨节点连接优化

-- 5.3 分片键选择策略
-- 珠宝行业分片键建议：
-- 1. customer_id：按客户分片，适合CRM场景
-- 2. store_id：按门店分片，适合连锁门店场景
-- 3. order_date：按时间分片，适合数据分析场景
-- 避免：高频更新列、低基数列

-- ============================================================
-- 六、性能基准测试
-- ============================================================

-- 6.1 分区表 vs 普通表性能对比
-- 测试场景：按日期范围查询

-- 普通表查询
EXPLAIN ANALYZE
SELECT COUNT(*), SUM(final_amount)
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- 分区表查询（自动分区消除）
EXPLAIN ANALYZE
SELECT COUNT(*), SUM(final_amount)
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- 预期：分区表只扫描 orders_q1_2024 分区，性能显著提升

-- 6.2 大数据量插入性能测试
-- 批量插入测试
INSERT INTO orders (customer_id, store_id, order_number, order_date, status, total_amount, final_amount)
SELECT
    (random() * 29 + 1)::int,
    (random() * 9 + 1)::int,
    'BENCH-' || generate_series,
    '2025-01-01'::timestamp + (random() * 365)::int * interval '1 day',
    'pending'::order_status,
    (random() * 100000 + 1000)::decimal(12,2),
    (random() * 100000 + 1000)::decimal(12,2)
FROM generate_series(1, 100000) AS gs;

-- 6.3 分区表维护性能
-- 快速归档旧数据（DETACH PARTITION比DELETE快很多）
ALTER TABLE orders DETACH PARTITION orders_q1_2024;
-- 旧数据仍在分离的表中，可以独立备份和清理

-- ============================================================
-- 七、自动化分区管理
-- ============================================================

-- 7.1 自动创建未来分区的函数
CREATE OR REPLACE FUNCTION fn_create_future_partitions(
    p_partitioned_table text,
    p_partition_interval interval,
    p_future_months int DEFAULT 6
)
RETURNS void AS $$
DECLARE
    partition_start date;
    partition_end date;
    partition_name text;
    table_oid oid;
BEGIN
    -- 获取分区表的oid
    SELECT oid INTO table_oid
    FROM pg_class WHERE relname = p_partitioned_table AND relnamespace = 'public'::regnamespace;

    -- 为未来N个月创建分区
    FOR i IN 0..p_future_months LOOP
        partition_start := date_trunc('month', CURRENT_DATE) + (i * interval '1 month');
        partition_end := partition_start + interval '1 month';
        partition_name := p_partitioned_table || '_' || to_char(partition_start, 'YYYY_MM');

        -- 检查分区是否已存在
        IF NOT EXISTS (
            SELECT 1 FROM pg_class WHERE relname = partition_name AND relnamespace = 'public'::regnamespace
        ) THEN
            -- 创建分区表
            EXECUTE format(
                'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
                partition_name, p_partitioned_table, partition_start, partition_end
            );

            -- 复制父表索引到分区（PostgreSQL 11+自动处理）
            RAISE NOTICE 'Created partition: %', partition_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 使用示例：提前创建未来6个月的分区
SELECT fn_create_future_partitions('orders', interval '1 month', 6);

-- 7.2 自动归档旧分区函数
CREATE OR REPLACE FUNCTION fn_archive_old_partitions(
    p_partitioned_table text,
    p_keep_partitions int DEFAULT 12,
    p_archive_schema text DEFAULT 'archive'
)
RETURNS void AS $$
DECLARE
    partition_record record;
    partition_count int;
BEGIN
    -- 获取需要归档的分区（超出保留数量的旧分区）
    SELECT count(*) INTO partition_count
    FROM pg_inherits
    WHERE inhparent = (SELECT oid FROM pg_class WHERE relname = p_partitioned_table)
    AND inhrelid NOT IN (
        SELECT inhrelid FROM pg_inherits
        WHERE inhparent = (SELECT oid FROM pg_class WHERE relname = p_partitioned_table)
        ORDER BY inhrelid DESC LIMIT p_keep_partitions
    );

    -- 归档旧分区
    FOR partition_record IN
        SELECT c.relname AS partition_name
        FROM pg_inherits i
        JOIN pg_class c ON i.inhrelid = c.oid
        WHERE i.inhparent = (SELECT oid FROM pg_class WHERE relname = p_partitioned_table)
        AND c.relname NOT IN (
            SELECT c2.relname
            FROM pg_inherits i2
            JOIN pg_class c2 ON i2.inhrelid = c2.oid
            WHERE i2.inhparent = (SELECT oid FROM pg_class WHERE relname = p_partitioned_table)
            ORDER BY c2.relname DESC LIMIT p_keep_partitions
        )
        ORDER BY c.relname
    LOOP
        -- 创建归档表
        EXECUTE format(
            'CREATE TABLE %I.%I (LIKE %I)',
            p_archive_schema, partition_record.partition_name, p_partitioned_table
        );

        -- 分离并附加到归档表
        EXECUTE format('ALTER TABLE %I DETACH PARTITION %I', p_partitioned_table, partition_record.partition_name);
        EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I', p_archive_schema, partition_record.partition_name, partition_record.partition_name);

        RAISE NOTICE 'Archived partition: %', partition_record.partition_name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 7.3 pg_cron 定时任务（自动分区管理）
-- 安装pg_cron扩展
-- CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 每天凌晨2点自动创建未来分区
-- SELECT cron.schedule('create-partitions', '0 2 * * *', 'SELECT fn_create_future_partitions('orders', ''1 month'', 6)');

-- 每周日凌晨3点归档旧分区
-- SELECT cron.schedule('archive-partitions', '0 3 * * 0', 'SELECT fn_archive_old_partitions('orders', 12)');

-- ============================================================
-- 八、分区性能监控
-- ============================================================

-- 8.1 查看分区统计信息
SELECT
    parent.relname AS parent_table,
    child.relname AS partition_name,
    pg_size_pretty(pg_relation_size(child.oid)) AS partition_size,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
LEFT JOIN pg_stat_user_tables stat ON child.oid = stat.relid
WHERE parent.relname IN ('orders', 'inventory_logs')
ORDER BY parent.relname, child.relname;

-- 8.2 分区查询性能分析
-- 对比分区消除前后的查询计划
SET enable_partition_pruning = on;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE order_date = '2024-06-15';

-- 8.3 分区维护建议
-- 1. 定期VACUUM各分区
-- 2. 监控分区大小分布
-- 3. 设置合理的分区键和分区数量
-- 4. 避免过多小分区（影响查询优化器）
-- 5. 使用pg_partman扩展自动化分区管理

-- 验证分区状态
SELECT
    schemaname,
    tablename,
    partitioned
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;