# Day15 MySQL 全面优化
# 优化思路
- 硬件层优化
- 系统层优化
- MySQL软件及版本选择优化
- MySQL三层结构及参数优化
- MySQL开发规范
- MySQL的索引优化
- MySQL的事务及锁优化
- MySQL架构优化
- MySQL的安全优化
- 常用工具介绍
# 硬件层优化
1. 硬件的选择
2. 关闭NUMA
3. 开启CPU高性能模式
4. 阵列卡推荐RAID10
5. 关闭THP
6. 网卡绑定，推荐主备模式
7. 存储多路径
# 系统层优化
a. 脏页内存优化
内核优化 /etc/sysctl.conf
vm.swappiness = 5
vm.dirty_ratio = 20
vm.dirty_background_ratio = 10
-- 补充：内存脏页/系统可用内存百分比，达到比例，会刷脏页到磁盘
vm.dirty_ratio = 20 -- 脏页阻断式刷新
vm.dirty_background_ratio = 10 -- 脏页异步刷新
-- free -h 当内存使用量过大时，可以调低一些，快速刷到磁盘，可能会导致IO问题
b. 防火墙相关规则
- 设置iptables
iptables -P FORWARD ACCEPT
- 关闭selinux和防火墙
sed -ri 's#(SELINUX=).*#\1disabled#' /etc/selinux/config
setenforce 0
systemctl disable firewalld && systemctl stop firewalld
c. 文件系统优化
推荐使用XFS文件系统，MySQL数据分区独立，例如挂载点 /data
mount参数 cat /etc/fstab 可增加IO并发
/dev/sdb /data xfs defauts,noatime,nodiratime,nobarrier 1 2
d. 不使用LVM
e. IO调度
SAS : deadline
SSD&PCI-E : noop
TODO 后续补充 top命令
# MySQL软件及版本选择优化
- 稳定版：选择开源社区版的稳定版GA版
- 小版本 20+
- 优先企业非核心业务采用新版本
最终建议：8.0.24+是一个不错的版本选择，参数已经预设优化好，选择双数版本
# MySQL三层结构及参数优化
- 连接层
max_connections=1000 --对外连接数，3000以内
max_connect_errors=999999 --错误数，足够大即可
wait_timeout=600 --空闲时间，TP类业务
interactive_wait_timeout=3600
net_read_timeout=120 --网络数据延迟
net_write_timeout=120
max_allowed_packet=32M --实际情况，可大写，特别是备份的时候
- Server层(和SQL相关)
sql_safe_updates=1 --update 要 where条件且是索引
--慢日志参数
server_id=51
slow_query_log=1
long_query_time=0.5
slow_query_log_file=/data/3306/log/slow_log
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=1000
--二进制日志参数
binlog_expire_logs_seconds=2592000
sync_binlog=1
log_bin=/data/3306/log/binlog
log_bin_index=/data/3306/log/binlog.index
max_binlog_size=500M
binlog_format=ROW
--缓冲大小 TP类业务 如果是AP类可设置大些
sort_buffer=8M
join_buffer=8M
read_buffer=1M
read_rnd_buffer=32M
tmp_table=16M
heap_table=16M
--其他设置
lock_wait_timeout=31536000 -- MDL锁时间，1年
lower_case_table_names=1
log_timestamps=SYSTEM
init_connect="set names utf8mb4"
- 存储引擎层
# MySQL开发规范
# 字段规范
1. 每个表建议在30个字段以内
2. 需要存储emoji字符时，需要使用utf8mb4字符集
3. 机密数据，加密后存储
4. 整型数据，考虑unsigned
5. 存储ip地址，建议 int unsigned，查询时再利用inet_aton()、inet_ntoa()函数转换
6. 如果遇到blob、text大字段时，存储设备单独存储，数据库存储附件
7. 使用尽可能小的数据类型，节省磁盘和内存空间
8. 存储浮点数，可以放大存储倍数
9. 每个表必须有主键，采用int/bigint且自增作为主键，分布式架构采用sequence序列生成器保存
10. 每个列使用 not null，或默认值
# SQL语句规范
多研究研究官网 https://dev.mysql.com/doc/refman/8.0/en/optimization.html
# MySQL的索引优化
1. 非唯一索引按照 idx_field_n(field...)命名
2. 唯一索引按照 u_field_n(field...)命名
3. 索引名使用小写
4. 索引中字符段数不超过5个
5. 索引选择度高的列作为联合索引最左条件
6. order by、group by、distinct需要添加到联合索引中
7. 当单张表的索引数量控制在5个以内。查询性能无法解决的，可以从产品设计角度进行重构
8. 使用explain判断sql语句是否合理使用索引，尽量避免extra列出现：Using file sort、Using temporary
9. update、delete语句需要根据where条件添加索引
10. 长度大于50的列建立索引时，按照需求恰当使用前缀索引，或者采用其他方法
11. 可以把长字段做hash得到数值，作为索引列
12. 合理创建联合索引(避免重复), (a, b, c)相当于(a)、(a, b)、(a, b, c)
13. 合理利用覆盖索引，减少回表
14. 减少冗余索引和使用率低的索引，sys库
# MySQL架构优化
高可用架构：
MHA+ProxySQL+GTID+增强半同步
Xenon --> 依赖 5.7+GTID+增强半同步
ORCH+MHA --> 图形化管理
MGR、MIC
读写分离：
ProxySQL、MySQL-router --> 高可用会结合读写分离
分布式架构：
shardingsphere、Mycat
NoSQL：
Redis Sentinel、Reids Cluster
MongoDB RS、MongoDB SHARDING Cluster
1. 百万级、千万级并发MySQL撑不住，需要配合NoSQL
2. 历史数据，如流水、登录日志等
NewSQL：
PingCAP TiDB
TDSQL
PolarDB
OceanBase
1. HTAP 海量数据实时分析
# MySQL的安全优化
1. 生产中使用普通nologin用户管理MySQL，防止篡权
2. 合理授权用户、密码复杂度及最小权限、系统表保证只有管理员用户访问
- 本地管理员
- 业务代码连接用户
- 开发人员，避免DML权限授权
- 备份监控用户
3. 删除数据库匿名用户(5.6之前版本)
- 如只有用户名，没有白名单
4. 锁定非活动用户
- 如员工离职
5. MySQL尽量不暴漏在互联网，需要暴漏互联网
- 用户需要设置明确白名单
- 替换MySQL默认端口号
- 使用SSL连接
6. 优化业务代码，防止SQL注入
7. 做好备份
8. SQL审核 -> 爱可生
9. 选择高可用架构及容灾
# 常用工具介绍
PT(percona-tookkits)工具应用
1. pt-archiver 归档表
场景：
需求：万级的大表，delete批量删除100万左右数据
需求：定期按照时间范围，进行归档表
原理：拆分事务，尽量能降低对业务的影响
2. pt-osc
场景：
修改表结构、索引创建删除
不能加快速度，但能减少业务影响(锁)
3. pt-table-checksum
作用：校验主从数据一致性
4. pt-table-sync
作用：同步数据
5. pt-duplicate-key-checker
作用：检查数据库是否有重复索引
6. pt-kill
作用：无法正常kill的连接，如sleep时间长的连接
7. pt-slave-find
8. pt-heartheat
9. pt-show-grants
作用：用户和权限信息迁移
10. 其他
pt-query-digest
pt-summary
pt-pmp