mysql 优化

影响mysql性能的因素

# sql查询的速度（QPS TPS） 2 硬件CPU内存 3 网卡的流量 4 磁盘IO 5 大表 6大事务
    1）sql查询的速度（QPS TPS）
        QPS 每秒钟处理的查询量
        TPS 
        按经验来讲80%都是因为慢查询导致数据库性能下降
    2）硬件CPU内存
        大量的并发：导致数据库谅解被占满 （max_connections默认100，所以要调高）
        高CPU的使用率:因为CPU被占满而宕机
    3）网卡流量
        风险：网卡IO被占满 1000Mb/s/8=100MB
        解决：1减少从服务器的数量 2 进程分级缓存 3 避免* 查询 4分离业务网络和服务器网络
    4）磁盘IO
        磁盘IO突然下降（解决：使用更快的磁盘设备）
        其他大量消耗磁性能的计划任务（解决：调整计划任务，做好磁盘维护）
    5）大表
        什么是大表：1记录的行数巨大 单表超过千万行 2表数据文件巨大，表数据超过10G
            1 带来慢查询
            2 建立索引时间长
            3 修改表结构需要长时间锁表
        如何解决大表问题
            1 分库分表（耗费大量人力物力） 
            2大表历史数据归档（减少对前后端业务影响，难点1是归档时间点2如何进行归档操作）
    6）大事务：
        事务特性：
            原子性：不可分割的最小单位，要么都完成，要么都失败（货款后，不成功，你钱不会少）
            一致性：在事务开始之前和事务结束之后数据库中的数据的完整性没有被破坏（转账后的总金额不变）
        事务的隔离性
            1 未提交读 
            2 已提交读 
            3 可重复读（默认）
            4 可串行化（少用）
        事务的持久性:事务一点提交就会被保存到数据库中
        大事务：运行时间长，操作的数据比较多的事务
        大事务风险： 
            1 锁的数据多，造成大量的阻塞和和锁超市
            2 回滚时间长
            3 执行时间长，容易造成主从延迟
            解决：
            1 避免处理太多数据（分批操作）
            2 查询操作不要放到事务中

影响服务器的性能

1 CPU 内存 磁盘IO
2 操作系统
3 mysql存储引擎 myisam（不支持事务，表级锁）innodb（支持事务，行级锁）
4 mysql配置参数（重点）
5 数据库的表结构
6 慢查询

优化mysql 之 硬件的优化

1 cpu： 2.5Hz 16/32核 64位
2 内存： 越多越好 频率高 单条要大
3 磁盘：riad5(从)/10（主） ssd（支持riad） pcie-ssd卡（会占用CPU和内存） mysql不适合用网络存储（随机读写差）
4 网络带宽：万兆核心 多网卡绑定 尽可能网络隔离

优化mysql 之 操作系统优化

1 内核优化配置文件/etc/sysctl.conf
    1 TCP相关参数
        1 TCP监听队列相关
         net.core.somaxconn = 65535
        net.core.netdev_max_backlog = 65535
        net.ipv4.tcp_max_syn_backlog = 65535
        2 加快TCP连接回收速度
        net.ipv4.tcp_fin_timeout = 10
        net.ipv4.tcp_tw_reuse = 1
        net.ipv4.tcp_tw_recycle = 1
        3 TCP接受和发送缓存区
        net.core.wmem_default = 87380
        net.core.wmem_max = 16777216
        net.core.rmem_default = 87380
        net.core.rmem_max = 16777216
        4 减少TCP失效连接所占用的系统资源
        net.ipv4.tcp_keepalive_time = 120
        net.ipv4.tcp_keepalive_intvl = 30
        net.ipv4.tcp_keepalive_prodes = 3
    2 内存参数
        kernel.shmmax = 4294967295
            这个参数应该设置足够大（可取最大值为大于内存之的一半），存放innodb存储池
        vm.swappiness = 0
            除非虚拟内存完全满了，否则不要使用交换分区

2 资源限制优化 配置文件 /etc/security/linit.conf
    将一下参数加入linit.conf末尾
    * soft nofile 65535
    * hard npfile 65535
        *         对所有用户有效
        soft    指的是当前系统生效
        hard    表明系统所能设置的最大值
        nofile  表示多限制的资源打开文件的最大值
        65535   表示限制的数量
3 磁盘调度策略优化 /sys/block/devname/queue/scheduler
    deadline     # 截止时间调度策略  适合数据库
    anticipatory # 预料I/O调度策略  适合写入较多的文件服务
    echo "deadline" > /sys/block/sda/queue/scheduler  # 修改sda的磁盘策略
4 文件系统优化
    1 最好用xfs
    2 ext4优化 /etc/fstab
        /dev/sda1     /data    noatime,nodiratime,data=writeback 1 1
        
        noatime    # 不更新文件系统上 inode 访问记录，可以提升性能
        nodiratime # 不更新文件系统上的目录 inode 访问记录
        data=writeback # 数据不做日志，数据和元数据写入无顺序。性能最好的模式
        data=ordered   # 数据先落盘，再做元数据的日志。性能会略逊色于 writeback 但是比 journal 模式要好
        data=journal   # 所有的数据都会被先写入到日志里，journal模式性能是三种模式中最低的
        _netdev    # 网络存储设备启动后才挂载，如nfs，icsci
        defaults   # 默认表示挂载类型,rw,suid,exec,auto,nouser,async.大多是默认值
            rw     # 读写
            suid   # 可以有suid权限
            exec/noexec   # 是能可进行执行二进制操作
            auto   # 主动挂在
            nouser # 限制此文件系统内是否能够进行"执行"的操作
            async  # 异步方式运行

优化 mysql 之 mysql本身的优化

1 版本
    存储引擎版本的选择：5.5.8之后innodb代替myasam
2 配置文件优化
    1 内存配置相关参数
    sort_buffer_size
    join_buffer_size
    read_buffer_size
    read_rnd_buffer_size
    Innodb_buffer_pool_size  # 总内存-（每个现成所需要的内存*连接数）-系统保留内存
    key_buffer_size
    2 IO相关配置
    Innodb_log_file_size
    Innodb_log_files_in_group
    Innodb_log_buffer_size
    Innodb_flush_log_at_trx_commit 0
    Innodb_flush_method = O_DIRECT
    Innodb_file_per_table = 1
    Innodb_doublewrite = 1

mysql的二进制日志选择

Binlog_format=row
Binlog_row_image=minimal

配置mysql主从复制

1 基于日志点的复制配置步骤 5.5 - 5.6
    1 初始化从服务器数据
        mysqldump --master-data=2 --single-transaction xtrabackup --slave-info  
    2 启动复制链路    连接主库 并主库的什么文件和什么位置同步binlog文件（注意提前授权)
        CHANGE MASTERTOMASTER_HOST= 'master_host_ip' ,
                    MASTER_USER= 'repl' ,
                    MASTER_PASSWORD= 'password' ,
                    MASTER_LOG_FILE= 'mysql_log_file_name' ,
                    MASTER_LOG_POS=4;

2 基于GTID复制 5.7
    1 主库配置
        1 授权
        2 开启binlog 最好与data分开 server_id gtid_mode
            bin_log = /usr/local/mysql/log/mysql-bin
            server_id = 100
            gtid_mode = on
            enforece-gtid-consistency
            enforece-gtid-consistency
            log-slave-updates = on
             5.6 要启动log-slave-updates = on 5.7不用
    2 从库配置
        1 配置文件
            server_id = 101
            relat_log = /usr/local/mysql/log/relay_log
            gtid_mode = on
            enforce-gtid-consistency
            log-slave-updates = on
            read_only = on
            master_info_repository = TABLE
            rekay_log_info_repository = TABLE
    3 主库前两备份，并恢复到从库（记录备份时的GTID）
        1 初始化从服务器数
        mysqldump --master-date=2 --single-transaction --triggers --routines --all-databases -uroot -pKc@123456 > all.sql
        xtarbackup --slave-info
        2 进入从库 指定主库信息启动gtid的复制
        CHANGE MASTERTOMASTER_HOST='master_host_ip' ,
                MASTER_USER='repl' ,
                MASTER_PASSWORD='password' ,
                MASTER_AUTO_position=1
        3 进入从库启动复制
        mysql> start slave;
        4 从库检查是否启动
        show slave status \
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

GTID复制优缺点

# 优点
    可以很方便的进行故障转移
    从库不会丢失主库上的任何修改
# 缺点
    故障处理比较复杂
    对执行的sql有意思一定限制

如何选择复制模式

mysql版本
复制架构以及主从切换的方式
所使用的的该可用管理组件
对应用的支持程度

复制架构

# 一主多从
    为不同业务使用不同的从库
    将某台从库放到远程IDC用作灾备
    分担主库的 读 负载

主从延迟问题

主库写入二进制日志的时间
控制主库的食物大小，分割大事务

解决主从延迟问题

1 大事务分割成小事务
2 日志格式
    设置 set binlog_row_image=minimal;
3 mysql5.7 开启多线程复制（之前是单线程）
4 在主从复制的基础上在从库上配置多线程复制
    stop slave  # 关闭复制
    show variables like 'slave_parallel_type'; # 查看原本的链路方式并发类型模式
        slave_parallel_type DATABASE  
    set global slace_parallel_type='logical_clock'; # 设置链路并发的为时钟模式
    set global slave_parallel_workers=4; # 设置4个线程
    start slave; # 开启复制
    show processlist\G  # 查看IO进程 \G

主从复制常见问题

# 由于数据损坏或丢失锁一起的主从复制错误
    1 主库或从库意外蹦极引起的错误
    2 主库上的二进制日志损坏
    3 备库上的中继日志损坏
# 在从库上进行数据修改造成的主从复制错误
# 不唯一的server_id 或 server_uuid
# max_allow_packet 设置引起的主从复制错误