一.存储引擎简介
- 1、文件系统：
- 1.1 操作系统组织和存取数据的一种机制。
- 1.2 文件系统是一种软件。
- 2、文件系统类型：ext2 3 4 ，xfs 数据
- 2.1 不管使用什么文件系统，数据内容不会变化
- 2.2 不同的是，存储空间、大小、速度。
- 3、MySQL引擎：
- 3.1 可以理解为，MySQL的“文件系统”，只不过功能更加强大。
- 4、MySQL引擎功能：
- 4.1 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能
总之，存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。
二.MySQL自带的存储引擎类型
MySQL 提供以下存储引擎:
- 01)InnoDB
- 02)MyISAM
- 03)MEMORY
- 04)ARCHIVE
- 05)FEDERATED
- 06)EXAMPLE
- 07)BLACKHOLE
- 08)MERGE
- 09)NDBCLUSTER
- 10)CSV
还可以使用第三方存储引擎:
- 01)MySQL当中插件式的存储引擎类型
- 02)MySQL的两个分支
- 03)perconaDB
- 04)mariaDB
- #查看当前MySQL支持的存储引擎类型
- mysql> show engines
- #查看innodb的表有哪些
- mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
- #查看myisam的表有哪些
- mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';
1、innodb和myisam的区别
物理上的区别：
- #进入mysql目录
- [root@db01~l]# cd /application/mysql/data/mysql
- #查看所有user的文件
- [root@db01 mysql]# ll user.*
- -rw-rw---- 1 mysql mysql 10684 Mar 6 2017 user.frm
- -rw-rw---- 1 mysql mysql 960 Aug 14 01:15 user.MYD
- -rw-rw---- 1 mysql mysql 2048 Aug 14 01:15 user.MYI
- #进入word目录
- [root@db01 world]# cd /application/mysql/data/world/
- #查看所有city的文件
- [root@db01 world]# ll city.*
- -rw-rw---- 1 mysql mysql 8710 Aug 14 16:23 city.frm
- -rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd
2.innodb存储引擎的简介
在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
优点:
- 01)事务安全(遵从 ACID)
- 02)MVCC(Multi-Versioning Concurrency Control，多版本并发控制)
- 03)InnoDB 行级别锁定
- 04)Oracle 样式一致非锁定读取
- 05)表数据进行整理来优化基于主键的查询
- 06)支持外键引用完整性约束
- 07)大型数据卷上的最大性能
- 08)将对表的查询与不同存储引擎混合
- 09)出现故障后快速自动恢复
- 10)用于在内存中缓存数据和索引的缓冲区池
innodb核心特性
重点:
- MVCC
- 事务
- 行级锁
- 热备份
- Crash Safe Recovery(自动故障恢复)
3.查看存储引擎
1)使用 SELECT 确认会话存储引擎
- #查询默认存储引擎
- SELECT @@default_storage_engine;
2)使用 SHOW 确认每个表的存储引擎
- #查看表的存储引擎
- SHOW CREATE TABLE City\G
- SHOW TABLE STATUS LIKE 'CountryLanguage'\G
3)使用 INFORMATION_SCHEMA 确认每个表的存储引擎
- #查看表的存储引擎
- SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'City'AND TABLE_SCHEMA = 'world'\G
4.存储引擎的设置
1)在启动配置文件中设置服务器存储引擎
- #在配置文件的[mysqld]标签下添加
- [mysqld]
- default-storage-engine=<Storage Engine>
2)使用 SET 命令为当前客户机会话设置
- #在MySQL命令行中临时设置
- SET @@storage_engine=<Storage Engine>
(3)在 CREATE TABLE 语句指定
- #建表的时候指定存储引擎
- CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
三.真实企业案例
项目背景：
公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本(MYISAM)，50M数据量。
小问题不断：
1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
2、不支持故障自动恢复(CSR)：当断电时有可能会出现数据损坏或丢失的问题。
如何解决：
1、提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
1)如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
2)5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。
2、实施过程和注意要素
1)备份生产库数据(mysqldump)
- [root@db01 ~]# mysqldump -uroot -p123 -A --triggers -R --master-data=2 >/tmp/full.sql
2)准备一个5.6.38版本的新数据库
3)对备份数据进行处理(将engine字段替换)
- [root@db01 ~]# sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#g' /tmp/full.sql
4)将修改后的备份恢复到新库
5)应用测试环境连接新库，测试所有功能
6)停应用，将备份之后的生产库发生的新变化，补偿到新库
7)应用割接到新数据库
