MySQL之15---全面优化
MySQL之15---全面优化
目录
- MySQL之15---全面优化
- 优化哲学
- 硬件优化
- 系统优化
- 软件版本选择
- 三层结构及参数优化
- 开发规范
- 索引优化
- 事务及锁优化
- 架构优化
- 安全优化
- PT（percona-toolkits）
- 性能验证
- 数据库优化参数详解
- max_connections
- back_log
- wait_timeout和interactive_timeout
- key_buffer_size
- query_cache_size
- max_connect_errors
- sort_buffer_size
- max_allowed_packet
- join_buffer_size
- thread_cache_size
- innodb_buffer_pool_size
- innodb_flush_log_at_trx_commit
- innodb_thread_concurrency
- innodb_log_buffer_size
- innodb_log_file_size = 100M
- innodb_log_files_in_group = 3
- read_buffer_size = 1M
- read_rnd_buffer_size = 1M
- bulk_insert_buffer_size = 8M
- binary log
- Innodb_flush_method=O_DIRECT
优化哲学
- 优化风险：
优化不总是对一个单纯的环境进行！还很可能是一个复杂的已投产的系统。
优化手段本来就有很大的风险，只不过你没能力意识到和预见到！
任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
对于优化来说解决问题而带来的问题控制在可接受的范围内才是有成果。
保持现状或出现更差的情况都是失败！
稳定性和业务可持续性通常比性能更重要！
优化不可避免涉及到变更，变更就有风险！
优化使性能变好，维持和变差是等概率事件！
优化不能只是数据库管理员担当风险，但会所有的人分享优化成果！
所以优化工作是由业务需要驱使的！！！
- 谁参与优化：
数据库管理员
业务部门代表
应用程序架构师
应用程序设计人员
应用程序开发人员
硬件及系统管理员
存储管理员
-
优化方向：
- 安全优化（业务持续性）
- 性能优化（业务高效性）
-
优化思路：
- 硬件优化
- 系统优化
- 软件版本选择
- 三层结构及参数优化
- 开发规范
- 索引优化
- 事务及锁优化
- 架构优化
- 安全优化
-
优化效果和成本的评估：
硬件优化
硬件选配
PC Server: DELL R系列，华为，浪潮，HP，联想，IBM
- CPU：
- CPU密集型：数据分析数据处理，OLAP，cpu密集型的业务。需要CPU高计算能力（I系列，IBM power系列）
- IO密集型：线上系统，OLTP，IO密集型的业务。高并发（E系列（至强），主频相对低，核心数量多）
- 内存：建议2-3倍cpu核心数量 （ECC）
- 磁盘：SAS/PCI-E SSD、Nvme flash
- RAID卡：RAID10
- 网卡：单卡单口，网卡绑定（bonding）主备模式
云服务器：ECS、数据库RDS、TDSQL、PolarxDB
网卡绑定（bonding）
balance-rr (mode=0)：默认, 有高可用 (容错) 和负载均衡的功能, 需要交换机的配置，每块网卡轮询发包 (流量分发比较均衡)
balance-alb (mode=6)：有高可用 ( 容错 )和负载均衡的功能，不需要交换机配置，流量分发到每个接口不是特别均衡
阵列卡配置
-
raid10(推荐)
-
PCI-E SSD/Flash
-
参数选择：参考
"Default Write Cache Policy"（即创建阵列时的"Write Policy"），写入缓存策略，仅当阵列卡配备有缓存时才可修改，否则固定为"Write Through"。
- 当设置为"Write Through"（直写）时，数据最终写入硬盘介质时才反馈写入完成，当阵列卡未配备有缓存时此项为默认值。
- 当设置为"Write Back"（回写）且阵列卡配备有备份单元（电池）时数据写入到阵列卡缓存时即反馈写入完成，此模式会提升阵列性能，当阵列卡配备有缓存时此项为默认值；但若未配备备份单元时会以"Write Through"模式操作。
- 当设置为"Always Write Back"（强制回写）时，不论缓存是否有备份单元（电池）都开启回写模式，此模式在服务器遇到意外停电时有较大数据丢失风险，请谨慎选用。
"BBU"，即备份单元状态，如阵列卡配备有电池(BBU)或者超级电容(CVM)的即会显示"Yes"，否则是"No"；
存储多路径
使用独立存储设备，需要配置多路径机制。
- linux 自带: Multipath，参考
- 厂商提供:
关闭NUMA
-
bios级别：在bios层面numa关闭时，无论os层面的numa是否打开，都不会影响性能。
-
OS grub级别：在os层numa关闭时，打开bios层的numa会影响性能，QPS会下降15-30%;
[root@db01 ~]# grep GRUB_CMDLINE_LINUX /etc/default/grub
GRUB_CMDLINE_LINUX="rd.lvm.lv=centos/root rd.lvm.lv=centos/swap biosdevname=0 net.ifnames=0 rhgb quiet"
[root@db01 ~]# sed -ri 's/(GRUB_CMDLINE_LINUX.*)"/\1 numa=off"/' /etc/default/grub
[root@db01 ~]# grep GRUB_CMDLINE_LINUX /etc/default/grub
GRUB_CMDLINE_LINUX="rd.lvm.lv=centos/root rd.lvm.lv=centos/swap biosdevname=0 net.ifnames=0 rhgb quiet numa=off"
# MBR 分区表重建GRUB配置文件
grub2-mkconfig -o /boot/grub2/grub.cfg
reboot
验证：
# 验证boot参数已配置
[root@db01 ~]# cat /proc/cmdline
BOOT_IMAGE=/vmlinuz-3.10.0-1127.19.1.el7.x86_64 root=/dev/mapper/centos-root ro rd.lvm.lv=centos/root rd.lvm.lv=centos/swap biosdevname=0 net.ifnames=0 rhgb quiet numa=off
# 验证available: 1 nodes (0)
[root@db01 ~]# numactl --hardware
available: 1 nodes (0) # 如果是2或多个nodes就说明numa没关掉
node 0 cpus: 0
node 0 size: 1980 MB
node 0 free: 1559 MB
node distances:
node 0
0: 10
- 数据库级别(8.0.20默认关闭):
# 查看
mysql> select @@innodb_numa_interleave;
+--------------------------+
| @@innodb_numa_interleave |
+--------------------------+
| 0 |
+--------------------------+
# 修改：
[root@db01 opt]# grep -A 1 '# may be overwritten at next upgrade.' /etc/init.d/mysqld
# may be overwritten at next upgrade.
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
修改为：
/usr/bin/numactl --interleave all $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
关闭大页内存（THP）
关闭THP方式一：
chmod +x /etc/rc.d/rc.local && cat >> /etc/rc.d/rc.local <<EOF
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
EOF
关闭THP方式二：（仅enabled是never，但HugePages禁用）
[root@db01 ~]# grep GRUB_CMDLINE_LINUX /etc/default/grub
GRUB_CMDLINE_LINUX="rd.lvm.lv=centos/root rd.lvm.lv=centos/swap biosdevname=0 net.ifnames=0 rhgb quiet"
[root@db01 ~]# sed -ri 's/(GRUB_CMDLINE_LINUX.*)"/\1 transparent_hugepage=never"/' /etc/default/grub
[root@db01 ~]# grep GRUB_CMDLINE_LINUX /etc/default/grub
GRUB_CMDLINE_LINUX="rd.lvm.lv=centos/root rd.lvm.lv=centos/swap biosdevname=0 net.ifnames=0 rhgb quiet transparent_hugepage=never"
# MBR 分区表重建GRUB配置文件
grub2-mkconfig -o /boot/grub2/grub.cfg
reboot
验证关闭THP
[root@db01 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@db01 ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]
关闭前
[root@db01 opt]# grep Huge /proc/meminfo
AnonHugePages: 141312 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
关闭后
[root@db01 opt]# grep Huge /proc/meminfo
AnonHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
系统优化
- 关闭 selinux
sed -i '/^SELINUX=/c SELINUX=disabled' /etc/selinux/config
- 关闭防火墙
systemctl disable firewalld
systemctl stop firewalld
# 使用需要放行MySQL使用的端口号
- 更改文件句柄和进程数(
*改为用户名)
echo '* - nofile 65535' >> /etc/security/limits.conf
echo '* - nproc 65535' >> /etc/security/limits.conf
- 内核优化
cat >> /etc/sysctl.conf <<EOF
# 剩余未使用内存小于5%时，开始使用swap分区。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
vm.swappiness=5
# 脏页刷新
vm.dirty_ratio=20
vm.dirty_background_ratio=10
# 网络tcp堆栈，轮询 ...
net.ipv4.tcp_max_syn_backlog=819200
net.core.netdev_max_backlog=400000
net.core.somaxconn=4096
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=0
EOF
sysctl -p
- 文件系统优化
# 推荐使用XFS文件系统，MySQL数据在独立分区存放，例如挂载点为: /data
cat >> /etc/fstab <<EOF
/dev/sdb /data xfs defaults 0 0
EOF
- 不使用LVM（逻辑卷管理）
- IO调度策略：
- SAS： deadline（centos7 默认）
- SSD&PCI-E： noop
[root@db01 ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
# 永久修改：GRUB_CMDLINE_LINUX 追加参数：elevator=deadline
软件版本选择
- 稳定版：选择开源的社区版的稳定版GA版本。
- 选择mysql数据库GA版本发布后6个月-12个月的GA双数版本，大约在15-20个小版本左右。
- 要选择前后几个月没有大的BUG修复的版本，而不是大量修复BUG的集中版本。
- 要考虑开发人员开发程序使用的版本是否兼容你选的版本。
- 作为内部开发测试数据库环境，跑大概3-6个月的时间。
- 优先企业非核心业务采用新版本的数据库GA版本软件。
- 向DBA高手请教，或者在技术氛围好的群里和大家一起交流，使用真正的高手们用过的好用的GA版本产品。
最终建议：8.0.20是一个不错的版本选择。向后可以选择双数版。
三层结构及参数优化
连接层
# 高并发示例，TPS=200，QPS=3000~4000
max_connections=1000 # 最大连接数（<3000） *****
max_connect_errors=999999 # 最大错误连接数
wait_timeout=600 # 等待连接超时时间 *****
interactive_wait_timeout=3600 # 活动连接超时时间
net_read_timeout=120 # 网络读数据超时时间
net_write_timeout=120 # 网络写数据超时时间
max_allowed_packet=32M # 服务器接受数据包最大大小 *****
Server层
sql_safe_updates=1 # 开启安全update SQL语法 *****
slow_query_log=ON # 开启慢日志
slow_query_log_file=/data/3307/slow.log # 慢日志文件存放位置及名字 *****
long_query_time=1 # 查询时间超过1s认为是慢查询 *****
log_queries_not_using_indexes=ON # 记录没走索引的语句 *****
log_throttle_queries_not_using_indexes=10 # 超过10条重复的记录，不再统计 *****
sort_buffer = 1M # 不走索引的排序使用的缓冲（<4M）
join_buffer = 1M
read_buffer = 1M
read_rnd_buffer = 1M
tmp_table = 16M # 临时表大小
heap_table = 16M # 堆表大小
max_execution_time = 28800 # 一条语句最大执行时间，默认0，不控制
lock_wait_timeout = 60 # 元数据锁等待超时时间 *****
lower_case_table_names =1 # 创建表名自动转为小写 *****
thread_cache_size =64 # 线程缓存大小
log_timestamps =SYSTEM # 日志时间使用系统时间 *****
init_connect ="set names utf8" # 连接默认字符集 *****
event_scheduler =OFF # 事件调度程序
secure-file-priv =/tmp # 私人安全文件路径 *****
binlog_expire_logs_seconds =2592000 # 二进制日志过期时间 *****
sync_binlog=1 # 保证事务提交立即刷新binlog到磁盘 *****
log-bin=/data/3307/mysql-bin # 二进制日志存放位置及名字
log-bin-index=/data/3307/mysql-bin.index # 二进制日志索引存放位置及名字
max_binlog_size=500M # 二进制日志最大大小
binlog_format=ROW # 二进制日志格式
5.7版本：
binlog_expire_logs_seconds就是expire_logs_days
存储引擎层
transaction-isolation ="READ-COMMITTED" # 隔离级别 *****
innodb_data_home_dir =/xxx # 数据家目录
innodb_log_group_home_dir =/xxx # 日志组家目录
innodb_log_file_size =2048M # 日志文件大小
innodb_log_files_in_group =3 # 日志文件3个
innodb_flush_log_at_trx_commit=2 # 日志刷新时间，开启组提交，设置为非1 *****
innodb_flush_method =O_DIRECT # 刷新策略：刷脏时不经过文件系统缓存 *****
innodb_io_capacity =1000 # io容量，SSD 1000，SAS 200 *****
innodb_io_capacity_max =4000 # 最大io容量，SSD 4000 SAS 600
innodb_buffer_pool_size =64G # 内存缓冲池大小 70％ *****
innodb_buffer_pool_instances =4 # 内存缓冲池实例数 16 *****
innodb_log_buffer_size =64M # 日志缓冲区大小 256M *****
innodb_max_dirty_pages_pct =85 # 脏页数量超过85％刷盘 *****
innodb_lock_wait_timeout =10 # 锁等待超时时间 *****
innodb_open_files =63000 # 最大打开文件数 *****
innodb_page_cleaners =4 # 刷脏线程
innodb_sort_buffer_size =64M # 排序缓冲区大小
innodb_print_all_deadlocks =1 # 打印所有死锁到日志 *****
innodb_rollback_on_timeout =ON # 开启回滚超时
innodb_deadlock_detect =ON # 开启死锁监控
复制
relay_log=/opt/log/mysql/blog/relay # 中继日志存放位置及名字
relay_log_index=/opt/log/mysql/blog/relay.index # 中继日志索引存放位置及名字
max_relay_log_size=500M # 中继日志最大大小
relay_log_recovery=ON # 开启中继日志回收
relay_log_purge=0 # 关闭中继日志清理
rpl_semi_sync_master_enabled =ON # 开启半同步复制
rpl_semi_sync_master_timeout =1000 # 半同步复制超时时间
rpl_semi_sync_master_trace_level =32 # 追踪等级
rpl_semi_sync_master_wait_for_slave_count =1 # 等待至少一个从库响应
rpl_semi_sync_master_wait_no_slave =ON # 开启没从库等待
rpl_semi_sync_master_wait_point =AFTER_SYNC # 增强半同步
rpl_semi_sync_slave_enabled =ON
rpl_semi_sync_slave_trace_level =32
binlog_group_commit_sync_delay =1 # 组提交同步等待1秒
binlog_group_commit_sync_no_delay_count =1000 # 组提交同步等待1000个事务
gtid_mode =ON # 开启GTID
enforce_gtid_consistency =ON # 开启GTID强一致性
skip-slave-start =1 # 不自动开启从库
#read_only =ON # 只读
#super_read_only =ON # 管理员只读
log_slave_updates =ON # 从库强制写入二进制日志
server_id =2330602
report_host =xxxx # 报告主机地址
report_port =3306 # 报告端口号
slave_parallel_type =LOGICAL_CLOCK # 逻辑时钟，并行复制
slave_parallel_workers =4 # 4个工作线程
master_info_repository =TABLE # master_info存入表中
relay_log_info_repository =TABLE # relay_log_info存入表中
其它
[mysql]
no-auto-rehash # 不自动加载数据库所有文件
配置文件基础模板
[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=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
[mysql]
no-auto-rehash
开发规范
1. 开发过程规范，标准
2. 减少烂SQL:不走索引，复杂逻辑，切割大事务
3. 避免业务逻辑错误，避免锁争用
这个阶段，需要深入业务，和开发人员\业务人员配合实现
优化，最根本的是"优化"人. ----oldguo
字段规范
- 每个表名建议在20个字符以内。
- 每个表建议在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语句（X锁） -
update和delete，开启安全更新参数（sql_safe_updates） -
减少
insert ... select ...语句应用 -
使用
load替代insert录入大量数据（LOAD ） -
导入大量数据时，可以禁用索引、增大缓冲区、增大redo的文件和buffer、关闭
autocommit、RC隔离级别可以提高效率 -
优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询，避免跳过大量行（会有索引扫描）：
limit 5000000,10 -
DDL执行前要审核
-
多表连接语句执行前要看执行计划
索引优化
-
非唯一索引按照“
i_字段名称_字段名称[_字段名]”进行命名。 -
唯一索引按照“
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）做HASH，增加一列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)。
-
合理利用覆盖索引，减少回表。
-
减少冗余索引和使用率较低的索引
-- 8.0自带视图 -- 查看到现在为止，未使用过的索引 select * from sys.schema_unused_indexes; -- 查看冗余索引 select * from sys.schema_redundant_indexes\G
事务及锁优化
Global Read lock
Global Read lock（全局读锁） 是 MDL（matedatalock）层面锁，出现场景：
- mysqldump --master-data 备份时
- xtrabackup（8.0之前早期版本）备份时
- MHA 在线角色切换
- 手动加锁：
flush tables with read lock; - 手动解锁：
unlock tables;
影响情况： 加锁期间，阻塞所有事务写入，阻塞所有已有事务commit。
等待时间：lock_wait_timeout=31536000
检测方法
-- 检查监测功能是否打开（8.0默认打开）
select * from performance_schema.setup_instruments
WHERE NAME = 'wait/lock/metadata/sql/mdl';
-- 打开监测功能
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
-- 查看元数据锁
select * from performance_schema.metadata_locks;
select OBJECT_SCHEMA ,OBJECT_NAME ,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS ,OWNER_THREAD_ID,OWNER_EVENT_ID from performance_schema.metadata_locks;
-- 查看所有线程
select * from performance_schema.threads\G
-- 查看所有进程
show processlist;
-- 查看锁等待
select * from sys.schema_table_lock_waits;
故障复现
xtrabackup/mysqldump备份时数据库（5.7版本）出现hang状态，所有查询都不能进行
innobackupex备份全库（5.7版本），进程死了，mysql里就是全库读锁，后边insert全阻塞了
-- session1: 模拟一个大查询或事务
select id,sleep(100) from city where id<10;
-- session2: 模拟备份时的FTWRL
mysql> flush tables with read lock;
-- 此时发现命令被阻塞
-- session3: 发起查询
mysql> select * from world.city where id=100 for update;
-- 此时发现命令被阻塞
-- 结论： 备份时，一定要选择业务不繁忙期间，否则有可能会阻塞正常业务。
故障处理
# 查看所有进程，根据Info: SQL语句，找到State: Waiting for global read lock ，Id: 22
show processlist;
# 查看元数据锁，根据
-- LOCK_STATUS: PENDING 被阻塞
-- LOCK_STATUS: GRANTED 获取到
-- 找到OWNER_THREAD_ID
select * from performance_schema.metadata_locks;
# 查看所有线程，根据
-- performance_schema.metadata_locks.OWNER_THREAD_ID=performance_schema.threads.THREAD_ID
-- PROCESSLIST_INFO: SQL语句，找到PROCESSLIST_ID
-- processlist.Id=performance_schema.threads.PROCESSLIST_ID
select * from performance_schema.threads\G
# 终止进程
-- 终止被阻塞的，被阻塞的会停止执行
-- 终止获取到的，被阻塞的会继续执行
kill processlist_Id;
row lock wait
record lock：gap、next lock，都是基于索引加锁,与事务隔离级别有关。
影响情况：老是卡，等一段时间就失效了，可能是行锁问题
行锁监控及分析
# 确认有没有锁等待:
show status like 'innodb_row_lock%';
# 查询事务信息，trx_state: LOCK WAIT 锁等待
SELECT * FROM information_schema.INNODB_TRX;
# 查询在等待锁的事务信息
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
# trx_id: 事务ID号
# trx_state: 当前事务的状态
# trx_mysql_thread_id: 连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
# trx_query: 当前被阻塞的操作(一般是要丢给开发的)
# 查找锁源
# 查询锁等待详细信息：blocking_pid 锁源的连接进程 = processlist.Id
select * from sys.innodb_lock_waits;
# locked_table: 哪张表出现的等待
# waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
# waiting_pid: 等待的线程号(与上个视图trx_mysql_thread_id)
# blocking_trx_id: 锁源的事务ID
# blocking_pid: 锁源的连接进程号
# 通过连接进程找SQL线程
-- processlist.Id=performance_schema.threads.PROCESSLIST_ID
-- performance_schema.threads.THREAD_ID=performance_schema.metadata_locks.OWNER_THREAD_ID
SELECT * FROM performance_schema.threads;
select THREAD_ID,PROCESSLIST_ID from performance_schema.threads where PROCESSLIST_ID=30;
# 通过SQL线程找到当前在执行的语句
SELECT * FROM performance_schema.events_statements_current;
# 通过SQL线程找到历史SQL语句
select * from performance_schema.events_statements_history;
select THREAD_ID,sql_text from performance_schema.events_statements_history where THREAD_ID=71;
总结：
表信息
被阻塞的SQL
锁源SQL
丢给开发
系统方面检测
1. 通过系统检测（top），发现CPU Wait,SYS高，IO低: 可能IO出问题，锁等待过多的概率大.
2. 通过CPU SYS高的进程查找其下线程：top -Hp PID ，获得线程PID
3. 在mysql中查看线程PID的信息
-- 线程PID=performance_schema.threads.THREAD_OS_ID
select * from performance_schema.threads;
select * from performance_schema.threads where THREAD_OS_ID=线程PID;
故障项目
- 背景：
硬件环境: DELL R720,E系列16核,48G MEM,SAS900G6,RAID10
在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%) - 排查：
- 通过top详细排查，发现mysqld进程占比达到了700-800%
- 大量CPU被用作SYS和WAIT，us正常
- 怀疑是MySQL锁或者SQL语句出了问题
- 排查slowlog及锁等待情况，发现有大量锁等待及少量慢语句
- pt-query-diagest 查看慢日志
- 查看锁等待：
db01 [(none)]> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
# 情况一: 100多个current_waits，说明当前很多锁等待情况
# 情况二: 1000多个lock_waits，说明历史上发生过的锁等待很多
查看那个事务在等待(谁被阻塞了)
查看锁源事务信息(谁锁的我)
找到锁源的thread_id
找到锁源的SQL语句
- 找到语句之后，和开发人员协商
- 开发人员描述，此语句是事务挂起导致
我们建议是临时 kill 会话，最终解决问题 - 开发人员查看后，发现是业务逻辑问题导致的死锁，产生了大量锁等待。
临时解决方案，将阻塞事务的会话kill掉。
最终解决方案，修改代码中的业务逻辑。
- 开发人员描述，此语句是事务挂起导致
- 项目结果：经过排查处理，锁等待的个数减少80%。解决了CPU持续峰值的问题.
优化方向
- 优化索引
- 减少事务的更新范围(大范围拆分运行)
- RC隔离级别
- 拆分语句：
-- 例如：
-- k1 是辅助索引，会有 record lock：gap 或 next
update t1 set num=num+10 where k1 <100;
-- 改为:
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---99.99%
- MGR\InnoDB Cluster---99.999%
- PXC
读写分离：
ProxySQL、MySQL-router
NoSQL:
- Redis Sentinel/Redis Cluster
- MongoDB RS/MongoDB SHARDING Cluster
- ES
安全优化
- 使用普通nologin用户管理MySQL。
- 合理授权用户、密码复杂度及最小权限、系统表保证只有管理员用户可访问。
- 删除数据库匿名用户。
- 锁定非活动用户。
- MySQL尽量不暴露在互联网中，必须暴露则用户需要设置明确白名单、替换MySQL默认端口号、使用SSL连接。
- 优化业务代码，防止SQL注入。
PT（percona-toolkits）
安装
[root@db01 ~]# 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条件列有索引
示例：
- 准备环境：
create user oldguo@'10.0.0.%' identified with mysql_native_password by '123';
grant all on *.* to oldguo@'10.0.0.%';
source /root/t100w;
source /root/world;
alter table t100w change id id int not null primary key auto_increment;
desc t100w;
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=oldguo,p=123 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat"
- 导入csv文件到数据库
use world;
create table test like city;
load data infile '/tmp/archiver.dat' into table test;
pt-osc
应用场景： 修改表结构、索引创建删除，不能加快速度，但能减少业务影响（锁）。
主要参数：
pt-online-schema-change
--host="127.0.0.1" # IP
--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" # alter语句
--print # 打印
#--execute # 执行
#--dry-run # 模拟测试
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-osc工作流程：
- 检查更改表是否有主键或唯一索引，是否有触发器（如果有先删除）
- 检查修改表的表结构，创建一个临时表，在新表上执行ALTER TABLE语句
create table bak like t1;
alter table bak add telnum char(11) not null;
- 在源表上创建三个触发器，分别对于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-table-checksum
应用场景：校验主从数据一致性
主要参数：
--[no]check-replication-filters # 是否检查复制的过滤器，默认是yes，建议启用不检查模式。
--databases | -d # 指定需要被检查的数据库，多个库之间可以用逗号分隔。
--[no]check-binlog-format # 是否检查binlog文件的格式，默认值yes。建议开启不检查。因为在默认的row格式下会出错。
--replicate # 把checksum的信息写入到指定表中。
--replicate-check-only # 只显示不同步信息
注意：需要从库配置报告IP和PORT
report_host=10.0.0.51 report_port=3309主库查看验证
mysql -S /data/3307/mysql.sock -e 'show slave hosts;'
- 创建数据库
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;
- 创建测试库表
Create database test;
Create table t1 (id int not null primary key auto_increment, name varchar(20));
insert into t1 values(1,'a'),(2,'b');
commit;
- 连接主库校验
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-replication-filters --nocheck-binlog-format --replicate=pt.checksums --nocheck-plan --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= # 服务器地址：多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.51,u=checksum,p=checksum,P=3306 --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.51,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;
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文件中
pt-kill --user=用户名 --password=密码 --match-command Sleep --idle-time 5 --victim all --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &
# 查询SELECT超过1分钟的SQL
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掉 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
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 &
# 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 &
其他
# 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-find
# 主从报错，跳过报错
pt-slave-restart
# 整个系统的的概述
pt-summary
# MySQL的表述，包括配置文件的描述
pt-mysql-summary
# 检查数据库重复索引
pt-duplicate-key-checker
性能验证
性能指标
TPS：Transactions Per Second，每秒事务数。具体事务的定义，都是人为的，可以一个接口、多个接口、一个业务流程等等。如果没有定义事务，会把每个请求作为一个事务。
一个事务是指事务内第一个请求发送到接收到最后一个请求的响应的过程，以此来计算使用的时间和完成的事务个数。以单接口定义为事务为例，每个事务包括了如下3个过程：
a. 向服务器发请求
b. 服务器自己的内部处理（包含应用服务器、数据库服务器等）
c. 服务器返回结果给客户端
如果每秒能够完成N次这三个过程，tps就是N；
如果多个接口定义为一个事务，那么，会重复执行abc，完成一次这几个请求，算做一个TPS。
QPS：Queries Per Second，每秒查询率。是一台服务器每秒能够响应的查询次数（数据库中的每秒执行查询sql的次数），不能描述增删改，不建议用qps来作为系统性能指标。
TPS和QPS区别：
- 如果是对一个查询接口（单场景）压测，且这个接口内部不会再去请求其它接口，那么
TPS=QPS，否则，TPS≠QPS。 - 如果是容量场景，假设n个接口都是查询接口，且这个接口内部不会再去请求其它接口，
QPS=n*TPS。
性能测试
查询命令
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc ·explain·
·slowlog·
查询系统表、视图
information_schema
performance_schema
sys
其他工具
mysqlslap
percona-toolkits
sysbench
压测：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose
dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1
现象说明
- CPU us 高，IO 高：正常现象
- CPU us 高，IO 低，MySQL不做增删改查：可能是存储过程,函数,排序,分组,多表连接
- CPU Wait，SYS 高，IO低：IO出问题，锁等待过多的概率大.
- IOPS（每秒磁盘最多能够发生的IO次数）很高，达到阈值，频繁小事务。
可能IO吞吐量没超过IO最大吞吐量，但是无法增加新的IO了，存储规划有问题。
数据库优化参数详解
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
其中，有一步是：show fields from `guo`。从slow query记录的执行计划中，可以知道它也产生了 Tmp_table_on_disk。
所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。
（3）配置方法
key_buffer_size=64M
query_cache_size
（1）简介：
查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。
SQL层：
select * from t1 where name=:NAME;
select * from t1 where name=:NAME;
1、查询完结果之后，会对SQL语句进行hash运算，得出hash值,我们把他称之为SQL_ID
2、会将存储引擎返回的结果+SQL_ID存储到缓存中。
存储方式：
例子：select * from t1 where id=10; 100次
1、将select * from t1 where id=10; 进行hash运算计算出一串hash值，我们把它称之为“SQL_ID"
2、将存储引擎返回上来的表的内容+SQLID存储到查询缓存中
使用方式：
1、一条SQL执行时，进行hash运算，得出SQLID，去找query cache
2、如果cache中有，则直接返回数据行，如果没有，就走原有的SQL执行流程
一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。
注：两个sql语句，只要想差哪怕是一个字符（列如大小写不一样；多一个空格等）,那么这两个sql将使用不同的一个cache。
（2）判断依据
mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2002 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
---------------------状态说明--------------------
Qcache_free_blocks：缓存中相邻内存块的个数。
如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
注：当一个表被更新之后，和它相关的cache
blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes：
多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；
Qcache_total_blocks：当前Query Cache 中的block 数量；。
Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
90/ 10000 0 90
如果出现hits比例过低，其实就可以关闭查询缓存了。使用redis专门缓存数据库
Qcache_free_blocks 来判断碎片
Qcache_free_memory + Qcache_lowmem_prunes 来判断内存够不够
Qcache_hits 多少次命中 Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
（3）配置示例
mysql> show variables like '%query_cache%' ;
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
mysql>
-------------------配置说明-------------------------------
以上信息可以看出query_cache_type为off表示不缓存任何查询
各字段的解释：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：
如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。
如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
修改/etc/my.cnf,配置完后的部分文件如下：
query_cache_size=128M
query_cache_type=1
max_connect_errors
max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
修改/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内存
（3）配置方法
修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=1M
max_allowed_packet
（1）简介：
mysql根据配置文件会限制，server接受的数据包大小。
（2）配置依据：
有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数
（3）配置方法：
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服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
(3)配置方法：
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%。
（3）配置方法
innodb_buffer_pool_size=2048M
innodb_flush_log_at_trx_commit
（1）简介
主要控制了innodb将log buffer中的数据写入日志文件并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。
（3）配置方法
innodb_flush_log_at_trx_commit=1
双1标准中的一个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使用情况，均不均匀
top
2、当前的连接数，有没有达到顶峰
show status like 'threads_%';
show processlist;
（3）配置方法：
innodb_thread_concurrency=8
方法:
1. 看top ,观察每个cpu的各自的负载情况
2. 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值
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
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 // 什么时候刷新binlog到磁盘，每次事务commit
// 双1标准(基于安全的控制)：
sync_binlog=1
innodb_flush_log_at_trx_commit=1
// 临时关闭二进制日志记录
set sql_log_bin=0;
show status like 'com_%';
Innodb_flush_method=O_DIRECT
Innodb_flush_method=(O_DIRECT, fsync)
1、Innodb_flush_method=fsync
（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
2、 Innodb_flush_method=O_DIRECT
（1）在数据页需要持久化时，直接写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
最安全模式：
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
