当前位置:首页 > Mysql > 正文内容

MySQL的存储引擎(一)Innodb

5年前 (2019-09-08)Mysql492

一.存储引擎简介

image.png


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 提供以下存储引擎:

1:InnoDB

2:MyISAM

3:MEMORY

4:ARCHIVE

5:FEDERATED

6:EXAMPLE

7:BLACKHOLE

8:MERGE

9:NDBCLUSTER

10:CSV


还可以使用第三方存储引擎:

1:MySQL当中插件式的存储引擎类型

2:MySQL的两个分支

3:perconaDB

4: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版本之后,默认的存储引擎,提供高可靠性和高性能。


优点:

1:事务安全(遵从 ACID)

2:MVCC(Multi-Versioning Concurrency Control,多版本并发控制)

3:InnoDB 行级别锁定

4:Oracle 样式一致非锁定读取

5:表数据进行整理来优化基于主键的查询

6:支持外键引用完整性约束

7:大型数据卷上的最大性能

8:将对表的查询与不同存储引擎混合

9:出现故障后快速自动恢复

10:用于在内存中缓存数据和索引的缓冲区池

image.png


innodb核心特性


重点:

1:MVCC

2:事务

3:行级锁

4:热备份

5:Crash Safe Recovery(自动故障恢复)



1.查看存储引擎

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.TABLES
WHERE TABLE_NAME = 'City'
AND TABLE_SCHEMA = 'world'\G


2.存储引擎的设置

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)应用割接到新数据库



四.Innodb存储引擎——表空间介绍


5.5版本以后出现共享表空间概念


表空间的管理模式的出现是为了数据库的存储更容易扩展


5.6版本中默认的是独立表空间


1、共享表空间

1)查看共享表空间


#物理查看
[root@db01 ~]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 79691776 Aug 14 16:23 ibdata1
#命令行查看
mysql> show variables like '%path%';
innodb_data_file_path =bdata1:12M:autoextend



5.6版本中默认存储:

1.系统数据

2.undo

3.临时表



5.7版本中默认会将undo和临时表独立出来,5.6版本也可以独立,只不过需要在初始化的时候进行配置


2)设置方法


#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend


2、独立表空间

对于用户自主创建的表,会采用此种模式,每个表由一个独立的表空间进行管理


1)查看独立表空间


#物理查看
[root@db01 ~]# ll /application/mysql/data/world/
-rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd

#命令行查看
mysql> show variables like '%per_table%';
innodb_file_per_table=ON



企业案例


在没有备份数据的情况下,突然断电导致表损坏,打不开数据库。


1)拷贝库目录到新库中

[root@db01 ~]# cp -r /application/mysql/data/world/ /data/3307/data/


2)启动新数据库

[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &


3)登陆数据库查看

mysql> show databases;


4)查询表中数据

mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist


5)找到以前的表结构在新库中创建表

mysql> show create table world.city;
#删掉外键创建语句
CREATE TABLE `city` (
  `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`),
  KEY `idx_city` (`Population`,`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;


6)删除表空间文件

mysql> alter table city_new discard tablespaces;


7)拷贝旧表空间文件

[root@db01 world]# cp /data/3307/data/world/city.ibd /data/3307/data/world/city_new.ibd


8)授权

[root@db01 world]# chown -R mysql.mysql *


9)导入表空间

mysql> alter table city_new import tablespace;


“ MySQL的存储引擎(一)Innodb” 的相关文章

mysql数据库SQL语句及字符集定义

mysql数据库SQL语句及字符集定义

SQL简单语句用于数据库的连接管理:mysql命令行管理工具:mysqladmin备份数据库和表的内容:mysqldumpMySQL接口自带的命令\h 或 help 或?    查看帮助 \G   ...

MySQL的存储引擎(二)Innodb核心特性——事务

MySQL的存储引擎(二)Innodb核心特性——事务

Innodb核心特性——事务1.什么是事务主要针对DML语句(update,delete,insert)一组数据操作执行步骤,这些步骤被视为一个工作单元:1)用于对多个语句进行分组2)可以在多个客户机并发访问同一个表中的数据时使用所有步骤都成功或都失败1)如果所有步骤正常,则执行2)如果步骤出现错误...

MySQL集群——MHA高可用及读写分离

MySQL集群——MHA高可用及读写分离

一.MHA简介MHA能够在较短的时间内实现自动故障检测和故障转移,通常在10-30秒以内;在复制框架中,MHA能够很好地解决复制过程中的数据一致性问题,由于不需要在现有的replication中添加额外的服务器,仅需要一个manager节点,而一个Manager能管理多套复制,所以能大大地节约服务器...