MySQL数据库之存储引擎（附简历案例，客户案例）

作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢大家的关注
座右铭：云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
个人主页：团儿.-CSDN博客

前言：

在当今的信息化时代，数据库作为信息存储与管理的核心基础设施，其性能、可靠性和可扩展性直接决定了应用的运行效率和用户体验。MySQL，作为世界上最流行的开源关系型数据库管理系统之一，广泛应用于各类网站、应用程序以及企业级解决方案中。而MySQL之所以能在众多数据库系统中脱颖而出，很大程度上得益于其灵活且强大的存储引擎架构。

存储引擎，作为MySQL数据库的核心组件，是数据在物理层面进行存储、索引、更新和查询的关键技术实现。MySQL允许用户根据不同的应用需求和场景，为数据库表选择不同的存储引擎，以实现最优的性能和功能。这种设计思路极大地增强了MySQL的灵活性和适应性，使其能够满足从简单的Web应用到复杂的企业级数据仓库的各种需求。

在MySQL的发展历程中，多种存储引擎应运而生，各自拥有独特的特性和适用场景。其中，MyISAM和InnoDB是最具代表性的两种存储引擎。MyISAM以其快速读取和高效的全文索引能力，成为早期MySQL的默认存储引擎，广泛应用于读操作频繁的应用场景。而InnoDB，自MySQL 5.5版本成为默认存储引擎以来，凭借其支持事务处理、行级锁定和外键约束等高级特性，成为处理高并发和数据一致性要求较高的应用的首选。

本文将深入探讨MySQL存储引擎的概念、分类、特点以及适用场景（附简历案例，客户案例），重点介绍MyISAM和InnoDB两种存储引擎的详细对比，帮助读者更好地理解如何选择适合自身应用需求的存储引擎。

正文：

一.1.简介

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

存储引擎是数据库管理系统（DBMS）中的核心组件，它负责执行实际的数据I/O操作，将数据存储在文件系统中，并提供不同的存储机制、索引技巧、锁定水平等功能。在MySQL等数据库系统中，存储引擎是基于表的，即数据库中的每一个表都可以指定专用的存储引擎。

2、功能了解

数据读写

数据安全和一致性

提高性能

热备份

自动故障恢复

高可用方面支持

3. 存储引擎介绍

show engines;

CSV

MRG_MYISAM

MyISAM

BLACKHOLE

PERFORMANCE_SCHEMA

MEMORY

笔试题: 常见的存储引擎？

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存储过程，接受了输入的参数，也输出参数，改变了变量

期待您的关注~

标签：存储,简历,log,flush,案例,引擎,InnoDB,MySQL
From： https://blog.csdn.net/xyyy060908/article/details/142203459

MySQL数据库之存储引擎（附简历案例，客户案例）

前言：

正文：

一.1.简介

2、功能了解

3. 存储引擎介绍

笔试题: 常见的存储引擎？

第三方的存储引擎:

4. Innodb于myisam 的区别 ***

5. 简历案例---zabbix监控系统架构整改(2000多台主机)

环境:

现象 :

问题 :

优化建议:

优化结果:

为什么?

6. InnoDB 存储引擎核心特性说明

事务

外键

热备

7. InnoDB和MyISAM存储引擎的替换(客户案例)

问题分析:

职责：

解决方案:

二.1.参数意义

innodb_flush_log_at_trx_commit

设置为0：

设置为1：

设置为2：

sync_binlog

2. 性能

查看当前参数值：

两个参数在不同值时对db的纯写入的影响表现如下：

测试场景1

测试场景2

测试场景3

在以上3个场景下的TPS分别为:

3. 安全

三.1.存储过程定义

2.存储过程的优点：

（1）封装性

（2）可增强 SQL 语句的功能和灵活性

（3） 可减少网络流量

（4）高性能

（5）提高数据库的安全性和数据的完整性

3.创建无参数存储过程（CREATE PROCEDURE）

调用存储过程

4.创建有参数的存储过程

调用存储过程

5.修改存储过程

6.删除存储过程

7.查看存储过程

8.查看创建存储过程语法

四.存储过程的参数

IN 输入参数：

OUT 输出参数：

INOUT 输入输出参数：

1、in 输入参数

2、out输出参数

3、inout输入参数

相关文章

赞助商

阅读排行

（3）可减少网络流量