MySQL 优化

一、优化工具

1.1 OS

CPU : top , htop, PS ,w ,glance
MEM : top , htop ,vmstat ,sar ,free
IO : iotop ,iostat ,iftop
FS : xfs_info ,df -Th , fdisk -l

1.2 数据库

show status
show variables
show index
show processlist
show slave status
show engine innodb status\G
desc /explain
slowlog
mysqladmin
扩展类深度优化:
pt系列
mysqlslap
sysbench
tpc-c
information_schema
performance_schema
sys

1.3 工具使用方法介绍

1）top

H
zsc 
[root@db01 ~]# cat /data/mysql/data/db01.pid 
1856
[root@db01 ~]# top -p 1856

%Cpu(s): 
0.0 us  : 用户态程序运行占用的cpu时间百分比
0.0 sy  : 内核态和资源调度时应用到的时间百分比
死锁 , 锁等待严重 ,并发连接多, 高并发 
99.9 id : 空闲的时间.
0.0 wa  : IO , 锁 , 内存空间释放

2）iotop，iostat，iftop
3）数据库层面

二、优化思路分解

2.1 硬件优化

cpu：

CPU 根据数据库类型
OLTP  IO密集型
OLAP  计算密集型

IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发
CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列）

CPU密集型： I 系列的，主频很高，核心少 
IO密集型：  E系列（至强），主频相对低，核心数量多

内存：

建议2-3倍cpu核心数量 （ECC）

磁盘：

SATA-III   SAS    Fc    SSD（sata） pci-e  ssd  Flash
主机 RAID卡的BBU(Battery Backup Unit)关闭

存储：

根据存储数据种类的不同，选择不同的存储设备
配置合理的RAID级别(raid1,raid5、raid10、热备盘) 
r0 :条带化 ,性能高
r1 :镜像，安全
r5 :校验+条带化，安全较高+性能较高（读），写性能较低 （适合于读多写少）
r10：安全+性能都很高，最少四块盘，浪费一半的空间（高IO要求）

项目:    10T +  MySQL  +  raid10 , LVM 

IOPS 

网络：

1、硬件买好的（单卡单口）
2、网卡绑定（bonding）， 选择主备模式, 交换机堆叠
以上问题，提前规避掉。

2.2 操作系统优化

swap调整

echo 0 >/proc/sys/vm/swappiness的内容改成0（临时）

vim /etc/sysctl.conf

vm.swappiness=0（永久）
sysctl -p
这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
修改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式

这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。
值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多
IO调度策略

centos 7 默认是deadline
cat   /sys/block/sda/queue/scheduler

#临时修改为deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler 
vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet


IO ：
    raid
    no lvm
    ext4或xfs
    ssd
    IO调度策略

提前规划好以上所有问题，减轻MySQL优化的难度。

2.3 应用端

1. 开发过程规范,标准
2. 减少烂SQL:不走索引,复杂逻辑,切割大事务.
3. 避免业务逻辑错误,避免锁争用.
这个阶段,需要我们DBA深入业务,或者要和开发人员\业务人员配合实现

三、MySQL 参数优化测试

虚拟机vm12.5，OS centos 6.9（系统已优化），cpu*4（I5 4440 3.1GHZ）,MEM*4GB ,HardDisk:SSD

模拟数据库数据

drop database if exists oldboy;
create database oldboy charset utf8mb4 collate utf8mb4_bin;
use oldboy;
create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t_100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;

插入100w条数据

call rand_data(1000000);
commit;

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='FGCD'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose

五、优化细节

5.1 参数优化

MAX_connections

1）简介

Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

2）判断依据

show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
show status like 'Max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 101   |
    +----------------------+-------+

3）修改方式

vim /etc/my.cnf 
Max_connections=1024

• 1.开启数据库时，我们可以临时设置一个比较大的测试值
• 2.观察show status like ‘Max_used_connections’； 变化、
• 3.如果max_used_connections跟max_connections 相同，那么就是max_used_connections 设置过低或者超过服务器的负载上限了，低于10%则设置过大

back_log

1）简介

mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它

2）依据

show full processlist

发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值

3）修改方式

vim /etc/my.cnf 
back_log=1024

wait_timeout和interactive_timeout

1）简介

wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数

interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为720

wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用

2）设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低
3）修改方式

wait_timeout=60
interactive_timeout=1200

长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。

key_buffer_size

1）简介

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
1.此参数与myisam表的索引有关
2.临时表的创建有关（多表连接、子查询中、union）

在有以上查询语句出现的时候，需要创建临时表，用完之后就会被丢弃
临时表有两种创建方式：
1.内存中————key_buffer_size
2.磁盘上————ibdata1(5.6) ibtmp1(5.7)

2）设置依据

注：key_buffer_size 支队myisam 表起作用，即使不适用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值
可以使用检查状态值created_tmp_disk_tables得知：

mysql> show status like "created_tmp%";

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

通常的，我们习惯以Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
或者以各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables
是否过多，从而认定当前服务器运行状态的优劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
控制在5%-10%以内

看以下例子：
在调用mysqldump备份数据时，大概执行步骤如下：

180322 17:39:33       7 Connect     root@localhost on
7 Query       /*!40100 SET @@SQL_MODE='' */
7 Init DB     guo
7 Query       SHOW TABLES LIKE 'guo'
7 Query       LOCK TABLES `guo` READ /*!32311 LOCAL */
7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query       show create table `guo`
7 Query       show fields from `guo`
7 Query       show table status like 'guo'
7 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`
7 Query       UNLOCK TABLES
7 Quit

其中，有一步是：show fields from ‘guo’ 从slow query记录的执行计划中，可以知道它也产生了Tmp_table_on_disk。
所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况产生的Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注
Created_tmp_disk_tables，但如果他的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。

3）配置方法

key_buffer_size=64M

max_connect_errors

max_connect_errors 是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或者通过flush hosts命令清空此host的相关信息 max_connect_error 的值 与性能并无太大关系。

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容

max_connect_errors=2000

sort_buffer_size

1）简介
每个需要进行排序的 线程分配该大小的一个缓冲区，增加这值加速

ORDER BY 
GROUP BY
distinct
union 

2）配置
Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
例如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存

修改/etc/my.cnf问阿金，在[mysqld]下面添加如下：
sort_buffer_size=1M

max_allowed_packet

1）简介
myslq根据配置文件会限制，server接收的数据包大小。
2）配置
有时候大的插入和更新会受max_allowed_packet 参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数

max_allowed_packet=32M

join_buffer_size

select a.name,b.name from a join b on a.id=b.id where xxxx
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
尽量在SQL方法进行优化，效果较为明显。
优化的方法：在on条件列加索引，至少应当是有MUL索引

thread_cache_size

1）简介

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

2）配置依据

通过比较connections和 threads_created状态的变量，可以看到这个变量的作用。
设置规则如下，1GB内存配置为8，2GB配置为16 ，3GB 配置为32 4GB 或者更高内存，可配置更大
服务器处理此用户的线程建辉缓存起来以响应下一个客户而不是销毁（前提是缓存数未达到上限）
试图连接到MySQL（不管是否连接成功）的连接数

mysql>  show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 2     |
| Threads_created   | 4783  |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected：代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created：带包从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpuSYS资源，可以适当增加配置文件中thread_cache_size值。
Threads_running：代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep的状态。

thread_cache_size=32

整理：
Threads_created：一般在架构设计阶段，会设置一个测试值，做压力测试。
结合zabbix监控，看一段时间内此状态的变化。
如果在一段时间内，Threads_created邹于平稳，说明对应参数设定是ok
如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）

innodb_buffer_pool_size

1）简介

对于InnoDB表来说，innodb_buffer_pool_size 的作用就相当于key_buffer_size 对于MyISAM 表的作用一样。

2）配置

InnoDb使用该参数指定大小的内存来缓冲数据和索引。
对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%，一般建议不要超过物理内存的70%。

innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit

1）简介
主要控制了innodb 将logbuffer 中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个

• 0：表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次
• 1：每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的ACID；
• 2：每次事务提交引起写入日志文件的动作，但每秒钟完成一个flush磁盘操作
  2）配置依据

实际测试发现，该值对插入数据的速度影响非常大，设置为2时，插入10000条记录只需要2秒，设置0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提升速度。
根据Mysql官方文档，在允许丢失最近部分事务的危险的前提下，可以吧该值设为0或2。

innodb_flush_log_at_trx_commit=1

双一标准的一个1

安全相关参数

Innodb_flush_method=(O_DIRECT, fsync…..)

1.fsync

• 在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
• 在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
  但如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘

2.Innodb_flush_method=O_DIRECT

• 在数据页需要持久化时，直接写入磁盘
• 在redo buffer 需要持久化时，首先将数据写入OS buffer 中，然后由os决定什么时候写入磁盘，但如果innodb_fiush_log_at_trx_commit=1的话，日志还是直接每次ommit直接写入磁盘

最安全模式：

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

最高性能：

innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

一般情况下，我们更偏于安全
“双一标准”

innodb_flush_log_at_trx_commit=1        ***************
sync_binlog=1                                   ***************
innodb_flush_method=O_DIRECT

binary log

log-bin=/data/mysql-bin

• binlog_cache_size = 2M

//为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是–1M，后者建议是：即 2–4M

• max_binlog_cache_size = 8M

//表示的是binlog 能够使用的最大cache 内存大小

• max_binlog_size= 512M

//指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。

• expire_logs_days = 7

//定义了mysql清除过期日志的时间。
二进制日志自动删除的天数。默认值为0,表示“没有自动删除”

log-bin=/data/mysql-bin
binlog_format=row 
sync_binlog=1

双一标准（基于安全的控制）：

sync_binlog=1   什么时候刷新binlog到磁盘，每次事务commit
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
show status like 'com_%';

1. 双一是两个参数
2. 第一个参数是控制redo刷写策略的
3. 第二参数是控制binlog
4. 首先介绍下redo的刷写策略
5. redo的书写策略,一共提供了三种,分别是:0,1,2
6. 所谓的双一就是参数设置为1.
7. 在5.5中默认是0,从5.6.x 开始默认是1.
8. 当参数为1时, 每次事务的提交都会引起redo日志文件写入OS buffer、立即fsync()磁盘的操作，确保了事务ACID的持久性；
9. 当参数为0,2时:
   0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
   2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。
10. 另外,redo的刷写,还会受到Innodb_flush_method=(O_DIRECT, fsync)
11. 我们核心业务1,0………
12. sync_binlog=1 ……..

innodb_thread_concurrency

1）简介

次参数用来设置innodb线程的并发数量，默认值为0表示不限制。

2）配置依据

在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，
例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，
性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，
建议设置innodb_thread_concurrency参数为80，以避免影响性能。
如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），
建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），
如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。
但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，
你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。
128 —–> top cpu

设置标准：

• 1.当前系统cpu使用情况，均不均匀
• 2.当前的连接数，有没有达到顶峰

show status like 'threads_%';
show processlist;

3）配置

innodb_thread_concurrency=8

方法：

• 1.看top，观察每个cpu的各自的负载情况
• 2.发现不平均，先设置参数为cpou个数，然后不断增加（一倍）这个数值
• 3.一直观察top状态，直到达到比较均匀的时候，书名已经到位了。

innodb_log_buffer_size
此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
innodb_log_buffer_size=128M

设定依据：

1. 大事务： 存储过程调用 CALL
2. 多事务

innodb_log_file_size = 100M *****
设置 ib_logfile0  ib_logfile1 
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
innodb_log_file_size = 100M

innodb_log_files_in_group = 3 *****
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

read_buffer_size = 1M **

MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

read_rnd_buffer_size = 1M **

MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

bulk_insert_buffer_size = 8M **
批量插入数据缓存大小，可以有效提高插入效率，默认为8M
tokuDB    percona
myrocks   
RocksDB
TiDB
MongoDB

六、总体优化参数

[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
enforce-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=4096M
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  

七、主从优化

1. 5.7以上的版本(忘记小版本)
2. 必须开启GTID
3. binlog必须是row模式

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

5.7

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心数作为标准
CHANGE MASTER TO
  MASTER_HOST='10.0.0.128',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_AUTO_POSITION=1;
start slave;