16、MySQL优化

作者: 一个反派人物 | 来源:发表于2021-07-11 14:09 被阅读0次

摘《mysql5.5从0开始》3
MySQL数据库多种优化方案
MYSQL分页limit速度太慢优化方法
MySQL相关文章索引（2）
11-mysqlSQL分析
MySQL性能调优
mysql性能优化-慢查询分析、优化索引和配置
MYSQL优化相关
MySQL优化系列16-优化MySQL的锁
第三个模块 MySQL-UUID、分词字典、MySQL全文索引

1 优化工具

1.1 系统层

1.1.1 硬件的功能

CPU：计算（主）和调度（次）
MEN：缓存和缓冲
IO：输入和输出

1.1.2 top命令

CPU整体利用率部分
重点关注us、sy、id、wa，这4个值相加为100，代表使用cpu时间的占比。

id：空闲的cpu占比
us：用户程序工作所占用的时间片占比
sy：内核工作（调度）花费cpu时间片占比
过高的原因：

内核本身bug
并发很高
锁

wa：cpu用来等待的时间片占比
过高的原因：

IO
等待处理大事物
锁

显示每个CPU的使用情况
在top界面按1

每个CPU利用率显示

1.1.3 iostat

iostat包含在sysstat包中
iostat -d 1每秒显示一次device的io信息

1.2 数据库层

基本优化：
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc / explain
slowlog

扩展类深度优化：
mysqlslap
sysbench
information_schema
performance_shcema

2 优化思路

2.1 主机、存储、网络

数据库类型
OLTP：IO密集型，增删改查都比较多。如线上系统，高并发。
OLAP：CPU密集型，查询多。如数据分析处理，需要CPU高算力。

CPU选型
OLTP：E系列，主频相对低，核心多
OLAP：I系列，主频高，核心少

内存
建议2-3倍CPU核心数

磁盘
主机RAID卡关闭BBU（Battery Backup Unit），关闭RAID卡缓存的备用电源

2.2 系统层面

2.2.1 关闭swap

系统默认在内存剩余到30%时，开始使用swap，通过以下命令可以查看配置

cat /proc/sys/vm/swappiness
30

临时修改

echo 0 > /proc/sys/vm/swappiness

永久修改

$ vim /etc/sysctl.conf
#末尾添加
vm.swappiness=0

#重新加载配置
$ sysctl -p

2.2.2 IO调度策略

Centos7默认是deadline，如果不是推荐修改为deadline

2.3 数据库参数

2.3.1 Max connections

2.3.1.1 简介

最大连接数，如果并发请求量比较大，可以调高这个值。因为Mysql会为每个请求提供缓冲区，连接数越多开销的内存越大，所以调整要适当。

2.3.1.2 判断依据

查看当前配置值，默认是151

show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.02 sec)

查看数据库启动以来，历史的最大连接数

show status like "Max_used_connections";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 9     |
+----------------------+-------+
1 row in set (0.01 sec)

2.3.1.3 修改方式

vim /etc/my.cnf
[mysqld]
...
Max_connections=1024

开启数据库时，可以临时设置一个比较大的测试值
观察show status like "Max_used_connections";变化
如果Max_used_connections跟max_connections相同，就需要调大max_connections值

2.3.2 back_log

2.3.2.1 简介

mysql能暂存的连接数量，当连接数达到max_connections后，能够暂存back_log个连接在堆栈中，等待有连接释放资源后进行连接，超过back_log数量的连接将不再应答。

2.3.2.2 判断依据show status like "Max_used_connections";

show full processlist;

当出现大量的待连接进程时，就需要加大back_log或加大max_connections

2.3.2.3 修改方式

vim /etc/my.cnf
[mysqld]
...
back_log=1024

2.3.3 wait_timeout和interactive_timeout

2.3.3.1 简介

wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数，如连接mysql后没做任何操作的连接为非交互连接。
interactive_timeout：指的是mysql在关闭一个交互的连接之前所要等待的秒数

2.3.3.2 设置建议

wait_timeout不建议设置太大，否则会造成连接打开时间过长，后续新连接会等待释放资源。
interactive_timeout一般不进行特别设置

2.3.3.3 修改方式

单位是秒

vim /etc/my.cnf更
[mysqld]
...
wait_timeout=600

2.3.4 key_buffer_size

2.3.4.1 简介

myisam表的索引缓冲区
临时表（使用union、order by、group by、distinct等语句会产生）的缓冲区

2.3.4.2 判断依据

db [(none)]>show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 44    |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

Created_tmp_disk_tables        硬盘上创建的临时表个数
Created_tmp_tables             内存上创建的临时表个数

控制Created_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables)在5%-10%以内

2.3.4.3 修改方式

默认单位是bit

vim /etc/my.cnf
[mysqld]
...
key_buffer_size=64M

2.3.5 max_connect_errors

连接错误的最大重试次数，防止暴力破解，超过指定次数后，服务器将禁止host的连接请求，直到重启服务或flush hosts清空host相关信息。

修改方式

vim /etc/my.cnf
[mysqld]
...
max_connect_errors=20

2.3.6 sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size

2.3.6.1 简介

排序缓冲区，多表关联缓冲区，读入（顺序扫描）缓冲区，随机读（查询操作）缓冲区大小。

2.3.6.2 配置依据

由于是connection级别的参数，不是越大越好，过大的设置+高并发会耗尽系统内存。

2.3.6.3 配置方法

一般不建议修改

vim /etc/my.cnf
[mysqld]
...
sort_buffer_size=1M
join_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M

2.3.7 max_allowed_packet

2.3.7.1 简介

限制server接收的网络数据包的大小

2.3.7.2 配置依据

有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，必须是1024bit的倍数

2.3.7.3 配置方法

vim /etc/my.cnf
[mysqld]
...
max_allowed_packet=256M

2.3.8 thread_cache_size

2.3.8.1 简介

服务器线程缓存，表示可以重新利用保存在缓存中线程的数量。当连接断开时，客户端线程将被放到缓存中以响应下一个客户，而不是销毁（前提是缓存数未达上限）。如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建。

2.3.8.2 配置依据

通过show status like "thread%";查看

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 7     |
| Threads_connected | 2     |
| Threads_created   | 9     |
| Threads_running   | 1     |
+-------------------+-------+

Threads_cached            代表当前此时此刻线程缓存中有多少空闲线程
Threads_connected         代表当前已经建立连接的数量，一个连接就是一个线程
Threads_created           为处理连接而创建的线程数，包含Threads_running
Threads_running           已连接，没有睡眠的线程

当Threads_cached 越来越少，但 Threads_connected 始终不降且 Threads_created 持续升高，这时可适当增加 thread_cache_size 的大小
可根据物理内存设置规则如下：
1G ―> 8
2G ―> 16
3G ―> 32
大于3G ―> 64

2.3.8.3 配置方法

vim /etc/my.cnf
[mysqld]
...
thread_cache_size=20

2.3.9 innodb_buffer_pool_size重要参数

2.3.9.1 简介

innodb表数据和索引的缓冲区

2.3.9.2 配置依据

InnoDB使用该参数指定大小的内存来缓冲数据和索引，一般建议不超过物理内存的70%。

2.3.9.3 配置方法

vim /etc/my.cnf
[mysqld]
...
innodb_buffer_pool_size=2048M

2.3.10 innodb_flush_log_at_trx_commit

双1标准之一

innodb_flush_log_at_trx_commit=1

2.3.11 innodb_thread_concurrency

用来设置innodb线程的并发数量，默认为0表示不限制。默认情况下mysql会最多使用64个CPU，如果用户线程并发数小于64可以保持默认值。大于的话可以适当调高这个数值。

2.3.12 innodb_log_buffer_size

此参数确定redo buffer大小，以M为单位。如果redo buffer较小，事务量比较大，redo buffer频繁落盘，会造成大量的IO操作。

2.3.13 innodb_log_file_size

REDO日志文件，事务日志文件的大小，ib_logfile0~ib_logfile1，以M为单位。

2.3.14 innodb_log_files_in_group

轮询的REDO日志文件个数，推荐设置为3。

3 完整配置文件

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enfore-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock

4 主从多线程MTS

5.7以上版本，必须开启GTID，binlog是row模式

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16                                #以CPU核心数作为标准
master_info_repository=TABLE                             #在表中存储master info
relay_log_info_repository=TABLE                          #在表中存储relay log
relay_log_recovery=ON

摘《mysql5.5从0开始》3
16性能优化 16.1 优化简介MySQL优化原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。SHOW ...
MySQL数据库多种优化方案
MySQL数据库多种优化方案硬件优化(物理机) CPU 8~16颗CPU ，2~4颗 mem 96G128G，...
MYSQL分页limit速度太慢优化方法
MySQL 百万级分页优化(Mysql千万级快速分页)（转） MYSQL分页limit速度太慢优化方法 MYSQL...
MySQL相关文章索引（2）
1.MySQL性能优化对MySQL语句的性能分析与优化 Mysql 监控 Innodb 阻塞状况 MySQL索引...
11-mysqlSQL分析
六星教育 - java-mysql优化1909 SQL优化所谓SQL优化：基于MySQL的优化器查询规则来优化S...
MySQL性能调优
MYSQL查询语句优化 mysql的性能优化包罗甚广：索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬...
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置分类：Mysql/postgreSQL 目录一、优化概述二、查...
MYSQL优化相关
mysql优化3大方向：优化mysql所在服务器内核（运维完成）对mysql配置参数进行优化（my.cnf），此...
MySQL优化系列16-优化MySQL的锁
备注:测试数据库版本为MySQL 8.0 一. 内部锁定方法本节讨论内部锁定;也就是说，在MySQL服务器内部执...
第三个模块 MySQL-UUID、分词字典、MySQL全文索引
论mysql5.7.13性能优化之索引优化mysql优化（1）show命令慢查询日志 explain profi...