存储引擎

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

  • 功能了解
  1. 数据读写
  2. 数据安全和一致性
  3. 提高性能
  4. 热备份
  5. 自动故障恢复
  6. 高可用方面支持

查看支持的存储引擎

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.05 sec)

笔试题:数据库常用的存储引擎

  • InnoDB ,MyISAM,MEMORY,CSV
  • 默认的存储引擎:InnoDB
  • 第三方的存储引擎:RocksDB,MyRocks,TokuDB,压缩比较高,数据的插入性能高.其他功能和InnoDB没差
  • PerconaDB:默认是XtraDB
  • MariaDB:默认是InnoDB

InnoDB存储引擎核心特性说明

  1. 事务
  2. 行锁
  3. MVCC(多版本并发控制)
  4. 外键
  5. ACSR自动故障恢复
  6. 热备
  7. 复制(多线程,GTID,MTS)

真实案例:zabbix监控系统架构整改

  • 环境:zabbix 3.2 mariaDB 5.5 centos 7.3
  • 现象:zabbix卡的要死,每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满
  • 问题:zabbix版本 数据库版本 zabbix数据库500G,存在一个文件里

优化建议

  • 数据库版本升级到mariaDB最新版本,zabbix升级更高版本
  • 存储引擎改为tokudb
  • 监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
  • 关闭binlog和双1
  • 参数调整

优化原理

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

真实案例:InnoDB和MyISAM存储引擎的替换

  • 环境:centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
  • 问题:业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失

问题分析

  1. MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
  2. MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据

监控情况

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

解决方案

  1. 升级MySQL 5.5版本再升级MySQL 5.6.10版本,不要一步到位防止出现兼容性问题
  2. 迁移所有表到新环境
  3. 开启双1安全参数
#批量替换zabbix100多张innodb为tokudb
#拼凑sql语句,然后批量执行
select concat("alter table ",table_schema,".",table_name,"  engine=tokudb;") from nformation_schema.tables where table_schema='zabbix';

存储引擎设置

#查看所有可以设置的类型
SHOW ENGINES;
#查看当前数据库引擎的类型
SELECT @@default_storage_engine;
  • 也可以在my.cnf文件中[mtsqld]标签下设置default_storage_engine=InnoDB指定数据库默认引擎为InnoDB
#通过建表语句查看表存储引擎状态
show create table test;
#指定查看当前库下的某个表的引擎
SHOW TABLE STATUS LIKE 'countrylanguage'
#查看除系统库以外的表引擎
select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

修改存储引擎

#修改存储引擎
alter table test.test engine=myisam;
#查看表引擎
show create table test.test;
#如果表引擎是innodb,可以用该语句整理碎片
alter table test.test engine=innodb;

真实案例:大量数据碎片处理

  • 环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
  • 问题::数据量级较大,经常需要按月删除历史数据,磁盘空间占用很大,不释放
  • 之前处理方法:将数据逻辑导出,手工truncate表,然后导入进去

解决方法

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

InnoDB存储引擎物理存储结构

最直观的存储方式(/data/mysql/data)

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

表空间(Tablespace)

共享表空间(ibdata1~ibdataN)

  • 5.5 版本的默认模式,5.6中转换为了独立表空间,需要将所有数据存储到同一个表空间中,管理比较混乱.5.5版本出现的管理模式,也是默认的管理模式
  • 5.6 版本共享表空间保留,只用来存储:数据字典信息,undo,临时表
  • 5.7 版本,临时表(tmp)被独立出去
  • 8.0版本,undo被独立出去
  • 具体变化参考官方文档
  • 5.6
  • 5.7
  • 8.0

共享表空间设置

  • 在搭建MySQL时,初始化数据之前设置到参数文件中
mysqld --initialize-insecure --user=mysql --basedir=/data/mysql/data innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
  • 相关查询语句
#查看数据文件位置
select @@innodb_data_file_path;
#查看自动扩展量,单位为M
show variables like '%extend%';
SELECT @@innodb_autoextend_increment;

独立表空间

  • 从5.6版本开始,默认表空间不再使用共享表空间,替换为独立表空间.主要存储的是用户数据,存储特点为,一个表一个ibd文件,存储数据行和索引信息
#查看当前数据库是否为表空间格式 0为共享 1为独立
select @@innodb_file_per_table;

最终结论

  • 元数据=数据行+索引
  • mysql表数据=(ibdataN+frm)+ibd(段、区、页)

MySQL的存储引擎日志

  • Redo Log : ib_logfile0,ib_logfile1,重做日志
  • Undo Log : ibdata1,ibdata2(存储在共享表空间中),回滚日志
  • ibtmp1 : 临时表,在做join union操作产生临时数据,用完就自动删除

MySQL数据库数据迁移

当数据表是独立表空间时,在服务器或数据库故障时并且拥有数据表的ibd文件时,可以使用该方法恢复数据

  1. 在新的数据库中创建和原表结构一致的空表
  2. 使用该命令alter table 表名 discard tablespace;执行以后,将空表的ibd文件删除
  3. 将原表的ibd文件复制到数据目录下,并且修改权限
  4. 使用该命令alter table 表名 import tablespace;将原表ibd进行导入,完成数据迁移和恢复

真实案例:断电引起故障,启动后/只读

  • 硬件及软件环境:联想服务器(IBM) 磁盘500G 没有raid centos 6.8 mysql 5.6.33 innodb引擎 独立表空间 LNMT
  • 开发用户专用库:jira(bug追踪) confluence(内部知识库)
  • 故障描述:开发人员使用的服务器断电重启后'/'只读,提示输入fsck重启,系统成功启动,mysql启动不了
  • 结果:confulence库在,jira库不见了

求助过程

求助者：
这种情况怎么恢复？
回答：
有备份没
求助者：
连二进制日志都没有,没有备份,没有主从
回答：
没招了,jira需要硬盘恢复了
  • jira库需要硬盘恢复了
  • 能不能暂时把confulence库先打开用着
  • 将生产环境的confulence库,拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

使用数据迁移方式恢复confulence库数据

  • 必须先获得confulence所有库的建表语句,在其他同事的虚拟机里获得了2016年的confulence库使用命令mysqldump -uroot -ppassw0rd -B confulence --no-data >test.sql获得了只有表结构的sql语句,到这步为止,表结构有了
  • 创建confulence库的表,因为部分表设有外键,set foreign_key_checks=0跳过外键检查,并一步步处理数据表的结构确保创建成功
  • 拼凑出所有表的命令并使用该命令alter table 表名 dicard tablespace;执行以后,将空表的ibd文件删除
  • 复制生产环境的confulence库的ibd文件,拼凑出所有表的命令并使用该命令alter table 表名 import tablespace;,表都可以访问了,数据挽回到了出现问题时刻的状态