电校讲课Mysql_实验1_存储引擎切换
1 存储引擎的切换
1.1 Mysql存储引擎概念
存储引擎是数据库的核心，是数据库底层软件组织。对于mysql来说，存储引擎是以插件的形式运行的。Mysql数据库及其分支版本主要的存储引擎有innoDB、MyISAM、Memory。
innoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用。一般来说，支持事务安全表（ACID），支持行锁定和外键，而且有较高的并发读取频率，InnoDB是默认的MySQL引擎。
MyISAM是独立于操作系统的，可以轻松将MyISAM的表从windowns服务器移植到Linux服务器。ISAM(indexed Sequebtuak Access Method) 有索引的顺序访问的缩写。每个MyISAM数据表在磁盘上存储的有3个文件，每个文件名以表名开头，并搭配不同的扩展名区分文件类型：.frm 格式 用于存储表的定义、.myd用于存储数据、.myi用于存放表索引。
在MySQL5.5之前的版本，MyISAM一直是Mysql默认的存储引擎。但由于其不支持外键，不支持事务，锁的支持粒度只到表级锁。在OLTP读写并重的使用场景中存在明显的缺陷，因此在MySQL5.5及以后的版本中被InnoDB引擎取代为默认的存储引擎。
Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中。
先来看一下主要的存储引擎大的特性对比，如下图所示：
|
存储引擎名称 |
特点 |
应用场景 |
|
InnoDB |
支持事务、行锁、支持MVCC多版本并发控制，并发性高，支持外键。 |
应用于OLTP业务系统。 |
|
MyISAM |
不支持事务、表锁、并发性低，支持全文索引。 |
应用于OLAP业务系统。 |
|
Memory |
表中的数据都在内存中存放，不落地。支持Hash和Btree索引，数据安全性不高，读取熟读快。 |
应用于对数据安全性要求不高的环境下。 |
1.2 Mysql存储引擎查看
innodb和MyISAM是最主流的两个存储引擎，而且MySQL数据库默认的存储引擎是innodb。可以通过如下命令查看当前书库支持的存储引擎类型。
上图可以看出当前数据库支持的存储引擎有InnoDB、CSV、MyISAM、BLACKHOLE、PERFORMANCE_SCHEMA、MEMORY等等，当前默认的存储引擎是innodb，该存储引擎支持事务，支持行读锁，支持外键，支持保存点。
1.3 mysql存储引擎切换主使用场景
1、Mysql表的碎片整理
在日常运维中，当我们删除一些大表的无用数据，发现数据文件的大小并没有减少，因为使用delete删除数据的时候，只是删除标识位，没有整理数据文件，因此并不会彻底释放表空间。
因此需要对期对mysql表碎片情况进行统计，对碎片比率高的大表有必要进行碎片整理，对表进行优化，释放存储空间。
2、xtrabackup备份myisam存储引擎的表
由于xtrabackup 工具备份数据到最后会执行flash tables with read lock ，对数据库加只读锁表以便进行一致性备份，然而对于myisam存储引擎的表加锁，会导致mysql的sql线程hung住。
针对以上两个场景的问题目前的比较好的解决方式是修改表结构为innodb存储引擎的表，即进行表的存储引擎切换。
实验1：mysql表存储引擎切换
要求：
在对mysql表的存储引擎进行切换过程中，一定要在业务低峰时段操作。避免出现锁表导致阻塞业务读写操作的情况。
环境：
Red Hat Linux Enterprise release 7.4 mysql5.7.30 percona-toolkit-3.2.1
实验步骤：
1 、采用alter table 方式切换表的存储引擎
alter table table_name engine=innodb //修改表的存储引擎为innodb
优点: 操作简单方便。
缺点：需要先给整表加写锁，如果数据量比较大，耗时久，影响业务。
2 、采用percona-toolkit工具切换表的存储引擎
percona-toolkit是percona公司开发的一个mysql开源运维管理工具。
使用percona-toolkit工具的pt-online-schema-change 命令，不锁表对业务基本无影响。
pt-online-schema-change的主要命令参数选项有：
--alter 指定ALTER 语句，正常的ALTER TABLE TBNAME ;
--execute 确定执行ALTER操作，这个选项如果不指定，则仅做检查然后退出。
--host 连接的主机名;
--user --password 连接的用户名和密码；
-D= 在哪个mysql 库上执行；
-t=针对哪个表执行操作；
--check-replication-filters 检查复制中是否设置了过滤条件;
--preserve-triggers 保留触发器；
[root@pxc3 bin]# ./pt-online-schema-change --user=root --password=mysql123 --alter='engine=innodb' D=emm_dbmodel,t=t --no-check-replication-filters --execute --preserve-triggers
//登陆mysql数据库主机，执行在线修改表的存储引擎语句，在执行的过程中保留触发器，不检查复制中设置的过滤条件。
No slaves found. See --recursion-method if host pxc3 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `emm_dbmodel`.`t`...
Creating new table...
Created new table emm_dbmodel._t_new OK.
Altering new table...
Altered `emm_dbmodel`.`_t_new` OK.
2020-10-27T20:23:25 Creating triggers...
2020-10-27T20:23:25 Created triggers OK.
2020-10-27T20:23:25 Copying approximately 8 rows...
Cannot connect to h=192.168.56.130,p=...,u=root
2020-10-27T20:23:25 Copied rows OK.
2020-10-27T20:23:25 Adding original triggers to new table.
2020-10-27T20:23:25 Analyzing new table...
2020-10-27T20:23:25 Swapping tables...
2020-10-27T20:23:25 Swapped original and new tables OK.
2020-10-27T20:23:25 Dropping old table...
2020-10-27T20:23:25 Dropped old table `emm_dbmodel`.`_t_old` OK.
2020-10-27T20:23:25 Dropping triggers...
2020-10-27T20:23:25 Dropped triggers OK.
Successfully altered `emm_dbmodel`.`t`.
验证MySQL表的存储引擎已经切换：
mysql> show table status like 't%' \G ;//查看表的状态，包含表的存储引擎信息。
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2020-10-27 20:23:25
Update_time: 2020-10-27 20:23:25
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
实验总结:
本实验通过两种方法对MySQL表的存储引擎进行切换修改，在考虑不锁表且对业务影响最低的情况下，建议使用PT工具包中的pt-online-schema-change命令进行存储引擎切换操作。
