MySQL的存储引擎

Wesley13
• 阅读 540

 一.存储引擎简介

MySQL的存储引擎

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  62017 user.frm

-rw-rw---- 1 mysql mysql   960 Aug 1401:15 user.MYD

-rw-rw---- 1 mysql mysql  2048 Aug 1401: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 1416:23 city.frm

-rw-rw---- 1 mysql mysql 688128 Aug 1416: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)用于在内存中缓存数据和索引的缓冲区池

MySQL的存储引擎

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

4.存储引擎的设置

1)在启动配置文件中设置服务器存储引擎

#在配置文件的[mysqld]标签下添加

[mysqld]

default-storage-engine=

2)使用 SET 命令为当前客户机会话设置

#在MySQL命令行中临时设置 SET @@storage_engine=

3)在 CREATE TABLE 语句指定

#建表的时候指定存储引擎

CREATE TABLE t (i INT) 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存储引擎——表空间介绍

MySQL的存储引擎

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;

五.Innodb核心特性——事务

1.什么是事务

主要针对DML语句(update,delete,insert)

一组数据操作执行步骤,这些步骤被视为一个工作单元:

1)用于对多个语句进行分组

2)可以在多个客户机并发访问同一个表中的数据时使用

所有步骤都成功或都失败

1)如果所有步骤正常,则执行

2)如果步骤出现错误或不完整,则取消

2.事务的通俗理解

伴随着“交易”出现的数据库概念。

我们理解的“交易”是什么?

1)物与物的交换(古代)

2)货币现金与实物的交换(现代1)

3)虚拟货币与实物的交换(现代2)

4)虚拟货币与虚拟实物交换(现代3)

数据库中的“交易”是什么?

1)事务又是如何保证“交易”的“和谐”?

2)ACID

3.事务ACID特性

Atomic(原子性)

所有语句作为一个单元全部成功执行或全部取消。

Consistent(一致性)

如果数据库在事务开始时处于一致状态,则在执行该。
事务期间将保留一致状态。

Isolated(隔离性)

事务之间不相互影响。

Durable(持久性)

事务成功完成后,所做的所有更改都会准确地记录在
数据库中。所做的更改不会丢失。

4.事务流程举例

MySQL的存储引擎

5.事务的控制语句

如下:

START TRANSACTION(或 BEGIN):显式开始一个新事务

SAVEPOINT:分配事务过程中的一个位置,以供将来引用

COMMIT:永久记录当前事务所做的更改

ROLLBACK:取消当前事务所做的更改

ROLLBACK TO SAVEPOINT:取消在 savepoint 之后执行的更改

RELEASE SAVEPOINT:删除 savepoint 标识符

SET AUTOCOMMIT:为当前连接禁用或启用默认 autocommit 模式

一个成功事务的生命周期

begin;

sql1

sql2

sql3

...

commit;

一个失败事务的生命周期

begin;

sql1

sql2

sql3

...

rollback;

3.自动提交

#查看自动提交

mysql> show variables like 'autocommit';

#临时关闭

mysql> set autocommit=0;

#永久关闭

[root@db01 world]# vim /etc/my.cnf [mysqld] autocommit=0

4.事务演示

1)成功事务

mysql> create table stu(id int,name varchar(10),sex enum('f','m'),money int);

mysql> begin; mysql> insert into stu(id,name,sex,money) values(1,'zhang3','m',100), (2,'zhang4','m',110);

mysql> commit;

2)事务回滚

mysql> begin;

mysql> update stu set name='zhang3';

mysql> delete from stu; mysql> rollback;

6.事务隐式提交情况

1)现在版本在开启事务时,不需要手工begin,只要你输入的是DML语句,就会自动开启事务。

2)有些情况下事务会被隐式提交

例如:

在事务运行期间,手工执行begin的时候会自动提交上个事务

在事务运行期间,加入DDL、DCL操作会自动提交上个事务

在事务运行期间,执行锁定语句(lock tables、unlock tables)

load data infile

select for update

在autocommit=1的时候

7.事务日志redo基本功能

1)Redo是什么?

redo,顾名思义“重做日志”,是事务日志的一种。

2)作用是什么?

在事务ACID过程中,实现的是“D”持久化的作用。

 MySQL的存储引擎

特性:WAL(Write Ahead Log)日志优先写

REDO:记录的是,内存数据页的变化过程

3)REDO工作过程

#执行步骤 update t1 set num=2 where num=1;

1)首先将t1表中num=1的行所在数据页加载到内存中buffer page

2)MySQL实例在内存中将num=1的数据页改成num=2

3)num=1变成num=2的变化过程会记录到,redo内存区域,也就是redo buffer page中

#提交事务执行步骤 commit;

1)当敲下commit命令的瞬间,MySQL会将redo buffer page写入磁盘区域redo log

2)当写入成功之后,commit返回ok

8.redo数据实例恢复过程

图解

9.事务日志undo

1)undo是什么?

undo,顾名思义“回滚日志”,是事务日志的一种。

_2)作用是什么?

在事务ACID过程中,实现的是“A”原子性的作用。当然CI的特性也和undo有关

MySQL的存储引擎

10.redo和undo的存储位置

#redo位置

[root@db01 data]# ll /application/mysql/data/

-rw-rw---- 1 mysql mysql 50331648 Aug 15 06:34 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Mar  6  2017 ib_logfile1

#undo位置

[root@db01 data]# ll /application/mysql/data/

-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata1

-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata2

在MySQL5.6版本中undo是在ibdata文件中,在MySQL5.7版本会独立出来。

11.事务中的锁

1)什么是“锁”?

“锁”顾名思义就是锁定的意思。

2)“锁”的作用是什么?

在事务ACID特性过程中,“锁”和“隔离级别”一起来实现“I”隔离性的作用.

排他锁:保证在多事务操作时,数据的一致性。

共享锁:保证在多事务工作期间,数据查询时不会被阻塞。

12.多版本并发控制(MVCC)

1)只阻塞修改类操作,不阻塞查询类操作

2)乐观锁的机制(谁先提交谁为准)

13.锁的粒度

MyIsam:低并发锁(表级锁)

Innodb:高并发锁(行级锁)

14.事务的隔离级别

四种隔离级别:

READ UNCOMMITTED(独立提交)

允许事务查看其他事务所进行的未提交更改

READ COMMITTED

允许事务查看其他事务所进行的已提交更改

REPEATABLE READ******

确保每个事务的 SELECT 输出一致

InnoDB 的默认级别

SERIALIZABLE

将一个事务的结果与其他事务完全隔离

#查看隔离级别

mysql> show variables like '%iso%';

#修改隔离级别为RU

[mysqld] transaction_isolation=read-uncommit mysql> use oldboy

mysql> select * from stu;

mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123);

#修改隔离级别为RC

[mysqld] transaction_isolation=read-commit

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这