存储引擎-1

标签：存储 log 引擎 参数 flush 过程 out

简介

相当于Linux文件系统，只不过比文件系统强大

功能

数据读写

数据安全和一致性

提高性能

热备份

自动故障恢复

高可用方面支持

查看存储引擎

show engines;

常见存储引擎：

InnoDB、MyISAM、MEMORY、CSV

MySQL5.7默认的存储引擎：InnoDB

PerconaDB默认是：XtraDB

MariaDB默认是：InnoDB

第三方的存储引擎：

RocksDB、MyRocks、TokuDB

压缩比高，数据的插入性能高，其他功能和InnoDB没差别

RocksDB：是领先的嵌入式键值存储引擎，已在各行各业得到广泛应用。Meta、微软、Netflix和Uber等知名公司都已将RocksDB集成到生产环境

MyRocks、RocksDB：是facebook基于LevelDB实现的，目前为facebook内部大量业务提供服务

TokuDB：是一个开源的高性能存储引擎，适用于MySQL和MariaDB

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（索引）

简历案例---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（双1是mysql的日志刷新模式，安全高，但读写性能差）
5. zabbix监控参数调整

优化结果：

监控状态良好 

为什么?

1. Mariadb支持TokuDB，经过测试:10版本要比5.5版本性能高 2-3倍
2. TokuDB：insert数据比Innodb快的多，数据压缩比要比Innodb高
3. 监控数据按月份进行切割，为了能够truncate每个分区表，立即释放空间
4. 关闭binlog ----->减少无关日志的记录
5. 参数调整...----->安全性参数关闭,提高性能

InnoDB 存储引擎核心特性说明

事务

行锁：Myisam为表锁

MVCC：多版本并发控制

外键

ACSR：自动故障恢复（把内存中的数据，关电源的一刻写入到硬盘上）

热备（快照把当前的表拷贝一份，就不会出现缩表的问题了）

复制 (多线程，GTID：全局事务标识，MTS：并行复制)

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版本
2. 迁移所有表到新环境
3. 开启双1安全参数

mysql的"双1验证"指的是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，这两个是是控制MySQL 磁盘写入策略以及数据安全性的关键参数。下面从参数含义，性能，安全角度阐述两个参数为不同的值时对db 性能、数据的影响

• 参数意义：

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就会有一次写操作；否则每个事务对应一个写操作

• 性能

查看当前参数值（默认双一模式）：

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的写操作才能达到最高性能。

• 安全

当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，防止系统断电异常。

存储过程定义（类似shell脚本，但提前编译好了二进制存储起来，比直接执行命令提高速度2-7倍）

存储过程是一组为了完成特定功能的 SQL 语句集合。

使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。

存储过程的优点

• 封装性

存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

• 可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

• 可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

• 高性能

存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。

• 提高数据库的安全性和数据的完整性

使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。

总结：调用存储过程名称，不用写语句

创建实验数据库和表并写入内容

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 //

create procedure showstuscore()

begin

select * from tb_students_score;

end //

delimiter ;

调用存储过程

call showstuscore();

创建有参数的存储过程

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');

修改存储过程

ALTER PROCEDURE <过程名> [ <特征> … ]

删除存储过程

drop procedure getscorebystu;

查看存储过程

show procedure status;

查看创建存储过程语法

show create procedure showstuscore;

存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型IN、OUT、INOUT

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

• 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 的值，因为前者为局部变量，后者为全局变量

• 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变量的值

• 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);

调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

标签：存储,log,引擎,参数,flush,过程,out
From： https://blog.csdn.net/2402_88627342/article/details/144986760