目录
一.存储引擎简介
?
- 1、文件系统：
- 1.1 操作系统组织和存取数据的一种机制。
- 1.2 文件系统是一种软件。
- 2、文件系统类型：ext2 3 4 ，xfs 数据
- 2.1 不管使用什么文件系统，数据内容不会变化
- 2.2 不同的是，存储空间、大小、速度。
- 3、MySQL引擎： 可以理解为，MySQL的“文件系统”，只不过功能更加强大。
- 4、MySQL引擎功能： 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能
总之，存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。
二.MySQL自带的存储引擎类型
MySQL 提供以下存储引擎:
01）InnoDB02）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;
-------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------
| Engine | Support | Comment | Transactions | XA | Savepoints |
-------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
-------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------
9 rows in set (0.00 sec)
#查看innodb的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
-------------- ---------------------- --------
| table_schema | table_name | engine |
-------------- ---------------------- --------
| mysql | innodb_index_stats | InnoDB |
| mysql | innodb_table_stats | InnoDB |
| mysql | slave_master_info | InnoDB |
| mysql | slave_relay_log_info | InnoDB |
| mysql | slave_worker_info | InnoDB |
-------------- ---------------------- --------
20 rows in set (0.03 sec)
#查看myisam的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';
-------------------- --------------------------- --------
| table_schema | table_name | engine |
-------------------- --------------------------- --------
| information_schema | COLUMNS | MyISAM |
| information_schema | EVENTS | MyISAM |
| mysql | help_category | MyISAM |
| mysql | ndb_binlog_index | MyISAM |
-------------------- --------------------------- --------
33 rows in set (0.01 sec)
- 1、innodb和myisam的区别
物理上的区别：
#进入mysql目录
[[email protected]~l]# cd /application/mysql/data/mysql
#myisam
[[email protected] 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目录
[[email protected] world]# cd /application/mysql/data/world/
#innodb
[[email protected] 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 确认会话存储引擎
#查询默认存储引擎
mysql> SELECT @@default_storage_engine;
--------------------------
| @@default_storage_engine |
--------------------------
| InnoDB |
--------------------------
1 row in set (0.00 sec)2）使用 SHOW 确认每个表的存储引擎
#查看表的存储引擎
mysql> show create table cityG
*************************** 1. row ***************************
...
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show table status like 'city'G
*************************** 1. row ***************************
Name: city
Engine: InnoDB
...
3）使用 INFORMATION_SCHEMA 确认每个表的存储引擎
#查看表的存储引擎
mysql> select table_name,engine from information_schema.tables where table_name='city' and table_schema='world'G
*************************** 1. row ***************************
table_name: city
engine: InnoDB
1 row in set (0.00 sec)
- 4.存储引擎的设置
1）在启动配置文件中设置服务器存储引擎
#在配置文件的[mysqld]标签下添加/etc/my.cnf
[mysqld]
default-storage-engine=innodb2）使用 SET 命令为当前客户机会话设置
#在MySQL命令行中临时设置
mysql> SET @@storage_engine=myisam;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#查看
mysql> select @@default_storage_engine;
--------------------------
| @@default_storage_engine |
--------------------------
| MyISAM |
--------------------------
1 row in set (0.00 sec)
（3）在 CREATE TABLE 语句指定
#建表的时候指定存储引擎
create table t (i INT) engine = <Storage Engine>;
#如：建test1表，指定存储引擎为myisam
mysql> create table test1(id int) engine=myisam;
Query OK, 0 rows affected (0.02 sec)三.真实企业案例
项目背景：
公司原有的架构：一个展示型的网站，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）
[[email protected] test]# mysql -uroot -p1
mysql> show tables;
----------------
| Tables_in_test |
----------------
| student4 |
| tlbb2 |
----------------
2 rows in set (0.00 sec)
#测试环境（先简单创几个存储引擎为myisam）
mysql> create table test1(id int) engine=myisam;
Query OK, 0 rows affected (0.02 sec)
mysql> create table test2(id int) engine=myisam;;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test3(id int) engine=myisam;;
Query OK, 0 rows affected (0.01 sec)2）准备一个5.6.44版本的新数据库
#先导库，准备一个环境
[[email protected] test]# mysqldump -uroot -p1 -B test >/tmp/full.sql3）对备份数据进行处理（将engine字段替换）
#方法一
[[email protected] ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/full.sql
#方法二
[[email protected] ~]# vim /tmp/full.sql
:%s#MyISAM#InnoDB#g4）将修改后的备份恢复到新库
#方法一
[[email protected] test]# mysql -uroot -p123 -h 10.0.0.52 < /tmp/full.sql5）应用测试环境连接新库，测试所有功能
#连接
[[email protected] ~]# mysql -uroot -p123
#查看存储引擎
mysql> select @@default_storage_engine;
--------------------------
| @@default_storage_engine |
--------------------------
| InnoDB |
--------------------------
1 row in set (0.00 sec)6）停应用，将备份之后的生产库发生的新变化，补偿到新库
7）应用割接到新数据库
项目结果：
解决了”小问题”
四.Innodb存储引擎——表空间介绍
?
5.5版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展
5.6版本中默认的是独立表空间
- 1、共享表空间
1）查看共享表空间
#物理查看
[[email protected] ~]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 79691776 Aug 14 16:23 ibdata1
#命令行查看
mysql> show variables like '%path%';
----------------------- ------------------------------------
| Variable_name | Value |
----------------------- ------------------------------------
| innodb_data_file_path | ibdata1:76M;ibdata2:50M:autoextend |
| ssl_capath | |
| ssl_crlpath | |
----------------------- ------------------------------------
3 rows in set (0.00 sec)
#查看大小
[[email protected] data]# du -sh ibdata1
76M ibdata1 5.6版本中默认存储:
1.系统数据
2.undo
3.临时表
5.7版本中默认会将undo和临时表独立出来，5.6版本也可以独立，只不过需要在初始化的时候进行配置
2）设置方法
#编辑配置文件
[[email protected] ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend- 2、独立表空间
对于用户自主创建的表，会采用此种模式，每个表由一个独立的表空间进行管理
1）查看独立表空间
#物理查看
[[email protected] ~]# ll /application/mysql/data/world/
-rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd
#命令行查看
mysql> show variables like '%per_table%';
----------------------- -------
| Variable_name | Value |
----------------------- -------
| innodb_file_per_table | ON |
----------------------- -------
1 row in set (0.01 sec)
企业案例
在没有备份数据的情况下，突然断电导致表损坏，打不开数据库。
1）拷贝库目录到新库中（先准备一个和原表结构一样的环境）
[[email protected] data]# tar zcf world1.tgz world/
#传到测试环境
[[email protected] data]# scp world1.tgz 172.16.1.52:/application/mysql/data/
#解压
[[email protected] data]# tar xf world1.tgz 2）启动新数据库
[[email protected] ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &3）登陆数据库查看r
#查看
mysql> show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| test |
| world |
--------------------
5 rows in set (0.00 sec)4）查询表中数据
mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist （表不存在）5）找到以前的表结构在新库中创建表
mysql> show create table world.city;
#删掉外键创建语句
CREATE TABLE `city1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`)
#CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1; 6）删除表空间文件
#删除表空间文件
mysql> alter table city1 discard tablespace;
#在表的物理结构查看
[[email protected] world]# ll
total 1000
-rw-rw---- 1 mysql mysql 8710 Nov 4 10:26 city1.frm
-rw-rw---- 1 mysql mysql 8710 Nov 4 10:26 city.frm
-rw-rw---- 1 mysql mysql 589824 Nov 4 10:26 city.ibd
7）拷贝旧表空间文件
[[email protected] world1]# cp city.ibd city1.ibd8）授权
[[email protected] world]# chown -R mysql.mysql *9）导入表空间
#查看，会报错
mysql> select * from city1;
#导入表空间
mysql> alter table city_new import tablespace;
#再次查看
mysql> select * from city1;10) 改表名
#改表名
mysql> alter table city1 rename city;
#再次查看
mysql> show tables;
------------------
| Tables_in_world1 |
------------------
| city |
| country |
| countrylanguage |
------------------
3 rows in set (0.00 sec)