性能优化优先顺序：

  1. 数据库结构设计和SQL语句
  2. 数据库存储引擎的选择和参数配置
  3. 系统选择和优化
  4. 硬件升级

服务器硬件

CPU

  • 64位的CPU一定要工作在64位的系统下
  • 32位操作系统对单线程运行的MySQL有很大的限制
  • 频率决定单个SQL处理快慢；核数决定并行处理SQL数量，即QPS
  • 对于并发比较高的场景，CPU的数量比频率重要
  • 对于CPU密集型场景或比较复杂的SQL，CPU频率越高越好
  • Web类应用，核数优先级大于频率
1
2
3
4
5
6
7
8
# 查看CPU是否超频
cat /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

# 查看CPU最大频率
cat /sys/devices/system/cpu/cpu*/cpufreq/cpuinfo_max_freq

# 查看CPU可用策略
cat /sys/devices/system/cpu/cpu*/cpufreq/scaling_available_governors

内存

  • 内存选择时，应选择主板所能使用的最高频率的内存
  • 内存的大小对性能很重要，所以要尽可能的大
  • 在数据量大于内存时，内存不能缓存全部的数据，提升内存对性能有较明显的提升

I/O子系统

优先级：PCIe > SSD > 传统磁盘 + RAID10 > 磁盘 > SAN

传统磁盘需要注意：

  • 存储容量
  • 传输速度
  • 访问时间
  • 主轴转速
  • 物理尺寸

RAID级别的选择：主库建议RAID10，从库建议RAID0、RAID5。

等级 特点 是否冗余 盘数 读 写
RAID0 便宜，快速，危险 否 N 快 快
RAID1 高速读，简单，安全 是 2 快 慢
RAID5 安全，成本折中 是 N+1 快 取决于最慢的盘
RAID10 贵，高速，安全 是 2N 快 快

相比于机械磁盘，固态磁盘有以下几个特点：

  1. 更好的随机读写性能
  2. 更好的支持并发
  3. 更容易损坏

使用场景：

  • 适用于存在大量随机IO的场景

  • 适用于解决单线程负载的IO瓶颈

Linux系统

内核相关参数：/etc/sysctl.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 增加连接数和队列长度
net.core.somaxconn=65535
net.core.netdev_max_backlog=65535
net.ipv4.tcp_max_syn_backlog=65535

# 加快tcp连接回收速度
net.ipv4.tcp_fin_timeout=10
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1

# 网络接收和发送缓冲区大小
net.core.wmem_default=87380
net.core.wmem_max=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216

# tcp存活探测
net.ipv4.tcp_keepalive_time=120
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_probes=3

# 单个共享内存段的最大值
kernel.shmmax=4294967296
# 注意：
# 1. 这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个Innodb缓冲池的大小
# 2. 对于64位linux系统，可取的最大值为物理内存值-1byte，建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可。

# 内存交换分区
vm.swappiness=5
# 注意：
# 1. 内存使用率超过95%才会使用交换分区
# 2. 内存交换分区，在内存不足时会对性能产生明显的影响

# 脏页
vm.dirty_background_ratio=50
vm.dirty_ratio=80
vm.dirty_writeback_centisecs = 2000
vm.dirty_expire_centisecs = 12000

资源限制：/etc/security/limits.conf

需要重启操作系统才能生效

1
2
3
4
5
6
7
8
9
10
# 控制打开文件数限制
* soft nofile 65535
* hard nofile 65535

# PS：
# *        表示对所有用户有效
# soft     表示当前系统生效
# hard     表名系统中所能设定的最大值
# nofile   表示锁限制的资源是打开文件的最大数目
# 65535    限制的数量

磁盘调度策略：/sys/block/devname/queue/scheduler

Linux 内核的 4 大 IO 调度算法：

  • Noop IO scheduler（电梯式调度策略）：NOOP实现了一个FIFO队列，它像电梯的工作方法一样对I/O请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。NOOP倾向于饿死读而利于写，因此NOOP对于闪存设备、RAM及嵌入式系统是最好的选择。
  • Deadline IO scheduler（截止时间调度策略）：Deadline确保了在一个截止时间内服务请求，这个截止时间是可调整的，而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象，Deadline对数据库类应用是最好的选择。
  • Anticipatory IO scheduler（预料I/O调度策略）：本质上与Deadline一样，但在最后一次读操作后，要等待6ms，才能继续进行对其它I/O请求进行调度。它会在每个6ms中插入新的I/O操作，而会将一些小写入流合并成一个大写入流，用写入延时换取最大的写入吞吐量。AS适合于写入较多的环境，比如文件服务器，AS对数据库环境表现很差。
  • CFQ IO scheduler（Completely Fair Queuing, 完全公平排队）：它试图为竞争块设备使用权的所有进程分配一个请求队列和一个时间片，在调度器分配给进程的时间片内，进程可以将其读写请求发送给底层块设备，当进程的时间片消耗完，进程的请求队列将被挂起，等待调度。对于通用的服务器来说，CFQ是较好的选择。
1
2
3
4
5
6
7
8
# 查看磁盘当前调度策略（方括号里面的是当前选定的调度策略）
cat /sys/block/sda/queue/scheduler
[mq-deadline] kyber bfq none

# 修改磁盘调度策略
echo deadline > /sys/block/sda/queue/scheduler
# or
echo deadline | sudo tee /sys/block/sda/queue/scheduler

文件系统

各操作系统推荐适用如下文件系统，性能最优：

  • Windows：NTFS
  • Linux：XFS

若Linux使用ext4文件系统，则关闭文件系统的操作时间记录可以提升读写性能，/etc/fstab 内容如下：

1
/dev/sda1    /     ext4   noatime,nodiratime,data=writeback 1 1

MySQL体系结构

image-20211012113231107

存储引擎是针对于表的，而不是针对于库的（一个库中的不同表可以使用不同的存储引擎，但不建议这样操作。）

MySQL存储引擎

MyISAM

Innodb

MySQL配置参数

MySQL获取配置信息途径

  • 命令行参数

    1
    mysqld_safe --datadir=/data/sql_data

  • 配置文件

    1
    2
    3
    # 查看配置文件优先级
    mysqld --help --verbose | grep -A 1 'Default options'
    # /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

MySQL配置参数的作用域

  • 全局参数

    1
    2
    3
    4
    5
    6
    7
    # 动态设置全局参数
    set global 参数名 = 参数值;
    # or
    set @@global.参数名 = 参数值;

    # 查看全局参数
    SHOW GLOBAL VARIABLES LIKE 'Innodb%';

  • 会话参数

    1
    2
    3
    4
    # 动态设置会话参数
    set [session] 参数名 = 参数值;
    # or
    set @@session.参数名 = 参数值;

内存相关配置参数

  • 确定可使用的内存上线

  • 确定MySQL的每个连接使用的内存，占用内存大小为：buffer_size * 连接数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # 查询排序时，立即分配指定大小内存给线程
    sort_buffer_size

    # 连接缓冲区
    join_buffer_size

    # MyISAM全表扫描时，分配指定大小，需要为4K的倍数
    read_buffer_size

    # 索引缓冲区
    read_rnd_buffer_size

  • 确定需要为操作系统保留多少内存

  • 如何为缓存池分配内存，总内存 -（每个线程所需要的内存 * 连接数）- 系统保留内存

    1
    2
    3
    4
    innodb_buffer_pool_size

    # 主要用于MyISAM存储引擎
    key_buffer_size

IO相关配置参数

Innodb I/O相关配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 单个事务日志大小
innodb_log_file_size

# 事务日志文件个数
innodb_log_files_in_group 
# 事务日志总大小 = Innodb_log_files_in_group * Innodb_log_file_size
# 一个日志文件写满后才写下一个，Innodb_log_files_in_group意义不大，事务繁忙时，Innodb_log_file_size建议调大（32M ~ 128M）

# 日志缓冲区，一般不需要设置太大，基本1s就会刷新一次日志缓冲区，只需要保证可以缓冲1s的事务即可。建议32M ~ 128M即可
innodb_log_buffer_size

# Innodb刷新log的频率
#  - 0：每隔1秒钟会将log buffer中的数据写入到文件，同时通知文件系统进行文件同步的flush操作（该值会导致MySQL崩溃时至少丢失1s的事务）
#  - 1：默认值，每次事务的结束都会触发Log Thread将log buffer中的数据写入文件并通知文件系统同步文件（该值可以保证数据不丢失）
#  - 2：建议值，每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘（如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据）
innodb_flush_log_at_trx_commit

# Innodb刷新方式
#  - fsync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.
#  - O_DSYNC: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
#  - littlesync: This option is used for internal performance testing and is currently unsupported. Use at your own risk.
#  - nosync: This option is used for internal performance testing and is currently unsupported. Use at your own risk.
#  - O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.
#  - O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call after each write operation.
# 
# Prior to MySQL 5.7.25, this setting is not suitable for file systems such as XFS and EXT4, which require an fsync() system call to synchronize file system metadata changes. If you are not sure whether your file system requires an fsync() system call to synchronize file system metadata changes, use O_DIRECT instead.
# As of MySQL 5.7.25, fsync() is called after creating a new file, after increasing file size, and after closing a file, to ensure that file system metadata changes are synchronized. The fsync() system call is still skipped after each write operation.
# Data loss is possible if redo log files and data files reside on different storage devices, and an unexpected exit occurs before data file writes are flushed from a device cache that is not battery-backed. If you use or intend to use different storage devices for redo log files and data files, and your data files reside on a device with a cache that is not battery-backed, use O_DIRECT instead.
innodb_flush_method

# Innodb独立表空间，强烈建议使用该参数
#  - 0：关闭
#  - 1：开启
innodb_file_per_table

# Innodb双写缓存，避免文件系统写入错误是文件损坏，损耗约10%写性能，保证安全性
#  - 0：关闭
#  - 1：开启
innodb_doublewrite

MyISAM I/O相关配置

1
2
3
4
5
# 控制缓冲刷新到磁盘的频率
#  - OFF：每次写操作后刷新键缓冲区内的脏块到磁盘
#  - ON：只对在建表时指定了delay_key_write选项的表使用延迟刷新
#  - ALL：对所有MyISAM表都是用延迟键写入
delay_key_write

锁相关配置参数

Innodb 锁相关配置

1
2
3
4
5
6
7
8
9
10
11
12
13
## 自旋锁
# 自旋锁的优点：自旋锁要上锁时，如果需要等待其他线程释放锁，那么：
#   - 在等待锁的过程中会先线程会先自旋一段时间：
#     - 自旋阶段，线程不会放弃 CPU
#   - 自旋过后：
#     - 如果可以获取锁了，那么响应会比较快（自旋没发生上下文切换）
#     - 如果还需要等待锁，再用更高成本的方式进行锁等待
# CPU 使用率会随着自旋阶段变长而升高。
#
# 自旋阶段的时间长度
innodb_spin_wait_delay
# 自旋周期
innodb_sync_spin_loops

安全相关配置参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 自动清理binlog的天数，建议覆盖两次全备的间隔
expire_logs_days

# MySQL可以接受的包大小，建议32M。主从复制结构中，主库与从库需保持一致
max_allowed_packet

# 禁用DNS查找。使用该参数后，授权需要使用ip或网段，不能使用域名
skip_name_resolve

# 确保sysdate()返回确定性日期
sysdate_is_now

# 禁止非super权限用户的写权限。建议主从复制结构中，从库使用
read_only

# 禁用slave自动恢复
skip_slave_start

# MySQL所使用的SQL模式（对SQL语法检验严谨度，不建议在已经运行的生产环境中修改），常用值如下：
#  - STRICT_TRANS_TABLES：在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制。
#  - ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中。
#  - NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
#  - NO_ZERO_IN_DATE：在严格模式下,不允许日期和月份为零。
#  - NO_ZERO_DATE：设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
#  - ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL。
#  - NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户。
#  - NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常。
#  - PIPES_AS_CONCAT：将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似。
#  - ANSI_QUOTES：启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符。
sql_mode

其他常用配置参数

1
2
3
4
5
6
7
8
9
10
11
# MySQL刷新binlog到磁盘的方式
#  - 0：性能最好，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步。
#  - n：当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
sync_binlog

# 内存临时表大小，组合使用，两个参数值要一致，避免过大
tmp_table_size
max_heap_table_size

# 最大连接数。默认100，建议2000+
max_connections

参考文档

官方文档

实践

InnoDB存储引擎

MySQL监控

Linux优化