MySQL之十---性能优化
参与优化:
数据库管理员
业务部门代表
应用程序架构师
应用程序设计人员
应用程序开发人员
硬件及系统管理员
存储管理员数据库管理员
业务部门代表
应用程序架构师
应用程序设计人员
应用程序开发人员
硬件及系统管理员
存储管理员
优化方向
安全优化（业务持续性）
性能优化（业务高效性）
优化的范围及思路
优化范围：
存储、主机和操作系统:
主机架构稳定性
I/O规划及配置
Swap
OS内核参数
网络问题
应用程序:（Index，lock，session）
应用程序稳定性和性能
SQL语句性能
串行访问资源
性能欠佳会话管理
数据库优化:（内存、数据库设计、参数）
内存
数据库结构(物理&逻辑)
实例配置
优化效果和成本的评估
优化工具的使用
系统层面的
CPU
top
cpu使用情况的平均值：
程序是如何使用CPU的？
系统给每个程序分配CPU的时候，以时间来划分表的。
CPU有效工作时间?
计算: 程序运行,数据处理
控制: 少量的关于申请资源和释放资源等
CPU无效工作时间
等待 IO
CPU各项指标说明
0.0 us
用户程序，在运行过程中，使用的CPU时间的占比。
我们希望的是越高越好，尽量控制在90%
0.0 sy
控制: 资源管理，内核的工作(系统调用)
sys高的原因:
bug ,中病毒了
锁的问题
99.9 id
CPU空间的时间占比
0.0 wa
CPU花在等待上的时间
wa高的原因：
锁
（raid，过度条带化）
索引
多cpu使用情况监控：
主要判断我们cpu多核心有没有被充分利用。
现象：单颗很忙，其他很闲，对于MySQL来讲，有可能是并发参数设定不合理导致的。
MEM
KiB Mem : 4028432 total, 3774464 free, 116240 used, 137728 buff/cache 名称介绍 total :总内存大小 free ：空闲的 used ：在使用的 buff/cache ：缓冲区 和 缓存
内存管理子系统：
slab Allocator
buddy system
程序=指令+数据
对于page cache来讲(OS buffer)
内存的可用空间的计算 free +buffer cache
内存回收(buffer)的方式:
写入磁盘
swap
对于数据库来讲：需要将swap屏蔽掉
swap
KiB Swap: 2097148 total, 2097148 free, 0 used. 3701464 avail Mem
Linux 6操作系统，默认回收策略（buffer cache），不立即回收策略
内存使用达到100%-60%时候，40% 会使用swap
Linux 7操作系统
内存使用达到100%-30%（70%）时候，才会使用swap
cat /proc/sys/vm/swappiness 30 echo 0 >/proc/sys/vm/swappiness 的内容改成0（临时） vim /etc/sysctl.conf 添加: vm.swappiness=0 sysctl -p
iostat 命令
dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1
现象说明
IO 高 cpu us 也高,属于正常现象
CPU us高 IO很低 ,MySQL 不在做增删改查,有可能是存储过程,函数,排序,分组,多表连接
Wait,SYS 高 , IO低:IO出问题了,锁等待过多的几率比较大.
IOPS：每秒磁盘最多能够发生的IO次数，这是个定值
频繁小事务,IOPS很高,达到阈值,可能IO吞吐量没超过IO最大吞吐量.无法新的IO了
存储规划有问题.
数据库优化工具
show status show variables show index show processlist show slave status show engine innodb status desc /explain slowlog 扩展类深度优化: pt系列 mysqlslap sysbench information_schema performance_schema sys
优化思路分解
硬件层面优化
硬件选配
真实的硬件（PC Server）: DELL R系列 ，华为，浪潮，HP，联想
云产品：ECS、数据库RDS、DRDS
IBM 小型机 P6 570 595 P7 720 750 780 P8
CPU:I,E
CPU：I、E
OLTP
OLAP
IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发
CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列）
CPU密集型： I 系列的，主频很高，核心少
IO密集型： E系列（至强），主频相对低，核心数量多
内存:ECC
建议2-3倍cpu核心数量 （ECC）
磁盘选择
SATA-III SAS Fc SSD（sata） pci-e ssd Flash Nvme flash
主机 RAID卡的BBU(Battery Backup Unit)关闭
存储
##最优选择:Raid10
根据存储数据种类的不同，选择不同的存储设备
配置合理的RAID级别(raid5、raid10、热备盘)
Raid0 :条带化 ,性能高
Raid1 :镜像，安全
Raid5 :校验+条带化，安全较高+性能较高（读），写性能较低 （适合于读多写少）
Raid10：安全+性能都很高，最少四块盘，浪费一半的空间（高IO要求）
网卡
单卡单口 bonding + 交换机堆叠
云服务器
ECS ,RDS,TDSQL,PolarxDB
关闭NUMA (是一种关于多个cpu如何访问内存的架构模型)
SMP(是指在一个计算机上汇集了一组处理器(多CPU),各CPU之间共享内存子系统以及总线结构。它是相对非对称多处理技术而言的、应用十分广泛的并行技术) NUMA(是一种关于多个cpu如何访问内存的架构模型)
bios级别
在bios层面numa关闭时，无论os层面的numa是否打开，都不会影响性能。
# numactl --hardware
available: 1 nodes (0) #如果是2或多个nodes就说明numa没关掉
OS grub级别
vi /boot/grub2/grub.cfg #/* Copyright 2010, Oracle. All rights reserved. */ default=0 timeout=5 hiddenmenu foreground=000000 background=ffffff splashimage=(hd0,0)/boot/grub/oracle.xpm.gz title Trying_C0D0_as_HD0 root (hd0,0) kernel /boot/vmlinuz-2.6.18-128.1.16.0.1.el5 root=LABEL=DBSYS ro bootarea=dbsys rhgb quiet linux16 /vmlinuz-0-rescue-c7c0850619f74e35b874b9b844bd79b7 root=UUID=14e7cbfb-31d3-4e6e-8527-f6c949493583 ro biosdevname=0 net.ifnames=0 rhgb quiet numa=off #关闭numa 查询度降低15-30% initrd /boot/initrd-2.6.18-128.1.16.0.1.el5.img
注:在os层numa关闭时,打开bios层的numa会影响性能，QPS会下降15-30%;
数据库级别:
法一:
mysql -S /data/3306/mysql.sock mysql> show variables like '%numa%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_numa_interleave | OFF | +------------------------+-------+
法二:
vi /etc/init.d/mysqld # Give extra arguments to mysqld with the my.cnf file. This script # may be overwritten at next upgrade. $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null & wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$? 将$bindir/mysqld_safe --datadir="$datadir"这一行修改为： /usr/bin/numactl --interleave all $bindir/mysqld_safe --datadir="$datadir"
--pid-file="$mysqld_pid_file_path" $other_args >/dev/null & wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?
阵列卡配置
raid10(推荐)
SSD或者PCI-E或者Flash
强制回写（Force WriteBack）
BBU 电池 ： 如果没电会有较大性能影响、定期充放电，如果UPS、多路电源、发电机。可以关闭。
关闭预读
有可能的话开启Cache(如果UPS、多路电源、发电机。)
vi /etc/rc.local
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
[root@db01 ~]# chmod +x /etc/rc.local
[root@db01 ~]# reboot
[root@db01 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@db01 ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]
网卡绑定
bonding技术，业务数据库服务器都要配置bonding继续。建议是主备模式。
交换机一定要堆叠。
存储多路径
使用独立存储设备的话，需要配置多路径。
linux 自带 : multipath
厂商提供 :
系统层面优化
更改文件句柄和进程数
内核优化
vim /etc/sysctl.conf vm.swappiness = 5 也可设置为0 #swap分区值 尽可能调低，不要使用swap，防止oom的出现 vm.dirty_ratio = 20 #绝对的脏数据限制，内存里的脏数据百分比不能超过这个值。如果脏数据超过这个数量，
新的IO请求将会被阻挡。 vm.dirty_background_ratio = 10 #内存可以填充“脏数据”的百分比 net.ipv4.tcp_max_syn_backlog = 819200 #增大SYN队列的长度，容纳更多连接 net.core.netdev_max_backlog = 400000 #该参数决定了，网络设备接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目。 net.core.somaxconn = 4096 #Linux kernel参数，表示socket监听的backlog(监听队列)上限 net.ipv4.tcp_tw_reuse=1 #表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭； net.ipv4.tcp_tw_recycle=0 #表示关闭TCP连接中TIME-WAIT sockets的快速回收. vim /etc/security/limits.conf #文件句柄 root - nofile 63000 #最大文件句柄数
防火墙
禁用selinux： /etc/sysconfig/selinux 更改SELINUX=disabled. iptables如果不使用可以关闭。可是需要打开MySQL需要的端口号
文件系统优化
推荐使用XFS文件系统 MySQL数据分区独立 ，例如挂载点为: /data
mount参数 defaults, noatime, nodiratime, nobarrier 如/etc/fstab：
/dev/sdb /data xfs
defaults #默认的参数
noatime,#显著提高文件系统的性能。
nodiratime,#一般不用设置
nobarrier 1 2 #可以保证文件系统在日志数据写入磁盘之后才写commit记录,但影响性能。重要数据应用慎用,有可能造成数据损坏。
不使用LVM (逻辑卷管理)
io调度
SAS : deadline
SSD&PCI-E: noop 生命电梯
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
数据库版本选择
1.稳定版：选择开源的社区版的稳定版GA版本。
2.选择mysql数据库GA版本发布后6个月-12个月的GA双数版本，大约在15-20个小版本左右。 3.选择前后几个月没有大的BUG修复的版本，而不是大量修复BUG的集中版本。 4.要考虑开发人员开发程序使用的版本是否兼容你选的版本。 5.作为内部开发测试数据库环境，跑大概3-6个月的时间。 6.优先企业非核心业务采用新版本的数据库GA版本软件。
7.向DBA高手请教，或者在技术氛围好的群里和大家一起交流，使用真正的高手们用过的好用的GA版本产品。 8.最终建议： 8.0.20是一个不错的版本选择。向后可以选择双数版。
数据库三层结构及核心参数优化
连接层
max_connections=1000（3000max）#MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值 max_connect_errors=999999 # 每个客户端连接最大的错误允许数量，
当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息 wait_timeout=600 # 连接超时时间 interactive_wait_timeout=3600 #交互式的连接超时时间 net_read_timeout = 120 #网络连接读传输数据包 net_write_timeout = 120 #网络连接写传输数据包 max_allowed_packet= 32M #服务器端限制server接受的数据包大小 back_log # 可以在堆栈中的连接数量
Server层
query_cache_size： #查询缓存 sql_safe_updates =1 #安全模式 使用where子句,并且where子句中列必须为prefix索引列或者limit语句 也可同时使用where语句和limit slow_query_log =ON #慢日志开启 slow_query_log_file =/data/3307/slow.log #慢日志路径 long_query_time =1 # 慢查询时间，超过1秒为慢查询 log_queries_not_using_indexes =ON # 开启记录没有使用索引查询语句 log_throttle_queries_not_using_indexes = 10 # #设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间 sort_buffer = 1M #不走索引排序缓存区 join_buffer = 1M # 联合查询操作所能使用的缓冲区大小 read_buffer = 1M # MySQL 读入缓冲区大小 read_rnd_buffer = 1M #MySQL 的随机读（查询操作）缓冲区大小 tmp_table = 16M #临时表 heap_table = 16M #是Oracle的默认数据表存储结构，数据行是按照“随机存取”的方式进行管理。 max_execution_time = 28800 #一条语句花费在执行的时间长度 lock_wait_timeout = 60 默认一年 #触发锁等待60s lower_case_table_names =1 # 不区分大小写 thread_cache_size =64 # 默认(8)控制mysql缓存客户服务线程的数量 log_timestamps =SYSTEM #5.7版本新增时间戳所属时区参数，默认记录UTC时区的时间戳到慢查询日志，应修改为记录系统时区 init_connect ="set names utf8" #客户端字符集 event_scheduler =OFF #开启定时器 secure-file-priv =/tmp #指定导出目录 binlog_expire_logs_seconds =2592000(必须调) # binlog过期的时间 sync_binlog =1 # 双一标准 log-bin =/data/3307/mysql-bin #二进制日志路径 log-bin-index=/data/3307/mysql-bin.index #指定binlog文件的索引文件，这个文件管理了所有的binlog文件的目录 max_binlog_size=500M #指定binlog日志文件的大小 binlog_format =ROW #确保使用DRS进行MySQL的增量迁移或同步时，不会导致任务失败甚至数据丢失 max_binlog_cache_size=8M //表示的是binlog能够使用的最大cache内存大小 binlog_cache_size=2M //为每个session分配的内存，在事务过程中用来存储二进制日志的缓存，提高记录bin-log的效率。
存储引擎层
transaction-isolation="READ-COMMITTED" # *****RC级别 innodb_data_home_dir =/xxx #目录 innodb_log_group_home_dir=/xxx #文件存放路径 innodb_log_file_size =2048M #确定数据日志文件的大小，更大的设置可以提高性能 innodb_log_files_in_group=3 #为提高性能，MySQL可以以循环方式将日志文件写到多个文件 innodb_flush_log_at_trx_commit=2 # ***** 双一另一个标准gtid 每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。 innodb_flush_method =O_DIRECT # ***** 数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲。 innodb_io_capacity =1000 # *****设定io ps并发能力 固态盘1000 innodb_io_capacity_max =4000 #设定io ps并发最大能力 innodb_buffer_pool_size=64G # ***** 指定大小的内存来缓冲数据和索引 innodb_buffer_pool_instances=4 # ***** 调整缓存池数量 innodb_log_buffer_size =64M # *****确定日志文件所用的内存大小，缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。 innodb_max_dirty_pages_pc=85 # ***** innodb主线程刷新缓存池中的数据，使脏数据比例小于85% innodb_lock_wait_timeout =10 # ***** InnoDB事务在被回滚之前可以等待一个锁定的超时秒数 默认值是50秒 innodb_open_files=63000 # ***** 限制Innodb能打开的表的数据 默认300 innodb_page_cleaners =4 #从缓冲池实例刷新脏页的页面清理线程数 innodb_sort_buffer_size =64M #创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小。 innodb_print_all_deadlocks =1 #死锁的打开 innodb_rollback_on_timeout =ON #回滚业务，回滚当前事务或者回滚当前语句 innodb_deadlock_detect =ON #开启死锁自动检测机制 innodb_write_io_threads = 4 #写线程 默认四个，负责数据块的写入 innodb_read_io_threads = 4 #读线程 默认四个，负责数据块的读取 innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求
复制
relay_log =/opt/log/mysql/blog/relay #定义relay_log的位置和名称 relay_log_index =/opt/log/mysql/blog/relay.index #定义relay_log的位置和名称； max_relay_log_size =500M #最大的relay_log文件大小； relay_log_recovery =ON #当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。 rpl_semi_sync_master_enabled =ON #打开半同步复制 rpl_semi_sync_master_timeout =1000 #等待ack的超时时间 rpl_semi_sync_master_trace_level =32 #日志打印参数 rpl_semi_sync_master_wait_for_slave_count=1 #必须等待多少个slave的ack信息，才可以提交 rpl_semi_sync_master_wait_no_slave=ON #在没有slave时，是否选择等待。 rpl_semi_sync_master_wait_point=AFTER_SYNC #控制等待ack的逻辑处于整个事务提交过程的哪个阶段，目前支持两种模式 AFTER_SYNC AFTER_COMMIT 提高主从一致性 rpl_semi_sync_slave_enabled =ON #slave半同步的开关 rpl_semi_sync_slave_trace_level =32 #日志相关 binlog_group_commit_sync_delay =1 #影响commit提交时间 binlog_group_commit_sync_no_delay_count=1000 #影响commit提交时间 gtid_mode =ON #gtid开启 enforce_gtid_consistency =ON #开启gtid事务 skip-slave-start =1 #防止复制随着mysql启动而自动启动 read_only =ON #普通用户只读权限 super_read_only =ON #root用户管理员权限 log_slave_updates =ON #只有开启log_slave_updates，从库binlog才会记录主库同步的操作日志。 server_id =2330602 #服务器唯一ID report_host=10.0.0.51 report_port =3306 slave_parallel_type =LOGICAL_CLOCK #SQL线程事务 slave_parallel_workers =4 #决定并发处理的线程数 master_info_repository =TABLE #避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错 relay_log_info_repository=TABLE #决定slave同步的位置信息记录在哪里
其它
客户端配置：
[mysql]
no-auto-rehash #扫描数据库下的所有文件，打开所有的文件
开发规范
字段规范
-
每个表建议在
30个字段以内。 -
需要存储
emoji字符的，则选择`utf8mb4字符集。 -
机密数据，加密后存储。
-
整型数据，默认加上
UNSIGNED。 -
存储
IPV4地址建议用bigINT UNSIGNE`，查询时再利用`INET_ATON()、INET_NTOA()函数转换。 -
如果遇到
BLOB、TEXT大字段单独存储表或者附件形式存储。 -
选择尽可能小的数据类型，用于节省磁盘和内存空间。
-
存储浮点数，可以放大倍数存储。
-
每个表必须有主键，
INT/BIGINT并且自增做为主键，分布式架构使用sequence序列生成器保存。 -
每个列使用
not null，或增加默认值。
SQL语句规范
-
去掉不必要的括号 如：
((a AND b) AND c OR (((a AND b) AND (c AND d))))修改成(a AND b AND c) OR (a AND b AND c AND d) -
去掉重叠条件 如：
(a<b AND b=c) AND a=5修改成b>5 AND b=c AND a=5如：(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)修改成B=5 OR B=6 -
避免使用
not in`、`not exists` 、`<>、like %% -
多表连接，小表驱动大表
-
减少临时表应用，优化
order by、group by、union、distinct、join等 -
减少语句查询范围，精确查询条件
-
多条件，符合联合索引最左原则
-
查询条件减少使用函数、拼接字符等条件、条件隐式转换
-
union all替代union -
减少
having子句使用 -
如非必须不使用
for update语句
update和delete，开启安全更新参数
减少inset ... select语句应用
使用load 替代insert录入大数据
导入大量数据时，可以禁用索引、增大缓冲区、增大redo文件和buffer、关闭autocommit、RC级别可以提高效率
优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询
limit 5000000,10 limit 10 , 200(跳过前10行)
DDL执行前要审核（邮件审核需要备份）
多表连接语句执行前要看执行计划
索引优化
-
非唯一索引按照“
i或者is字段名称字段名称[_字段名]”进行命名。 -
唯一索引按照“
u字段名称字段名称[_字段名]”进行命名。 -
索引名称使用小写。
-
索引中的字段数不超过
5个。 -
唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。
-
没有唯一键或者唯一键不符合5中的条件时，使用自增id作为主键。
-
唯一键不和主键重复。
-
索引选择度高的列作为联合索引最左条件
-
ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。 -
单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过
DBA评估。 查询性能问题无法解决的，应从产品设计上进行重构。 -
使用
EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。
UPDATE、DELETE语句需要根据WHERE条件添加索引。
对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法。
下面的表增加一列url_crc32，然后对url_crc32建立索引，减少索引字段的长度，提高效率。
CREATE TABLE all_url(ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, url VARCHAR(255) NOT NULL DEFAULT 0, url_crc32 INT UNSIGNED NOT NULL DEFAULT 0, index idx_url(url_crc32));
合理创建联合索引（避免冗余）,
(a,b,c)相当于(a)、(a,b) (a,b,c)。
-
合理利用覆盖索引，减少回表。
-
减少冗余索引和使用率较低的索引
mysql> select * from schema_unused_indexes; mysql> select * from schema_redundant_indexes\G
锁优化
全局锁 Global Read lock
全局读锁。 加锁方法： FTWRL，flush tables with read lock. 解锁方法： unlock tables; 出现场景： mysqldump --master-data xtrabackup（8.0之前早期版本）等备份时。 属于类型： MDL（matedatalock）层面锁 影响情况： 加锁期间，阻塞所有事务写入，阻塞所有已有事务commit。 MDL，等待时间受lock_wait_timeout=31536000
检测方法
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; mysql> select * from performance_schema.metadata_locks; mysql> select OBJECT_SCHEMA ,OBJECT_NAME ,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS , OWNER_THREAD_ID,OWNER_EVENT_ID from performance_schema.metadata_locks; mysql> show processlist; mysql> select * from sys.schema_table_lock_waits;
经典故障1:
5.7 xtrabackup/mysqldump备份时数据库出现hang状态，所有查询都不能进行
session1: 模拟一个大的查询或事务
mysql> select id,sleep(100) from city where id<100 for update;
session2: 模拟备份时的FTWRL
mysql> flush tables with read lock;
此时发现命令被阻塞 session3: 发起查询，发现被阻塞
mysql> select * from world.city where id=1 for update;
结论:备份时，一定要选择业务不繁忙期间，否则有可能会阻塞正常业务。
故障2：
5.7版本 innobackupex备份全库，进程死了，`mysql里就是全库读锁，后边insert 全阻塞了
show processlist ----> select * from performance_schema.metadata_locks; ---> pending ---->granted ----> OWNER_THREAD_ID: 66 ----> select * from threads \G ----->processlist_Id ----> show processlist -----> kill processlist_Id
row lock wait(行锁定等待)
介绍
record lock 、gap、next lock 都是基于索引加锁,与事务隔离级别有关。
行锁监控及分析
-
确认有没有锁等待:
show status like 'innodb_row_lock%' select * from information_schema.innodb_trx;
-
查询锁等待详细信息
select * from sys.innodb_lock_waits; ----> blocking_pid(锁源的连接线程)
-
通过连接线程找SQL线程
select * from performance_schema.threads;
-
通过SQL线程找到 SQL语句
select * from performance_schema.events_statements_history;
优化方向
-
优化索引
-
减少事务的更新范围
-
RC
拆分语句： 例如：
update t1 set num=num+10 where k1 <100; k1 是辅助索引,record lock gap next
改为:
select id from t1 where k1 <100; ---> id: 20,30,50 update t1 set num=num+10 where id in (20,30,50);
架构优化
高可用架构： MHA+ProxySQL+GTID MGR\InnoDB Cluster PXC 读写分离： ProxySQL、`MySQL-router NoSQL: Redis+sentinel,Redis Cluster MongoDB RS/MongoDB SHARDING Cluster ES
安全优化
使用普通nologin用户管理MySQL 合理授权用户、密码复杂度及最小权限、系统表保证只有管理员用户可访问。 删除数据库匿名用户 锁定非活动用户 MySQL尽量不暴露互联网,需要暴露互联网用户需要设置明确白名单、替换MySQL默认端口号、使用ssl连接 优化业务代码，防止SQL注入。
常用工具介绍
PT（percona-toolkits）工具的应用:
pt工具安装
[root@master ~]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm
常用工具使用介绍
pt-archiver归档表 场景：
面试题： 亿级的大表，delete批量删除100w左右数据。 面试题： 定期按照时间范围，进行归档表。
重要参数
--limit 100 每次取100行数据用pt-archive处理
--txn-size 100 设置100行为一个事务提交一次，
--where 'id<3000' 设置操作条件
--progress 5000 每处理5000行输出一次处理信息
--statistics 输出执行过程及最后的操作统计。（只要不加上--quiet，默认情况下pt- archive都会输出执行过程的）
--charset=UTF8 指定字符集为UTF8—这个最后加上不然可能出现乱码。
--bulk-delete 批量删除source上的旧数据(例如每次1000行的批量删除操作)
注意: 需要归档表中至少有一个索引,做好是
`where条件列有索引
案例：
归档到数据库
db01 [test]>create table test1 like t100w; pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --dest h=10.0.0.51,D=test,t=test1,u=oldguo,p=123 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 1000 --statistics
清理数据
pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --where 'id<10000' --purge --limit=1 --no-check-charset
把数据导出到外部文件，但是不删除源表里的数据
pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 - -where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat" pt-osc
-
场景： 修改表结构、索引创建删除 不能加快速度，但能减少业务影响（锁）。 面试题 ：
pt-osc工作流程： 1、检查更改表是否有主键或唯一索引，是否有触发器 2、检查修改表的表结构，创建一个临时表，在新表上执行ALTER TABLE语句
create table bak like t1;
alter table bak add telnum char(11) not null;
3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作
create trigger
a
b
c
从源表拷贝数据到临时表，在拷贝过程中，对源表的更新操作会写入到新建表中
将临时表和源表rename（需要元数据修改锁，需要短时间锁表）
删除源表和触发器，完成表结构的修改。
pt-osc工具限制
源表必须有主键或唯一索引，如果没有工具将停止工作 如果线上的复制环境过滤器操作过于复杂，工具将无法工作 如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作 如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作 当表使用外键时，如果未使用--alter-foreign-keys-method参数，工具将无法执行 只支持Innodb存储引擎表，且要求服务器上有该表1倍以上的空闲空间。
pt-osc之alter语句限制
不需要包含alter table关键字，可以包含多个修改操作，使用逗号分开，如"drop clolumn c1, add column c2 int"
不支持rename语句来对表进行重命名操作
不支持对索引进行重命名操作
如果删除外键，需要对外键名加下划线，如删除外键fk_uid, 修改语句为"DROP FOREIGN KEY _fk_uid"
pt-osc之命令模板
--execute表示执行
--dry-run表示只进行模拟测试
表名只能使用参数t来设置，没有长参数
pt-online-schema-change \ --host="127.0.0.1" \ --port=3358 \ --user="root" \ --password="root@root" \ --charset="utf8" \ --max-lag=10 \ --check-salve-lag='xxx.xxx.xxx.xxx' \ --recursion-method="hosts" \ --check-interval=2 \ --database="testdb1" \ t="tb001" \ --alter="add column c4 int" \ --execute
应用实战：
pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51
--alter "add column state int not null default 1" D=test,t=t100w --print --execute pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51
--alter "add index idx(num)" D=test,t=t100w --print --execute
pt-table-checksum
场景:校验主从数据一致性
创建数据库
Create database pt CHARACTER SET utf8;
创建用户checksum并授权
create user 'checksum'@'10.0.0.%' identified with mysql_native_password by 'checksum'; GRANT ALL ON *.* TO 'checksum'@'10.0.0.%' ; flush privileges;
参数:
--[no]check-replication-filters：是否检查复制的过滤器，默认是yes，建议启用不检查模式。 --databases | -d：指定需要被检查的数据库，多个库之间可以用逗号分隔。 --[no]check-binlog-format：是否检查binlog文件的格式，默认值yes。建议开启不检查。因为在默认的row格式下会出错。 --replicate:把checksum的信息写入到指定表中。 --replicate-check-only：只显示不同步信息 pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t1 h=10.0.0.51,u=checksum,p=checksum,P=3307 #!/bin/bash date >> /root/db/checksum.log pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test --tables t1 -u'checksum' -p'checksum' -h'10.0.0.51' >> /tmp/checksum.log date >> /root/db/checksum.log
pt-table-sync
主要参数介绍
--replicate ：指定通过pt-table-checksum得到的表. --databases : 指定执行同步的数据库。 --tables ：指定执行同步的表，多个用逗号隔开。 --sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。 h= ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。 u= ：帐号。 p= ：密码。 --print ：打印，但不执行命令。 --execute ：执行命令。 pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3307 h=10.0.0.52,u=checksum,p=checksum,P=3307 --print pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3307 h=10.0.0.52,u=checksum,p=checksum,P=3307 --execute
pt-show-grants
作用: 用户和权限信息迁移。
pt-show-grants -h10.0.0.51 -P3307 -uchecksum -pchecksum -- Grants dumped by pt-show-grants -- Dumped from server 10.0.0.51 via TCP/IP, MySQL 5.7.28-log at 2020-05-15 17:11:06 -- Grants for 'checksum'@'10.0.0.%' CREATE USER IF NOT EXISTS 'checksum'@'10.0.0.%'; ALTER USER 'checksum'@'10.0.0.%'
IDENTIFIED WITH 'mysql_native_password' AS
'*E5E390AF1BDF241B51D9C0DBBEA262CC9407A2DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT ALL PRIVILEGES ON *.* TO 'checksum'@'10.0.0.%'; -- Grants for 'mysql.session'@'localhost' CREATE USER IF NOT EXISTS 'mysql.session'@'localhost'; ALTER USER 'mysql.session'@'localhost'
IDENTIFIED WITH 'mysql_native_password'
AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK; GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost'; GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost'; GRANT SUPER ON *.* TO 'mysql.session'@'localhost'; -- Grants for 'mysql.sys'@'localhost' CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost'; ALTER USER 'mysql.sys'@'localhost'
IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE'
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK; GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost'; GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost'; GRANT USAGE ON *.* TO 'mysql.sys'@'localhost'; -- Grants for 'repl'@'10.0.0.%' CREATE USER IF NOT EXISTS 'repl'@'10.0.0.%'; ALTER USER 'repl'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password'
AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%'; -- Grants for 'root'@'10.0.0.%' CREATE USER IF NOT EXISTS 'root'@'10.0.0.%'; ALTER USER 'root'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password'
AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%'; -- Grants for 'root'@'localhost' CREATE USER IF NOT EXISTS 'root'@'localhost'; ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password'
AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
mysql死锁监测
pt-deadlock-logger h='127.0.0.1' --user=root --password=123456
主键冲突检查
pt-duplicate-key-checker --database=world h='127.0.0.1' --user=root --password=123456
pt-kill 语句
参数说明
--daemonize 放在后台以守护进程的形式运行； --interval 多久运行一次，单位可以是s,m,h，d等默认是s –不加这个默认是5秒 --victims 默认是oldest,只杀最古老的查询。这是防止被查杀是不是真的长时间运行的查询，他们只是长期等待 这种种匹配按时间查询，杀死一个时间最高值。 --all 杀掉所有满足的线程 --kill-query 只杀掉连接执行的语句，但是线程不会被终止 --print 打印满足条件的语句 --busy-time 批次查询已运行的时间超过这个时间的线程； --idle-time 杀掉sleep 空闲了多少时间的连接线程，必须在--match-command sleep时才有效—也就是匹配使用 -- –match-command 匹配相关的语句。 ----ignore-command 忽略相关的匹配。 这两个搭配使用一定是ignore-commandd在前 match-command在后， --match-db cdelzone 匹配哪个库 command有：Query、Sleep、Binlog Dump、Connect、Delayed insert、Execute、Fetch、Init DB、Kill、Prepare、Processlist、Quit、Reset stmt、Table Dump
---杀掉空闲链接sleep 5秒的 SQL 并把日志放到/home/pt-kill.log文件中
/usr/bin/pt-kill --user=用户名 --password=密码 --match-command Sleep --idle-time 5 --victim all --interval 5 --kill --daemonize -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
---查询SELECT 超过1分钟路
/usr/bin/pt-kill --user=用户名 --password=密码 --busy-time 60 --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
--- Kill掉 select IFNULl.*语句开头的SQL
pt-kill --user=用户名 --password=密码 --victims all --busy-time=0 --match-info="select IFNULl.*" --interval 1 -S /tmp/mysqld.sock --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
----kill掉state Locked
/usr/bin/pt-kill --user=用户名 --password=密码 --victims all --match-state='Locked' --victim all --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
---kill掉 a库，web为10.0.0.11的链接
pt-kill --user=用户名 --password=密码
--victims all --match-db='a'
--match-host='10.0.0.11' --kill
--daemonize --interval 10 -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print-log=/tmp/pt-kill.log &
---指定哪个用户kill
pt-kill --user=用户名 --password=密码
--victims all --match-user='root'
--kill --daemonize --interval 10 -S /home/zb/data/my6006/socket/mysqld.sock
--pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
---查询SELECT 超过1分钟路
pt-kill --user=用户名 --password=密码 --busy-time 60 --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
----kill掉 command query | Execute
pt-kill --user=用户名 --password=密码
--victims all --match-command= "query|Execute" --interval 5
--kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
其他
pt-find ---找出几天之前建立的表
pt-slave-restart -----主从报错，跳过报错
pt-summary ---整个系统的的概述
pt-mysql-summary ---MySQL的表述，包括配置文件的描述
pt-duplicate-key-checker ---检查数据库重复索引
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_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，可调优为7200。
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）
在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃
临时表有两种创建方式：
内存中------->key_buffer_size
磁盘上------->ibdata1(5.6)
ibtmp1 (5.7）
（2）设置依据
通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。
mysql> show variables like "key_buffer_size%"; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------+ 1 row in set (0.00 sec) mysql> mysql> show status like "key_read%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Key_read_requests | 10 | | Key_reads | 2 | +-------------------+-------+ 2 rows in set (0.00 sec)
mysql>
一共有10个索引读取请求，有2个请求在内存中没有找到直接从硬盘中读取索引
控制在 5%以内 。
注：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)
mysql>
通常地，我们习惯以 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