本文档全面阐述了数据库优化的关键方面，涵盖从硬件选型到系统配置、从参数调优到索引设计、从架构规划到安全管理的全方位优化策略。同时详细介绍了Percona Toolkit工具集的实战应用，帮助DBA和管理员构建高性能、高可用的数据库环境，并提供解决日常运维问题的实用工具和方法。

一、数据库优化

1.硬件优化

1.1 硬件配置建议

选型建议	具体选择说明
存储设备厂商	DELL、HP、IBM、华为、浪潮
CPU选型	Inter-I系列、E系列(Xeon)
内存选型	具有ECC功能特性内存（Error Correcting Code 错误检查和纠正技术），提高计算机运行的稳定性和增加可靠性
IO存储选型	SAS、pci-e SSD、Nvme flash
Raid选型	Raid 10
网卡选型	单卡单口网卡（使用寿命较长）
云存储选型	ECS、RDS、PolarDB、TDSQL

1.2 硬件参数调配

关闭numa功能，从而提高数据库服务应用性能（提高QPS效率）

# 关闭Numa（早期SMP）
# 关闭方式一：bios级别关闭
参见下图关闭（实现让mysql多使用一些内存空间）
numactl --hardware
available：1 node（0）    -- 如果是2或多个nodes就说明numa没关闭
-- 在bios层面numa关闭时，无论os层面的numa是否关闭，都不会影响数据库服务性能；

# 关闭方式二：os grub级别关闭
[root@db01 ~]# vim /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="spectre_v2=retpoline net.ifnames=0 rhgb quiet numa=off"
GRUB_DISABLE_RECOVERY="true"

[root@db01 ~]# grub2-mkconfig -o /etc/grub2.cfg
-- 重新生成/etc/grub2.cfg配置文件
[root@db01 ~]# reboot
[root@db01 grub2]# dmesg|grep -i numa
[    0.000000] NUMA turned off
-- 重启系统后确认numa是否关闭
[root@01 grub2]# cat /proc/cmdline
BOOT_IMAGE=/vmlinuz-3.10.0-1160.el7.x86_64 root=UUID=f2d5232b-945e-4e7a-b76f-7ec609c35499 ro spectre_v2=retpoline net.ifnames=0 rhgb quiet LANG=en_US.UTF-8 numa=off
-- 再次核实确认
-- 在OS层numa关闭时，打开bios层的numa会影响性能，QPS会下降15~30%；

# 关闭方式三：数据库级别关闭
mysql> show variables like '%numa%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_numa_interleave | OFF   |
+------------------------+-------+
1 row in set (0.04 sec)

或者
[root@db01 ~]# vim /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=$?

2.系统优化

1. 更改文件句柄和进程数
[root@db01 ~]# vim /etc/sysctl.conf
vm.swappiness = 5   
-- 也可以设置为0 (/proc/sys/vm/swappiness)  物理内存剩余的百分比之后，使用swap
-- 参数值越大，越积极使用swap空间，参数值越小，越积极使用物理内存
-- 默认值为可以通过cat /proc/sys/vm/swappiness命令查看
vm.dirty_ratio = 20
-- 表示可以用脏数据填充的绝对最大系统内存量，当系统到达此点时，必须将所有脏数据提交到磁盘，
-- 同时所有新的I/O块都会被阻塞，直到脏数据被写入磁盘。
-- 这通常是长I/O卡顿的原因，但这也是保证内存中不会存在过量脏数据的保护机制。
vm.dirty_background_ratio = 10
-- 定义内存中脏页的刷新比例，在指定比例之上就要刷写脏页
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

[root@db01 ~]# vim /etc/security/limits.conf
hard  nofile 63000
-- 可打开的文件描述符的最大数(超过会报错);
soft  nofile 63000
-- 可打开的文件描述符的最大数(超过会警告);

2. 防火墙与selinux安全设置
[root@db01 ~]# systemctl is-active firewalld      
unknown
[root@db01 ~]# systemctl is-enabled firewalld
disabled
-- 查看防火墙服务是否关闭，如果有需要开启时，别忘把数据库服务相关的端口开启即可

[root@db01 ~]# getenforce 
Disabled
-- 查看selinux安全策略是否关闭

3. 文件系统优化设置
# 推荐使用XFS文件系统，并设置数据库的数据为独立分区，不建议使用LVM
挂载点为: /data
挂载参数：defaults，noatime，nodiratime，nobarrier  
[root@db01 ~]# vim /etc/fstab
/dev/sdb1    /data        xfs     defaults，noatime，nodiratime，nobarrier   1   2
                                                                        文件系统备份 检查文件系统是否正常

4. io调度设置

总结：
系统优化：1.控制脏页的存储量到达什么程度让脏页落盘
        2.配置可打开的最大文件描述符，调至63000
        3.使用XFS文件系统挂载
        4.io调度设置

3.结构参数优化

3.1 数据库连接层优化

# 连接层相关优化参数
max_connections=1000
-- 单节点建议不高于3000
max_connect_errors=999999
-- 定义最大连接失败的次数，当超过定义的数值，就会影响正常的连接建立
wait_timeout=600
-- 定义连接会话的超时时间（释放更多的连接数），具体指定sleep连接会话的超时时间
interactive_timeout=3600
-- 定义连接会话的超时时间（释放更多的连接数），定义交互式的超时时间
net_read_timeout=120
net_write_timeout=120
-- 定义网络传输读或写数据包的超时时间；
max_allowed_packet=32M
-- 定义允许的最大数据包大小

3.2 数据库服务层优化

# 服务层相关优化参数
sql_safe_updates = 1
-- 设置当使用update或delete命令时，必须加上where才能执行
slow_query_log = ON
slow_query_log_file = /xxx
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10  -- 不走索引的相同索引语句只记录指定的次数
-- 进行数据库慢日志信息相关配置
sort_buffer_size = 262144 
join_buffer_size = 262144
read_buffer_size = 131072 
read_rnd_buffer_size = 262144 
-- 定义session级别的缓冲区大小，不建议设置大小超过8M，因为是根据每个会话进行的缓冲区分配；
tmp_table_size = 16777216       
max_heap_table_size = 16777216
-- 生成的临时表空间建议不超过128M
max_execution_time = 28800
-- 当跑大的事务操作时，可以设置事务最大的执行时间，建议再跑批量操作时，可以设置大些
lock_wait_timeout = 60
-- 表示设置锁等待的时间，当锁定时间到达指定时间后，会实现自动解锁（主要针对元数据锁，默认是1年）
lower-case-table-names = 1  
-- 表示创建表时，自动将表名的大写信息转化为小写；（必须初始化时进行设置）
thread_cache_size = 64
-- 表示设置线程缓存的个数信息，可以使线程缓存资源进行复用，从而减少CPU工作压力；（比如连接线程就可以应用）
character_set_server = utf8mb4
-- 设置数据库服务端字符集，建议设置为utf8或utf8mb4
log_timestamps = SYSTEM
-- 表示设置日志信息的时间尽量和系统时间信息保持一致
init_connect = '普通用户登录信息'
init_connect='insert into auditdb.access(thread_id,login_time,localname,matchname) values (connection_id(),now(),user(),current_user());'
-- 一般在进行审计时进行使用，表示普通用户登录时，自动进行相应语句的操作
-- 参考链接说明：https://blog.csdn.net/mingli_a/article/details/115351986
event_scheduler = OFF
-- 事件调度信息一般不用使用，关闭即可
secure-file-priv=/tmp
-- 当需要在数据库子系统中把信息导出到当前系统指定目录文件中时，进行使用
expire_logs_days = 10  
sync_binlog = 1            #双一配置，保证数据的安全性
log_bin = ON               #激活binlogi日志记录
log_bin_basename = /data/3306/binlog/mysql-bin
log_bin_index = /data/3306/binlog/mysql-bin.index
max_binlog_size = 500M
binlog_format = ROW 
max_binlog_cache_size = 2G
max_binlog_stmt_cache_size = 2G
-- 表示和binlog有关的配置信息

3.3 数据库引擎层优化

# 引擎层相关优化参数
transaction_isolation = "READ-COMMITTED"
-- 设置事务默认隔离级别，基本RC级别即可
innodb_data_home_dir = /xxx  
-- 表示定义共享表空间文件ibdate存储路径（了解即可）
innodb_log_group_home_dir = /xxx  
-- 表示定义redo日志文件存储路径（了解即可）
innodb_log_file_size = 2048M
-- 表示定义redo日志单个文件大小（建议1~4G）
innodb_log_files_in_group = 3
-- 表示定义redo日志文件的组数（一般可以定义为3~4组）
innodb_flush_log_at_trx_commit = 2 
-- 表示定义事务redo日志刷新到磁盘的策略（双一配置中的其中一个），有binlog日志时，可以不用设置为1
innodb_flush_method = O_DIRECT
-- 表示log buffer中的信息是直接写入到磁盘中的，而不经过系统的buffer（建议硬盘配合SSD使用）
innodb_io_capacity = 1000  
innodb_io_capacity_max = 4000  
-- 表示每次IO可以刷新数据页的数量（SSD盘按照以上配置  SAS盘按照默认即可）
innodb_buffer_pool_size = 64G 
-- 表示定义buffer pool的空间大小（基于128G内存配置，建议不要超过75~80%）
innodb_buffer_pool_instances = 4
-- 表示将定义好的buffer pool空间可以拆分为4份，给不同的实例进行使用，避免相同内存空间的争用；
innodb_log_buffer_size = 64M
-- 定义log buffer空间大小，建议不要超过128M；
innodb_max_dirty_pages_pct = 85
-- 控制在buffer pool中脏页数量的比例，当达到指定的比例就进行checkpoint操作，将脏页信息进行落盘
innodb_lock_wait_timeout = 10
-- 主要是控制行锁的等待超时时间，一般控制在10s内
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 
-- 表示开启死锁检测功能（默认开启）
-- 表示和死锁检测和分析有关的参数
-- 对于死锁概念的资料参考：https://blog.csdn.net/java1527/article/details/127105144

3.4 数据库复制相关优化

# 主从复制相关优化参数
relay_log= db-01-relay-bin
relay_log_basename = /data/3306/data/db-01-relay-bin
relay_log_index = /data/3306/data/db-01-relay-bin.index
max_relay_log_size = 500M
relay_log_purge = ON
relay_log_recovery = ON
-- 表示和relay log日志相关的配置参数信息
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
binlog_group_commit_sync_no_delay_count=1000
-- 表示和半同步复制相关的配置参数
gtid_mode = ON
enforce_gtid_consistency = ON
-- 表示和GTID相关的配置参数信息
master_verify_checksum = ON
-- 表示激活主从复制事件校验机制
sync_master_info = 1
-- 表示每个EVENT都要执行刷盘操作，主要影响master info信息（注意不是每个事务!）
-- 参数参考博文资料：https://blog.csdn.net/weixin_39940344/article/details/113275456
skip-slave-start = 1    #手工激活从库线程
-- 表示随着数据库服务的启动，自动启动从库线程
-- 参数参考博文资料：https://blog.csdn.net/csdnhsh/article/details/116355191
# read_only = ON
# super_read_only = ON
-- 表示是否设置从库为只读状态
log_slave_updates = ON
-- 表示指定从库的事务更新操作，是否也记录到从库的binlog日志中
server_id = xx
-- 定义主从的实例标识信息
report_host = xxxx
report_port = xxxx
-- 表示是否允许主库探测从库的网络配置信息，主要影响show slave hosts命令的输出
-- 参数参考博文资料：http://04007.cn/article/527.html
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
-- 表示设置从库的多线程复制，可以对单个事务中的语句进行多线程回放
master_info_repository = TABLE
relay_log_info_repository = TABLE 
-- 表示定义master info和relay log info以什么方式记录信息

3.5 数据库其他相关优化

# 数据库客户端配置
[mysql]
no-auto-rehash  （等价于mysql -A参数作用）
-- 默认每次连接数据库服务会扫描数据库中所有元数据信息，可以利用此参数关闭扫描功能，有效节省扫描过程占用的内存资源；
pager less
-- 开启数据库输出信息过滤功能

4.索引优化

• 非唯一索引按照 ‘i_字段名称_字段名称[_字段名]’ 进行命名

• 是唯一索引按照 ‘u_字段名称_字段名称[_字段名]’ 进行命名

• 索引名称使用小写

  • 联合索引中的字段数不超过5个

• 唯一键由3个以下字段组成，并且字段都是整型时，使用唯一键作为组合主键

• 没有唯一键或者唯一键不符合上面的条件时，使用自增id作为主键

• 唯一键不能和主键重复

• 索引选择度高的列作为联合索引最左条件

• ORDER BY、GROUP BY、DISTINCY的字段需要添加在索引的后面，构建联合索引

• 单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估；

  查询性能问题无法解决的，应从产品设计上进行重构

• 使用EXPLAN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temorary；

• UPDATE DELETE 语句需要根据where条件添加索引；

• 对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法；

• 合理创建联合索引（避免冗余），（a,b,c）相当于（a），（a,b），(a,b,c)

• 合理利用覆盖索引，减少回表次数；

• 减少冗余索引和使用率较低的索引；

5.事务及锁优化

6.架构设计优化

选择合理架构环境，避免单点故障对数据库服务的影响：

• 可以选择高可用架构：MHA+ProxySQL+GTID+半同步，MGR+InnoDB Cluster，PXC；

• 可以选择读写分离架构：ProxySLQ、MySQL-Router

• 可以选择分布式架构：MyCAT

• 可以选择缓存服务架构：Redis+sentinel，Redis Cluster，MongoDB RS/MongoDB SHARDING Cluster，ES

7.安全应用优化

• 使用普通nologin用户管理MySQL服务进程；

• 合理授权用户、设置密码复杂度及最小权限，系统表保证只有管理员用户可以访问；

• 删除数据库服务中的默认匿名用户信息；

• 锁定数据库服务中的非活动用户信息；

• 数据库服务尽量不要暴露到互联网中，需要在互联网中暴露数据库服务地址信息时，要明确设置好白名单信息；

  替换数据库默认端口，使用SSL远程连接数据库；

• 对业务程序代码做好扫描检测优化，防止出现SQL注入漏洞情况；

8.软件版本选择

• 建议选择开源社区版本，并且选择稳定的GA版本；
• 选择数据库服务GA版本时，最好是发布了6个月_{12个月的GA双数版本，大约在15}20个小版本左右；
• 选择数据库服务版本时，要主要选择前后几个月没有大的BUG修复的版本，而不是大量修复BUG的集中版本；
• 选数据库服务版本还要考虑开发人员所开发程序使用的版本是否与实际数据库应用兼容；
• 选择好数据库服务版本后，建议内部开发人员测试下数据库环境，跑大概3~6个月的时间；
• 企业非核心业务可以优先采用新版本的数据库进行应用；
• 可以多咨询DBA大佬，或者在技术氛围好的群里进行交流咨询，使用真正的高手们用过且好用的GA版本产品；

说明：最终建议可以选择8.0.20，以及8.0.20之后的双数版本；

9.开发规范

9.1 数据库开发字段规范

• 每个表建议在30个字段以内；

• 需要存储emoji字符时，则选择utf8mb4字符集；

• 机密数据信息，需要进行加密后再存储；

• 整型数据类型，默认加上UNSIGNED；

• 存储IPv4地址建议用INT UNSIGNE只存储数字信息，查询时在利用INET_ATON()，INET_NTOA()函数转换；

• 如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储；

• 选择尽可能小的数据类型，用于介绍磁盘和内存空间；

• 存储浮点数，可以放大倍数存储；

• 每个表必须有主键，INT/BIGINT类型、以及自增作为主键，分布式架构使用sequence序列生成器保存；

• 表中每个列使用not null或者增加默认值；

9.2 数据库开发语句规范（SQL）

• 避免使用not in、not exists、<>、like %%
• 多表连接，小表驱动大表
• 减少临时表应用，优化order by、group by、uninon、distinct、join等
• 减少语句查询范围，精确查询条件
• 多条件，符合联合索引最左原则
• 查询条件减少使用函数、拼接字符等条件、条件隐式转换
• union all 替代 union
• 减少having子句使用
• 如非必要不使用for update语句（会加表级别意向锁）
• update和delete，开启安全更新参数
• 减少insert … select 语句应用
• 使用load替代insert录入大数据（会产生大量的意向锁）
• 导入大量数据时，可以禁用索引、增大缓冲区、增大redo文件和buffer，关闭autocommit、RC级别可以提高效率
• 优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询
• DDL执行前要审核
• 多表连接语句执行前要看执行计划

二、数据库工具应用

pt（percona-toolkit）工具安装

https://github.com/percona/percona-toolkit/tags	
[root@master ~]# cd /usr/local/
[root@master local]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm

1.pt-archiver

pt-archiver工具比较适合于大量数据信息的归档操作；

比如：亿级的数据大表，当delete批量删除100w左右数据信息时，就可以使用到此工具；

比如：定期按照时间范围进行归档数据表；

官方资料参考：https://docs.percona.com/percona-toolkit/pt-archiver.html

工具使用过程重要参数：

序号	参数信息	解释说明
01	–limit 100	每次取100行数据用pt-archive处理
02	–txn-size 100	设置每100行进行一次事务提交操作
03	–where ‘id<3000’	设置操作条件
04	–progress 5000	每处理5000行数据信息，输出一次处理信息的情况
05	–statistics	输出执行过程及最后的操作统计 只要不加上–quiet，默认情况下会输出命令操作的执行过程
06	–charset=UTF8	指定字符集为UTF8（这个参数最后要加上，否则可能会出现乱码）
07	–bulk-delete	批量删除source上的旧数据（例如每次1000行的批量删除操作）
08	–commit-each	提交每组提取和归档的行事务

说明：需要归档表中至少有一个索引，最好是where条件列信息具有索引；

# 将数据表归档到另一个数据表中
mysql> create table test1 like t100w;
-- SQL语句操作实现创建归档表
[root@master ~]# pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=liux,p=123 --dest h=10.0.0.51,D=test,t=test1,u=liux,p=123 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 1000 --statistics

# 实践练习环境操作
mysql> create database liux;
mysql> use liux;
mysql> source ~/t100w_liux.sql;
mysql> commit;
mysql> create table test1 like t100w;
mysql> show tables;
+-----------------------+
| Tables_in_liux |
+-----------------------+
| t100w                     |
| test1                       |
+-----------------------+
2 rows in set (0.00 sec)
mysql> alter table t100w modify id int not null  primary key;
mysql>  alter table test1 modify id int not null  primary key;
-- pt工具操作练习环境准备

[root@master ~]# pt-archiver --source h=10.0.0.51,P=3307,D=liux,t=t100w,u=root,p=123 --dest h=10.0.0.51,P=3307,D=liux,t=test1,u=root,p=123 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 1000 --statistics
-- 此操作并不能加快归档或删除操作的速度，只会减少原有业务情况的影响（即大事务切割为小事务操作）

mysql> select * from test1;
-- 进行核查检验数据信息是否迁移归档

# 将数据表中数据信息清理
[root@master ~]# pt-archiver --source h=10.0.0.51,P=3307,D=liux,t=t100w,u=root,p=123  --where 'id<10000' --purge --limit=1 --no-check-charset
mysql> select * from liux.t100w limit 100;

# 将数据导出到外部文件，但不删除原表中的数据
[root@master local]# pt-archiver --source h=10.0.0.51,P=3307,D=liux,t=t100w,u=root,p=123  --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.csv"

2.pt-osc

2.1 概述

pt-osc工具对于修改表结构、索引创建删除比较擅长，pt工具应用不能加快改写速度，但能减少业务影响，主要是锁对业务的影响；

2.2 pt-osc工具应用流程：（面试题）

• 检查更改表是否有主键或唯一键索引信息，并检查是否存在触发器设置；
• 检查要修改的表结构情况，创建一个临时表，在新表上执行alter table语句；

mysql> create table backup like t1;
mysql> alter table backup add telnum char(11) not null;

• 在源表上创建三个触发器分别对于insert update delete操作；

mysql> create trigger;a xx;b xx;c xx;

• 从源表拷贝数据到临时表，在拷贝过程中，对原表的更新操作也会写入到新建的临时表中；

mysql> insert into backup select * from t1

• 将临时表和源表进行重命名操作rename；（需要利用元数据修改锁，会出现短时间锁表）
• 删除源表和触发器设置，最终完成表结构信息的修改；

2.3 pt-osc工具使用限制：

• 源表必须有主键或唯一键索引，如果没有工具将停止工作；
• 如果线上的复制环境过滤器操作过于复杂，工具将无法工作；
• 如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作；
• 如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作；
• 当表使用外键时，如果未使用–alter-foreign-keys-method参数，工具将无法执行；
• 只支持Innodb存储引擎表，且要求服务器上有该表1倍以上的空闲空间；

2.4 pt-osc工具应用alter语句限制：

• 不需要包含alter table关键字，可以包含多个修改操作，使用逗号分隔：drop column c1;add column c2 int;
• 不支持rename语句来对表进行重命名操作；
• 不支持对索引进行重命名操作；
• 如果删除外键，需要对外键名加下划线，例如删除外键fk_uid，修改语句为”DROP FOREIGN KEY _fk_uid”

说明：一般使用pt-osc主要用于对索引或表结构，进行添加或删除操作；默认在数据库8.0之后，也可以不使用工具直接修改；

2.5 pt-osc工具应用模板：

pt-online-schema-change \
--host="127.0.0.1" \
--port=3358 \
--user="root" \
--password="root@root" \
--charset="utf8" \
--max-lag=10 \
--check-slave-lag='xx.xx.xx.xx' \
--recursion-method="hosts" \
--check-interval=2 \
--datebase="testdb1" \
   t="tb001" \
--alter="add column c4 int"
--execute

# 重点参数解释说明：
--execute：表示执行参数
--dry-run：表示只进行模拟测试
其中表名只能使用参数t来设置，没有长参数；

2.6 pt-osc工具应用实践

# 操作语句参考
pt-online-schema-change --user=liux --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=liux --password=123 --host=10.0.0.51 --alter "add index idx(num)" D=test,t=t100w --print --execute

# 实际操作语句
pt-online-schema-change --user=root --password=123 --host=10.0.0.51  --port=3307 --alter "add column state int not null default 1" D=oldboy,t=test1 --print --dry-run
pt-online-schema-change --user=root --password=123 --host=10.0.0.51  --port=3307 --alter "add column state int not null default 1" D=oldboy,t=test1 --print --execute

说明：pt-osc工具bug汇总参考：https://cloud.tencent.com/developer/article/1821985

3.pt-table-checksum

pt-table-checksum工具主要用于校验主从数据一致性情况，针对数据库或者数据表进行一致性检查；

此工具可以在主从复制时，当SQL线程出现异常报错时，可以利用此工具进行校验检查；

1. 主库节点创建校验库和校验用户信息
# 创建校验使用数据库信息
mysql> create database pt character set utf8;
-- 用于存储pt工具验证主从一致性信息，数据库中的表会自动创建；

# 创建校验使用连接用户信息
mysql> create user checksum@'10.0.0.%' identified with mysql_native_password by 'checksum';
mysql> grant all on *.* to checksum@'10.0.0.%';
mysql> flush privileges;

2. 从库节点设置report报告信息
# 从库设置报告信息
[root@master ~]# vim /data/3309/my.cnf
[mysqld]
report_host='10.0.0.51'
report_port='3309'
-- 进行配置后需要重启数据库服务

# 主库查看报告信息
mysql> show slave hosts;
+-----------+----------------+------+-----------+------------------------------------+
| Server_id | Host           | Port | Master_id | Slave_UUID                           |
+-----------+----------------+------+-----------+------------------------------------+
|         9 | 10.0.0.51 | 3309 |         7 | 6dedf963-9e04-11ed-996f-000c2996c4f5 |
+-----------+----------------+------+-----------+------------------------------------+

3. pt-table-checksum命令检验过程
# 验证主从一致性
[root@master ~]# 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,P=3307,u=checksum,p=checksum
Checking if all tables can be checksummed ...
Starting checksum ...
            TS     ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME    TABLE
02-19T01:42:50     0      1               5                     0       1                  0         0.024   test.t1
-- 根据以上校验结果发现有一行差异情况

[root@master ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test  h=10.0.0.51,P=3307,u=checksum,p=checksum
-- 表示可以针对库进行检查，但是对应库中表需要有主键或唯一键索引信息

# 执行参数信息说明
--[no]check-replication-filters：表示是否检查复制的过滤器，默认是yes，建议启用不检查模式；
--database | -d：指定需要被检查的数据库，多个库之间可以用逗号分隔；
--[no]-check-binlog-format：是否检查binlog文件的格式，默认是yes，建议开启不检查，因为在默认row格式下会出错；
--replicate：把checksum的信息写入到指定表中；
--replicate-check-only：只显示不同步信息

4.pt-table-sync

pt-table-sync工具可以对主从不一致的数据信息，进行同步复制修复，实现恢复主从数据的一致性；

# 根据校验结果进行主从修复
pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3306 h=10.0.0.52,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=3306 h=10.0.0.52,u=checksum,p=checksum,P=3306 --execute

# 参数信息解释说明
--replicate：指定通过pt-table-checksum得到的表；
--database：指定执行同步的数据库
--tables：指定执行同步的表，多个表用逗号分隔；
--sync-to-master：指定一个DSN，即从库的IP，会通过show processlist或show slave status去自动的找主
h=：服务器地址，命令里有2个IP，第一次出现的是master的地址，第二次出现是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=3309 --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=3309 --execute

5.pt-duplicate-key-checker

pt-duplicate-key-checker工具主要用于检查数据库重复索引信息；

pt-duplicate-key-checker --database=test --host=10.0.0.51 --user=root --password=123 --port=3307

6.pt-kill

pt-kill工具主要用于杀掉异常的数据库连接会话信息；

实践情况-01：杀掉空闲链接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 &

实践情况-02：查询select 语句超过1分钟的会话

/usr/bin/pt-kill --user=用户名 --password=密码 --busy-time 60 --match-info "select|SELECT"  --victim all --interval 5 --kill --daemonize -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

实践情况-03：kill掉查询语句 select …ifnull.* 语句开头的SQL

/usr/bin/pt-kill --user=用户名 --password=密码 --victim all --busy-time=0 --match-info "select IFNULL.*"  --interval 1 -S /tmp/mysql.sock --kill --daemonize  --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

实践情况-04：kill掉state locked

/usr/bin/pt-kill --user=用户名 --password=密码 --victim all  --match-state="Locked"  --interval 5  --kill --daemonize -S /tmp/mysql.sock  --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

实践情况-05：kill掉 A库 web为10.0.0.11的连接

/usr/bin/pt-kill --user=用户名 --password=密码 --victim all  --match-db="a"  --match-host='10.0.0.11' --kill  --daemonize --interval 10  -S /tmp/mysql.sock  --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

实践情况-06：指定哪个用户kill

/usr/bin/pt-kill --user=用户名 --password=密码 --victims all  --match-user="root" --kill  --daemonize  --interval 10  -S /tmp/mysql.sock  --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

实际情况-07：kill掉 command query|Execute

/usr/bin/pt-kill --user=用户名 --password=密码 --victims all  --match-command="query|Execute"  --interval 10 --kill  --daemonize   -S /tmp/mysql.sock  --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

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-command在前，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

7.pt-slave-find

pt-slave-find工具主要用于输出主从关系的拓扑结构信息；

[root@master local]# pt-slave-find -h10.0.0.51 -P3307 -uchecksum -pchecksum
10.0.0.51:3307
Version         8.0.26
Server ID       7
Uptime          16:16:43 (started 2023-02-18T11:12:42)
Replication     Is not a slave, has 1 slaves connected, is not read_only
Filters
Binary logging  ROW
Slave status
Slave mode      STRICT
Auto-increment  increment 1, offset 1
InnoDB version  8.0.26
+- 10.0.0.51:3309
   Version         8.0.26
   Server ID       9
   Uptime          01:59:10 (started 2023-02-19T01:30:15)
   Replication     Is a slave, has 0 slaves connected, is not read_only
   Filters
   Binary logging  ROW
   Slave status    0 seconds behind, running, no errors
   Slave mode      STRICT
   Auto-increment  increment 1, offset 1
   InnoDB version  8.0.26

8.pt-heartbeat

pt-heartbeat工具主要用于监控主从延时的情况；

# 主库进行操作
pt-heartbeat --user=root --ask-pass --host=10.0.0.51 --port=3307 --create-table -D test --interval=1 --update --replace --daemonize
-- 

# 从库进行操作
pt-heartbeat --user=root --ask-pass --host=10.0.0.51 --port=3309 -D test --table=heartbeat --monitor
pt-heartbeat --user=root --password=123 --host=10.0.0.51 --port=3309 -D test --table=heartbeat --monitor --file=/tmp/aa.log &
-- 可以模拟停止从库SQL线程（stop slave sql_thread;），查看延时情况

9.pt-show-grants

pt-show-grants工具主要用于权限信息迁移，当只迁移业务数据库信息时，可以实现单独迁移用户和授权信息；

三、总结

数据库优化是一个系统工程，需要从多个层面综合考虑：

1.优化层次总结：

• 硬件基础：合理的硬件选型和配置是数据库性能的物理保障
• 系统环境：优化的操作系统配置为数据库提供稳定运行平台
• 参数调优：精细化的参数配置充分发挥数据库性能潜力
• 索引设计：科学的索引策略是查询性能的关键
• 架构规划：合理的架构设计满足业务扩展需求
• 安全管理：完善的安全措施保障数据资产安全
• 开发规范：统一的开发标准减少性能问题的产生

2.工具应用价值：

Percona Toolkit作为专业的数据库管理工具集，提供了：

• 数据管理：pt-archiver、pt-osc解决数据迁移和结构变更
• 数据校验：pt-table-checksum、pt-table-sync保障数据一致性
• 性能监控：pt-heartbeat、pt-kill实现实时监控和异常处理
• 诊断分析：pt-duplicate-key-checker、pt-slave-find辅助问题定位

3.核心理念：

• 预防为主：通过规范化设计和配置预防性能问题
• 分层优化：从硬件到应用的全面优化思路
• 工具辅助：利用专业工具提升运维效率和质量
• 持续改进：数据库优化是一个持续的过程，需要定期评估和调整

通过系统化的优化策略和专业的工具支持，可以构建高性能、高可用、易维护的数据库环境，为业务发展提供坚实的数据基础支撑。在实际工作中，DBA需要根据具体的业务场景和资源条件，灵活运用这些优化方法和工具，不断探索最适合的优化路径。