13 10 月 2026

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

这篇讲 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

参考:

服务器内存建议值
1GB128M~256M
2GB512M
4GB1G~2G
8GB4G~6G
16GB+10G~12G

设太大会导致系统内存不足被 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 是全表扫描(差),ref/range/const 较好
key实际使用的索引,NULL 表示没用上
rows预计扫描行数,越小越好
ExtraUsing filesort、Using temporary 都是需要优化的信号

看到 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;

九、常见问题

问题原因解决
Too many connections连接数打满调大 max_connections,或查连接泄漏
Can't connect to MySQL server服务没起 / 只监听 127.0.0.1systemctl status,检查 bind-address
Access denied for user账号密码或 host 不对检查用户 'user'@'host' 的 host 部分
查询很慢没索引EXPLAIN 分析,加索引
磁盘空间暴涨binlog / 临时文件 / 表碎片清理 binlog,OPTIMIZE TABLE
内存占用高buffer_pool 太大调小 innodb_buffer_pool_size
Table is marked as crashed表损坏REPAIR TABLE(MyISAM)或 InnoDB 恢复
中文乱码字符集不对统一用 utf8mb4
死锁事务顺序不一致查看 SHOW ENGINE INNODB STATUS

详细排查见《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. 高防误区与避坑清单:25 个坑+下单前必问 14 问+到货验货流程
  2. DNS 攻击防护专题:劫持、隧道、投毒、放大四类攻击与 DNSSEC
  3. 电商大促防攻击预案:T-14 到当天的分级响应与降级开关
  4. 源站真实 IP 隐藏完全指南:六大暴露面排查+NAT 型终极方案
  5. 日志分析找攻击源:Nginx 日志三维度分析法与证据固定
  6. 攻击应急响应手册:六阶段响应流程,从确认到复盘的照做清单
  7. iptables 抗 DDoS 配置模板:状态过滤+SYN 防护+防扫描+游戏服版
  8. OpenResty Lua 自研 WAF:动态限流+攻击特征检测+Redis 黑名单
更多阅读
  1. 上一篇:sed 与 awk 文本处理实战:命令行下的批量编辑与数据分析
  2. 下一篇:MySQL 常见故障排查:连接爆满、慢查询、死锁、磁盘暴涨
发表评论 7

    1. 云服务器迁移上云实操:换机器不丢数据、业务少中断 - 			财富云计算 - 维基百科的头像
      云服务器迁移上云实操:换机器不丢数据、业务少中断 - 财富云计算 - 维基百科  

      [...]gunzip < /tmp/mysql-all.sql.gz | mysql -u root -p--single-transaction 保证 InnoDB 表导出期间数据一致,不用停库。大数据库(几十 G)建议用主从同步,先让新机器做旧机器的从库,同步追平后再切换。这块展开讲太长,站内有 MySQL 安装与优化入门 可以参考。三、正式切换:中断时间最短的流程数据已经预热同步过一轮,正式切[...]

    2. MySQL 慢查询优化实战:慢日志、EXPLAIN、索引三板斧 - 			财富云计算 - 维基百科的头像
      MySQL 慢查询优化实战:慢日志、EXPLAIN、索引三板斧 - 财富云计算 - 维基百科  

      [...]# - buffer pool 命中率(应 >99%)一次只改一个变量,改完观察一天。批量改索引,出了问题不知道回滚哪个。六、速查:EXPLAIN 结果对照表看到动作type=ALL 且 rows 很大加索引Extra=Using filesort索引带上排序字段Extra=Using temporary优化 GROUP BY,索引配合key=NULLWHERE 条件列没索引rows 远[...]

    3. NAT 服务器能力边界详解:能干什么、不能干什么、怎么扬长避短 - 			财富云计算 - 维基百科的头像
      NAT 服务器能力边界详解:能干什么、不能干什么、怎么扬长避短 - 财富云计算 - 维基百科  

      [...]4核16G照常有 公网IP:端口 才能进维度NAT 型独立 IPCPU / 内存 / 磁盘性能完全一致一致出站连接(你连别人)无限制无限制入站连接(别人连你)需端口映射IP 直连可映射端口数平台规则(通常 5-20)65535价格独立 IP 一半以下全价结论先行:对 90% 的个人用户场景,NAT 型和独立 IP 的体验差距只在「配置端口映射」这一步。二、完全无感的场景:映射[...]

    4. 小项目用什么数据库:SQLite 与 MySQL 怎么选 - 			财富云计算 - 维基百科的头像
      小项目用什么数据库:SQLite 与 MySQL 怎么选 - 财富云计算 - 维基百科  

      [...]# https://z0w.cn/wiki/database-security-hardening.htmlMySQL 的完整运维(安装、备份、主从、优化)见MySQL 安装与优化入门、MySQL 备份与恢复实战。七、常见疑问Q:SQLite 真的能扛几千 IP 的网站吗?能。Typecho + SQLite 扛日 IP 几千的博客是社区常态——读多写少的场景 SQLite 很从容。瓶颈通常在 P[...]

    5. Steam游戏开服端口速查表:AppID、端口协议、配置需求一表查完(含Windows/Linux双系统索引) - 			财富云计算 - 维基百科的头像
      Steam游戏开服端口速查表:AppID、端口协议、配置需求一表查完(含Windows/Linux双系统索引) - 财富云计算 - 维基百科  

      [...]netsh advfirewall firewall add rule name="GameRange" dir=in action=allow protocol=UDP localport=26901-26903完整的 Windows 环境准备(远程桌面连接、运行库安装、SteamCMD 用法、开机自启、8 个常见坑)见:《Windows 云服务器开服通用前置教程》→ htt[...]

    6. 小程序后端搭建实战:云服务器 + 域名备案 + HTTPS 全流程 - 			财富云计算 - 维基百科的头像
      小程序后端搭建实战:云服务器 + 域名备案 + HTTPS 全流程 - 财富云计算 - 维基百科  

      [...]redisdata:MySQL 安装调优细节见MySQL 安装与优化入门,Redis 见Redis 安装与缓存应用实战。四、配 HTTPS:证书 + Nginx 反代申请证书(Let's Encrypt 免费,宝塔里一键申请;或商家送的免费证书):# certbot 方式(Debian/Ubuntu)[...]

    7. 小程序数据库对接实战:MySQL 选型、连接池与注入防护 - 			财富云计算 - 维基百科的头像
      小程序数据库对接实战:MySQL 选型、连接池与注入防护 - 财富云计算 - 维基百科  

      [...]innodb_flush_log_at_trx_commit = 2 # 性能与安全的折中完整调优见MySQL 安装与优化入门。三、连接管理:连接池是生命线反面教材——每个请求建一次连接:// 错误:高并发下把 MySQL 连接数打满,直接 "Too many connections"[...]