MySQL​存储引擎简介

大家好,我是anyux。本文介绍MySQL存储引擎。

简介

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

功能

数据读写

数据安全和一致性

提高性能

热备份

自动故障恢复

高可用方面支持

引擎类别

showengines;

引擎类型如下

CSV

MRG_MYISAM

MyISAM

BLACKHOLE

PERFORMANCE_SCHEMA

MEMORY

ARCHIVE

InnoDB

FEDERATED

必须要了解引擎类型

InnoDB,MyISAM,MEMORY,CSV

MySQL存储引擎从5.5以后默认使用的是InnoDB,从8.0以后就放弃了MyISAM

另外MySQL还支持插件式开发存储引擎。比较出名的存储引擎有RocksDB,MyRocks,TokuDB。这三个存储引擎压缩比较高,数据插入性能高,其他功能与InnoDB无差别,所以比InnoDB优秀,属于第三代存储引擎

PerconaDB使用的是XtraDB

MariaDB:默认使用是InnoDB,MySQL的InnoDB和这个是不一样的

InnoDB存储引擎核心特性说明

存储引擎替换

InnoDB替换MyISAM

环境:CentOS5.8 MySQL5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右

问题:业务压力大时,非常卡,经历过宕机,会有部分数据丢失

问题分析:

MyISAM存储引擎表级锁,在高并发时,会有很高的锁等待

MyISAM存储引擎不支持事务,在断电时,有可能会丢失数据

监测状态

监控锁的状态,有很多的表锁等待

存储引擎查看:所有表默认是MyISAM

解决方案

升级到MySQL 5.6版本。注意,低版本数据库要先升级到5.5,再升级到5.6。直接升级到5.6会出现服务无法启动的情况

表数据迁移

开启双1安全参数

查看存储引擎

创建表时,如果没有指定存储引擎,则使用默认的存储引擎

打印所有支持的存储引擎

showengines;

查看当前的存储引擎

select@@default_storage_engine;

可以在配置文件中,设置存储引擎

vim/etc/my.cnf

[mysqld]

default_storage_engine=InnoDB

查看自建的所有库表的存储引擎

selecttable_schemaas数据库,group_concat(concat(" ",table_name,":",engine," "))as数据表frominformation_schema.tableswheretable_schemanotin('sys','mysql','information_schema','performance_schema')groupbytable_schema;

存储引擎修改

altertabletmp_db.tmp_studentengine=MyISAM;

再修改回InnoDB

altertabletmp_db.tmp_studentengine=InnoDB;

查看单表的存储引擎

showcreatetabletmp_db.tmp_student;

整理碎片

在做delete删除操作时,会对表进行逐行删除数据,删除后原来所占用的空间,不会被立即释放,因此产生碎片。

在服务运行期间,这些碎片空间不会被服务回收,因此占用的无效空间相对变大,使用索引时会产生无效索引,进而影响服务的性能。

因此需要整理碎片。让数据行更加紧凑,让查询索引时性能变强。

环境:CentOS7.4,MySQL5.7,InnoDB存储引擎

业务特点:数据量级较大,经常需要按月删除历史数据

问题:磁盘空间占用很大,不释放

最直接的办法是,将表中所有数据导出,删除表,再重新导入到服务中。

这种方法就是有点折腾,而且需要业务停止后才方便操作。如果业务需要24小时运行,那就要使用到第二种方法了

对表进行按月分表(partition,中间件),业务替换为truncate方式,定期进行碎片整理

批量替换存储引擎

selectconcat("alter table ",table_schema,".",table_name," engine=toukudb;")as批量替换语句frominformation_schema.tableswheretable_schema='zabbix';

可以将这些语句,重定向到文件,使用shell脚本执行

MyISAM由三个文件构成,FRM,FYD,MYI。FRM用来存储表结构,FYD用来存储数据行,MYI用来存储索引

InnoDB物理结构

在5.6版本中,ibdata1包含存储系统元信息,undo表空间数据和临时表空间

在5.7版本中,文件结构与作用如下

表空间

在5.7版本中下面两个参数长的很像,名字叫起来也相似,但完全不一样,需要重点牢记

ibdata1:整个库的统计信息和undo信息

ibd:数据行和索引信息

关于ibdata1是如何出现的,又是如何没落了。

虽然ibdata1和ibd是两个文件,但是在MySQL系统中将它们两个称为表空间(Tablespace)

现在我们使用磁盘,都是将一块300G的磁盘,格式化并且挂载到/data目录下,给MySQL服务使用。半年后,磁盘几乎被占满。此时又添加一块新的600G磁盘,格式化并且挂载到/data目录下,给MySQL服务使用。又半年后,又需要重新挂载磁盘,并且每次都需要停止数据库服务,那么有没有可能不重启也能实现数据在磁盘间的转移呢?

此时就使用到了表空间,表空间这个名词是来自于Oracle数据库。Oracle就是将数据库中的数据存储到表空间(Tablespace),而在表空间内存,会分配数据存储到sdb1,sdc1等磁盘分区位置上,也就是说磁盘空间的增加对于数据库服务而言是透明的,是不可感知的

而对于MySQL到了5.5这个版本,已经被Oracle收购了。就将表空间的概念引入到MySQL中,MySQL也具备了表空间的管理模式。不足的是MySQL访问数据是基于文件系统去访问,表空间被迫改成了共享表空间,分别存储到各个磁盘下。

最初始时只要是个innodb引擎的表,都需要将数据行,索引,元信息和undo信息都存入到共享表空间,共享表空间再以不可见的形式存入到各大磁盘下,全都存入了ibdata1中。这样做的好处是可以实现类似Oracle的表空间管理模式,做一些扩容。问题是所有数据全部写入到几个有限的文件中,一旦数据多了,处理效率就会出现严重下降

所以到了5.6版本就改成了独立表空间模式,独立表空间解决了共享表空间数据"扎堆"的问题,解决方式是独立存储每一张表,每张表都自己的ibd,frm,见下图

需要知道现在共离表空间还在,只不在存储每张表的数据了,而是存储库的元信息和undo信息。而独立表空间存储数据行和索引信息

对于mariaDB和PerconaDB和5.5版本,需要更新,否则一旦数据量增长到一定量级,会导致性能下降

对于共享表空间(ibdata1~N),自5.5版本以来,其负责的职责就被不同的程度的剥离

5.6版本以后,共享表空间得以保留,只用来存储:数字字典信息,undo信息,临时表

5.7版本以后,临时表被剥离

8.0版本以后,undo信息被剥离

查看共享表空间路径

select@@innodb_data_file_path;

下图表示,第一次启动时创建共享表空间ibdata1,初始大小为12M,空间不够时,会自动增长64M的空间

使用du -sh 命令查看文件大小

du-sh/data/mysql/data/ibdata1;

查看共享表空间默认每次自动增长值

showvariableslike'innodb_data_file_path';

showvariableslike'%extend%'

共享表空间参数的配置需要要创建数据库服务时配置,否则无法修改

独立表空间

从MySQL5.6版本开始,默认表空间已经变成了独立表空间,而不再是共享表空间

独立表空间主要用于存储用户数据

每一个表都有一个ibd文件,用于存储数据及索引信息

基本表结构元数据存储在frm文件

mysql数据表是由元数据,数据,索引构成

mysql数据库是由ibdata1~N和frm(元数据),ibd文件(段区页)

一张InnoDB表由frm,ibd,ibdata1构成

查看独立表空间变量

select@@innodb_file_per_table;

如果将innodb_file_per_table设置为0,那么以后创建的数据表,就没有了独立表空间了

修改独立表空间变量

setglobalinnodb_file_per_table=;

不建议修改此变量,原因是改为0后,所以表将存储到共享表空间中,数据量增长后,容易导致数据库性能瓶颈

MySQL的存储引擎日志

Redo Log: ib_logfile0 ib_logfile1 重做日志

Undo Log: ibdata1 ibdata2(存储在共享表空间中),回滚日志

临时表:ibtmp1,在做join union操作产生临时数据,用完就自动清理

表空间迁移

参考https://www.cnblogs.com/nodchen/p/9416175.html

建立与原表一样的空表

discard空表的ibd文件

复制原表的ibd文件,修改权限,import原表的ibd文件

查询验证

altertablecity dicardtablespace;

altertablecityimporttablespace;

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20200306A03VG000?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券