1.什么是存储引擎
相当于MySQL内置的文件系统。与 Linux中的文件系统打交道的层次结构。
2. MySQL存储引擎种类
mysql> show engines;
MRG_MYISAM
CSV
MyISAM
BLACKHOLE
PERFORMANCE_SCHEMA
InnoDB MySQL5.5版本以后默认的存储引擎。99%以上的业务表是InnoDB
ARCHIVE
MEMORY
FEDERATED
2.1其他分支
percona : XtraDB
MariaDB : InnoDB
其他的引擎:
TokuDB , MyRocks
Compression: 25x for high compression
Fast Insertions and Deletions
适合于，业务当中有大量插入或者删除操做的场景。
应用于，数据量较大的业务。
2.2实际案例
环境: zabbix 3.2 + centos 7.3 + mariaDB 5.5 InnoDB引擎 ,zabbix系统 监控了2000多个节点服务
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
1. zabbix 版本
2. 数据库版本
3. zabbix数据库500G,存在一个文件里ibdata1，手工删除1个月之前的数据，空间不释放。
优化建议:
1.数据库版本升级到percona 5.7+ 版本 mariadb 10.1+,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.参数调整...----->安全性参数关闭,提高性能.
3. InnoDB 核心特性介绍
1 Clustered index2 Change buffer3 自适应hash索引：AHI4 MVCC5 多缓冲区池6 事务7 行级锁粒度8 外键9 更多复制特性10 支持热备11 自动故障恢复
生产项目
小项目：
环境: 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. 存储引擎的基础管理命令
1 使用 SELECT 确认会话存储引擎
SELECT @@default_storage_engine;
2 默认存储引擎修改(不代表生产操作)
set default_storage_engine=myisam;
3 全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.
4 永久生效
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.
4.1 SHOW 确认每个表的存储引擎：
SHOW CREATE TABLE x\G;
SHOW TABLE STATUS LIKE 'xx'\G
4.2 INFORMATION_SCHEMA 确认每个表的存储引擎
select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
4.3修改一个表的存储引擎
mysql> alter table x engine=innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理
平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)或者归档表(pt-archive)
业务替换为truncate方式
4.4扩展:如何批量修改
需求1:将zabbix库中的所有表,innodb替换为tokudb
select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';
需求2：将所有非InnoDB业务表查询出来，并修改为InnoDB
select concat("alter table ",table_schema,".",table_name," engine=innodb;" )
from information_schema.tables
where engine != 'InnoDB'
and table_schema not in ('sys','performance_schema','information_schema','mysql')
into outfile '/tmp/alter.sql' ;
5. MySQL 存储引擎体系结构
5.1宏观结构
5.1.1 MyISAM
myt.frm ： 数据字典信息(列的定义和属性)
myt.MYD ： 数据行
myt.MYI ： 索引
5.1.2 InnoDB
city.frm ： 数据字典信息(列的定义和属性)
city.ibd(独立表空间文件) ： 数据行和索引
ibdata1 (共享表空间文件) ： 数据字典信息，UNDO(事务回滚日志)
double write 磁盘区域，change buffer磁盘区域
说明： 不同版本ibdata1中存储的数据不一样
5.5 ： ibdata1 中还会存储临时表数据 +用户数据(数据行+索引)
5.6 ： ibdata1 中还会存储临时表数据 5.1.2
8.0 ： ibdata1取消存储 数据字典信息，undo 独立了。
你可以理解为，MySQL在慢慢瘦身ibdata1共享表空间，把比较关键的数据独立出来了。
ib_logfile0~ib_logfileN: InnoDB 事务重做日志(redo log)
ibtmp1: 临时表空间文件(排序，分组，多表连接，子查询，逻辑备份等。)
ib_buffer_pool：正常关库的时候，存储缓冲区的热数据。
所以： InnoDB的表，仅仅是拷贝ibd 和 frm 文件到新的数据库，是无法正常使用。
5.2 InnoDB 微观结构
5.2.1 磁盘
数据存储：
(1) 表空间
1>. 什么是表空间？
表空间概念是引入于Oracle数据库。
起初为了解决存储空间扩展的问题。MySQL5.5 版本引入了共享表空间模式。
2>. MySQL 表空间类型
共享表空间：
在5.5 版本引入了共享表空间(ibdata1)，作为默认存储方式。
用来存储：系统数据，日志，undo，临时表，用户数据和索引。
独立表空间： 5.6 版本默认独立表空间模式。单表单表空间。
普通表空间： 完全和Oracle一致的表空间管理模式
undo表空间： 存储undo logs(回滚日志)
临时表空间 存储临时表。5.7 默认独立。
3>. 表空间管理
用户数据默认的存储方式，独立表空间模式。独立表空间和共享表空间是可以互相切换的。
-- 查看默认表空间模式？
mysql> select @@innodb_file_per_table;
说明：
1代表独立表空间
0代表共享表空间模式
-- 如何切换 ？
临时：
mysql> set global innodb_file_per_table=0;
重新登陆会话：
永久：
vim /etc/my.cnf
innodb_file_per_table=0
-- 验证
mysql> select @@innodb_file_per_table;
说明: 修改完成之后，只影响新创建的表。
-- 如何扩展共享表空间大小和个数？
说明： 通常是在初始化数据时，就设定好参数。
方法1：初始化之前，需要在my.cnf 加入以下配置即可：
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
方法2：已运行的数据库上扩展多个ibdata文件
5.2.2事务日志
redo log 重做日志
(1)文件位置：
/data/3306/ib_logfile0~ib_logfileN
(2)控制参数：
innodb_log_file_size=50331648 # 设置文件大小
innodb_log_files_in_group=2 # 设置文件个数
innodb_log_group_home_dir=./ # 设置存储位置
(3)功能：
用来存储，MySQL在做修改类(DML)操作时的《数据页变化》过程及版本号(LSN)，属于物理日志。
默认两个文件存储redo，是循环覆盖使用的。
undo logs 回滚日志
(1) 文件位置：(5.7 默认位置)
ibdataN
ibtmp1
(2) 参数：
innodb_rollback_segments=128 # 回滚段的个数
(4) 功能：
用来存储回滚日志，可以理解为记录了每次操作的反操作，属于逻辑日志。
1. 使用快照功能 提供InnoDB多版本并发读写。
2. 通过记录的反操作，提供回滚功能。
5.2.2 内存
(1)数据内存区域：
(1.1)共享内存缓冲区
buffer pool 缓冲区池：
参数：
mysql> select @@innodb_buffer_pool_size;
功能：
缓冲 数据页 + 索引页
(1.2) 会话内存缓冲区
join_buffer_size
key_buffer_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
(2) 日志
innodb_log_buffer_size=16777216
功能: 负责redo日志的缓冲