数据库是网站的性能瓶颈所在。同样的服务器，数据库配置得当与否，性能可能差十倍。

这篇讲 MySQL/MariaDB 的安装、基础配置、必调参数、索引基础和备份。

数据库对内存和磁盘 IO 敏感。财富云计算 提供多种规格云服务器，建议数据库用 SSD；内网服务、测试环境可选 NAT 型（无独立公网 IP，价格比带独立 IP 机型省一半以上）。

一、安装

MariaDB（推荐，MySQL 的开源分支）

# Debian/Ubuntu
apt update
apt install mariadb-server -y
systemctl enable --now mariadb

# CentOS/Rocky
yum install mariadb-server -y
systemctl enable --now mariadb

MySQL 8.0（官方版）

# Ubuntu
apt install mysql-server -y

# CentOS（用官方 repo）
yum install https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm -y
yum install mysql-community-server -y
systemctl enable --now mysqld

初始化安全设置

mysql_secure_installation

按提示：

1. 设置 root 密码
2. 删除匿名用户 → Y
3. 禁止 root 远程登录 → Y
4. 删除 test 数据库 → Y
5. 重新加载权限表 → Y

mysql -u root -p

二、基础操作

-- 数据库
SHOW DATABASES;
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE mydb;
DROP DATABASE mydb;

-- 表
SHOW TABLES;
DESCRIBE tablename;
SHOW CREATE TABLE tablename;
SHOW INDEX FROM tablename;

-- 用户
CREATE USER 'user'@'localhost' IDENTIFIED BY '密码';
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'localhost';
REVOKE ALL ON mydb.* FROM 'user'@'localhost';
DROP USER 'user'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'user'@'localhost';

-- 状态
SHOW STATUS;
SHOW VARIABLES;
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

-- 版本
SELECT VERSION();

字符集一定要用 utf8mb4（支持 emoji 和全部 Unicode），不要用 utf8（MySQL 的 utf8 是残缺的 3 字节版本）。

三、必调的核心参数

配置文件位置：

/etc/mysql/mysql.conf.d/mysqld.cnf       # Ubuntu MySQL
/etc/mysql/mariadb.conf.d/50-server.cnf  # Ubuntu MariaDB
/etc/my.cnf.d/mysql-server.cnf           # CentOS

1）innodb_buffer_pool_size（最重要）

InnoDB 的缓存池，缓存数据和索引。这是影响性能最大的参数。

# 专用数据库服务器：物理内存的 60%~75%
innodb_buffer_pool_size = 4G

# Web + 数据库同机（2G 内存）：512M~1G
innodb_buffer_pool_size = 512M

参考：

设太大会导致系统内存不足被 OOM 杀掉。

2）连接数

max_connections = 200          # 默认 151，按需调整

不要盲目设很大：每个连接都占内存（几 MB），设 1000 而实际用不到会白白占用资源。

查看实际使用：

SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';

3）其他重要参数

[mysqld]
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

# 连接
max_connections = 200
max_connect_errors = 100
wait_timeout = 600
interactive_timeout = 600

# InnoDB
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2      # 1 最安全，2 性能更好（宕机可能丢 1 秒数据）
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# 查询缓存（MySQL 8 已移除，MariaDB 可开）
# query_cache_type = 1
# query_cache_size = 64M

# 临时表
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序缓冲（不要设太大，是每连接分配）
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M

# 慢查询日志（必须开）
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# 错误日志
log_error = /var/log/mysql/error.log

# 表缓存
table_open_cache = 2000

# 最大数据包（大字段/大导入时需要）
max_allowed_packet = 64M

# 二进制日志（主从/恢复用）
# server-id = 1
# log_bin = /var/log/mysql/mysql-bin
# expire_logs_days = 7

mkdir -p /var/log/mysql && chown mysql:mysql /var/log/mysql
systemctl restart mysql

四、索引：性能的关键

为什么要索引

没有索引的查询是全表扫描：100 万行的表要读 100 万行。有索引可能只读几行。

-- 创建索引
CREATE INDEX idx_email ON users(email);
ALTER TABLE users ADD INDEX idx_created (created_at);

-- 复合索引（注意顺序很重要）
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);

-- 删除
DROP INDEX idx_email ON users;

-- 查看
SHOW INDEX FROM users;

用 EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';

关注列：

看到 type=ALL 且 rows 很大，就要加索引。

索引失效的常见情况

-- ① 函数包裹列
WHERE DATE(created_at) = '2026-10-13'        -- ❌
WHERE created_at >= '2026-10-13' AND created_at < '2026-10-14'   -- ✅

-- ② LIKE 以 % 开头
WHERE name LIKE '%张%'                        -- ❌
WHERE name LIKE '张%'                         -- ✅

-- ③ 类型不匹配（隐式转换）
WHERE phone = 13800138000                     -- ❌  phone 是 varchar
WHERE phone = '13800138000'                   -- ✅

-- ④ 对索引列做运算
WHERE age + 1 > 20                            -- ❌
WHERE age > 19                                -- ✅

-- ⑤ 违反最左前缀（复合索引）
INDEX (a, b, c)
WHERE b = 1                                   -- ❌ 没用到
WHERE a = 1 AND b = 2                         -- ✅

-- ⑥ OR 连接不同列（部分情况）
WHERE a = 1 OR b = 2                          -- 可能失效

索引不是越多越好

• 索引占磁盘空间
• 每次 INSERT/UPDATE/DELETE 都要维护索引，写操作变慢
• 建议：常用查询条件建索引，区分度低的列（如性别）不建

五、慢查询分析

开启慢日志

slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

systemctl restart mysql
tail -100 /var/log/mysql/slow.log

分析工具

# 自带汇总工具
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log      # 最耗时的 10 条
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log      # 出现最多的 10 条
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log     # 平均耗时最长

# 专业工具（推荐）
apt install percona-toolkit -y
pt-query-digest /var/log/mysql/slow.log | head -50

优化思路

1. 加索引（效果最大）
2. 改写查询（避免 SELECT *、避免子查询嵌套、加 LIMIT）
3. 分页优化（大偏移用主键过滤）
4. 加缓存（Redis 缓存查询结果）
5. 读写分离

-- 分页优化示例
-- 慢：
SELECT * FROM posts ORDER BY id LIMIT 100000, 20;

-- 快：
SELECT * FROM posts WHERE id > 上一页最大id ORDER BY id LIMIT 20;

六、备份与恢复

# 备份单库
mysqldump -uroot -p --single-transaction --routines --triggers mydb > mydb.sql

# 备份全库
mysqldump -uroot -p --single-transaction --all-databases > all.sql

# 只备份结构
mysqldump -uroot -p --no-data mydb > schema.sql

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

# 恢复
mysql -uroot -p mydb < mydb.sql
gunzip < mydb.sql.gz | mysql -uroot -p mydb

--single-transaction 很重要：InnoDB 热备份，不锁表。

自动备份脚本：

#!/bin/bash
DATE=$(date +%F)
DIR=/backup/mysql
mkdir -p $DIR
mysqldump -uroot --single-transaction --routines --triggers --all-databases \
  | gzip > $DIR/all-$DATE.sql.gz
find $DIR -name "all-*.sql.gz" -mtime +30 -delete

详见《服务器备份与快照策略》。

七、安全配置

-- ① 不要用 root 跑应用
CREATE USER 'app'@'localhost' IDENTIFIED BY '强密码';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'localhost';

-- ② 删除匿名用户
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

# ③ 只监听本地（除非需要远程）
bind-address = 127.0.0.1

# ④ 防火墙不放行 3306（或只放行内网）
ss -tulnp | grep 3306

# ⑤ 关闭 LOCAL INFILE（防文件读取）
local-infile = 0

八、日常维护

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

-- 查看表大小
SELECT table_name,
       ROUND((data_length + index_length)/1024/1024, 2) AS '大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY (data_length + index_length) DESC;

-- 查看当前连接和正在执行的 SQL
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 2;

-- 杀掉慢查询
KILL 进程ID;

-- 优化表（整理碎片，InnoDB 效果有限）
OPTIMIZE TABLE tablename;

-- 检查表
CHECK TABLE tablename;

-- 分析表（更新索引统计信息，优化器用）
ANALYZE TABLE tablename;

九、常见问题

详细排查见《MySQL 常见故障排查》。

十、配置模板

2GB 内存，Web + MySQL 同机：

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

max_connections = 150
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1

max_allowed_packet = 32M
tmp_table_size = 32M
max_heap_table_size = 32M

slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

bind-address = 127.0.0.1

8GB 内存，数据库专用：

[mysqld]
character-set-server = utf8mb4
max_connections = 300
innodb_buffer_pool_size = 5G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1        # 数据安全优先
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000                 # SSD 可调高
table_open_cache = 4000
slow_query_log = 1
long_query_time = 1

数据库是性能瓶颈高发区，内存和磁盘要给够。财富云计算 的云服务器支持按需升级，建议数据库用 SSD 磁盘；开发测试、内部系统可选 NAT 型（无独立公网 IP，价格比带独立 IP 机型省一半以上），配合 127.0.0.1 监听，安全性更好。

相关阅读：

• 《MySQL 常见故障排查》
• 《Redis 安装与缓存应用实战》
• 《服务器备份与快照策略》
• 《网站打开慢的排查流程》

延伸阅读

1. 网站环境怎么选：LNMP 手动 / 宝塔面板 / Docker / 云开发
2. 问卷表单系统搭建：SurveyKing / Formbricks，数据自己收
3. 图床搭建：Lsky Pro / Lychee，自托管图片托管方案
4. 邮件列表搭建：listmonk 自托管 Newsletter，把读者握在自己手里
5. 文档站点搭建：MkDocs / VuePress / Docsify，产品文档与技术手册方案
6. RSS 阅读器搭建：FreshRSS 自托管，把信息流拿回自己手里
7. 短链接服务搭建：Shlink 自托管，链接数据自己掌握
8. 网站状态监控页搭建：Uptime Kuma，服务器挂了第一时间知道

更多阅读

1. 上一篇：sed 与 awk 文本处理实战：命令行下的批量编辑与数据分析
2. 下一篇：MySQL 常见故障排查：连接爆满、慢查询、死锁、磁盘暴涨