SQL基础教程：MySQL与PostgreSQL实战对比指南

一、数据库选型对比

1.1 核心特性对比

-- MySQL 默认引擎特性
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- PostgreSQL 特性展示
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price NUMERIC(10,2) CHECK (price > 0),
  tags VARCHAR(20)[] NOT NULL
);

关键特性矩阵：

二、基础语法差异

2.1 数据操作对比

-- 自动ID生成（MySQL）
INSERT INTO users (name) VALUES ('Alice');

-- 序列使用（PostgreSQL）
INSERT INTO products (price, tags) 
VALUES (99.99, '{"electronics", "new"}');

2.2 高级查询对比

-- MySQL 分页查询
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 10 OFFSET 20;

-- PostgreSQL 窗口函数
SELECT id, amount,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) 
FROM orders;

三、性能优化实战

3.1 索引优化案例

-- MySQL 复合索引
ALTER TABLE orders 
ADD INDEX idx_user_time (user_id, create_time);

-- PostgreSQL 覆盖索引
CREATE INDEX idx_order_cover ON orders 
USING BRIN (user_id) INCLUDE (amount);

3.2 查询计划分析

-- MySQL执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE id = 1;

-- PostgreSQL执行计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE price > 100;

四、事务与锁机制

4.1 事务处理对比

-- MySQL事务
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- PostgreSQL事务隔离
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 事务操作
COMMIT;

4.2 锁机制演示

-- MySQL行级锁
SELECT * FROM orders 
WHERE user_id = 123 
FOR UPDATE;

-- PostgreSQL咨询锁
SELECT pg_advisory_lock(123);
-- 业务操作
SELECT pg_advisory_unlock(123);

五、适用场景分析

5.1 技术选型决策树

5.2 典型应用场景

# 电商系统选型建议
MySQL：订单交易核心库（高并发写入）
PostgreSQL：商品信息库（JSONB存储多规格）

# 物联网系统选型
MySQL：设备状态时序数据
PostgreSQL：地理空间数据存储（PostGIS）

六、常见问题解决方案

6.1 死锁处理

-- MySQL死锁检测
SHOW ENGINE INNODB STATUS;

-- PostgreSQL死锁处理
SELECT * FROM pg_stat_activity 
WHERE waiting = true;

6.2 数据迁移技巧

# MySQL数据导出
mysqldump -u root -p dbname > backup.sql

# PostgreSQL数据导入
pg_restore -U user -d dbname -v backup.dump

附：学习路径建议

# 30天进阶计划
1-7天：基础CRUD操作
8-14天：索引与事务
15-21天：性能优化
22-30天：架构设计