数据库运维知识-编程学习之家

数据库是现代 Web 应用的数据底座，几乎所有业务系统的数据都依托数据库进行持久化存储。本教程以最流行的关系型数据库 MySQL 为例，从安装配置到运维优化，系统讲解数据库核心知识。

1. MySQL 安装配置

Windows 推荐使用 MySQL 官方安装包，macOS 推荐 Homebrew，Linux 推荐 apt/yum 仓库。安装后需关注字符集与端口配置。

# macOS Homebrew 安装
brew install mysql
brew services start mysql

# 初始化安全配置（设置 root 密码等）
mysql_secure_installation

# 登录 MySQL
mysql -u root -p

建议在 my.cnf / my.ini 中将默认字符集设为 utf8mb4，以支持 emoji 等 4 字节字符。

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections = 200
innodb_buffer_pool_size = 1G

2. 数据库与表

数据库是表的集合，表是数据的载体。创建数据库与表时需明确指定字符集与存储引擎。

-- 创建数据库
CREATE DATABASE pgsr
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE pgsr;

-- 创建用户表
CREATE TABLE users (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(32) NOT NULL UNIQUE,
  email       VARCHAR(128) NOT NULL UNIQUE,
  password    VARCHAR(64) NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. SQL 基础语法

SQL 包含 DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）四类。日常开发最常用的是 DQL 与 DML。

-- 插入数据
INSERT INTO users (username, email, password)
VALUES ("alice", "alice@pgsr.cn", "hashed_pwd");

-- 查询数据
SELECT id, username, email FROM users
WHERE username = "alice";

-- 更新数据
UPDATE users SET email = "new@pgsr.cn"
WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

危险操作警示：UPDATE 与 DELETE 必须带 WHERE 条件！否则会全表更新/删除。生产环境建议先用 SELECT 验证条件。

4. 多表查询

关系型数据库的核心是表与表之间的关联。JOIN 用于将多张表按关联字段合并查询。

-- 文章表关联用户表
SELECT a.id, a.title, u.username AS author, a.created_at
FROM articles a
INNER JOIN users u ON a.user_id = u.id
WHERE a.status = "published"
ORDER BY a.created_at DESC
LIMIT 20;

-- 统计每个用户的文章数
SELECT u.username, COUNT(a.id) AS article_count
FROM users u
LEFT JOIN articles a ON a.user_id = u.id
GROUP BY u.id
HAVING article_count > 0;

JOIN 类型对比：INNER JOIN 取交集、LEFT JOIN 保留左表全部、RIGHT JOIN 保留右表全部、FULL JOIN 取并集（MySQL 不直接支持，需用 UNION）。

5. 数据库设计

合理的表结构是高性能的基础。三大范式是关系型数据库设计的基本原则：

第一范式（1NF）：字段不可再分，每列只包含原子值
第二范式（2NF）：非主键字段必须完全依赖主键（消除部分依赖）
第三范式（3NF）：非主键字段之间不能相互依赖（消除传递依赖）

-- 反例：用户表中直接存所有订单信息（违反 2NF）
users: id, username, order_id, order_title, order_amount...

-- 正例：拆分为用户表与订单表
users:    id, username, email
orders:   id, user_id, title, amount, created_at

实际开发中允许在性能与范式之间做权衡，适度冗余以减少 JOIN 开销，但需保证数据一致性。

6. 索引优化

索引是数据库性能优化的核心手段，类似书的目录，能让数据库快速定位数据。但索引并非越多越好，需根据查询场景合理设计。

-- 单列索引
CREATE INDEX idx_username ON users(username);

-- 联合索引（最左前缀原则）
CREATE INDEX idx_status_created ON articles(status, created_at);

-- 查看执行计划，判断是否走索引
EXPLAIN SELECT * FROM users WHERE email = "alice@pgsr.cn";

索引使用建议：

WHERE、ORDER BY、JOIN 关联字段优先建索引
区分度低的字段（如性别）建索引意义不大
避免在索引列上使用函数或运算：WHERE YEAR(created_at)=2026 会失效
LIKE 以通配符开头：LIKE '%abc' 不走索引，LIKE 'abc%' 可以

7. 数据备份恢复

数据是核心资产，定期备份是数据库运维的底线。常用 mysqldump 进行逻辑备份。

# 备份单个数据库
mysqldump -u root -p pgsr > pgsr_20260803.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_20260803.sql

# 备份并压缩
mysqldump -u root -p pgsr | gzip > pgsr_20260803.sql.gz

# 恢复数据
mysql -u root -p pgsr < pgsr_20260803.sql

# 仅恢复某张表
mysql -u root -p pgsr < pgsr_20260803.sql articles

备份策略建议：每日全量 + binlog 增量备份，备份文件至少保留 7 天并异地存储，定期演练恢复流程确保可用。

8. 日常运维

数据库日常运维需要关注慢查询、连接数、表空间与主从同步状态等核心指标。

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 查看当前连接数
SHOW STATUS LIKE "Threads_connected";

-- 查看数据库大小
SELECT
  table_schema AS db,
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;

-- 查看主从同步状态
SHOW SLAVE STATUS\G

结语

数据库运维是技术深度与经验并重的领域。本教程涵盖了从入门到日常运维的核心知识，建议在掌握后继续学习：

主从复制与读写分离
分库分表与中间件（MyCAT、ShardingSphere）
NoSQL 数据库（Redis、MongoDB）适用场景
云数据库（RDS）的托管与迁移

实战建议：在本机搭建一个 MySQL 实例，按本教程顺序完成建库建表、CRUD、索引、备份恢复全流程，每一步都动手实操才能真正掌握。

数据库运维知识

数据库运维知识：MySQL 从入门到运维