软件设计架构 · 完整教程 · 详细说明
📖 全面指南 · 从入门到精通
📐 一、MariaDB 软件设计架构
1.1 总体架构概述
核心
MariaDB 采用经典的客户端-服务器（Client-Server）架构模型，整体设计遵循分层解耦的原则。其核心架构可分为以下几个关键层次：
┌─────────────────────────────────────────────────────────────────┐ │ 客户端层 (Client Layer) │ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │ │ 应用程序 │ │ 管理工具 │ │ 连接器 │ │ ORM框架 │ │ │ │ (Java等) │ │ (CLI/GUI)│ │(Connector)│ │(Hibernate)│ │ │ └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘ │ ├─────────┼──────────────┼──────────────┼──────────────┼────────────┤ │ └──────────────┴──────────────┴──────────────┘ │ │ 连接与协议层 (Connection & Protocol) │ │ ┌─────────────────────────────────────────────┐ │ │ │ MySQL Protocol / TCP/IP / Unix Socket / SSL │ │ │ └─────────────────────────────────────────────┘ │ ├───────────────────────────────────────────────────────────────────┤ │ SQL 接口层 (SQL Interface Layer) │ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │ │ 解析器 │ │ 预处理器 │ │ 优化器 │ │ 执行器 │ │ │ │(Parser) │ │(PreProc) │ │(Optimizer)│ │(Executor)│ │ │ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │ ├───────────────────────────────────────────────────────────────────┤ │ 存储引擎层 (Storage Engine Layer) │ │ ┌───────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────────┐ │ │ │InnoDB │ │Aria │ │MyISAM│ │Memory│ │Column│ │ Archive │ │ │ │ │ │ │ │ │ │ │ │ Store│ │ │ │ │ └───────┘ └──────┘ └──────┘ └──────┘ └──────┘ └──────────┘ │ ├───────────────────────────────────────────────────────────────────┤ │ 文件系统与缓存层 (File System & Cache) │ │ ┌────────────────┐ ┌──────────────┐ ┌──────────────────┐ │ │ │ Buffer Pool │ │ Query Cache │ │ Binary Log Files │ │ │ │ Redo/Undo Log │ │ Key Cache │ │ Data/Index Files │ │ │ └────────────────┘ └──────────────┘ └──────────────────┘ │ └───────────────────────────────────────────────────────────────────┘
核心设计理念：
- 插件化架构：存储引擎、认证方式、审计模块等均支持插件机制，可灵活扩展
- 多线程模型：每个客户端连接由独立线程处理，支持高并发
- ACID 事务支持：通过 InnoDB/Aria 存储引擎实现完整的事务保证
- MVCC 机制：多版本并发控制，提升读写并发性能
- 开放标准兼容：兼容 SQL 标准，保持与 MySQL 高度兼容
1.2 连接处理与线程管理架构
核心
MariaDB 使用一线程一连接模型来处理客户端请求。当客户端发起连接时，服务器的连接管理器会为其分配一个专用线程。
连接生命周期：
- TCP 握手
- 认证验证
- 权限检查
- 线程分配
- 查询处理
- 结果返回
- 连接关闭/缓存
- 谓词下推（Predicate Pushdown）：将 WHERE 条件尽量推到表扫描之前
- 常量传播（Constant Propagation）：利用已知等式推导新的过滤条件
- JOIN 顺序优化：评估所有可能的 JOIN 顺序，选择代价最小的
- 索引选择：基于统计信息选择最优索引
- 子查询物化/改写：将子查询转换为 JOIN 或物化临时表
- 分区裁剪（Partition Pruning）：排除不相关的分区
- LRU 算法：使用改进的 LRU（最近最少使用）算法管理页面，将列表分为 Young 和 Old 区域
- 预读（Read-Ahead）：线性预读和随机预读机制，提前加载可能需要的页面
- Page 大小：默认 16KB（可配置为 4KB-64KB），每次 IO 操作一个页面
- 事务修改数据时，先写入 Redo Log Buffer
- Buffer 定期或按条件刷新到磁盘上的 Redo Log 文件
- 数据页面的修改在 Buffer Pool 中进行（脏页）
- Checkpoint 机制定期将脏页写入磁盘数据文件
- 崩溃恢复时，通过 Redo Log 重放未写入数据文件的修改
- 同步复制：事务在所有节点上同时提交，保证零数据丢失
- 多主模式：所有节点可读写，无主从之分
- 自动成员管理：节点故障自动剔除，新节点自动加入
- 证书复制（Certification）：通过全局排序和冲突检测保证一致性
- 节点状态转移（SST/IST）：全量同步(SST)或增量同步(IST)
- 金额字段：始终使用
DECIMAL(10,2)或DECIMAL(12,2)，绝不用 FLOAT/DOUBLE - 主键：推荐使用
BIGINT UNSIGNED AUTO_INCREMENT或雪花算法生成的 BIGINT - 布尔值：使用
TINYINT(1)或BOOLEAN（MariaDB 中等价） - 时区敏感的时间：使用
DATETIME（TIMESTAMP 有 2038 年问题和自动时区转换） - 状态字段：优先使用
ENUM（存储高效且有约束），或TINYINT+ 应用层映射 - IP 地址：使用
INT UNSIGNED+INET_ATON()/INET_NTOA()函数 - 共享锁（S Lock）：读锁，多个事务可同时持有，允许并发读
- 排他锁（X Lock）：写锁，独占访问，阻止其他锁
- 意向锁（IS/IX）：表级锁，表明事务打算在行上加 S/X 锁
- 间隙锁（Gap Lock）：锁定索引间隙，防止幻读（RR级别下）
- 临键锁（Next-Key Lock）：行锁 + 间隙锁的组合
- 插入意向锁（Insert Intention Lock）：特殊的间隙锁，允许不同事务向同一间隙插入不同行
- 以固定顺序访问表和行
- 尽量使用索引条件，避免表锁
- 保持事务简短，尽快提交
- 合理设置
- 避免大事务中包含用户交互
- 日志表、流水表：使用 RANGE 按时间分区
- 多租户数据：使用 LIST 按租户ID分区
- 均匀分布需求：使用 HASH 分区
- 分区键必须包含在主键/唯一键中
- 跨分区查询性能可能下降，避免频繁跨分区操作
- 遵循 3-2-1 规则：3份备份，2种介质，1份异地
- 定期验证备份可恢复性（实际还原测试）
- 生产环境使用 mariabackup 做热备
- 结合二进制日志做时间点恢复（PITR）
- 备份文件加密存储，控制访问权限
- 绝不使用 root 账户连接应用程序
- 应用程序使用最小权限用户
- 定期轮换密码（90天内）
- 开启审计日志追踪敏感操作
- 所有外部连接必须使用 SSL/TLS 加密
- 使用防火墙限制数据库端口访问（仅允许应用服务器IP）
- 核心业务推荐 Galera Cluster（3节点+负载均衡器）
- 读写分离场景使用 MaxScale 作为数据库代理
- 设置合理的超时和重试机制
- 定期演练故障切换流程
- 监控延迟并设置告警阈值
- 使用 VIP 或 DNS 实现应用端无缝切换
- 三范式（3NF）为基础：消除数据冗余，必要时适当反范式化
- 必备字段：每张表应有 id（主键）、created_at、updated_at
- 逻辑删除：使用 is_deleted 或 status 字段替代物理删除
- 避免NULL：使用 NOT NULL + DEFAULT 值，减少索引复杂度
- 注释规范：每张表、每个重要字段都要有 COMMENT
- 避免大表：单表超过500万行考虑分表或归档
- 避免大字段：BLOB/TEXT 单独拆分到扩展表
- 关联不超过3张表：复杂JOIN影响性能和可维护性
- Prometheus + Grafana：使用 mysqld_exporter 采集指标，Grafana 可视化
- Percona Monitoring and Management (PMM)：专业的 MySQL/MariaDB 监控平台
- Zabbix：企业级监控，支持自定义模板
- 慢查询分析工具：pt-query-digest, mysqldumpslow, Anemometer
- 确认源 MySQL 版本（MariaDB 兼容 MySQL 5.5-8.0 大部分特性）
- 导出 MySQL 数据（mysqldump 或 mariabackup）
- 安装 MariaDB
- 导入数据
- 运行 mysql_upgrade
- 测试应用兼容性
- 切换连接字符串
- MariaDB 10.4+ 不再支持 MySQL 8.0 的某些新特性（如部分 JSON 函数）
- 认证插件可能不同，需要检查用户认证方式
- 大版本升级前务必在测试环境验证
- 保留原始数据文件至少 7 天
→
→
→
→
→
→
线程缓存机制（Thread Cache）：
为避免频繁创建和销毁线程的开销，MariaDB 实现了线程缓存。当连接关闭时，线程不会立即销毁，而是放入缓存池中供后续连接复用。
-- 查看线程缓存相关参数 SHOW VARIABLES LIKE 'thread_cache_size'; SHOW STATUS LIKE 'Threads_created'; SHOW STATUS LIKE 'Threads_cached'; SHOW STATUS LIKE 'Threads_connected'; -- 计算缓存命中率 -- 命中率 = Threads_cached / (Threads_created + Threads_cached) * 100 -- 理想命中率应 > 95%
💡 提示
在高并发场景下，建议配置连接池（如 HikariCP、Druid）来管理客户端侧的连接，避免服务端线程资源耗尽。
线程池插件（Thread Pool）：
MariaDB 提供线程池插件，可以替代默认的一线程一连接模型。线程池将工作线程数固定为较小值，通过调度方式处理大量连接，特别适合高并发短查询场景。
| 模型 | 适用场景 | 最大连接数 | 内存消耗 |
|---|---|---|---|
| 一线程一连接 | 中等并发（<500） | 受限于系统资源 | 较高（每连接8-10MB） |
| 线程池 | 高并发（>1000） | 理论上不限 | 较低（固定线程） |
1.3 SQL 查询处理管道详解
核心
一条 SQL 查询从客户端到达服务器后，会经历多个阶段的处理，这就是查询处理管道（Query Processing Pipeline）。
完整的查询处理流程：
客户端发送SQL │ ▼ ┌─────────────┐ │ 语法解析 │ ← 词法分析 + 语法分析，生成解析树(AST) │ (Parser) │ └──────┬──────┘ │ ▼ ┌─────────────┐ │ 语义检查 │ ← 验证表/列是否存在、权限检查 │ (Validation) │ └──────┬──────┘ │ ▼ ┌─────────────┐ │ 查询重写 │ ← 视图展开、子查询转换 │ (Rewrite) │ └──────┬──────┘ │ ▼ ┌─────────────┐ │ 查询优化 │ ← 选择最优执行计划（基于代价模型CBO） │ (Optimizer) │ └──────┬──────┘ │ ▼ ┌─────────────┐ │ 执行引擎 │ ← 调用存储引擎API执行查询 │ (Executor) │ └──────┬──────┘ │ ▼ ┌─────────────┐ │ 结果返回 │ ← 结果集序列化发送回客户端 │ (Return) │ └─────────────┘
各阶段详解：
语法解析阶段 (Parser)
解析器将 SQL 文本转换为抽象语法树（AST）。词法分析器（Lexer）将输入分解为 Token（关键词、标识符、运算符等），语法分析器根据 SQL 语法规则构建语法树。
-- 示例：SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active' GROUP BY u.name HAVING COUNT(o.id) > 5; -- 解析器生成的AST结构（简化）： SELECT ├── 列列表: [u.name, COUNT(o.id)] ├── FROM: JOIN(users AS u, orders AS o, ON: u.id = o.user_id) ├── WHERE: u.status = 'active' ├── GROUP BY: [u.name] └── HAVING: COUNT(o.id) > 5
查询优化阶段 (Optimizer)
优化器是 MariaDB 最核心的组件之一，它采用基于代价（Cost-Based Optimization）的模型来选择最优执行计划。
优化器执行的转换包括：
-- 使用 EXPLAIN 查看优化器的执行计划 EXPLAIN SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_at > '2024-01-01' GROUP BY u.name; -- 使用 EXPLAIN FORMAT=JSON 获取更详细的代价信息 EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE order_date > '2024-06-01';
1.4 存储引擎架构与插件机制
核心
MariaDB 的存储引擎架构是其最优秀的设计之一。服务器层通过统一的 Handler API 与底层存储引擎交互，实现了存储引擎的可插拔性。
Handler API 核心接口：
| 方法 | 功能 | 说明 |
|---|---|---|
create() | 创建表 | 分配文件/内存空间 |
open() | 打开表 | 初始化内部结构 |
rnd_init() | 全表扫描初始化 | 准备顺序读取 |
rnd_next() | 读取下一行 | 全表扫描使用 |
index_init() | 索引扫描初始化 | 使用索引读取 |
index_read() | 索引查找 | 定位索引位置 |
write_row() | 插入行 | 写入数据 |
update_row() | 更新行 | 修改现有数据 |
delete_row() | 删除行 | 标记/物理删除 |
close() | 关闭表 | 释放资源 |
MariaDB 内置存储引擎对比：
InnoDB
默认事务引擎，支持 ACID、行级锁、外键、MVCC、崩溃恢复。适合 OLTP 工作负载。
Aria
MariaDB 独有引擎，MyISAM 的增强版，支持崩溃恢复。用于内部临时表和系统表。
MyISAM
传统非事务引擎，表级锁，适合读密集型场景。在 MariaDB 中正逐步被 Aria 替代。
Memory (Heap)
数据存储在内存中，访问极快。适合临时表、会话数据。重启后数据丢失。
ColumnStore
列式存储引擎，适合 OLAP、大数据分析。支持向量化执行。
Archive
高压缩比存储，只支持 INSERT 和 SELECT。适合历史数据归档。
存储引擎管理命令：
-- 查看所有可用存储引擎 SHOW ENGINES; -- 查看表使用的存储引擎 SHOW TABLE STATUS WHERE Name = 'users'; -- 修改表的存储引擎 ALTER TABLE orders ENGINE = InnoDB; -- 安装/卸载存储引擎插件 INSTALL PLUGIN rocksdb SONAME 'ha_rocksdb.so'; UNINSTALL PLUGIN rocksdb; -- 设置默认存储引擎 SET default_storage_engine = InnoDB;
1.5 InnoDB 存储引擎内部架构详解
高级
InnoDB 是 MariaDB 最重要的事务型存储引擎，其架构设计精巧且高度优化。以下是其核心组件详解：
InnoDB 内存架构：
┌─────────────────────────────────────────────────────────┐ │ InnoDB 内存区域 │ │ │ │ ┌─────────────────────────────────────────────────┐ │ │ │ Buffer Pool (默认128MB-几十GB) │ │ │ │ ┌───────────────┐ ┌────────────────────┐ │ │ │ │ │ 数据页缓存 │ │ 索引页缓存 │ │ │ │ │ │ (Data Pages) │ │ (Index Pages) │ │ │ │ │ └───────────────┘ └────────────────────┘ │ │ │ │ ┌───────────────┐ ┌────────────────────┐ │ │ │ │ │ Change Buffer │ │ Adaptive Hash Index│ │ │ │ │ │ (插入缓冲区) │ │ (自适应哈希索引) │ │ │ │ │ └───────────────┘ └────────────────────┘ │ │ │ └─────────────────────────────────────────────────┘ │ │ │ │ ┌───────────────┐ ┌────────────────────────────┐ │ │ │ Log Buffer │ │ Doublewrite Buffer │ │ │ │ (日志缓冲区) │ │ (双写缓冲区) │ │ │ └───────────────┘ └────────────────────────────┘ │ └─────────────────────────────────────────────────────────┘
Buffer Pool 工作机制：
InnoDB 磁盘架构：
| 文件类型 | 用途 | 关键说明 |
|---|---|---|
| .ibd 文件 | 表空间（数据+索引） | 每个表独立文件（innodb_file_per_table=ON） |
| ibdata1 | 系统表空间 | 包含变更缓冲、双写缓冲、UNDO日志等 |
| ib_logfile0/1 | 重做日志（Redo Log） | 循环写入，用于崩溃恢复 |
| *.TRG | 触发器文件 | 触发器定义存储 |
WAL 机制（Write-Ahead Logging）：
InnoDB 使用 WAL 协议确保事务持久性（Durability）：
-- InnoDB 关键配置参数 [mysqld] # Buffer Pool 配置（建议设为物理内存的60-80%） innodb_buffer_pool_size = 8G innodb_buffer_pool_instances = 8 # 多实例减少锁竞争 # Redo Log 配置 innodb_log_file_size = 512M # 每个日志文件大小 innodb_log_files_in_group = 2 # 日志文件组数量 innodb_log_buffer_size = 16M # 日志缓冲区 # IO 与刷盘策略 innodb_flush_method = O_DIRECT # 绕过OS缓存直接写入 innodb_flush_log_at_trx_commit = 1 # 1=每次提交刷盘（最安全） innodb_io_capacity = 2000 # SSD建议2000+ innodb_io_capacity_max = 4000 # 并发控制 innodb_thread_concurrency = 0 # 0=不限制 innodb_read_io_threads = 8 innodb_write_io_threads = 8
1.6 Galera Cluster 多主集群架构
高级
Galera Cluster 是 MariaDB 官方推荐的同步多主复制方案，提供真正的同步复制、自动成员管理、读写任意节点的高可用能力。
Galera 架构原理：
┌──────────────────────────────────────────────────────────────┐ │ Galera Cluster │ │ │ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │ │ Node 1 │ │ Node 2 │ │ Node 3 │ │ │ │ (Primary) │◄──►│ (Primary) │◄──►│ (Primary) │ │ │ │ │ │ │ │ │ │ │ │ ┌─────────┐ │ │ ┌─────────┐ │ │ ┌─────────┐ │ │ │ │ │MariaDB │ │ │ │MariaDB │ │ │ │MariaDB │ │ │ │ │ │+wsrep │ │ │ │+wsrep │ │ │ │+wsrep │ │ │ │ │ └─────────┘ │ │ └─────────┘ │ │ └─────────┘ │ │ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │ │ │ │ │ │ └──────────────────┼──────────────────┘ │ │ │ │ │ ┌────────▼────────┐ │ │ │ Galera Plugin │ │ │ │ (wsrep API) │ │ │ └────────┬────────┘ │ │ │ │ │ ┌────────▼────────┐ │ │ │ Group Comm. │ │ │ │ (gcomm backend) │ │ │ └─────────────────┘ │ └──────────────────────────────────────────────────────────────┘
Galera 核心特性：
⚠️ 注意事项
Galera 不支持 MyISAM 表；DDL 操作是全集群阻塞的；建议最少 3 个节点以避免脑裂（Split-brain）；大事务（>1000行变更）会导致性能下降。
# Galera Cluster 配置示例 (/etc/mysql/mariadb.conf.d/galera.cnf) [mysqld] # Galera 基本配置 wsrep_on = ON wsrep_provider = /usr/lib/galera/libgalera_smm.so wsrep_cluster_address = "gcomm://192.168.1.10,192.168.1.11,192.168.1.12" wsrep_cluster_name = "my_galera_cluster" # 节点标识 wsrep_node_address = "192.168.1.10" wsrep_node_name = "node1" # SST 方法（推荐使用 mariabackup） wsrep_sst_method = mariabackup wsrep_sst_auth = "sstuser:sstpassword" # 必须的 InnoDB 配置 binlog_format = ROW default_storage_engine = InnoDB innodb_autoinc_lock_mode = 2
📝 二、MariaDB 基础教程
2.1 安装部署与环境配置
实操
Linux (Ubuntu/Debian) 安装：
# 更新包索引 sudo apt update # 安装 MariaDB Server 和 Client sudo apt install mariadb-server mariadb-client -y # 启动并设置开机自启 sudo systemctl start mariadb sudo systemctl enable mariadb # 运行安全配置向导 sudo mysql_secure_installation # 查看运行状态 sudo systemctl status mariadb
Linux (CentOS/RHEL/Rocky) 安装：
# 安装 sudo dnf install mariadb-server mariadb -y # 启动服务 sudo systemctl start mariadb sudo systemctl enable mariadb # 安全配置 sudo mysql_secure_installation
Docker 部署：
# 拉取官方镜像 docker pull mariadb:latest # 快速启动（使用 docker-compose） cat > docker-compose.yml << 'EOF' version: '3.8' services: mariadb: image: mariadb:11.4 container_name: mariadb restart: always environment: MARIADB_ROOT_PASSWORD: your_root_password MARIADB_DATABASE: myapp MARIADB_USER: appuser MARIADB_PASSWORD: apppassword ports: - "3306:3306" volumes: - mariadb_data:/var/lib/mysql - ./init-scripts:/docker-entrypoint-initdb.d command: > --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --innodb-buffer-pool-size=256M volumes: mariadb_data: EOF # 启动 docker compose up -d
核心配置文件结构：
# 主要配置文件位置： # /etc/mysql/mariadb.conf.d/50-server.cnf (Ubuntu) # /etc/my.cnf.d/server.cnf (CentOS) [mysqld] # ===== 基础配置 ===== bind-address = 0.0.0.0 # 监听所有网卡 port = 3306 # 默认端口 datadir = /var/lib/mysql # 数据目录 socket = /var/run/mysqld/mysqld.sock # ===== 字符集配置 ===== character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci # ===== 连接数配置 ===== max_connections = 200 max_connect_errors = 100000 wait_timeout = 600 interactive_timeout = 1800 # ===== 日志配置 ===== log_error = /var/log/mysql/error.log slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2 log_queries_not_using_indexes = 1 # ===== 通用日志（仅调试时开启）===== # general_log = 1 # general_log_file = /var/log/mysql/general.log
💡 Windows 安装提示
Windows 用户可从
下载 MSI 安装包，或使用 ZIP 包手动安装。建议使用 MariaDB 官方提供的 HeidiSQL 或 DBeaver 作为管理工具。
2.2 数据库与表的 CRUD 操作
实操
数据库管理：
-- 查看所有数据库 SHOW DATABASES; -- 创建数据库（指定字符集） CREATE DATABASE ecommerce CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 选择/切换数据库 USE ecommerce; -- 查看当前数据库 SELECT DATABASE(); -- 删除数据库（危险！） DROP DATABASE IF EXISTS test_db;
表结构设计示例：
-- 创建用户表 CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键', username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱', password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希', phone VARCHAR(20) DEFAULT NULL COMMENT '手机号', status ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '状态', avatar_url VARCHAR(500) DEFAULT NULL COMMENT '头像URL', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', -- 索引定义 INDEX idx_status (status), INDEX idx_created_at (created_at), INDEX idx_email_status (email, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户信息表'; -- 创建商品表 CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, stock INT UNSIGNED DEFAULT 0, category_id BIGINT UNSIGNED, sku VARCHAR(50) UNIQUE NOT NULL, weight DECIMAL(8,3) DEFAULT NULL COMMENT '重量(kg)', is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_category (category_id), INDEX idx_price (price), INDEX idx_sku (sku), FULLTEXT INDEX ft_name_desc (name, description) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品信息表'; -- 创建订单表（含外键约束） CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单编号', user_id BIGINT UNSIGNED NOT NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending', shipping_address JSON COMMENT '收货地址(JSON)', paid_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 外键约束 CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE, INDEX idx_user_id (user_id), INDEX idx_order_no (order_no), INDEX idx_status (status), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单主表';
数据插入操作：
-- 单行插入 INSERT INTO users (username, email, password_hash, phone, status) VALUES ('zhangsan', 'zhang@example.com', SHA2('password123', 256), '13800138000', 'active'); -- 批量插入 INSERT INTO users (username, email, password_hash, status) VALUES ('lisi', 'li@example.com', SHA2('pass456', 256), 'active'), ('wangwu', 'wang@example.com', SHA2('pass789', 256), 'active'), ('zhaoliu', 'zhao@example.com', SHA2('pass000', 256), 'inactive'); -- INSERT ... ON DUPLICATE KEY UPDATE（UPSERT） INSERT INTO users (username, email, password_hash, phone) VALUES ('zhangsan', 'zhang_new@example.com', SHA2('newpass', 256), '13900139000') ON DUPLICATE KEY UPDATE email = VALUES(email), phone = VALUES(phone), updated_at = CURRENT_TIMESTAMP; -- INSERT ... SELECT（从查询结果插入） INSERT INTO users_archive (username, email, archived_at) SELECT username, email, NOW() FROM users WHERE status = 'banned';
数据查询操作：
-- 基本查询 SELECT id, username, email, status FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 20 OFFSET 0; -- 条件查询（多种WHERE用法） SELECT * FROM products WHERE price BETWEEN 100 AND 500 AND category_id IN (1, 2, 3) AND name LIKE '%手机%' AND stock > 0 AND is_active = TRUE; -- 聚合查询 SELECT status, COUNT(*) AS user_count, COUNT(DISTINCT email) AS unique_emails FROM users GROUP BY status HAVING COUNT(*) > 10; -- 多表 JOIN 查询 SELECT o.order_no, o.total_amount, o.status, u.username, u.email, o.created_at FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.created_at >= '2024-01-01' AND o.status IN ('paid', 'shipped') ORDER BY o.created_at DESC LIMIT 50;
数据更新与删除：
-- 更新数据 UPDATE users SET status = 'inactive', updated_at = NOW() WHERE username = 'zhangsan'; -- 多表更新（使用JOIN） UPDATE orders o INNER JOIN users u ON o.user_id = u.id SET o.status = 'cancelled' WHERE u.status = 'banned' AND o.status = 'pending'; -- 安全删除（逻辑删除推荐） UPDATE users SET status = 'deleted' WHERE id = 123; -- 物理删除（谨慎使用） DELETE FROM users WHERE status = 'banned' AND created_at < '2023-01-01'; -- 批量删除（使用LIMIT） DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 10000;
2.3 索引设计与使用详解
核心
索引是数据库性能优化的核心手段。MariaDB 支持多种索引类型，理解其底层原理对设计高效查询至关重要。
索引类型一览：
| 索引类型 | 存储引擎 | 数据结构 | 适用场景 |
|---|---|---|---|
| B+Tree 索引 | InnoDB/Aria | B+树 | 等值查询、范围查询、排序 |
| Hash 索引 | Memory | 哈希表 | 仅等值查询 |
| 全文索引 (Fulltext) | InnoDB/Aria | 倒排索引 | 全文搜索 |
| 空间索引 (Spatial) | InnoDB/MyISAM | R-Tree | 地理空间数据 |
| 聚簇索引 | InnoDB | B+树(数据+索引一体) | 主键自动创建 |
| 覆盖索引 | 所有 | - | 查询列全在索引中 |
索引创建语法：
-- 创建普通索引 CREATE INDEX idx_users_email ON users(email); -- 创建唯一索引 CREATE UNIQUE INDEX idx_products_sku ON products(sku); -- 创建复合索引（遵循最左前缀原则） CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at); -- 创建前缀索引（节省空间，适用于长字符串） CREATE INDEX idx_users_email_prefix ON users(email(20)); -- 创建全文索引 ALTER TABLE products ADD FULLTEXT INDEX ft_search (name, description); -- 创建降序索引（MariaDB 10.8+ 支持，用于混合排序） CREATE INDEX idx_mixed_sort ON orders(user_id ASC, created_at DESC); -- 使用ALTER TABLE添加索引 ALTER TABLE users ADD INDEX idx_phone (phone); -- 删除索引 DROP INDEX idx_phone ON users;
最左前缀原则详解：
⚠️ 重要
对于复合索引 (A, B, C)，以下条件可以使用索引：
✅ WHERE A = 1
✅ WHERE A = 1 AND B = 2
✅ WHERE A = 1 AND B = 2 AND C = 3
✅ WHERE A = 1 AND B > 5
❌ WHERE B = 2（无法使用，缺少最左列 A）
❌ WHERE C = 3（无法使用）
⚡ WHERE A = 1 AND C = 3（仅使用 A 的部分）
EXPLAIN 分析执行计划：
EXPLAIN SELECT o.order_no, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.user_id = 123 AND o.status = 'paid' AND o.created_at > '2024-01-01'\G -- 重点关注字段： -- id: 查询标识 -- select_type: 查询类型（SIMPLE/PRIMARY/SUBQUERY等） -- table: 访问的表 -- type: 访问类型（system > const > eq_ref > ref > range > index > ALL） -- possible_keys: 可能使用的索引 -- key: 实际使用的索引 -- key_len: 使用的索引长度（字节） -- rows: 预估扫描行数 -- Extra: 额外信息（Using index/Using filesort/Using temporary等）
2.4 常用数据类型与最佳实践
实操
| 数据类型 | 占用空间 | 范围/说明 | 使用建议 |
|---|---|---|---|
| TINYINT | 1字节 | -128 ~ 127 (无符号 0~255) | 状态码、布尔值 |
| SMALLINT | 2字节 | -32768 ~ 32767 | 小范围整数 |
| INT | 4字节 | -21亿 ~ 21亿 | 一般主键/外键 |
| BIGINT | 8字节 | ±9.2×10^18 | 大表主键、雪花ID |
| DECIMAL(M,D) | 可变 | 精确小数 | 金额、价格 |
| FLOAT/DOUBLE | 4/8字节 | 近似浮点数 | 科学计算（非金融） |
| VARCHAR(N) | 可变 | 最大65535字节 | 短文本、名称 |
| TEXT | 可变 | 最大64KB | 文章内容、描述 |
| MEDIUMTEXT | 可变 | 最大16MB | 大文本存储 |
| DATETIME | 8字节 | 1000-01-01 ~ 9999-12-31 | 绝对时间 |
| TIMESTAMP | 4字节 | 1970 ~ 2038 | 记录修改时间 |
| JSON | 可变 | JSON文档 | 半结构化数据 |
| ENUM | 1-2字节 | 预定义枚举值 | 固定选项列表 |
| UUID | 16字节 | 128位唯一标识 | 分布式系统ID |
数据类型选择最佳实践：
-- JSON 类型操作示例（MariaDB 10.2+） CREATE TABLE user_preferences ( user_id BIGINT PRIMARY KEY, prefs JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO user_preferences VALUES (1, '{"theme": "dark", "language": "zh-CN", "notifications": {"email": true, "push": false}}'); -- JSON 查询 SELECT JSON_VALUE(prefs, '$.theme') AS theme, JSON_VALUE(prefs, '$.notifications.email') AS email_notify FROM user_preferences WHERE user_id = 1; -- JSON 更新 UPDATE user_preferences SET prefs = JSON_SET(prefs, '$.theme', 'light') WHERE user_id = 1; -- IP 地址存储 CREATE TABLE access_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, ip_addr INT UNSIGNED NOT NULL, -- 存储转换后的整数 user_agent VARCHAR(500), access_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO access_log (ip_addr) VALUES (INET_ATON('192.168.1.100')); SELECT INET_NTOA(ip_addr) FROM access_log;
🚀 三、高级特性与进阶教程
3.1 窗口函数（Window Functions）
高级
MariaDB 10.2+ 完整支持 SQL 窗口函数，可以在不改变结果集行数的情况下对数据进行聚合分析。
常用窗口函数：
-- 行号与排名 SELECT username, created_at, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num, RANK() OVER (ORDER BY total_orders DESC) AS ranking, DENSE_RANK() OVER (ORDER BY total_orders DESC) AS dense_ranking, NTILE(4) OVER (ORDER BY total_orders DESC) AS quartile FROM user_stats; -- 累计与移动窗口 SELECT order_date, daily_revenue, SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue, AVG(daily_revenue) OVER ( ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS moving_avg_7days, LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS prev_day, LEAD(daily_revenue, 1) OVER (ORDER BY order_date) AS next_day, FIRST_VALUE(daily_revenue) OVER (ORDER BY order_date) AS first_revenue, LAST_VALUE(daily_revenue) OVER ( ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_revenue FROM daily_sales; -- 分组排名（每组取 Top N） WITH ranked_products AS ( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_count DESC) AS rn FROM products p ) SELECT * FROM ranked_products WHERE rn <= 5; -- 每个类目 Top 5 -- 同比环比计算 SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_month, ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month)) / LAG(revenue, 1) OVER (ORDER BY month) * 100, 2) AS growth_rate_pct FROM monthly_revenue;
3.2 存储过程、函数与触发器
高级
存储过程示例：
DELIMITER // CREATE PROCEDURE sp_process_order( IN p_user_id BIGINT, IN p_product_id BIGINT, IN p_quantity INT, OUT p_order_id BIGINT, OUT p_result_code INT, OUT p_result_msg VARCHAR(200) ) BEGIN DECLARE v_stock INT; DECLARE v_price DECIMAL(10,2); DECLARE v_total DECIMAL(12,2); -- 异常处理 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_result_code = -1; SET p_result_msg = '订单处理异常，已回滚'; END; START TRANSACTION; -- 检查库存 SELECT stock, price INTO v_stock, v_price FROM products WHERE id = p_product_id FOR UPDATE; IF v_stock < p_quantity THEN SET p_result_code = -2; SET p_result_msg = CONCAT('库存不足，当前库存: ', v_stock); ROLLBACK; ELSE -- 扣减库存 UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id; -- 计算总价 SET v_total = v_price * p_quantity; -- 创建订单 INSERT INTO orders (order_no, user_id, total_amount, status) VALUES ( CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000), 4, '0')), p_user_id, v_total, 'pending' ); SET p_order_id = LAST_INSERT_ID(); SET p_result_code = 0; SET p_result_msg = '订单创建成功'; COMMIT; END IF; END // DELIMITER ; -- 调用存储过程 CALL sp_process_order(1, 100, 2, @order_id, @code, @msg); SELECT @order_id, @code, @msg;
自定义函数：
DELIMITER // CREATE FUNCTION fn_calculate_discount( original_price DECIMAL(10,2), user_level ENUM('bronze','silver','gold','platinum') ) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN DECLARE discount_rate DECIMAL(3,2); SET discount_rate = CASE user_level WHEN 'bronze' THEN 0.00 WHEN 'silver' THEN 0.05 WHEN 'gold' THEN 0.10 WHEN 'platinum' THEN 0.15 ELSE 0.00 END; RETURN ROUND(original_price * (1 - discount_rate), 2); END // DELIMITER ; -- 使用函数 SELECT product_name, price, fn_calculate_discount(price, 'gold') AS gold_price FROM products WHERE category_id = 1;
触发器：
-- 创建审计日志触发器 DELIMITER // CREATE TRIGGER trg_users_after_update AFTER UPDATE ON users FOR EACH ROW BEGIN INSERT INTO user_audit_log ( user_id, action, old_value, new_value, changed_by, changed_at ) VALUES ( NEW.id, 'UPDATE', JSON_OBJECT('status', OLD.status, 'email', OLD.email), JSON_OBJECT('status', NEW.status, 'email', NEW.email), CURRENT_USER(), NOW() ); END // DELIMITER ;
3.3 事务隔离级别与锁机制详解
高级
事务 ACID 属性：
原子性 (Atomicity)
事务要么全部成功，要么全部回滚。通过 Undo Log 实现回滚。
一致性 (Consistency)
事务前后数据保持一致状态。通过约束、触发器和锁机制保证。
隔离性 (Isolation)
并发事务互不干扰。通过 MVCC 和锁机制实现。
持久性 (Durability)
事务提交后数据永久保存。通过 Redo Log (WAL) 保证。
四种隔离级别对比：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 |
|---|---|---|---|---|
| READ UNCOMMITTED | ❌ 可能 | ❌ 可能 | ❌ 可能 | 最高 |
| READ COMMITTED | ✅ 避免 | ❌ 可能 | ❌ 可能 | 高 |
| REPEATABLE READ（默认） | ✅ 避免 | ✅ 避免 | ⚡ MVCC解决 | 中 |
| SERIALIZABLE | ✅ 避免 | ✅ 避免 | ✅ 避免 | 最低 |
InnoDB 锁类型：
-- 设置事务隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 显式锁定 SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 排他锁 SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE; -- 共享锁 -- 查看锁信息 SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS; SHOW ENGINE INNODB STATUS\G -- 事务控制 START TRANSACTION; -- 或 BEGIN; -- ... 执行SQL ... COMMIT; -- 或 ROLLBACK; -- 保存点 SAVEPOINT sp1; -- ... 执行SQL ... ROLLBACK TO SAVEPOINT sp1; RELEASE SAVEPOINT sp1;
🚨 死锁预防
避免死锁的最佳实践：
innodb_lock_wait_timeout
3.4 视图、CTE 与派生表
高级
视图（Views）：
-- 创建视图 CREATE OR REPLACE VIEW v_active_users AS SELECT id, username, email, created_at FROM users WHERE status = 'active' WITH CHECK OPTION; -- 可更新视图的条件： -- 不含聚合函数、DISTINCT、GROUP BY、HAVING、UNION、子查询等 -- 创建带聚合的视图（只读） CREATE VIEW v_user_order_stats AS SELECT u.id AS user_id, u.username, COUNT(o.id) AS order_count, COALESCE(SUM(o.total_amount), 0) AS total_spent, MAX(o.created_at) AS last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.username; -- 使用视图 SELECT * FROM v_user_order_stats WHERE order_count > 10;
CTE（公共表表达式）：
-- 基本 CTE WITH user_spending AS ( SELECT user_id, SUM(total_amount) AS total_spent, COUNT(*) AS order_count FROM orders WHERE status != 'cancelled' GROUP BY user_id ), vip_users AS ( SELECT user_id, 'VIP' AS tier FROM user_spending WHERE total_spent > 10000 UNION ALL SELECT user_id, 'Regular' FROM user_spending WHERE total_spent <= 10000 ) SELECT u.username, v.tier, us.total_spent, us.order_count FROM users u JOIN vip_users v ON u.id = v.user_id JOIN user_spending us ON u.id = us.user_id ORDER BY us.total_spent DESC; -- 递归 CTE（树形结构查询） WITH RECURSIVE category_tree AS ( -- 锚点成员（顶级分类） SELECT id, name, parent_id, 0 AS depth, CAST(name AS CHAR(500)) AS path FROM categories WHERE parent_id IS NULL UNION ALL -- 递归成员（子分类） SELECT c.id, c.name, c.parent_id, ct.depth + 1, CONCAT(ct.path, ' > ', c.name) FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id WHERE ct.depth < 5 -- 防止无限递归 ) SELECT * FROM category_tree ORDER BY path;
3.5 表分区策略与管理
高级
表分区将大表物理分割为多个小分区，每个分区是独立的存储单元。分区可以显著提升查询性能（分区裁剪）和管理效率。
支持的分区类型：
-- 1. RANGE 分区（按范围，最常用） CREATE TABLE orders_partitioned ( id BIGINT AUTO_INCREMENT, user_id BIGINT NOT NULL, order_date DATE NOT NULL, total_amount DECIMAL(12,2), status VARCHAR(20), PRIMARY KEY (id, order_date) -- 分区键必须是主键的一部分 ) ENGINE=InnoDB PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026), PARTITION pmax VALUES LESS THAN MAXVALUE ); -- 2. LIST 分区（按枚举值） CREATE TABLE logs_by_region ( id BIGINT AUTO_INCREMENT, region VARCHAR(20), message TEXT, created_at TIMESTAMP, PRIMARY KEY (id, region) ) PARTITION BY LIST COLUMNS(region) ( PARTITION p_asia VALUES IN ('CN', 'JP', 'KR', 'SG'), PARTITION p_europe VALUES IN ('UK', 'DE', 'FR', 'IT'), PARTITION p_america VALUES IN ('US', 'CA', 'BR'), PARTITION p_other VALUES IN (NULL) ); -- 3. HASH 分区（均匀分布） CREATE TABLE sessions ( id BIGINT, user_id BIGINT, data JSON, created_at TIMESTAMP ) PARTITION BY HASH(user_id) PARTITIONS 16; -- 分区管理 ALTER TABLE orders_partitioned ADD PARTITION ( PARTITION p2026 VALUES LESS THAN (2027) ); ALTER TABLE orders_partitioned DROP PARTITION p2022; ALTER TABLE orders_partitioned REORGANIZE PARTITION p2025 INTO ( PARTITION p2025h1 VALUES LESS THAN (2025 + INTERVAL 6 MONTH), PARTITION p2025h2 VALUES LESS THAN (2026) ); -- 查看分区信息 SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'orders_partitioned';
💡 分区选择建议
⚡ 四、性能优化实战
4.1 服务器参数调优指南
性能
关键性能参数一览：
# ===== 最佳性能配置模板（16GB内存服务器） ===== [mysqld] # ===== 连接配置 ===== max_connections = 500 max_connect_errors = 100000 thread_cache_size = 64 table_open_cache = 4000 table_definition_cache = 2000 # ===== InnoDB 核心（最关键！）===== innodb_buffer_pool_size = 12G # 物理内存的70-80% innodb_buffer_pool_instances = 12 # 每实例约1GB innodb_log_file_size = 1G # 足够大减少checkpoint频率 innodb_log_buffer_size = 64M innodb_flush_method = O_DIRECT # SSD必配 innodb_flush_log_at_trx_commit = 1 # 1=安全 2=性能(每秒刷盘) innodb_io_capacity = 4000 # SSD推荐 innodb_io_capacity_max = 8000 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_purge_threads = 4 innodb_page_cleaners = 12 innodb_lru_scan_depth = 4096 # ===== 查询缓存（注意：MariaDB 10.1.7+ 默认关闭）===== query_cache_type = 0 # 建议关闭，用应用层缓存替代 query_cache_size = 0 # ===== 临时表与排序 ===== tmp_table_size = 128M max_heap_table_size = 128M sort_buffer_size = 4M # 每连接分配，勿设太大 join_buffer_size = 4M read_buffer_size = 2M read_rnd_buffer_size = 4M # ===== 慢查询日志 ===== slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = 1 log_slow_admin_statements = 1 # ===== 二进制日志（如果做复制/备份）===== log_bin = /var/log/mysql/mariadb-bin binlog_format = ROW binlog_row_image = MINIMAL # 减少日志量 expire_logs_days = 7 sync_binlog = 1 # 1=每次事务同步（安全） # sync_binlog = 0 # 0=依赖OS（性能优先） # ===== 优化器配置 ===== optimizer_search_depth = 6 # JOIN优化搜索深度 optimizer_switch = 'index_merge=on,index_merge_union=on'
性能监控命令：
-- 服务器状态概览 SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES; -- 关键性能指标查询 -- 1. Buffer Pool 命中率（应该 > 99%） SELECT (1 - (variable_value / (SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') )) * 100 AS buffer_pool_hit_rate FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads'; -- 2. 线程缓存命中率 SELECT (1 - Threads_created / (Connections + 0.001)) * 100 AS thread_cache_hit_rate FROM ( SELECT MAX(CASE WHEN variable_name = 'Threads_created' THEN variable_value END) AS Threads_created, MAX(CASE WHEN variable_name = 'Connections' THEN variable_value END) AS Connections FROM information_schema.global_status WHERE variable_name IN ('Threads_created', 'Connections') ) t; -- 3. 临时表磁盘使用率 SHOW STATUS LIKE 'Created_tmp%'; -- 如果 Created_tmp_disk_tables / Created_tmp_tables > 25%，需要调大tmp_table_size -- 4. 表锁竞争 SHOW STATUS LIKE 'Table_locks%'; -- Table_locks_waited / Table_locks_immediate 应 < 0.001
4.2 慢查询分析与优化实战
性能
常见慢查询模式及优化方案：
❌ N+1 查询问题
-- ❌ 错误做法：循环中逐条查询 SELECT * FROM orders WHERE user_id = 1; SELECT * FROM orders WHERE user_id = 2; SELECT * FROM orders WHERE user_id = 3; -- ... 循环N次 -- ✅ 正确做法：使用 IN 查询或 JOIN SELECT o.*, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.user_id IN (1, 2, 3, 4, 5, ...); -- ✅ 或使用子查询 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');
❌ SELECT * 滥用
-- ❌ 拉取所有列（可能包含大文本/BLOB） SELECT * FROM articles WHERE category_id = 1; -- ✅ 只选择需要的列（利用覆盖索引） SELECT id, title, summary, created_at FROM articles WHERE category_id = 1; -- 如果只需要判断是否存在： SELECT EXISTS(SELECT 1 FROM articles WHERE category_id = 1);
❌ 分页性能问题（OFFSET过大）
-- ❌ OFFSET 很大时性能急剧下降 SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 1000000; -- ✅ 延迟关联（Deferred Join）优化 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 20 ) AS tmp ON o.id = tmp.id; -- ✅ 游标分页（推荐，性能最优） SELECT * FROM orders WHERE id > 1000000 -- 上一页最后一条的ID ORDER BY id LIMIT 20; -- ✅ 基于时间/条件过滤替代 OFFSET SELECT * FROM orders WHERE created_at < '2024-06-01 12:00:00' ORDER BY created_at DESC LIMIT 20;
❌ NOT IN / NOT EXISTS 性能问题
-- ❌ NOT IN 性能差（特别是子查询含NULL时结果错误） SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders); -- ✅ LEFT JOIN + IS NULL（推荐） SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.user_id IS NULL; -- ✅ NOT EXISTS SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id );
❌ 索引失效场景
-- 以下情况会导致索引失效： -- 1. 对索引列使用函数/表达式 SELECT * FROM users WHERE YEAR(created_at) = 2024; -- ✅ 改写为范围查询 SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'; -- 2. 隐式类型转换 SELECT * FROM users WHERE phone = 13800138000; -- phone是VARCHAR -- ✅ 保持类型一致 SELECT * FROM users WHERE phone = '13800138000'; -- 3. LIKE 左模糊 SELECT * FROM products WHERE name LIKE '%手机'; -- 全表扫描 -- ✅ 使用全文索引或反向匹配 -- 4. OR 条件（部分列无索引） SELECT * FROM users WHERE status = 'active' OR phone = '13800138000'; -- ✅ 如果 phone 无索引，改写为 UNION SELECT * FROM users WHERE status = 'active' UNION SELECT * FROM users WHERE phone = '13800138000'; -- 5. != 或 NOT 操作 SELECT * FROM users WHERE status != 'active'; -- 可能走全表扫描
4.3 备份策略与数据恢复
实操
MariaDB 备份工具对比：
| 工具 | 类型 | 热备 | 速度 | 增量备份 | 适用场景 |
|---|---|---|---|---|---|
| mariabackup | 物理备份 | ✅ | ⭐⭐⭐⭐⭐ | ✅ | 生产环境首选 |
| mysqldump | 逻辑备份 | ⚠️锁表 | ⭐⭐ | ❌ | 小数据库/迁移 |
| mysqlpump | 逻辑备份 | ⚠️ | ⭐⭐⭐ | ❌ | 并行mysqldump |
| LVM 快照 | 文件系统级 | ✅ | ⭐⭐⭐⭐⭐ | ❌ | 有LVM的环境 |
mariabackup 完整备份脚本：
#!/bin/bash # MariaDB 自动备份脚本 # 安装: sudo apt install mariadb-backup BACKUP_DIR="/backup/mariadb" DATE=$(date +%Y%m%d_%H%M%S) FULL_DIR="${BACKUP_DIR}/full_${DATE}" KEEP_DAYS=7 # 创建备份目录 mkdir -p ${FULL_DIR} # 执行全量备份（热备份，不锁表） mariabackup --backup \ --target-dir=${FULL_DIR} \ --user=backup_user \ --password='backup_password' \ --parallel=4 \ --compress \ --compress-threads=4 # 准备备份（应用日志，使其一致） mariabackup --prepare --target-dir=${FULL_DIR} # 压缩备份 tar -czf ${BACKUP_DIR}/full_${DATE}.tar.gz -C ${BACKUP_DIR} full_${DATE} rm -rf ${FULL_DIR} # 清理过期备份 find ${BACKUP_DIR} -name "full_*.tar.gz" -mtime +${KEEP_DAYS} -delete echo "[$(date)] Backup completed: full_${DATE}.tar.gz" >> /var/log/mariadb-backup.log
恢复流程：
# 1. 停止MariaDB服务 sudo systemctl stop mariadb # 2. 清理数据目录 sudo rm -rf /var/lib/mysql/* # 3. 恢复备份 sudo mariabackup --copy-back --target-dir=/backup/mariadb/full_20240101_020000 # 4. 修复权限 sudo chown -R mysql:mysql /var/lib/mysql # 5. 启动服务 sudo systemctl start mariadb
mysqldump 逻辑备份：
# 导出单个数据库 mysqldump -u root -p --single-transaction --routines --triggers \ --events ecommerce > ecommerce_$(date +%Y%m%d).sql # 导出所有数据库 mysqldump -u root -p --all-databases --single-transaction \ --routines --triggers --events > all_dbs_$(date +%Y%m%d).sql # 只导出表结构 mysqldump -u root -p --no-data ecommerce > schema_only.sql # 恢复 mysql -u root -p ecommerce < ecommerce_20240101.sql # 从备份中提取单表 mysqldump -u root -p ecommerce users > users_only.sql
⚠️ 备份最佳实践
🔒 五、安全管理与权限控制
5.1 用户权限管理与安全配置
安全
用户创建与权限授予：
-- 创建用户（指定主机限制） CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongP@ssw0rd!'; CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly123!'; CREATE USER 'admin'@'localhost' IDENTIFIED BY 'AdminSecure456!'; -- 授予权限 -- 只读权限 GRANT SELECT ON ecommerce.* TO 'readonly'@'%'; -- 应用用户权限（增删改查，不含DDL） GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* TO 'app_user'@'192.168.1.%'; -- 管理员权限 GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; -- 特定表权限 GRANT SELECT, INSERT ON ecommerce.orders TO 'app_user'@'192.168.1.%'; -- 存储过程执行权限 GRANT EXECUTE ON PROCEDURE ecommerce.sp_process_order TO 'app_user'@'192.168.1.%'; -- 刷新权限 FLUSH PRIVILEGES; -- 查看用户权限 SHOW GRANTS FOR 'app_user'@'192.168.1.%'; -- 回收权限 REVOKE INSERT, UPDATE ON ecommerce.* FROM 'readonly'@'%'; -- 删除用户 DROP USER 'readonly'@'%'; -- 修改密码 ALTER USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'NewSecureP@ss!';
安全加固配置：
# 安全加固配置 (/etc/mysql/mariadb.conf.d/50-server.cnf) [mysqld] # 禁止远程 root 登录 skip-name-resolve bind-address = 127.0.0.1 # 或只绑定内网IP # 禁用本地文件加载（防SQL注入读文件） local-infile = 0 # 禁用符号链接 symbolic-links = 0 # 密码强度策略（需要 simple_password_check 插件） # plugin-load-add = simple_password_check # 连接加密（SSL/TLS） ssl-ca = /etc/mysql/ssl/ca-cert.pem ssl-cert = /etc/mysql/ssl/server-cert.pem ssl-key = /etc/mysql/ssl/server-key.pem require-secure-transport = ON
角色管理（MariaDB 10.1+）：
-- 创建角色 CREATE ROLE 'read_only'; CREATE ROLE 'app_readwrite'; CREATE ROLE 'db_admin'; -- 给角色授权 GRANT SELECT ON ecommerce.* TO 'read_only'; GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* TO 'app_readwrite'; GRANT ALL ON ecommerce.* TO 'db_admin'; -- 将角色分配给用户 GRANT 'app_readwrite' TO 'app_user'@'192.168.1.%'; GRANT 'read_only' TO 'readonly'@'%'; -- 设置默认角色 SET DEFAULT ROLE 'app_readwrite' FOR 'app_user'@'192.168.1.%'; -- 激活角色 SET ROLE 'app_readwrite'; -- 查看角色 SELECT * FROM information_schema.APPLICABLE_ROLES;
🚨 安全红线
5.2 SQL 注入防护与审计日志
安全
SQL 注入防护策略：
| 防护手段 | 实现层级 | 说明 |
|---|---|---|
| 参数化查询 | 应用层 | 使用 Prepared Statement，最有效 |
| ORM 框架 | 应用层 | MyBatis/Hibernate 自动处理 |
| 输入验证 | 应用层 | 白名单校验，过滤特殊字符 |
| 最小权限 | 数据库层 | 应用账户不给 DROP/ALTER 权限 |
| WAF 防护 | 网络层 | Web应用防火墙拦截注入尝试 |
审计日志配置（MariaDB Audit Plugin）：
-- 安装审计插件 INSTALL PLUGIN server_audit SONAME 'server_audit.so'; -- 配置审计 SET GLOBAL server_audit_logging = ON; SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE'; SET GLOBAL server_audit_output_type = 'file'; SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log'; SET GLOBAL server_audit_file_rotate_size = 100000000; -- 100MB SET GLOBAL server_audit_file_rotations = 10; -- 排除特定用户（如备份用户） SET GLOBAL server_audit_excl_users = 'backup_user,monitoring'; -- 永久配置 -- 写入 /etc/mysql/mariadb.conf.d/50-server.cnf: -- [mysqld] -- plugin-load-add = server_audit -- server_audit_logging = ON -- server_audit_events = CONNECT,QUERY,TABLE,ALTER -- server_audit_output_type = FILE -- server_audit_file_path = /var/log/mysql/audit.log
# 查看审计日志 sudo tail -f /var/log/mysql/audit.log # 查找危险操作 sudo grep -i "DROP\|ALTER\|TRUNCATE" /var/log/mysql/audit.log # 查找失败登录 sudo grep ",CONNECT,," /var/log/mysql/audit.log | grep "FAILED"
🔄 六、主从复制与高可用集群
6.1 主从复制配置详解
实操
复制架构类型：
异步复制
主库写入后不等从库确认即返回，延迟可能较大。性能最好。
半同步复制
主库等待至少一个从库确认收到 Binlog 后才提交。兼顾安全和性能。
全同步复制
所有从库确认后才提交（Galera 模式）。最安全但延迟最高。
主库配置（Master）：
# Master: /etc/mysql/mariadb.conf.d/50-server.cnf [mysqld] server-id = 1 # 唯一ID（1-4294967295） log-bin = /var/log/mysql/mariadb-bin # 开启二进制日志 binlog-format = ROW # 行格式（推荐） binlog-row-image = FULL # FULL/MINIMAL/NOBLOB sync-binlog = 1 # 每次事务同步到磁盘 expire-logs-days = 7 # 自动清理7天前的binlog # GTID（全局事务标识符，推荐开启） gtid-strict-mode = 1 log-slave-updates = 1 # 从库也记录binlog（级联复制需要） # 过滤（可选） # binlog-do-db = ecommerce # 只复制指定库 # binlog-ignore-db = mysql # 忽略指定库
从库配置（Slave）：
# Slave: /etc/mysql/mariadb.conf.d/50-server.cnf [mysqld] server-id = 2 # 必须不同于主库 relay-log = /var/log/mysql/relay-bin # 中继日志 read-only = 1 # 从库只读（应用不应写从库） # super-read-only = 1 # 连super用户也只读 # GTID gtid-strict-mode = 1 # 并行复制（MariaDB 10.0+） slave-parallel-type = optimistic # 或 conservative slave-parallel-threads = 4 # 并行复制线程数 slave-parallel-mode = optimistic # 乐观模式
配置复制连接：
-- 主库：创建复制用户 CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPassword123!'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; -- 主库：查看当前 Binlog 位置 SHOW MASTER STATUS; -- 输出：File: mariadb-bin.000001, Position: 642 -- 从库：配置主库连接（传统方式） CHANGE MASTER TO MASTER_HOST = '192.168.1.10', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'ReplPassword123!', MASTER_LOG_FILE = 'mariadb-bin.000001', MASTER_LOG_POS = 642; -- 从库：使用 GTID 方式（推荐） CHANGE MASTER TO MASTER_HOST = '192.168.1.10', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'ReplPassword123!', MASTER_USE_GTID = slave_pos; -- 启动复制 START SLAVE; -- 查看复制状态 SHOW SLAVE STATUS\G -- 关注：Slave_IO_Running = Yes, Slave_SQL_Running = Yes -- Seconds_Behind_Master = 0（无延迟）
✅ 复制监控
日常需要监控的关键指标：
• Seconds_Behind_Master：复制延迟秒数
• Slave_IO_Running / Slave_SQL_Running：两个线程状态
• Last_Error / Last_SQL_Error：错误信息
• Relay_Log_Space：中继日志磁盘占用
6.2 高可用方案与故障切换
高级
常见高可用方案对比：
| 方案 | 架构 | RPO | RTO | 复杂度 | 适用场景 |
|---|---|---|---|---|---|
| Galera Cluster | 多主同步 | 0（零丢失） | < 10秒 | 中 | 金融、电商 |
| MaxScale Proxy | 读写分离+故障转移 | 取决于复制 | 10-30秒 | 中 | 通用场景 |
| Orchestrator | 自动故障转移 | 可能丢少量 | 30-60秒 | 低 | 传统主从 |
| MHA | 故障切换 | 少量丢失 | 30秒 | 低 | 传统方案 |
| Pacemaker+DRBD | 共享存储 | 0 | 30-60秒 | 高 | 特殊场景 |
MaxScale 读写分离配置：
# /etc/maxscale.cnf [maxscale] threads = auto # 服务器定义 [server1] type = server address = 192.168.1.10 port = 3306 protocol = MariaDBBackend [server2] type = server address = 192.168.1.11 port = 3306 protocol = MariaDBBackend [server3] type = server address = 192.168.1.12 port = 3306 protocol = MariaDBBackend # 监控器 [monitor] type = monitor module = mariadbmon servers = server1, server2, server3 user = maxscale_monitor password = MonitorP@ss monitor_interval = 2000ms # 路由（读写分离） [rwsplit-service] type = service router = readwritesplit servers = server1, server2, server3 user = maxscale_router password = RouterP@ss # 监听端口 [rwsplit-listener] type = listener service = rwsplit-service protocol = MariaDBClient port = 3306
💡 高可用最佳实践
💡 七、最佳实践与规范
7.1 数据库设计规范
实操
命名规范：
| 对象 | 命名规则 | 示例 |
|---|---|---|
| 数据库 | 小写+下划线，业务名 | ecommerce, user_center |
| 表名 | 小写+下划线，单数名词 | user, order_item, product_category |
| 列名 | 小写+下划线 | user_id, created_at, is_deleted |
| 索引 | idx_表名_列名 | idx_users_email, idx_orders_user_status |
| 唯一索引 | uk_表名_列名 | uk_users_email |
| 外键 | fk_表名_关联表 | fk_orders_users |
| 存储过程 | sp_业务描述 | sp_process_order, sp_generate_report |
| 触发器 | trg_表名_时机_事件 | trg_users_after_update |
| 视图 | v_描述 | v_active_users, v_order_summary |
设计原则：
标准建表模板：
-- 标准业务表模板 CREATE TABLE business_entity ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', -- 业务字段 name VARCHAR(100) NOT NULL COMMENT '名称', description VARCHAR(500) DEFAULT '' COMMENT '描述', status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态: 0=禁用, 1=正常', -- 外键字段 creator_id BIGINT UNSIGNED DEFAULT NULL COMMENT '创建人ID', department_id BIGINT UNSIGNED DEFAULT NULL COMMENT '所属部门ID', -- 系统必备字段 is_deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否删除: 0=否, 1=是', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', version INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '乐观锁版本号', PRIMARY KEY (id), INDEX idx_status (status), INDEX idx_department_id (department_id), INDEX idx_created_at (created_at), INDEX idx_is_deleted_status (is_deleted, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='业务实体表';
7.2 运维监控与日常巡检
实操
核心监控指标：
| 指标分类 | 指标名称 | 告警阈值 | 查看方法 |
|---|---|---|---|
| 连接 | 当前连接数 | > max_connections * 80% | SHOW STATUS LIKE 'Threads_connected' |
| 性能 | Buffer Pool命中率 | < 99% | Innodb_buffer_pool_reads/requests |
| 复制 | 复制延迟 | > 30秒 | SHOW SLAVE STATUS |
| 磁盘 | 数据目录使用率 | > 80% | df -h |
| 慢查询 | 慢查询数量 | 持续增长 | SHOW STATUS LIKE 'Slow_queries' |
| 锁 | 锁等待时间 | > 5秒 | information_schema.INNODB_LOCK_WAITS |
| 临时表 | 磁盘临时表比例 | > 25% | Created_tmp_disk_tables/tables |
| CPU | CPU使用率 | > 70%持续 | top / htop |
日常巡检脚本：
-- MariaDB 日常巡检 SQL（保存为 check_health.sql） -- 1. 基本状态 SELECT @@version AS version; SELECT NOW() AS current_time; SHOW STATUS LIKE 'Uptime'; -- 2. 连接数 SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections'; SHOW VARIABLES LIKE 'max_connections'; -- 3. 慢查询统计 SHOW STATUS LIKE 'Slow_queries'; SHOW VARIABLES LIKE 'long_query_time'; -- 4. Buffer Pool SELECT ROUND( (1 - ( (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') / (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') )) * 100, 2 ) AS buffer_pool_hit_pct; -- 5. 表空间使用情况 SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb, ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_mb, ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_mb, COUNT(*) AS table_count FROM information_schema.TABLES GROUP BY table_schema ORDER BY total_mb DESC; -- 6. 检查复制状态（从库执行） SHOW SLAVE STATUS\G
推荐监控工具栈：
7.3 版本升级与迁移指南
实操
MariaDB 版本升级步骤：
# 1. 完整备份 sudo mariabackup --backup --target-dir=/backup/upgrade_backup \ --user=root --password='xxx' # 2. 检查兼容性 mysqlcheck -u root -p --all-databases --check-upgrade # 3. 停止旧版本 sudo systemctl stop mariadb # 4. 卸载旧版本 sudo apt remove mariadb-server mariadb-client # 5. 添加新版本仓库 curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-11.4" # 6. 安装新版本 sudo apt install mariadb-server mariadb-client # 7. 启动并运行升级脚本 sudo systemctl start mariadb sudo mariadb-upgrade -u root -p # 8. 重启确认 sudo systemctl restart mariadb # 9. 验证版本 mysql -u root -p -e "SELECT VERSION();"
从 MySQL 迁移到 MariaDB：
⚠️ 注意事项
📚 附录：常用命令速查手册
附录 A：常用管理命令速查
核心
-- ===== 服务器信息 ===== SELECT VERSION(); -- 版本号 SELECT NOW(); -- 当前时间 SHOW DATABASES; -- 所有数据库 SHOW TABLES; -- 当前库所有表 SHOW PROCESSLIST; -- 当前连接/查询 KILL QUERY 12345; -- 终止指定查询 -- ===== 变量与状态 ===== SHOW VARIABLES; -- 所有变量 SHOW VARIABLES LIKE '%buffer%'; -- 模糊查找变量 SHOW STATUS; -- 所有状态 SHOW STATUS LIKE 'Com_%'; -- 命令统计 -- ===== 表管理 ===== SHOW CREATE TABLE users; -- 查看建表语句 SHOW TABLE STATUS; -- 表详细信息 SHOW INDEX FROM users; -- 查看索引 ANALYZE TABLE users; -- 更新统计信息 CHECK TABLE users; -- 检查表完整性 OPTIMIZE TABLE users; -- 优化表（碎片整理） REPAIR TABLE users; -- 修复损坏的表 -- ===== 用户与权限 ===== SELECT user, host FROM mysql.user; -- 所有用户 SHOW GRANTS FOR 'user'@'host'; -- 查看权限 CREATE USER 'newuser'@'%' IDENTIFIED BY 'pass'; GRANT ALL ON db.* TO 'newuser'@'%'; REVOKE ALL ON db.* FROM 'newuser'@'%'; FLUSH PRIVILEGES; -- ===== 性能诊断 ===== SHOW ENGINE INNODB STATUS\G -- InnoDB详细状态 EXPLAIN SELECT ...; -- 执行计划 EXPLAIN FORMAT=JSON SELECT ...; -- JSON格式执行计划 SHOW PROFILE; -- 查询资源消耗 SELECT SLEEP(0); -- reset SHOW PROFILES; -- 历史profile -- ===== 数据字典查询 ===== SELECT * FROM information_schema.SCHEMATA; SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ecommerce'; SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'users'; SELECT * FROM information_schema.STATISTICS WHERE TABLE_NAME = 'users'; SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'ecommerce';
附录 B：常用函数速查
核心
字符串函数：
| 函数 | 说明 | 示例 |
|---|---|---|
| CONCAT() | 字符串拼接 | CONCAT('Hello', ' ', 'World') |
| CONCAT_WS() | 带分隔符拼接 | CONCAT_WS('-', year, month, day) |
| SUBSTRING() | 子串截取 | SUBSTRING(str, start, len) |
| LENGTH() | 字节长度 | LENGTH('你好') = 6 |
| CHAR_LENGTH() | 字符长度 | CHAR_LENGTH('你好') = 2 |
| UPPER()/LOWER() | 大小写转换 | UPPER('hello') = 'HELLO' |
| TRIM()/LTRIM()/RTRIM() | 去除空格 | TRIM(' hello ') |
| REPLACE() | 替换子串 | REPLACE(str, 'old', 'new') |
| REVERSE() | 字符串反转 | REVERSE('abc') = 'cba' |
| LPAD()/RPAD() | 左/右填充 | LPAD('5', 3, '0') = '005' |
日期时间函数：
| 函数 | 说明 | 示例 |
|---|---|---|
| NOW() | 当前日期时间 | 2024-01-15 10:30:00 |
| CURDATE() | 当前日期 | 2024-01-15 |
| DATE_FORMAT() | 格式化日期 | DATE_FORMAT(NOW(), '%Y年%m月') |
| DATE_ADD()/DATE_SUB() | 日期加减 | DATE_ADD(NOW(), INTERVAL 7 DAY) |
| DATEDIFF() | 日期差（天） | DATEDIFF('2024-12-31', '2024-01-01') |
| TIMESTAMPDIFF() | 时间差 | TIMESTAMPDIFF(HOUR, start, end) |
| UNIX_TIMESTAMP() | 转Unix时间戳 | UNIX_TIMESTAMP(NOW()) |
| FROM_UNIXTIME() | 时间戳转日期 | FROM_UNIXTIME(1705300200) |
| YEAR()/MONTH()/DAY() | 提取年月日 | YEAR(NOW()) |
聚合与条件函数：
| 函数 | 说明 | 示例 |
|---|---|---|
| COUNT()/SUM()/AVG() | 计数/求和/平均 | COUNT(*), SUM(amount) |
| MAX()/MIN() | 最大/最小值 | MAX(price) |
| GROUP_CONCAT() | 分组拼接 | GROUP_CONCAT(name SEPARATOR ',') |
| IF() | 条件判断 | IF(status='active', '正常', '禁用') |
| IFNULL() | 空值替换 | IFNULL(phone, '未填写') |
| COALESCE() | 返回第一个非NULL | COALESCE(a, b, c, '默认') |
| CASE WHEN | 多条件分支 | CASE WHEN score>=60 THEN '及格' ELSE '不及格' END |
| NULLIF() | 相等返回NULL | NULLIF(a, 0) -- 避免除零错误 |