-- PostgreSQL 更嚴(yán)格的SQL標(biāo)準(zhǔn)支持
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. 復(fù)合類型
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zip_code VARCHAR(10)
);

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

-- 4. Hstore (鍵值對(duì))
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;  -- 行級(jí)鎖
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

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

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

注：實(shí)際性能受配置、硬件、數(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));  -- 表達(dá)式索引

-- MySQL 主從復(fù)制配置示例
# 主庫配置 (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 流復(fù)制配置示例
# 主庫配置 (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 擴(kuò)展示例
-- 1. 安裝擴(kuò)展
CREATE EXTENSION postgis;          -- 地理空間
CREATE EXTENSION hstore;           -- 鍵值存儲(chǔ)
CREATE EXTENSION pg_trgm;          -- 模糊搜索
CREATE EXTENSION timescaledb;      -- 時(shí)序數(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;  -- 計(jì)算距離

-- 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;

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

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

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

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

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

-- MySQL 遷移到 PostgreSQL 注意事項(xiàng)
-- 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年):
┌─────────────────┬─────────────┬──────────────┐
│     指標(biāo)        │   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ù)復(fù)雜查詢調(diào)整
maintenance_work_mem = 較大值用于維護(hù)操作
effective_cache_size = 系統(tǒng)內(nèi)存的50-75%

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

# PostgreSQL 國產(chǎn)化優(yōu)勢(shì)
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應(yīng)用
  - 團(tuán)隊(duì)已經(jīng)熟悉MySQL生態(tài)
  - 需要與大量現(xiàn)有MySQL應(yīng)用集成
  - 資源受限，需要輕量級(jí)方案

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

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