PostgreSQL vs MySQL深度对比分析

-- 部分索引（Partial Index）: 只索引满足条件的行
CREATE INDEX idx_active_users ON users (email)
WHERE status = 'active';
-- 只索引活跃用户，索引更小，查询更快

-- 表达式索引（Expression Index）
CREATE INDEX idx_lower_email ON users (LOWER(email));
-- 支持 WHERE LOWER(email) = '[email protected]'

-- GIN索引用于JSONB
CREATE INDEX idx_profile_tags ON users USING gin (profile_data jsonb_path_ops);
-- 支持 WHERE profile_data @> '{"tags": ["developer"]}'

-- BRIN索引用于时序数据（极小的索引体积）
CREATE INDEX idx_logs_created ON access_logs USING brin (created_at);
-- 100GB的表，BRIN索引可能只有几百KB

-- GiST索引用于地理查询
CREATE INDEX idx_locations ON places USING gist (coordinates);
-- 支持 WHERE coordinates <-> point(116.4, 39.9) < 0.01

-- 覆盖索引（INCLUDE语法，PostgreSQL 11+）
CREATE INDEX idx_user_email ON users (email) INCLUDE (name, phone);
-- email用于查找，name和phone只存储不参与索引排序

-- 联合索引（最左前缀）
ALTER TABLE orders ADD INDEX idx_user_status(user_id, status, created_at);

-- 前缀索引
ALTER TABLE articles ADD INDEX idx_title(title(20));

-- 全文索引（InnoDB 5.6+）
ALTER TABLE articles ADD FULLTEXT INDEX ft_content(title, content)
WITH PARSER ngram;  -- 支持中文分词
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('数据库' IN BOOLEAN MODE);

-- 不可见索引（MySQL 8.0+，用于测试删除索引的影响）
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- 优化器不再使用此索引，但索引仍在维护
-- 确认无影响后再真正删除
ALTER TABLE orders DROP INDEX idx_status;

-- 降序索引（MySQL 8.0+）
ALTER TABLE orders ADD INDEX idx_user_time(user_id ASC, created_at DESC);

-- 创建含JSONB列的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

-- 插入数据
INSERT INTO products (name, attributes) VALUES
('iPhone 15', '{"brand": "Apple", "specs": {"ram": 8, "storage": 256}, "tags": ["smartphone", "5G"]}'),
('Galaxy S24', '{"brand": "Samsung", "specs": {"ram": 12, "storage": 512}, "tags": ["smartphone", "AI"]}');

-- 丰富的查询运算符
-- 包含查询
SELECT * FROM products WHERE attributes @> '{"brand": "Apple"}';

-- 路径查询
SELECT * FROM products WHERE attributes -> 'specs' ->> 'ram' = '8';
SELECT * FROM products WHERE (attributes #>> '{specs,storage}')::int > 128;

-- 数组元素查询
SELECT * FROM products WHERE attributes -> 'tags' ? 'AI';

-- JSONPath查询（PostgreSQL 12+）
SELECT * FROM products WHERE attributes @? '$.specs ? (@.ram > 8)';

-- GIN索引加速JSON查询
CREATE INDEX idx_attrs ON products USING gin (attributes);
CREATE INDEX idx_attrs_path ON products USING gin (attributes jsonb_path_ops);

-- JSONB聚合
SELECT
    attributes ->> 'brand' AS brand,
    AVG((attributes #>> '{specs,ram}')::int) AS avg_ram
FROM products
GROUP BY attributes ->> 'brand';

-- JSONB修改
UPDATE products SET attributes = jsonb_set(
    attributes,
    '{specs,ram}',
    '16'
) WHERE name = 'Galaxy S24';

-- 创建含JSON列的表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

-- JSON函数查询
SELECT * FROM products
WHERE JSON_EXTRACT(attributes, '$.brand') = 'Apple';
-- 或使用简写
SELECT * FROM products
WHERE attributes -> '$.brand' = '"Apple"';

-- 虚拟生成列 + 索引（MySQL解决JSON索引的方式）
ALTER TABLE products
ADD COLUMN brand VARCHAR(50) GENERATED ALWAYS AS (attributes ->> '$.brand') VIRTUAL,
ADD INDEX idx_brand (brand);

-- JSON_TABLE: 将JSON数组展开为关系表（MySQL 8.0+）
SELECT p.name, t.tag
FROM products p,
JSON_TABLE(p.attributes, '$.tags[*]' COLUMNS (
    tag VARCHAR(50) PATH '$'
)) t;

-- JSON修改
UPDATE products SET attributes = JSON_SET(
    attributes,
    '$.specs.ram', 16
) WHERE name = 'Galaxy S24';

-- 创建包含全文搜索向量的列
ALTER TABLE articles ADD COLUMN tsv tsvector;
UPDATE articles SET tsv = to_tsvector('english', title || ' ' || content);
CREATE INDEX idx_tsv ON articles USING gin(tsv);

-- 全文搜索查询
SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'database & optimization');

-- 搜索结果排序
SELECT title, ts_rank(tsv, query) AS rank
FROM articles, to_tsquery('english', 'database | index') query
WHERE tsv @@ query
ORDER BY rank DESC;

-- 搜索结果高亮
SELECT title,
    ts_headline('english', content, to_tsquery('database'), 'StartSel=<b>, StopSel=</b>')
FROM articles
WHERE tsv @@ to_tsquery('database');

-- 中文全文搜索（需要安装 zhparser 扩展）
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l WITH simple;
SELECT * FROM articles WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', '数据库');

-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_content(title, content) WITH PARSER ngram;

-- 自然语言模式搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

-- 布尔模式搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+database -mysql' IN BOOLEAN MODE);

-- 查询扩展搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

-- 高并发简单查询（OLTP）
-- MySQL通常略优: 线程模型开销更小，连接管理更高效

-- 复杂分析查询（OLAP）
-- PostgreSQL通常优势明显:
--   1. 更强的查询优化器（支持Hash Join、Merge Join等多种策略）
--   2. 并行查询支持更完善
--   3. CTE（WITH语句）支持更好
--   4. 窗口函数更丰富

-- 窗口函数示例（PostgreSQL更丰富）
SELECT
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    PERCENT_RANK() OVER (PARTITION BY user_id ORDER BY amount) AS pct_rank,
    LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount
FROM orders;

-- PostgreSQL独有: LATERAL JOIN
SELECT u.*, recent_orders.*
FROM users u,
LATERAL (
    SELECT * FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.created_at DESC
    LIMIT 5
) recent_orders;

-- PostgreSQL独有: FILTER子句
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid_count,
    SUM(amount) FILTER (WHERE status = 'shipped') AS shipped_amount
FROM orders;

-- 1. 丰富的数据类型
-- 数组
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);
INSERT INTO events (tags) VALUES (ARRAY['tech', 'database', 'pg']);
SELECT * FROM events WHERE 'database' = ANY(tags);

-- 范围类型
CREATE TABLE reservations (
    room_id INT,
    during TSTZRANGE,  -- 时间范围
    EXCLUDE USING gist (room_id WITH =, during WITH &&)  -- 排他约束防止重叠
);
INSERT INTO reservations VALUES (1, '[2025-01-01, 2025-01-05)');
-- 插入重叠时段会报错

-- 自定义类型
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zipcode TEXT
);

-- 2. 可扩展性
-- 自定义函数（多种语言）
CREATE FUNCTION fibonacci(n INT) RETURNS INT AS $$
BEGIN
    IF n <= 1 THEN RETURN n; END IF;
    RETURN fibonacci(n-1) + fibonacci(n-2);
END;
$$ LANGUAGE plpgsql;

-- PostGIS地理信息扩展
CREATE EXTENSION postgis;
SELECT ST_Distance(
    ST_Point(116.4, 39.9)::geography,
    ST_Point(121.5, 31.2)::geography
) / 1000 AS distance_km;

-- 3. 高级约束
-- CHECK约束
ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);

-- 排他约束
-- (上面的范围类型示例)

-- 4. 物化视图
CREATE MATERIALIZED VIEW monthly_stats AS
SELECT date_trunc('month', created_at) AS month,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY 1;

-- 刷新物化视图（支持并发刷新）
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;