innodb存储引擎简介

1.innodb核心特性

MVCC
事务
行级锁			#innodb支持行级锁，myiasm支持表级锁
热备份			#innodb支持热备，myisam不支持热备
自动故障恢复 Crash Safe Recovery

2.存储引擎相关命令

1.查看当前数据库配置的存储引擎

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

2.查看表的存储引擎

# 查看哪些表时innodb存储引擎
mysql> select TABLE_SCHEMA,TABLE_Name,ENGINE from tables where ENGINE='innodb';

# 查看哪些表时myisam存储引擎
mysql> select TABLE_SCHEMA,TABLE_Name,ENGINE from tables where ENGINE='myisam';

# 查看表信息
mysql> select * from information_schema.tables where table_name='test11'G
# 查看指定表的存储引擎
mysql> show create table city;

# 查看列信息
mysql> select * from COLUMNS where COLUMN_NAME='name'G

3.修改存储引擎

# 临时设置(会话级别)
mysql> set @@default_storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)

# 临时设置(全局级别)
mysql> set global @@default_storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+
1 row in set (0.00 sec)

# 永久修改
[root@db03 mysql]# vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam

# 建表时指定存储引擎
mysql> create table innodb(id int) ENGINE=innodb;

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

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

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

# 扩展:如何批量修改
需求:将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';

4.案例

1.项目背景

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。

2.升级原因

1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。

3.解决方案

1.提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
	1）如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
	2）5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。

4.执行计划

1.准备一台新的数据库，版本为5.6.38
2.就数据库备份数据
	[root@db01 ~]# mysqldump -uroot -p123 --triggers -R --master-data=2 -B >/tmp/full.sql
3.将备份的数据库传到新数据库
	scp、rsync、NFS、导出导入、sftp
4.修改sql中的存储引擎
	sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql
5.将修改后的sql文件导入新数据
	mysql < /tmp/full.sql
	source
	.
6.将代码中的数据库地址修改为新的数据库地址
7.通过binlog将数据迁移过程中新生成的数据也倒入新库