MySQL 与 PostgreSQL 核心差异深度对比 | 极客日志

MySQL 与 PostgreSQL 核心差异深度对比 | 极客日志

维度	MySQL(InnoDB)	PostgreSQL
默认隔离级别	REPEATABLE READ	READ COMMITTED
幻读抑制	next-key lock (范围锁)	MVCC + SSI (RC) / 强串行化 (SERIALIZABLE)
长事务影响	Undo 累积，Purge 压力	阻塞 Vacuum, 表膨胀 (Bloat)
当前读	`SELECT ... FOR UPDATE` 锁定并回表	`FOR UPDATE/SHARE` 行锁
一致性读	MVCC 快照 (undo)	MVCC 快照 (行版本)
Serializable	粗暴加锁，性能开销大	SSI 算法，工程可用

-- MySQL 切到 READ COMMITTED 级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM orders WHERE id = 1;
-- ... 同时另一个会话更新该行并提交 ...
SELECT * FROM orders WHERE id = 1;
-- 看到新值 (MySQL RC)
COMMIT;

-- PostgreSQL 默认就是 READ COMMITTED
BEGIN;
-- 默认 RC
SELECT * FROM orders WHERE id = 1;
-- 并发会话更新并提交后
SELECT * FROM orders WHERE id = 1;
-- 看到新值
COMMIT;

-- 会话 A
START TRANSACTION;
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;
-- 范围锁 (记录 + 间隙)
-- 会话 B
UPDATE users SET age = 25 WHERE age = 19;
-- 阻塞，因为 19 在间隙内

索引类型	最佳场景	写入性能	查询性能	内存/磁盘占用	是否支持排序
B-Tree	等值、范围、排序	中	极快	中～高	✅
GIN	JSON/数组/全文（包含查询）	慢	快（过滤）	高	❌
GiST	空间、全文（写多）	快	中（常需回表）	中	❌
BRIN	超大表 + 物理有序列	极快	快（范围跳过）	极低	❌

-- 二级索引包含所有查询列，避免回表
CREATE INDEX idx_email_status ON users(email,status);
EXPLAIN FORMAT=JSON SELECT email,status FROM users WHERE email='[email protected]';

-- Index Only Scan 需要可见性图 (Visibility Map)
CREATE INDEX idx_email_status ON users(email,status);
EXPLAIN(ANALYZE, BUFFERS) SELECT email,status FROM users WHERE email='[email protected]';

ALTER TABLE users ADD COLUMN email_lower VARCHAR(255) GENERATED ALWAYS AS(LOWER(email)) STORED;
CREATE INDEX idx_email_lower ON users(email_lower);
SELECT * FROM users WHERE email_lower='[email protected]';

CREATE INDEX idx_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email)='[email protected]';

SELECT * FROM users WHERE id < 100000 ORDER BY id DESC LIMIT 20;

CREATE PUBLICATION pub_users FOR TABLE users;

CREATE SUBSCRIPTION sub_users CONNECTION 'host=... port=... user=... password=... dbname=...' PUBLICATION pub_users;

-- 假设有表 events(id bigint, payload jsonb)
CREATE TABLE events ( id BIGSERIAL PRIMARY KEY,
                       payload jsonb NOT NULL);
-- GIN 索引（适合 @> 包含查询等）
CREATE INDEX idx_events_payload_gin ON events USING gin (payload);
-- 查询：payload 包含 {"type":"pay"}
EXPLAIN(ANALYZE, BUFFERS) SELECT * FROM events WHERE payload @>'{"type":"pay"}';
-- 取字段文本
SELECT payload->>'type' FROM events WHERE id = 1;

-- 假设有表 events(id bigint primary key auto_increment, payload json)
CREATE TABLE events ( id BIGINT PRIMARY KEY AUTO_INCREMENT,
                       payload JSON NOT NULL,
                       -- 生成列抽取 JSON key
                       type VARCHAR(32) GENERATED ALWAYS AS(JSON_UNQUOTE(JSON_EXTRACT(payload,'$.type'))) STORED,
                       INDEX idx_events_type(type));
EXPLAIN SELECT * FROM events WHERE type='pay';

-- 简化示例：title + body 做全文
CREATE TABLE docs ( id BIGSERIAL PRIMARY KEY,
                    title text,
                    body text,
                    tsv tsvector );
-- 维护 tsv（实际生产通常用触发器或生成列策略）
UPDATE docs SET tsv = to_tsvector('simple',coalesce(title,'')||' '||coalesce(body,''));
CREATE INDEX idx_docs_tsv_gin ON docs USING gin(tsv);
SELECT * FROM docs WHERE tsv @@ plainto_tsquery('simple','postgres');

CREATE TABLE docs ( id BIGINT PRIMARY KEY AUTO_INCREMENT,
                    title text,
                    body text,
                    FULLTEXT KEY ft_title_body(title, body));
SELECT * FROM docs WHERE MATCH(title, body) AGAINST('postgres' IN NATURAL LANGUAGE MODE);

SELECT * FROM users ORDER BY id LIMIT 100,20;

SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100;

INSERT INTO t(id, v) VALUES(1,'a') ON DUPLICATE KEY UPDATE v = VALUES(v);

INSERT INTO t(id, v) VALUES(1,'a') ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v;

SELECT CONCAT(a, b), IFNULL(x, y);

SELECT a || b, COALESCE(x, y);

SELECT NOW(), DATE_FORMAT(NOW(),'%Y-%m-%d');

SELECT now(), to_char(now(),'YYYY-MM-DD');