数据库配置优化

1. 数据库优化概述

1.1 优化的重要性

数据库优化是系统架构中的核心环节，直接影响系统的性能、稳定性和用户体验。合理的优化可以提升系统性能数倍，而不当的配置可能导致系统崩溃。

优化的核心价值：

• 性能提升：提高查询速度和响应时间
• 资源利用：充分利用硬件资源
• 稳定性：提高系统稳定性和可用性
• 成本控制：在有限资源下达到最佳性能

1.2 MySQL数据库优化框架体系

优化要有框架和体系，根据用户访问流程优化集群，根据OSI 7层模型，从下往上优化数据库。

优化层次：

1
2
3
4
5
6
7
8
9
10
11
12
13

┌─────────────────────────────────────┐
│  6. MySQL安全优化                    │
├─────────────────────────────────────┤
│  5. MySQL流程、制度控制优化          │
├─────────────────────────────────────┤
│  4. 网站集群架构优化                 │
├─────────────────────────────────────┤
│  3. MySQL数据库层面优化              │
├─────────────────────────────────────┤
│  2. 操作系统层面优化                 │
├─────────────────────────────────────┤
│  1. 硬件层面优化                     │
└─────────────────────────────────────┘

优化原则：

• 自下而上：从硬件到应用层逐层优化
• 系统化：建立完整的优化体系
• 持续优化：优化是一个持续的过程
• 数据驱动：基于监控数据进行优化

1.3 优化流程

1
2
3
4
5
6
7
8
9
10
11
12
13

1. 性能分析
   ↓
2. 问题定位
   ↓
3. 优化方案设计
   ↓
4. 测试验证
   ↓
5. 上线部署
   ↓
6. 监控评估
   ↓
7. 持续优化

2. 硬件层面优化

2.1 数据库物理机采购

硬件是数据库性能的基础，合理的硬件配置可以大幅提升数据库性能。

2.1.1 CPU选择

推荐配置：

• 架构：64位CPU
• 数量：一台机器2-16颗CPU
• 最低要求：至少2-4颗CPU
• 缓存：L2缓存越大越好

选择建议：

• 数据库需要高运算量，CPU性能很重要
• 多核CPU可以支持并行处理
• L2缓存大可以减少内存访问延迟

案例参考：

• 百度：某部门IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例
• 新浪：服务器是DELL R510居多，CPU是E5210，48GB内存

2.1.2 内存（MEM）选择

推荐配置：

• 生产环境：96G-128G，跑3-4个实例
• 中等环境：32G-64G，跑1-2实例
• 小型环境：16G-32G，跑1个实例

选择建议：

• 内存越大，可以缓存更多数据
• InnoDB的buffer_pool_size建议设置为物理内存的50%-70%
• 多实例部署需要合理分配内存

2.1.3 磁盘（Disk）选择

磁盘类型对比：

选择建议：

机械盘（SAS）：

• 选择SAS盘，数量越多越好
• 转数越高越好，推荐15K转
• 适合普通业务线上环境

SSD固态盘：

• 使用SSD或PCIE SSD设备
• 可提升上千倍的IOPS效率
• 随机IO：单盘能力可达35000 IOPS
• 适合高并发业务场景

Flashcache HBA卡：

• 使用Flashcache HBA卡
• 在机械盘基础上增加SSD缓存
• 性价比高

性能对比：

1
2
3

SSD随机IO：35000 IOPS/盘
SAS随机IO：300 IOPS/盘
性能提升：100倍+

2.1.4 RAID阵列卡选择

RAID级别对比：

选择建议：

• 至少4块盘：保证性能和冗余
• 推荐RAID 10：性能好，可靠性高
• 避免RAID 5：写性能差，重建时间长
• RAID 0：仅用于测试环境

2.1.5 网卡选择

推荐配置：

• 多块网卡：使用bonding绑定
• 千兆网卡：至少千兆网卡
• 万兆网卡：高并发场景推荐万兆
• buffer优化：调整TCP buffer参数

网络设备：

• 千兆网线：使用6类或超6类网线
• 千兆交换机：保证网络带宽

2.1.6 其他硬件建议

虚拟化：

• 数据库服务器尽量不用虚拟化
• 虚拟化会带来性能损失
• 物理机性能更稳定

Slave硬件：

• Slave硬件要等于或大于Master的性能
• 保证从库能跟上主库的复制速度
• 避免复制延迟

2.2 服务器硬件配置调整

2.2.1 服务器BIOS调整

提升CPU效率参考设置：

1. 打开Performance Per Watt Optimized（DAPC）模式

设置：打开DAPC模式，发挥CPU最大性能

原因：数据库通常需要高运算量，需要CPU最大性能

2. 关闭C1E和C States等选项

设置：关闭CPU节能选项

原因：CPU节能模式会降低性能，数据库需要持续高性能

3. Memory Frequency（内存频率）选择Maximum Performance

设置：内存频率选择最佳性能模式

原因：提高内存访问速度

4. 启用Node Interleaving

设置：在内存设置菜单中，启用Node Interleaving

原因：避免NUMA问题，提高内存访问效率

NUMA问题说明：

• NUMA（Non-Uniform Memory Access）可能导致内存访问不均衡
• 启用Node Interleaving可以避免这个问题

2.2.2 阵列卡调整

1. 购置阵列卡同时配备CACHE及BBU模块

配置：

• CACHE模块：提供高速缓存
• BBU模块：电池备份单元，保证缓存数据不丢失

原因：

• 机械盘需要CACHE提升性能
• BBU保证断电时缓存数据不丢失

2. 设置阵列写策略为WB

设置：Write Back（回写）策略

配置选项：

• WB（Write Back）：数据先写入缓存，再异步写入磁盘
• FPRCE WB（Force Write Back）：强制回写模式（对数据安全要求高时使用）

原因：

• WB策略可以大幅提升写性能
• 数据先写入高速缓存，再异步写入磁盘

3. 严禁使用WT策略

禁止：Write Through（透写）策略

原因：

• WT策略会降低写性能
• 每次写入都要等待磁盘确认

4. 关闭阵列预读策略

设置：关闭阵列预读（Read Ahead）

原因：

• 数据库的访问模式通常是随机IO
• 预读策略对随机IO无效，反而浪费资源

3. 操作系统层面优化

3.1 操作系统及MySQL实例选择

3.1.1 操作系统选择

推荐配置：

1. 一定要选择x86_64系统

   • 64位系统支持更大内存
   • 性能优于32位系统

2. 推荐使用CentOS 6.8 Linux

   • 稳定可靠
   • 社区支持好
   • 兼容性强

3. 关闭NUMA特性

   1 2 3 4

   # 在GRUB配置中关闭NUMA vim /etc/grub.conf # 在kernel行添加 numa=off

3.1.2 分区规划

分区建议：

1. 将操作系统和数据分区分开

   • 不仅仅是逻辑上分开
   • 还包括物理上分开（不同磁盘）

2. 避免使用swap交换分区

   • 数据库服务器内存充足
   • Swap会严重影响性能
   • 如果必须使用，设置vm.swappiness=0

3. 避免使用软件磁盘阵列

   • 使用硬件RAID卡
   • 软件RAID性能差

4. 避免使用LVM逻辑卷

   • LVM会增加一层抽象
   • 可能影响性能
   • 直接使用物理分区

3.1.3 系统清理

删除服务器上未使用的安装包和守护进程：

1
2
3
4
5
6
7
8
9

# 停止不需要的服务
systemctl stop postfix
systemctl disable postfix

# 卸载不需要的软件包
yum remove -y postfix sendmail

# 清理系统
yum clean all

3.2 文件系统层面优化

3.2.1 调整磁盘Cache mode

启用WCE和RCD模式：

1
2
3
4
5

# 启用WCE=1（Write Cache Enable），RCD=0（Read Cache Disable）模式
sdparm -s WCE=1,RCD=0 -S /dev/sdb

# 验证设置
sdparm /dev/sdb | grep -i cache

参数说明：

• WCE=1：启用写缓存，提升写性能
• RCD=0：禁用读缓存，避免数据不一致

3.2.2 采用Linux I/O scheduler算法

使用deadline调度算法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 查看当前调度算法
cat /sys/block/sdb/queue/scheduler

# 设置为deadline
echo deadline > /sys/block/sdb/queue/scheduler

# 调整deadline参数
# 对于CentOS Linux建议 read_expire = 1/2 write_expire
echo 500 > /sys/block/sdb/queue/iosched/read_expire
echo 1000 > /sys/block/sdb/queue/iosched/write_expire

# 永久生效（添加到/etc/rc.local）
echo 'echo deadline > /sys/block/sdb/queue/scheduler' >> /etc/rc.local
echo 'echo 500 > /sys/block/sdb/queue/iosched/read_expire' >> /etc/rc.local
echo 'echo 1000 > /sys/block/sdb/queue/iosched/write_expire' >> /etc/rc.local

调度算法对比：

3.2.3 文件系统选择

推荐文件系统：

1. 采用xfs文件系统（推荐）

   • 业务量很大推荐xfs
   • 支持大文件和大目录
   • 性能优秀

2. 业务量不是很大也可以采用ext4

   • 稳定可靠
   • 兼容性好

创建xfs文件系统：

1
2
3
4
5
6

# 创建xfs文件系统
mkfs.xfs /dev/sdb1

# 调整xfs文件系统日志和缓冲变量
# 挂载时指定参数
mount -o noatime,nodiratime,nobarrier /dev/sdb1 /data

3.2.4 挂载选项优化

mount挂载文件系统增加优化选项：

1
2
3
4
5

# 编辑/etc/fstab
vim /etc/fstab

# 添加优化选项
/dev/sdb1  /data  xfs  defaults,async,noatime,nodiratime,nobarrier  0  0

参数说明：

注意：如果使用RAID卡电池，可以不使用nobarrier选项。

3.3 Linux内核参数优化

3.3.1 内存参数优化

编辑/etc/sysctl.conf：

1

vim /etc/sysctl.conf

内存优化参数：

1
2
3
4
5
6
7
8
9
10
11

# 将vm.swappiness设置为0-10
# 0表示尽可能不使用swap，10表示在内存不足时使用少量swap
vm.swappiness=0

# 将vm.dirty_background_ratio设置为5-10
# 后台进程开始刷新脏数据的百分比
vm.dirty_background_ratio=5

# 将vm.dirty_ratio设置为它的两倍左右（10-20）
# 系统开始强制刷新脏数据的百分比
vm.dirty_ratio=10

参数说明：

• swappiness：控制swap使用，数据库服务器建议设为0
• dirty_background_ratio：后台刷新脏数据的阈值
• dirty_ratio：强制刷新脏数据的阈值

优化目的：

• 确保持续将脏数据刷新到磁盘
• 避免瞬间I/O写，产生严重等待

3.3.2 TCP协议栈优化

TCP优化参数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 减少TIME_WAIT，提高tcp效率
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_tw_reuse=1

# 减少处于FIN-WAIT-2连接状态的时间，使系统可以处理更多的连接
net.ipv4.tcp_fin_timeout=2

# 减少TCP KeepAlive连接侦测的时间，使系统处理更多的连接
net.ipv4.tcp_keepalive_time=600

# 提高系统支持的最大SYN半连接数（默认1024）
net.ipv4.tcp_max_syn_backlog=16384

# 减少系统SYN连接重试次数（默认5）
net.ipv4.tcp_synack_retries=1
net.ipv4.tcp_syn_retries=1

# 允许系统打开的端口范围
net.ipv4.ip_local_port_range=4500 65535

参数说明：

3.3.3 网络缓冲区优化

网络缓冲区参数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 优化系统套接字缓冲区
# Increase TCP max buffer size
net.core.rmem_max=16777216      # 最大socket读buffer
net.core.wmem_max=16777216      # 最大socket写buffer
net.core.wmem_default=8388608   # 默认socket写buffer
net.core.rmem_default=8388608   # 默认socket读buffer

# 优化TCP接收/发送缓冲区
# Increase Linux autotuning TCP buffer limits
net.ipv4.tcp_rmem=4096 87380 16777216
net.ipv4.tcp_wmem=4096 65536 16777216
net.ipv4.tcp_mem=94500000 915000000 927000000

# 优化网络设备接收队列
net.core.netdev_max_backlog=3000
net.core.somaxconn=32768

参数说明：

3.3.4 其他优化参数

1
2
3
4
5
6
7
8

# 禁用TCP时间戳（某些场景下可以提高性能）
net.ipv4.tcp_timestamps=0

# 优化TCP孤儿连接
net.ipv4.tcp_max_orphans=3276800

# 优化TIME_WAIT连接数
net.ipv4.tcp_max_tw_buckets=360000

应用配置：

1
2
3
4
5

# 使配置生效
sysctl -p

# 验证配置
sysctl -a | grep tcp

4. MySQL数据库层面优化

4.1 my.cnf参数优化

4.1.1 存储引擎选择

强烈推荐采用InnoDB引擎：

1
2

# 设置默认存储引擎
default-storage-engine=InnoDB

原因：

• InnoDB支持事务
• 支持行级锁
• 支持外键
• 崩溃恢复能力强
• 性能优秀

MyISAM引擎优化（如果必须使用）：

1
2

# 如果采用MyISAM引擎，需要key_buffer_size加大
key_buffer_size=256M

4.1.2 InnoDB核心参数优化

1. innodb_buffer_pool_size

设置：考虑设置为物理内存的50%~60%左右

1
2

# 例如：64G内存的服务器
innodb_buffer_pool_size=40G

说明：

• 这是InnoDB最重要的参数
• 设置越大，缓存的数据越多
• 建议设置为物理内存的50%-70%
• 不要设置过大，需要为操作系统和其他进程预留内存

2. innodb_flush_log_at_trx_commit和sync_binlog

数据安全要求高：

1
2
3

# 如果要求数据不能丢失，两个都要设置为1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

允许丢一点数据：

1
2
3

# 如果允许丢一点数据，可分别设为2和0
innodb_flush_log_at_trx_commit=2
sync_binlog=0

从库设置：

1
2
3

# 在slave可设为0（从库可以容忍数据丢失）
innodb_flush_log_at_trx_commit=0
sync_binlog=0

参数说明：

3. innodb_file_per_table

设置：使用独立表空间

1

innodb_file_per_table=1

优势：

• 每个表独立的.ibd文件
• 便于管理和维护
• 可以单独回收表空间
• 避免ibdata文件过大

4. innodb_data_file_path

设置：不要用默认的10M

1

innodb_data_file_path=ibdata1:1G:autoextend

说明：

• 初始大小设置为1G或更大
• 避免频繁扩展
• 提高性能

5. innodb_log_file_size和innodb_log_files_in_group

设置：

1
2

innodb_log_file_size=256M
innodb_log_files_in_group=2

说明：

• 基本可满足90%以上的场景
• 不要设置太大，可以更快同时又更多的磁盘空间
• 丢掉更多的日志通常是好的，在数据库崩溃后可以降低恢复数据库的时间

总日志大小：256M × 2 = 512M

4.1.3 连接相关参数

连接参数优化：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 根据业务实际需要，适当调整最大连接数
max_connections=2000

# 最大错误连接数，建议设置为10万以上
max_connect_errors=100000

# 打开文件数限制，设为约10倍于max_connections的大小
open_files_limit=20000

# InnoDB打开文件数
innodb_open_files=20000

# 表缓存
table_open_cache=20000

# 表定义缓存
table_definition_cache=20000

参数关系：

• open_files_limit ≥ innodb_open_files ≥ table_open_cache
• 建议都设置为max_connections的10倍左右

4.1.4 慢查询日志

设置慢查询日志：

1
2
3
4
5
6
7
8
9

# 记录执行时间超过1秒的SQL
long_query_time=1

# 慢查询日志文件
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log

# 记录未使用索引的查询
log_queries_not_using_indexes=1

用途：

• 记录那些执行较慢的SQL
• 用于后续的分析排查
• 优化慢SQL

4.1.5 Session级参数优化

重要：这些参数是每个连接session分配的，因此不能设置过大。

1
2
3
4
5
6
7
8
9
10
11
12
13

# 临时表大小
tmp_table_size=32M
max_heap_table_size=32M

# 排序缓冲区
sort_buffer_size=2M

# 连接缓冲区
join_buffer_size=2M

# 读缓冲区
read_buffer_size=1M
read_rnd_buffer_size=1M

参数说明：

• 这些参数是每个连接分配的
• 如果设置过大，会导致内存消耗过大
• 需要根据实际业务调整

4.1.6 Query Cache优化

建议关闭query cache功能或降低设置：

1
2
3
4
5
6
7

# 关闭Query Cache（推荐）
query_cache_type=0
query_cache_size=0

# 或者降低设置不要超过512M
# query_cache_type=1
# query_cache_size=512M

原因：

• Query Cache在MySQL 5.7+性能提升有限
• 在高并发场景下可能成为瓶颈
• 维护Query Cache需要额外开销

4.1.7 参数优化工具

使用工具检查参数配置：

1
2
3
4
5

# mysqlreport工具分析参数配置
mysqlreport --user=root --password=Bgx123.com

# 或者使用pt工具
pt-mysql-summary

工具作用：

• 分析当前配置是否合理
• 提供优化建议
• 发现配置问题

4.2 关于库表的设计规范

4.2.1 字符集选择

推荐utf-8字符集：

1
2

-- 创建数据库时指定字符集
CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

说明：

• 虽然有人说latin1更快，但utf8兼容性更好
• utf8mb4支持emoji等特殊字符
• 推荐使用utf8mb4

4.2.2 字段类型选择

1. 固定字符串使用CHAR

推荐：固定字符串的列尽可能多用定长CHAR，少用VARCHAR

1
2
3
4
5
6
7
8
9
10
11

-- 推荐：使用CHAR
CREATE TABLE t1 (
    id INT,
    status CHAR(10)  -- 固定长度
);

-- 不推荐：使用VARCHAR存储固定长度
CREATE TABLE t1 (
    id INT,
    status VARCHAR(10)  -- 固定长度应该用CHAR
);

说明：

• 存储可变长度的字符串时使用VARCHAR
• 固定长度的字符串使用CHAR（UTF8不受此影响）

2. 字段长度选择

原则：字段长度满足需求的前提下，尽可能选择长度小的

1
2
3
4
5
6
7

-- 推荐：使用合适的数据类型
CREATE TABLE t1 (
    id TINYINT UNSIGNED,      -- 0-255，使用TINYINT
    age TINYINT UNSIGNED,     -- 年龄不会超过255
    status TINYINT,            -- 状态值，使用TINYINT
    name VARCHAR(50)          -- 根据实际需求设置长度
);

3. 字段属性约束

推荐：字段属性尽量加上NOT NULL约束

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 推荐：使用NOT NULL
CREATE TABLE t1 (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

-- 对于某些文本字段，使用ENUM类型
CREATE TABLE t1 (
    id INT,
    province ENUM('北京','上海','广州','深圳') NOT NULL,
    gender ENUM('M','F') NOT NULL
);

优势：

• NOT NULL可以避免NULL值比较的复杂性
• ENUM类型节省存储空间
• 提高查询性能

4.2.3 主键设计

推荐：所有的InnoDB表都要设计一个无业务用途的自增列做主键

1
2
3
4
5
6
7

-- 推荐：使用自增ID作为主键
CREATE TABLE t1 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    name VARCHAR(50),
    INDEX idx_user_id(user_id)
);

原因：

• 自增主键插入性能好
• 主键索引更小
• 避免业务字段变更影响主键

4.2.4 TEXT/BLOB类型使用

推荐：尽可能不使用TEXT/BLOB类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 不推荐：TEXT和主表放在一起
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    content TEXT,  -- 不推荐
    blog_content BLOB  -- 不推荐
);

-- 推荐：拆分到子表
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    -- 其他常用字段
);

CREATE TABLE t1_content (
    id INT PRIMARY KEY,
    content TEXT,
    FOREIGN KEY (id) REFERENCES t1(id)
);

原因：

• TEXT/BLOB字段会影响SELECT *的性能
• 拆分后可以按需查询
• 提高查询性能

4.2.5 查询优化

推荐：只选取所需要的列，不要每次都SELECT *

1
2
3
4
5

-- 不推荐
SELECT * FROM t1;

-- 推荐：只选择需要的列
SELECT id, name, email FROM t1;

原因：

• 避免读取TEXT/BLOB等大字段
• 减少网络传输
• 提高查询性能

4.2.6 索引设计规范

1. 前缀索引

推荐：对VARCHAR(N)列创建索引时，通常取50%左右长度创建前缀索引

1
2
3
4
5

-- 推荐：使用前缀索引
CREATE INDEX idx_name ON t1(name(50));

-- 不推荐：使用全长度索引
CREATE INDEX idx_name ON t1(name);

原因：

• 前缀索引足以满足80%以上的查询需求
• 节省存储空间
• 提高索引性能

2. 复合索引

推荐：多用复合索引，少用多个独立索引

1
2
3
4
5
6

-- 推荐：使用复合索引
CREATE INDEX idx_name_age ON t1(name, age);

-- 不推荐：使用多个独立索引
CREATE INDEX idx_name ON t1(name);
CREATE INDEX idx_age ON t1(age);

原因：

• 复合索引可以满足多个查询条件
• 减少索引数量
• 提高查询性能

3. 低基数列不建索引

推荐：基数（Cardinality）太小的列不要创建独立索引

1
2
3
4
5

-- 不推荐：为低基数列创建索引
CREATE INDEX idx_gender ON t1(gender);  -- 只有M和F两个值

-- 推荐：使用复合索引
CREATE INDEX idx_gender_age ON t1(gender, age);

原因：

• 低基数列索引效果差
• 浪费存储空间
• 可能影响写性能

5. SQL语句的优化

5.1 索引优化

5.1.1 白名单机制

流程：项目开发，DBA参与，减少上线后的慢SQL数量

步骤：

1. 抓出慢SQL

   1 2 3 4

   # 配置my.cnf long_query_time=2 log-slow-queries=/data/3306/slow-log.log log_query_not_using_indexes

2. 按天轮询slow-log.log

   1 2 3 4

   # 日志轮询脚本 #!/bin/bash mv /data/3306/slow-log.log /data/3306/slow-log-$(date +%F).log mysqladmin flush-logs

3. 慢查询日志分析工具

   1 2 3 4 5

   # 使用pt-query-digest分析（推荐） pt-query-digest /data/3306/slow-log.log > slow-report.txt # 或使用mysqlsla mysqlsla -lt slow /data/3306/slow-log.log

4. 每天晚上0点定时分析查询

   1 2	# 定时任务 0 0 * * * pt-query-digest /data/3306/slow-log.log | mail -s "Slow SQL Report" dba@example.com

5. DBA分析给出优化建议 → 核心开发确认更改 → DBA线上操作处理

5.1.2 索引维护

定期检查重复索引：

1
2

# 使用pt-duplicate-key-checker检查并删除重复的索引
pt-duplicate-key-checker -u root -p Bgx123.com

定期检查未使用的索引：

1
2

# 使用pt-index-usage工具检查并删除使用频率很低的索引
pt-index-usage /data/3306/slow-log.log -u root -p Bgx123.com

5.1.3 在线DDL

使用pt-online-schema-change完成大表的ONLINE DDL需求：

1
2
3
4
5

# 在线修改表结构
pt-online-schema-change \
    --alter "ADD COLUMN new_col INT" \
    D=testdb,t=large_table \
    --execute

优势：

• 不锁表
• 不影响业务
• 安全可靠

5.1.4 索引使用优化

有时候MySQL会使用错误的索引：

1
2
3
4
5

-- 使用USE INDEX强制使用索引
SELECT * FROM t1 USE INDEX (idx_name) WHERE name = 'test';

-- 使用EXPLAIN查看执行计划
EXPLAIN SELECT * FROM t1 WHERE name = 'test';

使用EXPLAIN和SET PROFILE优化语句：

1
2
3
4
5
6
7
8

-- 查看执行计划
EXPLAIN SELECT * FROM t1 WHERE id = 1;

-- 使用PROFILE分析
SET profiling = 1;
SELECT * FROM t1 WHERE id = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

5.2 SQL语句优化技巧

5.2.1 拆分复杂SQL

原则：大的复杂的SQL语句拆分成多个小的SQL语句

1
2
3
4
5
6
7
8
9
10

-- 不推荐：复杂的子查询
SELECT * FROM t1 WHERE id IN (
    SELECT user_id FROM t2 WHERE status = 1
);

-- 推荐：拆分成多个SQL
-- 第一步：查询user_id
SELECT user_id FROM t2 WHERE status = 1;
-- 第二步：使用结果查询
SELECT * FROM t1 WHERE id IN (1,2,3,...);

原因：

• 子查询可能执行效率低
• 拆分后可以分别优化
• 便于理解和维护

5.2.2 避免JOIN连表查询

原则：某个表4000万条记录时，避免大表JOIN

1
2
3
4
5
6
7
8
9
10

-- 不推荐：大表JOIN
SELECT * FROM large_table1 t1
JOIN large_table2 t2 ON t1.id = t2.id;

-- 推荐：应用层处理
-- 1. 查询第一个表
SELECT * FROM large_table1 WHERE ...;
-- 2. 应用层处理
-- 3. 查询第二个表
SELECT * FROM large_table2 WHERE id IN (...);

5.2.3 数据库不是计算的地方

原则：数据库是存储数据的地方，但是不是计算数据的地方

1
2
3
4
5
6
7
8
9
10

-- 不推荐：在数据库中进行复杂计算
SELECT 
    id,
    (price * quantity * discount) AS total,
    DATE_FORMAT(created_at, '%Y-%m') AS month
FROM orders;

-- 推荐：在应用层计算
SELECT id, price, quantity, discount, created_at FROM orders;
-- 应用层计算total和month

原因：

• 数据库计算消耗CPU资源
• 应用层计算更灵活
• 便于扩展和维护

5.2.4 搜索功能优化

原则：搜索功能，like ‘%老男孩%’，一般不要用MySQL数据库

推荐方案：

• 使用Elasticsearch进行全文搜索
• 使用Solr进行搜索
• 使用专业的搜索引擎

MySQL全文索引限制：

• 性能较差
• 功能有限
• 不适合复杂搜索

5.2.5 其他SQL优化技巧

1. 使用JOIN代替子查询

1
2
3
4
5

-- 不推荐：子查询
SELECT * FROM t1 WHERE id IN (SELECT user_id FROM t2);

-- 推荐：JOIN
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.user_id;

2. 避免COUNT(*)

1
2
3
4
5

-- 不推荐：在整个表上使用count(*)
SELECT COUNT(*) FROM large_table;

-- 推荐：使用统计表或缓存
-- 维护一个统计表，定期更新

3. 关联字段类型一致

1
2
3
4

-- 推荐：关联字段类型一致，并且都要有索引
SELECT * FROM t1 
JOIN t2 ON t1.id = t2.user_id  -- id和user_id类型一致，都有索引
WHERE t1.status = 1;

4. 驱动表选择

原则：多表连接查询时，把结果集小的表作为驱动表

1
2
3

-- 优化器会自动选择，但可以手动提示
SELECT * FROM small_table t1
STRAIGHT_JOIN large_table t2 ON t1.id = t2.user_id;

5. 排序字段优化

原则：多表联接并且有排序时，排序字段必须是驱动表里的

1
2
3
4

-- 推荐：排序字段在驱动表中
SELECT * FROM small_table t1
JOIN large_table t2 ON t1.id = t2.user_id
ORDER BY t1.created_at;  -- 排序字段在驱动表中

6. 避免IN、OR、<>

1
2
3
4
5
6
7
8
9

-- 不推荐
SELECT * FROM t1 WHERE id IN (1,2,3);
SELECT * FROM t1 WHERE status = 1 OR status = 2;
SELECT * FROM t1 WHERE id <> 1;

-- 推荐
SELECT * FROM t1 WHERE id BETWEEN 1 AND 3;
SELECT * FROM t1 WHERE status IN (1,2);
SELECT * FROM t1 WHERE id > 1 OR id < 1;

7. 分页优化

原则：类似分页功能的SQL，建议先用主键关联，然后返回结果集

1
2
3
4
5
6
7

-- 不推荐：直接分页
SELECT * FROM large_table LIMIT 1000000, 20;

-- 推荐：使用主键关联
SELECT t1.* FROM large_table t1
JOIN (SELECT id FROM large_table LIMIT 1000000, 20) t2
ON t1.id = t2.id;

6. 网站集群架构上的优化

6.1 多实例部署

服务器上跑多实例，2-4个：

1
2
3
4
5

# 配置多个MySQL实例
/data/mysql/3306/
/data/mysql/3307/
/data/mysql/3308/
/data/mysql/3309/

优势：

• 充分利用服务器资源
• 隔离不同业务
• 提高资源利用率

6.2 主从复制优化

主从复制一主五从，采用mixed模式：

1
2

# 使用mixed模式
binlog_format=MIXED

建议：

• 尽量不要跨机房同步：网络延迟大
• 尽量远程写本地读：降低延迟
• 定期检查主从一致性：使用pt-table-checksum

6.3 数据一致性检查

定期使用pt-table-checksum、pt-table-sync来检查并修复MySQL主从复制的数据差异：

1
2
3
4
5
6
7
8
9
10
11
12
13

# 检查主从数据一致性
pt-table-checksum \
    --host=192.168.70.160 \
    --user=root \
    --password=Bgx123.com \
    --databases=testdb

# 修复数据差异
pt-table-sync \
    --execute \
    --sync-to-master \
    h=192.168.70.161,u=root,p=Bgx123.com \
    D=testdb,t=t1

6.4 业务拆分

6.4.1 搜索功能拆分

业务拆分：搜索功能，like “%老男孩%”，一般不要用MySQL数据库

推荐方案：

• 使用Elasticsearch
• 使用Solr
• 使用专业的搜索引擎

6.4.2 NoSQL持久化存储

业务拆分：某些业务应用NoSQL持久化存储

推荐方案：

• Memcachedb：键值存储
• Redis：内存数据库
• TTServer：Tokyo Tyrant

适用场景：

• 粉丝关注关系
• 好友关系
• 缓存数据
• 会话存储

6.5 缓存架构

数据库前端必须要加cache：

推荐方案：

• Memcached：分布式内存缓存
• Redis：内存数据库，支持持久化

应用场景：

• 用户登录信息
• 商品查询结果
• 热点数据
• 会话数据

6.6 数据静态化

动态的数据静态化：

方案：

1. 整个文件静态化：生成静态HTML文件
2. 页面片段静态化：缓存页面片段

优势：

• 减少数据库压力
• 提高访问速度
• 降低服务器负载

6.7 数据库集群与读写分离

数据库集群与读写分离：

架构：

• 一主多从：一个主库，多个从库
• 读写分离：通过程序或dbproxy进行集群读写分离

实现方式：

• 应用层实现：代码中区分读写
• 中间件实现：MyCat、ProxySQL等

6.8 分库分表

单表超过800万，拆库拆表：

拆分方式：

• 人工拆表拆库：按业务拆分
  • 登录库
  • 商品库
  • 订单库

拆分策略：

• 垂直拆分：按业务模块拆分
• 水平拆分：按数据量拆分
• 分库分表：同时进行库和表的拆分

6.9 备份策略

选择从库进行备份：

1
2
3

# 在从库上进行备份，不影响主库
mysqldump -h192.168.70.161 -uroot -pBgx123.com \
    --all-databases > backup.sql

对数据库进行分库分表备份：

1
2
3
4

# 按库备份
for db in $(mysql -uroot -pBgx123.com -e "SHOW DATABASES" | grep -v Database); do
    mysqldump -uroot -pBgx123.com $db > ${db}.sql
done

7. 流程、制度优化

7.1 数据库变更流程

任何一次认为数据库记录的更新，都要走一个流程：

7.1.1 人的流程

流程：建表增删改字段插入记录 → 开发 → 核心开发 → 运维或DBA

步骤：

1. 开发提出需求
2. 核心开发审核
3. DBA审核和优化
4. 运维执行变更

7.1.2 测试流程

流程：内网测试 → IDC测试 → 线上执行

步骤：

1. 内网测试环境：验证功能正确性
2. IDC测试环境：模拟生产环境
3. 线上执行：在维护窗口执行

7.2 客户端管理

客户端管理，PHPMYADMIN：

安全建议：

• 限制PHPMYADMIN访问IP
• 使用HTTPS访问
• 定期更新PHPMYADMIN版本
• 使用强密码

8. 安全优化

8.1 文件权限

启动程序设置700，属主和用户组为mysql：

1
2
3
4
5
6
7

# 设置MySQL目录权限
chmod 700 /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql

# 设置数据目录权限
chmod 700 /data/mysql
chown -R mysql.mysql /data/mysql

8.2 用户密码

为MySQL超级用户root设置密码：

1
2

-- 设置root密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('StrongPassword123!');

如果要求严格可以删除root用户，创建其它管理用户：

1
2
3
4
5
6

-- 创建管理用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 删除root用户（谨慎操作）
DROP USER 'root'@'localhost';

8.3 备份脚本安全

登录时尽量不要在命令行暴露密码：

1
2
3
4
5
6
7
8
9

# 不推荐：密码在命令行
mysql -uroot -p'password' -e "SHOW DATABASES;"

# 推荐：使用配置文件
mysql --defaults-file=/root/.my.cnf -e "SHOW DATABASES;"

# 配置文件权限
chmod 600 /root/.my.cnf
chown root.root /root/.my.cnf

备份脚本如果有密码，给设置为700，属主和用户组为mysql或root：

1
2
3

# 设置备份脚本权限
chmod 700 /root/backup.sh
chown root.root /root/backup.sh

8.4 数据库清理

删除默认存在的test库：

1
2

-- 删除test库
DROP DATABASE IF EXISTS test;

初始删除无用的用户，只保留：

1
2
3
4
5
6
7

-- 保留必要的用户
-- root@127.0.0.1
-- root@localhost

-- 删除其他用户
DROP USER ''@'localhost';
DROP USER ''@'%';

8.5 用户授权

**授权用户对应的主机不要用%**：

1
2
3
4
5

-- 不推荐
GRANT ALL ON *.* TO 'user'@'%';

-- 推荐：限制特定IP或网段
GRANT ALL ON *.* TO 'user'@'192.168.70.%';

权限不要给all，最小化授权：

1
2
3
4
5

-- 不推荐
GRANT ALL ON *.* TO 'user'@'192.168.70.%';

-- 推荐：最小权限
GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'user'@'192.168.70.%';

从库只给select：

1
2

-- 从库用户只给SELECT权限
GRANT SELECT ON *.* TO 'readonly'@'192.168.70.%';

不要一个用户管所有的库，尽量专库专用户：

1
2
3

-- 推荐：专库专用户
GRANT ALL ON app_db.* TO 'app_user'@'192.168.70.%';
GRANT ALL ON blog_db.* TO 'blog_user'@'192.168.70.%';

8.6 日志清理

清理mysql操作日志文件：

1
2
3
4
5

# 清理历史命令
rm -f ~/.mysql_history

# 或者设置权限
chmod 600 ~/.mysql_history

8.7 开发安全

禁止开发获取到web连接的密码：

• 密码由DBA管理
• 开发使用只读账户
• 密码不写入代码

禁止开发连接操作生产对外的库：

• 开发只能连接测试环境
• 生产环境严格控制访问
• 使用跳板机访问

8.8 PHPMYADMIN安全

PHPMYADMIN安全配置：

1
2
3
4
5
6
7
8
9

// 限制访问IP
$cfg['Servers'][$i]['AllowDeny']['order'] = 'deny,allow';
$cfg['Servers'][$i]['AllowDeny']['rules'] = array(
    'deny % from all',
    'allow 192.168.70.% from all'
);

// 使用HTTPS
$cfg['PmaAbsoluteUri'] = 'https://phpmyadmin.example.com/';

8.9 网络安全

服务器禁止设置外网IP：

• 数据库服务器不配置外网IP
• 通过内网访问
• 使用VPN或跳板机

8.10 SQL注入防护

防SQL注入（WEB）：

方案：

1. php.ini配置：

   1	magic_quotes_gpc = On

2. Web开发插件控件：

   • 使用PDO预处理语句
   • 使用参数化查询
   • 输入验证和过滤

3. WAF控制：

   • 使用Web应用防火墙
   • 过滤恶意SQL语句
   • 实时监控和防护

9. 优化检查清单

9.1 硬件优化检查

• CPU是否64位，数量是否足够
• 内存是否充足（推荐96G-128G）
• 是否使用SSD或高性能SAS盘
• RAID配置是否为RAID 10
• 网卡是否为千兆或万兆
• BIOS是否优化
• 阵列卡是否配置CACHE和BBU

9.2 操作系统优化检查

• 是否使用64位系统
• 是否关闭NUMA
• 文件系统是否为xfs
• I/O调度算法是否为deadline
• 内核参数是否优化
• TCP参数是否优化
• 网络缓冲区是否优化

9.3 MySQL优化检查

• 是否使用InnoDB引擎
• buffer_pool_size是否合理
• 日志参数是否配置
• 连接参数是否优化
• 慢查询日志是否开启
• Query Cache是否关闭

9.4 SQL优化检查

• 慢SQL是否定期分析
• 索引是否合理
• 是否有重复索引
• 是否有未使用的索引
• SQL语句是否优化

9.5 架构优化检查

• 是否使用主从复制
• 是否实现读写分离
• 是否使用缓存
• 是否进行业务拆分
• 是否需要分库分表

9.6 安全优化检查

• 文件权限是否正确
• 用户密码是否强密码
• 用户权限是否最小化
• 是否删除test库
• 是否清理无用用户
• 是否防护SQL注入

10. 总结

10.1 优化的核心价值

1. 性能提升：通过全方位优化提升系统性能
2. 资源利用：充分利用硬件和软件资源
3. 稳定性：提高系统稳定性和可用性
4. 成本控制：在有限资源下达到最佳性能

10.2 优化原则

1. 系统化优化：建立完整的优化体系
2. 自下而上：从硬件到应用层逐层优化
3. 数据驱动：基于监控数据进行优化
4. 持续优化：优化是一个持续的过程

10.3 架构师建议

1. 建立优化体系：制定完整的优化框架
2. 定期评估：定期评估系统性能
3. 监控告警：建立完善的监控告警系统
4. 文档维护：记录优化过程和结果
5. 团队协作：开发、运维、DBA协作优化

10.4 优化优先级

1. 硬件优化：基础，影响最大
2. 操作系统优化：系统层，影响显著
3. MySQL优化：数据库层，直接影响性能
4. SQL优化：应用层，影响查询性能
5. 架构优化：整体架构，影响系统性能
6. 安全优化：保障系统安全

相关文章：

• 第435集 MySQL主从复制架构详解
• 第434集 数据库主从复制
• 第433集 数据库备份恢复