第五章：MySQL-存储引擎_mysql 使用memory引擎-CSDN博客

本文深入探讨了MySQL的存储引擎，重点介绍了InnoDB和MyISAM的区别，以及InnoDB的ACID特性。通过实际案例，讨论了truncate与delete的区别，并详细解析了InnoDB的事务管理，包括事务的开始、结束、自动提交策略和隔离级别。此外，还讨论了redo和undo日志在事务中的角色，以及如何通过参数调整优化InnoDB存储引擎的性能。

摘要由CSDN通过智能技术生成

1、简介

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

mysql—>文件系统—>磁盘
mysql的数据需要经过两次缓存才能到磁盘中。

2、功能了解

数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
等.

3、存储引擎种类（笔试）

3.1 介绍(Oracle MySQL)

InnoDB    ------重点
MyISAM    ---重点
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV

3.2 引擎种类查看

show engines;
存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。
select table_name, engine from information_schema.tables where engine='innodb';
select table_schema,table_name ,engine from information_schema.tables where engine='memory';
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB    
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.

3.3 简历案例—zabbix监控系统架构整改

环境: zabbix 3.2    mariaDB 5.5  centos 7.3
现象 : zabbix卡的要死 ,  每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
1. zabbix 版本 
2. 数据库版本
3. zabbix数据库500G,存在一个文件里
优化建议:
1.数据库版本升级到5.7版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....
优化结果:
监控状态良好

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

truncate和delete的区别？

truncate:立即释放空间
delete:不利己释放空间

3.4 InnoDB个MyISAM存储引擎的替换

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数

4、InnoDB存储引擎介绍

image.png

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。

4.1 优点

1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )

4.2 笔试题

# 请你列举MySQL InnoDB存储优点？
1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL ) 
#请你列举 InooDB和MyIsam的区别？
innodb 支持行   支持热备   MVCC ACSR
myisam:支持表   支持分备

5. 存储引擎查看

5.1 使用 SELECT 确认会话存储引擎

SELECT @@default_storage_engine;
## 5.2 存储引擎(不代表生产操作)
会话级别:
set default_storage_engine=myisam;
全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.
如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

5.3 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G;
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

5.4 INFORMATION_SCHEMA 确认每个表的存储引擎

[world]>select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

Master [world]>show table status;
show table status from world ;

Master [world]>show create table city;

5.5 修改一个表的存储引擎

db01 [oldboy]>alter table world.t1 engine=innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理


修改所有表的时候用concat拼接一下
select concat("alter table ",table_schema,'.',table_name," engine=innodb") from information_schema.tables where table_schema='world';
+---------------------------------------------------------------------+
| concat