第二章 mysql的全维度优化总结

一、数据库设计阶段优化

1. 规范化设计（前期）

合理使用范式（第一/第二/第三范式），减少数据冗余。
对性能要求极高的场景可适当反范式设计（如冗余字段、合并表）。

2. 数据类型选择

使用合适的字段类型（如 INT < BIGINT，CHAR < VARCHAR）。
避免使用 TEXT、BLOB、ENUM。
时间字段建议使用 DATETIME 或 TIMESTAMP。

3. 主键设计

尽量使用自增主键或雪花算法生成的整型主键，避免使用 UUID。
InnoDB 下主键是聚簇索引，主键设计直接影响行存储。

二、索引优化

1. 索引类型

单列索引：简单查询适用。
联合索引：满足多条件过滤；遵循“最左前缀”原则。
覆盖索引：SELECT 的字段包含于索引中，无需回表。
唯一索引：适用于唯一约束字段，提高查询性能。

2. 建立索引建议

高频查询字段、WHERE 条件、JOIN 条件、ORDER BY 字段。
避免对低选择度字段（如性别、状态）建索引。
避免过多索引，更新成本高。

3. 索引失效情况

使用 !=、OR、函数包裹列。
隐式类型转换（例如字符串比较整型）。
索引列未出现在最左前缀中。

4.避免死锁和锁等待

减少锁范围：尽量让锁的范围小（如只锁定必要的行），避免表锁的使用。
减少事务执行时间：事务越长，锁定的资源时间越长，容易导致锁等待甚至死锁。尽量减少事务中的查询或更新操作时间。

三、SQL 编写与优化

1. 查询优化

明确列：SELECT col1, col2 优于 SELECT *。
WHERE 条件过滤尽可能精准。
使用 EXISTS 替代 IN（大数据量场景）。
避免在 WHERE 中使用函数、计算。
控制分页 LIMIT 偏移量，使用主键或游标分页优化大偏移。
避免在 WHERE 条件中使用 OR：OR 会导致全表扫描，尽量使用 IN 或分解查询。

2. JOIN 优化

明确 JOIN 类型（INNER、LEFT、RIGHT）。
控制结果集大小，避免笛卡尔积。
JOIN ON 字段需加索引。

3. 子查询与临时表

尽量使用 JOIN 替代子查询。
子查询可用 WITH 或中间临时表替代。

四、慢查询分析与调优

1. 开启慢查询日志

slow_query_log = 1
long_query_time = 1 # 设置为超过1秒的查询记录到日志
log_queries_not_using_indexes = 1

2. 分析工具

EXPLAIN：查看 SQL 执行计划。
SHOW PROFILE：分析 SQL 各阶段耗时。
pt-query-digest：分析慢日志，定位热点 SQL。

3. 重点关注指标

type：访问类型（ALL > index > range > ref > const > system）。
rows：扫描行数，越少越好。
Extra：如出现 Using filesort / Using temporary，要优化。

五、表结构与数据优化

1. 分区与分表（大表处理）

分区表：按 RANGE、LIST、HASH 分区。
垂直分表：按字段功能划分表。
水平分表：按范围、哈希对行分表。

2. 数据归档与冷热分离

通过历史表、归档表，减少主表数据量。
可结合时间戳字段自动归档。

3. 表统计信息更新

保持 ANALYZE TABLE 更新统计信息，避免执行计划失真。

六、事务与锁优化

1. 事务控制

使用合适的隔离级别（建议：REPEATABLE READ）。
控制事务粒度和时间，避免长事务。

2. 锁优化

避免全表锁，尽量使用行锁。
WHERE 条件中包含索引，避免锁升级。
使用 SELECT ... FOR UPDATE 精确加锁。

七、服务器与参数优化

1. 调整 InnoDB Buffer Pool

Buffer Pool 的大小：InnoDB 的 Buffer Pool 用于缓存数据和索引，配置合理的缓存大小是优化 MySQL 性能的关键之一。建议 Buffer Pool 设置为物理内存的 70-80%。
```
innodb_buffer_pool_size = 4G  # 根据内存大小调整
```

2. 查询缓存（Query Cache）

关闭查询缓存：在 MySQL 5.7 及以后的版本，查询缓存功能逐渐被弃用，因为它在高并发场景下容易成为瓶颈。因此，建议将其关闭。
```
query_cache_type = 0
```

3. 线程池优化

调整连接线程：对于高并发的业务场景，可以调整 MySQL 的最大连接数（max_connections）和每个连接线程的最大数量。
```
max_connections = 500
```

4. 磁盘 I/O 优化

调整 innodb_flush_log_at_trx_commit：innodb_flush_log_at_trx_commit 控制日志何时写入磁盘。设置为 2 时，可以降低磁盘 I/O，提升性能，但会稍微增加数据丢失的风险。
```
innodb_flush_log_at_trx_commit = 2
```

5. 调整日志文件大小

设置合适的 redo log 大小：innodb_log_file_size 配置 redo log 文件大小，建议根据写操作的频率和磁盘情况设置适合的大小，过小的 redo log 会频繁触发检查点，影响性能。
```
innodb_log_file_size = 512M
```

6. 调整连接超时

避免无效连接长时间占用：可以设置 MySQL 的连接超时参数，避免连接长时间闲置，造成资源浪费。
```
wait_timeout = 600
interactive_timeout = 600
```

八、监控与告警

1. 常用监控项

QPS、TPS、慢查询数、锁等待、连接数。
InnoDB Buffer Pool 命中率、Redo Log 写入、IO 等。

2. 工具推荐

Prometheus + Grafana 监控。
MySQL Enterprise Monitor、Percona Toolkit。
阿里云、腾讯云 RDS 提供内建监控。

九、分布式与高可用架构优化

1. 主从复制

异步/半同步复制。
延迟监控，读写分离配置优化。

2. 高可用方案

MGR、Galera Cluster、MySQL InnoDB Cluster。
外部方案：Keepalived + MHA / Orchestrator。

3. 分库分表中间件

ShardingSphere、MyCAT、Vitess。

十、调优流程总结图（建议保存）

[系统设计] -> [表结构设计] -> [SQL 编写规范] -> [索引优化] -> [慢SQL分析]
                                ↓
                     [执行计划分析 (EXPLAIN)]
                                ↓
                   [SQL重写 / 索引调整 / 表结构调整]
                                ↓
         [事务控制] + [锁优化] + [数据归档] + [参数调优]
                                ↓
                         [持续监控与告警]

配置文件详解

配置文件结构

MySQL 配置文件由多个部分组成，每个部分以方括号 [section_name] 开头，指定该部分适用于哪个程序或工具。常见的部分包括：

[client]：配置所有客户端程序的默认连接参数。
[mysqld]：配置 MySQL 服务器（mysqld 守护进程）的参数。
[mysqld_safe]：配置 mysqld_safe 启动脚本的参数。
[mysql]：配置 mysql 命令行客户端的参数。
[mysqldump]：配置 mysqldump 工具的参数。
[mysqladmin]：配置 mysqladmin 工具的参数。
[mysqlhotcopy]：配置 mysqlhotcopy 工具的参数。
[mysqlimport]：配置 mysqlimport 工具的参数。
[mysqlshow]：配置 mysqlshow 工具的参数。
[server]：在某些版本中，与 [mysqld] 部分相同或类似，用于配置服务器参数。

常用参数详解

以下是各部分中常用参数的说明：

`[client]` 和 `[mysql]` 部分

port：指定连接的端口号，默认是 3306。
socket：指定 Unix 套接字文件的路径，用于本地连接。
default-character-set：设置默认的字符集，如 utf8mb4。

`[mysqld]` 部分（服务器配置）

基本设置

port：服务器监听的端口号，默认是 3306。
socket：Unix 套接字文件的路径。
basedir：MySQL 的安装目录。
datadir：数据库文件的存储目录。
tmpdir：临时文件的存储目录。
skip-name-resolve：禁用 DNS 解析，加快连接速度，但要求授权时使用 IP 地址。

连接管理

max_connections：允许的最大并发连接数。
max_connect_errors：在阻止主机连接之前允许的失败连接次数。
wait_timeout：关闭非交互式连接之前的等待时间（秒）。
interactive_timeout：关闭交互式连接之前的等待时间（秒）。

字符集设置

character-set-server：服务器默认的字符集，如 utf8mb4。
collation-server：服务器默认的校对规则，如 utf8mb4_general_ci。
init_connect：每次连接时执行的 SQL 语句，常用于设置字符集。

日志设置

log-error：错误日志文件的路径。
slow_query_log：启用慢查询日志。
slow_query_log_file：慢查询日志文件的路径。
long_query_time：定义慢查询的阈值（秒）。
log_queries_not_using_indexes：记录未使用索引的查询。

缓冲区和缓存设置

key_buffer_size：用于 MyISAM 表索引的缓冲区大小。
query_cache_size：查询缓存的大小（MySQL 5.7 已废弃）。
tmp_table_size：内存临时表的最大大小。
max_heap_table_size：用户创建的内存表的最大大小。

InnoDB 存储引擎设置

innodb_buffer_pool_size：InnoDB 缓冲池的大小，建议设置为物理内存的 60%～80%。
innodb_log_file_size：InnoDB 重做日志文件的大小。
innodb_log_buffer_size：InnoDB 日志缓冲区的大小。
innodb_flush_log_at_trx_commit：事务提交时日志的刷新策略（0、1、2）。
innodb_file_per_table：每个表使用独立的表空间。
innodb_flush_method：InnoDB 的刷新方法，如 O_DIRECT。

二进制日志和复制设置

server-id：服务器的唯一标识符，用于主从复制。
log-bin：启用二进制日志。
binlog_format：二进制日志的格式（STATEMENT、ROW、MIXED）。
expire_logs_days：二进制日志的过期天数。

安全性设置

skip-networking：禁用 TCP/IP 连接，仅允许本地连接。
secure-file-priv：限制 LOAD DATA 和 SELECT INTO OUTFILE 的文件操作目录。

配置示例

以下是一个典型的 my.cnf 配置文件示例：

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4

[mysqld]
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /var/lib/mysql
tmpdir = /tmp
skip-name-resolve

max_connections = 500
max_connect_errors = 1000
wait_timeout = 600
interactive_timeout = 600

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

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

key_buffer_size = 64M
query_cache_size = 0
tmp_table_size = 64M
max_heap_table_size = 64M

innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

server-id = 1
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

skip-networking = 0
secure-file-priv = /var/lib/mysql-files

[client]
# 客户端连接配置
port = 3306                         # 默认连接端口
socket = /var/lib/mysql/mysql.sock # 与服务器通信的套接字文件路径

[mysqld]
# ========================
# 基础参数
# ========================
port = 3306                          # 服务器监听端口
user = mysql                         # 运行 MySQL 的系统用户
basedir = /usr/local/mysql           # MySQL 安装路径（按实际路径设置）
datadir = /var/lib/mysql             # 数据文件目录
socket = /var/lib/mysql/mysql.sock   # 套接字文件路径
pid-file = /var/run/mysqld/mysqld.pid # PID 文件路径

# ========================
# 网络连接相关
# ========================
bind-address = 0.0.0.0               # 监听所有 IP 地址
max_connections = 1000               # 最大并发连接数
max_connect_errors = 10000           # 单个 IP 的连接错误上限（防止暴力攻击）
wait_timeout = 300                   # 空闲连接等待超时（单位秒）
interactive_timeout = 300            # 交互式连接超时（单位秒）
skip-name-resolve                    # 禁用 DNS 解析，加快连接速度（需使用 IP 授权）

# ========================
# 字符集设置
# ========================
character-set-server = utf8mb4       # 默认字符集（推荐 UTF-8）
collation-server = utf8mb4_general_ci# 字符集排序规则
init_connect = 'SET NAMES utf8mb4'   # 客户端连接初始化字符集

# ========================
# 错误日志与慢查询日志
# ========================
log-error = /var/log/mysql/mysql-error.log   # 错误日志文件路径
slow_query_log = 1                            # 开启慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log # 慢查询日志路径
long_query_time = 1                           # 查询超过 1 秒记录为慢查询
log_queries_not_using_indexes = 0            # 是否记录未用索引的查询（优化期可开启）

# ========================
# 缓存与临时表
# ========================
query_cache_size = 0                 # 查询缓存大小（已废弃建议为0）
query_cache_type = 0
table_open_cache = 4096             # 表缓存大小（提高频繁访问的表命中率）
table_definition_cache = 4096       # 表定义缓存（影响 INFORMATION_SCHEMA 访问速度）
open_files_limit = 65535            # 允许打开的最大文件数（需配合系统 ulimit）

# ========================
# InnoDB 引擎设置
# ========================
default-storage-engine = InnoDB     # 默认存储引擎
innodb_buffer_pool_size = 8G        # InnoDB 缓存池（建议设置为物理内存的 60%~80%）
innodb_log_file_size = 1G           # redo 日志文件大小
innodb_log_buffer_size = 64M        # redo 缓冲区大小
innodb_flush_log_at_trx_commit = 2  # redo 刷盘策略：0（最快）1（最安全）2（折中）
innodb_file_per_table = 1           # 每张表使用独立的表空间文件
innodb_flush_method = O_DIRECT      # 使用 O_DIRECT 避免双重缓存
innodb_io_capacity = 200            # IO 能力指标（SSD 可设大如 1000+）
innodb_read_io_threads = 4          # 读 IO 线程数
innodb_write_io_threads = 4         # 写 IO 线程数

# ========================
# Binlog 设置（主从/备份）
# ========================
server-id = 1                       # 唯一的服务器 ID（主从复制需要）
log-bin = /var/log/mysql/mysql-bin  # 开启 binlog 并指定路径
binlog_format = ROW                # binlog 格式（ROW 支持更精确的复制）
expire_logs_days = 7               # binlog 保留天数
sync_binlog = 1                    # binlog 同步策略（1：每次提交同步最安全）

# ========================
# GTID 复制（主从复制需用）
# ========================
gtid_mode = ON                     # 开启 GTID 模式
enforce_gtid_consistency = ON      # 强制 GTID 一致性
log_slave_updates = 1              # 从库记录 relay-log 中的 SQL 到 binlog
skip_slave_start = 1               # 启动时不自动开启从库复制线程

# ========================
# 安全性
# ========================
secure_file_priv = ""              # 限制 `LOAD DATA` 和导出文件路径，留空表示不限制

# ========================
# 性能监控插件（可选）
# ========================
performance_schema = ON            # 开启性能模式（内存开销小，建议开启）
performance_schema_instrument = '%=on'

# ========================
# 临时表配置
# ========================
tmp_table_size = 128M              # 内存临时表最大值
max_heap_table_size = 128M         # Heap 表最大值（影响内部内存临时表）

# ========================
# 开发调试用（生产慎用）
# ========================
# general_log = ON
# general_log_file = /var/log/mysql/mysql-general.log

调整建议

内存分配：根据服务器的物理内存，合理分配 innodb_buffer_pool_size，以提高 InnoDB 的性能。
连接数：根据应用的并发需求，调整 max_connections 和相关的超时设置。
日志管理：启用慢查询日志和二进制日志，有助于性能分析和数据恢复。
字符集：统一使用 utf8mb4，以支持更多字符和表情符号。
安全性：根据实际需求，配置 skip-networking 和 secure-file-priv，增强安全性。

第二章 mysql的全维度优化总结

一、数据库设计阶段优化

1. 规范化设计（前期）

2. 数据类型选择

3. 主键设计

二、索引优化

1. 索引类型

2. 建立索引建议

3. 索引失效情况

4.避免死锁和锁等待

三、SQL 编写与优化

1. 查询优化

2. JOIN 优化

3. 子查询与临时表

四、慢查询分析与调优

1. 开启慢查询日志

2. 分析工具

3. 重点关注指标

五、表结构与数据优化

1. 分区与分表（大表处理）

2. 数据归档与冷热分离

3. 表统计信息更新

六、事务与锁优化

1. 事务控制

2. 锁优化

七、服务器与参数优化

1. 调整 InnoDB Buffer Pool

2. 查询缓存（Query Cache）

3. 线程池优化

4. 磁盘 I/O 优化

5. 调整日志文件大小

6. 调整连接超时

八、监控与告警

1. 常用监控项

2. 工具推荐

九、分布式与高可用架构优化

1. 主从复制

2. 高可用方案

3. 分库分表中间件

十、调优流程总结图（建议保存）

配置文件详解

配置文件结构

常用参数详解

[client] 和 [mysql] 部分

[mysqld] 部分（服务器配置）

基本设置

连接管理

字符集设置

日志设置

缓冲区和缓存设置

InnoDB 存储引擎设置

二进制日志和复制设置

安全性设置

配置示例

调整建议

`[client]` 和 `[mysql]` 部分

`[mysqld]` 部分（服务器配置）