MySQL-存储引擎

最新推荐文章于 2024-01-02 12:38:30 发布

一个憨豆

最新推荐文章于 2024-01-02 12:38:30 发布

阅读量151

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_52636854/article/details/114643590

版权

数据库专栏收录该内容

17 篇文章

订阅专栏

一、存储引擎介绍

1.什么是存储引擎？

相当于mysql内置的文件系统。
与linux中的文件系统打交道的层次结构

2.MySQL存储引擎的种类

2.1 Oracle MySQL

可以针对不同的表，设定不同的存储引擎
#查看当前版本支持的存储引擎
mysql> shows;
InnoDB
MRG_MYISAM         
MEMORY
BLACKHOLE    
MyISAM         
CSV              
ARCHIVE      
PERFORMANCE_SCHEMA 
FEDERATED     

#列举MySQL中支持的存储引擎种类？
InnoDB
MyIsam
CSV
MEMORY

2.2 其他分支

percona ： XtraDB
Mariadb ： InnoDB
#其他的引擎
TokuDB，MyRocks
TokuDB：
Compression：25x for high compression
Fast Insertions and Deletions
适合于，业务当中有大量插入或者删除操作做的场景
比较应用于，数据量较大的业务

案例

#环境：
zabbix 3.2 + centos 7.3 + mariadb 5.5 InnoDB引擎，zabbix系统 监控2000多个节点服务
#现象：
zabbix很卡，而且每隔3-4个月，都要重新搭建一遍zabbix，存储空间非常爆满，存储空间严重不足
#优化建议：
1.数据库版本升级到percona 5.7版本 mariadb 10.1+zabbix升级到更高版本（4.0 +）
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.参数调整---------->安全性参数关闭，提高性能

#可参考：
https://www.jianshu.com/p/898d2e4bd3a7
https://mariadb.com/kb/en/inatalling-tokudb/
https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html

二、InnoDB核心特性

1.InnoDB介绍

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PibZKJ9M-1615375076014)(C:\Users\86150\AppData\Roaming\Typora\typora-user-images\image-20210308113021780.png)]$

MVCC                   #多版本并发控制
群集索引                 #聚簇索引
多缓存区池 
事务
行级锁粒度
外键
更多复制性
支持热备
自动故障恢复
更改缓冲
自适应hash索引：AHI

1.1请列举InnoDB和MyISAM区别

#环境：
centos 5.8，MySQL 5.0 版本，MyISAM存储引擎，网站业务，数据量50G左右
#现象问题：
业务压力大的时候，非常卡，经历过宕机会有部分数据丢失
#问题分析：
1.MyISAM 存储引擎表级锁，在高并发时，会有更高级锁等待
2.MyISAM 存储引擎不支持事务，在断电时，会有可能丢失数据

#职责：
1.监控锁的情况：有很多的表锁等待
2.存储引擎查看：所有表默认是MyISAM

#解决方案：
1.升级到MySQL 5.6.10版本
2.迁移所有表到新环境
3.开启双1安全参数

三、存储引擎的基础管理命令

1.使用select确认会话存储引擎

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

2.存储引擎（不代表生产操作）

#会话级别：
mysql> set global default_storage_engine=myisam;
#全局级别（仅影响新会话）：
set global default_storage_engine=myisam；

重启之后，所有参数均失效
如果要永久生效：
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam；
存储引擎是表级别的，每个表创建时可以指定不同的存储引擎，但是我们统一为InnoDB

3.show 确认每个表的存储引擎

mysql> show create table x;
mysql> table status like 'xx'\G

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;
Master [world]>show create table city;

5.修改一个表的存储引擎

mysql> alter table t1 engine innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理

6.平常处理过的MySQL问题–碎片处理

#环境:
centos7.4,MySQL 5.7.20,InnoDB存储引擎
#业务特点:
数据量级较大,经常需要按月删除历史数据.
#问题:
磁盘空间占用很大,不释放
#处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)或者归档表（pt-archive）
业务替换为truncate方式

6.1扩展:如何批量修改

#需求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
mysql> select concat("alter table",table_schema,".","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';

四、MySQL存储引擎体系结构

1.宏观结构

#创建MyISAM表
mysql> create table myt (id int)engine=myisam;
myt.frm        ：数据字典信息（列的定义和属性）
myt.MYD        ：数据行        
myt.MYI        ：索引

ibdata1：系统数据字典信息(统计信息)，UNDO表空间等数据
ib_logfile0 ~ ib_logfile1: REDO日志文件，事务日志文件。
ibtmp1： 临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-crB4C34a-1615375076016)(C:\Users\86150\AppData\Roaming\Typora\typora-user-images\image-20210308151830246.png)]$

1.1InnoDB

city.frm:数据字典信息（列的定义和属性）
city.ibd（独立表空间文件）:数据行和索引
ibdtal（共享表空间文件）:数字字典信息。UNDO（事务回滚日志），double write磁盘区域，change buffer 磁盘区域

#说明：
不同版本ibdata1中存储的数据不一样
5.5：ibtable1中还会存储临时表数据+用户数据（数据行+索引）
5.6：ibtable1中还会存储临时表数据
8.0：ibtable取消存储 数据字典信息，undo独立

#可以理解为：
MySQL在慢慢瘦身ibtable1共享表空间，把比较关键的数据独立出来了

ib_logfile0-ib_logfileN:InnoDB  （事务重做日志）---->  redo log
ibtmp1：临时表空间文件（排序，分组，多表连接，子查询，逻辑备份）
ib_buffer——pool：正常关库的时候，存储缓冲区的热数据

#所以：
InnoDB的表，仅仅是拷贝ibd和frm文件到新的数据库，是无法正常使用

2.InnoDB微观结构

2.1 磁盘

1）表空间

数据的微观存储

#1.什么是表空间？
表空间的概念是引入于Oracle数据库
起初为了解决存储空间扩展问题。MySQL5.5版本引入了共享表空间模式

#2.MySQL表空间类型
共享表空间：
在5.5版本引入了共享表空间(ibtable1)，作为默认存储方式
用来存储，系统数据，日志，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;intable2:1G:autoextend
方法2：已运行的数据库上扩展多个ibdata文件
错误的方式：
innodb_data_file_path=ibdata1:128M；ibdata2:128M;ibdata3:autoextend

#解决方法：在设置innodb_data_file_path参数时，已有的ibdata1文件大小应该和磁盘上的大小一致，而不是随便指定的

正确的调整方式：
innodb_data_file_path=ibtable1:76M;ibtable2:128M;ibtable3:128M:autoextend

2)段区页

表---->表空间---->段---->多个区---->连续的page---->连续的block---->连续的扇区

事务日志：

--redo log 重做日志
1.文件位置： /data/3306/ib_logfile0~ib_logfileN
2.控制参数：mysql> show variables like '&innodb_log%';
innodb_log_files_size=50331648       设置文件大小
innodb_log_files_in_group=2          设置文件个数
innodb_log_group_home_dir=./         设置存储位置

功能：
用来存储，MySQL在做修改类（DML）操作时的数据页变化过程，属于物理日志。
默认两个文件存储redo，是循环覆盖使用的

--undo log 回滚日志
1.文件位置：（5.7默认位置）
	ibtableN
	ibtable1
2.参数：
innodb_rollback_segments=128    #回滚段的个数
3.功能：
	用来存储回滚日志。可以理解为记录了每次操作的反操作，属于逻辑操作
	1.使用快照提供InnoDB多版本并发读写
	2.通过记录反操作提供回滚功能

2.2 内存buffer

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=16777218

故障处理案例

#案例背景：
1.硬件：联想服务器（IBM）
2.软件环境：centos 6.8 MySQL5.6.33 InnoDB引擎  独立表空间
3.备份没有，日志也没开
4.开发用户专用库：jira（bug追踪），confluence（内部知识库）----limt

#故障描述
断电了。启动完成后“/”只读
fsck 重启，系统成功启动，mysql启动不了
结果：confulence库在，jira库不见了

#问题
这种情况要怎么恢复？
连二进制日志都没有，没有备份，没有主从
能不能暂时把confulence库先打开用着，将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql，直接访问时，暂时访问不了

#解决方法：
表空间迁移：
1.创建一个一模一样的t1表
create table t1   
2.删掉空表的表空间 ibd
alter table confluence.t1 discard tablespace;
#跳过外键检查
set foregin_key_checks=0
3.拷贝准备好的ibd，改权限，导入表空间到t1表
alter table confluence.t1 import tablespace;
4.验证数据

#问题2：
几亿行的表，想要删除其中1000W，你们公司都是怎么做的？假如是按照时间列条件

#解决方法：
1.如果几亿行数据表，还没有生成，建议在设计表时，采用分区表的方式（按月range），然后删除（truncate）
2.如果几亿行数据表，已经存在，建议使用pt-archive工具进行归档表，并且删除无用数据