#MySQL与PostgreSQL新特性对比 - 2024年最新功能分析

-- JSON数据类型
CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    profile JSON
);

-- JSON数据插入
INSERT INTO user_profiles VALUES (
    1, 
    JSON_OBJECT(
        'name', 'John Doe',
        'age', 30,
        'skills', JSON_ARRAY('Python', 'JavaScript', 'SQL'),
        'address', JSON_OBJECT(
            'city', 'New York',
            'zip', '10001'
        )
    )
);

-- JSON查询
SELECT 
    id,
    JSON_EXTRACT(profile, '$.name') AS name,
    JSON_EXTRACT(profile, '$.skills') AS skills
FROM user_profiles
WHERE JSON_EXTRACT(profile, '$.age') > 25;

-- MySQL 8.0.17+支持JSON_TABLE
SELECT jt.*
FROM user_profiles,
JSON_TABLE(
    profile,
    '$.skills[*]' COLUMNS (
        skill_name VARCHAR(50) PATH '$'
    )
) AS jt
WHERE jt.skill_name = 'Python';

-- 为JSON字段创建虚拟列和索引
ALTER TABLE user_profiles 
ADD COLUMN skill_index VARCHAR(50) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.skills[0]'))) STORED;

CREATE INDEX idx_primary_skill ON user_profiles(skill_index);

-- JSON和JSONB数据类型
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile_json JSON,
    profile_jsonb JSONB
);

-- JSONB数据插入（推荐，性能更好）
INSERT INTO user_profiles (profile_jsonb) VALUES (
    '{
        "name": "John Doe",
        "age": 30,
        "skills": ["Python", "JavaScript", "SQL"],
        "address": {
            "city": "New York",
            "zip": "10001"
        }
    }'::jsonb
);

-- JSONB查询
SELECT 
    id,
    profile_jsonb->>'name' AS name,
    profile_jsonb->'skills' AS skills
FROM user_profiles
WHERE (profile_jsonb->>'age')::int > 25;

-- JSONB路径查询
SELECT *
FROM user_profiles
WHERE profile_jsonb @> '{"skills": ["Python"]}'::jsonb;  -- 包含Python技能

-- JSONB存在查询
SELECT *
FROM user_profiles
WHERE profile_jsonb ? 'address';  -- 包含address字段

-- JSONB路径存在查询
SELECT *
FROM user_profiles
WHERE profile_jsonb ?& ARRAY['name', 'age'];  -- 同时包含name和age字段

-- GIN索引（推荐用于JSONB）
CREATE INDEX idx_profile_gin ON user_profiles USING GIN (profile_jsonb);

-- GIN索引支持复杂的JSON查询
CREATE INDEX idx_skills_gin ON user_profiles USING GIN ((profile_jsonb->'skills'));

-- GiST索引（另一种选择）
CREATE INDEX idx_profile_gist ON user_profiles USING GIST (profile_jsonb);

-- 窗口函数支持
SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank,
    RANK() OVER (ORDER BY salary DESC) AS company_rank,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS prev_salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

-- 递归CTE（8.0+）
WITH RECURSIVE org_hierarchy AS (
    -- 锚点查询
    SELECT 
        employee_id, 
        first_name, 
        last_name, 
        manager_id, 
        1 as level
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询
    SELECT 
        e.employee_id, 
        e.first_name, 
        e.last_name, 
        e.manager_id, 
        oh.level + 1
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy ORDER BY level, last_name;

-- 窗口函数（更丰富的功能）
SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank,
    RANK() OVER (ORDER BY salary DESC) AS company_rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_dense_rank,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS prev_salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
    -- PostgreSQL特有
    PERCENT_RANK() OVER (ORDER BY salary) AS salary_percentile,
    CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution,
    NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

-- 高级CTE功能
WITH RECURSIVE fibonacci(n, fib_n, next_fib) AS (
    -- 基础情况
    SELECT 1, 1, 1
    UNION ALL
    -- 递归情况
    SELECT n + 1, next_fib, fib_n + next_fib
    FROM fibonacci
    WHERE n < 10
)
SELECT n, fib_n FROM fibonacci;

-- PostgreSQL特有：MATERIALIZED/NOT MATERIALIZED
WITH large_dataset AS MATERIALIZED (
    -- 这个CTE会被物化（计算一次并存储）
    SELECT * FROM very_large_table WHERE condition = 'value'
),
processed_data AS (
    SELECT * FROM large_dataset WHERE other_condition = 'other_value'
)
SELECT * FROM processed_data;

-- MySQL分区表（8.0+移除了对早期分区语法的支持）
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    region VARCHAR(50)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 列分区（8.0+支持）
CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT,
    order_date DATE,
    region CHAR(2)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('NW', 'NE'),
    PARTITION p_south VALUES IN ('SW', 'SE'),
    PARTITION p_central VALUES IN ('NC', 'SC')
);

-- 哈希分区
CREATE TABLE sessions (
    session_id VARCHAR(128) NOT NULL,
    user_id INT,
    created_at TIMESTAMP
) PARTITION BY HASH(user_id) PARTITIONS 4;

-- PostgreSQL声明式分区（10+）
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 列表分区
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    region CHAR(2)
) PARTITION BY LIST (region);

CREATE TABLE orders_north PARTITION OF orders
    FOR VALUES IN ('NW', 'NE');

CREATE TABLE orders_south PARTITION OF orders
    FOR VALUES IN ('SW', 'SE');

-- 哈希分区（PostgreSQL 11+）
CREATE TABLE large_table (
    id INT,
    data TEXT
) PARTITION BY HASH (id) (PARTITIONS 8);

-- PostgreSQL 11+：分区上的索引
CREATE INDEX idx_sales_date ON sales_2023 (sale_date);
-- 自动传播到所有分区
CREATE INDEX idx_sales_amount ON sales (amount);

-- MySQL 8.0+ 并行复制
-- 在配置文件中设置
-- slave_parallel_workers = 4
-- slave_parallel_type = LOGICAL_CLOCK

-- InnoDB并行查询（有限支持）
-- MySQL主要依靠查询优化器进行优化

-- 并行扫描
EXPLAIN (ANALYZE, BUFFERS) 
SELECT COUNT(*) FROM large_table WHERE some_column > 1000;

-- 并行聚合
EXPLAIN (ANALYZE, BUFFERS) 
SELECT department_id, COUNT(*), AVG(salary)
FROM employees 
GROUP BY department_id;

-- 并行连接
EXPLAIN (ANALYZE, BUFFERS) 
SELECT e.*, d.*
FROM large_employees e
JOIN large_departments d ON e.dept_id = d.id;

-- 配置并行处理参数
-- 在postgresql.conf中设置
-- max_worker_processes = 8
-- max_parallel_workers_per_gather = 4
-- max_parallel_workers = 8
-- parallel_setup_cost = 1000
-- parallel_tuple_cost = 0.05

-- MySQL 8.0+ 密码强度插件
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- 账户管理
CREATE USER 'app_user'@'%' 
IDENTIFIED BY 'StrongPassword123!'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;

-- 角色管理
CREATE ROLE 'app_reader', 'app_writer', 'app_admin';
GRANT SELECT ON company_db.* TO 'app_reader';
GRANT SELECT, INSERT, UPDATE ON company_db.* TO 'app_writer';
GRANT ALL PRIVILEGES ON company_db.* TO 'app_admin';

-- 动态权限（8.0+新增）
CREATE USER 'monitor_user'@'localhost';
GRANT SELECT ON performance_schema.* TO 'monitor_user'@'localhost';
GRANT SHOW_ROUTINE ON *.* TO 'monitor_user'@'localhost';

-- 行级安全（RLS）
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_isolation_policy ON employees
    FOR ALL
    TO app_user
    USING (department_id = current_setting('app.current_dept')::integer);

-- 列级安全通过视图实现
CREATE VIEW secure_employee_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE current_user IN (
    SELECT role_name FROM user_roles 
    WHERE department_id = employees.department_id
);

-- 透明数据加密（需要外部扩展）
-- 通过pgcrypto扩展实现
CREATE EXTENSION pgcrypto;

-- 加密数据存储
INSERT INTO secure_table (encrypted_data) 
VALUES (pgp_sym_encrypt('sensitive data', 'encryption_key'));

-- SSL连接配置
-- 在pg_hba.conf中配置
-- hostssl all all 0.0.0.0/0 md5

-- MySQL 8.0+ 优化特性
-- 不可见索引
CREATE INDEX idx_invisible ON employees (hire_date) INVISIBLE;

-- 直方图统计
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary, department_id;

-- 直方图使用
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 70000;

-- 成本模型优化
SET SESSION optimizer_switch = 'semijoin=on,factorization=on';

-- MySQL 8.0.22+ 增加的优化
-- 哈希连接（在某些情况下）
EXPLAIN FORMAT=TREE 
SELECT e.*, d.* 
FROM employees e 
JOIN departments d ON e.dept_id = d.id;

-- 即时压缩（PostgreSQL 14+）
-- 在postgresql.conf中设置
-- enable_incremental_sort = on
-- jit_provider = 'llvmjit'  -- JIT编译支持

-- 分区剪枝优化
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sales 
WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

-- 并行处理优化
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT department_id, COUNT(*), AVG(salary)
FROM employees 
GROUP BY department_id;

-- 索引优化
-- BRIN索引（适合大表）
CREATE INDEX idx_large_table_brin ON large_table USING BRIN (date_column);

-- 覆盖索引
CREATE INDEX idx_covering ON employees (department_id, salary) 
INCLUDE (first_name, last_name);

-- 部分索引
CREATE INDEX idx_active_employees ON employees (hire_date) 
WHERE status = 'active';

-- 存储引擎插件
-- 安装组件
INSTALL COMPONENT 'file://component_keyring_file';

-- 插件管理
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
UNINSTALL PLUGIN audit_log;

-- 用户定义函数
-- 需要C/C++编写的UDF

-- 丰富的扩展生态系统
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- 查询性能监控
CREATE EXTENSION IF NOT EXISTS pg_trgm;           -- 文本相似度搜索
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;     -- 模糊匹配
CREATE EXTENSION IF NOT EXISTS uuid-ossp;         -- UUID生成
CREATE EXTENSION IF NOT EXISTS hstore;            -- 键值对存储
CREATE EXTENSION IF NOT EXISTS citext;            -- 大小写不敏感文本
CREATE EXTENSION IF NOT EXISTS ltree;             -- 层次数据结构

-- PostGIS（地理信息）
CREATE EXTENSION IF NOT EXISTS postgis;

-- 全文搜索增强
CREATE EXTENSION IF NOT EXISTS pg_bigm;

-- 使用扩展功能
SELECT similarity('PostgreSQL', 'Postgres');
SELECT levenshtein('kitten', 'sitting');
SELECT gen_random_uuid();

-- MySQL 8.0+ 组复制
-- 配置文件设置
# [mysqld]
# server_id=1
# gtid_mode=ON
# enforce_gtid_consistency=ON
# master_info_repository=TABLE
# relay_log_info_repository=TABLE
# binlog_checksum=NONE
# log_slave_updates=ON
# log_bin=binlog
# binlog_format=ROW
# plugin_load_add='group_replication.so'
# loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
# loose-group_replication_start_on_boot=off
# loose-group_replication_local_address= "host1:33061"
# loose-group_replication_group_seeds= "host1:33061,host2:33061,host3:33061"
# loose-group_replication_bootstrap_group= off

-- 主从复制监控
SHOW SLAVE STATUS\G
SHOW MASTER STATUS;

-- 逻辑复制（PostgreSQL 10+）
-- 创建发布者
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- 创建订阅者
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=pubserver dbname=mydb'
PUBLICATION my_publication;

-- 监控复制
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_stat_wal_receiver;

-- 流复制监控
SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- 性能模式（Performance Schema）
SELECT 
    event_name,
    count_star,
    sum_timer_wait/1000000000 as total_time_seconds
FROM performance_schema.events_statements_summary_global_by_event_name
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 信息模式查询
SELECT 
    table_schema,
    table_name,
    table_rows,
    data_length,
    index_length
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY data_length DESC;

-- 慢查询日志分析
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- pg_stat_statements扩展
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 系统统计视图
SELECT 
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname = current_database();

-- 表统计信息
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;