目录
5. 简历案例---zabbix监控系统架构整改(2000多台主机)
1.简介
相当于Linux文件系统,只不过比文件系统强大
2、功能了解
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
3. 存储引擎介绍
show engines; CSV MRG_MYISAM MyISAM BLACKHOLE PERFORMANCE_SCHEMA MEMORY ARCHIVE InnoDB FEDERATED
笔试题: 常见的存储引擎？
InnoDB ,MyISAM ,MEMORY,CSV
MySQL默认的存储引擎:InnoDB
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
第三方的存储引擎:
RocksDB MyRocks TokuDB
压缩比高,数据的插入性能高.其他功能和InnoDB没差.
4. Innodb于myisam 的区别 ***
（1）innodb支持事务
（2）InnoDB支持外键
（3）InnoDB是聚集索引，myisam是非聚集索引
（4）InnoDB不保存表的具体行数，而MyISAM用一个变量保存了整个表的行数
（5）Innodb不支持全文索引，而MyISAM支持全文索引
（6）MyISAM表格可以被压缩后进行查询操作
（7）InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
（8）InnoDB表必须有唯一索引
（9）Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
5. 简历案例---zabbix监控系统架构整改(2000多台主机)
环境: zabbix 3.2 mariaDB 5.5 centos 7.3
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
（1）zabbix 版本
（2）数据库版本
（3）zabbix数据库500G,存在一个文件里
优化建议:
（1）数据库版本升级到mariaDB最新版本,zabbix升级更高版本
（2）存储引擎改为tokudb
（3）监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
（4）关闭binlog和双1
（5）zabbix监控参数调整
优化结果:
监控状态良好
为什么?
（1）Mariadb支持TokuDB,经过测试:10版本要比5.5 版本性能 高 2-3倍
（2）TokuDB:insert数据比Innodb快的多，数据压缩比要比Innodb高
（3）监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
（4）关闭binlog ----->减少无关日志的记录.
（5）参数调整...----->安全性参数关闭,提高性能.
6. InnoDB 存储引擎核心特性说明
事务
行锁：Myisam为表锁
MVCC：多版本并发控制
外键
ACSR：自动故障恢复
热备
复制(多线程,GTID：全局事务标识,MTS：并行复制)
7. InnoDB和MyISAM存储引擎的替换(客户案例)
环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
（1）MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
（2）MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责：
（1）监控锁的情况:有很多的表锁等待
（2）存储引擎查看:所有表默认是MyISAM
解决方案:
（1）先升级mysql 5.5，再升级MySQL 5.6.10版本
（2）迁移所有表到新环境
（3）开启双1安全参数
======================================================================
mysql的"双1验证"指的是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，这两个是是控制MySQL 磁盘写入策略以及数据安全性的关键参数。下面从参数含义，性能，安全角度阐述两个参数为不同的值时对db 性能,数据的影响。
1.参数意义
innodb_flush_log_at_trx_commit
设置为0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作;
设置为1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去;
设置为2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
注意：由于进程调度策略问题,这个"每秒执行一次 flush(刷到磁盘)操作"并不是保证100%的"每秒"。
sync_binlog
默认值是0，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。
注意：如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。
2. 性能
查看当前参数值：
show variables like 'innodb_flush_log%';
show variables like 'sync_binlog%';
两个参数在不同值时对db的纯写入的影响表现如下：
测试场景1
innodb_flush_log_at_trx_commit=2
sync_binlog=1000
测试场景2
innodb_flush_log_at_trx_commit=1
sync_binlog=1000
测试场景3
innodb_flush_log_at_trx_commit=1
sync_binlog=1
在以上3个场景下的TPS分别为:
场景1 41000
场景2 33000
场景3 26000
由此可见，当两个参数设置为双1的时候，写入性能最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。
3. 安全
当innodb_flush_log_at_trx_commit和sync_binlog 都为 1 时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双11 会导致频繁的io操作，因此该模式也是最慢的一种方式。
当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
"双1设置"适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时 比如11.11 活动的压力。推荐的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。
============================================================================
1.存储过程定义
存储过程是一组为了完成特定功能的 SQL 语句集合。
使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。
2.存储过程的优点：
（1）封装性
存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。
（2）可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
（3） 可减少网络流量
由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
（4）高性能
存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。
（5）提高数据库的安全性和数据的完整性
使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。
3.创建无参数存储过程（CREATE PROCEDURE）
create database school default charset utf8mb4 collate utf8mb4_bin; use school; create table tb_students_score (student_name char(20) not null,student_score int); insert into tb_students_score values ('dany','90'),('green','99'),('henry','95'),('jane','98'),('jim','88'); DELIMITER // #修改mysql结束符为// CREATE PROCEDURE ShowStuScore() BEGIN SELECT * FROM tb_students_score; END // 调用存储过程 DELIMITER ; #把mysql结束符改回;号 CALL ShowStuScore();
4.创建有参数的存储过程
DELIMITER // CREATE PROCEDURE GetScoreByStu (IN name VARCHAR(30)) BEGIN SELECT student_score FROM tb_students_score WHERE student_name=name; END // 调用存储过程 DELIMITER ; CALL GetScoreByStu('green');
5.修改存储过程
ALTER PROCEDURE <过程名> [ <特征> … ]
6.删除存储过程
DROP PROCEDURE GetScoreByStu;
7.查看存储过程
show procedure status;
8.查看创建存储过程语法
show create procedure ShowStuScore;
===============================================================================
存储过程的参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT：
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
1、in 输入参数
delimiter // create procedure in_param(in p_in int) begin select p_in; set p_in=2; select p_in; end// delimiter ; set @p_in=1; call in_param(@p_in); select @p_in;
可以看出，p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为前者为局部变量、后者为全局变量。
2、out输出参数
delimiter // create procedure out_param(out p_out int) begin select p_out; set p_out=2; select p_out; end // delimiter ; set @p_out=1; call out_param(@p_out);
因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
select @p_out;
调用了out_param存储过程，输出参数，改变了p_out变量的值
3、inout输入参数
delimiter // create procedure inout_param(inout p_inout int) begin select p_inout; set p_inout=2; select p_inout; end // delimiter ; set @p_inout=1; call inout_param(@p_inout); select @p_inout;
调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量
