-- PostgreSQL 更嚴格的SQL標準支持
SELECT * FROM users 
WHERE last_login > CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC
OFFSET 10 LIMIT 20;

-- MySQL 語法（更寬松）
SELECT * FROM users 
WHERE last_login > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY created_at DESC
LIMIT 10, 20;

-- PostgreSQL 特有數(shù)據(jù)類型示例
-- 1. 數(shù)組類型
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],  -- 文本數(shù)組
    prices NUMERIC[]  -- 數(shù)值數(shù)組
);

-- 2. 范圍類型
CREATE TABLE bookings (
    room_id INT,
    period DATERANGE,  -- 日期范圍
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);

-- 3. 復合類型
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zip_code VARCHAR(10)
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    home_address address  -- 使用復合類型
);

-- 4. Hstore (鍵值對)
CREATE TABLE configurations (
    id SERIAL PRIMARY KEY,
    settings HSTORE  -- {"theme": "dark", "lang": "zh_CN"}
);

-- MySQL (InnoDB) MVCC示例
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 行級鎖
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- PostgreSQL MVCC示例
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 行級鎖
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

TPC-C基準測試 (近似值):
┌─────────────────┬─────────────┬──────────────┐
│     場景        │   MySQL 8.0 │ PostgreSQL 16│
├─────────────────┼─────────────┼──────────────┤
│ 簡單點查        │ 85,000 QPS  │ 78,000 QPS   │
│ 復雜JOIN查詢    │ 12,000 QPS  │ 18,000 QPS   │
│ 批量插入        │ 45,000 TPS  │ 52,000 TPS   │
│ 混合讀寫        │ 32,000 TPS  │ 38,000 TPS   │
│ 連接池性能      │ 8,000 連接  │ 5,000 連接   │
└─────────────────┴─────────────┴──────────────┘

注：實際性能受配置、硬件、數(shù)據(jù)模型影響

-- MySQL 索引示例
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT,
    INDEX idx_name (name),
    UNIQUE INDEX uk_email (email),
    FULLTEXT INDEX ft_name (name)  -- 全文索引
);

-- PostgreSQL 索引示例
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    age INT,
    profile JSONB
);

-- 創(chuàng)建多種索引類型
CREATE INDEX idx_name ON users (name);
CREATE UNIQUE INDEX uk_email ON users (email);
CREATE INDEX idx_gin_profile ON users USING GIN (profile);  -- GIN索引
CREATE INDEX idx_brin_date ON log_table USING BRIN (created_at);  -- BRIN索引
CREATE INDEX idx_expression ON users (LOWER(name));  -- 表達式索引

-- MySQL 主從復制配置示例
# 主庫配置 (my.cnf)
[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

# 從庫配置
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;
START SLAVE;

-- PostgreSQL 流復制配置示例
# 主庫配置 (postgresql.conf)
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

# 從庫配置 (recovery.conf)
primary_conninfo = 'host=master port=5432 user=replicator'
primary_slot_name = 'replica_slot'

-- PostgreSQL 擴展示例
-- 1. 安裝擴展
CREATE EXTENSION postgis;          -- 地理空間
CREATE EXTENSION hstore;           -- 鍵值存儲
CREATE EXTENSION pg_trgm;          -- 模糊搜索
CREATE EXTENSION timescaledb;      -- 時序數(shù)據(jù)
CREATE EXTENSION citus;            -- 分布式

-- 2. 使用PostGIS
SELECT ST_Distance(
    ST_MakePoint(-74.006, 40.7128),
    ST_MakePoint(-118.2437, 34.0522)
) * 111319.9 as distance_km;  -- 計算距離

-- 3. 使用TimescaleDB
CREATE TABLE conditions (
    time TIMESTAMPTZ NOT NULL,
    device_id INT,
    temperature FLOAT
);
SELECT create_hypertable('conditions', 'time');

-- MySQL 監(jiān)控
SHOW ENGINE INNODB STATUS\G
SHOW PROCESSLIST;
SELECT * FROM performance_schema.events_statements_summary_by_digest;

-- PostgreSQL 監(jiān)控
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_statements;

-- MySQL 安全配置
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON mydb.* TO 'app_user'@'%';
REVOKE DELETE ON mydb.* FROM 'app_user'@'%';

-- 啟用SSL
[mysqld]
require_secure_transport = ON
ssl_ca = /path/to/ca.pem
ssl_cert = /path/to/server-cert.pem
ssl_key = /path/to/server-key.pem

-- PostgreSQL 安全配置
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO app_user;
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM app_user;

-- 行級安全策略
CREATE POLICY user_policy ON documents
    USING (owner = current_user);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

場景:
  1. Web應用/內(nèi)容管理:
    - WordPress, Drupal, Joomla等
    - 簡單的讀寫操作，大量點查
  
  2. 電商平臺:
    - 高并發(fā)下單
    - 簡單庫存管理
  
  3. 日志/監(jiān)控數(shù)據(jù):
    - 時序數(shù)據(jù)寫入
    - 簡單統(tǒng)計查詢
  
  4. 快速原型開發(fā):
    - 需要快速上手
    - 開發(fā)團隊熟悉MySQL
  
  5. 嵌入式/輕量級應用:
    - 資源受限環(huán)境
    - 單機部署

場景:
  1. 地理信息系統(tǒng):
    - 需要復雜空間查詢
    - PostGIS擴展
  
  2. 金融系統(tǒng):
    - 需要嚴格ACID事務
    - 復雜報表和分析
  
  3. 科學研究:
    - 復雜數(shù)據(jù)分析
    - 自定義數(shù)據(jù)類型
  
  4. 企業(yè)級應用:
    - 復雜業(yè)務邏輯
    - 需要數(shù)據(jù)完整性
  
  5. 數(shù)據(jù)倉庫/分析:
    - 復雜JOIN和窗口函數(shù)
    - 需要ETL處理
  
  6. 國產(chǎn)化環(huán)境:
    - 需要源碼級可控
    - 深度定制需求

-- 使用這個決策樹選擇
CASE 
    WHEN 場景 = '簡單Web應用' AND 團隊技能 = 'MySQL' THEN '選擇MySQL'
    WHEN 場景 = '復雜數(shù)據(jù)分析' OR 需要高級SQL功能 THEN '選擇PostgreSQL'
    WHEN 需要地理空間功能 THEN '選擇PostgreSQL (PostGIS)'
    WHEN 需要JSON深度處理 THEN 'PostgreSQL JSONB更好'
    WHEN 需要簡單復制和高可用 THEN '兩者均可，根據(jù)團隊熟悉度'
    WHEN 需要企業(yè)級功能且預算有限 THEN 'PostgreSQL'
    WHEN 已有Oracle技能希望遷移 THEN 'PostgreSQL (更接近Oracle)'
    WHEN 需要與特定框架集成 THEN '檢查框架推薦'
    ELSE '進行概念驗證測試兩者'
END;

-- MySQL 遷移到 PostgreSQL 注意事項
-- 1. 數(shù)據(jù)類型映射
VARCHAR(255) → TEXT 或 VARCHAR(255)
DATETIME → TIMESTAMP
TINYINT(1) → BOOLEAN

-- 2. 語法差異
-- MySQL
INSERT INTO t1 VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

-- PostgreSQL
INSERT INTO t1 VALUES (1,2,3) 
ON CONFLICT (id) DO UPDATE SET c = t1.c + 1;

-- 3. 自增列
-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

-- PostgreSQL
id SERIAL PRIMARY KEY
-- 或
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

GitHub數(shù)據(jù) (2024年):
┌─────────────────┬─────────────┬──────────────┐
│     指標        │   MySQL     │  PostgreSQL  │
├─────────────────┼─────────────┼──────────────┤
│ GitHub Stars    │ 8.5k        │ 12.5k        │
│ Forks           │ 3.2k        │ 4.1k         │
│ Contributors    │ 450+        │ 1,200+       │
│ 最新提交頻率    │ 中等        │ 高           │
│ Stack Overflow問題| 550k+      | 280k+        |
└─────────────────┴─────────────┴──────────────┘

-- MySQL 優(yōu)化示例
-- 1. 索引優(yōu)化
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);

-- 2. 查詢優(yōu)化
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
-- 使用覆蓋索引
SELECT customer_id, order_date FROM orders WHERE customer_id = 123;

-- 3. 配置優(yōu)化
[mysqld]
innodb_buffer_pool_size = 系統(tǒng)內(nèi)存的70-80%
innodb_log_file_size = 1-2GB
query_cache_type = 0  -- 8.0已移除查詢緩存

-- PostgreSQL 優(yōu)化示例
-- 1. 索引優(yōu)化
CREATE INDEX CONCURRENTLY idx_orders_customer_date 
ON orders (customer_id, order_date DESC);

-- 2. 查詢優(yōu)化
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 123;

-- 3. 配置優(yōu)化
shared_buffers = 系統(tǒng)內(nèi)存的25%
work_mem = 根據(jù)復雜查詢調(diào)整
maintenance_work_mem = 較大值用于維護操作
effective_cache_size = 系統(tǒng)內(nèi)存的50-75%

# MySQL 國產(chǎn)化問題
1. 許可問題: Oracle控制，商業(yè)使用需考慮許可
2. 源碼修改: 非完全自由修改
3. 已適配情況: 部分國產(chǎn)CPU有二進制包

# PostgreSQL 國產(chǎn)化優(yōu)勢
1. 許可自由: PostgreSQL License允許任意修改
2. 源碼可控: 可深度定制適配國產(chǎn)芯片
3. 國產(chǎn)發(fā)行版: 人大金倉、瀚高等基于PostgreSQL
4. 編譯支持:
./configure --host=aarch64-linux-gnu  # ARM
./configure --host=mips64el-linux     # 龍芯

選擇MySQL如果:
  - 你是初創(chuàng)公司，需要快速原型
  - 主要做簡單CRUD的Web應用
  - 團隊已經(jīng)熟悉MySQL生態(tài)
  - 需要與大量現(xiàn)有MySQL應用集成
  - 資源受限，需要輕量級方案

選擇PostgreSQL如果:
  - 你需要復雜查詢和數(shù)據(jù)分析
  - 數(shù)據(jù)完整性至關重要
  - 需要高級SQL功能或擴展
  - 考慮長期技術自主可控
  - 在國產(chǎn)化環(huán)境中部署
  - 需要地理空間或JSON深度處理

1. 初期用MySQL快速啟動，后期遷移到PostgreSQL
2. 使用PostgreSQL作為主數(shù)據(jù)庫，MySQL作為緩存或特定用途
3. 微服務架構(gòu)中，不同服務使用不同數(shù)據(jù)庫
   - 用戶服務 → PostgreSQL (需要數(shù)據(jù)完整性)
   - 日志服務 → MySQL (簡單寫入)
   - 地理服務 → PostgreSQL + PostGIS