数据库基础介绍

1. 数据库概述

1.1 数据库定义

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

数据库的核心价值:

数据持久化: 长期存储数据
数据共享: 多用户并发访问
数据一致性: 保证数据的准确性和完整性
数据独立性: 数据与应用程序分离

1.2 数据的存储方式

数据存储经历了从简单到复杂、从低效到高效的发展过程。

1. 人工管理阶段

特点:

使用Excel表格等简单工具
数据与程序紧密结合
数据无法共享
数据冗余度高
数据独立性差

应用场景:

个人数据管理
小型项目初期
临时数据存储

局限性:

数据量小
无法并发访问
数据安全性差
维护成本高

2. 文件系统阶段

特点:

使用文件系统存储数据
数据与程序有一定分离
支持基本的文件操作
数据共享能力有限

应用场景:

小型应用系统
配置文件存储
日志文件管理

局限性:

数据冗余
数据不一致
数据独立性差
缺乏统一管理

3. 数据库系统阶段

特点:

数据结构化
数据共享性高
数据独立性好
数据统一管理
支持并发控制
提供数据安全保护

优势:

减少数据冗余
提高数据一致性
支持复杂查询
提供事务处理
支持高并发访问

1.3 数据库技术构成

1. 数据库系统 (DataBase System, DBS)

数据库系统是一个完整的系统，包括：

组成部分:

数据库（DB）: 存储数据的集合
数据库管理系统（DBMS）: 管理数据库的软件
数据库管理员（DBA）: 管理数据库的人员
应用程序: 使用数据库的应用系统
用户: 使用数据库的最终用户

系统架构:

用户层
  ↓
应用层
  ↓
数据库管理系统（DBMS）
  ↓
操作系统（OS）
  ↓
硬件层

2. 数据库管理系统（DataBase Management System, DBMS）

数据库管理系统是数据库系统的核心，负责数据库的创建、使用和维护。

DBMS主要功能:

功能模块	说明
数据定义	定义数据库结构、表结构、索引等
数据操作	插入、删除、修改、查询数据
数据控制	用户权限管理、数据安全控制
数据维护	数据备份、恢复、重组、性能监控
事务管理	保证数据一致性和完整性
并发控制	处理多用户并发访问
故障恢复	系统故障后的数据恢复

DBMS分类:

分类	说明	代表产品
层次型DBMS	树形结构	IMS
网状型DBMS	网状结构	IDMS
关系型DBMS	二维表结构	MySQL、Oracle、PostgreSQL
对象型DBMS	面向对象	ObjectStore
NoSQL DBMS	非关系型	MongoDB、Redis

1.4 数据库类型划分

关系型数据库 (RDS - Relational Database System)

关系型数据库是基于关系模型的数据库，使用二维表来存储数据。

特点:

数据结构化
数据一致性
支持ACID特性
支持SQL标准
支持复杂查询
支持事务处理

ACID特性:

Atomicity（原子性）: 事务要么全部执行，要么全部不执行
Consistency（一致性）: 事务执行前后数据保持一致
Isolation（隔离性）: 并发事务之间相互隔离
Durability（持久性）: 事务提交后数据永久保存

主流关系型数据库:

数据库	厂商	特点	应用场景
Oracle	Oracle	企业级、功能强大、性能优秀	大型企业、金融、电信
MySQL	Oracle	开源、易用、性能好	Web应用、中小企业
MariaDB	MariaDB Foundation	MySQL分支、完全兼容	Web应用、替代MySQL
Percona Server for MySQL	Percona	MySQL优化版本、性能提升	高性能Web应用
PostgreSQL	PostgreSQL Global Development Group	功能丰富、标准兼容	企业应用、GIS系统
SQL Server	Microsoft	Windows平台、易用	Windows环境企业应用
DB2	IBM	企业级、高可用	大型企业、金融

关系型数据库优势:

成熟稳定
标准化程度高
支持复杂查询
数据一致性保证
丰富的工具支持

关系型数据库劣势:

扩展性有限
性能瓶颈
不适合非结构化数据
水平扩展困难

非关系型数据库 (NoSQL)

NoSQL（Not Only SQL）数据库是为了解决关系型数据库的局限性而出现的。

特点:

非结构化数据存储
高性能
高可扩展性
灵活的数据模型
弱一致性或最终一致性

NoSQL分类:

类型	说明	代表产品	应用场景
键值存储	简单的key-value存储	Redis、Memcached	缓存、会话存储
文档数据库	存储文档型数据	MongoDB、CouchDB	内容管理、日志存储
列式数据库	按列存储数据	HBase、Cassandra	大数据分析、时序数据
图数据库	存储图结构数据	Neo4j、ArangoDB	社交网络、推荐系统

主流NoSQL数据库:

数据库	类型	特点	应用场景
Redis	键值存储	内存数据库、高性能	缓存、消息队列、计数器
Memcached	键值存储	分布式内存缓存	Web缓存、会话存储
MongoDB	文档数据库	灵活的文档模型	内容管理、日志分析
Cassandra	列式数据库	分布式、高可用	大数据、时序数据
HBase	列式数据库	Hadoop生态	大数据存储、分析
Neo4j	图数据库	图结构存储	社交网络、知识图谱

NoSQL优势:

高性能
高可扩展性
灵活的数据模型
适合大数据场景
水平扩展容易

NoSQL劣势:

数据一致性弱
查询能力有限
标准化程度低
学习成本高

数据库选型建议

选择关系型数据库的场景:

需要强一致性
复杂查询需求
事务处理要求
结构化数据
中小规模应用

选择NoSQL数据库的场景:

高并发读写
大数据量存储
非结构化数据
水平扩展需求
缓存场景

混合架构:

关系型数据库存储核心业务数据
NoSQL数据库用于缓存、日志、分析
根据业务场景选择合适的数据库

1.5 SQL语言（结构化查询语言）

SQL（Structured Query Language）是用于管理关系型数据库的标准语言。

SQL语言分类

1. DDL（Data Definition Language，数据库定义语言）

用于定义数据库结构，由开发人员使用。

主要命令:

CREATE: 创建数据库、表、视图、索引等
DROP: 删除数据库、表、视图、索引等
ALTER: 修改数据库、表结构
TRUNCATE: 清空表数据

示例:

-- 创建数据库
CREATE DATABASE testdb;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_username ON users(username);

-- 修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 删除表
DROP TABLE users;

2. DML（Data Manipulation Language，数据库操作语言）

用于操作数据库中的数据，由开发人员使用。

主要命令:

INSERT: 插入数据
UPDATE: 更新数据
DELETE: 删除数据

示例:

-- 插入数据
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- 更新数据
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

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

3. DQL（Data Query Language，数据库查询语言）

用于查询数据库中的数据，由运维和开发人员使用。

主要命令:

SELECT: 查询数据

示例:

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT username, email FROM users WHERE id > 10;

-- 聚合查询
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;

-- 多表关联查询
SELECT u.username, o.order_id 
FROM users u 
JOIN orders o ON u.id = o.user_id;

4. DCL（Data Control Language，数据库控制语言）

用于控制用户的访问权限，由运维人员使用。

主要命令:

GRANT: 授予权限
REVOKE: 撤销权限
COMMIT: 提交事务
ROLLBACK: 回滚事务

示例:

-- 授予权限
GRANT SELECT, INSERT ON testdb.users TO 'user1'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON testdb.* TO 'admin'@'%';

-- 撤销权限
REVOKE INSERT ON testdb.users FROM 'user1'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

1.6 数据访问方式

ODBC（Open Database Connectivity）

ODBC是微软开发的数据库访问接口标准。

特点:

跨平台数据库访问
统一的API接口
支持多种数据库
主要用于Windows平台

应用场景:

PHP应用
.NET应用
Windows桌面应用

配置示例:

# odbc.ini
[MySQL]
Driver = /usr/lib/libmyodbc.so
Server = localhost
Database = testdb
Port = 3306

JDBC（Java Database Connectivity）

JDBC是Java语言的数据库访问接口标准。

特点:

Java标准API
跨平台支持
支持多种数据库
面向对象设计

应用场景:

Java Web应用（JSP）
Spring框架应用
Java桌面应用

连接示例:

// JDBC连接示例
String url = "jdbc:mysql://localhost:3306/testdb";
String user = "root";
String password = "password";

Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

其他数据访问方式

ORM框架:

Hibernate: Java ORM框架
MyBatis: Java持久层框架
Entity Framework: .NET ORM框架
Django ORM: Python ORM框架

连接池技术:

C3P0: Java连接池
Druid: 阿里巴巴连接池
HikariCP: 高性能连接池

2. 数据库运维

2.1 安装部署

数据库的安装部署是数据库运维的基础，不同的安装方式适用于不同的场景。

YUM安装

特点:

简单快速
自动解决依赖
易于管理
适合快速部署

适用场景:

开发测试环境
快速部署
学习环境

安装示例:

# 配置MySQL YUM源
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm

# 安装MySQL
yum install mysql-server -y

# 启动服务
systemctl start mysqld
systemctl enable mysqld

二进制安装

特点:

安装速度快
不需要编译
版本可控
适合生产环境

适用场景:

生产环境
需要特定版本
标准化部署

安装示例:

# 下载二进制包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

# 解压
tar xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

# 创建用户和目录
useradd -r -s /sbin/nologin mysql
mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql
chown -R mysql:mysql /usr/local/mysql

# 初始化数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql

# 配置启动脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld

源码编译安装

特点:

完全可控
可定制编译选项
性能优化
学习价值高

适用场景:

需要特定功能
性能优化需求
学习研究
特殊环境

编译示例:

# 安装编译依赖
yum install -y gcc gcc-c++ cmake ncurses-devel openssl-devel

# 下载源码
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33.tar.gz
tar xf mysql-8.0.33.tar.gz
cd mysql-8.0.33

# 配置编译选项
cmake . \
    -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DMYSQL_DATADIR=/data/mysql \
    -DSYSCONFDIR=/etc \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_READLINE=1 \
    -DWITH_SSL=system \
    -DWITH_ZLIB=system \
    -DWITH_LIBWRAP=0 \
    -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
    -DDEFAULT_CHARSET=utf8mb4 \
    -DDEFAULT_COLLATION=utf8mb4_unicode_ci

# 编译安装
make -j$(nproc)
make install

编译选项说明:

CMAKE_INSTALL_PREFIX: 安装路径
MYSQL_DATADIR: 数据目录
WITH_INNOBASE_STORAGE_ENGINE: 启用InnoDB引擎
DEFAULT_CHARSET: 默认字符集
DEFAULT_COLLATION: 默认排序规则

2.2 基础操作

数据库基础操作是数据库运维的核心技能。

增删查改（CRUD）

Create（创建）:

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

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    password VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO users (username, email, password) 
VALUES ('john', 'john@example.com', 'password123');

Read（读取）:

-- 查询所有数据
SELECT * FROM users;

-- 条件查询
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username LIKE 'j%';

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC;

-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 0;

Update（更新）:

-- 更新单条记录
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- 批量更新
UPDATE users SET status = 'active' WHERE created_at < '2023-01-01';

Delete（删除）:

-- 删除单条记录
DELETE FROM users WHERE id = 1;

-- 批量删除
DELETE FROM users WHERE status = 'inactive';

-- 清空表（谨慎使用）
TRUNCATE TABLE users;

单表查询

基本查询:

-- 选择特定列
SELECT username, email FROM users;

-- 使用别名
SELECT username AS name, email AS mail FROM users;

-- 去重查询
SELECT DISTINCT status FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- 模糊查询
SELECT * FROM users WHERE username LIKE '%john%';

-- 范围查询
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);

-- NULL值查询
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

聚合函数:

-- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM users;

-- 求和
SELECT SUM(amount) FROM orders;

-- 平均值
SELECT AVG(age) FROM users;

-- 最大值/最小值
SELECT MAX(created_at) FROM users;
SELECT MIN(age) FROM users;

-- 分组聚合
SELECT status, COUNT(*) FROM users GROUP BY status;
SELECT DATE(created_at), COUNT(*) FROM users GROUP BY DATE(created_at);

排序和限制:

-- 排序
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY age ASC, username DESC;

-- 限制结果
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 分页

多表查询

内连接（INNER JOIN）:

-- 等值连接
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 多表连接
SELECT u.username, o.order_id, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

外连接（OUTER JOIN）:

-- 左连接（LEFT JOIN）
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接（RIGHT JOIN）
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 全连接（FULL OUTER JOIN）- MySQL不支持，使用UNION实现
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

子查询:

-- 标量子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- 行子查询
SELECT * FROM users 
WHERE (age, status) = (SELECT age, status FROM users WHERE id = 1);

-- 列子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 表子查询
SELECT u.username, o.order_count
FROM users u
JOIN (SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id) o
ON u.id = o.user_id;

联合查询（UNION）:

-- 合并查询结果
SELECT username FROM users WHERE status = 'active'
UNION
SELECT username FROM users WHERE age > 18;

-- UNION ALL（不去重）
SELECT username FROM users WHERE status = 'active'
UNION ALL
SELECT username FROM users WHERE age > 18;

视图（Views）

视图是虚拟表，基于SQL查询结果。

创建视图:

-- 创建简单视图
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';

-- 创建复杂视图
CREATE VIEW user_order_summary AS
SELECT 
    u.id,
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 使用视图
SELECT * FROM active_users;
SELECT * FROM user_order_summary WHERE order_count > 10;

视图优势:

简化复杂查询
数据安全性
逻辑独立性
数据抽象

视图限制:

不能包含子查询中的ORDER BY
某些视图不能更新
性能可能不如直接查询

触发器（Triggers）

触发器是在特定事件发生时自动执行的存储过程。

创建触发器:

-- 插入前触发器
DELIMITER $$
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.email IS NULL THEN
        SET NEW.email = CONCAT(NEW.username, '@example.com');
    END IF;
END$$
DELIMITER ;

-- 更新后触发器
DELIMITER $$
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action, old_value, new_value, created_at)
    VALUES (NEW.id, 'UPDATE', OLD.username, NEW.username, NOW());
END$$
DELIMITER ;

-- 删除触发器
DELIMITER $$
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users (id, username, email, deleted_at)
    VALUES (OLD.id, OLD.username, OLD.email, NOW());
END$$
DELIMITER ;

触发器应用场景:

数据验证
审计日志
数据同步
业务规则 enforcement

存储过程（Stored Procedures）

存储过程是预编译的SQL语句集合。

创建存储过程:

-- 简单存储过程
DELIMITER $$
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END$$
DELIMITER ;

-- 调用存储过程
CALL GetUserById(1);

-- 带输出参数的存储过程
DELIMITER $$
CREATE PROCEDURE GetUserCount(OUT total_count INT)
BEGIN
    SELECT COUNT(*) INTO total_count FROM users;
END$$
DELIMITER ;

-- 调用
CALL GetUserCount(@count);
SELECT @count;

-- 复杂存储过程
DELIMITER $$
CREATE PROCEDURE CreateUserWithOrder(
    IN p_username VARCHAR(50),
    IN p_email VARCHAR(100),
    IN p_order_amount DECIMAL(10,2),
    OUT p_user_id INT,
    OUT p_order_id INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    INSERT INTO users (username, email) VALUES (p_username, p_email);
    SET p_user_id = LAST_INSERT_ID();
    
    INSERT INTO orders (user_id, amount) VALUES (p_user_id, p_order_amount);
    SET p_order_id = LAST_INSERT_ID();
    
    COMMIT;
END$$
DELIMITER ;

存储过程优势:

性能优化
代码复用
安全性
事务处理

2.3 权限管理

数据库权限管理是保障数据安全的重要手段。

用户管理

创建用户:

-- 创建本地用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

-- 创建远程用户
CREATE USER 'user2'@'%' IDENTIFIED BY 'password123';

-- 创建指定IP用户
CREATE USER 'user3'@'192.168.1.%' IDENTIFIED BY 'password123';

-- 修改用户密码
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword123';

-- 删除用户
DROP USER 'user1'@'localhost';

权限授予

基本权限授予:

-- 授予SELECT权限
GRANT SELECT ON testdb.* TO 'user1'@'localhost';

-- 授予多个权限
GRANT SELECT, INSERT, UPDATE ON testdb.users TO 'user1'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON testdb.* TO 'admin'@'%';

-- 授予特定表权限
GRANT SELECT, INSERT ON testdb.users TO 'user1'@'localhost';
GRANT SELECT ON testdb.orders TO 'user1'@'localhost';

权限级别:

全局权限: GRANT ALL PRIVILEGES ON *.*
数据库权限: GRANT ALL PRIVILEGES ON database.*
表权限: GRANT ALL PRIVILEGES ON database.table
列权限: GRANT SELECT (col1, col2) ON database.table

常用权限:

SELECT: 查询权限
INSERT: 插入权限
UPDATE: 更新权限
DELETE: 删除权限
CREATE: 创建权限
DROP: 删除权限
ALTER: 修改权限
INDEX: 索引权限
EXECUTE: 执行存储过程权限

权限撤销

-- 撤销特定权限
REVOKE INSERT ON testdb.users FROM 'user1'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON testdb.* FROM 'user1'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

查看权限

-- 查看用户权限
SHOW GRANTS FOR 'user1'@'localhost';

-- 查看当前用户权限
SHOW GRANTS;

-- 查看所有用户
SELECT user, host FROM mysql.user;

安全机制

密码策略:

-- 设置密码复杂度
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- 设置密码过期
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 设置账户锁定
ALTER USER 'user1'@'localhost' ACCOUNT LOCK;
ALTER USER 'user1'@'localhost' ACCOUNT UNLOCK;

最小权限原则:

只授予必要的权限
定期审查用户权限
及时撤销不需要的权限
使用角色管理权限

2.4 日志管理

数据库日志是数据库运维和故障排查的重要工具。

访问日志

通用查询日志（General Query Log）:

-- 查看通用查询日志配置
SHOW VARIABLES LIKE 'general_log%';

-- 启用通用查询日志
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- 查看日志
tail -f /var/log/mysql/general.log

慢查询日志（Slow Query Log）:

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 分析慢查询日志
mysqldumpslow /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log

错误日志

错误日志配置:

-- 查看错误日志位置
SHOW VARIABLES LIKE 'log_error';

-- 查看错误日志
tail -f /var/log/mysql/error.log

-- 查看最近的错误
grep -i error /var/log/mysql/error.log | tail -20

错误日志内容:

启动和关闭信息
错误和警告信息
崩溃信息
性能问题

Binlog（二进制日志）

Binlog配置:

-- 查看binlog配置
SHOW VARIABLES LIKE 'log_bin%';

-- 启用binlog
[mysqld]
log-bin=mysql-bin
binlog-format=ROW
expire_logs_days=7
max_binlog_size=100M

-- 查看binlog文件
SHOW BINARY LOGS;

-- 查看当前binlog
SHOW MASTER STATUS;

-- 查看binlog内容
mysqlbinlog /var/lib/mysql/mysql-bin.000001

-- 查看特定时间段的binlog
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
            --stop-datetime="2023-01-02 00:00:00" \
            /var/lib/mysql/mysql-bin.000001

Binlog格式:

STATEMENT: 记录SQL语句
ROW: 记录行变化（推荐）
MIXED: 混合模式

Binlog应用场景:

主从复制
数据恢复
数据审计
增量备份

日志轮转

配置日志轮转:

# /etc/logrotate.d/mysql
/var/log/mysql/*.log {
    daily
    rotate 30
    missingok
    compress
    delaycompress
    notifempty
    create 0640 mysql mysql
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

2.5 备份恢复

数据备份是数据库运维的核心工作，是数据安全的最后一道防线。

逻辑备份

mysqldump备份:

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

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

# 备份特定表
mysqldump -u root -p testdb users orders > tables_backup.sql

# 只备份结构
mysqldump -u root -p --no-data testdb > testdb_structure.sql

# 只备份数据
mysqldump -u root -p --no-create-info testdb > testdb_data.sql

# 压缩备份
mysqldump -u root -p testdb | gzip > testdb_backup.sql.gz

# 备份时锁定表
mysqldump -u root -p --single-transaction testdb > testdb_backup.sql

# 备份时指定字符集
mysqldump -u root -p --default-character-set=utf8mb4 testdb > testdb_backup.sql

mysqldump恢复:

# 恢复数据库
mysql -u root -p testdb < testdb_backup.sql

# 恢复所有数据库
mysql -u root -p < all_databases_backup.sql

# 恢复压缩备份
gunzip < testdb_backup.sql.gz | mysql -u root -p testdb

逻辑备份特点:

可读性强
跨平台兼容
可选择性恢复
备份文件较大
恢复速度较慢

物理备份

XtraBackup备份:

# 全量备份
innobackupex --user=root --password=password /backup/

# 增量备份
innobackupex --user=root --password=password \
    --incremental /backup/ \
    --incremental-basedir=/backup/2023-01-01_00-00-00/

# 准备备份
innobackupex --apply-log /backup/2023-01-01_00-00-00/

# 恢复备份
innobackupex --copy-back /backup/2023-01-01_00-00-00/

物理备份特点:

备份速度快
恢复速度快
备份文件较小
需要停止服务或锁表
平台相关

备份策略

全量备份:

每天一次全量备份
保留最近7天的全量备份

增量备份:

每小时一次增量备份
基于上次全量或增量备份

备份脚本示例:

#!/bin/bash
# 数据库备份脚本

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="root"
DB_PASS="password"
DB_NAME="testdb"
RETENTION_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 全量备份
mysqldump -u$DB_USER -p$DB_PASS \
    --single-transaction \
    --routines \
    --triggers \
    $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 删除过期备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

# 发送备份通知
echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz" | mail -s "MySQL Backup" admin@example.com

2.6 HA架构（高可用架构）

高可用架构是保障数据库服务连续性的重要手段。

主从复制（Master-Slave Replication）

主从复制原理:

Master将变更写入binlog
Slave的IO线程从Master读取binlog
Slave的SQL线程执行binlog中的SQL
Slave与Master保持数据同步

主从复制配置:

Master配置:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
expire_logs_days = 7
max_binlog_size = 100M

创建复制用户:

1
2
3

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

查看Master状态:

1	SHOW MASTER STATUS;

Slave配置:

[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read-only = 1

配置Slave:

CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

主从复制优势:

数据备份
读写分离
负载分担
高可用基础

MHA（Master High Availability）

MHA是MySQL高可用解决方案。

MHA架构:

1
2
3

Master (192.168.1.10)
  ├── Slave1 (192.168.1.11)
  └── Slave2 (192.168.1.12)

MHA功能:

自动故障检测
自动故障转移
自动切换VIP
在线切换Master

MHA配置:

[server default]
user=mha
password=mha_password
ssh_user=root
repl_user=repl
repl_password=repl_password

[server1]
hostname=192.168.1.10
candidate_master=1

[server2]
hostname=192.168.1.11
candidate_master=1

[server3]
hostname=192.168.1.12
no_master=1

Mycat（数据库中间件）

Mycat是数据库分库分表中间件。

Mycat功能:

读写分离
分库分表
负载均衡
SQL路由

Mycat配置示例:

<!-- schema.xml -->
<schema name="testdb" checkSQLschema="false" sqlMaxLimit="100">
    <table name="users" dataNode="dn1,dn2" rule="mod-long"/>
</schema>

<dataNode name="dn1" dataHost="dh1" database="testdb1"/>
<dataNode name="dn2" dataHost="dh2" database="testdb2"/>

<dataHost name="dh1" maxCon="1000" minCon="10" balance="1">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master1" url="192.168.1.10:3306" user="root" password="password">
        <readHost host="slave1" url="192.168.1.11:3306" user="root" password="password"/>
    </writeHost>
</dataHost>

2.7 性能优化

数据库性能优化是数据库运维的核心技能。

索引优化

创建索引:

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

-- 复合索引
CREATE INDEX idx_user_status ON users(username, status);

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

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

索引优化原则:

为经常查询的列创建索引
为WHERE、JOIN、ORDER BY中的列创建索引
避免在小表上创建过多索引
定期分析索引使用情况

查看索引使用情况:

-- 查看表索引
SHOW INDEX FROM users;

-- 分析查询计划
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- 查看索引统计
SELECT * FROM information_schema.STATISTICS 
WHERE TABLE_NAME = 'users';

查询优化

优化技巧:

-- 1. 避免SELECT *
SELECT id, username, email FROM users;  -- 好
SELECT * FROM users;  -- 差

-- 2. 使用LIMIT限制结果
SELECT * FROM users LIMIT 10;  -- 好
SELECT * FROM users;  -- 差

-- 3. 使用索引列进行查询
SELECT * FROM users WHERE id = 1;  -- 好（id是主键）
SELECT * FROM users WHERE username = 'john';  -- 好（有索引）

-- 4. 避免在WHERE子句中使用函数
SELECT * FROM users WHERE DATE(created_at) = '2023-01-01';  -- 差
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02';  -- 好

-- 5. 使用JOIN代替子查询
SELECT u.* FROM users u 
JOIN orders o ON u.id = o.user_id;  -- 好
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);  -- 差

配置优化

InnoDB配置优化:

[mysqld]
# InnoDB缓冲池大小（建议为内存的70-80%）
innodb_buffer_pool_size = 4G

# InnoDB日志文件大小
innodb_log_file_size = 512M

# InnoDB刷新方法
innodb_flush_method = O_DIRECT

# InnoDB并发线程数
innodb_thread_concurrency = 0

# 连接数
max_connections = 500

# 查询缓存（MySQL 8.0已移除）
# query_cache_size = 64M
# query_cache_type = 1

MyISAM配置优化:

[mysqld]
# Key Buffer大小
key_buffer_size = 256M

# 表缓存
table_open_cache = 2000

监控和诊断

性能监控:

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看慢查询
SELECT * FROM mysql.slow_log;

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

性能分析工具:

pt-query-digest: 分析慢查询日志
mysqltuner: MySQL配置建议
pt-mysql-summary: MySQL系统摘要
Performance Schema: MySQL性能监控

3. 数据库架构设计

3.1 数据库设计原则

范式设计:

第一范式（1NF）: 每个字段都是原子值
第二范式（2NF）: 消除部分函数依赖
第三范式（3NF）: 消除传递函数依赖

反范式设计:

适当冗余提高查询性能
根据业务场景权衡

3.2 分库分表策略

垂直分库:

按业务模块分库
减少单库压力

水平分表:

按数据量分表
提高查询性能

分片策略:

范围分片
哈希分片
目录分片

3.3 读写分离架构

架构设计:

应用层
  ↓
读写分离中间件（Mycat/ProxySQL）
  ↓
Master (写) ←→ Slave1 (读)
         ↓
      Slave2 (读)

实现方式:

应用层实现
中间件实现
数据库代理

4. 数据库最佳实践

4.1 开发规范

命名规范:

表名：小写，下划线分隔
字段名：小写，下划线分隔
索引名：idx_表名_字段名

设计规范:

每个表必须有主键
字段使用合适的数据类型
避免使用NULL值
添加必要的索引

4.2 运维规范

备份规范:

定期全量备份
定期增量备份
备份验证
异地备份

监控规范:

性能监控
容量监控
错误监控
告警机制

4.3 安全规范

访问控制:

最小权限原则
定期审查权限
使用强密码
启用SSL连接

数据安全:

数据加密
敏感数据脱敏
审计日志
安全加固

实战优化

第424集数据库基础介绍

数据库基础介绍

1. 数据库概述

1.1 数据库定义

1.2 数据的存储方式

1. 人工管理阶段

2. 文件系统阶段

3. 数据库系统阶段

1.3 数据库技术构成

1. 数据库系统 (DataBase System, DBS)

2. 数据库管理系统（DataBase Management System, DBMS）

1.4 数据库类型划分

关系型数据库 (RDS - Relational Database System)

非关系型数据库 (NoSQL)

数据库选型建议

1.5 SQL语言（结构化查询语言）

SQL语言分类

1.6 数据访问方式

ODBC（Open Database Connectivity）

JDBC（Java Database Connectivity）

其他数据访问方式

2. 数据库运维

2.1 安装部署

YUM安装

二进制安装

源码编译安装

2.2 基础操作

增删查改（CRUD）

单表查询

多表查询

视图（Views）

触发器（Triggers）

存储过程（Stored Procedures）

2.3 权限管理

用户管理

权限授予

权限撤销

查看权限

安全机制

2.4 日志管理

访问日志

错误日志

Binlog（二进制日志）

日志轮转

2.5 备份恢复

逻辑备份

物理备份

备份策略

2.6 HA架构（高可用架构）

主从复制（Master-Slave Replication）

MHA（Master High Availability）

Mycat（数据库中间件）

2.7 性能优化

索引优化

查询优化

配置优化

监控和诊断

3. 数据库架构设计

3.1 数据库设计原则

3.2 分库分表策略

3.3 读写分离架构

4. 数据库最佳实践

4.1 开发规范

4.2 运维规范

4.3 安全规范