MYSQL 存储引擎
六 存储引擎
简历：熟悉InnoDB核心原理：ACID MVCC,事务,锁等
1. 介绍
类似于Linux系统中文件系统
2. 功能
数据读写 数据安全和一致性 提高性能 热备份 自动故障恢复 高可用方面支持 等.
3. 种类
3.1 Oracle的MySQL的引擎
InnoDB MyISAM CSV MEMORY ARCHIVE mysql> show engines; mysql> select table_schema,table_name ,engine from information_schema.tables where engine=’innodb’; 说明： 存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。
3.2 其他MySQL的引擎
PerconaDB:默认是XtraDB MariaDB:默认是InnoDB 其他的存储引擎支持: TokuDB RocksDB MyRocks 以上三种存储引擎的共同点:压缩比较高,数据插入性能极高 现在很多的NewSQL,使用比较多的功能特性.
3.3 项目案例——监控系统架构整改
环境: zabbix 3.2 mariaDB 5.5 centos 7.3 现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满. 问题 :1. zabbix 版本 2. 数据库版本3. zabbix数据库500G,存在一个文件 优化建议:1.数据库版本升级到10.0版本,zabbix升级更高版本2.存储引擎改为tokudb3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)4.关闭binlog和双15.参数调整…. 优化结果: 监控状态良好 为什么?1. 原生态支持TokuDB,另外经过测试环境,10.0要比5.5 版本性能 高 2-3倍2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间4.关闭binlog ——->减少无关日志的记录.zabbix不需要注重安全，注重性能5.参数调整…——->安全性参数关闭,提高性能. 环境: 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. 重新主从5. 升级zabbix到较新版本
4.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) ,多线程(MTS,Multi-Threads-SQL )
5. 存储引擎操作类命令
5.1 使用 SELECT 确认会话存储引擎
mysql> show variables like ‘%engine%’; 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. 扩展： 在线修改MySQL参数： 会话级别，例如：set default_storage_engine=myisam; 功能：只会影响到当前会话 全局级别，例如：set global default_storage_engine=myisam; 功能: 不影响当前和历史会话，只影响到新开的会话 以上两种方法，在重启之后会失效，除非参数添加至my.cnf
5.3 SHOW 确认每个表的存储引擎：
show create table city\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; Master [world]>show create table city;
5.5 修改一个表的存储引擎
db01 [oldboy]>alter table t1 engine innodb; 注意：此命令我们经常使用他，进行innodb表的碎片整理 生产需求： 将oldboy数据库下的所有1000表，存储引擎从MyISAM替换为innodb select concat(“alter table “,table_name,” engine innodb;”) from information_schema.tables where table_schema=’oldboy’ into outfile ‘/tmp/alter.sql’;
5.6 平常处理过的MySQL问题—碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎 业务特点:数据量级较大,经常需要按月删除历史数据. 问题:磁盘空间占用很大,不释放 处理方法: 以前:将数据逻辑导出,手工drop表,然后导入进去 现在: 对表进行按月进行分表(partition,中间件) 业务替换为truncate方式 定期执行： alter table t1 engine=’innodb’;
5.7 扩展:如何批量修改
需求:将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’;
6 InnoDB存储引擎物理存储结构
6.0 最直观的存储方式(/data/mysql/data)
ibdata1：系统数据字典信息(统计信息)，UNDO（回滚）表空间等数据 ib_logfile0 ~ ib_logfile1: REDO（重做日志）日志文件，事务日志文件。 ibtmp1： 临时表空间磁盘位置，存储临时表 frm：存储表的列信息 ibd：表的数据行和索引
6.1 表空间(Tablespace)
6.1.1、共享表空间
需要将所有数据存储到同一个表空间中 ，管理比较混乱 5.5版本出现的管理模式，也是默认的管理模式。（数据字典，undo，临时表，索引，表数据） 5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。 5.7 版本,临时表被独立出来了 8.0版本,undo也被独立出去了 具体变化参考官方文档: https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html https://dev.mysql.com/doc/refman/5.8/en/innodb-architecture.html
6.1.2 共享表空间设置
共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中) [(none)]>select @@innodb_data_file_path; [(none)]>show variables like ‘%extend%’; innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend innodb_autoextend_increment=64
6.1.3 独立表空间
实战演练
#备份数据 root@oldboy world]# cp city.ibd city.ibd.back mysql> alter table city discard tablespace; Query OK, 0 rows affected (0.02 sec) #删除city.ibd 将其回复过来 [root@oldboy world]# cp city.ibd.back city.ibd [root@oldboy world]# chown -R mysql.mysql mysql> alter table city import tablespace; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> select from city limit 10;
db01 [(none)]>select @@innodb_file_per_table; +————————————-+ | @@innodb_file_per_table | +————————————-+ | 1 | +————————————-+ alter table city dicard tablespace; alter table city import tablespace; 6.1.5 真实的学生案例 案例背景: 硬件及软件环境: 联想服务器（IBM） 磁盘500G 没有raid centos 6.8 mysql 5.6.33 innodb引擎 独立表空间 备份没有，日志也没开 开发用户专用库: jira(bug追踪) 、 confluence(内部知识库) ———>LNMT 故障描述: 断电了，启动完成后“/” 只读 fsck 重启,系统成功启动,mysql启动不了。 结果：confulence库在 ， jira库不见了 学员求助内容: 求助： 这种情况怎么恢复？ 我问： 有备份没 求助： 连二进制日志都没有，没有备份，没有主从 我说： 没招了，jira需要硬盘恢复了。 求助： 1、jira问题拉倒中关村了 2、能不能暂时把confulence库先打开用着 将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的 问：有没有工具能直接读取ibd 我说：我查查，最后发现没有 我想出一个办法来： 表空间迁移: create table xxx alter table confulence.t1 discard tablespace; alter table confulence.t1 import tablespace; 虚拟机测试可行。 处理问题思路: confulence库中一共有107张表。 1、创建107和和原来一模一样的表。 他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库 mysqldump -uroot -ppassw0rd -B confulence —no-data >test.sql 拿到你的测试库，进行恢复 到这步为止，表结构有了。 2、表空间删除。 select concat(‘alter table ‘,table_schema,’.’table_name,’ discard tablespace;’) from information_schema.tables where table_schema=’confluence’ into outfile ‘/tmp/discad.sql’; source /tmp/discard.sql 执行过程中发现，有20-30个表无法成功。主外键关系 很绝望，一个表一个表分析表结构，很痛苦。 set foreign_key_checks=0 跳过外键检查。 把有问题的表表空间也删掉了。 3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中 select concat(‘alter table ‘,table_schema,’.’table_name,’ import tablespace;’) from information_schema.tables where table_schema=’confluence’ into outfile ‘/tmp/discad.sql’; 4、验证数据 表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）
实战演练
[root@oldboy data]# cp -r world world.back select concat(‘alter table ‘,table_schema,’.’,table_name,’ discard tablespace;’) from information_schema.tables where table_schema=’world’ into outfile ‘/tmp/discard.sql’; mysql> source /tmp/discard.sql Query OK, 0 rows affected (0.01 sec) ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails () Query OK, 0 rows affected (0.00 sec) mysql> set foreign_key_checks=0; mysql> source /tmp/discard.sql [root@oldboy world.back]# cp -a .ibd ../world/ [root@oldboy world.back]# chown -R mysql.mysql /data/ mysql> select concat(‘alter table ‘,table_schema,’.’,table_name,’ import tablespace;’) from information_schema.tables where table_schema=’world’ into outfile ‘/tmp/import.sql’; mysql> source /tmp/import.sql; mysql> select * from city;
若有收获，就点个赞吧
0 人点赞
