数据库基础 (Database Fundamentals)

一、数据库概述

什么是数据库

数据库 (DB, Database):持久化存储的有组织的数据集合

数据 + 数据的组织方式(Schema)
提供 CRUD 接口
支持并发、事务、查询
保证 ACID 特性

数据库 vs 文件系统

维度	文件系统	数据库
数据结构	字节流	结构化
查询	手动	SQL
索引	无	B+Tree、Hash
事务	无	ACID
并发	文件锁	行锁、MVCC
一致性	弱	强
性能	慢	快(有索引)
适用	简单文件	大量结构化数据

数据库分类

按模型分: - 关系型 (RDBMS): MySQL, PostgreSQL, Oracle, SQL Server - 文档型 (Document): MongoDB, CouchDB - 键值型 (Key-Value): Redis, DynamoDB, etcd - 列式 (Wide-Column): Cassandra, HBase, ClickHouse - 图数据库: Neo4j, JanusGraph - 时序数据库 (Time-Series): InfluxDB, TimescaleDB, Prometheus - 搜索引擎: Elasticsearch, Solr - 向量数据库: Milvus, Pinecone

按使用分: - OLTP (在线事务处理): 日常业务 - OLAP (在线分析处理): 数据分析 - HTAP (混合): TiDB, CockroachDB

二、关系型数据库 (RDBMS)

1. 关系模型

关系 (Relation):二维表(Table)

-- 员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- 部门表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

2. SQL 基础

DDL (Data Definition Language) - 数据定义:

-- CREATE
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ALTER
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);

-- DROP
DROP TABLE users;

-- INDEX
CREATE INDEX idx_users_name ON users(name);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_composite ON users(dept_id, name);

DML (Data Manipulation Language) - 数据操作:

-- INSERT
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30);
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');

-- UPDATE
UPDATE users SET age = 31 WHERE name = 'Alice';
UPDATE users SET age = age + 1;

-- DELETE
DELETE FROM users WHERE age < 18;
TRUNCATE TABLE users;        -- 全删,快,不可回滚

-- SELECT
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 25;
SELECT DISTINCT dept_id FROM users;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

DQL (Data Query Language) - 查询:

-- WHERE
SELECT * FROM users WHERE age > 25 AND name LIKE 'A%';

-- GROUP BY + HAVING
SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM users
GROUP BY dept_id
HAVING cnt > 5
ORDER BY avg_sal DESC;

-- JOIN
SELECT u.name, d.name AS dept
FROM users u
LEFT JOIN departments d ON u.dept_id = d.id;

-- 子查询
SELECT * FROM users
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'Beijing');

-- LIMIT
SELECT * FROM users LIMIT 10 OFFSET 20;

-- UNION
SELECT name FROM users WHERE age > 30
UNION
SELECT name FROM users WHERE dept_id = 1;

DCL (Data Control Language) - 权限:

-- GRANT
GRANT SELECT, INSERT ON users TO 'alice'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

-- REVOKE
REVOKE INSERT ON users FROM 'alice'@'localhost';

TCL (Transaction Control Language) - 事务:

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

-- ROLLBACK
ROLLBACK;

-- SAVEPOINT
SAVEPOINT sp1;
ROLLBACK TO sp1;

3. SQL 高级特性

-- 索引
CREATE INDEX idx_users_email ON users(email);
-- 复合索引
CREATE INDEX idx_users_dept_age ON users(dept_id, age);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_phone ON users(phone);
-- 部分索引
CREATE INDEX idx_active_users ON users(name) WHERE active = true;

-- 视图
CREATE VIEW active_users AS
SELECT id, name FROM users WHERE active = true;

-- 存储过程
DELIMITER //
CREATE PROCEDURE get_user_count(IN dept INT, OUT cnt INT)
BEGIN
    SELECT COUNT(*) INTO cnt FROM users WHERE dept_id = dept;
END //
DELIMITER ;

-- 触发器
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (user_id, change_time)
    VALUES (OLD.id, NOW());
END;

-- 窗口函数 (MySQL 8+, PostgreSQL)
SELECT name, dept_id, salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM users;

-- CTE (PostgreSQL, MySQL 8+)
WITH high_salary AS (
    SELECT * FROM users WHERE salary > 100000
)
SELECT * FROM high_salary WHERE dept_id = 1;

三、ACID 与事务

1. ACID 特性

A (Atomicity) 原子性: 事务要么全做,要么全不做
C (Consistency) 一致性: 事务前后数据一致
I (Isolation) 隔离性: 事务互不干扰
D (Durability) 持久性: 事务提交后永久

2. 事务的隔离级别

级别	脏读	不可重复读	幻读	性能
Read Uncommitted	是	是	是	最高
Read Committed	否	是	是	高
Repeatable Read (MySQL 默认)	否	否	是	中
Serializable	否	否	否	低

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3. 并发问题

脏读 (Dirty Read): 读未提交数据
不可重复读 (Non-repeatable Read): 同一事务两次读不一样
幻读 (Phantom Read): 范围查询,别人插入新行

4. 锁

按粒度: - 行锁: 锁单行 - 页锁: 锁一页(默认 InnoDB) - 表锁: 锁整表 - 数据库锁: 锁整个库

按类型: - 共享锁 (S): 读锁,多个兼容 - 排他锁 (X): 写锁,独占

死锁:见 16-死锁.md

四、索引

1. 索引类型

B+Tree 索引 (默认): - 范围查询优秀 - 等值查询优秀 - 有序

Hash 索引: - 等值查询极快 - 不支持范围 - 例: Memory 引擎

位图索引: - 低基数(性别、状态) - 数据仓库用 - 例: ClickHouse, Oracle

倒排索引: - 全文搜索 - 例: Elasticsearch

全文索引: - 文本搜索 - 例: MySQL FULLTEXT, PostgreSQL GIN

地理空间索引: - GIS 数据 - PostGIS

LSM 树: - 日志结构合并树 - 写性能极好 - 例: RocksDB, LevelDB

2. MySQL InnoDB 索引

-- 主键索引(聚簇)
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- 普通索引
CREATE INDEX idx_users_name ON users(name);

-- 复合索引
CREATE INDEX idx_users_name_age ON users(name, age);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 覆盖索引 (含 SELECT 字段)
CREATE INDEX idx_users_name_cover ON users(name, age, email);

最左前缀原则:

-- 索引 (name, age, email) 包含:
-- name
-- name, age
-- name, age, email
-- 不包含:age, email

3. 索引使用规则

选择性高的列优先: 不同值多
常用查询条件: WHERE、ORDER BY、GROUP BY
避免过多索引: 写慢、占空间
小表不必: 全表扫更快
更新频繁列少建: 索引更新开销

4. EXPLAIN 分析

EXPLAIN SELECT * FROM users WHERE name = 'Alice';
EXPLAIN ANALYZE SELECT ... (PostgreSQL);

关键字段: - type: 访问类型, system > const > eq_ref > ref > range > index > ALL - possible_keys: 可能用到的索引 - key: 实际用的索引 - rows: 扫描行数 - Extra: 额外信息 (Using where, Using index 等)

五、存储引擎

1. MySQL 存储引擎

引擎	事务	锁粒度	适用
InnoDB	是	行锁	默认,通用
MyISAM	否	表锁	只读、查询多
Memory	否	表锁	临时表
CSV	否	表锁	日志
Archive	否	行锁(只插入)	日志、归档
NDB	是	行锁	集群

2. InnoDB 架构

┌──────────────────────────────┐
│       InnoDB 内存              │
│  ┌──────────┐ ┌──────────┐   │
│  │ Buffer Pool│ │ Change    │   │
│  │  (数据页)  │ │ Buffer    │   │
│  └──────────┘ └──────────┘   │
│  ┌──────────┐ ┌──────────┐   │
│  │ Adaptive │ │ Log Buffer│   │
│  │ Hash Idx  │ │           │   │
│  └──────────┘ └──────────┘   │
└──────────────────────────────┘
              ↕
┌──────────────────────────────┐
│       InnoDB 磁盘              │
│  ┌──────────┐ ┌──────────┐   │
│  │ Tablespace│ │ Redo Log  │   │
│  │  (数据)  │ │           │   │
│  └──────────┘ └──────────┘   │
│  ┌──────────┐ ┌──────────┐   │
│  │ Undo Log │ │ Binlog   │   │
│  │          │ │ (Server) │   │
│  └──────────┘ └──────────┘   │
└──────────────────────────────┘

关键概念: - 聚簇索引: 数据按主键组织 - Buffer Pool: 内存缓存数据页 - Change Buffer: 缓存非唯一索引变更 - Redo Log: 重做日志(已提交) - Undo Log: 撤销日志(回滚 + MVCC) - LSN: Log Sequence Number

3. InnoDB 索引组织表

数据按主键聚簇
二级索引存主键值
找二级索引 → 拿主键 → 找数据(回表)

六、日志系统

1. MySQL 日志

日志	作用	引擎
Redo Log	重做,保证持久性	InnoDB
Undo Log	回滚 + MVCC	InnoDB
Binlog	二进制日志,主从复制	Server
Error Log	错误信息	Server
Slow Query Log	慢查询	Server
General Log	所有查询	Server
Relay Log	中继日志,主从	Server

2. WAL (Write-Ahead Logging)

原则: 数据写盘前,先写日志

事务:
1. 修改数据 → 先写 Redo Log
2. 写 Binlog
3. 写盘数据页 (延迟)
4. 提交 (Commit)

崩溃恢复: - 看 Redo Log,重做已提交 - 看 Undo Log,回滚未提交 - 保证 ACID

3. Binlog 模式

STATEMENT: 记录 SQL(默认 MySQL 5.7 前)
ROW: 记录行变化 (MySQL 5.7 后默认)
MIXED: 混合

七、并发控制

1. 锁机制

InnoDB 锁: - 共享锁 (S): 读锁 - 排他锁 (X): 写锁 - 意向锁 (IS / IX): 表级意向 - 记录锁 (Record Lock): 索引记录 - 间隙锁 (Gap Lock): 索引间隙 - Next-Key Lock: 记录 + 间隙

2. MVCC (Multi-Version Concurrency Control)

MVCC:多版本并发控制

读不加锁 (快照读)
写加锁 (当前读)
读不阻塞写,写不阻塞读
隔离性 + 并发性

InnoDB MVCC: - 每行有 2 个隐藏字段: DB_TRX_ID, DB_ROLL_PTR - 查询时构造快照(根据事务 ID) - Undo Log 链构造历史版本

ReadView: - 已提交读 (Read Committed): 每次 SELECT 新建 - 可重复读 (Repeatable Read): 事务第一次 SELECT 创建

3. 乐观锁 vs 悲观锁

维度	悲观锁	乐观锁
假设	一定冲突	大概率不冲突
实现	数据库锁	版本号 / CAS
适用	写多	读多

-- 乐观锁
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 123 AND version = 5;

八、复制与高可用

1. 主从复制

MySQL 主从复制:

Master (写)  →  Binlog  →  Slave (读)

流程: 1. Master 写 Binlog 2. Slave IO 线程拉 Binlog 3. Slave 写 Relay Log 4. Slave SQL 线程重放 5. 数据同步

配置:

# /etc/my.cnf (Master)
[mysqld]
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
binlog-format = ROW

# /etc/my.cnf (Slave)
[mysqld]
server-id = 2
relay-log = /var/lib/mysql/relay-bin
read-only = ON

-- 在 Slave 执行
CHANGE MASTER TO
    MASTER_HOST='master.example.com',
    MASTER_USER='repl',
    MASTER_PASSWORD='secret',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4;

START SLAVE;
SHOW SLAVE STATUS\G

2. 复制模式

异步复制: 默认,Master 不等 Slave
半同步: 至少一个 Slave 收到
同步: 所有 Slave 都收到(慢)

3. 高可用方案

MHA: Master High Availability
MGR: MySQL Group Replication
Galera / Percona XtraDB Cluster: 多主同步
MySQL InnoDB Cluster: MySQL Shell + MySQL Router
Orchestrator: GTID 自动 failover
ProxySQL: 代理层
Keepalived + VIP: 主备切换

4. 主从切换

ProxySQL + MHA 经典方案:

App → ProxySQL → MHA Manager
                ↓ 检测 Master 故障
                ↓ 选新 Master
                ↓ 切换 ProxySQL

九、备份与恢复

1. 备份类型

维度	物理备份	逻辑备份
方式	复制文件	mysqldump / pg_dump
速度	快	慢
体积	大	小
跨版本	难	易
恢复	快	慢
备份粒度	文件系统	库 / 表

2. mysqldump

# 全库
mysqldump -u root -p --all-databases > all.sql

# 单库
mysqldump -u root -p mydb > mydb.sql

# 单表
mysqldump -u root -p mydb users > users.sql

# 压缩
mysqldump -u root -p mydb | gzip > mydb.sql.gz

# 恢复
mysql -u root -p < all.sql
gunzip < mydb.sql.gz | mysql -u root -p

# 常用参数
--single-transaction     # 一致性快照
--routines               # 含存储过程
--triggers               # 含触发器
--events                 # 含事件
--master-data=2          # 记录 binlog 位点

3. mysqlbinlog (binlog 恢复)

# 看 binlog
mysqlbinlog /var/lib/mysql/mysql-bin.000001

# 恢复
mysqlbinlog /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

# 恢复到指定位点
mysqlbinlog --start-position=4 --stop-position=100 mysql-bin.000001 | mysql -u root -p

# 恢复到指定时间
mysqlbinlog --start-datetime="2024-01-01 10:00:00" mysql-bin.000001 | mysql -u root -p

4. XtraBackup (Percona)

# 装
yum install percona-xtrabackup

# 全量备份
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

# 增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

# 准备
xtrabackup --prepare --target-dir=/backup/full

# 恢复
xtrabackup --copy-back --target-dir=/backup/full

5. 物理备份

# 停库 + 复制
systemctl stop mysql
tar -czf /backup/db.tar.gz /var/lib/mysql
systemctl start mysql

# LVM 快照
lvcreate -L 2G -s -n db-snap /dev/vg/mysql
mount /dev/vg/db-snap /mnt
tar -cf - /mnt | gzip > /backup/db.tar.gz
umount /mnt
lvremove /dev/vg/db-snap

十、PostgreSQL

1. PostgreSQL 特点

对象关系型
功能丰富: JSONB、地理空间、窗口函数、CTE、物化视图
MVCC 原生
扩展性强: 自定义类型、函数、操作符
严格 SQL 兼容
ACID 强
社区活跃

2. PostgreSQL vs MySQL

维度	PostgreSQL	MySQL
SQL 标准	严格	较松
复杂查询	强	一般
性能 (简单查询)	快	极快
JSON 支持	JSONB (强)	JSON
地理空间	PostGIS	弱
复制	流复制 / 逻辑	主从 / GTID
高可用	Patroni	MHA / Orchestrator
扩展	极强	一般
适合	复杂查询、数据分析	Web 应用

3. PostgreSQL 工具

pg_dump / pg_restore: 备份
psql: 命令行
pgAdmin: GUI
pg_stat_statements: 查询统计
pg_repack: 在线 VACUUM
Patroni: HA
pgBackRest: 备份
pgBouncer: 连接池

4. PostgreSQL 配置

# /var/lib/pgsql/data/postgresql.conf
listen_addresses = 'localhost'
port = 5432
max_connections = 200
shared_buffers = 4GB                # 25% RAM
effective_cache_size = 12GB         # 75% RAM
work_mem = 64MB
maintenance_work_mem = 512MB
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
random_page_cost = 1.1             # SSD
effective_io_concurrency = 200     # SSD

十一、NoSQL 数据库

1. Redis (键值 / 缓存)

Redis:内存键值数据库,可持久化

特点: - 极快(100k+ QPS) - 多种数据结构 - 持久化 (RDB + AOF) - 主从复制、哨兵、Cluster - Lua 脚本 - Streams、Pub/Sub

数据结构: - String: 缓存、计数器 - Hash: 对象 - List: 队列、栈 - Set: 集合、去重 - Sorted Set: 排行榜 - Stream: 消息流 - HyperLogLog: 基数统计 - BitMap: 位图 - Geospatial: 地理空间

应用: - 缓存 - 分布式锁 (Redlock) - 排行榜 - 计数器 - 限流 - 会话存储

2. MongoDB (文档)

MongoDB:文档型 NoSQL

BSON 格式
类 JSON
灵活 schema
水平扩展
复制集、分片

应用: - 内容管理 - IoT - 实时分析 - 移动 App 后端

3. ClickHouse (OLAP)

ClickHouse:俄罗斯 Yandex 开源,OLAP 列式数据库

极快聚合查询
列式存储
压缩
分布式
适合大数据分析

应用: - 用户行为分析 - 日志分析 - 商业智能 - 实时报表

4. 时序数据库

InfluxDB
TimescaleDB (基于 PG)
Prometheus (内置)
OpenTSDB (基于 HBase)
TDengine (国产)

特点: - 高写入吞吐 - 时间窗口聚合 - 数据降采样 - 保留策略

5. Elasticsearch (搜索)

倒排索引
全文搜索
聚合
近实时
复杂

十二、数据库选型

选型矩阵

需求	推荐
通用 Web 应用	MySQL / PostgreSQL
复杂分析查询	PostgreSQL / ClickHouse
缓存	Redis / Memcached
文档存储	MongoDB
全文搜索	Elasticsearch
时序数据	InfluxDB / TimescaleDB
图关系	Neo4j
大规模 KV	DynamoDB / Cassandra
队列	Kafka / RabbitMQ / Redis Streams
排行榜	Redis Sorted Set
地理空间	PostGIS / Redis GEO

CAP 定理

CAP: 分布式系统三选二

C (Consistency): 一致性
A (Availability): 可用性
P (Partition tolerance): 分区容忍

选择: - CP: HBase, MongoDB (主从) - AP: Cassandra, DynamoDB - CA: 传统 RDBMS(不分区)

主流数据库对比

类别	主流	特点
RDBMS	MySQL, PostgreSQL, Oracle, SQL Server	强 SQL, 事务
KV	Redis, Memcached, etcd, RocksDB	缓存, 配置
文档	MongoDB, CouchDB, ArangoDB	灵活 Schema
列式	Cassandra, HBase, ClickHouse	大数据
搜索	Elasticsearch, Solr, OpenSearch	全文搜索
时序	InfluxDB, TimescaleDB, Prometheus	时序数据
图	Neo4j, JanusGraph, TigerGraph	图关系
向量	Milvus, Pinecone, Qdrant	AI

十三、数据库性能调优

1. 调优层次

1. SQL 与索引 (50%)
2. Schema 设计 (20%)
3. 配置参数 (10%)
4. 架构 (10%)
5. 硬件 (10%)

2. SQL 优化

-- EXPLAIN 分析
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
EXPLAIN ANALYZE SELECT ... -- PG

-- 慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;     -- 1 秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 索引命中
EXPLAIN 中 type=ALL (全表扫) → 加索引
EXPLAIN 中 Extra: Using filesort → 加索引

-- 优化原则
- SELECT 字段按需,不用 SELECT *
- 大表用 LIMIT
- 子查询改成 JOIN
- WHERE 条件用索引列
- 避免在索引列上做函数

3. Schema 优化

-- 选择合适类型
- 整数 INT 够用不用 BIGINT
- 短字符串用 VARCHAR(短)
- 大文本用 TEXT
- 时间用 DATETIME / TIMESTAMP

-- 范式 vs 反范式
- 频繁 JOIN → 反范式(冗余)
- 写多读少 → 范式
- 写少读多 → 反范式

-- 索引
- 索引选择性高的列
- 复合索引最左前缀
- 索引不要太多(5 个以内)
- 大字段不建索引

4. 配置参数

MySQL InnoDB:

[mysqld]
# 内存
innodb_buffer_pool_size = 物理内存的 60-70%
innodb_buffer_pool_instances = 8
innodb_log_file_size = 4G

# IO
innodb_flush_log_at_trx_commit = 1    # ACID
# = 2 性能更好(写 OS 缓存)
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000

# 连接
max_connections = 500

PostgreSQL:

shared_buffers = 25% RAM
effective_cache_size = 70% RAM
work_mem = 64MB
maintenance_work_mem = 512MB

5. 架构优化

读写分离
分库分表: ShardingSphere, MyCat, Vitess
缓存: Redis, Memcached
异步队列: Kafka, RabbitMQ
CDN: 静态资源
分库分表中间件

十四、数据库连接池

1. 主流连接池

HikariCP (Java, Spring Boot 默认)
Druid (阿里, 监控强)
c3p0 (Java, 老牌)
DBCP (Java, Apache)
pgbouncer (PostgreSQL)
ProxySQL (MySQL)
MaxScale (MariaDB)

2. HikariCP 推荐配置

hikari:
  maximum-pool-size: 20
  minimum-idle: 5
  connection-timeout: 30000
  idle-timeout: 600000
  max-lifetime: 1800000
  pool-name: MyHikariCP

3. 监控指标

活跃连接数
空闲连接数
等待连接数
慢查询数
QPS / TPS

十五、数据库监控

1. 监控工具

Prometheus + mysqld_exporter / postgres_exporter
Percona Monitoring (PMM)
Datadog / New Relic
VividCortex
自建 (慢查询 + 性能 schema)

2. 关键指标

-- MySQL
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
SHOW ENGINE INNODB STATUS\G

-- 性能 schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;

-- 进程
SELECT * FROM information_schema.processlist;

-- 锁等待
SELECT * FROM performance_schema.data_locks;

3. 告警

复制延迟 > 60s
慢查询 > 1s
连接数 > 80%
磁盘空间 > 80%
CPU > 80%

十六、数据库迁移

1. 迁移场景

数据迁移: A 数据库 → B 数据库
版本升级: MySQL 5.7 → 8.0
平台迁移: 本地 → 云
分库分表: 拆分大表
数据归档: 老数据移走

2. 迁移工具

mysqldump / mysqlimport
mydumper / myloader (并行,快)
XtraBackup
gh-ost (GitHub, 在线 DDL)
pt-online-schema-change (Percona)
Flyway / Liquibase (Schema 迁移)
DataX (阿里)
Sqoop (Hadoop)

3. Schema 迁移

# Flyway
flyway -url=jdbc:mysql://localhost/test \
       -user=root -password=xxx \
       migrate

# SQL 目录
src/main/resources/db/migration/
├── V1__init.sql
├── V2__add_user_table.sql
└── V3__add_index.sql

十七、数据库安全

1. 认证授权

-- 创建用户
CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'strong_password';
CREATE USER 'readonly'@'%' IDENTIFIED BY 'xxx';

-- 授权
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app'@'10.0.0.%';
GRANT SELECT ON *.* TO 'readonly'@'%';

-- 撤销
REVOKE INSERT ON mydb.* FROM 'app'@'10.0.0.%';

-- 看权限
SHOW GRANTS FOR 'app'@'10.0.0.%';

2. 加密

传输加密: SSL/TLS (require_secure_transport=ON)
静态加密: TDE (Transparent Data Encryption)
字段加密: 应用层加密
密钥管理: HashiCorp Vault, AWS KMS

3. 审计

MySQL Enterprise Audit
MariaDB Audit Plugin
PostgreSQL pgAudit
应用层审计

4. SQL 注入防护

-- ❌ 错误: 字符串拼接
SELECT * FROM users WHERE name = '$name';

-- ✅ 正确: 参数化查询
PREPARE stmt FROM 'SELECT * FROM users WHERE name = ?';
SET @name = 'Alice';
EXECUTE stmt USING @name;

# Python
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

# Java
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
pstmt.setString(1, name);

十八、数据库面试题

1. 事务的 ACID 是什么

原子性、一致性、隔离性、持久性

2. 4 种隔离级别

Read Uncommitted、Read Committed、Repeatable Read、Serializable MySQL 默认 RR,PostgreSQL 默认 RC

3. 索引的数据结构

B+Tree(主流)、Hash(等值)、位图(低基数)、倒排(全文)、LSM(写多)

4. 聚簇索引 vs 非聚簇索引

聚簇: 数据按主键组织,InnoDB 非聚簇: 索引和数据分开,MyISAM

5. MVCC 的原理

多个版本 + Undo Log + ReadView 读不阻塞写,写不阻塞读

6. 主从复制延迟怎么办

并行复制

半同步

强制走主

业务容忍延迟

7. 慢查询优化

EXPLAIN

加索引

重写 SQL

优化 Schema

读写分离

8. 索引为什么用 B+Tree

范围查询快

叶子节点链表

高度低,IO 少

适合磁盘

9. 三大范式

1NF: 原子性 2NF: 完全依赖 3NF: 无传递依赖

10. 乐观锁和悲观锁

悲观: 假设一定冲突,加锁乐观: 假设不冲突,版本号 / CAS

十九、核心要点速记

ACID = 原子/一致/隔离/持久
B+Tree = 主流索引
InnoDB = MySQL 默认引擎
MVCC = 多版本并发控制
WAL = 写日志后写盘
Binlog = Server 层日志
Redo Log = InnoDB 重做
Undo Log = InnoDB 撤销 + MVCC
RR = MySQL 默认隔离级
主从 = Binlog + Relay Log
半同步 = 至少 1 副本确认
HikariCP = Java 默认连接池
EXPLAIN = SQL 分析
覆盖索引 = 索引含查询字段
最左前缀 = 复合索引从左匹配
慢查询 = long_query_time 控制
Redis = 内存 KV
CAP = C/A/P 三选二
分库分表 = 垂直 / 水平
主键设计 = 自增 / UUID / Snowflake
连接池 = 必加
SQL 注入 = 参数化查询
审计 = pgAudit / MariaDB Audit
加密 = 传输 (TLS) / 静态 (TDE)
PostgreSQL = 功能丰富
ClickHouse = OLAP 列式
MongoDB = 文档
ES = 搜索
InfluxDB = 时序
MySQL 8.0 = 默认字符集 utf8mb4
隔离级别 = 越高越慢越安全
WAL + 复制 = 高可用