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